Java Apache POI autoSizeColumn 调整为最小宽度
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20924299/
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
Apache POI autoSizeColumn resizes to minimum width
提问by
When working with values that are formulas, I am having difficulty getting the columns to autoresize properly.
使用公式值时，我很难让列正确自动调整大小。
I have "solved" this by making a hidden row that has maximum values as constant strings values, but that is far from elegant and often requires evaluating the formulas in each cell to get the largest strings that are generated. While this kind of works for such a small spreadsheet, it becomes very impractical for sheets that are ~16 columns x ~6000 rows.
我通过将最大值作为常量字符串值的隐藏行“解决”了这个问题，但这远非优雅，并且通常需要评估每个单元格中的公式以获得生成的最大字符串。虽然这种类型的电子表格适用于如此小的电子表格，但对于约 16 列 x 约 6000 行的工作表变得非常不切实际。
The following code renders as in OpenOffice.
以下代码在 OpenOffice 中呈现。
package com.shagie.poipoc;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import java.io.FileOutputStream;
public class SimpleBug {
public static void main(String[] args) {
try {
Workbook wb = new HSSFWorkbook();
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
Sheet sheet = wb.createSheet("new sheet");
Row row = sheet.createRow(0);
CellStyle style = wb.createCellStyle();
style.setDataFormat(wb.createDataFormat().getFormat("[h]:mm"));
Cell cell = row.createCell(0);
cell.setCellValue(123.12);
cell.setCellStyle(style);
row.createCell(1).setCellFormula("A1");
row.createCell(2)
.setCellFormula("TRUNC(A1) & \"d \" & TRUNC(24 * MOD(A1,1))" +
" & \"h \" & TRUNC(MOD(60 * 24 * MOD(A1,1),60)) & \"m\"");
row.createCell(3).setCellValue("foo");
for(int i = 0; i < 4; i++) {
sheet.autoSizeColumn(i);
}
wb.write(fileOut);
fileOut.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
Related and tried:
相关并尝试过：
Apache POI autoSizeColumn Resizes IncorrectlyI've tried the font in the style. I got a list of all the fonts Java was aware of with
Apache POI autoSizeColumn 错误地调整大小我已经尝试过样式中的字体。我得到了 Java 知道的所有字体的列表
GraphicsEnvironment e = GraphicsEnvironment.getLocalGraphicsEnvironment();
for(String font:e.getAvailableFontFamilyNames()) {
System.out.println(font);
}
I tried several of the fonts listed with that loop, and while OpenOffice changed the font, the columns where still sized incorrectly.
我尝试了该循环中列出的几种字体，虽然 OpenOffice 更改了字体，但列的大小仍然不正确。
采纳答案by PopoFibo
Assuming you're looking to have the correct size of the column based on the formula results, just insert the following line right beforeyou do the autoSizeColumn, in this case before your forloop:
假设您希望根据公式结果获得正确的列大小，只需在执行之前插入以下行autoSizeColumn，在这种情况下是在for循环之前：
HSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
The reason is autoSizeColumnsizes your cell based on the cached formula evaluated results and if the formula was never evaluated, it would not know what size to set for it.
原因是autoSizeColumn根据缓存的公式评估结果来调整单元格的大小，如果从未评估过公式，它将不知道要为其设置什么大小。
Code:
代码：
...
row.createCell(3).setCellValue("foo");
HSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
for (int i = 0; i < 4; i++) {
sheet.autoSizeColumn(i);
}
...
