problem with generating too many cell styles

[GoogleCodeExporter]

from https://vaadin.com/forum/-/message_boards/view_message/579716

I got another issue that "bruno mertiku" posted to this thread as well. I am 
exporting a pretty big table (20+ columns x 500+ rows) and also see the same 
error message "The maximum number of cell styles was exceeded". I could see it 
is an error thrown by POI but is there any easy way to set less style to avoid 
the error? Thanks again.

Thanks
Gerry

com.vaadin.event.ListenerMethod$MethodException
Cause: java.lang.IllegalStateException: The maximum number of cell styles was 
exceeded. You can define up to 4000 styles in a .xls workbook
at com.vaadin.event.ListenerMethod.receiveEvent(ListenerMethod.java:510)
at com.vaadin.event.EventRouter.fireEvent(EventRouter.java:164)
at com.vaadin.ui.AbstractComponent.fireEvent(AbstractComponent.java:1198)
at com.vaadin.ui.Button.fireClick(Button.java:539)
at com.vaadin.ui.Button.changeVariables(Button.java:206)
at 
com.vaadin.terminal.gwt.server.AbstractCommunicationManager.handleVariableBurst(
AbstractCommunicationManager.java:1305)
at 
com.vaadin.terminal.gwt.server.AbstractCommunicationManager.handleVariables(Abst
ractCommunicationManager.java:1224)
at 
com.vaadin.terminal.gwt.server.AbstractCommunicationManager.doHandleUidlRequest(
AbstractCommunicationManager.java:739)
at 
com.vaadin.terminal.gwt.server.CommunicationManager.handleUidlRequest(Communicat
ionManager.java:296)
at 
com.vaadin.terminal.gwt.server.AbstractApplicationServlet.service(AbstractApplic
ationServlet.java:501)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:511)
at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:401)
at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:182)
at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:766)
at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:450)
at 
org.mortbay.jetty.handler.ContextHandlerCollection.handle(ContextHandlerCollecti
on.java:230)
at 
org.mortbay.jetty.handler.HandlerCollection.handle(HandlerCollection.java:114)
at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
at org.mortbay.jetty.Server.handle(Server.java:326)
at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:542)
at 
org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:945)
at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:756)
at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:212)
at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:404)
at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:410)
at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:582)
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:114
4)
at com.vaadin.addon.tableexport.ExcelExport.getCellStyle(ExcelExport.java:591)
at com.vaadin.addon.tableexport.ExcelExport.addDataRow(ExcelExport.java:500)
at com.vaadin.addon.tableexport.ExcelExport.addDataRows(ExcelExport.java:438)
at com.vaadin.addon.tableexport.ExcelExport.convertTable(ExcelExport.java:243)
at com.vaadin.addon.tableexport.TableExport.export(TableExport.java:38)
at com.nomura.audit.ui.RawTable.export(RawTable.java:81)
at com.nomura.audit.ui.AuditApplication.export(AuditApplication.java:156)
at com.nomura.audit.ui.SearchForm.buttonClick(SearchForm.java:113)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at 
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.jav
a:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.vaadin.event.ListenerMethod.receiveEvent(ListenerMethod.java:490)
... 27 more

Hi Jonathan

I did a quick workaround to avoid the issue. In my inherited class of 
ExcelExport, I override getCellStyle. Instead of creating new CellStyle upon 
every call, I just reuse the static Style. I could export large amount of data 
now and currently it is good enough for my case.

The ExportableColumnGenerator works perfectly well too.

Thanks! 
Gerry

Original issue reported on code.google.com by jnas...@gmail.com on 12 Oct 2011 at 2:38

[GoogleCodeExporter]

Fixed.  Handles CellStyles a lot more efficiently.  Previously was creating 
them new each time, which wasn't necessary and ran against a 4000 per sheet 
limit. There is now a separate dateDataCellStyle and a doubleDataCellStyle.

Original comment by jnas...@gmail.com on 12 Oct 2011 at 11:57

• Changed state: Fixed