家具公司网站源码,工程造价招聘网最新招聘,网站域名属于哪里管,嘉峪关建设局网站1.需求背景: ①需要从第三方获取数据,第三方接口有两个参数,开始时间和结束时间
②获取回来的数据并没有入库,所以不能通过数据库将数据归类统计,excel合并大概的流程是判断上一行或者左右相邻列是否相同,然后进行合并,所以不能是零散的数据且客户要求每一个自治区和每一个航站…1.需求背景: ①需要从第三方获取数据,第三方接口有两个参数,开始时间和结束时间
②获取回来的数据并没有入库,所以不能通过数据库将数据归类统计,excel合并大概的流程是判断上一行或者左右相邻列是否相同,然后进行合并,所以不能是零散的数据且客户要求每一个自治区和每一个航站要统计总数(后续会出一个数据整合文章),咱们默认数据已经整理好了.效果如下: ③最终效果: 2.初步实现: ①利用easyExcel模板填充,实现效果如下图
代码:
//模板位置InputStream template new PathMatchingResourcePatternResolver().getResource(templates/飞机扑救火场统计表.xlsx).getInputStream();response.setContentType(application/octet-stream);response.setCharacterEncoding(utf-8);// 这里URLEncoder.encode可以防止中文乱码response.setHeader(Content-Disposition,attachment;filename java.net.URLEncoder.encode(飞机扑救火场统计表.xlsx, UTF-8));//ExcelWriter该对象用于通过POI将值写入ExcelExcelWriter excelWriter EasyExcel.write(response.getOutputStream()).withTemplate(template).build();//构建excel的sheet WriteSheet writeSheet EasyExcel.writerSheet().build();MapString, String fileData new HashMap();fileData.put(beginDate, beginDate);fileData.put(endDate, endDate);excelWriter.fill(list, writeSheet);excelWriter.fill(fileData, writeSheet);excelWriter.finish();
模板: 3.列合并 列合并工具类,合并代码在afterCellDispose这个方法中,不管是列合并还是行合并其实是重写这个方法,将你的合并逻辑写在里面就可以
//列合并工具类
public class ExcelFillCellMergePrevColUtils implements CellWriteHandler {private static final String KEY %s-%s;//所有的合并信息都存在了这个map里面MapString, Integer mergeInfo new HashMap();public ExcelFillCellMergePrevColUtils() {}Overridepublic void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {}Overridepublic void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {}Overridepublic void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {}Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, ListCellData list, Cell cell, Head head, Integer integer, Boolean aBoolean) {//当前行int curRowIndex cell.getRowIndex();//当前列int curColIndex cell.getColumnIndex();Integer num mergeInfo.get(String.format(KEY, curRowIndex, curColIndex));if(null ! num){// 合并最后一行 ,列mergeWithPrevCol(writeSheetHolder, cell, curRowIndex, curColIndex,num);}}public void mergeWithPrevCol(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex, int num) {Sheet sheet writeSheetHolder.getSheet();CellRangeAddress cellRangeAddress new CellRangeAddress(curRowIndex, curRowIndex, curColIndex, curColIndex num);sheet.addMergedRegion(cellRangeAddress);}//num从第几列开始增加多少列,(6,2,7)代表的意思就是第6行的第2列至第27也就是9列开始合并public void add (int curRowIndex, int curColIndex , int num){mergeInfo.put(String.format(KEY, curRowIndex, curColIndex),num);}}可以参考下面的这个excel看一下,广西壮族自治区的航站合计是从第8行,第2列开始2列的范围合并
列合并效果图: 4.行合并 行合并工具类初级版本:
报错位置ExcelFillCellMergeStrategyUtils合并策略类的 mergeWithPrevRow()方法中
这一行代码会报空指针异常 java.lang.NullPointerException
Row preRow cell.getSheet().getRow(curRowIndex - 1);原因:
debug发现cell.getSheet() 行的下标第0到42的数据行获取的是同一个 sheet 实例
当下标为43时执行cell.getSheet()获取到的 sheet 实例不一样
而下标0到42的行数据被存储到 存储sheet中。如果上一行为空则去缓存中获取上一行,
writeSheetHolder.getCachedSheet()Row preRow cell.getSheet().getRow(curRowIndex - 1);if (preRow null) {// 当获取不到上一行数据时使用缓存sheet中数据preRow writeSheetHolder.getCachedSheet().getRow(curRowIndex - 1);}Cell preCellpreRow.getCell(curColIndex);
行合并工具类最终版:
public class ExcelFillCellMergeStrategyUtils implements CellWriteHandler {/*** 合并字段的下标*/private int[] mergeColumnIndex;/*** 合并几行*/private int mergeRowIndex;public ExcelFillCellMergeStrategyUtils(int mergeRowIndex, int[] mergeColumnIndex) {this.mergeRowIndex mergeRowIndex;this.mergeColumnIndex mergeColumnIndex;}Overridepublic void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,Head head, Integer integer, Integer integer1, Boolean aBoolean) {}Overridepublic void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,Head head, Integer integer, Boolean aBoolean) {}Overridepublic void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {}Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,ListCellData list, Cell cell, Head head, Integer integer, Boolean aBoolean) {//当前行int curRowIndex cell.getRowIndex();//当前列int curColIndex cell.getColumnIndex();if (curRowIndex mergeRowIndex) {for (int i 0; i mergeColumnIndex.length; i) {if (curColIndex mergeColumnIndex[i]) {mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);break;}}}}private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {//获取当前行的当前列的数据和上一行的当前列列数据通过上一行数据是否相同进行合并Object curData cell.getCellTypeEnum() CellType.STRING ? cell.getStringCellValue() :cell.getNumericCellValue();Row preRow cell.getSheet().getRow(curRowIndex - 1);if (preRow null) {// 当获取不到上一行数据时使用缓存sheet中数据preRow writeSheetHolder.getCachedSheet().getRow(curRowIndex - 1);}Cell preCellpreRow.getCell(curColIndex);Object preData preCell.getCellTypeEnum() CellType.STRING ? preCell.getStringCellValue() :preCell.getNumericCellValue();// 比较当前行的第一列的单元格与上一行是否相同相同合并当前单元格与上一行if (curData.equals(preData)) {Sheet sheet writeSheetHolder.getSheet();ListCellRangeAddress mergeRegions sheet.getMergedRegions();boolean isMerged false;for (int i 0; i mergeRegions.size() !isMerged; i) {CellRangeAddress cellRangeAddr mergeRegions.get(i);// 若上一个单元格已经被合并则先移出原有的合并单元再重新添加合并单元if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {sheet.removeMergedRegion(i);cellRangeAddr.setLastRow(curRowIndex);sheet.addMergedRegion(cellRangeAddr);isMerged true;}}// 若上一个单元格未被合并则新增合并单元if (!isMerged) {CellRangeAddress cellRangeAddress new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex,curColIndex);sheet.addMergedRegion(cellRangeAddress);}}}
}5.excel字体样式内容居中 样式工具类:
public class CellStyleStrategy extends AbstractCellStyleStrategy {private WriteCellStyle headWriteCellStyle;private ListWriteCellStyle contentWriteCellStyleList;private CellStyle headCellStyle;private ListCellStyle contentCellStyleList;public CellStyleStrategy(WriteCellStyle headWriteCellStyle,ListWriteCellStyle contentWriteCellStyleList) {this.headWriteCellStyle headWriteCellStyle;this.contentWriteCellStyleList contentWriteCellStyleList;}public CellStyleStrategy(WriteCellStyle headWriteCellStyle, WriteCellStyle contentWriteCellStyle) {this.headWriteCellStyle headWriteCellStyle;contentWriteCellStyleList new ArrayListWriteCellStyle();contentWriteCellStyleList.add(contentWriteCellStyle);}Overrideprotected void initCellStyle(Workbook workbook) {if (headWriteCellStyle ! null) {headCellStyle StyleUtil.buildHeadCellStyle(workbook, headWriteCellStyle);}if (contentWriteCellStyleList ! null !contentWriteCellStyleList.isEmpty()) {contentCellStyleList new ArrayListCellStyle();for (WriteCellStyle writeCellStyle : contentWriteCellStyleList) {contentCellStyleList.add(StyleUtil.buildContentCellStyle(workbook, writeCellStyle));}}}Overrideprotected void setHeadCellStyle(Cell cell, Head head, Integer relativeRowIndex) {if (headCellStyle null) {return;}cell.setCellStyle(headCellStyle);}Overrideprotected void setContentCellStyle(Cell cell, Head head, Integer relativeRowIndex) {if (contentCellStyleList null || contentCellStyleList.isEmpty()) {return;}cell.setCellStyle(contentCellStyleList.get(0));}}public CellStyleStrategy horizontalCellStyleStrategyBuilder() {WriteCellStyle headWriteCellStyle new WriteCellStyle();//设置头字体WriteFont headWriteFont new WriteFont();headWriteFont.setFontHeightInPoints((short) 13);headWriteFont.setBold(true);headWriteCellStyle.setWriteFont(headWriteFont);//设置头居中headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);//内容策略WriteCellStyle contentWriteCellStyle new WriteCellStyle();//设置 水平居中contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);//垂直居中contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);return new CellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);}6.将三个工具类初始化后注册后最终代码: public void exportExcel(HttpServletResponse response, String beginDate, String endDate) throws IOException {ExcelFillCellMergePrevColUtils excelFillCellMergePrevColUtils new ExcelFillCellMergePrevColUtils();String terminalTotal 航站合计;String provinceTotal 省区合计;MapString, ProvinceInfo map handlePlaneDownFire(beginDate, endDate);ListPlaneDownFire list new ArrayList();map.forEach((k, v) - {//添加航站合计v.getTerminalInfos().forEach((k1, v1) - {list.addAll(v1.getList());int size list.size();excelFillCellMergePrevColUtils.add(size 3, 2, 2);CommissionInfo terminalCommissionInfoTotal v1.getSum();PlaneDownFire planeDownFire CommissionInfoConvert.INSTANCE.commissionInfo2planeDownFire(terminalCommissionInfoTotal);planeDownFire.setProvincialArea(v.getName()).setMachineNumber(String.valueOf(size)).setFlyingCommission(String.valueOf(size)).setTerminal(v1.getName()).setModel(terminalTotal);list.add(planeDownFire);});int size list.size();excelFillCellMergePrevColUtils.add(size 3, 1, 3);//省区合计CommissionInfo provinceCommissionInfoTotal v.getSum();PlaneDownFire planeDownFire CommissionInfoConvert.INSTANCE.commissionInfo2planeDownFire(provinceCommissionInfoTotal);planeDownFire.setProvincialArea(v.getName()).setTerminal(provinceTotal).setModel(String.valueOf(size));list.add(planeDownFire);});//设置第几列开始合并int[] mergeColumnIndex {0, 1, 2, 3};//设置第几行开始合并int mergeRowIndex 3;ExcelFillCellMergeStrategyUtils excelFillCellMergeStrategyUtils new ExcelFillCellMergeStrategyUtils(mergeRowIndex, mergeColumnIndex);InputStream template new PathMatchingResourcePatternResolver().getResource(templates/飞机扑救火场统计表.xlsx).getInputStream();response.setContentType(application/octet-stream);response.setCharacterEncoding(utf-8);// 这里URLEncoder.encode可以防止中文乱码response.setHeader(Content-Disposition,attachment;filename java.net.URLEncoder.encode(飞机扑救火场统计表.xlsx, UTF-8));//ExcelWriter该对象用于通过POI将值写入ExcelExcelWriter excelWriter EasyExcel.write(response.getOutputStream()).withTemplate(template)//样式注册.registerWriteHandler(horizontalCellStyleStrategyBuilder())//行注册.registerWriteHandler(excelFillCellMergeStrategyUtils)//列注册.registerWriteHandler(excelFillCellMergePrevColUtils).build();//构建excel的sheetWriteSheet writeSheet EasyExcel.writerSheet().build();MapString, String fileData new HashMap();fileData.put(beginDate, beginDate);fileData.put(endDate, endDate);excelWriter.fill(fileData, writeSheet);excelWriter.fill(list, writeSheet);excelWriter.finish();}总结:EasyExcel动态导出几乎能够满足大部分需求,说到底还是实现CellWriteHandler 类里面的
