MySQL killing Excel creation

Jan 16, 2009 at 2:00 AM
I am writing an excel workbook with 5 sheets in it.  I am able to create the sheets with no problem.  The last sheet is where I have the problem.  I am pulling data from a mysql database to populate the cells.  Works great on all sheets except the last one.  I can succesfully insert 800 records into the last sheet with no problem.  Once I go above that, the excel dies on me.  I can open the file created but the last sheet has no records in it and there is an excel error that says that the file is currupted.  The amount of records I need to import into the last sheet is around 1500.  The table is only 3 cells long. So, 1500 records with three columns.

Here is my code.  Anyone know what is wrong?



//////////////////////////////////////////////////////////////////////////////////////////////////////

<?php
/**
 * PHPExcel
 *
 * Copyright (C) 2006 - 2009 PHPExcel
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 *
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
 *
 * @category   PHPExcel
 * @package    PHPExcel
 * @copyright  Copyright (c) 2006 - 2009 PHPExcel (http://www.codeplex.com/PHPExcel)
 * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
 * @version    1.6.5, 2009-01-05
 */

/** Error reporting */
error_reporting(E_ALL);

/** Include path **/
set_include_path(get_include_path() . PATH_SEPARATOR . '../Classes/');

/** PHPExcel */
require_once 'PHPExcel.php';

/** PHPExcel_RichText */
require_once 'PHPExcel/RichText.php';

// Create new PHPExcel object
echo date('H:i:s') . " Create new PHPExcel object\n";
$objPHPExcel = new PHPExcel();

// Set properties
echo date('H:i:s') . " Set properties\n";
$objPHPExcel->getProperties()->setCreator("IBM");
$objPHPExcel->getProperties()->setLastModifiedBy("IBM");
$objPHPExcel->getProperties()->setTitle("Office 2007 XLSX ISP RX Upgrade Document");
$objPHPExcel->getProperties()->setSubject("Office 2007 XLSX ISP RX Upgrade Document");
$objPHPExcel->getProperties()->setDescription("Document for ISP - RX Upgrade Data and Chart.");
$objPHPExcel->getProperties()->setKeywords("office 2007 openxml php");
$objPHPExcel->getProperties()->setCategory("Report result file");

////////////////////////////////////////////////////////////////////////////////////////
// Create a first sheet,
//echo date('H:i:s') . " Add some data\n";
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Completed');
$objPHPExcel->getActiveSheet()->setCellValue('B1', 20);
$objPHPExcel->getActiveSheet()->setCellValue('C1', 'ISP Weekly Projected');

//now add second row
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A2', '');
$objPHPExcel->getActiveSheet()->setCellValue('B2', '');
$objPHPExcel->getActiveSheet()->setCellValue('C2', 55);

$cnt=3;
$query = "SELECT
              completed,
              twenty,
              ispprojected
          FROM reportisp
          ";
     $isp = mysql_query($query);
     while ($row = mysql_fetch_array($isp))
   {
      
           
    
//now add data
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A'.$cnt, $row[completed]);
$objPHPExcel->getActiveSheet()->setCellValue('B'.$cnt, $row[twenty]);
$objPHPExcel->getActiveSheet()->setCellValue('C'.$cnt, $row[ispprojected]);

$cnt++;
}

// Rename sheet
//echo date('H:i:s') . " Rename sheet\n";
$objPHPExcel->getActiveSheet()->setTitle('ISP');

/////////////////////////////////////////////////////////////////////////////////////////
// Create a new worksheet, after the default sheet
//echo date('H:i:s') . " Create new Worksheet object\n";
$objPHPExcel->createSheet();

$objPHPExcel->setActiveSheetIndex(1);
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Completed');
$objPHPExcel->getActiveSheet()->setCellValue('B1', 62);
$objPHPExcel->getActiveSheet()->setCellValue('C1', 'SAPS1 Weekly Projected');

//now add second row
$objPHPExcel->setActiveSheetIndex(1);
$objPHPExcel->getActiveSheet()->setCellValue('A2', '');
$objPHPExcel->getActiveSheet()->setCellValue('B2', '');
$objPHPExcel->getActiveSheet()->setCellValue('C2', 304);

$cnt=3;
$query = "SELECT
              complete,
              sixtytwo,
              sapsweeklyprojected
          FROM reportsap
          ";
     $sap = mysql_query($query);
     while ($row = mysql_fetch_array($sap))
   {
      
           
    
//now add data
$objPHPExcel->setActiveSheetIndex(1);
$objPHPExcel->getActiveSheet()->setCellValue('A'.$cnt, $row[complete]);
$objPHPExcel->getActiveSheet()->setCellValue('B'.$cnt, $row[sixtytwo]);
$objPHPExcel->getActiveSheet()->setCellValue('C'.$cnt, $row[sapsweeklyprojected]);

$cnt++;
}

// Rename sheet
//echo date('H:i:s') . " Rename sheet\n";
$objPHPExcel->getActiveSheet()->setTitle('SAPS1');
////////////////////////////////////////////////////////////////////////////////////////////////

// Create a new worksheet, after the default sheet
//echo date('H:i:s') . " Create new Worksheet object\n";
$objPHPExcel->createSheet();

$objPHPExcel->setActiveSheetIndex(2);
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Completed');
$objPHPExcel->getActiveSheet()->setCellValue('B1', 54);
$objPHPExcel->getActiveSheet()->setCellValue('C1', 'SAPS2 Weekly Projected');

//now add second row
$objPHPExcel->setActiveSheetIndex(2);
$objPHPExcel->getActiveSheet()->setCellValue('A2', '');
$objPHPExcel->getActiveSheet()->setCellValue('B2', '');
$objPHPExcel->getActiveSheet()->setCellValue('C2', 300);

