目前,网上实现多个excel合并成一个excel的方法,使用版本比较老,问题较多,所以特此写一个方法,仅供参考。
导入依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.0</version>
</dependency>
utils工具类
public static void copyCellStyle(XSSFCellStyle fromStyle, XSSFCellStyle toStyle) {
toStyle.cloneStyleFrom(fromStyle);
}
public static void mergeSheetAllRegion(XSSFSheet fromSheet, XSSFSheet toSheet) {
int num = fromSheet.getNumMergedRegions();
CellRangeAddress cellR ;
for (int i = 0; i < num; i++) {
cellR = fromSheet.getMergedRegion(i);
toSheet.addMergedRegion(cellR);
}
}
public static void copyCell(XSSFWorkbook wb, XSSFCell fromCell, XSSFCell toCell) {
XSSFCellStyle newstyle = wb.createCellStyle();
copyCellStyle(fromCell.getCellStyle(), newstyle);
//样式
toCell.setCellStyle(newstyle);
if (fromCell.getCellComment() != null) {
toCell.setCellComment(fromCell.getCellComment());
}
// 不同数据类型处理
CellType cellType = fromCell.getCellType();
toCell.setCellType(cellType);
if (fromCell == null || fromCell.equals(null) || fromCell.getCellType() == CellType.BLANK) {
toCell.setCellValue("");
} else {
//判断数据类型
switch (cellType) {
case FORMULA:
toCell.setCellValue(fromCell.getCellFormula());
break;
case NUMERIC:
toCell.setCellValue(fromCell.getNumericCellValue());
break;
case STRING:
toCell.setCellValue(fromCell.getStringCellValue());
break;
default:
break;
}
}
}
public static void copyRow(XSSFWorkbook wb, XSSFRow oldRow, XSSFRow toRow) {
toRow.setHeight(oldRow.getHeight());
for (Iterator cellIt = oldRow.cellIterator(); cellIt.hasNext(); ) {
XSSFCell tmpCell = (XSSFCell) cellIt.next();
XSSFCell newCell = toRow.createCell(tmpCell.getColumnIndex());
copyCell(wb, tmpCell, newCell);
}
}
public static void copySheet(XSSFWorkbook wb, XSSFSheet fromSheet, XSSFSheet toSheet) {
mergeSheetAllRegion(fromSheet, toSheet);
//设置列宽
for (int i = 0; i <= fromSheet.getRow(fromSheet.getFirstRowNum()).getLastCellNum(); i++) {
toSheet.setColumnWidth(i, fromSheet.getColumnWidth(i));
}
for (Iterator rowIt = fromSheet.rowIterator(); rowIt.hasNext(); ) {
XSSFRow oldRow = (XSSFRow) rowIt.next();
XSSFRow newRow = toSheet.createRow(oldRow.getRowNum());
copyRow(wb, oldRow, newRow);
}
}
实现方法
public String hc(List<Workbook> workbooks, String uuid) {
ByteArrayOutputStream bos = null;
InputStream is = null;
FileOutputStream fileOut=null;
String filepath;
try {
XSSFWorkbook newExcelCreat = new XSSFWorkbook();
for (Workbook workbook : workbooks) {
bos = new ByteArrayOutputStream();
workbook.write(bos);
byte[] barray = bos.toByteArray();
is = new ByteArrayInputStream(barray);
XSSFWorkbook fromExcel = new XSSFWorkbook(is);
XSSFSheet oldSheet = fromExcel.getSheetAt(0);
XSSFSheet newSheet = newExcelCreat.createSheet(oldSheet.getSheetName());
PoiUtils.copySheet(newExcelCreat, oldSheet, newSheet);
}
String allFileName = "d:/excel/0.xlsx";
fileOut = new FileOutputStream(allFileName);
newExcelCreat.write(fileOut);
fileOut.flush();
/* bos = new ByteArrayOutputStream();
newExcelCreat.write(bos);
byte[] barray = bos.toByteArray();
is = new ByteArrayInputStream(barray);
filepath = hdfsPath + "twentyTwo/" + uuid + ".xls";
HdfsUtil.createFile(is, filepath);
log.info("文件路径为{}", filepath);*/
} catch (IOException i) {
log.info("导出异常{}", i.getMessage());
return null;
} finally {
try {
fileOut.close();
is.close();
bos.close();
} catch (IOException i) {
i.printStackTrace();
}
}
return null;
}