Avatar of DangItMarilyn
DangItMarilyn
 asked on

How would I convert a complex, multi sheet 256mb excel file to PHP?

We have a very complex excel spreadsheet that has kind of gotten out of hand. It's currently over 256 MB in size and takes over 1/2 hour to open on an Intel i3 with 8gb of ram and a zippy AMD SSD SATA drive. A full half hour. Ugh.

This file has multiple calls to several different sheets for computing dates, times, and more fancy calculations.

I have heard from others that there is a way to upload this file to our server and keep the calculations but speed up the process 1000x by using PHP. Is that true?

I have general PHP experience using My SQL and can code up a site pretty easily, but the conversion is where I'll need the most help. If that's enough info, please let me know. If not, just ask! I've got six months to put this together which I'm assuming is plenty of time. Thanks!
PHPMySQL ServerMicrosoft Excel

Avatar of undefined
Last Comment
Martin Liss

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Simon

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Neil Russell

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
DangItMarilyn

ASKER
It runs quite a bit faster when I put it to Manual calculations. Is there a way to trace the calcs to see what is taking up the most time?
Simon

> Is there a way to trace the calcs to see what is taking up the most time?

See this MS Office Dev Centre link for useful info on calculation speeds.
Neil Russell

CAUTION:

Ensure that EVERYONE that ever uses this sheet understands that you are running on MANUAL CALC mode.  I know of a man who lost his job when he submitted a revised budget, up by near £0.4M, because his last revisions were made and not recalculated.......
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Simon

+1 on making sure workbook is calculated before quoting results.

Use the option 'always calculate before save' and/or add a large 'calculate now' button.

I also like Neilsr's suggestion of breaking the workbook into separate smaller workbooks if at all possible so you're only opening the workbook with the business logic in and allowing it to refer to closed workbooks for reference values. With a bit of testing you'd see which of the other workbooks could be opened to speed up the referencing - e.g. getting values from closed workbooks can be slower, so if 100s or 1000s of values are being looked up it is better to open the source workbook.
Martin Liss

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.