Hi Mark, in our system we have a limited number of workbooks that we mainly use for calculation. The process withouth pooling looks like this
- create a new instance of the workbook (one of about 20 different workbook stored in the database) - fill in some data (entered by the user) - recalculate formula (incl. creating a new formula evaluator) - read the data from excel and transform them to an output format (the workbook in the database is not changed it's kind of a template and the main purpose of poi/excel is to perform calculations) The creation of the workbook takes 200ms till 15 seconds which is too slow for the required use case (it's online processing in a web application). The process with pooling looks like this: - ask the pool to either acquire an instance of the workbook - the pool checks if there is an idle instance of the workbook available in the pool -- if not a new instance of the workbook (and the formula evaluator) is created and put in the pool -- if yes the idle instance is returned - fill in some data (entered by the user) - recalculate formula (without creating a new formula evaluator - read the data from excel and transform them to an output format - ==> "reset" the instance to it's state when acquired from the pool <== I'm currently thinking how to implement this - I guess I'll have to "remember" all changes to the workbook and "revert" them at this step. - return the instance to the pool I think SXSSF is not an option as imho it doesn't support formula evaluation. In any way memory is not a big constraint here - the issue is the time that the system requires for instantiating the workbook instances. Best Regards Kai Murphy, Mark schrieb am 11.05.16 um 16:19: > I am trying to understand just what it is you are trying to do > >>> because the creation of workbook instances is pretty costly (about 200ms to >>> 15 seconds for the workbooks we are dealing with) we implemented a pooling >>> of workbook instances which seems to work pretty well. > > What do you mean by this? How does it work? > >>> Since we are only performing limited write-operations on the workbook >>> instances (basically only calling Cell.setCellType() and >>> Cell.setCellValue()) we need a way of "cloning" the original workbook >>> values when retrieving a workbook instance from the pool, remembering them >>> and "reseting" the workbook instance when returning it to the pool by >>> setting all those remembered values to the workbook instance. > > Are you holding a group of workbooks in memory, then using them as templates > to write new workbooks with changed values? Or are you changing the values > and saving it with a new file name? > >>> I'm wondering if there is already a functionality like this in poi or if >>> somebody already did sth. similar? Otherwhise I'll have to write sth. by my >>> own (which shouldn't be too hard) - but I thought that I'll ask anyway. > >>> Performing a deep clone of the complete workbook is not an option as this >>> would make the whole pooling idea pointless. > > It may be possible to write your new workbook using SXSSF which does not hold > everything in memory so long. You can specify how many rows to keep in memory > to help control the amount of memory used. > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [email protected] > For additional commands, e-mail: [email protected] > > --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
