本文实例为大家分享了Java使用POI将多个Sheet合并为一个Sheet的具体代码，供大家参考，具体内容如下
一、情景描述
最近在项目中客户提到一个新的需求，一开始是在列表查询时导出多个Excel表格，后面提到将多个Excel表格进行合并，实现一个sheet显示多个sheet内容，图示如下：
一开始：
合并后（不同表格空一行隔开）：
二、实现思路
首先，先按照生成五张表的思路来生成创建一个Workbook sourceWorkbook,然后再创建一个Workbook targetWorkbook，创建一个新的Sheet targetSheet工作表，之后将sourceWorkbook中第一个Sheet sheet1中的内容复制到该表中，再将第二个Sheet sheet2中的内容复制到targetSheet中，依次操作，复制完sourceWorkbook中全部的五张表，即可实现将多个Sheet合并为一个Sheet的操作。
三、示例代码
1.POIUtil工具类
|
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
|
package com.cdtye.itps.jjxt.model.util;import com.cdtye.itps.jjxt.model.excel.CellRangeAddressExcelVo;import org.apache.poi.ss.usermodel.*;import org.springframework.util.CollectionUtils;import java.util.List;/** * @Author Zhongks * @Description //TODO POI导出excel工具类 * @Date 17:16 2021/5/11 * @Param * @return **/public class POIUtil { /** * @Author Zhongks * @Description //TODO 拷贝sheet（表） * @Date 17:16 2021/5/11 * @Param [targetSheet, sourceSheet, targetWork, sourceWork, startRow, cellRangeAddressExcelVoList] * @return void **/ public static void copySheet(Sheet targetSheet, Sheet sourceSheet, Workbook targetWork, Workbook sourceWork, int startRow, List<CellRangeAddressExcelVo> cellRangeAddressExcelVoList) { if(targetSheet == null || sourceSheet == null || targetWork == null || sourceWork == null){ throw new IllegalArgumentException("调用PoiUtil.copySheet()方法时，targetSheet、sourceSheet、targetWork、sourceWork都不能为空，故抛出该异常！"); } //设置单元格默认宽度 targetSheet.setDefaultColumnWidth(25); //复制源表中的行 for (int i = sourceSheet.getFirstRowNum(); i <= sourceSheet.getLastRowNum(); i++) { Row sourceRow = sourceSheet.getRow(i); Row targetRow = targetSheet.createRow(i+startRow); //创建新的row if (sourceRow != null) { copyRow(targetRow, sourceRow, targetWork, sourceWork); } } //自定义合并单元格样式（若不需要进行单元格合并操作，将cellRangeAddressExcelVoList赋值为null即可） if(!CollectionUtils.isEmpty(cellRangeAddressExcelVoList)){ //合并单元格 for(CellRangeAddressExcelVo model:cellRangeAddressExcelVoList){ targetSheet.addMergedRegion(new org.apache.poi.ss.util.CellRangeAddress(model.getFirstRow(),model.getLastRow(),model.getFirstCol(),model.getLastCol())); } } } /** * @Author Zhongks * @Description //TODO 拷贝row(行) * @Date 17:17 2021/5/11 * @Param [targetRow, sourceRow, targetWork, sourceWork] * @return void **/ public static void copyRow(Row targetRow, Row sourceRow, Workbook targetWork, Workbook sourceWork) { if(targetRow == null || sourceRow == null || targetWork == null || sourceWork == null ){ throw new IllegalArgumentException("调用PoiUtil.copyRow()方法时，targetRow、sourceRow、targetWork、sourceWork、targetPatriarch都不能为空，故抛出该异常！"); } //设置行高 targetRow.setHeight(sourceRow.getHeight()); for (int i = sourceRow.getFirstCellNum(); i < sourceRow.getLastCellNum(); i++) { Cell sourceCell = sourceRow.getCell(i); Cell targetCell = null; if (sourceCell != null && sourceCell.getStringCellValue()!="") { if (targetCell == null) { targetCell = targetRow.createCell(i); } //拷贝单元格，包括内容和样式 copyCell(targetCell, sourceCell, targetWork, sourceWork); } } } /** * @Author Zhongks * @Description //TODO 拷贝cell（单元格） * @Date 17:18 2021/5/11 * @Param [targetCell, sourceCell, targetWork, sourceWork] * @return void **/ public static void copyCell(Cell targetCell, Cell sourceCell, Workbook targetWork, Workbook sourceWork) { if(targetCell == null || sourceCell == null || targetWork == null || sourceWork == null ){ throw new IllegalArgumentException("调用PoiUtil.copyCell()方法时，targetCell、sourceCell、targetWork、sourceWork都不能为空，故抛出该异常！"); } CellStyle targetCellStyle=targetWork.createCellStyle(); targetCellStyle.cloneStyleFrom(sourceCell.getCellStyle());//拷贝样式 //重新添加样式（这里可以根据你的需要重新进行单元格样式添加） /*targetCellStyle.setBorderTop(BorderStyle.THIN);//设置上边框线 targetCellStyle.setBorderLeft(BorderStyle.THIN);//设置左边框线 targetCellStyle.setBorderBottom(BorderStyle.THIN);//设置下边框线 targetCellStyle.setBorderRight(BorderStyle.THIN);//设置右边框线*/ targetCell.setCellStyle(targetCellStyle); targetCell.setCellValue(sourceCell.getStringCellValue()); }} |
2.需要合并的单元格位置信息实体
|
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
|
package com.cdtye.itps.jjxt.model.excel;import lombok.AllArgsConstructor;import lombok.Data;import lombok.experimental.Accessors;/** * @ClassName CellRangeAddressExcelVo * @Description TODO 需要合并的单元格位置信息Vo * @Author Zhongks * @Date 2021/5/11 14:09 * @Version 1.0 **/@Data@Accessors(chain = true)@AllArgsConstructorpublic class CellRangeAddressExcelVo { //起始行号 private int firstRow; //终止行号 private int lastRow; //起始列号 private int firstCol; //终止列号 private int lastCol;} |
该实体类是为了进行合并单元格操作，用来存储需要合并的单元格位置信息：
Service层代码：
|
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
|
* * @Author Zhongks * @Description //TODO excel导出 * @Date 12:25 2021/5/7 * @Param [list, response] * @return void **/ public void export(BureauDayShiftVo bureauDayShiftVo,HttpServletResponse response) { try { // 设置下载的Excel名称，以当前时间为文件后缀， String dateTime = DateUtil.formatDateString(new Date(), DateUtil.DATE_FORMAT); String fileName = "供电安全质量日交班表"+dateTime+".xlsx"; // 设置响应输出的头类型 response.setHeader("content-Type", "application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment;filename="+fileName); // excel信息部分 //供电处重点信息追踪表信息 bureauDayShiftVo.setTrackFlag(1); Map<String, Object> trackSafeQualityMap =this.getTrackSafeQualityMap(bureauDayShiftVo); //日安全质量信息表信息 bureauDayShiftVo.setTrackFlag(0); Map<String, Object> safeQualityParamsMap =this.getTrackSafeQualityMap(bureauDayShiftVo); //天窗兑现统计表 Map<String, Object> skylightCashStatisticsMap = this.getSkylightCashStatisticsMap(); //其他安全质量信息表 Map<String, Object> otherSafeQualityInfoMap = this.getOtherSafeQualityInfoMap(bureauDayShiftVo); //安全质量考核表 Map<String, Object> safeQualityAssessmentMap = this.getSafeQualityAssessmentMap(); //添加表 List<Map<String, Object>> sheetsList = new ArrayList<>(); sheetsList.add(trackSafeQualityMap); sheetsList.add(safeQualityParamsMap); sheetsList.add(skylightCashStatisticsMap); sheetsList.add(otherSafeQualityInfoMap); sheetsList.add(safeQualityAssessmentMap); List<Map<String, Object>> sourceSheetsList = new ArrayList<>(); //创建excel文件的方法 Workbook sourceWorkbook = ExcelExportUtil.exportExcel(sheetsList, ExcelType.HSSF); Workbook targetWorkbook = ExcelExportUtil.exportExcel(sourceSheetsList, ExcelType.HSSF); Workbook workbook = this.mergeWorkSheet(targetWorkbook, sourceWorkbook); //通过response输出流直接输入给客户端 ServletOutputStream outputStream = response.getOutputStream(); workbook.write(outputStream); outputStream.flush(); outputStream.close(); } catch (Exception e) { e.printStackTrace(); } } /** * @Author Zhongks * @Description //TODO 返回重点追踪以及非重点追踪excel信息 * @Date 9:31 2021/5/8 * @Param [bureauDayShiftVo] * @return java.util.List<java.util.Map<java.lang.String,java.lang.Object>> **/ public Map<String, Object> getTrackSafeQualityMap(BureauDayShiftVo bureauDayShiftVo){ List<BureauDayShiftExcelVo> exportList = new LinkedList<>(); List<Map<String, Object>> allTrackSafeQualityList = this.getAllTrackSafeQualityList(bureauDayShiftVo); //封装数据 allTrackSafeQualityList.forEach(map -> { String basicInformation="单位："+map.get("unitDeptName")+"\n"+ "线别："+map.get("lineName")+"\n"+ "所亭："+map.get("bdsSubstationName")+"\n"+ "开关号："+map.get("switchNo")+"\n"+ "故障地点："+map.get("faultPlace")+"\n"+ "发生时间："+DateUtil.formatDateString(map.get("stopDate"), DateUtil.DATE_FORMAT)+"\n"+ "停时（分钟）："+map.get("stopMinute")+"\n"+ "天气："+map.get("weatherInfo")+"\n"+ "专业分类："+map.get("faultMajorName")+"\n"; String segmentAnalysis="单位："+map.get("unitDeptName")+"\n"+ "单位：详见分析报告"+"\n"; String isTrack=""; if(bureauDayShiftVo.getTrackFlag()==0){ isTrack="否"; }else{ isTrack="是"; } String review="科室："+map.get("trackUnitDeptName")+"\n"+ "问题类别："+map.get("faultCategoryConfigName")+"\n"+ "定责考核："+map.get("dutyType")+"\n"+ "审核结果："+map.get("switchNo")+"\n"+ "重点追踪："+isTrack+"\n"; BureauDayShiftExcelVo bureauDayShiftExcelVo =new BureauDayShiftExcelVo( DateUtil.formatDateString(map.get("inputDate"), DateUtil.DATE_FORMAT), basicInformation, (String)map.get("faultDescription"), (String)map.get("reportType"), segmentAnalysis, review, map.get("safeQualityState").toString(), String.valueOf(bureauDayShiftVo.getTrackFlag())); exportList.add(bureauDayShiftExcelVo); }); ExportParams exportParams = new ExportParams(); //设置边框样式// exportParams.setStyle(ExcelStyleType.BORDER.getClazz()); // 设置sheet的名称 if(bureauDayShiftVo.getTrackFlag()==0){ exportParams.setSheetName("日安全质量信息"); }else{ exportParams.setSheetName("供电处重点追踪信息"); } Map<String, Object> map = new HashMap<>(); // title的参数为ExportParams类型，目前仅仅在ExportParams中设置了sheetName map.put("title", exportParams); // 模版导出对应得实体类型，即包含了List的对象 map.put("entity", BureauDayShiftExcelVo.class); // sheet中要填充得数据 map.put("data", exportList); return map; } /** * @Author Zhongks * @Description //TODO 返回天窗兑现统计excel信息 * @Date 10:59 2021/5/8 * @Param [] * @return java.util.Map<java.lang.String,java.lang.Object> **/ public Map<String, Object> getSkylightCashStatisticsMap(){ List<BureauSkylightCashStatisticsExcelVo> exportList = new LinkedList<>(); //ToDo 得到天窗兑现统计列表数据并进行封装 //示例数据 BureauSkylightCashStatisticsCommonExcelVo applicationExcelVo=new BureauSkylightCashStatisticsCommonExcelVo("申请供电类","申请非供电类"); BureauSkylightCashStatisticsCommonExcelVo applicationTimeExcelVo=new BureauSkylightCashStatisticsCommonExcelVo("申请时间供电类","申请时间非供电类"); BureauSkylightCashStatisticsCommonExcelVo getTimeExcelVo=new BureauSkylightCashStatisticsCommonExcelVo("给点时间供电类","给点时间非供电类"); BureauSkylightCashStatisticsCommonExcelVo workTimeExcelVo=new BureauSkylightCashStatisticsCommonExcelVo(null,null); BureauSkylightCashStatisticsExcelVo bureauSkylightCashStatisticsExcelVo =new BureauSkylightCashStatisticsExcelVo("怀化供电段","高铁","沪昆高速线", applicationExcelVo,"取消","10","10",applicationTimeExcelVo,getTimeExcelVo,workTimeExcelVo,"天窗取消原因"); exportList.add(bureauSkylightCashStatisticsExcelVo); exportList.add(bureauSkylightCashStatisticsExcelVo); exportList.add(bureauSkylightCashStatisticsExcelVo); //供电处重点追踪信息表 ExportParams exportParams = new ExportParams(); //设置边框样式// exportParams.setStyle(ExcelStyleType.BORDER.getClazz()); // 设置sheet的名称 exportParams.setSheetName("天窗兑现统计"); Map<String, Object> map = new HashMap<>(); // title的参数为ExportParams类型，目前仅仅在ExportParams中设置了sheetName map.put("title", exportParams); // 模版导出对应得实体类型，即包含了List的对象 map.put("entity", BureauSkylightCashStatisticsExcelVo.class); // sheet中要填充得数据 map.put("data", exportList); return map; } /** * @Author Zhongks * @Description //TODO 返回其他安全信息excel信息 * @Date 11:01 2021/5/8 * @Param [] * @return java.util.Map<java.lang.String,java.lang.Object> **/ public Map<String, Object> getOtherSafeQualityInfoMap(BureauDayShiftVo bureauDayShiftVo){ List<BureauOtherSafeQualityInfoExcelVo> exportList = new LinkedList<>(); //ToDo 得到其他安全信息列表数据并进行封装 BureauSafeQualityOtherInfoVo bureauSafeQualityOtherInfoVo=new BureauSafeQualityOtherInfoVo(); bureauSafeQualityOtherInfoVo.setStartDate(bureauDayShiftVo.getStartDate()); bureauSafeQualityOtherInfoVo.setEndDate(bureauDayShiftVo.getEndDate()); List<Map<String, Object>> list = bureauSafeQualityOtherInfoService.findList(bureauSafeQualityOtherInfoVo); list.forEach(map->{ BureauOtherSafeQualityInfoExcelVo otherSafeQualityInfoExcelVo=new BureauOtherSafeQualityInfoExcelVo( DateUtil.formatDateString(map.get("createDatetime"), DateUtil.DATE_FORMAT), (String)map.get("description"), (String)map.get("inputStaffName"), DateUtil.formatDateString(map.get("createDatetime"), DateUtil.DATE_FORMAT), (String)map.get("modifyStaffName"), DateUtil.formatDateString(map.get("updateDatetime"), DateUtil.DATE_FORMAT) ); exportList.add(otherSafeQualityInfoExcelVo); }); //供电处重点追踪信息表 ExportParams exportParams = new ExportParams(); //设置边框样式// exportParams.setStyle(ExcelStyleType.BORDER.getClazz()); // 设置sheet的名称 exportParams.setSheetName("其他安全信息"); Map<String, Object> map = new HashMap<>(); // title的参数为ExportParams类型，目前仅仅在ExportParams中设置了sheetName map.put("title", exportParams); // 模版导出对应得实体类型，即包含了List的对象 map.put("entity", BureauOtherSafeQualityInfoExcelVo.class); // sheet中要填充得数据 map.put("data", exportList); return map; } /** * @Author Zhongks * @Description //TODO 返回安全质量考核excel信息 * @Date 11:04 2021/5/8 * @Param [] * @return java.util.Map<java.lang.String,java.lang.Object> **/ public Map<String, Object> getSafeQualityAssessmentMap(){ List<BureauSafeQualityAssessmentExcelVo> exportList = new LinkedList<>(); //ToDo 得到安全质量考核列表数据并进行封装 //供电处重点追踪信息表 ExportParams exportParams = new ExportParams(); //设置边框样式// exportParams.setStyle(ExcelStyleType.BORDER.getClazz()); // 设置sheet的名称 exportParams.setSheetName("安全质量考核"); Map<String, Object> map = new HashMap<>(); // title的参数为ExportParams类型，目前仅仅在ExportParams中设置了sheetName map.put("title", exportParams); // 模版导出对应得实体类型，即包含了List的对象 map.put("entity", BureauSafeQualityAssessmentExcelVo.class); // sheet中要填充得数据 map.put("data", exportList); return map; } /** * @Author Zhongks * @Description //TODO 合并sheet * @Date 10:39 2021/5/11 * @Param [targetWorkbook, sourceWorkbook] * @return org.apache.poi.ss.usermodel.Workbook **/ public static Workbook mergeWorkSheet(Workbook targetWorkbook, Workbook sourceWorkbook){ try{ //第一个sheet Sheet firstSourceSheet=sourceWorkbook.getSheetAt(0); //获得第一个sheet总行数 int firstSourceSheetLen=firstSourceSheet.getPhysicalNumberOfRows(); //获取第几个工作表 Sheet secondSourceSheet= sourceWorkbook.getSheetAt(1); int secondSourceSheetLen=secondSourceSheet.getPhysicalNumberOfRows(); Sheet thirdSourceSheet=sourceWorkbook.getSheetAt(2); int thirdSourceSheetLen=thirdSourceSheet.getPhysicalNumberOfRows(); Sheet fourSourceSheet=sourceWorkbook.getSheetAt(3); int fourSourceSheetLen=fourSourceSheet.getPhysicalNumberOfRows(); Sheet fiveSourceSheet=sourceWorkbook.getSheetAt(4); //表合并后新表名称 Sheet targetSheet = targetWorkbook.createSheet("安全质量信息日交班表"); //表合并（根据startRow来控制各个表之间的距离，这里为空一行） POIUtil.copySheet(targetSheet, firstSourceSheet, targetWorkbook, sourceWorkbook,0,null); POIUtil.copySheet(targetSheet, secondSourceSheet, targetWorkbook, sourceWorkbook,firstSourceSheetLen+1,null); int thirdSourceSheetColLen=thirdSourceSheet.getRow(0).getPhysicalNumberOfCells(); //得到需要合并单元格的坐标列表，row与col都从0开始计算 List<CellRangeAddressExcelVo> cellRangeAddressExcelVoList = getCellRangeAddressExcelVoList(firstSourceSheetLen+secondSourceSheetLen+2, thirdSourceSheetColLen); //第三张表需要进行合并单元格操作 POIUtil.copySheet(targetSheet, thirdSourceSheet, targetWorkbook, sourceWorkbook,firstSourceSheetLen+secondSourceSheetLen+2,cellRangeAddressExcelVoList); POIUtil.copySheet(targetSheet, fourSourceSheet, targetWorkbook, sourceWorkbook,firstSourceSheetLen+secondSourceSheetLen+thirdSourceSheetLen+3,null); POIUtil.copySheet(targetSheet, fiveSourceSheet, targetWorkbook, sourceWorkbook,firstSourceSheetLen+secondSourceSheetLen+thirdSourceSheetLen+fourSourceSheetLen+4,null); return targetWorkbook; }catch (Exception e){ log.error("Workbook合并出错",e); return null; } } /** * @Author Zhongks * @Description //TODO 根据表格场景自定义需要返回合并的单元格位置坐标（注意：row与col都从0开始计算） * @Date 14:23 2021/5/11 * @Param [row, col] * @return java.util.List<com.cdtye.itps.jjxt.model.excel.CellRangeAddressExcelVo> **/ public static List<CellRangeAddressExcelVo> getCellRangeAddressExcelVoList(int row,int col){ //合并单元格坐标位置 List<CellRangeAddressExcelVo> list=new LinkedList<>(); for(int i=0;i<15;i++){ if(i<7){ CellRangeAddressExcelVo cellRangeAddressExcelVo=new CellRangeAddressExcelVo(row,row+1,i,i); list.add(cellRangeAddressExcelVo); }else{ CellRangeAddressExcelVo cellRangeAddressExcelVo=new CellRangeAddressExcelVo(row,row,i,i+1); list.add(cellRangeAddressExcelVo); i++; } } return list; } |
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持服务器之家。
原文链接：https://blog.csdn.net/weixin_44009447/article/details/116708514
