Java 如何加快apache POI中自动调整列的速度？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18983203/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to speed up autosizing columns in apache POI?
提问by antken
I use the following code in order to autosize columns in my spreadsheet:
我使用以下代码来自动调整电子表格中的列大小：
for (int i = 0; i < columns.size(); i++) {
sheet.autoSizeColumn(i, true);
sheet.setColumnWidth(i, sheet.getColumnWidth(i) + 600);
}
The problem is it takes more than 10 minutes to autosize each column in case of large spreadsheets with more than 3000 rows. It goes very fast for small documents though. Is there anything which could help autosizing to work faster?
问题是在超过 3000 行的大型电子表格的情况下，自动调整每列需要 10 多分钟。但是对于小文档，它运行得非常快。有什么可以帮助自动调整以更快地工作吗？
采纳答案by antken
Solution which worked for me:
对我有用的解决方案：
It was possible to avoid merged regions, so I could iterate through the other cells and finally autosize to the largest cell like this:
可以避免合并区域，因此我可以遍历其他单元格并最终自动调整到最大的单元格，如下所示：
int width = ((int)(maxNumCharacters * 1.14388)) * 256;
sheet.setColumnWidth(i, width);
where 1.14388 is a max character width of the "Serif" font and 256 font units.
其中 1.14388 是“Serif”字体和 256 个字体单位的最大字符宽度。
Performance of autosizing improved from 10 minutes to 6 seconds.
自动调整大小的性能从 10 分钟提高到 6 秒。
回答by Vladimir Shcherbukhin
The autoSizeColumnfunction itself works not perfect and some columns width not exactly fit the data inside. So, I found some solution that works for me.
该autoSizeColumn功能工作本身并不完美，一些列的宽度并不完全适合里面的数据。所以，我找到了一些适合我的解决方案。
- To avoid crazy calculations let give that to autoSizeColumn() function:
- 为了避免疯狂的计算，让我们把它交给 autoSizeColumn() 函数：
sheet.autoSizeColumn(<columnIndex>);
- Now, our column autosized by library but we wont to add a little bit more to the current column width to make table looks fine:
- 现在，我们的列由库自动调整大小，但我们不会向当前列宽添加更多内容以使表格看起来不错：
// 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));
- The full function could looks like:
- 完整的功能可能如下所示：
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. Thanks Ondrej Kvasnovskyfor the function https://stackoverflow.com/a/35324693/13087091
PS 感谢Ondrej Kvasnovsky的功能https://stackoverflow.com/a/35324693/13087091
