Weird error while opening generated xlsx file

Oct 29, 2009 at 12:53 PM

Hi Guys,

I'm experiencing really weird error when opening generated file...

I'm fetching data from MySQL DB and inserting them into the worksheet. For given rows I change their background color.

To illustrate it better please look at the code below:

<?php
session_start();
include('db.php');

$date = date("Y-m-d");
header('Content-Type: application/vnd.openXMLformats-officedocument.spreadsheetml.sheet');
header("Content-Disposition: attachment; filename=\"" . $date . ".xlsx\"");
header('Cache-Control: max-age=0');

/** Include path **/
ini_set('include_path', ini_get('include_path').';../Classes/');
include 'PHPExcel.php';
include 'PHPExcel/Writer/Excel2007.php';
include 'PHPExcel/Writer/Excel5.php';
include 'PHPExcel/IOFactory.php';

$excel = new PHPExcel();
$excel->getProperties()->setTitle($date);

// Set column names first
$headers = array ("Id", "Comment");

for ($i = 0; $i < count($headers); $i++) {
    $excel->getActiveSheet()->setCellValueByColumnAndRow($i, 1, $headers[$i]);
    $excel->getActiveSheet()->getStyleByColumnAndRow($i, 1)->getFont()->setBold(true);
    $excel->getActiveSheet()->getStyleByColumnAndRow($i, 1)->getFont()->setSize(11);
    $excel->getActiveSheet()->getStyleByColumnAndRow($i, 1)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);
}

// Set rows
$query = "SELECT * FROM cases";
$result = mysql_query($query) or die(mysql_error());
$row = 2;
while ($case = mysql_fetch_array($result)) {
    for ($col = 0; $col < count($case); $col++) {
        $excel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $case[$col]);
        if ($case[result] == "Won") {
            $excel->getActiveSheet()->getStyleByColumnAndRow($col, $row)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
            $excel->getActiveSheet()->getStyleByColumnAndRow($col, $row)->getFill()->getStartColor()->setARGB(PHPExcel_Style_Color::COLOR_GREEN);
        }
        if ($case[result] == "Spec not met") {
            $excel->getActiveSheet()->getStyleByColumnAndRow($col, $row)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
            $excel->getActiveSheet()->getStyleByColumnAndRow($col, $row)->getFill()->getStartColor()->setARGB(PHPExcel_Style_Color::COLOR_YELLOW);
        }
        if ($case[result] == "Canceled") {
            $excel->getActiveSheet()->getStyleByColumnAndRow($col, $row)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
            $excel->getActiveSheet()->getStyleByColumnAndRow($col, $row)->getFill()->getStartColor()->setARGB('FFFF6600');
        }
        /*if ($case[result] == "Lost") {
            $excel->getActiveSheet()->getStyleByColumnAndRow($col, $row)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
            $excel->getActiveSheet()->getStyleByColumnAndRow($col, $row)->getFill()->getStartColor()->setARGB('FFFF6600');
        }*/
    }
    $row++;
}

// Set up autofilter
$excel->getActiveSheet()->setAutoFilter('A1:' . $excel->getActiveSheet()->getHighestColumn().$excel->getActiveSheet()->getHighestRow());

// Set up autowidth of cells
$highestColumn = $excel->getActiveSheet()->getHighestColumn(); //e.g., 'G'
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); //e.g., 6
for($column = 0; $column < $highestColumnIndex; $column++)
    $excel->getActiveSheet()->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($column))->setAutoSize(true);

// Prepare the file
$excel = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
$excel->save('php://output');
?>

Everything is OK if I leave the part

/*if ($case[result] == "Lost") {
            $excel->getActiveSheet()->getStyleByColumnAndRow($col, $row)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
            $excel->getActiveSheet()->getStyleByColumnAndRow($col, $row)->getFill()->getStartColor()->setARGB('FFFF6600');
}*/

commented. If I uncomment that part, then I get the error when opening excel file saying something about "invalid file format or file extension".

I'm trying to fix this for the whole day now but no luck...

Maybe somebody here has an idea?

 


Developer
Oct 30, 2009 at 5:43 PM

Can you upload the defect file here so we can inspect? You may upload the file here:

http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=10749

Nov 2, 2009 at 8:45 AM
Edited Nov 2, 2009 at 8:48 AM

Here it is:

Defected excel file

Developer
Nov 2, 2009 at 11:21 AM

If you try to open the file in a text editor, you'll see the problem:

 

Fatal error:  Maximum execution time of 30 seconds exceeded in /home/asus-polska/ftp/tender/PHPExcel/Style/Borders.php on line 536

Solution is to set max_execution_time to something higher

ini_set('max_execution_time', 120);

Or you can try reduce execution time. I don't know how many rows you have, but it is faster to style many cells at a time. You may even try to use column styles which is even faster. E.g.

 

$excel->getActiveSheet()->getStyle('A')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$excel->getActiveSheet()->getStyle('A')->getFill()->getStartColor()->setARGB('FFFF6600');

 

Nov 2, 2009 at 11:26 AM

Thanks koyama.

I would like to color all rows at once (outside the loop). Is that possible? For example: "Color all rows to yellow where value of cell B = "xxx"". Can you tell me how to do it please?

Developer
Nov 2, 2009 at 12:07 PM
matino wrote:

Thanks koyama.

I would like to color all rows at once (outside the loop). Is that possible? For example: "Color all rows to yellow where value of cell B = "xxx"". Can you tell me how to do it please?

Ugg... I had not noticed that you were coloring cells based on value. In that case your code is fine.

The real solution would be to use a conditional style for the whole worksheet, but right now conditional styles are not working optimally when you have "many" cells so I wouldn't recommend that method at the moment.

 

Nov 3, 2009 at 10:01 AM
ini_set('max_execution_time', 120) in .htaccess file worked just fine, thank you for this solution :)