Writing large number of rows in 'Excel2007' writer

Topics: User Forum
Jun 1, 2012 at 12:47 PM

Hello! Help me, please, to find an error in my code

My code is something like this:

$query = "SELECT ... FROM ... WHERE ...";
$stmt = oci_parse( $conn, $query );
oci_execute( $stmt, OCI_DEFAULT )
$nrows = oci_fetch_all( $stmt, $results );
//
// At this point $nrows is 87784
//
require_once 'PHPExcel/Classes/PHPExcel.php';
require_once 'PHPExcel/Classes/PHPExcel/Cell.php';
require_once 'PHPExcel/Classes/PHPExcel/Writer/Excel2007.php';
PHPExcel_Settings::setCacheStorageMethod( PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip );
PHPExcel_Settings::setLocale( 'ru' );
$xls = new PHPExcel();
$sheet = $xls->getSheet(0);
...
for( $i = 0; $i < $nrows; $i++ )
{
// put SELECTed row into spreadsheed row
}
...
$xlsWriter = new PHPExcel_Writer_Excel2007( $xls );
$fn = tempnam( '', 'stat' );
$xlsWriter->save( $fn );

When I open saved file by OpenOffice 3.1.1 - I see only 65536 rows in sheet, not 87000+  as expected. So where is an error in my code?

Sorry, but I can't test this examlpe with MS Excel - I have LINUX on my computer.

Coordinator
Jun 4, 2012 at 12:13 PM

There is a 65536 limit to the number of rows in a sheet in the Excel5 Writer, but no such limit in the Excel2007 Writer, so I'm unsure why this is happening. Will conduct some tests to see if I can replicate it

It is worth noting that your method of using oci_fetch_all( $stmt, $results ); will be incredibly memory hungry. It would probably be more efficient to use oci_num_rows to rerieve the value of $nrows, and then to fetch each row in turn using oci_fetch_array().

Jun 4, 2012 at 12:19 PM

perhaps the limitation is in  openoffice? Have you tried opening the file with ms excel?

Jun 5, 2012 at 8:34 AM
MarkBaker wrote:

It is worth noting that your method of using oci_fetch_all( $stmt, $results ); will be incredibly memory hungry. It would probably be more efficient to use oci_num_rows to rerieve the value of $nrows, and then to fetch each row in turn using oci_fetch_array().

Memory limit is set to be 512 Mbytes, so this is not a problem.

Jun 5, 2012 at 9:30 AM

could you try installing a more recent version of openoffice? 3.1 is really old. I remember having problems with large worksheets in older versions. The current version works fine with xlsx sheets up to 1M rows (I have tested this ;)

 

Btw, 512MB is not a lot of memory when handling such large datasets (I've used as much as 24GiB for extremely large worksheets), so please check your logs for out of memory errors!

Jun 7, 2012 at 8:12 AM
borft wrote:
Btw, 512MB is not a lot of memory when handling such large datasets (I've used as much as 24GiB for extremely large worksheets), so please check your logs for out of memory errors!

512 MB limitation is a result of failure of script with 128 MB limitation.