PoiUtil.java 用于excel间sheet复制

80 阅读 0 评论 53 点赞

我是靠谱客的博主拉长画板，这篇文章主要介绍PoiUtil.java 用于excel间sheet复制，现在分享给大家，希望可以做个参考。

为什么80%的码农都做不了架构师？>>>

前言：apache提供的poi功能确实比较强大，但是不明白为什么没有相应的方法实现不同excel文件中sheet的复制功能。这也是本文整理PoiUtil工具类的初衷。网上有相关的解决方案，在参考了网上诸多的解决方案、示例代码之后，就有了该工具类，特别感谢各位前辈，让我能够站在巨人的肩膀上，用拳头抠抠鼻屎。废话不多说了，下面是PoiUtil工具类的源码：

复制代码

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;
}
}

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;
}
}