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: [email protected]
Reporter: [email protected]
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: [email protected]
For additional commands, e-mail: [email protected]
