Hi, I’m developing an application that will use Excel for all definitions of calculations/algorithms and then use Apache POI to update a set of (input) cells, evaluate another set of (output) cells and then use these results for presentations. The only Excel files I use are xlsx, so I’m only using XSSF in Apache POI.
So far, all is working well. However, for some Excel models calculations are quite slow and I would like to ensure I use Apache POI the best way. My constraints are that the Excel files are loaded as streams (on the server side I can only store the data in the files in a DB, not the actual files). Apart from that I can flex pretty much everything as long as I can update a fixed set of inputs and get the fixed set of outputs. From my searches on Google and in the archives, I have found little help so far in terms of optimizing this use case. My process for making calculations, all in a HTTP request, is basically; * Retrieve which inputs that are updated and to which values * Load the Excel file as an InputStream from the byte[] I have stored in a DB * Create a XSSFWorkbook from the InputStream * Update the inputs * Evaluate my output cells (from FormulaEvaluator's evaluate(Cell cell) * Pass back the updates to the user Even for very simple models, my performance for the steps above varies between 30 and 700ms excluding the quick retrieval from the DB. I would like to get a perspective on what you think would be most worthwhile to optimize, eg 1) Ensure I only save relevant sheets/cells from the files (to speed up retrieval/parsing) 2) Override parsing in XSSFWorkbook to avoid unnecessary work such as themes, styles etc 3) Pool the workbooks to avoid creating them every time (even though I need to be able to update them separately for every request) 4) Something else :-) Any other ideas? Greatly appreciated! Many thanks and best regards, Markus --------------------------------------------------------------------- To unsubscribe, e-mail: user-unsubscr...@poi.apache.org For additional commands, e-mail: user-h...@poi.apache.org
