Large Spreadsheet

[budschneehagen]

Love this tool. I've been using it for several years. Mostly I've just read from a spreadsheet and imported the data into a database.

I have a project with a rather large xlsx file, about 7MB. It has about 8 sheets, some containing hundreds of thousands of rows. One sheet does lookups after entering some data. The rest just hold data.

It takes about 10-15 seconds to load that sheet, update the cells, and read the results.

I tried to load the sheet as a query and it blows up the server every time. I'm also worried about the resources required. The app isn't facing the public, but will be used by several sales reps at once.

Im just wondering if anyone has any tips on how I can speed this up and reserve resources. It will save a ton of work if I don't have to move all this data into a database and write queries.

Thank you.

[cfsimplicity]

Hi there. Sounds like a job for the new processLargeFile() method.

I wrote a blog post on it a few weeks ago.

[budschneehagen]

That's great. It makes reading the values lightning fast. Now I need to update the file before reading it. In order to run setCellValue() I have to load a sheet into memory. Am I wrong there?

[cfsimplicity]

Right, to modify a sheet you would need to use read() without specifying a format so that it returns the workbook object, which you would then pass to setCellValue().

[budschneehagen]

OK, I'm pretty stumped. Yesterday I was using read() to read the workbook. Then I could use setCellValue to change the state and zip, and use getCellValue on a column to use the state and zip in the formula. It worked fine, but was slow. In order to try the newer readLargeFile and processLargeFile functions, I installed the latest library. Now I can change the state and zip, but the value in the lookup cell doesn't change. Did something change with the newer versions? I even tried calling the older version and it stopped working also.

[cfsimplicity]

Try using getCellValue() on the cell(s) that contain the formulas after setting the state/zip values and before writing to disk. That might trigger a recalculation?

[budschneehagen]

Well, I'm almost back where I started. LOL The problem is, I need to look up at least 6 cells that use a formula. I can create the workbook, update the static cells, and write it to file in under 20 seconds. But then looking up the 6 dynamic cells adds another 30-40 seconds. If I can somehow get it to trigger the cells to update before I write to disk, I could use the LargeFile options.

[cfsimplicity]

Not sure why getCellValue() would take 30-40 seconds to read just 6 cells, but I'm not clear what your formulas are doing.

Here's another method that seems to trigger an update on the whole workbook (so just one call rather than 6). Don't know if it would be any faster though. mppc_rates_xlsx is the variable holding the workbook object you've returned via read(). Again you would call this after setting your values and before writing to disk.

mppc_rates_xlsx.getCreationHelper().createFormulaEvaluator().evaluateAllFormulaCells( mppc_rates_xlsx );

[budschneehagen]

That actually works. Thank you. It's still taking most of 30 seconds to read the file and upload the fields I need to. But the data is then streamed using the readLargeFile quickly. It would be so nice if you could update the data without having to load the spreadsheet object.

And yes, the formulas being looked up are pretty insane.

Thanks. I appreciate your help. Im going to leave you alone now if I can.

[cfsimplicity]

I don't think it's possible to modify a spreadsheet without loading it. But hopefully you're seeing some improvement even if it's still not ideal.

In the next release I'll include support for recalculating all formulas.