The maximum number of cell styles that the exported data exceeds. You can define up to 4000 styles

The maximum number of cell styles was exceeded. You can define up to 4000 styles POI operations Excel, when the exported data is not very large, then there is no problem, and the data is many or more,

will report the following error, is due to the cell styles too many create, it is generally possible to put CellStyle settings outside the loop

The error is as follows:

caused by: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:1144)
At Org.apache.poi.hssf.usermodel.HSSFWorkbook.createCellStyle (hssfworkbook.java:88)
At Com.trendmicro.util.toExcel.ExcelExporter.addWorkbook (excelexporter.java:612)
At Com.trendmicro.util.toExcel.ExcelExporter.exportToExcel (excelexporter.java:112)
At Com.trendmicro.util.toExcel.ReportExporter.exportAutomationReport (reportexporter.java:190)
At Com.trendmicro.view.reports.TestCaseAutomationBean.exportAutoReport (testcaseautomationbean.java:856)
At Sun.reflect.NativeMethodAccessorImpl.invoke0 (Native Method)
At Sun.reflect.NativeMethodAccessorImpl.invoke (nativemethodaccessorimpl.java:39)
At Sun.reflect.DelegatingMethodAccessorImpl.invoke (delegatingmethodaccessorimpl.java:25)
At Java.lang.reflect.Method.invoke (method.java:597)
At Org.apache.el.parser.AstValue.invoke (astvalue.java:191)
At Org.apache.el.MethodExpressionImpl.invoke (methodexpressionimpl.java:276)
At Org.apache.jasper.el.JspMethodExpression.invoke (jspmethodexpression.java:68)
At Javax.faces.component.MethodBindingMethodExpressionAdapter.invoke (Methodbindingmethodexpressionadapter.java : 88)
... More

-------------Example--------------

Error example

Correct code after correction

1. CellStyle style = Workbook.createcellstyle ();
2. Font font = Workbook.createfont ();
3. Font.setboldweight (Font.boldweight_bold);
4. Style.setfont (font);
5. for (int i = 0; i < 10000; i++) {
6. Row row = Sheet.createrow (i);
7. Cell cell = Row.createcell ((short) 0);
8. Cell.setcellstyle (style);
9. }

Above method original address: http://blog.csdn.net/hoking_in/article/details/7919530

Method Two (not recommended, affect performance):

1.4000 Maximum style error

Java.lang.IllegalStateException:The Maximum number of cell styles was exceeded. You can define-4000 styles in a. xls workbook error

Find Org.zkoss.poi.hssf.usermodel.HSSFWorkbook in Zpoi.jar to modify the maximum number of styles within the Createcellstyle function. Re-Zpoi.jar can be played.

2. Public Hssfcellstyle Createcellstyle ()
4. {
6. if (workbook.getnumexformats () = = Max_styles) {
8. throw new IllegalStateException ("The maximum number of cell styles was exceeded." +
10. "Can define up to 4000 styles in a. xls workbook");
12. }
14. Extendedformatrecord XFR = WORKBOOK.CREATECELLXF ();
16. Short index = (short) (Getnumcellstyles ()-1);
18. Hssfcellstyle style = new Hssfcellstyle (index, XFR, this);
20. Workbook.createcellxfext (index);
22. return style;
24. }

IE compatibility issues

If there is a compatibility problem with the IE series browser (specifically, the report does not appear), I know that there may be problems with the Excel template, the number is written in string form, or a write date format of the cell has a write error. The specific solution is as follows.

Write code in digital form

2. public static Cell Writenumericvalue (Sheet Sheet, int row, int column,
4. Double value) {
6. Row Poirow = Sheet.getrow (row);
8. if (Poirow = = null) {
10. Poirow = Sheet.createrow (row);
12. }
14. Cell Poicell = Poirow.getcell (column);
18. if (Poicell! = null) {
20. Poirow.removecell (Poicell);
22. }
24. Poicell = Poirow.createcell (column);
26. Poicell.setcelltype (Cell.cell_type_numeric);
28. Poicell.setcellvalue (value);
30. return Poicell;
32. }

Write Date Code

2. public static Cell Writedatevalue (Workbook book, Sheet Sheet, int row,
4. int column, Date value) {
6. Row Poirow = Sheet.getrow (row);
8. Creationhelper createhelper = Book.getcreationhelper ();
10. if (Poirow = = null) {
12. Poirow = Sheet.createrow (row);
14. }
16. Cell Poicell = Poirow.getcell (column);
20. if (Poicell = = null) {
22. Poicell = Poirow.createcell (column);
24. }
28. CellStyle CellStyle = Book.createcellstyle ();
30. Cellstyle.setdataformat (Createhelper.createdataformat (). GetFormat (
32. "Yyyy-mm-dd"));
34. if (value = null) {
36. Poicell.setcellvalue (value);
38. } else {
40. Poicell.setcellvalue (New Date ());
42. }
44. Poicell.setcellstyle (CellStyle);
48. return Poicell;
50. }

Above method original address: http://realgodo.iteye.com/blog/1105529

The maximum number of cell styles that the exported data exceeds. You can define up to 4000 styles