You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by "jen.luke" <je...@seznam.cz> on 2012/07/10 14:36:07 UTC

SXSSFWorkbook with CellStyle -format issue (big file)

Hi,
I have problem with setting color for cell. 
When I open the excel with 10K rows I receive the error:

Repaired Part: /xl/worksheets/sheet1.xml part with XML error. Load error.
Line 17480, column 34.
Removed Feature: Format from /xl/styles.xml part (Styles)
Removed Records: Cell information from /xl/worksheets/sheet1.xml part
Repaired Records: Cell information from /xl/worksheets/sheet1.xml part

btw: (output file has also strange gap between rows 2186 - 4370) :-/

note:
if I have small excel (1K) row the color is set ok

POI version 3.8
Excel version 2007 with SP3

simple test code:

public static void main(String[] args) throws Throwable {
         int rowCount = 10000;

        Workbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory,
exceeding rows will be flushed to disk
        Sheet sh = wb.createSheet();
        for(int rownum = 0; rownum < rowCount ; rownum++){
            Row row = sh.createRow(rownum);
            for(int cellnum = 0; cellnum < 15; cellnum++){
                Cell cell = row.createCell(cellnum);
                cell.setCellValue("mock");
                
                XSSFCellStyle style = (XSSFCellStyle) wb.createCellStyle();
               
style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
                style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                cell.setCellStyle(style);
            }

        }
        FileOutputStream out = new FileOutputStream("c:/sxssf.xlsx");
        wb.write(out);
        out.close();
    }

thanks for advice.
Luke 

--
View this message in context: http://apache-poi.1045710.n5.nabble.com/SXSSFWorkbook-with-CellStyle-format-issue-big-file-tp5710422.html
Sent from the POI - User mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: SXSSFWorkbook with CellStyle -format issue (big file)

Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Good, and thanks for letting me know.

There is an additional point to note with styles. As they are shared, if you
recover a style from a cell and then change it, that change will be
reflected in all of the cells that share the style. This behaviour will be
the same if you open a workbook created with Excel just as if you were
working with one created through POI. The trick, in this case, is to use the
cloneStyleFrom() method of the CellStyle class to create a 'copy' of the
style, top modify that copy and apply it to the cell.

--
View this message in context: http://apache-poi.1045710.n5.nabble.com/SXSSFWorkbook-with-CellStyle-format-issue-big-file-tp5710422p5710427.html
Sent from the POI - User mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: SXSSFWorkbook with CellStyle -format issue (big file)

Posted by "jen.luke" <je...@seznam.cz>.
perfect... it works... thanks

--
View this message in context: http://apache-poi.1045710.n5.nabble.com/SXSSFWorkbook-with-CellStyle-format-issue-big-file-tp5710422p5710425.html
Sent from the POI - User mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: SXSSFWorkbook with CellStyle -format issue (big file)

Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Cannot say for certain that this is the error but my eye was drawn to the
fact that you are creating identical style objects on each iteration of your
main loop. Styles are shared and so you ought to create them outside of the
loop and then apply as necessary, something like this;

public static void main(String[] args) throws Throwable {
         int rowCount = 10000;

        Workbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory,
exceeding rows will be flushed to disk
        Sheet sh = wb.createSheet();
        XSSFCellStyle style = (XSSFCellStyle) wb.createCellStyle();
       
style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        for(int rownum = 0; rownum < rowCount ; rownum++){
            Row row = sh.createRow(rownum);
            for(int cellnum = 0; cellnum < 15; cellnum++){
                Cell cell = row.createCell(cellnum);
                cell.setCellValue("mock");
                cell.setCellStyle(style);
            }

        }
        FileOutputStream out = new FileOutputStream("c:/sxssf.xlsx");
        wb.write(out);
        out.close();
    } 



--
View this message in context: http://apache-poi.1045710.n5.nabble.com/SXSSFWorkbook-with-CellStyle-format-issue-big-file-tp5710422p5710424.html
Sent from the POI - User mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org