0 byte Excel sheet generated when trying to export a lot of data

Topics: Developer Forum, User Forum
Jan 5, 2011 at 10:49 AM
Edited Jan 5, 2011 at 10:52 AM

Hi all,

It's OK to export a few data.

When I try to export a lot of data( maybe 2000 rows or so, with columns A-P), a 0 byte Excel sheet is generated. And when open the excel sheet, it prompts the following error message:

"The file you are trying to open, 'test.xls', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file."

If I click "ok", an empty excel sheet is displayed.

But when I tried to split the data into smaller pieces, all of them could be exported as independent Excel sheets. So, it seem that it's not the problem of the data. I tried both writer "Excel5" and "Excel2007", the same thing happened.

I wonder if there a maximum rows limit of the PHPExcel writers?

Thanks,

Chad.

 

Coordinator
Jan 5, 2011 at 10:58 AM
chad wrote:

I wonder if there a maximum rows limit of the PHPExcel writers?

Yes, there is. The Excel5 Writer limits you to 256 columns and 65,536 rows in a worksheet. Excel2007 should limit you to 1,048,576 rows and 16,384 columns, but this isn't actively enforced.

However, the problem is much more likely to be PHP limits, either to script execution time, or memory. Check your error log to see if you're getting any errors there.

 

Jan 5, 2011 at 11:30 AM
Edited Jan 5, 2011 at 11:44 AM

Thanks MarkBaker.

I checked the error log, but didn't see anything related to this Excel generation.

Is it possible that the error is catched inside the PHPExcel writer and then returns nothing when I call "$objWriter->save('php://output');"?

Here are ome additional information, I tested on two servers:
*****************************
Server 1
OS: CentOS release 4.6
PHP: PHP 5.2.10
MemTotal:      1023812 kB
MemFree:         99784 kB
*****************************
Server 2
OS: CentOS release 5.5
PHP: PHP 5.2.10
MemTotal:      1843376 kB
MemFree:        135028 kB

Jan 5, 2011 at 12:12 PM

MarBarker, you are right, it's the problem of low memory.

I tried to allocate more memory before exporting the excel sheet:

// allocate more memory for PHP
ini_set('memory_limit','300M');
// do the export ...

It's OK now. Thanks!

Coordinator
Jan 5, 2011 at 12:13 PM
chad wrote:

I checked the error log, but didn't see anything related to this Excel generation.

Is it possible that the error is catched inside the PHPExcel writer and then returns nothing when I call "$objWriter->save('php://output');"?

 It's certainly possible, but I can't think of many cases where the Writers don't throw an exception that can be trapped within your own code. A formula that can't be handled by the Excel5 Writer, for example, will typically throw an exception. The only exceptions that I'm aware of are the time and memory limits, which (as with all PHP scripts that hit these limits) simply terminate the script, unless you have a dedicated shutdown function registered.

Jan 7, 2011 at 5:09 PM

I did some quick benchmarking myself on 5 different production servers and came up with a little fuzzy formula, this should ofcource be recalculated for each server. I am chaining 29 cells when setting the cell values,

$objPHPExcel->getActiveSheet()      
->setCellValue(chain1)      
->setCellValue(chain2)
... to 29 

 

From what learned as a rule of thumb I get ~100 rows pr sec available, and ~35 rows pr mb allowed memory. This way I pretty much can tell the user based on looking at php.ini if the export will work. A basic setup of 30 sec execution gives 3000 rows, or 35*128 available memory ~ 4000. So basically 3000 is within reach, above will pretty much fail.

I noticed that you need to dump around 1000 rows to get an accurate benchmark, one could easily make a little "benchmarker" in your system that will calculate the pr/sec and pr/mb averages.

regards,

Kim Steinhaug, kim@steinhaug.com, www.steinhaug.com

Coordinator
Jan 7, 2011 at 9:53 PM

Chaining method calls can certainly help boost the "build time" for a worksheet.

If you already have your data in a PHP array, then the worksheet's fromArray() method may also prove faster than setting cell values individually. This is particularly useful when transferring data from a database, a whole row at a time or even (as in this case) several rows at a time.

$dataArray = array( array('Number Heading','String Heading','Boolean','Date'), 
                    array(1,'Words',TRUE,'06-Jan-2011'), 
                    array(2,'More Words',FALSE,'07-Jan-2011')
                  );
