先引入依赖
<!-- poi引用 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
要复制sheet到另一个excel表中,虽然复制样式有点问题,样式不能超过4000
/**
* Sheet复制
*
* @param wb workbook
* @param fromSheet 要复制的Sheet
* @Parame toSheet 复制到的Sheet
*/
private static void copySheet(Workbook wb, Sheet fromSheet, Sheet toSheet) {
mergeSheetAllRegion(fromSheet, toSheet);
// 设置列宽
int length = fromSheet.getRow(fromSheet.getFirstRowNum()).getLastCellNum();
for (int i = 0; i <= length; i++) {
toSheet.setColumnWidth(i, fromSheet.getColumnWidth(i));
}
for (Iterator rowIt = fromSheet.rowIterator(); rowIt.hasNext(); ) {
Row oldRow = (Row) rowIt.next();
Row newRow = toSheet.createRow(oldRow.getRowNum());
copyRow(wb, oldRow, newRow);
}
}
/**
* 合并单元格
*
* @param fromSheet
* @param toSheet
*/
private static void mergeSheetAllRegion(Sheet fromSheet, Sheet toSheet) {
int num = fromSheet.getNumMergedRegions();
CellRangeAddress cellR = null;
for (int i = 0; i < num; i++) {
cellR = fromSheet.getMergedRegion(i);
toSheet.addMergedRegion(cellR);
}
}
/**
* 行复制功能
*
* @param wb workbook
* @param oldRow 要复制的行
* @param toRow 复制到这里行
*/
private static void copyRow(Workbook wb, Row oldRow, Row toRow) {
toRow.setHeight(oldRow.getHeight());
for (Iterator cellIt = oldRow.cellIterator(); cellIt.hasNext(); ) {
Cell tmpCell = (Cell) cellIt.next();
Cell newCell = toRow.createCell(tmpCell.getColumnIndex());
copyCell(wb, tmpCell, newCell);
}
}
/**
* 复制单元格
*
* @param wb
* @param fromCell 要复制的 Cell
* @param toCell 要复制的 Cell
*/
private static void copyCell(Workbook wb, Cell fromCell, Cell toCell) {
//注意样式创建太多报错,容量大概4000
CellStyle newstyle = wb.createCellStyle();
// 复制单元格样式
newstyle.cloneStyleFrom(fromCell.getCellStyle());
// 样式
toCell.setCellStyle(newstyle);
if (fromCell.getCellComment() != null) {
toCell.setCellComment(fromCell.getCellComment());
}
// 不同数据类型处理
CellType fromCellType = fromCell.getCellType();
toCell.setCellType(fromCellType);
if (fromCellType == CellType.NUMERIC) {
if (DateUtil.isCellDateFormatted(fromCell)) {
toCell.setCellValue(fromCell.getDateCellValue());
} else {
toCell.setCellValue(fromCell.getNumericCellValue());
}
} else if (fromCellType == CellType.STRING) {
toCell.setCellValue(fromCell.getRichStringCellValue());
} else if (fromCellType == CellType.BLANK) {
// nothing21
} else if (fromCellType == CellType.BOOLEAN) {
toCell.setCellValue(fromCell.getBooleanCellValue());
} else if (fromCellType == CellType.ERROR) {
toCell.setCellErrorValue(fromCell.getErrorCellValue());
} else if (fromCellType == CellType.FORMULA) {
toCell.setCellFormula(fromCell.getCellFormula());
} else {
// nothing29
}
}