poi之----easypoi 模板导出图片不显示，循环指令中合并单元格格式错乱问题解决1.加载模板数据导出语句，图片替换成自己的图片，代码种的图你们那里访问不到的2.获取 cn.afterturn.easypoi.excel.ExcelExportUtil3.修改cn.afterturn.easypoi.excel.export.template.ExcelExportOfTemplateUtil4.修改cn.afterturn.easypoi.excel.export.base.BaseEx

154 阅读 0 评论 102 点赞

我是靠谱客的博主香蕉大神，这篇文章主要介绍poi之----easypoi 模板导出图片不显示，循环指令中合并单元格格式错乱问题解决1.加载模板数据导出语句，图片替换成自己的图片，代码种的图你们那里访问不到的2.获取 cn.afterturn.easypoi.excel.ExcelExportUtil3.修改cn.afterturn.easypoi.excel.export.template.ExcelExportOfTemplateUtil4.修改cn.afterturn.easypoi.excel.export.base.BaseEx，现在分享给大家，希望可以做个参考。

我是在官方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();方法

复制代码

1
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>();

@Test
	public 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","2021-08-10");
			listMaintenanceRecordM.get(i).put("maintenancePlan0","计划是什么0："+String.valueOf(i));
			listMaintenanceRecordM.get(i).put("maintenanceYear1","2021-08-11");
			listMaintenanceRecordM.get(i).put("maintenancePlan1","计划是什么1："+String.valueOf(i));
			listMaintenanceRecordM.get(i).put("maintenanceYear2","2021-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/2021/G206+%E6%BD%8D%E5%9D%8A+K308-%E4%B8%8A%E8%A1%8C-1%E8%BD%A6%E9%81%93-20210603095007/PAVASSETINFO/Camera00/IMG0003/C00-002960-002959.JPG",800,400);
				imageRoadSurfacePicture = new ImageEntity("http://172.24.3.222:9000/roadimage/2021/G206+%E6%BD%8D%E5%9D%8A+K308-%E4%B8%8A%E8%A1%8C-1%E8%BD%A6%E9%81%93-20210603095007/PAVASSETINFO/Camera00/IMG0003/C00-002960-002959.JPG",800,400);
				imageCoreSamplesPicture = new ImageEntity("http://172.24.3.222:9000/roadimage/2021/G206+%E6%BD%8D%E5%9D%8A+K308-%E4%B8%8A%E8%A1%8C-1%E8%BD%A6%E9%81%93-20210603095007/PAVASSETINFO/Camera00/IMG0003/C00-002960-002959.JPG",800,400);
				imageCorePitPicture = new ImageEntity("http://172.24.3.222:9000/roadimage/2021/G206+%E6%BD%8D%E5%9D%8A+K308-%E4%B8%8A%E8%A1%8C-1%E8%BD%A6%E9%81%93-20210603095007/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),"2022-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",  "");//交通荷载	AADTT

valueXbx.put("pqi",  "");//pqi
		valueXbx.put("pci",  "");//pci
		valueXbx.put("rqi",  "");//rqi
		valueXbx.put("rdi",  "");//rdi
		valueXbx.put("pssi",  "");//pssi
		valueXbx.put("pssi",  "");//弯沉
		valueXbx.put("pwi",  "");//pwi
		valueXbx.put("sri",  "");//sri
		valueXbx.put("pbi",  "");//pbi
		valueXbx.put("listMaintenanceRecordM",listMaintenanceRecordM);
		valueXbx.put("listCoreDrillingM",listCoreDrillingM);
		ImageEntity image = new ImageEntity("http://172.24.3.222:9000/roadimage/2021/G206+%E6%BD%8D%E5%9D%8A+K308-%E4%B8%8A%E8%A1%8C-1%E8%BD%A6%E9%81%93-20210603095007/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/2021/G206+%E6%BD%8D%E5%9D%8A+K308-%E4%B8%8A%E8%A1%8C-1%E8%BD%A6%E9%81%93-20210603095007/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();

}

@Test
	public 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();

}

@Test
	public 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();

}

