Solved

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

Posted on 2014-12-30
8
143 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 250 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 250 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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 46

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Creating and Managing Databases with phpMyAdmin in cPanel.
The viewer will learn how to count occurrences of each item in an array.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

910 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now