0

I hope Mark Baker can help me: This seems redundant but I have the simplest need of PHP Excel and it isn't working. I simply need to read in .xlsx and .xls files submitted by the user (internal admin site). The library is so incredibly slow that it is unusable right now. I can read massive .csv files in no time (with my own code) but even a 20 row x 30 column excel file is taking over 30 seconds and sometimes close to a minute to read. I've tried using all the examples I could and in reading over MANY stack overflow questions, MANY people have the same issue. This isn't a "loader" issue as I'm not using a loader.

I have 3 lines of code in particular:

$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileName);

It is that 3rd line of code that is so slow every time.

Is there something I'm missing? Something EVERYONE is missing? Is there a reason it is so slow?

Any help would be much appreciated.

Thanks! -scott

Scott
  • 695
  • 1
  • 8
  • 16
  • `strace` the script to see what the program does, if you are interested why it's slow. My assumption would be that a pure php script which must parse something from M$ is doing a lot of work, hence it takes time. This is usually why we don't use excel files for submitting data, but some other format that's quicker to parse (.csv, json etc.). You can always resort to job queueing and asynchronous parsing so that people who submit excel files don't have to wait until the file is parsed. – N.B. Jul 23 '15 at 15:03
  • possible duplicate of [PHPExcel taking an extremely long time to read Excel file](http://stackoverflow.com/questions/16742647/phpexcel-taking-an-extremely-long-time-to-read-excel-file) – Rob W Jul 23 '15 at 15:05
  • If not a duplicate, let me know. Try those suggestions. Also, before loading, try `$objReader->setReadDataOnly( true );` if you're not writing. – Rob W Jul 23 '15 at 15:06
  • the setReadDataOnly had no change in my case. and yea, it isn't a duplicate because that solution was to fix their loader - I have no loader – Scott Jul 23 '15 at 15:07
  • I would avoid the Excel format and go with a plain CSV list, the reason is the excel format means the whole file must be loaded into memory, whereas using CSV it can be read 1 line at a time. – ArtisticPhoenix Jul 23 '15 at 15:08
  • We use CSVs as much as possible but some of these files are from vendors handing off complicated Excel files. We could copy/paste as values into a CSV (or save as CSV) but the end user who is using this system isn't that technical - need to make it easy for them. That is a GREAT idea though - just avoid Excel. – Scott Jul 24 '15 at 16:18

2 Answers2

1

If users can upload files of any size, even after optimizing your current code (and others already suggested a bunch of things), you'll potentially end up with the same problems you have now.

You need to read 600 cells now but what if a user uploads a file that's way bigger than this? 600,000 cells? This is exactly what happened to me...

If that's something that you want to consider, I'd recommend you to take a look at Spout: https://github.com/box/spout. No need to worry about performance, memory or file size anymore :)

Adrien
  • 1,929
  • 1
  • 13
  • 23
  • I don't mind it taking a while on a large file but when it is a small file it should be quick. And yes, that will happen but we are all used to it. – Scott Jul 24 '15 at 16:16
  • I looked into spout - it was equally slow - identically slow on this particular file. – Scott Jul 24 '15 at 16:16
  • Wow, that's weird. It usually takes less than a second to process 600 cells. Does your file contains a lot of extra data (charts, multiple sheets, ...)? – Adrien Jul 24 '15 at 20:43
-1

One of those things where you spend hours finding the problem and then submit the question and 1 minute later find an answer. My Excel file had 2 sheets - the second sheet was enormous... Click here to see Mark mentioning this idea:

PHPExcel runs out of 256, 512 and also 1024MB of RAM

Community
  • 1
  • 1
Scott
  • 695
  • 1
  • 8
  • 16