#377 Running out of cell styles in XlsDataSetWriter

[MSA]

Writing out an excel file with 10,000 numeric cells causes
java.lang.IllegalStateException: The maximum number of cell styles was exceeded. You can define up to 4000 styles in a .xls workbook
at org.apache.poi.hssf.usermodel.HSSFWorkbook.createCellStyle(HSSFWorkbook.java:1194)
at org.apache.poi.hssf.usermodel.HSSFWorkbook.createCellStyle(HSSFWorkbook.java:100)
at org.dbunit.dataset.excel.XlsDataSetWriter.setNumericCell(XlsDataSetWriter.java:230)
at org.dbunit.dataset.excel.XlsDataSetWriter.write(XlsDataSetWriter.java:119)
at org.dbunit.dataset.excel.XlsDataSet.write(XlsDataSet.java:101)

The reason is that every call to method setNumericCell(Cell cell, BigDecimal value, Workbook workbook) creates a new CellStyle instance, and poi seems to have a limit of 4000 of them.

One possible fix would be to reuse the CellStyle instances and only create new ones if a cell actually does have a style that differs from the already created ones. Based on the current code of XlsDataSetWriter, there seems to be less than a handful of different styles.

[Jeff Jensen]

Thanks for the report.
Would be great if you created a test from this code and made a proposed fix patch!

[MSA]

Not sure how to create the fix patch, but I am attaching my new version with the proposed fix code fragments marked in comments (look for "Proposed fix for bug #377").

I will have to look for the test file; will follow up when I find it.

[Jeff Jensen]

Great, thanks!

Regarding tests, I did not find a test class for this class so please create a new one.

While dbUnit can use all the tests you choose to write (yes, that's an invite to improve more code! :-), the key goal with each change is to protect from breaking existing things. So if you wouldn't mind at minimum making a couple of tests or so that show:
1. This problem without your change (fail) and then don't have this problem with your change.
2. Simple usage still works.

Thanks again!

[MSA]

Here is a minimalistic test case...

[MSA]

... and the XLS test input file.

Depending on your environment, you might have to change the path for the generated output-file in line 35 (the line that reads:
File _outXls = new File("/tmp/testTimestampTzOffsets_outfile.xls");
)

[MSA]

Basically, the test simply reads in an XLS file and writes it back out into a newly created output file.

[Jeff Jensen]

Thanks for the test and fix!
Committed.