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!
LVL 2
DangItMarilynAsked:
Who is Participating?
 
SimonConnect With a Mentor Commented:
I'd ask the 'others' for more details first! Sure, you can move all the data from your Excel worksheets to database tables, be they MySQL or another, but the time taken to open your workbook may be based on the calculations it is updating each time rather than the disk access.

How long does it take to re-open if you set calculation to manual before saving it?
 How long does it take to calculate if you then hit F9?

One major problem is re-creating all the Excel functions used in the workbook within PHP (as this blog describes).

A lot depends on the structure of your runaway workbook. If is is largely composed of huge tables of static data + a couple of sheets that do all the calculations, you can fairly easily move the static tables to a database backend and link to them from Excel, keeping the flexibility of Excel. If, on the other hand, your workbook is a series of dashboards and dynamic pivot tables and charts, and not composed of uniformly structured rows of data you might spend a long time and get little benefit.

There are certainly huge benefits to be had from moving a massive spreadsheet if you open it just to work with a tiny % of the data, but if the whole thing is a single complex mathematical model where the majority of the data is processed to provide a result each time you open it, there is slightly less to gain and more at risk.

Either way, a 200MB+ workbook is going to be very prone to corruption, so you'd do well to do something to split the data from the logic.

I would suggest that you post some non-sensitive samples of what the major tables/lists in the workbook look like.
0
 
Neil RussellConnect With a Mentor Technical Development LeadCommented:
Most times I come across Huge excel spreadsheets it is data, data and more data that occupies 90% or more of the sheet. The vast majority of this changes very rarely and is loaded in and out of memory for no good reason whatsoever every time the sheet is opened.
One of the other considerations you could make is to break up into several small(er) sheets that contain related data and then have a summary sheet that just pulls in the data needed from individual sheets in small chunks.

A well designed workbook 'set' can make your life much easier and a lot lot quicker.
0
 
DangItMarilynAuthor Commented:
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?
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
SimonCommented:
> 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.
0
 
Neil RussellTechnical Development LeadCommented:
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.......
0
 
SimonCommented:
+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.
0
 
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.