前天,被产品经理要求我将原本二十多个excel合并成一个excel,合并后的一个excel大概30W+左右的数据量,于是我使用poi对excel进行合并,我直接贴代码。实验数据大概15W左右,耗时大概35秒
合并excel代码
public static void mergeExcel(String path, String outputPath) throws IOException {
//1.校验文件是否符合要求
File targetFile = new File(path);
if (!targetFile.exists() || !targetFile.isDirectory()) {
LOGGER.info("文件不存在或者文件不是文件夹");
return;
}
File[] files = targetFile.listFiles(p -> p.getName().endsWith(".xlsx"));
if (files == null || files.length <= 0) {
LOGGER.info("文件夹没有excel文件");
return;
}
String startTime = DateUtil.getCurrentTimeStr();
//2.数据合并
//目前默认只合并第一个sheet,对于第一个excel文件,所有数据皆写入,对于后面的excel,从第二行数据开始拿起
int sourceValidRows = 0;
SXSSFWorkbook srcSXSSWb = null;
for (int i = 0; i < files.length; i++) {
if (i == 0) {
InputStream is = new FileInputStream(files[i]);
XSSFWorkbook headXssfWb = new XSSFWorkbook(is);
XSSFSheet sourceSheet = headXssfWb.getSheetAt(DEFAULT_SHEEET_INDEX);
sourceValidRows = sourceSheet.getPhysicalNumberOfRows();
//3.写入新文件
srcSXSSWb = new SXSSFWorkbook(headXssfWb);
} else {
InputStream is = new FileInputStream(files[i]);
XSSFWorkbook targetXssfWb = new XSSFWorkbook(is);
SXSSFSheet sourceSheet = srcSXSSWb.getSheetAt(DEFAULT_SHEEET_INDEX);
XSSFSheet targetSheet = targetXssfWb.getSheetAt(DEFAULT_SHEEET_INDEX);
for (int targetSheetRow = 1; targetSheetRow < targetSheet.getPhysicalNumberOfRows(); targetSheetRow++) {
LOGGER.info(i + "--" + targetSheetRow + " | sourceValidRows -- " + (sourceValidRows + targetSheetRow));
XSSFRow targetRow = targetSheet.getRow(targetSheetRow);
SXSSFRow sourceRow = sourceSheet.createRow(sourceValidRows + targetSheetRow);
for (int cellIndex = 0; cellIndex < targetRow.getPhysicalNumberOfCells(); cellIndex++) {
XSSFCell targetCell = targetRow.getCell(cellIndex);
SXSSFCell sourceCell = sourceRow.createCell(cellIndex);
sourceCell.setCellValue(targetCell.getStringCellValue());
}
}
sourceValidRows = sourceValidRows + targetSheet.getPhysicalNumberOfRows();
LOGGER.info("有效行数:" + sourceSheet.getPhysicalNumberOfRows());
is.close();
targetXssfWb.close();
}
}
//3.写入新文件
FileOutputStream out = new FileOutputStream(outputPath);
srcSXSSWb.write(out);
out.close();
srcSXSSWb.dispose();
LOGGER.info("开始时间:" + startTime + ",结束时间:" + DateUtil.getCurrentTimeStr());
}
main方法
public static void main(String[] args) {
try {
mergeExcel("C:/Users/xxx/Desktop/asd", "C:/Users/xxx/Desktop/ae.xlsx");
} catch (IOException e) {
e.printStackTrace();
}
}