Apache POI从现有excel文件创建excel文件耗时过长

Question

我正在创建一个excel文件从一个现有的excel文件与一些变化在新的文件，如添加徽标到头，改变打印相关的格式。我使用的是Apache POI版本3.15。我正在使用xlsx文件。现有文件有757行47列，文件大小为294KB。结果文件为630KB。但大约需要5分钟。我不确定我做错了什么。如有任何建议，我们将不胜感激。下面是我正在使用的方法和代码：

>

打开模板文件进行输出（所需时间可忽略不计）

File file = new File("templateName.xlsx");
workbook = new XSSFWorkbook(file);

打开现有文件（所需时间可忽略不计）

File file = new File("inputFile.xlsx");
xssfWorkbook = new XSSFWorkbook(file);

将现有工作表中的所有单元格与单元格样式一起复制到新工作表中。它还处理所有页面中合并的单元格和重复的标题（大约需要1分钟的时间）

private void copyData(XSSFSheet from, boolean repeatRow){
int length =  from.getPhysicalNumberOfRows();
System.out.println("Copying " + length + " lines of data...");
int maxNumOfCols = 0;
int numOfCols;

CreationHelper createHelper = workbook.getCreationHelper();

// Get header-footer related data
getHeaderFooterData(from);
// Set header footer
setHeader(headerLeft, headerCenter);
setFooterWithDateString(footerLeft, "Page", "of");

XSSFRow dataRow, destRow;
XSSFCell cell;
for(int i=ROW_OFFSET, r=0; i<length; i++, r++){
    dataRow = from.getRow(i);
    if(dataRow == null) continue;

    destRow = sheet.createRow(r);
    destRow.setHeight(dataRow.getHeight());
    //System.out.println("Height of row " + i + ":" + dataRow.getHeightInPoints());
    /*
    XSSFCellStyle rowStyle = destWorkbook.createCellStyle();
    rowStyle.cloneStyleFrom(dataRow.getRowStyle());
    destRow.setRowStyle(rowStyle);
    */

    numOfCols = dataRow.getLastCellNum();
    if(numOfCols > maxNumOfCols)
        maxNumOfCols = numOfCols;

    for(int j=0; j<numOfCols; j++){
        cell = from.getRow(i).getCell(j);
        if (cell!=null) {
            XSSFCell destCell = destRow.createCell(j);

            // Copy style from old cell and apply to new cell
            XSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.cloneStyleFrom(cell.getCellStyle());
            destCell.setCellStyle(cellStyle);

            // If there is a cell comment, copy. No comments for our case
            /*
            if (cell.getCellComment() != null) {
                System.out.println("Added cell comment");
                destCell.setCellComment(cell.getCellComment());
            }
            */
            // If there is a cell hyperlink, copy
            if (cell.getHyperlink() != null) {
                XSSFHyperlink link = (XSSFHyperlink) createHelper.createHyperlink(Hyperlink.LINK_URL);
                link.setAddress(cell.getHyperlink().getAddress());
                link.setTooltip(cell.getHyperlink().getTooltip());
                destCell.setHyperlink(link);
            }

            // Set the cell data type
            destCell.setCellType(cell.getCellType());

            switch (cell.getCellType()) {
                case XSSFCell.CELL_TYPE_BOOLEAN:
                    destCell.setCellValue(cell.getBooleanCellValue());
                    break;
                case XSSFCell.CELL_TYPE_NUMERIC:
                    if(HSSFDateUtil.isCellDateFormatted(cell)){
                        destCell.setCellValue(cell.getDateCellValue());
                    }else {
                        destCell.setCellValue(cell.getNumericCellValue());
                    }
                    break;
                case XSSFCell.CELL_TYPE_STRING:
                    destCell.setCellValue(cell.getRichStringCellValue());
                    break;
                case XSSFCell.CELL_TYPE_BLANK:
                    //destCell.setCellValue("");
                    break;
                case XSSFCell.CELL_TYPE_ERROR:
                    destCell.setCellErrorValue(cell.getErrorCellValue());
                    break;
                case XSSFCell.CELL_TYPE_FORMULA:
                    destCell.setCellFormula(cell.getCellFormula());
                    break;
                default:
                    destCell.setCellValue(cell.getRawValue());
                    break;
            }

        }

    }
}

// set column widths
System.out.println("maxNumOfCols: " + maxNumOfCols);
for(int w=0; w<=maxNumOfCols; w++)
    sheet.setColumnWidth(w, from.getColumnWidth(w));

// Process merged cells
for(int i=0; i<from.getNumMergedRegions(); i++){
    //System.out.println("Merging cells");
    CellRangeAddress region = from.getMergedRegion(i);
    // ignore any merged region in the header/footer data section which is upto the ROW_OFFSET
    if(region.getFirstRow() < ROW_OFFSET)
        continue;
    region.setFirstRow(region.getFirstRow() - ROW_OFFSET);
    region.setLastRow(region.getLastRow() - ROW_OFFSET);
    sheet.addMergedRegion(region);
}

// set repeating header row if applicable
String range = "" + String.valueOf(headerRowIndex-ROW_OFFSET) + ":" + String.valueOf(headerRowIndex-ROW_OFFSET);
if(repeatRow){
    sheet.setRepeatingRows(CellRangeAddress.valueOf(range));
}

}

将生成的工作簿写入新文件（大约需要4分钟时间）

FileOutputStream outputStream = new FileOutputStream("newFileName.xlsx");
workbook.write(outputStream);

Answer 1

我做了一些事情，他们改进了运行时间。以下是我做的事情：

>

根据Axel Richter的建议，完成后关闭工作簿和文件流。谢谢阿克塞尔！

将合并单元格进程移到顶部而不是底部

通过使用Hashmap重用单元格样式，而不是为工作簿中的每个单元格创建单元格样式。下面的代码显示了我是如何做到这一点的：

HashMap<Integer, XSSFCellStyle> cellStyleMap = new HashMap<Integer, XSSFCellStyle>();
// create the cell style only if it is not already there
int styleHashcode = cell.getCellStyle().hashCode();
XSSFCellStyle cellStyle = cellStyleMap.get(styleHashcode);
if(cellStyle == null) {
     cellStyle = workbook.createCellStyle();
     cellStyle.cloneStyleFrom(cell.getCellStyle());
     cellStyleMap.put(styleHashcode, cellStyle);
}
destCell.setCellStyle(cellStyle);