1

I use apache-poi to create Excel spreadsheet (xlsx format). When I call my finish() this causes the spreadsheet to actually be created on disk. 

fos = new FileOutputStream(reportName);
workbook = new SXSSFWorkbook(FLUSH_SIZE);

.........

    public void finish() throws IOException
    {
        for (Worksheet next : worksheets)
        {
            SXSSFSheet sheet = (SXSSFSheet)next.getSheet();

            for (int i = 0; i < next.getMapping().size(); i++)
            {
                int columnWidth = next.getMapping().get(i).getColumnWidthFromValue() + FONT_MARGIN_OF_ERROR;
                columnWidth = columnWidth > MAX_COL_WIDTH ? MAX_COL_WIDTH : columnWidth;
                sheet.setColumnWidth(i, columnWidth * COL_WIDTH_MULTIPLIER);
            }
        }
        workbook.write(fos);
        fos.close();
        workbook.dispose();
    }

However, the xlsx format is actually a zipped up collection of files. Since I have to make some additional changes to the spreadsheet after it was created with apache-poi (they cannot be done with apache-poi) is there a way to make apache-poi create the files but not zip them up as an xlsx. Because the first thing I have to do is unzip them the current processing is inefficient since poi zips the files, then I have to unzip them, make modifications and then zip them up again myself.

Paul Taylor
  • 13,411
  • 42
  • 184
  • 351
  • 1
    No because an XLSX is a zipped up collection of files. The files never get written outside the zip format. What is it that you can't figure out how to do with POI? Maybe we can help you with that. – jmarkmurphy Mar 15 '18 at 12:30
  • Are you saying they are written directly to zip rather than on disk first ? – Paul Taylor Mar 15 '18 at 12:33
  • If so, okay but it would be useful to get them to be written to disk instead of to a zip. Im using SXSSF because some of the spreadsheets are large and it takes too much memory to create them using the dom approach. What I want to is format as table but this is not supported using SXXF, and looks quite easy to this afterwards - https://stackoverflow.com/questions/49093442/can-i-use-apache-poi-to-format-excel-spreadsheet-as-a-table-in-streaming-mode – Paul Taylor Mar 15 '18 at 12:36
  • Why not use the POI-provided OPC methods to grab the bits of the file you need? – Gagravarr Mar 15 '18 at 12:38
  • @Gagravarr what do you mean Im not aware of this – Paul Taylor Mar 15 '18 at 12:40
  • 1
    Using `PackagePart sheetpart = opcpackage.getPartsByName(Pattern.compile("/xl/worksheets/sheet"+sheetnr+".xml")).get(0);` you could get one of the sheet xml files out of the package. Example: https://stackoverflow.com/questions/46601782/read-rows-sequentially-but-on-demand/46617086#46617086 – Axel Richter Mar 15 '18 at 14:15
  • Right but I have no need to get a sheet out, in fact I have no need to get anything out of the spreadsheet since i create it and have all the data, what I need to do is for each sheet add a table.xml into the zip. Actually as I write this just occurred to me, can I just add new files to an existing zip file without need to unzip it ? – Paul Taylor Mar 15 '18 at 14:20
  • Inserting new parts is also possible. In https://stackoverflow.com/questions/44491860/how-to-add-comment-by-apache-poi/44511824#44511824 I am inserting a `/word/comments.xml` into a `*.docx` archive. But simply adding the xml file is not enough. The relations needs also to be set. But for `table*.xml` why not simply using [XSSFTable](https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFTable.html)? – Axel Richter Mar 15 '18 at 14:32
  • But how, i cant use XSSFTable when i originally create because Im using SXSSF and its not available from SXSSFSheet. If I use it later I assume that requires using poi to open the existing spreadsheet in memory and hence hit memory problems again. Actually interestingly another thing I want to do is add comments, they can actually be added when using SXSSF but the memory hit is too high as they are all kept in memory until the spreadsheet has been created. – Paul Taylor Mar 15 '18 at 14:50
  • 2
    @Paul Taylor: So you are creating the workbook from scratch? Then you will knowing how the tables shall be structured. Then you can first creating a `XSSFWorkbook` having empty `XSSFSheet`s having relations to `XSSFTable`s. This data amount is not very high (only the structure, not the data). Then you can creating a [SXSSFWorkbook](https://poi.apache.org/apidocs/org/apache/poi/xssf/streaming/SXSSFWorkbook.html) from that `XSSFWorkbook` and then do streaming the big amount of data into the sheets. – Axel Richter Mar 15 '18 at 16:10
  • @AxelRichter that does sound like good solution actually I will try that, thanks. – Paul Taylor Mar 15 '18 at 16:39

0 Answers0