如何加快使用Apache POI自动调整列宽的速度？

Question

我使用以下代码来自动调整电子表格中的列大小：

for (int i = 0; i < columns.size(); i++) {
   sheet.autoSizeColumn(i, true);
   sheet.setColumnWidth(i, sheet.getColumnWidth(i) + 600);
}

问题在于对于具有超过3000行的大型电子表格，自动调整每列大小需要超过10分钟的时间。但对于小型文档来说，速度很快。是否有什么可以帮助自动调整大小更快地工作的方法？

- antken

---

sheet.autoSizeColumn(i, false) 可能会稍微快一些。 - developerwjk

它很慢，因为它必须在文档的每一行中计算该单元格的宽度。您是否有机会更改逻辑，以便先编写前几行并基于它们进行调整大小，然后稍后再添加其余行呢？ - Gagravarr

@developerwjk 绝对同意，根据poi库的源代码，每次计算每个单元格的宽度时，它会迭代所有合并区域。 - antken

@Gagravarr 很遗憾，在这种情况下不可能，因为大单元格可能随后出现在任何行中。 - antken

Answer 1

适用于我的解决方案：

我能够避免合并区域，因此我可以迭代其他单元格，并最终按如下自动调整大小以适应最大单元格：

int width = ((int)(maxNumCharacters * 1.14388)) * 256;
sheet.setColumnWidth(i, width);

其中1.14388是“Serif”字体的最大字符宽度，256个字体单位。

自适应大小调整的性能从10分钟提高到6秒。

- antken

---

你如何获取“maxNumCharacters”？ - To Kra

@ToKra 由你决定。我只是计算每个单元格列中的字符数，并找到最大值，然后将其应用于公式。 - antken

@antken，如何计算其他字体的“最大字符宽度”？ - pedrohreis

从 Apache API 中：在大型表格上，该过程可能相对较慢，因此通常只应在处理结束时每列调用一次。 - whatswrong

因为宽度以字符宽度的1/256为单位设置。请参见此链接：https://poi.apache.org/apidocs/dev/org/apache/poi/hssf/usermodel/HSSFSheet.html#setColumnWidth-int-int- - Nikhil

Answer 2

autoSizeColumn 函数本身并不完美，某些列的宽度不太适应内部数据。因此，我发现一些解决方案适用于我的情况。

1. 为了避免疯狂的计算，让我们将其交给 autoSizeColumn() 函数：

   sheet.autoSizeColumn(<columnIndex>);

现在，我们的列已经由库自动调整大小，但我们希望在当前列宽上再增加一点，使表格看起来更好：

   // get autosized column width
   int currentColumnWidth = sheet.getColumnWidth(<columnIndex>);

   // add custom value to the current width and apply it to column
   sheet.setColumnWidth(<columnIndex>, (currentColumnWidth + 2500));

完整的函数可能如下所示：

   public void autoSizeColumns(Workbook workbook) {
        int numberOfSheets = workbook.getNumberOfSheets();
        for (int i = 0; i < numberOfSheets; i++) {
            Sheet sheet = workbook.getSheetAt(i);
            if (sheet.getPhysicalNumberOfRows() > 0) {
                Row row = sheet.getRow(sheet.getFirstRowNum());
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    int columnIndex = cell.getColumnIndex();
                    sheet.autoSizeColumn(columnIndex);
                    int currentColumnWidth = sheet.getColumnWidth(columnIndex);
                    sheet.setColumnWidth(columnIndex, (currentColumnWidth + 2500));
                }
            }
        }
    }

P.S. 感谢Ondrej Kvasnovsky提供的函数https://dev59.com/em445IYBdhLWcg3w9O3R#35324693

- Vladimir Shcherbukhin

Answer 3

autosizeColumn()函数非常缓慢和低效。甚至apache POI的作者在文档中提到：

这个过程在大型工作表上可能相对缓慢...

手动计算和设置单元格宽度的速度要快得多-在我的案例中，我将时间从约25,000毫秒减少到约1-5毫秒。

下面是如何实现它（我参考了弗拉基米尔·谢尔布胡金的答案）：

Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet();
final int[] maxNumCharactersInColumns = new int[headers.length]; // maximum number of characters in columns. Necessary to calculate the cell width in most efficient way. sheet.autoSizeColumn(...) is very slow.

Row headersRow = sheet.createRow(0);
CellStyle headerStyle = createHeadersStyle(workbook); // createHeadersStyle() is my own function. Create headers style if you want

for (int i = 0; i < headers.length; i++) { // create headers
        Cell headerCell = headersRow.createCell(i, CELL_TYPE_STRING);
        headerCell.setCellValue(headers[i]);
        headerCell.setCellStyle(headerStyle);

        int length = headers[i].length();
        if (maxNumCharactersInColumns[i] < length) { // adjust the columns width
            maxNumCharactersInColumns[i] = length + 2; // you can add +2 if you have filtering enabled on your headers
        }
}