$cnt=3;
$query = "SELECT
              complete,
              sixtytwo,
              sapsweeklyprojected
          FROM reportsap1
          ";
     $sap = mysql_query($query);
     while ($row = mysql_fetch_array($sap))
   {
      
           
    
//now add data
$objPHPExcel->setActiveSheetIndex(2);
$objPHPExcel->getActiveSheet()->setCellValue('A'.$cnt, $row[complete]);
$objPHPExcel->getActiveSheet()->setCellValue('B'.$cnt, $row[sixtytwo]);
$objPHPExcel->getActiveSheet()->setCellValue('C'.$cnt, $row[sapsweeklyprojected]);

$cnt++;
}

// Rename sheet
//echo date('H:i:s') . " Rename sheet\n";
$objPHPExcel->getActiveSheet()->setTitle('SAPS2');
//////////////////////////////////////////////////////////////////////////////////////////////////

// Create a new worksheet, after the default sheet
//echo date('H:i:s') . " Create new Worksheet object\n";
$objPHPExcel->createSheet();

$objPHPExcel->setActiveSheetIndex(3);
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Completed');
$objPHPExcel->getActiveSheet()->setCellValue('B1', 0);
$objPHPExcel->getActiveSheet()->setCellValue('C1', 'RXP Weekly Projected');

//now add second row
$objPHPExcel->setActiveSheetIndex(3);
$objPHPExcel->getActiveSheet()->setCellValue('A2', '');
$objPHPExcel->getActiveSheet()->setCellValue('B2', '');
$objPHPExcel->getActiveSheet()->setCellValue('C2', 464);

$cnt=3;
$query = "SELECT
              complete,
              sixtytwo,
              rxpweeklyprojected
          FROM reportrxp
          ";
     $rxp = mysql_query($query);
     while ($row = mysql_fetch_array($rxp))
   {
      
           
    
//now add data
$objPHPExcel->setActiveSheetIndex(3);
$objPHPExcel->getActiveSheet()->setCellValue('A'.$cnt, $row[complete]);
$objPHPExcel->getActiveSheet()->setCellValue('B'.$cnt, $row[sixtytwo]);
$objPHPExcel->getActiveSheet()->setCellValue('C'.$cnt, $row[rxpweeklyprojected]);

$cnt++;
}

// Rename sheet
//echo date('H:i:s') . " Rename sheet\n";
$objPHPExcel->getActiveSheet()->setTitle('RXP');

//////////////////////////////////////////////////////////////////////////////////////////

// Create a new worksheet, after the default sheet
//echo date('H:i:s') . " Create new Worksheet object\n";
$objPHPExcel->createSheet();

$objPHPExcel->setActiveSheetIndex(4);
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Completed');
$objPHPExcel->getActiveSheet()->setCellValue('B1', 0);
$objPHPExcel->getActiveSheet()->setCellValue('C1', 'Filter Weekly Projected');

//now add second row
$objPHPExcel->setActiveSheetIndex(4);
$objPHPExcel->getActiveSheet()->setCellValue('A2', '');
$objPHPExcel->getActiveSheet()->setCellValue('B2', '');
$objPHPExcel->getActiveSheet()->setCellValue('C2', 1557);

$cnt=3;
$query = "SELECT
              complete,
              sixtytwo,
              filterweeklyprojected
          FROM reportfilter
          limit 800
          ";
     $rxp = mysql_query($query);
     while ($row = mysql_fetch_array($rxp))
   {
      
           
    
//now add data
$objPHPExcel->setActiveSheetIndex(4);
$objPHPExcel->getActiveSheet()->setCellValue('A'.$cnt, $row[complete]);
$objPHPExcel->getActiveSheet()->setCellValue('B'.$cnt, $row[sixtytwo]);
$objPHPExcel->getActiveSheet()->setCellValue('C'.$cnt, $row[filterweeklyprojected]);

$cnt++;
}

// Rename sheet
//echo date('H:i:s') . " Rename sheet\n";
$objPHPExcel->getActiveSheet()->setTitle('Filters');

 

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);

Developer
Jan 16, 2009 at 2:08 AM
These are the symptoms for exhausted memory. Check you memory_limit.

Please look at this related thread:
http://www.codeplex.com/PHPExcel/Thread/View.aspx?ThreadId=36847

Jan 16, 2009 at 2:14 AM
It says Peak memory usage 7MB.  I am guessing they have alocated more than 7mb to me.  Anyway in script that I could find out how much memory they have allocated me?
Developer
Jan 16, 2009 at 2:26 AM
That sounds unlikely for workbooks of this size. Put this at the end of your script (not the beginning if that was what you did):

// Echo memory peak usage
echo "Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB";


Then check also the memory_limit value by looking what it says when you use phpinfo().

If you are close to the limit, raise to appropriate level by putting e.g. this at the beginning of your script:

ini_set('memory_limit', '512M');


Jan 16, 2009 at 2:33 AM
You hit it right on the head.  the 

ini_set('memory_limit', '512M');
<o:p>
Worked perfectly. 

Great job and great support.

Thanks a bunch!</o:p>

Jan 20, 2009 at 3:34 PM
Hi!

I'm also having fun with MySQL and phpexccel. You should consider using ini_set('max_execution_time', '120')  too! 
I made "stress test" which write's values from A0 to Z200 in 50 worksheets.... It takes almost 7 minutes and uses 1,2G memory...

So what i'm trying say is that if your server has some other tasks running it may take long time and max exection time isn't enough anymore.

-Jarkko