Java – Apache poi sxssf and xssf

I have a problem. I'm right. If I have a workbook created by xssf constructor, is it enough to change the constructor to sxssf Workbook (passing xssf WB as a parameter) to make it work in streaming mode? Thank you for your answer

Solution: it all depends on the class you use for streaming If your class collects more stream buffers than it can hold, then this thing won't work Otherwise it will

Solution

You're right. The difference between the two implementations is that the stream version writes data directly to the stream and stores up to the specified number of rows in memory (the default value is 100 and stored in sxssfworkbook.default_window_size) Therefore, after writing to the output stream, you will not be able to obtain some row data The great advantage of using stream implementation is to reduce memory usage If you need to export large amounts of data, use sxssfworkbook

Example:

public static void main(String[] args) throws IOException {
        FileOutputStream inMemoryOut = new FileOutputStream(new File("inMemoryWorkbook.xlsx"));
        XSSFWorkbook workbook = new XSSFWorkbook();
        WorkbookExample example = new WorkbookExample(workbook,inMemoryOut);
        example.export();

        FileOutputStream streamOut = new FileOutputStream(new File("streamWorkbook.xlsx"));
        SXSSFWorkbook streamWorkbook = new SXSSFWorkbook();
        WorkbookExample streamExample = new WorkbookExample(streamWorkbook,streamOut);
        streamExample.export();
    }

public class WorkbookExample {

    private Logger logger = Logger.getLogger(WorkbookExample.class.getName());
    private Workbook workbook;
    private OutputStream out;

    public WorkbookExample(Workbook workbook,OutputStream out) {
        this.workbook = workbook;
        this.out = out;
    }

    public void export() throws IOException {
        logger.info("export start for " + workbook.getClass().getName());

        List<Person> persons = new ArrayList<Person>();
        for (int i = 0; i < 1000; i++) {
            persons.add(new Person(String.valueOf("user_" + i)));
        }

        Sheet sheet = workbook.createSheet();
        for (int i = 0; i < persons.size(); i++) {
            Person p = persons.get(i);
            Row row = sheet.createRow(i);
            Cell cell = row.createCell(0);
            cell.setCellValue(p.getName());
        }
        workbook.write(out);
        logger.info("Is row 1 accessible after writing to output stream? " + String.valueOf(sheet.getRow(1) != null));
        out.close();
        workbook.close();

        logger.info("export finished for " + workbook.getClass().getName());
    }

    public static class Person {

        private String name;

        public Person(String name) {
            this.name = name;
        }

        public String getName() {
            return name;
        }

        public void setName(String name) {
            this.name = name;
        }

    }

}

Output:

kwi 21,2015 7:56:14 PM pepuch.html2pdf.WorkbookExample export
    INFO: export start for org.apache.poi.xssf.usermodel.XSSFWorkbook
kwi 21,2015 7:56:15 PM pepuch.html2pdf.WorkbookExample export
    INFO: Is row 1 accessible after writing to output stream? true
kwi 21,2015 7:56:15 PM pepuch.html2pdf.WorkbookExample export
    INFO: export finished for org.apache.poi.xssf.usermodel.XSSFWorkbook
kwi 21,2015 7:56:15 PM pepuch.html2pdf.WorkbookExample export
    INFO: export start for org.apache.poi.xssf.streaming.SXSSFWorkbook
kwi 21,2015 7:56:15 PM pepuch.html2pdf.WorkbookExample export
    INFO: Is row 1 accessible after writing to output stream? false
kwi 21,2015 7:56:15 PM pepuch.html2pdf.WorkbookExample export
    INFO: export finished for org.apache.poi.xssf.streaming.SXSSFWorkbook

As you can see, line 1 cannot be accessed after writing the output stream using sxssf workbook