Bug 60396 - setWrapText not working for SXSSFWorkbook
Summary: setWrapText not working for SXSSFWorkbook
Status: RESOLVED WORKSFORME
Alias: None
Product: POI
Classification: Unclassified
Component: SXSSF (show other bugs)
Version: 3.15-FINAL
Hardware: Macintosh All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2016-11-21 00:44 UTC by Marc
Modified: 2017-06-16 11:01 UTC (History)
0 users



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Marc 2016-11-21 00:44:35 UTC
The setWrapText doesn't correctly interpret new line characters when using the streaming version the Excel renderer. This can be demonstrated using and amending the example code as follows:

        SXSSFWorkbook wb = new SXSSFWorkbook(100); 
        Sheet sh = wb.createSheet();
        for(int rownum = 0; rownum < 1000; rownum++){
            Row row = sh.createRow(rownum);
            for(int cellnum = 0; cellnum < 10; cellnum++) {
                
                Cell cell = row.createCell(cellnum);
                String address = new CellReference(cell).formatAsString();

                CellStyle cellStyle = wb.createCellStyle();
                cellStyle.setWrapText(true);

                cell.setCellStyle(cellStyle);

                address += "\ntest";

                row.setHeightInPoints((2*sh.getDefaultRowHeightInPoints()));

                cell.setCellValue(address);                
            }
        }

Switching the above code to use the XSSFWorkbook instead will correctly render the spreadsheet.
Comment 1 Nick Burch 2016-11-21 10:58:40 UTC
In Excel, cell styles are workbook scoped not cell scoped. If you create lots, you'll run out...

What happens if you move the cell style creation outside of the loop, so you create the style only once?
Comment 2 Marc 2016-11-21 23:54:58 UTC
In my production code, the styles are cached and I still get the same issue. To demonstrate, I've amended the code below to create the style outside the loop (so only 1 style is created), and there is still the problem with wrapping for the streamed version of the renderer.

        SXSSFWorkbook wb = new SXSSFWorkbook(100); 
//        XSSFWorkbook wb = new XSSFWorkbook(); 

        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setWrapText(true);
        
        Sheet sh = wb.createSheet();
        for(int rownum = 0; rownum < 1000; rownum++){
            Row row = sh.createRow(rownum);
            for(int cellnum = 0; cellnum < 10; cellnum++) {
                
                Cell cell = row.createCell(cellnum);
                String address = new CellReference(cell).formatAsString();
                address += "\ntest";

                row.setHeightInPoints((2*sh.getDefaultRowHeightInPoints()));

                cell.setCellStyle(cellStyle);
                cell.setCellValue(address);                
            }
        }

If you switch to the XSSFWorkbook (commented out), then the spreadsheet is rendered correctly
Comment 3 Dominik Stadler 2017-01-06 14:13:39 UTC
I tried to reproduce this with the following test, but LibreOffice does show both resulting files equally, let's see if it is the same on Excel itself for me.


    @Test
    public void bug60396() throws Exception {
        writeWithWrappedText(new SXSSFWorkbook(100), "60396s");
        writeWithWrappedText(new XSSFWorkbook(), "60396");
    }

    private void writeWithWrappedText(Workbook wb, String file) throws Exception {
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setWrapText(true);

        Sheet sh = wb.createSheet();
        for(int rownum = 0; rownum < 1000; rownum++){
            Row row = sh.createRow(rownum);
            for(int cellnum = 0; cellnum < 10; cellnum++) {

                Cell cell = row.createCell(cellnum);
                String address = new CellReference(cell).formatAsString();
                address += "\ntest";

                row.setHeightInPoints((2*sh.getDefaultRowHeightInPoints()));

                cell.setCellStyle(cellStyle);
                cell.setCellValue(address);
            }
        }

        OutputStream out = new FileOutputStream("/tmp/" + file +  ".xlsx");
        try {
            wb.write(out);
        } finally {
            out.close();
        }

        // also reformat the resulting files to make it easier to compare them
        OOXMLPrettyPrint.main(new String[] {
                "/tmp/" + file +  ".xlsx",
                "/tmp/" + file +  "-pretty.xlsx"
        } );

        wb.close();
    }
Comment 4 Dominik Stadler 2017-06-16 11:01:55 UTC
I have now tried with latest trunk and could not reproduce this, if you are still having this issue with the latest version of POI then please reopen this issue and attach screenshots that show the difference that you see together with the exact version of Excel that you are using.