我是在官方4.4.0源码上修改的，获取需要的源码文件，然后修改：点击下载

本文解决三个问题

（1）模板导出图片不显示（实际上导出来了只是最小化显示了）

（2）fe循环指令存在而合并单元格的时候格式错乱问题

（3）fe存在一对多关系的时候格式错乱问题

嫌看文章麻烦的伙伴们可以直接下载 代码+模板：点击下载

目录

1.加载模板数据导出语句，图片替换成自己的图片，代码种的图你们那里访问不到的

2.获取cn.afterturn.easypoi.excel.ExcelExportUtil

3.修改cn.afterturn.easypoi.excel.export.template.ExcelExportOfTemplateUtil

4.修改cn.afterturn.easypoi.excel.export.base.BaseExportService

5.新建工具类Sd3eUtil

1.加载模板数据导出语句，图片替换成自己的图片，代码种的图你们那里访问不到的

使用xbx();方法

Workbook book = ExcelExportUtil.exportExcel(params, valueXbx);

package cn.afterturn.easypoi.test.excel.template;import java.io.FileOutputStream;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import cn.afterturn.easypoi.entity.ImageEntity;import cn.afterturn.easypoi.easy.excel.ExcelExportUtil;import cn.afterturn.easypoi.excel.entity.TemplateExportParams;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.ss.util.CellRangeAddress;import org.junit.Before;import org.junit.Test;public class ExcelExportTemplateColFeTest {Map<String, Object> value = new HashMap<String, Object>();@Testpublic void xbx() throws Exception {//养护方案全部Map<String, Object> valueXbx = new HashMap<String, Object>();//List<MaintenanceRecordName> listMaintenanceRecordName = new ArrayList<MaintenanceRecordName>();List<Map<String, Object>> listMaintenanceRecordM = new ArrayList<Map<String, Object>>();for(int i=0; i<3; i++){listMaintenanceRecordM.add(new HashMap<String, Object>());listMaintenanceRecordM.get(i).put("maintenanceYear0","-08-10");listMaintenanceRecordM.get(i).put("maintenancePlan0","计划是什么0："+String.valueOf(i));listMaintenanceRecordM.get(i).put("maintenanceYear1","-08-11");listMaintenanceRecordM.get(i).put("maintenancePlan1","计划是什么1："+String.valueOf(i));listMaintenanceRecordM.get(i).put("maintenanceYear2","-08-12");listMaintenanceRecordM.get(i).put("maintenancePlan2","计划是什么2："+String.valueOf(i));}List<Map<String, Object>> listCoreDrillingM = new ArrayList<Map<String, Object>>();ImageEntity imageOverallConditionPicture = new ImageEntity();//整体状况图片ImageEntity imageRoadSurfacePicture = new ImageEntity();//钻芯处路表图片ImageEntity imageCoreSamplesPicture = new ImageEntity();//芯样情况图片ImageEntity imageCorePitPicture = new ImageEntity();//芯坑路况图片for(int i=0; i<3; i++){listCoreDrillingM.add(new HashMap<String, Object>());listCoreDrillingM.get(i).put("name","钻芯取样");listCoreDrillingM.get(i).put("positionMileageCoreName","桩号");listCoreDrillingM.get(i).put("samplingDateCoreName","取样时间");listCoreDrillingM.get(i).put("laneDistributionCoreName","横向位置");for(int k=0;k<3;k++){imageOverallConditionPicture = new ImageEntity("http://172.24.3.222:9000/roadimage//G206+%E6%BD%8D%E5%9D%8A+K308-%E4%B8%8A%E8%A1%8C-1%E8%BD%A6%E9%81%93-0603095007/PAVASSETINFO/Camera00/IMG0003/C00-002960-002959.JPG",800,400);imageRoadSurfacePicture = new ImageEntity("http://172.24.3.222:9000/roadimage//G206+%E6%BD%8D%E5%9D%8A+K308-%E4%B8%8A%E8%A1%8C-1%E8%BD%A6%E9%81%93-0603095007/PAVASSETINFO/Camera00/IMG0003/C00-002960-002959.JPG",800,400);imageCoreSamplesPicture = new ImageEntity("http://172.24.3.222:9000/roadimage//G206+%E6%BD%8D%E5%9D%8A+K308-%E4%B8%8A%E8%A1%8C-1%E8%BD%A6%E9%81%93-0603095007/PAVASSETINFO/Camera00/IMG0003/C00-002960-002959.JPG",800,400);imageCorePitPicture = new ImageEntity("http://172.24.3.222:9000/roadimage//G206+%E6%BD%8D%E5%9D%8A+K308-%E4%B8%8A%E8%A1%8C-1%E8%BD%A6%E9%81%93-0603095007/PAVASSETINFO/Camera00/IMG0003/C00-002960-002959.JPG",800,400);imageOverallConditionPicture.setColspan(4);imageRoadSurfacePicture.setColspan(4);imageCoreSamplesPicture.setColspan(4);imageCorePitPicture.setColspan(4);//imageOverallConditionPicture.setRowspan(2);//imageRoadSurfacePicture.setRowspan(2);//imageCoreSamplesPicture.setRowspan(2);//imageCorePitPicture.setRowspan(2);if (k==2) {imageCorePitPicture.setColspan(6);}listCoreDrillingM.get(i).put("positionMileageCore"+String.valueOf(k),"G105");listCoreDrillingM.get(i).put("samplingDateCore"+String.valueOf(k),"-03-08");listCoreDrillingM.get(i).put("laneDistributionCore"+String.valueOf(k),"横向2");listCoreDrillingM.get(i).put("imageOverallConditionPicture"+String.valueOf(k),imageOverallConditionPicture);listCoreDrillingM.get(i).put("imageRoadSurfacePicture"+String.valueOf(k),imageRoadSurfacePicture);listCoreDrillingM.get(i).put("imageCoreSamplesPicture"+String.valueOf(k),imageCoreSamplesPicture);listCoreDrillingM.get(i).put("imageCorePitPicture"+String.valueOf(k),imageCorePitPicture);}}valueXbx.put("highwayRouteNumber", "");//路线编号valueXbx.put("startingPositionMileage", "");//起始桩号valueXbx.put("endingPositionMileage", "");//终止桩号valueXbx.put("laneDirection", "");//行车方向valueXbx.put("lanePosition", "");//车道位置valueXbx.put("trafficLoadLevel", "");//交通荷载等级valueXbx.put("constructionYear", "");//修建年度valueXbx.put("pavementStructure", "");//结构形式valueXbx.put("roadAge", "");//路龄（年）valueXbx.put("maintenanceYear", "");//养护年度valueXbx.put("maintenancePlan", "");//养护方案valueXbx.put("aadtt", "");//交通荷载AADTTvalueXbx.put("pqi", "");//pqivalueXbx.put("pci", "");//pcivalueXbx.put("rqi", "");//rqivalueXbx.put("rdi", "");//rdivalueXbx.put("pssi", "");//pssivalueXbx.put("pssi", "");//弯沉valueXbx.put("pwi", "");//pwivalueXbx.put("sri", "");//srivalueXbx.put("pbi", "");//pbivalueXbx.put("listMaintenanceRecordM",listMaintenanceRecordM);valueXbx.put("listCoreDrillingM",listCoreDrillingM);ImageEntity image = new ImageEntity("http://172.24.3.222:9000/roadimage//G206+%E6%BD%8D%E5%9D%8A+K308-%E4%B8%8A%E8%A1%8C-1%E8%BD%A6%E9%81%93-0603095007/PAVASSETINFO/Camera00/IMG0003/C00-002960-002959.JPG",2,3);image.setHeight(400);image.setWidth(800);image.setRowspan(4);image.setColspan(2);//image.setUrl("http://172.24.3.222:9000/roadimage//G206+%E6%BD%8D%E5%9D%8A+K308-%E4%B8%8A%E8%A1%8C-1%E8%BD%A6%E9%81%93-0603095007/PAVASSETINFO/Camera00/IMG0003/C00-002960-002959.JPG");valueXbx.put("tp",image);TemplateExportParams params = new TemplateExportParams("doc/xbx1.xlsx");params.setColForEach(true);Workbook book = ExcelExportUtil.exportExcel(params, valueXbx);int listMaintenanceRecordMStartRow=3;CellRangeAddress regionMaintenanceRecord = new CellRangeAddress(listMaintenanceRecordMStartRow, listMaintenanceRecordMStartRow+listMaintenanceRecordM.size()-1, 0, 2);book.getSheetAt(0).addMergedRegion(regionMaintenanceRecord);book.getSheetAt(0).getRow(13).setHeightInPoints(135);book.getSheetAt(0).getRow(17).setHeightInPoints(135);book.getSheetAt(0).getRow(21).setHeightInPoints(135);//book.getSheetAt(0).getRow(14).setHeightInPoints(0);//book.getSheetAt(0).getRow(19).setHeightInPoints(0);//book.getSheetAt(0).getRow(23).setHeightInPoints(0);//PoiMergeCellUtil.mergeCells(book.getSheetAt(0), 1, 0,1);FileOutputStream fos = new FileOutputStream("D:/home/excel/xbx.xlsx");book.write(fos);fos.close();}@Testpublic void one() throws Exception {TemplateExportParams params = new TemplateExportParams("doc/for_Col.xlsx");params.setColForEach(true);Workbook book = ExcelExportUtil.exportExcel(params, value);//PoiMergeCellUtil.mergeCells(book.getSheetAt(0), 1, 0,1);FileOutputStream fos = new FileOutputStream("D:/home/excel/ExcelExportTemplateColFeTest_one.xlsx");book.write(fos);fos.close();}@Testpublic void two() throws Exception {TemplateExportParams params = new TemplateExportParams("doc/for_Col.xlsx", 1);params.setColForEach(true);Workbook book = ExcelExportUtil.exportExcel(params, value);FileOutputStream fos = new FileOutputStream("D:/home/excel/ExcelExportTemplateColFeTest_two.xlsx");book.write(fos);fos.close();}@Beforepublic void testBefore() {List<Map<String, Object>> colList = new ArrayList<Map<String, Object>>();//先处理表头Map<String, Object> map = new HashMap<String, Object>();map.put("name", "小明挑战");map.put("zq", "正确");map.put("cw", "错误");map.put("tj", "统计");map.put("zqmk", "t.zq_xm");map.put("cwmk", "t.cw_xm");map.put("tjmk", "t.tj_xm");colList.add(map);map = new HashMap<String, Object>();map.put("name", "小红挑战");map.put("zq", "正确");map.put("cw", "错误");map.put("tj", "统计");map.put("zqmk", "n:t.zq_xh");map.put("cwmk", "n:t.cw_xh");map.put("tjmk", "n:t.tj_xh");colList.add(map);value.put("colList", colList);List<Map<String, Object>> valList = new ArrayList<Map<String, Object>>();map = new HashMap<String, Object>();map.put("one", "运动");map.put("two", "跑步");map.put("zq_xm", 1);map.put("cw_xm", 2);map.put("tj_xm", 3);map.put("zq_xh", 4);map.put("cw_xh", 2);map.put("tj_xh", 6);valList.add(map);map = new HashMap<String, Object>();map.put("one", "运动");map.put("two", "跳高");map.put("zq_xm", 1);map.put("cw_xm", 2);map.put("tj_xm", 3);map.put("zq_xh", 4);map.put("cw_xh", 2);map.put("tj_xh", 6);valList.add(map);map = new HashMap<String, Object>();map.put("one", "文化");map.put("two", "数学");map.put("zq_xm", 1);map.put("cw_xm", 2);map.put("tj_xm", 3);map.put("zq_xh", 4);map.put("cw_xh", 2);map.put("tj_xh", 6);valList.add(map);value.put("valList", valList);}}

2.获取cn.afterturn.easypoi.excel.ExcelExportUtil

获取后无需修改代码，但是需要引用修改后的ExcelExportOfTemplateUtil.java

package com.ruoyi.framework.easypoi.excel;/*** @version v1.0* @ProjectName: road* @ClassName: ExcelExportUtil* @Description: 4.4.0 版本进行修改* @Author: xbx* @Date: /3/4 10:59* 徐本锡 mod by xbx .03.07 调用这个类加载模板导出 解决格式错乱问题*/import cn.afterturn.easypoi.excel.entity.ExportParams;import cn.afterturn.easypoi.excel.entity.TemplateExportParams;import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;import cn.afterturn.easypoi.excel.export.ExcelBatchExportService;import cn.afterturn.easypoi.excel.export.ExcelExportService;//import cn.afterturn.easypoi.excel.export.template.ExcelExportOfTemplateUtil;import cn.afterturn.easypoi.handler.inter.IExcelExportServer;import cn.afterturn.easypoi.handler.inter.IWriter;import com.ruoyi.framework.easypoi.excel.export.template.ExcelExportOfTemplateUtil;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.streaming.SXSSFWorkbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.util.Collection;import java.util.List;import java.util.Map;/*** excel 导出工具类** @author JueYue* @version 1.0* -10-17*/public final class ExcelExportUtil {public static int USE_SXSSF_LIMIT = 1000000;public static final String SHEET_NAME= "sheetName";private ExcelExportUtil() {}/*** 大数据量导出** @param entity 表格标题属性* @param pojoClass Excel对象Class*/public static IWriter<Workbook> exportBigExcel(ExportParams entity, Class<?> pojoClass) {ExcelBatchExportService batchServer = new ExcelBatchExportService();batchServer.init(entity, pojoClass);return batchServer;}/*** 大数据量导出** @param entity* @param excelParams* @return*/public static IWriter<Workbook> exportBigExcel(ExportParams entity, List<ExcelExportEntity> excelParams) {ExcelBatchExportService batchServer = new ExcelBatchExportService();batchServer.init(entity, excelParams);return batchServer;}/*** 大数据量导出** @param entity表格标题属性* @param pojoClass Excel对象Class* @param server查询数据的接口* @param queryParams 查询数据的参数*/public static Workbook exportBigExcel(ExportParams entity, Class<?> pojoClass,IExcelExportServer server, Object queryParams) {ExcelBatchExportService batchServer = new ExcelBatchExportService();batchServer.init(entity, pojoClass);return batchServer.exportBigExcel(server, queryParams);}/*** 大数据量导出** @param entity* @param excelParams* @param server查询数据的接口* @param queryParams 查询数据的参数* @return*/public static Workbook exportBigExcel(ExportParams entity, List<ExcelExportEntity> excelParams,IExcelExportServer server, Object queryParams) {ExcelBatchExportService batchServer = new ExcelBatchExportService();batchServer.init(entity, excelParams);return batchServer.exportBigExcel(server, queryParams);}/*** @param entity 表格标题属性* @param pojoClass Excel对象Class* @param dataSet Excel对象数据List*/public static Workbook exportExcel(ExportParams entity, Class<?> pojoClass,Collection<?> dataSet) {Workbook workbook = getWorkbook(entity.getType(), dataSet.size());new ExcelExportService().createSheet(workbook, entity, pojoClass, dataSet);return workbook;}private static Workbook getWorkbook(ExcelType type, int size) {if (ExcelType.HSSF.equals(type)) {return new HSSFWorkbook();} else {return new XSSFWorkbook();}}/*** 根据Map创建对应的Excel** @param entity表格标题属性* @param entityList Map对象列表* @param dataSet Excel对象数据List*/public static Workbook exportExcel(ExportParams entity, List<ExcelExportEntity> entityList,Collection<?> dataSet) {Workbook workbook = getWorkbook(entity.getType(), dataSet.size());new ExcelExportService().createSheetForMap(workbook, entity, entityList, dataSet);return workbook;}/*** 根据Map创建对应的Excel(一个excel 创建多个sheet)** @param list 多个Map key title 对应表格Title key entity 对应表格对应实体 key data* Collection 数据* @return*/public static Workbook exportExcel(List<Map<String, Object>> list, ExcelType type) {Workbook workbook = getWorkbook(type, 0);for (Map<String, Object> map : list) {ExcelExportService service = new ExcelExportService();ExportParams params = (ExportParams) map.get("title");params.setType(type);service.createSheet(workbook,params,(Class<?>) map.get("entity"), (Collection<?>) map.get("data"));}return workbook;}/*** 导出文件通过模板解析,不推荐这个了,推荐全部通过模板来执行处理** @param params 导出参数类* @param pojoClass 对应实体* @param dataSet 实体集合* @param map 模板集合* @return*/@Deprecatedpublic static Workbook exportExcel(TemplateExportParams params, Class<?> pojoClass,Collection<?> dataSet, Map<String, Object> map) {return new ExcelExportOfTemplateUtil().createExcelByTemplate(params, pojoClass, dataSet,map);}/*** 导出文件通过模板解析只有模板,没有集合** @param params 导出参数类* @param map 模板集合* @return*/public static Workbook exportExcel(TemplateExportParams params, Map<String, Object> map) {return new ExcelExportOfTemplateUtil().createExcelByTemplate(params, null, null, map);}/*** 导出文件通过模板解析只有模板,没有集合* 每个sheet对应一个map,导出到处,key是sheet的NUM** @param params 导出参数类* @param map 模板集合* @return*/public static Workbook exportExcel(Map<Integer, Map<String, Object>> map,TemplateExportParams params) {return new ExcelExportOfTemplateUtil().createExcelByTemplate(params, map);}/*** 导出文件通过模板解析只有模板,没有集合* 每个sheet对应一个list,按照数量进行导出排序,key是sheet的NUM** @param params 导出参数类* @param map 模板集合* @return*/public static Workbook exportExcelClone(Map<Integer, List<Map<String, Object>>> map,TemplateExportParams params) {return new ExcelExportOfTemplateUtil().createExcelCloneByTemplate(params, map);}}

3.修改cn.afterturn.easypoi.excel.export.template.ExcelExportOfTemplateUtil

package cn.afterturn.easypoi.easy.excel.export.template;/*** @version v1.0* @ProjectName: road* @ClassName: ExcelExportOfTemplateUtil* @Description: 4.4.0 版本进行修改* @Author: xbx* @Date: /3/4 10:56* addListDataToExcel(Cell, Map<String, Object>, String);* foreachCol(Cell, Map<String, Object>, String);* setForeachRowCellValue(boolean, Row, int, Object, List<ExcelForEachParams>, Map<String, Object>,int, int, MergedRegionHelper,int);* setForEachLoopRowCellValue(Row, int, Collection, List<ExcelForEachParams>,ExcelForEachParams, Map<String, Object>,int, int,MergedRegionHelper, String);* 徐本锡 mod by xbx .03.07 list循环遍历的时候 存在合并单元格情况 导致格式错乱** getLoopEachParams(List<ExcelForEachParams>, int, String) 徐本锡 mod by xbx .03.07 解决一对多 row循环中，row元素中包含list并且合并单元格时，报空的错误* * * 徐本锡 addListDataToExcel(Cell, Map<String, Object>, String); 《&INDEX& 表示循环中的序号,自动添加》 失效问题修改： 增加indexColumn.addConstValue(1); -04-19*/import cn.afterturn.easypoi.cache.ExcelCache;import cn.afterturn.easypoi.easy.excel.export.base.BaseExportService;import cn.afterturn.easypoi.easy.util.Sd3eUtil;import cn.afterturn.easypoi.entity.ImageEntity;import cn.afterturn.easypoi.excel.annotation.ExcelTarget;import cn.afterturn.easypoi.excel.entity.TemplateExportParams;import cn.afterturn.easypoi.excel.entity.TemplateSumEntity;import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;import cn.afterturn.easypoi.excel.entity.params.ExcelForEachParams;//import cn.afterturn.easypoi.excel.export.base.BaseExportService;import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler;import cn.afterturn.easypoi.excel.export.template.TemplateSumHandler;import cn.afterturn.easypoi.excel.html.helper.MergedRegionHelper;import cn.afterturn.easypoi.exception.excel.ExcelExportException;import cn.afterturn.easypoi.exception.excel.enums.ExcelExportEnum;import cn.afterturn.easypoi.util.*;import mons.lang3.StringUtils;import org.apache.poi.ss.usermodel.*;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import java.lang.reflect.Field;import java.util.*;import static cn.afterturn.easypoi.excel.ExcelExportUtil.SHEET_NAME;import static cn.afterturn.easypoi.util.PoiElUtil.*;/*** Excel 导出根据模板导出** @author JueYue* -10-17* @version 1.0*/public final class ExcelExportOfTemplateUtil extends BaseExportService {private static final Logger LOGGER = LoggerFactory.getLogger(ExcelExportOfTemplateUtil.class);/*** 缓存TEMP 的for each创建的cell ,跳过这个cell的模板语法查找,提高效率*/private Set<String>tempCreateCellSet = new HashSet<String>();/*** 模板参数,全局都用到*/private TemplateExportParams templateParams;/*** 单元格合并信息*/private MergedRegionHelper mergedRegionHelper;private TemplateSumHandler templateSumHandler;/*** 往Sheet 填充正常数据,根据表头信息 使用导入的部分逻辑,坐对象映射** @param sheet* @param pojoClass* @param dataSet* @param workbook*/private void addDataToSheet(Class<?> pojoClass, Collection<?> dataSet, Sheet sheet,Workbook workbook) throws Exception {// 获取表头数据Map<String, Integer> titlemap = getTitleMap(sheet);Drawing patriarch = PoiExcelGraphDataUtil.getDrawingPatriarch(sheet);// 得到所有字段Field[]fileds = PoiPublicUtil.getClassFields(pojoClass);ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class);StringtargetId = null;if (etarget != null) {targetId = etarget.value();}// 获取实体对象的导出数据List<ExcelExportEntity> excelParams = new ArrayList<ExcelExportEntity>();getAllExcelField(null, targetId, fileds, excelParams, pojoClass, null, null);// 根据表头进行筛选排序sortAndFilterExportField(excelParams, titlemap);short rowHeight = getRowHeight(excelParams);int index = templateParams.getHeadingRows() + templateParams.getHeadingStartRow(),titleHeight = index;int shiftRows = getShiftRows(dataSet, excelParams);//下移数据,模拟插入sheet.shiftRows(templateParams.getHeadingRows() + templateParams.getHeadingStartRow(),sheet.getLastRowNum(), shiftRows, true, true);mergedRegionHelper.shiftRows(sheet, templateParams.getHeadingRows() + templateParams.getHeadingStartRow(), shiftRows,sheet.getLastRowNum() - templateParams.getHeadingRows() - templateParams.getHeadingStartRow());templateSumHandler.shiftRows(templateParams.getHeadingRows() + templateParams.getHeadingStartRow(), shiftRows);PoiExcelTempUtil.reset(sheet, templateParams.getHeadingRows() + templateParams.getHeadingStartRow(), sheet.getLastRowNum());if (excelParams.size() == 0) {return;}Iterator<?> its = dataSet.iterator();while (its.hasNext()) {Object t = its.next();index += createCells(patriarch, index, t, excelParams, sheet, workbook, rowHeight, 0)[0];}// 合并同类项mergeCells(sheet, excelParams, titleHeight);}/*** 利用foreach循环输出数据** @param cell* @param map* @param name* @throws Exception* 徐本锡 mod by xbx .03.07 list循环遍历的时候 存在合并单元格情况 导致格式错乱*/private void addListDataToExcel(Cell cell, Map<String, Object> map,String name) throws Exception {//mod by xbx// 每次利用foreach循环输出数据时，重新处理 mergedRegionHelper// 原因：如果存在多个循环，前面循环时，下面的模板指令所在单元格的行号会发生变化，此时 mergedRegionHelper 中的缓存没有发生相应变化，需要重新获取一下 mergedRegionHelpermergedRegionHelper = new MergedRegionHelper(cell.getSheet());boolean isCreate = !name.contains(FOREACH_NOT_CREATE);boolean isShift = name.contains(FOREACH_AND_SHIFT);name = name.replace(FOREACH_NOT_CREATE, EMPTY).replace(FOREACH_AND_SHIFT, EMPTY).replace(FOREACH, EMPTY).replace(START_STR, EMPTY);String[]keys = name.replaceAll("\\s{1,}", " ").trim().split(" ");Collection<?> datas = (Collection<?>) PoiPublicUtil.getParamsValue(keys[0], map);if (datas == null) {return;}Object[] columnsInfo = getAllDataColumns(cell, name.replace(keys[0], EMPTY),mergedRegionHelper);Iterator<?> its= datas.iterator();int rowspan = (Integer) columnsInfo[0], colspan = (Integer) columnsInfo[1];@SuppressWarnings("unchecked")List<ExcelForEachParams> columns = (List<ExcelForEachParams>) columnsInfo[2];Rowrow = null;introwIndex = cell.getRow().getRowNum() + 1;ExcelForEachParams indexColumn = getIndexColumn(columns);//mod by xbx// 定义循环开始行号int startRowNum = cell.getRow().getRowNum();// 处理当前行if (its.hasNext()) {Object t = its.next();this.setForeachRowCellValue(isCreate, cell.getRow(), cell.getColumnIndex(), t, columns, map, rowspan, colspan, mergedRegionHelper,startRowNum);rowIndex += rowspan - 1;}// //处理当前行// int loopSize = 0;// if (its.hasNext()) {// Object t = its.next();// loopSize = setForeachRowCellValue(isCreate, cell.getRow(), cell.getColumnIndex(), t, columns, map,//rowspan, colspan, mergedRegionHelper)[0];// rowIndex += rowspan - 1 + loopSize - 1;// }//修复不论后面有没有数据,都应该执行的是插入操作if (isShift && datas.size() > 1 && datas.size() * rowspan > 1 && cell.getRowIndex() + rowspan <= cell.getRow().getSheet().getLastRowNum()) {int lastRowNum = cell.getRow().getSheet().getLastRowNum();int shiftRows = lastRowNum - cell.getRowIndex() - rowspan;cell.getRow().getSheet().shiftRows(cell.getRowIndex() + rowspan, lastRowNum, (datas.size() - 1) * rowspan, true, true);mergedRegionHelper.shiftRows(cell.getSheet(), cell.getRowIndex() + rowspan, (datas.size() - 1) * rowspan, shiftRows);templateSumHandler.shiftRows(cell.getRowIndex() + rowspan, (datas.size() - 1) * rowspan);PoiExcelTempUtil.reset(cell.getSheet(), cell.getRowIndex() + rowspan + (datas.size() - 1) * rowspan, cell.getRow().getSheet().getLastRowNum());}//mod by xbx// 定义循环结束行号int endRowNum = 0;// // 创建行while (its.hasNext()) {Object t = its.next();row = createRow(rowIndex, cell.getSheet(), isCreate, rowspan);indexColumn.addConstValue(1);this.setForeachRowCellValue(isCreate, row, cell.getColumnIndex(), t, columns, map, rowspan,colspan, mergedRegionHelper,startRowNum);rowIndex += rowspan;// 每次创建行后，重新给循环结束行号赋值endRowNum = row.getRowNum();}// 如果新创建行了// 合并循环左侧竖向单元格if (endRowNum != 0){Sheet sheet = cell.getRow().getSheet();int sheetMergeCount = sheet.getNumMergedRegions();int columnIndex = cell.getColumnIndex();for(int i = 0; i < sheetMergeCount; ++i) {CellRangeAddress ca = sheet.getMergedRegion(i);//int firstColumn = ca.getFirstColumn();int lastColumn = ca.getLastColumn();int firstRow = ca.getFirstRow();int lastRow = ca.getLastRow();for (int ii = 0;ii < columnIndex;ii++){if (firstRow <= startRowNum && lastRow > startRowNum && lastColumn == ii){ca.setLastRow(lastRow + (endRowNum - startRowNum));}}}}// while (its.hasNext()) {// Object t = its.next();// row = createRow(rowIndex, cell.getSheet(), isCreate, rowspan);// indexColumn.addConstValue(1);// loopSize = setForeachRowCellValue(isCreate, row, cell.getColumnIndex(), t, columns, map, rowspan,//colspan, mergedRegionHelper)[0];// rowIndex += rowspan + loopSize - 1;// }}private ExcelForEachParams getIndexColumn(List<ExcelForEachParams> columns) {for (int i = 0; i < columns.size(); i++) {if (columns.get(i) != null && INDEX.equals(columns.get(i).getConstValue())) {columns.get(i).setConstValue("1");return columns.get(i);}}return new ExcelForEachParams();}/*** 下移数据** @param dataSet* @param excelParams* @return*/private int getShiftRows(Collection<?> dataSet,List<ExcelExportEntity> excelParams) throws Exception {int size = 0;Iterator<?> its = dataSet.iterator();while (its.hasNext()) {Object t = its.next();size += getOneObjectSize(t, excelParams);}return size;}/*** 获取单个对象的高度,主要是处理一堆多的情况** @param t* @param excelParams* @throws Exception*/private int getOneObjectSize(Object t, List<ExcelExportEntity> excelParams) throws Exception {ExcelExportEntity entity;intmaxHeight = 1;for (int k = 0, paramSize = excelParams.size(); k < paramSize; k++) {entity = excelParams.get(k);if (entity.getList() != null) {Collection<?> list = (Collection<?>) entity.getMethod().invoke(t, new Object[]{});if (list != null && list.size() > maxHeight) {maxHeight = list.size();}}}return maxHeight;}public Workbook createExcelCloneByTemplate(TemplateExportParams params,Map<Integer, List<Map<String, Object>>> map) {// step 1. 判断模板的地址if (params == null || map == null || StringUtils.isEmpty(params.getTemplateUrl())) {throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR);}Workbook wb = null;// step 2. 判断模板的Excel类型,解析模板try {this.templateParams = params;wb = ExcelCache.getWorkbook(templateParams.getTemplateUrl(), templateParams.getSheetNum(),true);intoldSheetNum = wb.getNumberOfSheets();List<String> oldSheetName = new ArrayList<>();for (int i = 0; i < oldSheetNum; i++) {oldSheetName.add(wb.getSheetName(i));}// 把所有的KEY排个顺序List<Map<String, Object>> mapList;List<Integer> sheetNumList = new ArrayList<>();sheetNumList.addAll(map.keySet());Collections.sort(sheetNumList);//把需要克隆的全部克隆一遍for (Integer sheetNum : sheetNumList) {mapList = map.get(sheetNum);for (int i = mapList.size(); i > 0; i--) {wb.cloneSheet(sheetNum);}}for (int i = 0; i < oldSheetName.size(); i++) {wb.removeSheetAt(wb.getSheetIndex(oldSheetName.get(i)));}// 创建表格样式setExcelExportStyler((IExcelExportStyler) templateParams.getStyle().getConstructor(Workbook.class).newInstance(wb));// step 3. 解析模板int sheetIndex = 0;for (Integer sheetNum : sheetNumList) {mapList = map.get(sheetNum);for (int i = mapList.size() - 1; i >= 0; i--) {tempCreateCellSet.clear();if (mapList.get(i).containsKey(SHEET_NAME)) {wb.setSheetName(sheetIndex, mapList.get(i).get(SHEET_NAME).toString());}parseTemplate(wb.getSheetAt(sheetIndex), mapList.get(i), params.isColForEach());if (params.isReadonly()) {wb.getSheetAt(i).protectSheet(UUID.randomUUID().toString());}sheetIndex++;}}} catch (Exception e) {LOGGER.error(e.getMessage(), e);return null;}return wb;}public Workbook createExcelByTemplate(TemplateExportParams params,Map<Integer, Map<String, Object>> map) {// step 1. 判断模板的地址if (params == null || map == null || StringUtils.isEmpty(params.getTemplateUrl())) {throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR);}Workbook wb = null;// step 2. 判断模板的Excel类型,解析模板try {this.templateParams = params;wb = getCloneWorkBook();// 创建表格样式setExcelExportStyler((IExcelExportStyler) templateParams.getStyle().getConstructor(Workbook.class).newInstance(wb));// step 3. 解析模板for (int i = 0, le = params.isScanAllsheet() ? wb.getNumberOfSheets(): params.getSheetNum().length; i < le; i++) {if (params.getSheetName() != null && params.getSheetName().length > i&& StringUtils.isNotEmpty(params.getSheetName()[i])) {wb.setSheetName(i, params.getSheetName()[i]);}tempCreateCellSet.clear();parseTemplate(wb.getSheetAt(i), map.get(i), params.isColForEach());if (params.isReadonly()) {wb.getSheetAt(i).protectSheet(UUID.randomUUID().toString());}}} catch (Exception e) {LOGGER.error(e.getMessage(), e);return null;}return wb;}public Workbook createExcelByTemplate(TemplateExportParams params, Class<?> pojoClass,Collection<?> dataSet, Map<String, Object> map) {// step 1. 判断模板的地址if (params == null || map == null || (StringUtils.isEmpty(params.getTemplateUrl()) && params.getTemplateWb() == null)) {throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR);}Workbook wb = null;// step 2. 判断模板的Excel类型,解析模板try {this.templateParams = params;if (params.getTemplateWb() != null) {wb = params.getTemplateWb();} else {wb = getCloneWorkBook();}if (params.getDictHandler() != null) {this.dictHandler = params.getDictHandler();}if (params.getI18nHandler() != null) {this.i18nHandler = params.getI18nHandler();}// 创建表格样式setExcelExportStyler((IExcelExportStyler) templateParams.getStyle().getConstructor(Workbook.class).newInstance(wb));// step 3. 解析模板for (int i = 0, le = params.isScanAllsheet() ? wb.getNumberOfSheets(): params.getSheetNum().length; i < le; i++) {if (params.getSheetName() != null && params.getSheetName().length > i&& StringUtils.isNotEmpty(params.getSheetName()[i])) {wb.setSheetName(i, params.getSheetName()[i]);}tempCreateCellSet.clear();parseTemplate(wb.getSheetAt(i), map, params.isColForEach());if (params.isReadonly()) {wb.getSheetAt(i).protectSheet(UUID.randomUUID().toString());}}if (dataSet != null) {// step 4. 正常的数据填充dataHandler = params.getDataHandler();if (dataHandler != null) {needHandlerList = Arrays.asList(dataHandler.getNeedHandlerFields());}addDataToSheet(pojoClass, dataSet, wb.getSheetAt(params.getDataSheetNum()), wb);}} catch (Exception e) {LOGGER.error(e.getMessage(), e);return null;}return wb;}/*** 克隆excel防止操作原对象,workbook无法克隆,只能对excel进行克隆** @throws Exception*/private Workbook getCloneWorkBook() throws Exception {return ExcelCache.getWorkbook(templateParams.getTemplateUrl(), templateParams.getSheetNum(),templateParams.isScanAllsheet());}/*** 获取表头数据,设置表头的序号** @param sheet* @return*/private Map<String, Integer> getTitleMap(Sheet sheet) {Row row= null;Iterator<Cell> cellTitle;Map<String, Integer> titlemap = new HashMap<String, Integer>();for (int j = 0; j < templateParams.getHeadingRows(); j++) {row = sheet.getRow(j + templateParams.getHeadingStartRow());cellTitle = row.cellIterator();int i = row.getFirstCellNum();while (cellTitle.hasNext()) {Cell cell = cellTitle.next();String value = cell.getStringCellValue();if (!StringUtils.isEmpty(value)) {titlemap.put(value, i);}i = i + 1;}}return titlemap;}private void parseTemplate(Sheet sheet, Map<String, Object> map,boolean colForeach) throws Exception {if (sheet.getWorkbook() instanceof XSSFWorkbook) {super.type = ExcelType.XSSF;}deleteCell(sheet, map);mergedRegionHelper = new MergedRegionHelper(sheet);templateSumHandler = new TemplateSumHandler(sheet);if (colForeach) {colForeach(sheet, map);}Row row = null;int index = 0;while (index <= sheet.getLastRowNum()) {row = sheet.getRow(index++);if (row == null) {continue;}for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {if (row.getCell(i) != null && !tempCreateCellSet.contains(row.getRowNum() + "_" + row.getCell(i).getColumnIndex())) {setValueForCellByMap(row.getCell(i), map);}}}//修改需要处理的统计值handlerSumCell(sheet);}private void handlerSumCell(Sheet sheet) {for (TemplateSumEntity sumEntity : templateSumHandler.getDataList()) {Cell cell = sheet.getRow(sumEntity.getRow()).getCell(sumEntity.getCol());if (cell.getStringCellValue().contains(sumEntity.getSumKey())) {cell.setCellValue(cell.getStringCellValue().replace("sum:(" + sumEntity.getSumKey() + ")", sumEntity.getValue() + ""));} else {cell.setCellValue(cell.getStringCellValue() + sumEntity.getValue());}}}/*** 先进行列的循环,因为涉及很多数据** @param sheet* @param map*/private void colForeach(Sheet sheet, Map<String, Object> map) throws Exception {Row row = null;Cell cell = null;int index = 0;while (index <= sheet.getLastRowNum()) {row = sheet.getRow(index++);if (row == null) {continue;}for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {cell = row.getCell(i);if (row.getCell(i) != null && (cell.getCellType() == CellType.STRING|| cell.getCellType() == CellType.NUMERIC)) {String text = PoiCellUtil.getCellValue(cell);if (text.contains(FOREACH_COL) || text.contains(FOREACH_COL_VALUE)) {foreachCol(cell, map, text);}}}}}/*** 循环列表** @param cell* @param map* @param name* @throws Exception* 徐本锡 mod by xbx .03.07 list循环遍历的时候 存在合并单元格情况 导致格式错乱*/private void foreachCol(Cell cell, Map<String, Object> map, String name) throws Exception {boolean isCreate = name.contains(FOREACH_COL_VALUE);name = name.replace(FOREACH_COL_VALUE, EMPTY).replace(FOREACH_COL, EMPTY).replace(START_STR,EMPTY);String[]keys = name.replaceAll("\\s{1,}", " ").trim().split(" ");Collection<?> datas = (Collection<?>) PoiPublicUtil.getParamsValue(keys[0], map);Object[] columnsInfo = getAllDataColumns(cell, name.replace(keys[0], EMPTY),mergedRegionHelper);if (datas == null) {return;}Iterator<?> its= datas.iterator();int rowspan = (Integer) columnsInfo[0], colspan = (Integer) columnsInfo[1];@SuppressWarnings("unchecked")List<ExcelForEachParams> columns = (List<ExcelForEachParams>) columnsInfo[2];while (its.hasNext()) {Object t = its.next();//mod by xbx// 定义循环开始行号int startRowNum = cell.getRow().getRowNum();setForeachRowCellValue(true, cell.getRow(), cell.getColumnIndex(), t, columns, map,rowspan, colspan, mergedRegionHelper,startRowNum);// setForeachRowCellValue(true, cell.getRow(), cell.getColumnIndex(), t, columns, map,//rowspan, colspan, mergedRegionHelper);if (cell.getRow().getCell(cell.getColumnIndex() + colspan) == null) {cell.getRow().createCell(cell.getColumnIndex() + colspan);}cell = cell.getRow().getCell(cell.getColumnIndex() + colspan);}if (isCreate) {cell = cell.getRow().getCell(cell.getColumnIndex() - 1);cell.setCellValue(cell.getStringCellValue() + END_STR);}}/*** 先判断删除,省得影响效率** @param sheet* @param map* @throws Exception*/private void deleteCell(Sheet sheet, Map<String, Object> map) throws Exception {Row row = null;Cell cell = null;int index = 0;while (index <= sheet.getLastRowNum()) {row = sheet.getRow(index++);if (row == null) {continue;}for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {cell = row.getCell(i);if (row.getCell(i) != null && (cell.getCellType() == CellType.STRING|| cell.getCellType() == CellType.NUMERIC)) {cell.setCellType(CellType.STRING);String text = cell.getStringCellValue();if (text.contains(IF_DELETE)) {if (Boolean.valueOf(eval(text.substring(text.indexOf(START_STR) + 2, text.indexOf(END_STR)).trim(), map).toString())) {PoiSheetUtil.deleteColumn(sheet, i);i--;}cell.setCellValue("");}}}}}/*** 给每个Cell通过解析方式set值** @param cell* @param map*/private void setValueForCellByMap(Cell cell, Map<String, Object> map) throws Exception {CellType cellType = cell.getCellType();if (cellType != CellType.STRING && cellType != CellType.NUMERIC) {return;}String oldString;oldString = cell.getStringCellValue();if (oldString != null && oldString.indexOf(START_STR) != -1&& !oldString.contains(FOREACH)) {boolean isNumber = isHasSymbol(oldString, NUMBER_SYMBOL);Object obj = getValByHandler(oldString,map, cell);//如何是数值 类型,就按照数值类型进行设置;如果是图片就设置为图片if (obj instanceof ImageEntity) {ImageEntity img = (ImageEntity) obj;cell.setCellValue("");if (img.getRowspan() > 1 || img.getColspan() > 1) {img.setHeight(0);PoiMergeCellUtil.addMergedRegion(cell.getSheet(), cell.getRowIndex(),cell.getRowIndex() + img.getRowspan() - 1, cell.getColumnIndex(), cell.getColumnIndex() + img.getColspan() - 1);}createImageCell(cell, img.getHeight(), img.getRowspan(), img.getColspan(), img.getUrl(), img.getData());} else if (isNumber && StringUtils.isNotBlank(obj.toString())) {cell.setCellValue(Double.parseDouble(obj.toString()));} else {cell.setCellValue(obj.toString());}}//判断foreach 这种方法if (oldString != null && oldString.contains(FOREACH)) {addListDataToExcel(cell, map, oldString.trim());}}/*** 根据模板解析函数获取值* @param funStr* @param map* @return*/private Object getValByHandler(String funStr,Map<String, Object> map, Cell cell) throws Exception {// step 2. 判断是否含有解析函数if (isHasSymbol(funStr, NUMBER_SYMBOL)) {funStr = funStr.replaceFirst(NUMBER_SYMBOL, "");}boolean isStyleBySelf = false;if (isHasSymbol(funStr, STYLE_SELF)) {isStyleBySelf = true;funStr = funStr.replaceFirst(STYLE_SELF, "");}boolean isDict = false;String dict = null;if (isHasSymbol(funStr, DICT_HANDLER)) {isDict = true;dict = funStr.substring(funStr.indexOf(DICT_HANDLER) + 5).split(";")[0];funStr = funStr.replaceFirst(DICT_HANDLER, "");funStr = funStr.replaceFirst(dict + ";", "");}boolean isI18n = false;if (isHasSymbol(funStr, I18N_HANDLER)) {isI18n = true;funStr = funStr.replaceFirst(I18N_HANDLER, "");}boolean isDern = false;String dern = null;if (isHasSymbol(funStr, DESENSITIZATION_RULE)) {isDern = true;dern = funStr.substring(funStr.indexOf(DESENSITIZATION_RULE) + 5).split(";")[0];funStr = funStr.replaceFirst(DESENSITIZATION_RULE, "");funStr = funStr.replaceFirst(dern + ";", "");}if (isHasSymbol(funStr, MERGE)) {String mergeStr = PoiPublicUtil.getElStr(funStr,MERGE);funStr = funStr.replace(mergeStr, "");mergeStr = mergeStr.replaceFirst(MERGE, "");try {int colSpan = (int)Double.parseDouble(PoiPublicUtil.getRealValue(mergeStr, map).toString());PoiMergeCellUtil.addMergedRegion(cell.getSheet(), cell.getRowIndex(),cell.getRowIndex() , cell.getColumnIndex(), cell.getColumnIndex() + colSpan - 1);} catch (Exception e) {LOGGER.error(e.getMessage(),e);}}Object obj = funStr.indexOf(START_STR) == -1 ? eval(funStr, map) : PoiPublicUtil.getRealValue(funStr, map);if (isDict) {obj = dictHandler.toName(dict, null, funStr, obj);}if (isI18n) {obj = i18nHandler.getLocaleName(obj.toString());}if (isDern) {obj = PoiDataDesensitizationUtil.desensitization(dern,obj);}return obj;}private boolean isHasSymbol(String text, String symbol) {return text.startsWith(symbol) || text.contains("{" + symbol)|| text.contains(" " + symbol) || text.contains(";" + symbol);}/*** 创建并返回第一个Row** @param sheet* @param rowIndex* @param isCreate* @param rows* @return*/private Row createRow(int rowIndex, Sheet sheet, boolean isCreate, int rows) {for (int i = 0; i < rows; i++) {if (isCreate) {sheet.createRow(rowIndex++);} else if (sheet.getRow(rowIndex++) == null) {sheet.createRow(rowIndex - 1);}}return sheet.getRow(rowIndex - rows);}/*** 循环迭代创建,遍历row** @param isCreate* @param row* @param columnIndex* @param t* @param columns* @param map* @param rowspan* @param colspan* @param mergedRegionHelper* @return rowSize, cellSize* @throws Exception* 徐本锡 mod by xbx .03.07 list循环遍历的时候 存在合并单元格情况 导致格式错乱*/private int[] setForeachRowCellValue(boolean isCreate, Row row, int columnIndex, Object t,List<ExcelForEachParams> columns, Map<String, Object> map,int rowspan, int colspan,MergedRegionHelper mergedRegionHelper,int startRowNum) throws Exception {createRowCellSetStyle(row, columnIndex, columns, rowspan, colspan);//填写数据ExcelForEachParams params;intloopSize = 1;intloopCi = 1;//row = row.getSheet().getRow(row.getRowNum() - rowspan + 1);for (int k = 0; k < rowspan; k++) {int ci = columnIndex;row.setHeight(getMaxHeight(k, colspan, columns));for (int i = 0; i < colspan && i < columns.size(); i++) {boolean isNumber = false;params = columns.get(colspan * k + i);tempCreateCellSet.add(row.getRowNum() + "_" + (ci));if (params == null) {continue;}if (StringUtils.isEmpty(params.getName())&& StringUtils.isEmpty(params.getConstValue())) {row.getCell(ci).setCellStyle(params.getCellStyle());ci = ci + params.getColspan();continue;}String val;Object obj = null;//是不是常量String tempStr = params.getName();if (StringUtils.isEmpty(params.getName())) {val = params.getConstValue();} else {map.put(templateParams.getTempParams(), t);isNumber = isHasSymbol(tempStr, NUMBER_SYMBOL);obj = getValByHandler(tempStr,map,row.getCell(ci));val = obj.toString();}if (obj != null && obj instanceof Collection) {// 需要找到哪一级别是集合 ,方便后面的replaceString collectName = evalFindName(tempStr, map);int[] loop = setForEachLoopRowCellValue(row, ci, (Collection) obj, columns,params, map, rowspan, colspan, mergedRegionHelper, collectName);loopSize = Math.max(loopSize, loop[0]);i += loop[1] - 1;ci = loop[2] - params.getColspan();} else if (obj != null && obj instanceof ImageEntity) {ImageEntity img = (ImageEntity) obj;row.getCell(ci).setCellValue("");if (img.getRowspan() > 1 || img.getColspan() > 1) {img.setHeight(0);//mod by xbxSd3eUtil.addMergedRegionByListForEach(row.getCell(ci).getSheet(), row.getCell(ci).getRowIndex(), row.getCell(ci).getRowIndex() + img.getRowspan() - 1, row.getCell(ci).getColumnIndex(), row.getCell(ci).getColumnIndex() + img.getColspan() - 1);// row.getCell(ci).getSheet().addMergedRegion(new CellRangeAddress(row.getCell(ci).getRowIndex(),// row.getCell(ci).getRowIndex() + img.getRowspan() - 1, row.getCell(ci).getColumnIndex(), row.getCell(ci).getColumnIndex() + img.getColspan() - 1));}createImageCell(row.getCell(ci), img.getHeight(), img.getRowspan(), img.getColspan(), img.getUrl(), img.getData());} else if (isNumber && StringUtils.isNotEmpty(val)) {row.getCell(ci).setCellValue(Double.parseDouble(val));} else {try {row.getCell(ci).setCellValue(val);} catch (Exception e) {LOGGER.error(e.getMessage(), e);}}if (params.getCellStyle() != null) {row.getCell(ci).setCellStyle(params.getCellStyle());}//判断这个属性是不是需要统计if (params.isNeedSum()) {templateSumHandler.addValueOfKey(params.getName(), obj.toString());}//如果合并单元格,就把这个单元格的样式和之前的保持一致setMergedRegionStyle(row, ci, params);////合并对应单元格//boolean isNeedMerge = (params.getRowspan() != 1 || params.getColspan() != 1)// && !mergedRegionHelper.isMergedRegion(row.getRowNum() + 1, ci);//// 这里感觉判断没有意义,有点浪费性能,还不如后面报错//// && !PoiCellUtil.isMergedRegion(row.getSheet(), row.getRowNum(), ci);//if (isNeedMerge) {//PoiMergeCellUtil.addMergedRegion(row.getSheet(), row.getRowNum(),// row.getRowNum() + params.getRowspan() - 1, ci,// ci + params.getColspan() - 1);//}//mod by xbx//合并对应单元格// 存在合并单元格时，这个判断出问题了，需要注释//&& !mergedRegionHelper.isMergedRegion(row.getRowNum() + 1, ci)// 将第二个参数改为：循环开始行号// 原因：这个方法原先是判断当前行这一列，是否需要合并单元格// 如果是新创建的行，这个方法恒定返回 false ，判断出现问题// 所以需要改为：判断循环开始行这一列，是否需要合并单元格boolean isNeedMerge = (params.getRowspan() != 1 || params.getColspan() != 1) && PoiCellUtil.isMergedRegion(row.getSheet(), startRowNum, ci);if (isNeedMerge) {Sd3eUtil.addMergedRegionByListForEach(row.getSheet(), row.getRowNum(), row.getRowNum() + params.getRowspan() - 1, ci, ci + params.getColspan() - 1);}if (params.getRowspan() == 1 && params.getColspan() == 1){row.getCell(ci).getSheet().setColumnWidth(row.getCell(ci).getColumnIndex(), params.getWidth());}ci = ci + params.getColspan();}loopCi = Math.max(loopCi, ci);// 需要把需要合并的单元格合并了 --- 不是集合的栏位合并了if (loopSize > 1) {handlerLoopMergedRegion(row, columnIndex, columns, loopSize);}row = row.getSheet().getRow(row.getRowNum() + 1);}return new int[]{loopSize, loopCi};}// /**//* 循环迭代创建,遍历row//*//* @param isCreate//* @param row//* @param columnIndex//* @param t//* @param columns//* @param map//* @param rowspan//* @param colspan//* @param mergedRegionHelper//* @return rowSize, cellSize//* @throws Exception//*/// private int[] setForeachRowCellValue(boolean isCreate, Row row, int columnIndex, Object t,// List<ExcelForEachParams> columns, Map<String, Object> map,// int rowspan, int colspan,// MergedRegionHelper mergedRegionHelper) throws Exception {// createRowCellSetStyle(row, columnIndex, columns, rowspan, colspan);// //填写数据// ExcelForEachParams params;// intloopSize = 1;// intloopCi = 1;// //row = row.getSheet().getRow(row.getRowNum() - rowspan + 1);// for (int k = 0; k < rowspan; k++) {// int ci = columnIndex;// row.setHeight(getMaxHeight(k, colspan, columns));// for (int i = 0; i < colspan && i < columns.size(); i++) {//boolean isNumber = false;//params = columns.get(colspan * k + i);//tempCreateCellSet.add(row.getRowNum() + "_" + (ci));//if (params == null) {//continue;//}//if (StringUtils.isEmpty(params.getName())// && StringUtils.isEmpty(params.getConstValue())) {//row.getCell(ci).setCellStyle(params.getCellStyle());//ci = ci + params.getColspan();//continue;//}//String val;//Object obj = null;////是不是常量//String tempStr = params.getName();//if (StringUtils.isEmpty(params.getName())) {//val = params.getConstValue();//} else {//map.put(templateParams.getTempParams(), t);//isNumber = isHasSymbol(tempStr, NUMBER_SYMBOL);//obj = getValByHandler(tempStr,map,row.getCell(ci));//val = obj.toString();//}//if (obj != null && obj instanceof Collection) {//// 需要找到哪一级别是集合 ,方便后面的replace//String collectName = evalFindName(tempStr, map);//int[] loop = setForEachLoopRowCellValue(row, ci, (Collection) obj, columns,// params, map, rowspan, colspan, mergedRegionHelper, collectName);//loopSize = Math.max(loopSize, loop[0]);//i += loop[1] - 1;//ci = loop[2] - params.getColspan();//} else if (obj != null && obj instanceof ImageEntity) {//ImageEntity img = (ImageEntity) obj;//row.getCell(ci).setCellValue("");//if (img.getRowspan() > 1 || img.getColspan() > 1) {// img.setHeight(0);// row.getCell(ci).getSheet().addMergedRegion(new CellRangeAddress(row.getCell(ci).getRowIndex(),// row.getCell(ci).getRowIndex() + img.getRowspan() - 1, row.getCell(ci).getColumnIndex(), row.getCell(ci).getColumnIndex() + img.getColspan() - 1));//}//createImageCell(row.getCell(ci), img.getHeight(), img.getRowspan(), img.getColspan(), img.getUrl(), img.getData());//} else if (isNumber && StringUtils.isNotEmpty(val)) {//row.getCell(ci).setCellValue(Double.parseDouble(val));//} else {//try {// row.getCell(ci).setCellValue(val);//} catch (Exception e) {// LOGGER.error(e.getMessage(), e);//}//}//if (params.getCellStyle() != null) {//row.getCell(ci).setCellStyle(params.getCellStyle());//}////判断这个属性是不是需要统计//if (params.isNeedSum()) {//templateSumHandler.addValueOfKey(params.getName(), obj.toString());//}////如果合并单元格,就把这个单元格的样式和之前的保持一致//setMergedRegionStyle(row, ci, params);////合并对应单元格//boolean isNeedMerge = (params.getRowspan() != 1 || params.getColspan() != 1)// && !mergedRegionHelper.isMergedRegion(row.getRowNum() + 1, ci);//// 这里感觉判断没有意义,有点浪费性能,还不如后面报错//// && !PoiCellUtil.isMergedRegion(row.getSheet(), row.getRowNum(), ci);//if (isNeedMerge) {//PoiMergeCellUtil.addMergedRegion(row.getSheet(), row.getRowNum(),// row.getRowNum() + params.getRowspan() - 1, ci,// ci + params.getColspan() - 1);//}//if (params.getRowspan() == 1 && params.getColspan() == 1){//row.getCell(ci).getSheet().setColumnWidth(row.getCell(ci).getColumnIndex(), params.getWidth());//}//ci = ci + params.getColspan();// }// loopCi = Math.max(loopCi, ci);// // 需要把需要合并的单元格合并了 --- 不是集合的栏位合并了// if (loopSize > 1) {//handlerLoopMergedRegion(row, columnIndex, columns, loopSize);// }// row = row.getSheet().getRow(row.getRowNum() + 1);// }// return new int[]{loopSize, loopCi};// }/*** 迭代把不是集合的数据都合并了** @param row* @param columnIndex* @param columns* @param loopSize*/private void handlerLoopMergedRegion(Row row, int columnIndex, List<ExcelForEachParams> columns, int loopSize) {for (int i = 0; i < columns.size(); i++) {if (!columns.get(i).isCollectCell()) {PoiMergeCellUtil.addMergedRegion(row.getSheet(), row.getRowNum(),row.getRowNum() + loopSize - 1, columnIndex,columnIndex + columns.get(i).getColspan() - 1);}columnIndex = columnIndex + columns.get(i).getColspan();}}private short getMaxHeight(int k, int colspan, List<ExcelForEachParams> columns) {short high = columns.get(0).getHeight();int n = k;while (n > 0) {if (columns.get(n * colspan).getHeight() == 0) {n--;} else {high = columns.get(n * colspan).getHeight();break;}}return high;}/*** 处理内循环** @param row* @param columnIndex* @param obj* @param columns* @param params* @param map* @param rowspan* @param colspan* @param mergedRegionHelper* @param collectName* @return [rowNums, columnsNums, ciIndex]* @throws Exception* 徐本锡 mod by xbx .03.07 list循环遍历的时候 存在合并单元格情况 导致格式错乱*/private int[] setForEachLoopRowCellValue(Row row, int columnIndex, Collection obj, List<ExcelForEachParams> columns,ExcelForEachParams params, Map<String, Object> map,int rowspan, int colspan,MergedRegionHelper mergedRegionHelper, String collectName) throws Exception {//多个一起遍历 -去掉第一层 把所有的数据遍历一遍//STEP 1拿到所有的和当前一样项目的字段List<ExcelForEachParams> temp = getLoopEachParams(columns, columnIndex, collectName);Iterator<?> its= obj.iterator();Row tempRow = row;int nums = 0;int ci= columnIndex;while (its.hasNext()) {Object data = its.next();map.put("loop_" + columnIndex, data);// mod by xbx// 定义循环开始行号int startRowNum = tempRow.getRowNum();int[] loopArr = setForeachRowCellValue(false, tempRow, columnIndex, data, temp, map, rowspan,colspan, mergedRegionHelper,startRowNum);// int[] loopArr = setForeachRowCellValue(false, tempRow, columnIndex, data, temp, map, rowspan,//colspan, mergedRegionHelper);nums += loopArr[0];ci = Math.max(ci, loopArr[1]);map.remove("loop_" + columnIndex);tempRow = createRow(tempRow.getRowNum() + loopArr[0], row.getSheet(), false, rowspan);}for (int i = 0; i < temp.size(); i++) {temp.get(i).setName(temp.get(i).getTempName().pop());//都是集合temp.get(i).setCollectCell(true);}return new int[]{nums, temp.size(), ci};}/*** 根据 当前是集合的信息,把后面整个集合的迭代获取出来,并替换掉集合的前缀方便后面取数** @param columns* @param columnIndex* @param collectName* @return* 徐本锡 mod by xbx .03.07 解决一对多 row循环中，row元素中包含list并且合并单元格时，报空的错误*/private List<ExcelForEachParams> getLoopEachParams(List<ExcelForEachParams> columns, int columnIndex, String collectName) {List<ExcelForEachParams> temp = new ArrayList<>();for (int i = 0; i < columns.size(); i++) {//mod by xbx//解决row循环中，row元素中包含list并且合并单元格时，报空的错误if (columns.get(i)==null){continue;}//先置为不是集合columns.get(i).setCollectCell(false);if (columns.get(i) == null || columns.get(i).getName().contains(collectName)) {temp.add(columns.get(i));if (columns.get(i).getTempName() == null) {columns.get(i).setTempName(new Stack<>());}columns.get(i).setCollectCell(true);columns.get(i).getTempName().push(columns.get(i).getName());columns.get(i).setName(columns.get(i).getName().replace(collectName, "loop_" + columnIndex));}}return temp;}private void createRowCellSetStyle(Row row, int columnIndex, List<ExcelForEachParams> columns,int rowspan, int colspan) {//所有的cell创建一遍for (int i = 0; i < rowspan; i++) {int size = columns.size();for (int j = columnIndex, max = columnIndex + colspan; j < max; j++) {if (row.getCell(j) == null) {row.createCell(j);CellStyle style = row.getRowNum() % 2 == 0? getStyles(false,size <= j - columnIndex ? null : columns.get(j - columnIndex)): getStyles(true,size <= j - columnIndex ? null : columns.get(j - columnIndex));//返回的styler不为空时才使用,否则使用Excel设置的,更加推荐Excel设置的样式if (style != null) {row.getCell(j).setCellStyle(style);}}}if (i < rowspan - 1) {row = row.getSheet().getRow(row.getRowNum() + 1);}}}private CellStyle getStyles(boolean isSingle, ExcelForEachParams excelForEachParams) {return excelExportStyler.getTemplateStyles(isSingle, excelForEachParams);}/*** 设置合并单元格的样式** @param row* @param ci* @param params*/private void setMergedRegionStyle(Row row, int ci, ExcelForEachParams params) {//第一行数据for (int i = 1; i < params.getColspan(); i++) {if (params.getCellStyle() != null) {row.getCell(ci + i).setCellStyle(params.getCellStyle());}}for (int i = 1; i < params.getRowspan(); i++) {for (int j = 0; j < params.getColspan(); j++) {if (params.getCellStyle() != null) {row.getCell(ci + j).setCellStyle(params.getCellStyle());}}}}/*** 获取迭代的数据的值** @param cell* @param name* @param mergedRegionHelper* @return*/private Object[] getAllDataColumns(Cell cell, String name,MergedRegionHelper mergedRegionHelper) {List<ExcelForEachParams> columns = new ArrayList<ExcelForEachParams>();cell.setCellValue("");columns.add(getExcelTemplateParams(name.replace(END_STR, EMPTY), cell, mergedRegionHelper));int rowspan = 1, colspan = 1;if (!name.contains(END_STR)) {int index = cell.getColumnIndex();//保存col 的开始列int startIndex = cell.getColumnIndex();Row row = cell.getRow();while (index < row.getLastCellNum()) {int colSpan = columns.get(columns.size() - 1) != null? columns.get(columns.size() - 1).getColspan() : 1;index += colSpan;for (int i = 1; i < colSpan; i++) {//添加合并的单元格,这些单元可能不是空,但是没有值,所以也需要跳过columns.add(null);continue;}cell = row.getCell(index);//可能是合并的单元格if (cell == null) {//读取是判断,跳过columns.add(null);continue;}String cellStringString;try {//不允许为空 便利单元格必须有结尾和值cellStringString = cell.getStringCellValue();if (StringUtils.isBlank(cellStringString) && colspan + startIndex <= index) {throw new ExcelExportException("for each 当中存在空字符串,请检查模板");} else if (StringUtils.isBlank(cellStringString)&& colspan + startIndex > index) {//读取是判断,跳过,数据为空,但是不是第一次读这一列,所以可以跳过columns.add(new ExcelForEachParams(null, cell.getCellStyle(), (short) 0));continue;}} catch (Exception e) {throw new ExcelExportException(ExcelExportEnum.TEMPLATE_ERROR, e);}//把读取过的cell 置为空cell.setCellValue("");if (cellStringString.contains(END_STR)) {columns.add(getExcelTemplateParams(cellStringString.replace(END_STR, EMPTY),cell, mergedRegionHelper));//补全缺失的cell(合并单元格后面的)int lastCellColspan = columns.get(columns.size() - 1).getColspan();for (int i = 1; i < lastCellColspan; i++) {//添加合并的单元格,这些单元可能不是空,但是没有值,所以也需要跳过columns.add(null);}break;} else if (cellStringString.contains(WRAP)) {columns.add(getExcelTemplateParams(cellStringString.replace(WRAP, EMPTY), cell,mergedRegionHelper));//发现换行符,执行换行操作colspan = index - startIndex + 1;index = startIndex - columns.get(columns.size() - 1).getColspan();row = row.getSheet().getRow(row.getRowNum() + 1);rowspan++;} else {columns.add(getExcelTemplateParams(cellStringString.replace(WRAP, EMPTY), cell,mergedRegionHelper));}}}colspan = 0;for (int i = 0; i < columns.size(); i++) {colspan += columns.get(i) != null ? columns.get(i).getColspan() : 0;}colspan = colspan / rowspan;return new Object[]{rowspan, colspan, columns};}/*** 获取模板参数** @param name* @param cell* @param mergedRegionHelper* @return*/private ExcelForEachParams getExcelTemplateParams(String name, Cell cell,MergedRegionHelper mergedRegionHelper) {name = name.trim();ExcelForEachParams params = new ExcelForEachParams(name, cell.getCellStyle(),cell.getRow().getHeight());//判断是不是常量if (name.startsWith(CONST) && name.endsWith(CONST)) {params.setName(null);params.setConstValue(name.substring(1, name.length() - 1));}//判断是不是空if (NULL.equals(name)) {params.setName(null);params.setConstValue(EMPTY);}//是否是当前索引if (INDEX.equals(name)) {params.setName(null);params.setConstValue(INDEX);}//获取合并单元格的数据if (mergedRegionHelper.isMergedRegion(cell.getRowIndex() + 1, cell.getColumnIndex())) {Integer[] colAndrow = mergedRegionHelper.getRowAndColSpan(cell.getRowIndex() + 1,cell.getColumnIndex());params.setRowspan(colAndrow[0]);params.setColspan(colAndrow[1]);}params.setNeedSum(templateSumHandler.isSumKey(params.getName()));params.setWidth(cell.getSheet().getColumnWidth(cell.getColumnIndex()));return params;}/*** 对导出序列进行排序和塞选** @param excelParams* @param titlemap*/private void sortAndFilterExportField(List<ExcelExportEntity> excelParams,Map<String, Integer> titlemap) {for (int i = excelParams.size() - 1; i >= 0; i--) {if (excelParams.get(i).getList() != null && excelParams.get(i).getList().size() > 0) {sortAndFilterExportField(excelParams.get(i).getList(), titlemap);if (excelParams.get(i).getList().size() == 0) {excelParams.remove(i);} else {excelParams.get(i).setOrderNum(i);}} else {if (titlemap.containsKey(excelParams.get(i).getName())) {excelParams.get(i).setOrderNum(i);} else {excelParams.remove(i);}}}sortAllParams(excelParams);}}

4.修改cn.afterturn.easypoi.excel.export.base.BaseExportService

要保证ExcelExportStylerDefaultImpl.java 继承的是这个修改后的BaseExportService.java

package cn.afterturn.easypoi.easy.excel.export.base;/*** @version v1.0* @ProjectName: easypoi-test* @ClassName: BaseExportService* @Description: 4.4.0 版本进行修改* @Author: xbx* @Date: /3/8 18:28* createImageCell(Cell, double, int, int, String, byte[]) 徐本锡 mod by xbx .03.07 生成图片的时候 图片不显示问题（实际上是最小显示了）*/import cn.afterturn.easypoi.cache.ImageCache;import cn.afterturn.easypoi.entity.SpecialSymbolsEntity;import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;import cn.afterturn.easypoi.entity.BaseTypeConstants;import cn.afterturn.easypoi.entity.PoiBaseConstants;import cn.afterturn.easypoi.excel.export.base.ExportCommonService;import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler;import cn.afterturn.easypoi.exception.excel.ExcelExportException;import cn.afterturn.easypoi.exception.excel.enums.ExcelExportEnum;import cn.afterturn.easypoi.util.PoiExcelGraphDataUtil;import cn.afterturn.easypoi.util.PoiMergeCellUtil;import cn.afterturn.easypoi.util.PoiPublicUtil;import mons.lang3.StringUtils;import mons.lang3.builder.ReflectionToStringBuilder;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFClientAnchor;import org.apache.poi.hssf.usermodel.HSSFRichTextString;import org.apache.poi.ss.usermodel.*;import org.apache.poi.xssf.streaming.SXSSFSheet;import org.apache.poi.xssf.usermodel.XSSFClientAnchor;import org.apache.poi.xssf.usermodel.XSSFRichTextString;import java.text.DecimalFormat;import java.util.*;/*** 提供POI基础操作服务** @author JueYue 6月17日 下午6:15:13*/@SuppressWarnings("unchecked")public abstract class BaseExportService extends ExportCommonService {private int currentIndex = 0;protected ExcelType type = ExcelType.XSSF;private Map<Integer, Double> statistics = new HashMap<Integer, Double>();private static final DecimalFormat DOUBLE_FORMAT = new DecimalFormat("######0.00");protected IExcelExportStyler excelExportStyler;/*** 创建 最主要的 Cells*/public int[] createCells(Drawing patriarch, int index, Object t,List<ExcelExportEntity> excelParams, Sheet sheet, Workbook workbook,short rowHeight, int cellNum) {try {ExcelExportEntity entity;Row row = sheet.getRow(index) == null ? sheet.createRow(index) : sheet.getRow(index);if (rowHeight != -1) {row.setHeight(rowHeight);}int maxHeight = 1, listMaxHeight = 1;// 合并需要合并的单元格int margeCellNum = cellNum;int indexKey = 0;if (excelParams != null && !excelParams.isEmpty()) {indexKey = createIndexCell(row, index, excelParams.get(0));}cellNum += indexKey;for (int k = indexKey, paramSize = excelParams.size(); k < paramSize; k++) {entity = excelParams.get(k);//不论数据是否为空都应该把该列的数据跳过去if (entity.getList() != null) {Collection<?> list = getListCellValue(entity, t);int tmpListHeight = 0;if (list != null && list.size() > 0) {int tempCellNum = 0;for (Object obj : list) {int[] temp = createCells(patriarch, index + tmpListHeight, obj, entity.getList(), sheet, workbook, rowHeight, cellNum);tempCellNum = temp[1];tmpListHeight += temp[0];}cellNum = tempCellNum;listMaxHeight = Math.max(listMaxHeight, tmpListHeight);} else {cellNum = cellNum + getListCellSize(entity.getList());}} else {Object value = getCellValue(entity, t);if (entity.getType() == BaseTypeConstants.STRING_TYPE) {createStringCell(row, cellNum++, value == null ? "" : value.toString(),index % 2 == 0 ? getStyles(false, entity) : getStyles(true, entity),entity);} else if (entity.getType() == BaseTypeConstants.DOUBLE_TYPE) {createDoubleCell(row, cellNum++, value == null ? "" : value.toString(),index % 2 == 0 ? getStyles(false, entity) : getStyles(true, entity),entity);} else if (entity.getType() == BaseTypeConstants.Symbol_TYPE) {createSymbolCell(row, cellNum++, value,index % 2 == 0 ? getStyles(false, entity) : getStyles(true, entity),entity);} else {createImageCell(patriarch, entity, row, cellNum++,value == null ? "" : value.toString(), t);}if (entity.isHyperlink()) {row.getCell(cellNum - 1).setHyperlink(dataHandler.getHyperlink(row.getSheet().getWorkbook().getCreationHelper(), t,entity.getName(), value));}}}maxHeight += listMaxHeight - 1;if (indexKey == 1 && excelParams.get(1).isNeedMerge()) {excelParams.get(0).setNeedMerge(true);}for (int k = indexKey, paramSize = excelParams.size(); k < paramSize; k++) {entity = excelParams.get(k);if (entity.getList() != null) {margeCellNum += entity.getList().size();} else if (entity.isNeedMerge() && maxHeight > 1) {for (int i = index + 1; i < index + maxHeight; i++) {if (sheet instanceof SXSSFSheet && i <= ((SXSSFSheet) sheet).getLastFlushedRowNum()) {continue;}if (sheet.getRow(i) == null) {try {sheet.createRow(i);} catch (Exception e) {e.printStackTrace();}}sheet.getRow(i).createCell(margeCellNum);sheet.getRow(i).getCell(margeCellNum).setCellStyle(getStyles(false, entity));}PoiMergeCellUtil.addMergedRegion(sheet, index, index + maxHeight - 1, margeCellNum, margeCellNum);margeCellNum++;}}return new int[]{maxHeight, cellNum};} catch (Exception e) {LOGGER.error("excel cell export error ,data is :{}", ReflectionToStringBuilder.toString(t));LOGGER.error(e.getMessage(), e);throw new ExcelExportException(ExcelExportEnum.EXPORT_ERROR, e);}}private void createSymbolCell(Row row, int index, Object specialSymbolsEnum, CellStyle style,ExcelExportEntity entity) {SpecialSymbolsEntity symbol = (SpecialSymbolsEntity) specialSymbolsEnum;Cell cell = row.createCell(index);Font font = cell.getSheet().getWorkbook().createFont();font.setFontName(symbol.getFont());RichTextString rtext;if (cell instanceof HSSFCell) {rtext = new HSSFRichTextString(symbol.getUnicode());rtext.applyFont(font);} else {rtext = new XSSFRichTextString(symbol.getUnicode());rtext.applyFont(font);}cell.setCellValue(rtext);if (style != null) {cell.setCellStyle(style);}}/*** 获取集合的宽度** @param list* @return*/protected int getListCellSize(List<ExcelExportEntity> list) {int cellSize = 0;for (ExcelExportEntity ee : list) {if (ee.getList() != null) {cellSize += getListCellSize(ee.getList());} else {cellSize++;}}return cellSize;}/*** 图片类型的Cell*/public void createImageCell(Drawing patriarch, ExcelExportEntity entity, Row row, int i,String imagePath, Object obj) throws Exception {Cell cell = row.createCell(i);byte[] value = null;if (entity.getExportImageType() != 1) {if (entity.getMethods() == null && entity.getMethod() == null) {value = (byte[]) PoiPublicUtil.getParamsValue(entity.getKey().toString(), obj);} else {value = (byte[]) (entity.getMethods() != null ? getFieldBySomeMethod(entity.getMethods(), obj): entity.getMethod().invoke(obj, new Object[]{}));}}createImageCell(cell, 50 * entity.getHeight(), entity.getExportImageType() == 1 ? imagePath : null, value);}/*** 图片类型的Cell*/public void createImageCell(Cell cell, double height,String imagePath, byte[] data) throws Exception {if (height > cell.getRow().getHeight()) {cell.getRow().setHeight((short) height);}ClientAnchor anchor;if (type.equals(ExcelType.HSSF)) {// x range 0-1023 y range 0-255anchor = new HSSFClientAnchor(10, 10, 1010, 245, (short) cell.getColumnIndex(), cell.getRow().getRowNum(), (short) (cell.getColumnIndex()),cell.getRow().getRowNum());} else {anchor = new XSSFClientAnchor(10, 10, 1010, 245, (short) cell.getColumnIndex(), cell.getRow().getRowNum(), (short) (cell.getColumnIndex()),cell.getRow().getRowNum());}if (StringUtils.isNotEmpty(imagePath)) {data = ImageCache.getImage(imagePath);}if (data != null) {PoiExcelGraphDataUtil.getDrawingPatriarch(cell.getSheet()).createPicture(anchor,cell.getSheet().getWorkbook().addPicture(data, getImageType(data)));}}/*** 图片类型的Cell* 徐本锡 mod by xbx .03.07 生成图片的时候 图片不显示问题（实际上是最小显示了）*/public void createImageCell(Cell cell, double height, int rowspan, int colspan,String imagePath, byte[] data) throws Exception {if (height > cell.getRow().getHeight()) {cell.getRow().setHeight((short) height);}ClientAnchor anchor;// if (type.equals(ExcelType.HSSF)) {// anchor = new HSSFClientAnchor(10, 10, 1010, 245, (short) cell.getColumnIndex(), cell.getRow().getRowNum(), (short) (cell.getColumnIndex() + colspan - 1),//cell.getRow().getRowNum() + rowspan - 1);// } else {// anchor = new XSSFClientAnchor(10, 10, 1010, 245, (short) cell.getColumnIndex(), cell.getRow().getRowNum(), (short) (cell.getColumnIndex() + colspan - 1),//cell.getRow().getRowNum() + rowspan - 1);// }if (this.type.equals(ExcelType.HSSF)) {anchor = new HSSFClientAnchor(0, 0, 0, 0, (short)cell.getColumnIndex(), cell.getRow().getRowNum(), (short)(cell.getColumnIndex() + colspan), cell.getRow().getRowNum() + rowspan);} else {anchor = new XSSFClientAnchor(0, 0, 0, 0, (short)cell.getColumnIndex(), cell.getRow().getRowNum(), (short)(cell.getColumnIndex() + colspan), cell.getRow().getRowNum() + rowspan);}if (StringUtils.isNotEmpty(imagePath)) {data = ImageCache.getImage(imagePath);}if (data != null) {PoiExcelGraphDataUtil.getDrawingPatriarch(cell.getSheet()).createPicture(anchor,cell.getSheet().getWorkbook().addPicture(data, getImageType(data)));}}private int createIndexCell(Row row, int index, ExcelExportEntity excelExportEntity) {if (excelExportEntity.getFormat() != null && excelExportEntity.getFormat().equals(PoiBaseConstants.IS_ADD_INDEX)) {createStringCell(row, 0, currentIndex + "",index % 2 == 0 ? getStyles(false, null) : getStyles(true, null), null);currentIndex = currentIndex + 1;return 1;}return 0;}/*** 创建List之后的各个Cells*/public void createListCells(Drawing patriarch, int index, int cellNum, Object obj,List<ExcelExportEntity> excelParams, Sheet sheet,Workbook workbook, short rowHeight) throws Exception {ExcelExportEntity entity;Row row;if (sheet.getRow(index) == null) {row = sheet.createRow(index);if (rowHeight != -1) {row.setHeight(rowHeight);}} else {row = sheet.getRow(index);if (rowHeight != -1) {row.setHeight(rowHeight);}}for (int k = 0, paramSize = excelParams.size(); k < paramSize; k++) {entity = excelParams.get(k);Object value = getCellValue(entity, obj);if (entity.getType() == BaseTypeConstants.STRING_TYPE) {createStringCell(row, cellNum++, value == null ? "" : value.toString(),row.getRowNum() % 2 == 0 ? getStyles(false, entity) : getStyles(true, entity),entity);if (entity.isHyperlink()) {row.getCell(cellNum - 1).setHyperlink(dataHandler.getHyperlink(row.getSheet().getWorkbook().getCreationHelper(), obj, entity.getName(),value));}} else if (entity.getType() == BaseTypeConstants.DOUBLE_TYPE) {createDoubleCell(row, cellNum++, value == null ? "" : value.toString(),index % 2 == 0 ? getStyles(false, entity) : getStyles(true, entity), entity);if (entity.isHyperlink()) {row.getCell(cellNum - 1).setHyperlink(dataHandler.getHyperlink(row.getSheet().getWorkbook().getCreationHelper(), obj, entity.getName(),value));}} else {createImageCell(patriarch, entity, row, cellNum++,value == null ? "" : value.toString(), obj);}}}/*** 创建文本类型的Cell*/public void createStringCell(Row row, int index, String text, CellStyle style,ExcelExportEntity entity) {Cell cell = row.createCell(index);if (style != null && style.getDataFormat() > 0 && style.getDataFormat() < 12) {cell.setCellValue(Double.parseDouble(text));} else {RichTextString rtext;if (cell instanceof HSSFCell) {rtext = new HSSFRichTextString(text);} else {rtext = new XSSFRichTextString(text);}cell.setCellValue(rtext);}if (style != null) {cell.setCellStyle(style);}createCellComment(row, cell, text, entity);addStatisticsData(index, text, entity);}/*** 创建数字类型的Cell*/public void createDoubleCell(Row row, int index, String text, CellStyle style,ExcelExportEntity entity) {Cell cell = row.createCell(index);if (text != null && text.length() > 0) {try {cell.setCellValue(Double.parseDouble(text));} catch (NumberFormatException e) {cell.setCellValue(text);}}if (style != null) {cell.setCellStyle(style);}createCellComment(row, cell, text, entity);addStatisticsData(index, text, entity);}/*** 创建批注** @param row* @param cell* @param text* @param entity*/private void createCellComment(Row row, Cell cell, String text, ExcelExportEntity entity) {if (commentHandler != null) {String comment = entity == null || entity.getName().equals(text)|| (entity.getGroupName() != null && entity.getGroupName().equals(text)) ?commentHandler.getComment(text) : commentHandler.getComment(entity.getName(), text);if (StringUtils.isNotBlank(comment)) {cell.setCellComment(getComment(cell, comment, commentHandler.getAuthor()));}}}/*** 获取注释对象** @param cell* @param commentText* @param author* @return*/private Comment getComment(Cell cell, String commentText, String author) {Comment comment = null;if (cell instanceof HSSFCell) {//前四个参数是坐标点,后四个参数是编辑和显示批注时的大小.comment = cell.getSheet().createDrawingPatriarch().createCellComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 3, 2, (short) 5,commentText.length() / 15 + 2));comment.setString(new HSSFRichTextString(commentText));} else {comment = cell.getSheet().createDrawingPatriarch().createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 2, (short) 5,commentText.length() / 15 + 2));comment.setString(new XSSFRichTextString(commentText));}if (StringUtils.isNotBlank(author)) {comment.setAuthor(author);}return comment;}/*** 创建统计行*/public void addStatisticsRow(CellStyle styles, Sheet sheet) {if (statistics.size() > 0) {if (LOGGER.isDebugEnabled()) {LOGGER.debug("add statistics data ,size is {}", statistics.size());}Row row = sheet.createRow(sheet.getLastRowNum() + 1);Set<Integer> keys = statistics.keySet();createStringCell(row, 0, "合计", styles, null);for (Integer key : keys) {createStringCell(row, key, DOUBLE_FORMAT.format(statistics.get(key)), styles, null);}statistics.clear();}}/*** 合计统计信息*/private void addStatisticsData(Integer index, String text, ExcelExportEntity entity) {if (entity != null && entity.isStatistics()) {Double temp = 0D;if (!statistics.containsKey(index)) {statistics.put(index, temp);}try {temp = Double.valueOf(text);} catch (NumberFormatException e) {}statistics.put(index, statistics.get(index) + temp);}}/*** 获取图片类型,设置图片插入类型** @author JueYue 11月25日*/public int getImageType(byte[] value) {String type = PoiPublicUtil.getFileExtendName(value);if ("JPG".equalsIgnoreCase(type)) {return Workbook.PICTURE_TYPE_JPEG;} else if ("PNG".equalsIgnoreCase(type)) {return Workbook.PICTURE_TYPE_PNG;}return Workbook.PICTURE_TYPE_JPEG;}private Map<Integer, int[]> getMergeDataMap(List<ExcelExportEntity> excelParams) {Map<Integer, int[]> mergeMap = new HashMap<Integer, int[]>();// 设置参数顺序,为之后合并单元格做准备int i = 0;for (ExcelExportEntity entity : excelParams) {if (entity.isMergeVertical()) {mergeMap.put(i, entity.getMergeRely());}if (entity.getList() != null) {for (ExcelExportEntity inner : entity.getList()) {if (inner.isMergeVertical()) {mergeMap.put(i, inner.getMergeRely());}i++;}} else {i++;}}return mergeMap;}/*** 获取样式*/public CellStyle getStyles(boolean needOne, ExcelExportEntity entity) {return excelExportStyler.getStyles(needOne, entity);}/*** 合并单元格*/public void mergeCells(Sheet sheet, List<ExcelExportEntity> excelParams, int titleHeight) {Map<Integer, int[]> mergeMap = getMergeDataMap(excelParams);PoiMergeCellUtil.mergeCells(sheet, mergeMap, titleHeight);}public void setCellWith(List<ExcelExportEntity> excelParams, Sheet sheet) {int index = 0;for (int i = 0; i < excelParams.size(); i++) {if (excelParams.get(i).getList() != null) {List<ExcelExportEntity> list = excelParams.get(i).getList();for (int j = 0; j < list.size(); j++) {sheet.setColumnWidth(index, (int) (256 * list.get(j).getWidth()));index++;}} else {sheet.setColumnWidth(index, (int) (256 * excelParams.get(i).getWidth()));index++;}}}public void setColumnHidden(List<ExcelExportEntity> excelParams, Sheet sheet) {int index = 0;for (int i = 0; i < excelParams.size(); i++) {if (excelParams.get(i).getList() != null) {List<ExcelExportEntity> list = excelParams.get(i).getList();for (int j = 0; j < list.size(); j++) {sheet.setColumnHidden(index, list.get(j).isColumnHidden());index++;}} else {sheet.setColumnHidden(index, excelParams.get(i).isColumnHidden());index++;}}}public void setCurrentIndex(int currentIndex) {this.currentIndex = currentIndex;}public void setExcelExportStyler(IExcelExportStyler excelExportStyler) {this.excelExportStyler = excelExportStyler;}public IExcelExportStyler getExcelExportStyler() {return excelExportStyler;}}

5.新建工具类Sd3eUtil

package cn.afterturn.easypoi.easy.util;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.util.CellRangeAddress;import org.slf4j.Logger;import org.slf4j.LoggerFactory;/*** @version v1.0* @ProjectName: easypoi-test* @ClassName: Sd3eUtil* @Description:* @Author: xbx* @Date: /3/7 16:49*/public class Sd3eUtil {private static final Logger LOGGER = LoggerFactory.getLogger(Sd3eUtil.class);/*** @Author: 徐本锡* @Date: /3/7 16:49* @param: [sheet, firstRow, lastRow, firstCol, lastCol]* @return: void* @description: list循环的时候 存在合并单元格的时候 格式错乱问题*/public static void addMergedRegionByListForEach(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {try {sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));} catch (IllegalStateException var6){//合并单元格的循环 可能存在 已经合并的再次合并 不管他} catch (Exception var6) {LOGGER.debug("发生了一次合并单元格错误,{},{},{},{}", new Integer[]{firstRow, lastRow, firstCol, lastCol});LOGGER.debug(var6.getMessage(), var6);}}}