为什么80%的码农都做不了架构师?>>>
前言:apache提供的poi功能确实比较强大,但是不明白为什么没有相应的方法实现不同excel文件中sheet的复制功能。这也是本文整理PoiUtil工具类的初衷。网上有相关的解决方案,在参考了网上诸多的解决方案、示例代码之后,就有了该工具类,特别感谢各位前辈,让我能够站在巨人的肩膀上,用拳头抠抠鼻屎。废话不多说了,下面是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
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
package com.poi.extend; import java.util.HashMap; import java.util.Map; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFClientAnchor; import org.apache.poi.hssf.usermodel.HSSFComment; import org.apache.poi.hssf.usermodel.HSSFPalette; import org.apache.poi.hssf.usermodel.HSSFPatriarch; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.CellRangeAddress; import org.apache.poi.hssf.util.HSSFColor; /** * POI工具类 功能点: * 1、实现excel的sheet复制,复制的内容包括单元的内容、样式、注释 * 2、setMForeColor修改HSSFColor.YELLOW的色值,setMBorderColor修改PINK的色值 * * @author Administrator */ public final class PoiUtil { /** * 功能:拷贝sheet * 实际调用 copySheet(targetSheet, sourceSheet, targetWork, sourceWork, true) * @param targetSheet * @param sourceSheet * @param targetWork * @param sourceWork */ public static void copySheet(HSSFSheet targetSheet, HSSFSheet sourceSheet, HSSFWorkbook targetWork, HSSFWorkbook sourceWork) throws Exception{ if(targetSheet == null || sourceSheet == null || targetWork == null || sourceWork == null){ throw new IllegalArgumentException("调用PoiUtil.copySheet()方法时,targetSheet、sourceSheet、targetWork、sourceWork都不能为空,故抛出该异常!"); } copySheet(targetSheet, sourceSheet, targetWork, sourceWork, true); } /** * 功能:拷贝sheet * @param targetSheet * @param sourceSheet * @param targetWork * @param sourceWork * @param copyStyle boolean 是否拷贝样式 */ public static void copySheet(HSSFSheet targetSheet, HSSFSheet sourceSheet, HSSFWorkbook targetWork, HSSFWorkbook sourceWork, boolean copyStyle)throws Exception { if(targetSheet == null || sourceSheet == null || targetWork == null || sourceWork == null){ throw new IllegalArgumentException("调用PoiUtil.copySheet()方法时,targetSheet、sourceSheet、targetWork、sourceWork都不能为空,故抛出该异常!"); } //复制源表中的行 int maxColumnNum = 0; Map styleMap = (copyStyle) ? new HashMap() : null; HSSFPatriarch patriarch = targetSheet.createDrawingPatriarch(); //用于复制注释 for (int i = sourceSheet.getFirstRowNum(); i <= sourceSheet.getLastRowNum(); i++) { HSSFRow sourceRow = sourceSheet.getRow(i); HSSFRow targetRow = targetSheet.createRow(i); if (sourceRow != null) { copyRow(targetRow, sourceRow, targetWork, sourceWork,patriarch, styleMap); if (sourceRow.getLastCellNum() > maxColumnNum) { maxColumnNum = sourceRow.getLastCellNum(); } } } //复制源表中的合并单元格 mergerRegion(targetSheet, sourceSheet); //设置目标sheet的列宽 for (int i = 0; i <= maxColumnNum; i++) { targetSheet.setColumnWidth(i, sourceSheet.getColumnWidth(i)); } } /** * 功能:拷贝row * @param targetRow * @param sourceRow * @param styleMap * @param targetWork * @param sourceWork * @param targetPatriarch */ public static void copyRow(HSSFRow targetRow, HSSFRow sourceRow, HSSFWorkbook targetWork, HSSFWorkbook sourceWork,HSSFPatriarch targetPatriarch, Map styleMap) throws Exception { if(targetRow == null || sourceRow == null || targetWork == null || sourceWork == null || targetPatriarch == null){ throw new IllegalArgumentException("调用PoiUtil.copyRow()方法时,targetRow、sourceRow、targetWork、sourceWork、targetPatriarch都不能为空,故抛出该异常!"); } //设置行高 targetRow.setHeight(sourceRow.getHeight()); for (int i = sourceRow.getFirstCellNum(); i <= sourceRow.getLastCellNum(); i++) { HSSFCell sourceCell = sourceRow.getCell(i); HSSFCell targetCell = targetRow.getCell(i); if (sourceCell != null) { if (targetCell == null) { targetCell = targetRow.createCell(i); } //拷贝单元格,包括内容和样式 copyCell(targetCell, sourceCell, targetWork, sourceWork, styleMap); //拷贝单元格注释 copyComment(targetCell,sourceCell,targetPatriarch); } } } /** * 功能:拷贝cell,依据styleMap是否为空判断是否拷贝单元格样式 * @param targetCell 不能为空 * @param sourceCell 不能为空 * @param targetWork 不能为空 * @param sourceWork 不能为空 * @param styleMap 可以为空 */ public static void copyCell(HSSFCell targetCell, HSSFCell sourceCell, HSSFWorkbook targetWork, HSSFWorkbook sourceWork,Map styleMap) { if(targetCell == null || sourceCell == null || targetWork == null || sourceWork == null ){ throw new IllegalArgumentException("调用PoiUtil.copyCell()方法时,targetCell、sourceCell、targetWork、sourceWork都不能为空,故抛出该异常!"); } //处理单元格样式 if(styleMap != null){ if (targetWork == sourceWork) { targetCell.setCellStyle(sourceCell.getCellStyle()); } else { String stHashCode = "" + sourceCell.getCellStyle().hashCode(); HSSFCellStyle targetCellStyle = (HSSFCellStyle) styleMap .get(stHashCode); if (targetCellStyle == null) { targetCellStyle = targetWork.createCellStyle(); targetCellStyle.cloneStyleFrom(sourceCell.getCellStyle()); styleMap.put(stHashCode, targetCellStyle); } targetCell.setCellStyle(targetCellStyle); } } //处理单元格内容 switch (sourceCell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: targetCell.setCellValue(sourceCell.getRichStringCellValue()); break; case HSSFCell.CELL_TYPE_NUMERIC: targetCell.setCellValue(sourceCell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_BLANK: targetCell.setCellType(HSSFCell.CELL_TYPE_BLANK); break; case HSSFCell.CELL_TYPE_BOOLEAN: targetCell.setCellValue(sourceCell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_ERROR: targetCell.setCellErrorValue(sourceCell.getErrorCellValue()); break; case HSSFCell.CELL_TYPE_FORMULA: targetCell.setCellFormula(sourceCell.getCellFormula()); break; default: break; } } /** * 功能:拷贝comment * @param targetCell * @param sourceCell * @param targetPatriarch */ public static void copyComment(HSSFCell targetCell,HSSFCell sourceCell,HSSFPatriarch targetPatriarch)throws Exception{ if(targetCell == null || sourceCell == null || targetPatriarch == null){ throw new IllegalArgumentException("调用PoiUtil.copyCommentr()方法时,targetCell、sourceCell、targetPatriarch都不能为空,故抛出该异常!"); } //处理单元格注释 HSSFComment comment = sourceCell.getCellComment(); if(comment != null){ HSSFComment newComment = targetPatriarch.createComment(new HSSFClientAnchor()); newComment.setAuthor(comment.getAuthor()); newComment.setColumn(comment.getColumn()); newComment.setFillColor(comment.getFillColor()); newComment.setHorizontalAlignment(comment.getHorizontalAlignment()); newComment.setLineStyle(comment.getLineStyle()); newComment.setLineStyleColor(comment.getLineStyleColor()); newComment.setLineWidth(comment.getLineWidth()); newComment.setMarginBottom(comment.getMarginBottom()); newComment.setMarginLeft(comment.getMarginLeft()); newComment.setMarginTop(comment.getMarginTop()); newComment.setMarginRight(comment.getMarginRight()); newComment.setNoFill(comment.isNoFill()); newComment.setRow(comment.getRow()); newComment.setShapeType(comment.getShapeType()); newComment.setString(comment.getString()); newComment.setVerticalAlignment(comment.getVerticalAlignment()); newComment.setVisible(comment.isVisible()); targetCell.setCellComment(newComment); } } /** * 功能:复制原有sheet的合并单元格到新创建的sheet * * @param sheetCreat * @param sourceSheet */ public static void mergerRegion(HSSFSheet targetSheet, HSSFSheet sourceSheet)throws Exception { if(targetSheet == null || sourceSheet == null){ throw new IllegalArgumentException("调用PoiUtil.mergerRegion()方法时,targetSheet或者sourceSheet不能为空,故抛出该异常!"); } for (int i = 0; i < sourceSheet.getNumMergedRegions(); i++) { CellRangeAddress oldRange = sourceSheet.getMergedRegion(i); CellRangeAddress newRange = new CellRangeAddress( oldRange.getFirstRow(), oldRange.getLastRow(), oldRange.getFirstColumn(), oldRange.getLastColumn()); targetSheet.addMergedRegion(newRange); } } /** * 功能:重新定义HSSFColor.YELLOW的色值 * * @param workbook * @return */ public static HSSFColor setMForeColor(HSSFWorkbook workbook) { HSSFPalette palette = workbook.getCustomPalette(); HSSFColor hssfColor = null; // byte[] rgb = { (byte) 221, (byte) 241, (byte) 255 }; // try { // hssfColor = palette.findColor(rgb[0], rgb[1], rgb[2]); // if (hssfColor == null) { // palette.setColorAtIndex(HSSFColor.YELLOW.index, rgb[0], rgb[1], // rgb[2]); // hssfColor = palette.getColor(HSSFColor.YELLOW.index); // } // } catch (Exception e) { // e.printStackTrace(); // } // return hssfColor; // } /** * 功能:重新定义HSSFColor.PINK的色值 * * @param workbook * @return */ public static HSSFColor setMBorderColor(HSSFWorkbook workbook) { HSSFPalette palette = workbook.getCustomPalette(); HSSFColor hssfColor = null; byte[] rgb = { (byte) 0, (byte) 128, (byte) 192 }; try { hssfColor = palette.findColor(rgb[0], rgb[1], rgb[2]); if (hssfColor == null) { palette.setColorAtIndex(HSSFColor.PINK.index, rgb[0], rgb[1], rgb[2]); hssfColor = palette.getColor(HSSFColor.PINK.index); } } catch (Exception e) { e.printStackTrace(); } return hssfColor; } }
关于工具类的使用,可以简单的调用copySheet方法完成sheet的复制。
注意:整理该工具类用到的poi版本是 3.2-FINAL-20081019,jdk的版本是1.4
转载于:https://my.oschina.net/psuyun/blog/157990
最后
以上就是拉长画板最近收集整理的关于PoiUtil.java 用于excel间sheet复制的全部内容,更多相关PoiUtil.java内容请搜索靠谱客的其他文章。
本图文内容来源于网友提供,作为学习参考使用,或来自网络收集整理,版权属于原作者所有。
发表评论 取消回复