Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-12-30
8
Medium Priority
?
158 Views
Last Modified: 2015-02-02
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!
0
Comment
Question by:DangItMarilyn
8 Comments
 
LVL 18

Accepted Solution

by:
Simon earned 1000 total points
ID: 40523977
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
 
LVL 37

Assisted Solution

by:Neil Russell
Neil Russell earned 1000 total points
ID: 40524047
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
 
LVL 2

Author Comment

by:DangItMarilyn
ID: 40524084
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 18

Expert Comment

by:Simon
ID: 40525263
> 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
 
LVL 37

Expert Comment

by:Neil Russell
ID: 40525296
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
 
LVL 18

Expert Comment

by:Simon
ID: 40525575
+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
 
LVL 49

Expert Comment

by:Martin Liss
ID: 40583567
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Suggested Courses

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question