Problem Saving File and Long Loading Times

Topics: Developer Forum
Jan 17, 2011 at 12:28 PM

Good Morning:

I'm having problems saving an Excel2007 spreadsheet with PHPExcel.

Here is my code, it's really simple as I'm just experimenting:

require_once '../Classes/PHPExcel/IOFactory.php';
$objPHPExcel = PHPExcel_IOFactory::load("ks.xlsx");
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));
It loads the Excel spreadsheet fine and I am able to get the Cells it, however, I did do some debugging to see where the error lied, and it lies in the save line. The Excel Spreadsheet does contain a few errors, such as 'Invalid Cell Reference Errors', and 'Divide By Zero Error'. Can that be why it's having trouble resaving the file? If you would like the Excel Spreadsheet that I am using, PM me, or Reply stating.
Also, while loading the file, it takes around 30s to load it. Can it be because of the 92.5 MB peak memory usage or how large the file is (567KB)? 
Thank you very much, I hope my questions may be answered.
Coordinator
Jan 17, 2011 at 12:42 PM
Manifest wrote:

I'm having problems saving an Excel2007 spreadsheet with PHPExcel.

It loads the Excel spreadsheet fine and I am able to get the Cells it, however, I did do some debugging to see where the error lied, and it lies in the save line. The Excel Spreadsheet does contain a few errors, such as 'Invalid Cell Reference Errors', and 'Divide By Zero Error'. Can that be why it's having trouble resaving the file? If you would like the Excel Spreadsheet that I am using, PM me, or Reply stating.

Are there any real; errors here? or is this just a question about speed/performance? You don't desrcibe what result you get in the saved file? or any messages you might be seeing on screen?

Can't Excel read it? Is it missing data that was in the original loaded file? What is the actual problem?

 

Manifest wrote:
Also, while loading the file, it takes around 30s to load it. Can it be because of the 92.5 MB peak memory usage or how large the file is (567KB)? 
Thank you very much, I hope my questions may be answered.

 There are plenty of comments here on the boards about speed and memory usage.

A filesize is pretty meaningless when describing a workbook... it's much more important to know how many cells it contains. However, all you're doing is reading and then rewriting a workbook "as is", so there isn't much you can do to improve the speed of execution. However, if you know what filetype it is, then use:

$objReader = PHPExcel_IOFactory::createReader("Excel2007");
$objPHPExcel = $objReader->load("ks.xlsx");

rather than

$objPHPExcel = PHPExcel_IOFactory::load("ks.xlsx");

because then PHPExcel doesn't have to identify the filetype itself.

It won't make much difference though.

 

Jan 17, 2011 at 1:19 PM

Sorry for not explaining myself clearly.

1) There are not any real errors, Excel 2007 opens it up smoothly.

2) There is no saved file created from that code I have written. However,  I have tried to cut the Excel sheet down, to 2 Worksheets, (Usually 34 Worksheets), and the xlsx file was created, but didn't finish creating the file and halted. I opened up the xlsx spreadsheet and a 'Excel found unreadable content'..., and did not recover anything.

3) On the screen, I get an HTTP 500 Internal Server Error, most of the time. Or it loads the page, but it halts during the Save line. No error messages telling me what the problem is, no xlsx file created during these times.

4) There are about  (77Rx33C = ) 2,849 Cells for each of the 31 of the worksheets, the other 3 are information that the Excel spreadsheet gets its resources from.

Thank you for your quick reply.

Jan 21, 2011 at 4:02 PM

I got rid of the HTTP 500 Error by displaying the Errors.

Though, I have an unandled exception:

Fatal error: Uncaught exception 'Exception' with message 'PrevMonth!A17 -> Formula Error: An unexpected error occured' 
in C:\www\Classes\PHPExcel\Cell.php:284 Stack trace: 
#0 C:\www\Classes\PHPExcel\Writer\Excel2007\Worksheet.php(965): PHPExcel_Cell->getCalculatedValue() 
#1 C:\www\Classes\PHPExcel\Writer\Excel2007\Worksheet.php(911): PHPExcel_Writer_Excel2007_Worksheet->_writeCell(Object(PHPExcel_Shared_XMLWriter), Object(PHPExcel_Worksheet), 'A17', Array, Array) 
#2 C:\www\Classes\PHPExcel\Writer\Excel2007\Worksheet.php(82): PHPExcel_Writer_Excel2007_Worksheet->_writeSheetData(Object(PHPExcel_Shared_XMLWriter), Object(PHPExcel_Worksheet), Array) 
#3 C:\www\Classes\PHPExcel\Writer\Excel2007.php(264): PHPExcel_Writer_Excel2007_Worksheet->writeWorksheet(Object(PHPExcel_Worksheet), Array) 
#4 C:\www\Tests\kstest.php(38): PHPExcel_Writer_Excel2007->save('C:\www\Tests\ks...') 
#5 {main} thrown in C:\www\Classes\PHPExcel\Cell.php on line 284
In the worksheet PrevMonth, A17 contains an Ivalid Cell Reference. It's getting its data from Another Sheet, named Employee List and it's getting the information line by line.
e.g. "='Employee List'!A9"
If I just delete the #REF! error, it continues with the write.
Just thought I should post my findings, and hope in the near future you can fix the exception.
Thanks alot!
Manifest
Coordinator
Jan 21, 2011 at 10:26 PM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.