int rowIndex = 1;
    for (List<Object> rowValues : rows) {
        Row row = sheet.createRow(rowIndex);

        int columnIndex = 0;
        for (Object value : rowValues) {
            Cell cell = createRowCell(row, value, columnIndex); // createRowCell() is my own function.

            int length;
            if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                String cellValue = cell.getStringCellValue();

                // this is quite important part. In some excel spreadsheet you can have a values with line-breaks. It'll be cool to handle that scenario :)
                String[] arr = cellValue.split("\n"); // if cell contains complex value with line breaks, calculate only the longest line
                length = Arrays.stream(arr).map(String::length).max(Integer::compareTo).get();
            } else {
                length = value != null ? value.toString().length() : 0;
            }

            if (maxNumCharactersInColumns[columnIndex] < length) { // if the current cell value is the longest one, save it to an array
                maxNumCharactersInColumns[columnIndex] = length;
            }

            columnIndex++;
        }
        rowIndex++;
    }

    for (int i = 0; i < headers.length; i++) {
        int width = (int) (maxNumCharactersInColumns[i] * 1.45f) * 256; // 1.45f <- you can change this value
        sheet.setColumnWidth(i, Math.min(width, MAX_CELL_WIDTH)); // <- set calculated cell width
    }

    sheet.setAutoFilter(new CellRangeAddress(0, 0, 0, headers.length - 1));

    ByteArrayOutputStream output = new ByteArrayOutputStream();
    workbook.write(output);
    workbook.close();

- Jakub Słowikowski

Answer 4

很遗憾，我还没有足够的声望在回答中添加评论。因此，在这里提供一些注释：

• 当使用Row row = sheet.getRow(sheet.getFirstRowNum());时，请确保该行至少包含最后一列中的一个值。否则，cellIterator将会过早结束，即如果后续行在此列中具有值，则不会自动调整此列的大小。如果row包含标题（列的名称），则可以绕过此问题。或者明确使用已知的标题行，例如：
  

    int indexOfHeaderRow = ...;
    ...
    Row row = sheet.getRow(indexOfHeaderRow);

• Jakub Słowikowski
  sheet.setColumnWidth(i, Math.min(width, MAX_CELL_WIDTH)); // <- set calculated cellwidth
  我对这一行不确定，因为没有关于MAX_CELL_WIDTH内容的信息 - 可能是总体最大宽度? 所以我使用了：
  sheet.setColumnWidth(i, Math.max(width, 2048));
  2048似乎是默认宽度？ 这个值可以防止空列宽度过窄。

- Erich13

Answer 5

XSSFSheet遇到的最大问题是它没有一个函数可以返回特定列的所有值，你必须逐行迭代，所以当你指定列时，实际上是在迭代所有行和列。这可能是导致速度慢的原因。 我自己实现了一个方法，它只需要一次迭代所有列和行，然后更改listOfColumns中指定的列，我还添加了maxWidth，以防你处理长文本时出现过宽的情况。也许添加一个像Zz'Rot在他的实现中那样的字体宽度实现会是个好主意。

private static void autoSizeColumns(XSSFSheet sheet, Integer maxWidth, List<Integer> listOfColumns) {
    HashMap<Integer,Integer> sizesArray = new HashMap<>();
    for (Row row : sheet) {
        for (Cell cell : row) {
            int column = cell.getColumnIndex();
            int oldMaxSize = Optional.ofNullable(sizesArray.get(column)).orElse(0);
            int newMaxSize = switch (cell.getCellType()) {
                case STRING,FORMULA ->
                        Math.max(oldMaxSize, cell.getStringCellValue().length());
                case NUMERIC ->
                        Math.max(oldMaxSize, Double.toString(cell.getNumericCellValue()).length());
                case BOOLEAN ->
                        Math.max(oldMaxSize, Boolean.toString(cell.getBooleanCellValue()).length());
                case ERROR ->
                        Math.max(oldMaxSize, Byte.toString(cell.getErrorCellValue()).length());
                default ->
                        oldMaxSize;
            };
            if(oldMaxSize != newMaxSize)
                sizesArray.put(column, newMaxSize);
        }
    }
    if(listOfColumns == null){
        for (int i = 0; i < sizesArray.size(); i++) {
            int width = sizesArray.get(i) * 256;
            if (maxWidth != null && width > maxWidth) {
                width = maxWidth;
            }
            sheet.setColumnWidth(i, width);
        }
        return;
    }
    for (Integer column : listOfColumns) {
        Integer size = sizesArray.get(column);
        int width = sizesArray.get(size) * 256;
        if (maxWidth != null && width > maxWidth) {
            width = maxWidth;
        }
        sheet.setColumnWidth(column, width);
    }
}

- Rjelinek