粘贴一段自己写的，但都看不懂了代码吧，纪念那几个夜深人静的夜，poi导出多级表头

桃扇骨 2022-02-24 02:00 182阅读 0赞

package com.cf.controller;
    
    import java.io.File;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.net.URLEncoder;
    import java.text.SimpleDateFormat;
    import java.util.Arrays;
    import java.util.Calendar;
    import java.util.Date;
    import java.util.LinkedList;
    import java.util.List;
    import java.util.Map;
    
    import javax.servlet.ServletOutputStream;
    import javax.servlet.http.HttpServletResponse;
    
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.hssf.usermodel.HSSFFont;
    import org.apache.poi.hssf.util.HSSFColor;
    import org.apache.poi.ss.usermodel.CellStyle;
    import org.apache.poi.ss.usermodel.Font;
    import org.apache.poi.ss.util.CellRangeAddress;
    import org.apache.poi.xssf.usermodel.XSSFCell;
    import org.apache.poi.xssf.usermodel.XSSFRow;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Controller;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RequestParam;
    
    import com.cf.service.AirInputService;
    import com.cf.service.ElectricInputService;
    import com.cf.service.WaterInputService;
    
    @Controller
    public class ExportData {
    	@Autowired
    	private ElectricInputService electricInputService;
    	@Autowired
    	private WaterInputService waterInputService;
    	@Autowired
    	private AirInputService airInputService;
    	@RequestMapping("/exportData")
    	public void exportData(@RequestParam Map<String,Object> params,HttpServletResponse response){
    		ServletOutputStream outputStream = null;
    		try {
    			response.setContentType("application/force-download;charset=utf-8");
    			response.setHeader("Content-Disposition", "attachment;fileName="+URLEncoder.encode(params.get("fileName").toString()+"数据导出", "utf-8")+".xlsx");
    			outputStream = response.getOutputStream();
    			
    		} catch (IOException e1) {
    			e1.printStackTrace();
    		}
    		String starttime = params.get("starttime").toString();
    		String starttomonth = starttime.substring(0,7);
    		String endtime = params.get("endtime").toString();
    		String endtomonth = endtime.substring(0,7);
    		SimpleDateFormat dayformat = new SimpleDateFormat("yyyy-MM-dd");
    		SimpleDateFormat monthformat = new SimpleDateFormat("yyyy-MM");
    		List<String> sheetList = new LinkedList<>();
    		List<List<String>> dayList = new LinkedList<>();
    		try {
    			Calendar startca = Calendar.getInstance();
    			startca.setTime(monthformat.parse(starttomonth));//开始时间如2019-01
    			Calendar endca = Calendar.getInstance();
    			endca.setTime(monthformat.parse(endtomonth));//开始时间如2019-01
    			while(true) {
    				String sheetstr = startca.get(Calendar.YEAR)+"年"+(startca.get(Calendar.MONTH)+1)+"月";
    				sheetList.add(sheetstr);
    				List<String> lis = new LinkedList<>();//添加日期的list，空集合
    				dayList.add(lis);
    				if(startca.equals(endca)) {
    					break;
    				}
    				startca.add(Calendar.MONTH, 1);
    			}
    			Calendar startdayca = Calendar.getInstance();
    			startdayca.setTime(dayformat.parse(starttime));//开始时间如2019-01-01
    			Calendar enddayca = Calendar.getInstance();
    			enddayca.setTime(dayformat.parse(endtime));
    			while(true) {
    				String indexStr = startdayca.get(Calendar.YEAR)+"年"+(startdayca.get(Calendar.MONTH)+1)+"月";
    				List<String> list = dayList.get(sheetList.indexOf(indexStr));
    				list.add(startdayca.get(Calendar.YEAR)+"年"+(startdayca.get(Calendar.MONTH)+1)+"月"+startdayca.get(Calendar.DAY_OF_MONTH)+"日");
    				if(startdayca.equals(enddayca)) {
    					break;
    				}
    				startdayca.add(Calendar.DAY_OF_MONTH, 1);
    			}
    			//创建表格
    			XSSFWorkbook workbook = new XSSFWorkbook();
    	    	//表头样式
    	        CellStyle titleStyle = workbook.createCellStyle();
    	        titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    	        Font titleFont = workbook.createFont();
    	        titleFont.setFontHeightInPoints((short) 20);
    	        titleFont.setBoldweight((short) 700);
    	        titleStyle.setFont(titleFont);
    	        // 列头样式
    	        CellStyle headerStyle = workbook.createCellStyle();
    	        headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    	        headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    	        headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    	        headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
    	        headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
    	        headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    	        Font headerFont = workbook.createFont();
    	        headerFont.setFontHeightInPoints((short) 12);
    	        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    	        headerStyle.setFont(headerFont);
    	        
    	        // 单元格样式
    	        CellStyle cellStyle = workbook.createCellStyle();
    	        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    	        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    	        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    	        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
    	        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
    	        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    	        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    	        cellStyle.setFillForegroundColor(HSSFColor.WHITE.index);
    	        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    	        Font cellFont = workbook.createFont();
    	        cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
    	        cellStyle.setFont(cellFont);
    	        
    	    	String[][] dianOpt = {
       {"表号","倍率","表字","用量","产量","单张能耗"},{"表字","水量"},{"表字","气量"}};
    	    	String[][] dianProxyOpt = {
       {"eno","erate","edata","eval","eoutput","eenergy"},{"wdata","wval"},{"adata","aval"}};
    	    	String[] selectOpt = {"电表","水表","气表"};
    	    	String selectOption = params.get("selectOption").toString();
    			String[] split = selectOption.split(",");
    			int[] checkOpt = new int[split.length];
    			//0电表，1水表，2气表
    			List<String> selectedOpt = new LinkedList<>();
    			for(int i = 0;i < split.length;i++) {
    				int val = Integer.parseInt(split[i]);
    				checkOpt[i] = val;
    				selectedOpt.addAll(Arrays.asList(dianProxyOpt[Integer.parseInt(split[i])]));
    			}
    	    	int firstHe = 0;//第一级合并的宽度
    	    	int leftStartSum = 1;
    	    	for (int i : checkOpt) {//算出第三级的宽度，即第一级的合并宽度
    				firstHe += dianOpt[i].length;
    			}
    	    	for(String sheetStr :sheetList) {
    	    		// 生成一个(带标题)表格
    		        XSSFSheet sheet = workbook.createSheet(sheetStr);
    		        //设置列宽
    		        XSSFRow row = sheet.createRow(0);//第一行显示日期
    		        row.setHeight((short)400);
    		        //创建车间表头
    		        XSSFCell workCell = row.createCell(0);
    		        workCell.setCellStyle(cellStyle);
    		        workCell.setCellValue("车间");
    		        sheet.setColumnWidth(0, 3000);
    		        sheet.addMergedRegion(new CellRangeAddress(0, 2, 0, 0));
    		        int dateLength = dayList.get(sheetList.indexOf(sheetStr)).size();
    		        for(int i = leftStartSum;i < leftStartSum+firstHe*dateLength;i++){
    		        	XSSFCell cell = row.createCell(i);
    		        	cell.setCellStyle(cellStyle);
    		        	if(i % firstHe == leftStartSum){
    		        		try {
    							cell.setCellValue(dayList.get(sheetList.indexOf(sheetStr)).get(i/firstHe));
    						} catch (Exception e) {
    							e.printStackTrace();
    						}
    		        		sheet.addMergedRegion(new CellRangeAddress(0, 0, i, i+firstHe-1));
    		        	}
    		        }
    		        row = sheet.createRow(1);//第二行显示监测选项
    		        row.setHeight((short)400);
    		        for(int i = leftStartSum;i < leftStartSum+firstHe*dateLength;i++){
    		        	XSSFCell cell = row.createCell(i);
    		        	cell.setCellStyle(cellStyle);
    		        }
    		        int sum = leftStartSum;
    		        for(int i = leftStartSum;i < leftStartSum+firstHe*dateLength;i+=firstHe){
    		        	for(int j = 0;j < checkOpt.length;j++){
    		        		XSSFCell cell = row.getCell(sum);
    		        		cell.setCellValue(selectOpt[checkOpt[j]]);
    		        		sheet.addMergedRegion(new CellRangeAddress(1, 1, sum, sum+dianOpt[checkOpt[j]].length-1));
    		        		sum += dianOpt[checkOpt[j]].length;
    		        	}
    		        }
    		        row = sheet.createRow(2);//第三行显示监测选项
    		        XSSFCell createCell = row.createCell(0);
    		        createCell.setCellStyle(cellStyle);//补上车间下面的边框
    		        row.setHeight((short)400);
    		        for(int i = leftStartSum;i < leftStartSum+firstHe*dateLength;i++){
    		        	XSSFCell cell = row.createCell(i);
    		        	cell.setCellStyle(cellStyle);
    		        }
    		        int optCount = leftStartSum;
    		        for(int i = leftStartSum;i < leftStartSum+firstHe*dateLength;i+=firstHe){
    		        	for(int j = 0;j < checkOpt.length;j++){
    		        		for(int s = 0;s < dianOpt[checkOpt[j]].length;s++){
    		        			XSSFCell cell = row.getCell(optCount);
    		        			cell.setCellValue(dianOpt[checkOpt[j]][s]);
    		        			optCount++;
    		        		}
    		        	}
    		        }
    	    	}
    			//创建表格
    			//0电表，1水表，2气表
    	    	int startRow = 3;
    	    	String endtimeaddone = params.get("endtime").toString();
    	    	Date parse = dayformat.parse(endtimeaddone);
    	    	Calendar instance = Calendar.getInstance();
    	    	instance.setTime(parse);
    	    	instance.add(Calendar.DAY_OF_MONTH, 1);
    	    	params.put("endtime", instance.get(Calendar.YEAR)+"-"+(instance.get(Calendar.MONTH)+1)+"-"+instance.get(Calendar.DAY_OF_MONTH));
    			for(int i = 0;i < checkOpt.length;i++) {
    				int val = checkOpt[i];
    				if(val == 0) {//调用查询电表 
    					List<Map<String, Object>> queryData = electricInputService.getExportData(params);
    					for (Map<String, Object> map : queryData) {
    						String createtime = map.get("createtime").toString();
    						Calendar dataca = Calendar.getInstance();
    						dataca.setTime(dayformat.parse(createtime));
    						String yearMonth = dataca.get(Calendar.YEAR)+"年"+(dataca.get(Calendar.MONTH)+1)+"月";
    						String yearMonthDay = yearMonth+dataca.get(Calendar.DAY_OF_MONTH)+"日";
    						XSSFSheet sheet = workbook.getSheet(yearMonth);
    						String workname = map.get("workname").toString();
    						XSSFRow row = null;
    						XSSFCell cell = null;
    						XSSFCell workcell = null;
    						//填充电表号
    						String eno = map.get("no").toString();
    						int indexOfCheckOpt = selectedOpt.indexOf("eno");//属于每组日期的第几个
    						List<String> list = dayList.get(sheetList.indexOf(yearMonth));
    						int indexOfDate = list.indexOf(yearMonthDay);//属于第几组日期
    						boolean flag = false;
    						int lastRowNum = startRow;
    						//确定数据插入第几行，首先遍历全部的行，寻找和车间名所匹配的行（可能会匹配多行），然后查询要插入数据的列存不存在数据，如果不存在，则插入，如果存在，则新建一行，再在此列插入
    						int lastRowNum2 = sheet.getLastRowNum();
    						for(int s = startRow;s <= lastRowNum2;s++) {//确定数据插入第几行
    							row = sheet.getRow(s);
    							if(row != null) {
    								workcell = row.getCell(0);
    								String cellWorkName = workcell.getStringCellValue();
    								if(cellWorkName.equals(workname)) {
    									cell = row.getCell(indexOfDate*selectedOpt.size()+indexOfCheckOpt+leftStartSum);
    									if(cell != null) {
    										lastRowNum = s + 1;
    										flag = true;
    									}else{
    										break;
    									}
    								}else {
    									row = null;
    								}
    							}
    						}
    						if(flag) {
    							row = sheet.createRow(lastRowNum);
    						}
    						if(row == null) {
    							row = sheet.createRow(sheet.getLastRowNum()+1);
    						}
    						workcell = row.getCell(0);
    						workname = map.get("workname").toString();
    						if(workcell == null) {
    							workcell = row.createCell(0);
    							workcell.setCellStyle(cellStyle);
    							workcell.setCellValue(workname);
    						}
    						cell = row.createCell(indexOfDate*selectedOpt.size()+indexOfCheckOpt+leftStartSum);
    						cell.setCellStyle(cellStyle);
    						cell.setCellValue(eno);
    						//填充电表倍率
    						String erate = map.get("rate").toString();
    						indexOfCheckOpt = selectedOpt.indexOf("erate");
    						cell = row.createCell(indexOfDate*selectedOpt.size()+indexOfCheckOpt+leftStartSum);
    						cell.setCellStyle(cellStyle);
    						cell.setCellValue(erate);
    						//填充表字
    						String edata = map.get("data").toString();
    						indexOfCheckOpt = selectedOpt.indexOf("edata");
    						cell = row.createCell(indexOfDate*selectedOpt.size()+indexOfCheckOpt+leftStartSum);
    						cell.setCellStyle(cellStyle);
    						cell.setCellValue(edata);
    						//填充表用量
    						String eval = map.get("useval").toString();
    						indexOfCheckOpt = selectedOpt.indexOf("eval");
    						cell = row.createCell(indexOfDate*selectedOpt.size()+indexOfCheckOpt+leftStartSum);
    						cell.setCellStyle(cellStyle);
    						cell.setCellValue(eval);
    						//填充表产量
    						Object eoutput = map.get("output");
    						indexOfCheckOpt = selectedOpt.indexOf("eoutput");
    						cell = row.createCell(indexOfDate*selectedOpt.size()+indexOfCheckOpt+leftStartSum);
    						cell.setCellStyle(cellStyle);
    						cell.setCellValue(eoutput == null ? "" : eoutput.toString());
    						//填充单行能耗
    						Object eenergy = map.get("energy");
    						indexOfCheckOpt = selectedOpt.indexOf("eenergy");
    						cell = row.createCell(indexOfDate*selectedOpt.size()+indexOfCheckOpt+leftStartSum);
    						cell.setCellStyle(cellStyle);
    						cell.setCellValue(eenergy == null ? "" : eenergy.toString());
    					}
    				}
    				if(val == 1) {//调用查询水表 
    					List<Map<String, Object>> queryData = waterInputService.getExportData(params);
    					for (Map<String, Object> map : queryData) {
    						String createtime = map.get("createtime").toString();
    						Calendar dataca = Calendar.getInstance();
    						dataca.setTime(dayformat.parse(createtime));
    						String yearMonth = dataca.get(Calendar.YEAR)+"年"+(dataca.get(Calendar.MONTH)+1)+"月";
    						String yearMonthDay = yearMonth+dataca.get(Calendar.DAY_OF_MONTH)+"日";
    						XSSFSheet sheet = workbook.getSheet(yearMonth);
    						String workname = map.get("workname").toString();
    						XSSFRow row = null;
    						XSSFCell cell = null;
    						XSSFCell workcell = null;
    						//填充电表号
    						String eno = map.get("data").toString();
    						int indexOfCheckOpt = selectedOpt.indexOf("wdata");//属于每组日期的第几个
    						List<String> list = dayList.get(sheetList.indexOf(yearMonth));
    						int indexOfDate = list.indexOf(yearMonthDay);//属于第几组日期
    						boolean flag = false;
    						int lastRowNum = startRow;
    						//确定数据插入第几行，首先遍历全部的行，寻找和车间名所匹配的行（可能会匹配多行），然后查询要插入数据的列存不存在数据，如果不存在，则插入，如果存在，则新建一行，再在此列插入
    						int lastRowNum2 = sheet.getLastRowNum();
    						for(int s = startRow;s <= lastRowNum2;s++) {//确定数据插入第几行
    							row = sheet.getRow(s);
    							if(row != null) {
    								workcell = row.getCell(0);
    								String cellWorkName = workcell.getStringCellValue();
    								if(cellWorkName.equals(workname)) {
    									cell = row.getCell(indexOfDate*selectedOpt.size()+indexOfCheckOpt+leftStartSum);
    									if(cell != null) {//如果在循环中找到了车间名称，则记录此row的下一行，并标记需要插入
    										lastRowNum = s + 1;
    										flag = true;
    									}else{//如果此行的车间等于要插入的车间号，并且cell是个空的，则这个row就是需要插入数据的row，所以break;
    										flag = false;
    										break;
    									}
    								}else {//如果循环走完了，找不到车间号对应的，则加在最后
    									row = null;
    								}
    							}
    						}
    						if(flag) {
    							try {
    								if(row == null){
    									sheet.shiftRows(lastRowNum, sheet.getLastRowNum(), 1,true,true);
    								}
    							} catch (Exception e) {
    								System.out.println("移动失败："+lastRowNum+","+sheet.getLastRowNum());
    							}
    							row = sheet.createRow(lastRowNum);
    						}
    						if(row == null) {
    							row = sheet.createRow(sheet.getLastRowNum()+1);
    						}
    						workcell = row.getCell(0);
    						workname = map.get("workname").toString();
    						if(workcell == null) {
    							workcell = row.createCell(0);
    							workcell.setCellStyle(cellStyle);
    							workcell.setCellValue(workname);
    						}
    						cell = row.createCell(indexOfDate*selectedOpt.size()+indexOfCheckOpt+leftStartSum);
    						cell.setCellStyle(cellStyle);
    						cell.setCellValue(eno);
    						//填充电表倍率
    						String erate = map.get("useval").toString();
    						indexOfCheckOpt = selectedOpt.indexOf("wval");
    						cell = row.createCell(indexOfDate*selectedOpt.size()+indexOfCheckOpt+leftStartSum);
    						cell.setCellStyle(cellStyle);
    						cell.setCellValue(erate);
    					}
    				}
    				if(val == 2) {//调用查询气表 
    					List<Map<String, Object>> queryData = airInputService.getExportData(params);
    					for (Map<String, Object> map : queryData) {
    						String createtime = map.get("createtime").toString();
    						Calendar dataca = Calendar.getInstance();
    						dataca.setTime(dayformat.parse(createtime));
    						String yearMonth = dataca.get(Calendar.YEAR)+"年"+(dataca.get(Calendar.MONTH)+1)+"月";
    						String yearMonthDay = yearMonth+dataca.get(Calendar.DAY_OF_MONTH)+"日";
    						XSSFSheet sheet = workbook.getSheet(yearMonth);
    						String workname = map.get("workname").toString();
    						XSSFRow row = null;
    						XSSFCell cell = null;
    						XSSFCell workcell = null;
    						//填充电表号
    						String eno = map.get("data").toString();
    						int indexOfCheckOpt = selectedOpt.indexOf("adata");//属于每组日期的第几个
    						List<String> list = dayList.get(sheetList.indexOf(yearMonth));
    						int indexOfDate = list.indexOf(yearMonthDay);//属于第几组日期
    						boolean flag = false;
    						int lastRowNum = startRow;
    						//确定数据插入第几行，首先遍历全部的行，寻找和车间名所匹配的行（可能会匹配多行），然后查询要插入数据的列存不存在数据，如果不存在，则插入，如果存在，则新建一行，再在此列插入
    						int lastRowNum2 = sheet.getLastRowNum();
    						for(int s = startRow;s <= lastRowNum2;s++) {//确定数据插入第几行
    							row = sheet.getRow(s);
    							if(row != null) {
    								workcell = row.getCell(0);
    								String cellWorkName = workcell.getStringCellValue();
    								if(cellWorkName.equals(workname)) {
    									cell = row.getCell(indexOfDate*selectedOpt.size()+indexOfCheckOpt+leftStartSum);
    									if(cell != null) {//如果在循环中找到了车间名称，则记录此row的下一行，并标记需要插入
    										lastRowNum = s + 1;
    										flag = true;
    									}else{//如果此行的车间等于要插入的车间号，并且cell是个空的，则这个row就是需要插入数据的row，所以break;
    										flag = false;
    										break;
    									}
    								}else {//如果循环走完了，找不到车间号对应的，则加在最后
    									row = null;
    								}
    							}
    						}
    						if(flag) {
    							try {
    								if(row == null){
    									sheet.shiftRows(lastRowNum, sheet.getLastRowNum(), 1,true,true);
    								}
    							} catch (Exception e) {
    								System.out.println("移动失败："+lastRowNum+","+sheet.getLastRowNum());
    							}
    							row = sheet.createRow(lastRowNum);
    						}
    						if(row == null) {
    							row = sheet.createRow(sheet.getLastRowNum()+1);
    						}
    						workcell = row.getCell(0);
    						workname = map.get("workname").toString();
    						if(workcell == null) {
    							workcell = row.createCell(0);
    							workcell.setCellStyle(cellStyle);
    							workcell.setCellValue(workname);
    						}
    						cell = row.createCell(indexOfDate*selectedOpt.size()+indexOfCheckOpt+leftStartSum);
    						cell.setCellStyle(cellStyle);
    						cell.setCellValue(eno);
    						//填充电表倍率
    						String erate = map.get("useval").toString();
    						indexOfCheckOpt = selectedOpt.indexOf("aval");
    						cell = row.createCell(indexOfDate*selectedOpt.size()+indexOfCheckOpt+leftStartSum);
    						cell.setCellStyle(cellStyle);
    						cell.setCellValue(erate);
    					}
    				}
    			}
    			//数据添加完成之后，循环sheet合并车间
    			for(String sheetName : sheetList){
    				XSSFSheet sheet = workbook.getSheet(sheetName);
    				int startNum = 3;
    				int endNum = 3;
    				String workName = "";
    				for(int i = 3;i <= sheet.getLastRowNum();i++){
    					XSSFRow row = sheet.getRow(i);
    					XSSFCell cell = row.getCell(0);
    					String cellValue = cell.getStringCellValue();
    					if(!cellValue.equals(workName)){
    						if(workName.equals("")){
    							startNum = i;
    						}else{
    							endNum = i - 1;
    							sheet.addMergedRegion(new CellRangeAddress(startNum, endNum, 0, 0));
    							startNum = i;
    						}
    						workName = cellValue;
    					}
    				}
    				if(startNum != sheet.getLastRowNum()+1){
    					sheet.addMergedRegion(new CellRangeAddress(startNum, sheet.getLastRowNum(), 0, 0));
    				}
    			}
    			workbook.write(outputStream);
    			workbook.close();
    		} catch (Exception e) {
    			e.printStackTrace();
    		}
    	}
    }