Bug 54479 - Problems with setCellStyle when creating large xlsx files using SXSSF
Summary: Problems with setCellStyle when creating large xlsx files using SXSSF
Status: RESOLVED REMIND
Alias: None
Product: POI
Classification: Unclassified
Component: SXSSF (show other bugs)
Version: 3.9-FINAL
Hardware: PC All
: P2 major (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2013-01-24 18:44 UTC by Jordan Stankov
Modified: 2013-01-25 13:25 UTC (History)
0 users



Attachments
Result files (89.97 KB, application/octet-stream)
2013-01-24 18:44 UTC, Jordan Stankov
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Jordan Stankov 2013-01-24 18:44:05 UTC
Created attachment 29891 [details]
Result files

Hello, I'm trying to create a DB Data extractor,who creates .xlsx files using SXSSF but quite an unexpected things happened. I tried 2 ways to do this,but I end with different problems.

1. Applying CellStyle to each cell,which needs it:
    
   Here's an example program I changed to demonstrate the problem:
  
           public static void main(String[] args) throws Throwable {
            
            SXSSFWorkbook wb = new SXSSFWorkbook(-1); 
            Sheet sh = wb.createSheet();
            
            for(int rownum = 0; rownum < 5000; rownum++){
                Row row = sh.createRow(rownum);
                for(int cellnum = 0; cellnum < 10; cellnum++){
                    
                    Cell cell = row.createCell(cellnum);
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    
                    CellStyle style = wb.createCellStyle();
                    CreationHelper cHelper =wb.getCreationHelper();
                                                    style.setDataFormat(cHelper.createDataFormat().getFormat("#.##"));
                    cell.setCellValue(1);
                    cell.setCellStyle(style);
                }

               
               if(rownum % 100 == 0) {
                    ((SXSSFSheet)sh).flushRows(100); 

               }

            }

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

            wb.dispose();

    }
}

Result: The styles are applied for some of the cell:
        The reason is flooding the Style.xml with rows of kind:

		<xf numFmtId="165" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="true"/> ...... as many times as I set CellStyle.
 
       Which leads to overflow of style variable 's' causing it to go to negative number:

Sheet.xml:
      <c r="A3677" s="-28775" t="n">
	<v>1.0</v>

So I did the same .xlsx manually and the results are:

Styles.xml:

<cellStyleXfs count="1">
		<xf numFmtId="0" fontId="0" fillId="0" borderId="0"/>
	</cellStyleXfs>
	<cellXfs count="2">
		<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>
		<xf numFmtId="164" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>
	</cellXfs>
	<cellStyles count="1">
		<cellStyle name="Normal" xfId="0" builtinId="0"/>
	</cellStyles>
	<dxfs count="0"/>
	<tableStyles count="0" defaultTableStyle="TableStyleMedium2" defaultPivotStyle="PivotStyleMedium9"/>
	<extLst>
		<ext uri="{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main">
			<x14:slicerStyles defaultSlicerStyle="SlicerStyleLight1"/>
		</ext>
	</extLst>

And Sheet.xml:

     <c r="B1" s="1">
       <v>1</v>


Is this a problem with the SXSSF or I'm trying something that's not meant to work this way.


2. Setting Default CellStyle to a column to skip all the CellStyle setting:
    
   The above program changed to show the problem in this approach:
   
   public class testing2 {


        public static void main(String[] args) throws Throwable {
            
            SXSSFWorkbook wb = new SXSSFWorkbook(-1);
            Sheet sh = wb.createSheet();
            for(int col=0;col<10;col++){
                    CellStyle style = wb.createCellStyle();
                    CreationHelper cHelper =wb.getCreationHelper();
                    
                    style.setDataFormat(cHelper.createDataFormat().getFormat("#.##"));
                    sh.setDefaultColumnStyle(col, style);
            }
            
            for(int rownum = 0; rownum < 5000; rownum++){
                Row row = sh.createRow(rownum);
                for(int cellnum = 0; cellnum < 10; cellnum++){
                    
                    Cell cell = row.createCell(cellnum);
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    
                    cell.setCellValue(1);

                }

               if(rownum % 100 == 0) {
                    ((SXSSFSheet)sh).flushRows(100); 
               }

            }

            FileOutputStream out = new FileOutputStream("F:/sxssf2.xlsx");
            wb.write(out);
            out.close();


            wb.dispose();
    }
}
 
Result: I end up with hidden columns and not applied formatting.


This is my second attempt to do the task,but may be I'm doing something wrong.
Could you give me some advice how to do this, or hopefully fix the problems if this is the case.
Thank you a lot.

P.S. :I'm Attaching the results of the attempts.
Comment 1 Yegor Kozlov 2013-01-25 10:05:08 UTC
Cell styles should be created outside of the loop, see http://poi.apache.org/faq.html#faq-N100EF