将 autoSizeColumn 放在 Apache POI 的何处?

问题描述

我已经研究 Apache POI XSSF 模型有一段时间了.我正在尝试生成一个格式化并应用了一些样式的 Excel 工作表.出于性能原因，我一直在使用 POI 提供的 Big-Grid 示例.

https://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/usermodel/examples/BigGridDemo.java.>

除此之外，我还想为每一列应用 autoSizeColumn.但我不确定在给定程序中应该在哪里应用 autoSizeColumn 方法.

private static void generate(Writer out, Map styles,String inputFileName, XSSFSheet sheet) throws Exception {BufferedReader br = null;字符串行 = "";字符串 cvsSplitBy = ",";SpreadsheetWriter sw = new SpreadsheetWriter(out);sw.beginSheet();整数 rowId = 0;br = new BufferedReader(new FileReader(inputFileName));while ((line = br.readLine()) != null) {//使用逗号作为分隔符String[] reportRecords = line.split(cvsSplitBy);if (rowId == 0) {//打印报告名称sw.insertRow(rowId++);sw.createCell(0, StringEscapeUtils.escapeXml(line.substring(1, line.length() - 1)), styles.get("header").getIndex());sw.endRow();}否则 if(rowId == 1){sw.insertRow(rowId++);for (int column = 0; column < reportRecords.length; column++) {sw.createCell(column, StringEscapeUtils.escapeXml(reportRecords[column].substring(1,reportRecords[column].length() - 1)), styles.get("header").getIndex());}sw.endRow();}别的{sw.insertRow(rowId++);for (int column = 0; column 

非常感谢您在这方面的帮助.

谢谢！

解决方案

您提到了 BigGridDemo.您似乎没有阅读的是 文件的顶部，以及在头文件 javadocs 中更小的一个:

注意 - 您可能不想再使用这种方法！兴趣点现在包括为您处理所有这些的 SXSSF，您应该而是使用它！此代码主要用于历史兴趣.

正如标题所说，您应该使用 SXSSF 代替.SXSSF 提供了一种很大程度上与 XSSF 兼容的方式来编写 .xlsx 文件，但通过创建 XML 本身并仅在内存中保留行和单元格的小窗口"，保留了非常低的内存占用BigGridDemo 确实

因此，您需要更改代码以使用 SXSSFWorkbook，但在其他方面与您需要大量内存的 XSSF 版本基本相同，您会很好

嗯...除了，自动调整大小需要访问列中的所有行，才能知道哪个单元格最大.SXSSF 无法为您提供这些，因为它已将几乎所有行刷新到磁盘以节省内存.因此，您的自动调整大小仅适用于仍在窗口中的少数几行，因为这些是唯一可用于检查其大小的行.它可能会大部分正确，因为通常一列中的大多数单元格大小相同.它可能并不完美，但遗憾的是，这是较低内存使用的权衡之一

I have been working on Apache POI XSSF model for quite some time now. I'm trying to generate a excel sheet which is formatted and have some set of styles applied. For performance reasons i have been using the Big-Grid example provided by POI.

https://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/usermodel/examples/BigGridDemo.java.

Along with this i also want to apply the autoSizeColumn for each of the columns. But i'm not sure where should i apply the autoSizeColumn method in the given program.

private static void generate(Writer out, Map<String, XSSFCellStyle> styles,String inputFileName, XSSFSheet sheet) throws Exception {

    BufferedReader br = null;
    String line = "";
    String cvsSplitBy = ",";
    SpreadsheetWriter sw = new SpreadsheetWriter(out);
    sw.beginSheet();
    Integer rowId = 0;
    br = new BufferedReader(new FileReader(inputFileName));
    while ((line = br.readLine()) != null) {
         //use comma as separator
        String[] reportRecords = line.split(cvsSplitBy);
        if (rowId == 0) { // Print the Report Name
            sw.insertRow(rowId++);
            sw.createCell(0, StringEscapeUtils.escapeXml(line.substring(1, line.length() - 1)), styles.get("header").getIndex());
            sw.endRow();
        }
        else if(rowId == 1){
            sw.insertRow(rowId++);
            for (int column = 0; column < reportRecords.length; column++) {
                sw.createCell(column, StringEscapeUtils.escapeXml(reportRecords[column].substring(1,reportRecords[column].length() - 1)), styles.get("header").getIndex());
            }
            sw.endRow();
        }
        else{
            sw.insertRow(rowId++);
            for (int column = 0; column < reportRecords.length; column++) {
                sw.createCell(column, StringEscapeUtils.escapeXml(reportRecords[column].substring(1,reportRecords[column].length() - 1)));
            }
            sw.endRow();
        }
    }
    br.close();
    sheet.autoSizeColumn(1);
    sheet.autoSizeColumn(2);
    sheet.autoSizeColumn(3);
    sw.endSheet();

}

Your help is much appreciated in this regard.

Thanks!

解决方案

You mention the BigGridDemo. What you appear to have failed to read is the big warning at the top of the file, and the slightly smaller one further down through the header javadocs:

As the header goes on to say, you should be using SXSSF instead. SXSSF provides a largely XSSF-compatible way to write a .xlsx file, but by creating the XML itself and only keeping a small "window" of rows and cells in memory, retains the very low memory footprint that the BigGridDemo does

So, you need to change your code to use SXSSFWorkbook, but otherwise largely be the same as your memory-hungry XSSF version, and you'll be good

Well... Except, that autosizing requires access to all rows in a column, to know which cell is biggest. SXSSF can't give you that, as it has flushed almost all the rows to disk to save memory. So, your auto-sizing will only work for the handful of rows still in the window, as those are the only ones available to check the size of. It'll probably get it mostly right, as normally most cells in a column are the same size. It might not be perfect, but sadly that's one of the trade-offs of the lower memory use

这篇关于将 autoSizeColumn 放在 Apache POI 的何处?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！