3

I know there are already many questions similar to this one (I have read most of them), but I still haven't found a solution so I figured I'd ask my own question with specifics.

Ok, so I have spent the past two days researching and trying to parse an Excel (.xlsx) spreadsheet in php. I've tried using the PHPExcel Library, however, it times out when trying to load the spreadsheet into memory. The spreadsheet is not very large, only 240kb, but it does have 40+ worksheets each heavily formatted (i.e. cell color, font size/color, etc...). Since I don't need any of the formatting I tried $objReader->setReadDataOnly(true); in order to ignore it, but it still times out when trying to read the file.

So my next step was to try and load only the last couple of worksheets I actually needed using, $objReader->setLoadSheetsOnly(array(42,43,44,45)); This loads successfully, but now the problem is that the last sheets are almost completely formulas which total and read data from the previous worksheets. And since I excluded those earlier worksheets in order to load the document I can't use $cell->getCalculatedValue(); when retrieving each cell's value, thus making everything else obsolete.

So my question is, is there something else I can do in order to load and read this Excel spreadsheet?

Is there another library I should try and use? Or a completely different method I should try, since my end goal is to be able to upload parts of the data to a database?

Here is a link to the particular spreadsheet I am using.

Thanks for taking the time to read this and for any help you can provide.

pnuts
  • 58,317
  • 11
  • 87
  • 139
unrealgam3r11
  • 73
  • 1
  • 5
  • If it timed out with `$objReader->setReadDataOnly(true);` then you'll be hard put finding any library that can handle this file other than one based on a native-code extension that reads the file such as [COM](http://www.php.net/manual/en/book.com.php) or [Ilia Alshanetsky's](https://github.com/iliaal/php_excel) Excel extension to the commercial [libXL](http://www.libxl.com/); but try with the list [here](http://stackoverflow.com/questions/3930975/alternative-for-php-excel) – Mark Baker Aug 05 '13 at 07:16
  • 1
    Call time to read the Workbook on my development machine using the 1.7.9 production release of PHPExcel was 1.9218 seconds (46 worksheets in all) with all formatting, and memory usage was 19.75 MB: 1.6848 seconds without formatting, and 17.25 MB memory.... what version of PHPExcel are you using? – Mark Baker Aug 05 '13 at 08:08
  • Oh, well I think you just solved my problem... I thought I was using the latest version of PHPExcel, but it turns out I was using 1.7.6, not really sure how that happened. I'll try out 1.7.9 when I get home from work and let you know. Thanks for your help. – unrealgam3r11 Aug 05 '13 at 19:04
  • @MarkBaker Ok, it must be something with my php and/or Apache setup, because when I try to load the workbook it still times out even when given 60 seconds. I have switched to version 1.7.9, so is there some setting you can think of that I might be missing in the php.ini or somewhere else? – unrealgam3r11 Aug 05 '13 at 23:03

2 Answers2

1

Ok, I figured out why it was taking so long, one partial reason might be I was using an outdated version of PHPExcel, but I believe the real culprit was I had left XDebug enabled from a previous project. Because once I disabled that the script loaded the workbook just fine at a speed of 1.5001 seconds and using 17.5 MB of memory. Thanks for the help guys, it got me looking in the right direction.

unrealgam3r11
  • 73
  • 1
  • 5
0

use below code

require_once 'Classes/PHPExcel.php';
$objPHPExcel = PHPExcel_IOFactory::load("myExcelFile.xls");
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'HTML');
$objWriter->writeAllSheets();
$objWriter->save('php://output');

and you still getting time out error that means that your script takes longer than 30 seconds to run, but that PHP has a limit of 30 seconds execution time

Please increase max execution time for php script

liyakat
  • 11,825
  • 2
  • 40
  • 46
  • I think you misunderstood my question. I am not trying to write an excel file, only reading it. Also increasing the execution time only works to a certain extent before becoming a ridiculously large value at some point, which is not acceptable for the end user. Thanks though – unrealgam3r11 Aug 05 '13 at 05:36
  • sorry for that, please use this answer http://stackoverflow.com/questions/4666746/how-to-read-large-worksheets-from-large-excel-files-27mb-with-phpexcel for chunk read if possible you can solve your issue – liyakat Aug 05 '13 at 05:49
  • @likayat - chunk reads are for memory reduction, and actually increase execution time, so unlikely to help – Mark Baker Aug 05 '13 at 07:16