poi-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Stuart Turner <stu...@turner.eu.com>
Subject Re: [Bug 54479] New: Problems with setCellStyle when creating large xlsx files using SXSSF
Date Thu, 24 Jan 2013 23:07:37 GMT
Hi

I think the first problem is caused because you create a new CellStyle
(and CreationHelper) for each cell. What you probably want to do is
create one style and apply it to each cell.

You can do this by taking the following lines and move them above your
loops, so you only create them once.

CellStyle style = wb.createCellStyle();
CreationHelper cHelper =wb.getCreationHelper();
style.setDataFormat(cHelper.createDataFormat().getFormat("#.##"));

for(int rownum = 0; rownum < 5000; rownum++){
...
cell.setCellStyle(style);
}

For the second problem:

The default style for a row or column is not used when you create a
cell as a cell has its own default style.

What you may want to do is use the default style and apply that to each cell:

cell.setCellStyle(sheet.getColumnStyle(columnIndex));

This will use the default style for the column and apply it to the cell.

If there is no style set for a column it will return null.

I hope this helps.

Stuart



On 25 January 2013 02:44,  <bugzilla@apache.org> wrote:
> https://issues.apache.org/bugzilla/show_bug.cgi?id=54479
>
>             Bug ID: 54479
>            Summary: Problems with setCellStyle when creating large xlsx
>                     files using SXSSF
>            Product: POI
>            Version: 3.9
>           Hardware: PC
>             Status: NEW
>           Severity: major
>           Priority: P2
>          Component: SXSSF
>           Assignee: dev@poi.apache.org
>           Reporter: jvs9322@gmail.com
>     Classification: Unclassified
>
> Created attachment 29891
>   --> https://issues.apache.org/bugzilla/attachment.cgi?id=29891&action=edit
> 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.
>
> --
> You are receiving this mail because:
> You are the assignee for the bug.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
> For additional commands, e-mail: dev-help@poi.apache.org
>

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


Mime
View raw message