$objPHPExcel->getActiveSheet()-fromArray($dataArray,NULL,'A1');
Note that the above data array will handle the dates correctly (converting the strings to Excel dates) if the worksheet has been set to use the Advanced Value Binder.
Jan 9, 2011 at 2:54 PM

Thanks for this Mark, I was searching the documentation however didn't find to much info. However I did a quick benchmark and your fromArray() example does indeed save memory, speed and code lines in the script!

It does seem that you are forgetting a vital piece of info in you example above, as the dataarray has the first line as defining datatypes. Not sure where I find theese properties, searched the *.doc files in documentation folder without luck. I just pulled the data this way:

$items = mysql_fetch_array($res,MYSQL_NUM);
$dataArray = array($items);
$objPHPExcel->getActiveSheet()->fromArray($dataArray,NULL,'A1');

The result worked, and hopefully it is correct. The above code is also so much shorter that the chained version which needs 29 chains for my export.

The benchmark I did however gave me this result:

Coordinator
Jan 9, 2011 at 10:39 PM

In my example above, the first line of $dataArray doesn't actually define datatypes, it is simple a set of strings to be stored as cell values in cells A1 to D1, in much the same way that "Words" and "More Words" are simply strings to be stored in cells B2 and B3. There's nothing special about the values in the array, other than as an example of passing a set of values to the fromArray() function to be stored as cell values. The parameters for the method are detailed in the API docs.

Jan 9, 2011 at 10:48 PM
Edited Jan 9, 2011 at 10:48 PM

In that case your example did not work, atleast not the way I expected it to work.

Since your array contained an array of 3 arrays, starting from A1 I expected the next array to be inserted from A2. Howerer it didnt,
I only got one line from it. Maby I should retry the above example if it was supposed to work the way I though it should work.

Coordinator
Jan 9, 2011 at 10:53 PM

It should have populated cells A1 to D3, though I normall only use fromArray() to populate a single row at a time (e.g. from a database row)... and I've been at a convention all weekend, so didn't really have time to test it

Coordinator
Jan 9, 2011 at 11:23 PM

The following code:

error_reporting(E_ALL);
set_time_limit(0);
date_default_timezone_set('Europe/London');
set_include_path(get_include_path() . PATH_SEPARATOR . './Classes/');
include 'PHPExcel.php';
$outputFileType = 'HTML'; 
$outputFileName = 'php://output'; 
$objPHPExcel = new PHPExcel(); 
$dataArray = array( array('Number Heading','String Heading','Boolean','Date'),
                     array(1,'Words',TRUE,'06-Jan-2011'),
                     array(2,'More Words',FALSE,'07-Jan-2011')
                  );
$objPHPExcel->getActiveSheet()->fromArray($dataArray,NULL,'A1');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, $outputFileType);
$objWriter->save($outputFileName);

generates a worksheet looking something like:
Number Heading String Heading Boolean Date
1 Words 1 06-Jan-2011
2 More Words   07-Jan-2011

 

 

Oct 7, 2011 at 11:48 PM
Edited Oct 7, 2011 at 11:49 PM

i read that fromArray() is faster than iterating over each cell individually so i converted a few scripts over to use fromArray. now, i cannot set the data types on these cells. i have tried everything including a custom value binder to no avail. This wouldn't be a problem if PHPExcel didn't take the PHP string "02021602201" that I am passing within an array to fromArray and cut the leading zero off. It does this even if I set the number format type to TEXT. This string appears in a column with mixed alphanumeric data as well so treating all values in the column as numbers is not an option. The only way I have found so far to get the leading 0 to show up is to use setCellValueExplicit and this obviously doesn't work with fromArray.

Sep 6, 2012 at 7:36 PM

Hi!

First, congratulations for great code for PHP!!

I downloaded the source code of PHPExcel, but am having trouble exporting files ....

I did the installation, and when I export a sample file (MySQL SELECT from table) works if mysql table have any less than 10,000 rows of data... When you have more than 10,000 rows of data, generates the error:
"Fatal error: Allowed memory size of 536870912 bytes exhausted (tried to allocate 24 bytes) in / var / www / xls / Classes / PHPExcel / Cell.php on line 506"

In my PHP file for generation, i'm setting:
ini_set ("memory_limit", "512M");
ini_set ("max_execution_time", "300");

But if i increasing the memory_limit of PHP, the server will drop....

What should I do?

Please I need urgent help!!

 

Thanks Diego !!