java导出复杂格式的Excel（poi组件）

原文链接：https://blog.csdn.net/qq_42612200/article/details/105860179

效果

img

POM.xml

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
</dependency>
 
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>3.17</version>
</dependency>
 
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>

样式设置-》https://www.cnblogs.com/fqfanqi/p/6172223.html

MyExcelUtil类

public class MyExcelUtil {
	/**
	 * 导出 Excel
	 * 
	 * @param response  HttpServletResponse  响应对象
	 * @param fileName  导出时的 项目名称 
	 * @param getselectTo    需要要导出的数据
	 */
 
	public static void run(HttpServletResponse response,String fileName,List<collectionAndPaymentListBean> getselectTo) {
 
		/** 第一步，创建一个Workbook，对应一个Excel文件  */
		HSSFWorkbook wb = new HSSFWorkbook();  
 
		/** 第二步，在Workbook中添加一个sheet,对应Excel文件中的sheet  */
		HSSFSheet sheet = wb.createSheet("excel导出标题");
 
		/** 第三步，设置样式以及字体样式*/
		//标题样式
		HSSFCellStyle titleStyle = createTitleCellStyle(wb);
		//创建表头样式
		HSSFCellStyle headerStyle = createHeadCellStyle(wb);
		//内容样式
		HSSFCellStyle contentStyle = createContentCellStyle(wb);
		//		日期格式
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		
        //设置全局 单元格  默认 宽度 
		sheet.setDefaultColumnWidth(12);
 
		/** 第四步，创建标题 ,合并标题单元格 */
		// 行号
		int rowNum = 0;
		// 创建第一页的第一行，索引从0开始
		HSSFRow row0 = sheet.createRow(rowNum++);
		row0.setHeight((short) 800);// 设置行高
 
		String title = "工程项目结算明细表";
		HSSFCell c00 = row0.createCell(0);
		c00.setCellValue(title);
		c00.setCellStyle(titleStyle);
		// 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 17));//标题合并单元格操作，17为总列数 
 
 
 
 
		// 第二行
		HSSFRow row1 = sheet.createRow(rowNum++);
		row1.setHeight((short) 500);
		for (int i = 0; i < 18; i++) {
			HSSFCell tempCell = row1.createCell(i);
			tempCell.setCellStyle(headerStyle);
			if (i == 0) {
				tempCell.setCellValue("编制单位：广东东篱环境股份有限公司   ");
 
			} else if (i == 5) {
				tempCell.setCellStyle(headerStyle);
				tempCell.setCellValue("制表日期: "+sdf.format(new Date()));
			} else if(i == 9) {
				tempCell.setCellValue(" 单位：人民币/元");
			}
		}
 
		// 合并
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 4));
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 5, 8));
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 9, 17));
 
 
 
 
		// 第三行
		for (collectionAndPaymentListBean c :  getselectTo) {
 
			HSSFRow row2 = sheet.createRow(rowNum++);
			row2.setHeight((short) 500);
			for (int i = 0; i < 18; i++) {
 
				HSSFCell tempCell = row2.createCell(i);
 
				tempCell.setCellStyle(headerStyle);
				if (i == 0) {
					tempCell.setCellValue("项目名称： "+c.getProjectName());
				} else if (i == 8) {
					tempCell.setCellValue("合同编号: "+c.getContractNo());
				} else if(i == 14) {
					tempCell.setCellValue("业主: ");
				}
			}
 
			// 合并
			sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 7));
			sheet.addMergedRegion(new CellRangeAddress(2, 2, 8, 13));
			sheet.addMergedRegion(new CellRangeAddress(2, 2, 14, 17));
 
			break;
		}
 
 
 
 
 
		// 第四行
		for (collectionAndPaymentListBean c :  getselectTo) {
 
			HSSFRow row3 = sheet.createRow(rowNum++);
			row3.setHeight((short) 500);
			for (int i = 0; i < 18; i++) {
 
				HSSFCell tempCell = row3.createCell(i);
 
				tempCell.setCellStyle(headerStyle);
				if (i == 0) {
					tempCell.setCellValue("合同总额： "+c.getContractNo());
				} else if (i == 5) {
					Date parse = null;
					try {
						parse = sdf.parse(c.getReceiveBillDateTo());
					} catch (ParseException e) {
						e.printStackTrace();
					}
					tempCell.setCellValue("合同日期: "+sdf.format(parse));
				} else if(i == 10) {
					tempCell.setCellValue("负责人: "+c.getProjectLeadNameTo());
				}else if(i== 14){
					tempCell.setCellValue("联系电话: "+c.getPhone());
				}
			}
			// 合并
			sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 4));
			sheet.addMergedRegion(new CellRangeAddress(3, 3, 5, 9));
			sheet.addMergedRegion(new CellRangeAddress(3, 3, 10, 13));
			sheet.addMergedRegion(new CellRangeAddress(3, 3, 14, 17));
			break;
		}
 
 
 
		// 第五行
 
		HSSFRow row4 = sheet.createRow(rowNum++);
		row4.setHeight((short) 500);
		for (int i = 0; i < 18; i++) {
			HSSFCell tempCell = row4.createCell(i);
			tempCell.setCellStyle(headerStyle);
			if (i == 0) {
				tempCell.setCellValue("管理费率 ");
			} else if (i == 3) {
				tempCell.setCellValue("增值税率");
			} else if(i == 6) {
				tempCell.setCellValue("印花税率");
			}else if(i== 9){
				tempCell.setCellValue("资料保证金比率");
			}else{
				tempCell.setCellValue("");
			}
		}
		// 合并
		sheet.addMergedRegion(new CellRangeAddress(4, 4, 0, 2));
		sheet.addMergedRegion(new CellRangeAddress(4, 4, 3, 5));
		sheet.addMergedRegion(new CellRangeAddress(4, 4, 6, 8));
		sheet.addMergedRegion(new CellRangeAddress(4, 4, 9, 11));
		sheet.addMergedRegion(new CellRangeAddress(4, 4, 12, 17));
 
 
 
 
 
 
		// 第六行
		for (collectionAndPaymentListBean c :  getselectTo) {
			HSSFRow row5 = sheet.createRow(rowNum++);
			row5.setHeight((short) 500);
			for (int i = 0; i < 18; i++) {
				HSSFCell tempCell = row5.createCell(i);
				tempCell.setCellStyle(headerStyle);
				if (i == 0) {
					tempCell.setCellValue(c.getManagement());
				} else if (i == 3) {
					tempCell.setCellValue(c.getTaxes());
				} else if(i == 6) {
					tempCell.setCellValue(c.getDuty());
				}else if(i== 9){
					tempCell.setCellValue(c.getDatasets());
				}else{
					tempCell.setCellValue("");
				}
			}
			// 合并
			sheet.addMergedRegion(new CellRangeAddress(5, 5, 0, 2));
			sheet.addMergedRegion(new CellRangeAddress(5, 5, 3, 5));
			sheet.addMergedRegion(new CellRangeAddress(5, 5, 6, 8));
			sheet.addMergedRegion(new CellRangeAddress(5, 5, 9, 11));
			sheet.addMergedRegion(new CellRangeAddress(5, 5, 12, 17));
			break;
		}
 
		// 第七行  分割
		rowNum++;
 
		// 第八行
		HSSFRow row7 = sheet.createRow(rowNum++);
		row7.setHeight((short) 500);
		for (int i = 0; i < 18; i++) {
			HSSFCell tempCell = row7.createCell(i);
			tempCell.setCellStyle(headerStyle);
			if (i == 0) {
				tempCell.setCellValue("序号 ");
			}else if (i == 1) {
				tempCell.setCellValue("'收款日期");
			}else if(i == 2) {
				tempCell.setCellValue("发票金额");
			}else if(i== 3){
				tempCell.setCellValue("业主拨款");
			}else if(i== 4){
				tempCell.setCellValue("管理费");
			}else if(i== 5){
				tempCell.setCellValue("税费");
			}else if(i== 6){
				tempCell.setCellValue("资料保证金");
			}else if(i== 7){
				tempCell.setCellValue("通讯费");
			}else if(i== 8){
				tempCell.setCellValue("交通费");
			}else if(i== 9){
				tempCell.setCellValue("办公费");
			}else if(i== 10){
				tempCell.setCellValue("保险费");
			}else if(i== 11){
				tempCell.setCellValue("差旅费");
			}else if(i== 12){
				tempCell.setCellValue("利息");
			}else if(i== 13){
				tempCell.setCellValue("履约保证金");
			}else if(i== 14){
				tempCell.setCellValue("保理手续费");
			}else if(i== 15){
				tempCell.setCellValue("工资社保公积金");
			}else if(i== 16){
				tempCell.setCellValue("扣款合计");
			}else if(i== 17){
				tempCell.setCellValue("实付款");
			}
 
		}
 
 
 
		
		
		for (collectionAndPaymentListBean c :  getselectTo) {
					HSSFRow tempRow = sheet.createRow(rowNum++);
					tempRow.setHeight((short) 500);
					// 循环单元格填入数据
					for (int j = 0; j < 18; j++) {
						HSSFCell tempCell = tempRow.createCell(j);
						tempCell.setCellStyle(contentStyle);
						String tempValue = null;
						if (j == 0) {
							//序号
							tempValue = c.getIsNewRecord();
						} else if (j == 1) {
							// 收款日期
							if(null == c.getReceiveBillDate()){
								tempValue = "";
							}else{
								tempValue = c.getReceiveBillDate().substring(0,10);
							}
						} else if (j == 2) {
							// 发票金额
							if(null == c.getAmount()){
								tempValue = "0.00";
							}else{
								tempValue = c.getAmount()+"";
							}
						} else if (j == 3) {
							// 业主拨款
							if(null == c.getHomeowners()){
								tempValue = "0.00";
							}else{
								tempValue = c.getHomeowners()+"";
							}
						} else if (j == 4) {
							// 管理费
							if(null == c.getCoffee()){
								tempValue = "0.00";
							}else{
								tempValue = c.getCoffee()+"";
							}
							
						}else if (j == 5) {
							// 税费
							if(null == c.getVorhanden()){
								tempValue = "0.00";
							}else{
								tempValue = c.getVorhanden()+"";
							}
						} else if(j == 6){
							// 资料保证金
							if(null == c.getSecurity()){
								tempValue = "0.00";
							}else{
								tempValue = c.getSecurity()+"";
							}
						} else if(j == 7){
							//通讯费
							if(null == c.getCommunications()){
								tempValue = "0.00";
							}else{
								tempValue = c.getCommunications()+"";
							}
						} else if(j == 8){
							// 交通费
							if(null == c.getTransportation()){
								tempValue = "0.00";
							}else{
								tempValue = c.getTransportation()+"";
							}
						} else if(j == 9){
							// 办公费
							if(null == c.getWork()){
								tempValue = "0.00";
							}else{
								tempValue = c.getWork()+"";
							}
						} else if(j == 10){
							// 保险费
							if(null == c.getSetAInsPremium()){
								tempValue = "0.00";
							}else{
								tempValue = c.getSetAInsPremium()+"";
							}
						} else if(j == 11){
							// 差旅费
							if(null == c.getResearchTravel()){
								tempValue = "0.00";
							}else{
								tempValue = c.getResearchTravel()+"";
							}
						} else if(j == 12){
							// 利息
							if(null == c.getAccrualAmount()){
								tempValue = "0.00";
							}else{
								tempValue = c.getAccrualAmount()+"";
							}
						} else if(j == 13){
							// 履约保证金
							if(null == c.getPerformance()){
								tempValue = "0.00";
							}else{
								tempValue = c.getPerformance()+"";
							}
						} else if(j == 14){
							// 保理手续费
							if(null == c.getFactoring()){
								tempValue = "0.00";
							}else{
								tempValue = c.getFactoring()+"";
							}
						} else if(j == 15){
							// 工资社保公积金
							if(null == c.getCoffeehous()){
								tempValue = "0.00";
							}else{
								tempValue = c.getCoffeehous()+"";
							}
						} else if(j == 16){
							// 扣款合计
							if(null == c.getOfdeductions()){
								tempValue = "0.00";
							}else{
								tempValue = c.getOfdeductions()+"";
							}
						} else if(j == 17){
							// 实付款
							if(null == c.getPaymentImpl()){
								tempValue = "0.00";
							}else{
								tempValue = c.getPaymentImpl()+"";
							}
						} else if(j == 18){
							// 备注
							tempValue = "0.00";
						}
						
						tempCell.setCellValue(tempValue);
					}
				}
		
		try {
			//调用下载     导出
			downloadExcel(response,wb ,fileName+".xls");
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
 
	//下载
	public static void downloadExcel(HttpServletResponse response,HSSFWorkbook wb ,String fileName) throws IOException {
		response.reset();
		response.setContentType("application/vnd.ms-excel;charset=utf-8");
		response.setHeader("Content-Disposition", "attachment;filename="+ new String((fileName).getBytes(), "iso-8859-1"));
		OutputStream out = null;
		try {
			out = response.getOutputStream();
			wb.write(out);
			out.flush();
		} catch (IOException e) {
			e.printStackTrace();
		}finally {
			out.close();
 
		}
 
	}
 
 
 
	/**
	 * 创建标题样式
	 * @param wb
	 * @return
	 */
	private static HSSFCellStyle createTitleCellStyle(HSSFWorkbook wb) {
		HSSFCellStyle cellStyle = wb.createCellStyle();
		cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中  
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直对齐
		cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); 
		cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());//背景颜色
 
		HSSFFont headerFont1 = (HSSFFont) wb.createFont(); // 创建字体样式  
		headerFont1.setBold(true); //字体加粗 
		headerFont1.setFontName("黑体"); // 设置字体类型  
		headerFont1.setFontHeightInPoints((short) 15); // 设置字体大小  
		cellStyle.setFont(headerFont1); // 为标题样式设置字体样式 
 
		return cellStyle;
	}
 
 
	/**
	 * 创建表头样式
	 * @param wb
	 * @return
	 */
	private static HSSFCellStyle createHeadCellStyle(HSSFWorkbook wb) {
		HSSFCellStyle cellStyle = wb.createCellStyle();
		cellStyle.setWrapText(true);// 设置自动换行  
		cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//背景颜色
		cellStyle.setAlignment(HorizontalAlignment.LEFT); //水平居中   
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //垂直对齐
		cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); 
		cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);  
		cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
		cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
		cellStyle.setBorderRight(BorderStyle.THIN); //右边框
		cellStyle.setBorderTop(BorderStyle.THIN); //上边框
 
		HSSFFont headerFont = (HSSFFont) wb.createFont(); // 创建字体样式  
		headerFont.setBold(true); //字体加粗  
		headerFont.setFontName("黑体"); // 设置字体类型  
		headerFont.setFontHeightInPoints((short) 10); // 设置字体大小  
		cellStyle.setFont(headerFont); // 为标题样式设置字体样式  
 
		return cellStyle;
	}
 
 
 
	/**
	 * 创建内容样式
	 * @param wb
	 * @return
	 */
	private static HSSFCellStyle createContentCellStyle(HSSFWorkbook wb) {
		HSSFCellStyle cellStyle = wb.createCellStyle();
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
		cellStyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中
		cellStyle.setWrapText(true);// 设置自动换行  
		cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
		cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
		cellStyle.setBorderRight(BorderStyle.THIN); //右边框
		cellStyle.setBorderTop(BorderStyle.THIN); //上边框
 
		// 生成12号字体
		HSSFFont font = wb.createFont();
		font.setColor((short)8);
		font.setFontHeightInPoints((short) 12);
		cellStyle.setFont(font);
 
		return cellStyle;
	}
 
 
}
posted @   luckyangg  阅读(1788)  评论(0)  编辑  收藏  举报
编辑推荐：
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识（上）
· 浏览器原生「磁吸」效果！Anchor Positioning 锚点定位神器解析
阅读排行：
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· AI与.NET技术实操系列（二）：开始使用ML.NET
· .NET10 - 预览版1新功能体验（一）
点击右上角即可分享
微信分享提示
AI FOR CODE 大赛