Solved

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

Posted on 2014-12-30
8
145 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

839 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