@Before
	public 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);
	}

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
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>();

	@Test
	public 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","2021-08-10");
			listMaintenanceRecordM.get(i).put("maintenancePlan0","计划是什么0："+String.valueOf(i));
			listMaintenanceRecordM.get(i).put("maintenanceYear1","2021-08-11");
			listMaintenanceRecordM.get(i).put("maintenancePlan1","计划是什么1："+String.valueOf(i));
			listMaintenanceRecordM.get(i).put("maintenanceYear2","2021-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/2021/G206+%E6%BD%8D%E5%9D%8A+K308-%E4%B8%8A%E8%A1%8C-1%E8%BD%A6%E9%81%93-20210603095007/PAVASSETINFO/Camera00/IMG0003/C00-002960-002959.JPG",800,400);
				imageRoadSurfacePicture = new ImageEntity("http://172.24.3.222:9000/roadimage/2021/G206+%E6%BD%8D%E5%9D%8A+K308-%E4%B8%8A%E8%A1%8C-1%E8%BD%A6%E9%81%93-20210603095007/PAVASSETINFO/Camera00/IMG0003/C00-002960-002959.JPG",800,400);
				imageCoreSamplesPicture = new ImageEntity("http://172.24.3.222:9000/roadimage/2021/G206+%E6%BD%8D%E5%9D%8A+K308-%E4%B8%8A%E8%A1%8C-1%E8%BD%A6%E9%81%93-20210603095007/PAVASSETINFO/Camera00/IMG0003/C00-002960-002959.JPG",800,400);
				imageCorePitPicture = new ImageEntity("http://172.24.3.222:9000/roadimage/2021/G206+%E6%BD%8D%E5%9D%8A+K308-%E4%B8%8A%E8%A1%8C-1%E8%BD%A6%E9%81%93-20210603095007/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),"2022-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",  "");//交通荷载	AADTT

		valueXbx.put("pqi",  "");//pqi
		valueXbx.put("pci",  "");//pci
		valueXbx.put("rqi",  "");//rqi
		valueXbx.put("rdi",  "");//rdi
		valueXbx.put("pssi",  "");//pssi
		valueXbx.put("pssi",  "");//弯沉
		valueXbx.put("pwi",  "");//pwi
		valueXbx.put("sri",  "");//sri
		valueXbx.put("pbi",  "");//pbi
		valueXbx.put("listMaintenanceRecordM",listMaintenanceRecordM);
		valueXbx.put("listCoreDrillingM",listCoreDrillingM);
		ImageEntity image = new ImageEntity("http://172.24.3.222:9000/roadimage/2021/G206+%E6%BD%8D%E5%9D%8A+K308-%E4%B8%8A%E8%A1%8C-1%E8%BD%A6%E9%81%93-20210603095007/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/2021/G206+%E6%BD%8D%E5%9D%8A+K308-%E4%B8%8A%E8%A1%8C-1%E8%BD%A6%E9%81%93-20210603095007/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();

	}

	@Test
	public 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();

	}


	@Test
	public 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();

	}

	@Before
	public 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

复制代码

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

复制代码

package com.ruoyi.framework.easypoi.excel;
/**
 * @version v1.0
 * @ProjectName: road
 * @ClassName: ExcelExportUtil
 * @Description: 4.4.0 版本进行修改
 * @Author: xbx
 * @Date: 2022/3/4 10:59
 * 徐本锡 mod by xbx 2022.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
 * 2013-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
     */
    @Deprecated
    public 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);
    }

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
package com.ruoyi.framework.easypoi.excel;
/**
 * @version v1.0
 * @ProjectName: road
 * @ClassName: ExcelExportUtil
 * @Description: 4.4.0 版本进行修改
 * @Author: xbx
 * @Date: 2022/3/4 10:59
 * 徐本锡 mod by xbx 2022.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
 * 2013-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
     */
    @Deprecated
    public 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: 2022/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 2022.03.07  list循环遍历的时候  存在合并单元格情况  导致格式错乱
 *
 * getLoopEachParams(List<ExcelForEachParams>, int, String) 徐本锡 mod by xbx 2022.03.07  解决一对多  row循环中，row元素中包含list并且合并单元格时，报空的错误
 *  *  * 徐本锡 addListDataToExcel(Cell, Map<String, Object>, String);  《&INDEX& 表示循环中的序号,自动添加》 失效问题修改： 增加indexColumn.addConstValue(1); 2022-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 org.apache.commons.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
 * 2013-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);
        String      targetId = 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 2022.03.07  list循环遍历的时候  存在合并单元格情况  导致格式错乱
     */
    private void addListDataToExcel(Cell cell, Map<String, Object> map,
                                    String name) throws Exception {

//mod by xbx
        // 每次利用foreach循环输出数据时，重新处理 mergedRegionHelper
        // 原因：如果存在多个循环，前面循环时，下面的模板指令所在单元格的行号会发生变化，此时 mergedRegionHelper 中的缓存没有发生相应变化，需要重新获取一下 mergedRegionHelper
        mergedRegionHelper = 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];
        Row                row         = null;
        int                rowIndex    = 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;
        int               maxHeight = 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);
            int          oldSheetNum  = 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 2022.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 2022.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;
        int                loopSize = 1;
        int                loopCi   = 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);

