You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by bu...@apache.org on 2013/01/24 19:44:05 UTC
[Bug 54479] New: Problems with setCellStyle when creating large xlsx
files using SXSSF
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
Re: [Bug 54479] New: Problems with setCellStyle when creating large
xlsx files using SXSSF
Posted by Stuart Turner <st...@turner.eu.com>.
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, <bu...@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
[Bug 54479] Problems with setCellStyle when creating large xlsx
files using SXSSF
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=54479
Jordan Stankov <jv...@gmail.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |RESOLVED
Resolution|--- |REMIND
--
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
[Bug 54479] Problems with setCellStyle when creating large xlsx
files using SXSSF
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=54479
Yegor Kozlov <ye...@dinom.ru> changed:
What |Removed |Added
----------------------------------------------------------------------------
OS| |All
--- Comment #1 from Yegor Kozlov <ye...@dinom.ru> ---
Cell styles should be created outside of the loop, see
http://poi.apache.org/faq.html#faq-N100EF
--
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