Solved

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

Posted on 2014-12-30
8
150 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
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 48

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

623 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