//mod by xbx
                        Sd3eUtil.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;
//        int                loopSize = 1;
//        int                loopCi   = 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 2022.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 2022.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);
    }

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
package cn.afterturn.easypoi.easy.excel.export.template;
/**
 * @version v1.0
 * @ProjectName: road
 * @ClassName: ExcelExportOfTemplateUtil
 * @Description: 4.4.0 版本进行修改
 * @Author: xbx
 * @Date: 2022/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 2022.03.07  list循环遍历的时候  存在合并单元格情况  导致格式错乱
 *
 * getLoopEachParams(List<ExcelForEachParams>, int, String) 徐本锡 mod by xbx 2022.03.07  解决一对多  row循环中，row元素中包含list并且合并单元格时，报空的错误
 *  *  * 徐本锡 addListDataToExcel(Cell, Map<String, Object>, String);  《&INDEX& 表示循环中的序号,自动添加》 失效问题修改： 增加indexColumn.addConstValue(1); 2022-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 org.apache.commons.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
 * 2013-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);
        String      targetId = 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 2022.03.07  list循环遍历的时候  存在合并单元格情况  导致格式错乱
     */
    private void addListDataToExcel(Cell cell, Map<String, Object> map,
                                    String name) throws Exception {

        //mod by xbx
        // 每次利用foreach循环输出数据时，重新处理 mergedRegionHelper
        // 原因：如果存在多个循环，前面循环时，下面的模板指令所在单元格的行号会发生变化，此时 mergedRegionHelper 中的缓存没有发生相应变化，需要重新获取一下 mergedRegionHelper
        mergedRegionHelper = 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];
        Row                row         = null;
        int                rowIndex    = 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;
        int               maxHeight = 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);
            int          oldSheetNum  = 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 2022.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 2022.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;
        int                loopSize = 1;
        int                loopCi   = 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);

                        //mod by xbx
                        Sd3eUtil.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;
//        int                loopSize = 1;
//        int                loopCi   = 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 2022.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 2022.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: 2022/3/8 18:28
 * createImageCell(Cell, double, int, int, String, byte[]) 徐本锡 mod by xbx 2022.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 org.apache.commons.lang3.StringUtils;
import org.apache.commons.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 2014年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-255
            anchor = 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 2022.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 2013年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;
    }

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
package cn.afterturn.easypoi.easy.excel.export.base;

/**
 * @version v1.0
 * @ProjectName: easypoi-test
 * @ClassName: BaseExportService
 * @Description: 4.4.0 版本进行修改
 * @Author: xbx
 * @Date: 2022/3/8 18:28
 * createImageCell(Cell, double, int, int, String, byte[]) 徐本锡 mod by xbx 2022.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 org.apache.commons.lang3.StringUtils;
import org.apache.commons.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 2014年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-255
            anchor = 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 2022.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 2013年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: 2022/3/7 16:49
 */
public class Sd3eUtil {
    private static final Logger LOGGER = LoggerFactory.getLogger(Sd3eUtil.class);

/**
     * @Author: 徐本锡
     * @Date: 2022/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);
        }

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
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: 2022/3/7 16:49
 */
public class Sd3eUtil {
    private static final Logger LOGGER = LoggerFactory.getLogger(Sd3eUtil.class);

    /**
     * @Author: 徐本锡
     * @Date: 2022/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);
        }

    }

}