PHPExcel performance issues, script timeouts.

Topics: Developer Forum, User Forum
May 15, 2008 at 5:14 PM
I am running into a problem with my script timing out when I try running it. Now before I jump too far into it I realize that I am doing a lot of things in this script that are processor intensive, so I don't expect a miracle by any means. I also want to point out that I have used a built-in php function to extend the php script timeout to 10 minutes. However I am getting timeouts now from the web server, which is set to a 5 minute timeout (IIS 5.3, this is for a customer, so no comments about switching to Apache).

Further notes: In the end this will be run by a Windows batch process, not through the web server (and I know, that will change this a bit, bear with me please).

What I am doing is scanning through a series of directories to check for silicon wafer testing files. When I find a folder without an Excel file in it, but with the test files in it I begin the process.
It scans through the test results of these wafer tests and builds image map data to generate an image and legend image of the wafer that is on a color-coded grid. Currently it is generating a png image for the wafer results and a png image for the legend.

I then take these generated images and output a text label to column A, the wafer image to column B, and the legend to column C. Each test file in the folder represents a wafer and contains multiple tests (with an image for each test). Each wafer in the folder gets an individual worksheet in the Excel file. There is one Excel file per folder.

After adding each image to the worksheet I destroy the image object. After saving each file I destroy the PHPExcel object. Both situations using unset().  Is there a more efficient way to clear each xls fille in memory than destroying and recreating the object each time?

One other note, I have to create the files in the old BIFF format, so I am using the ExcelWriter5 class.

What I would like to know is are there any tricks you can suggest for improving the speed and performance of the script? I am considering creating the images as gifs instead of png files. I am also considering using no compression for the png format. Also I am running the latest PHP (5.2.6), which should help things along. I have all the latest libraries installed with it as well. Any ideas, tricks, suggestions, etc. are welcome. Thanks for your time.

Thanks,
Patrick
Coordinator
May 16, 2008 at 8:42 AM
Do you know what proportion of the time is directory scanning, image creation and PHPExcel.

Directory scanning is notoriously slow because of the disk access.
If you're using logic like:
if ($dh opendir($dir)) {
   while ((
$file readdir($dh)) !== false
) {
         ...file processing code in here...

   }
  
closedir($dh
);
}

Try switching to use scandir instead.

if (($filelist scandir($dir)) !== false
) {
   foreach ($filelist as $file
) {
      ...file processing code in here...

   }
}
May 18, 2008 at 4:37 PM
Hello Mark, thanks for the reply. I have been finding that opening and reading  a directory zips along pretty quickly.
Where I seem to be running into a problem is when PHPExcel goes to save the xls file. That is when it drastically slows down. Image creation seems to be nearly instantaneous. Granted the files it is creating range from 8MB to 109MB. So it has a lot of work to do, but sometimes it takes more than 10 minutes for it to save one file.

Here is some sample code:

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

  //Looping code
  $images = make_png($label);

  //Set the height of the second row.
  $objPHPExcel->getActiveSheet()->getRowDimension($row_idx)->setRowHeight(380);
  $objPHPExcel->getActiveSheet()->duplicateStyleArray(array_merge($cellformat), 'A'.$row_idx.':B'.$row_idx);
 
 
  //Add wafer map image
  $objDrawing1 = new PHPExcel_Worksheet_Drawing();
  $objDrawing1->setName('Wafer Map');
  $objDrawing1->setDescription('Wafer Map');
  $objDrawing1->setPath($images['wafer_map']);
  $objDrawing1->setWidthAndHeight(500, 500);
  $objDrawing1->setCoordinates('A'.$row_idx);
 
  $objDrawing1->setWorksheet($objPHPExcel->getActiveSheet());
  unset($objDrawing1);
 
 
  //Add the legend image
  $objDrawing2 = new PHPExcel_Worksheet_Drawing();
  $objDrawing2->setName('Wafer Map Legend');
  $objDrawing2->setDescription('Wafer Map');
  $objDrawing2->setPath($images['bin_table']);
  $objDrawing2->setWidthAndHeight(244, 224);
  $objDrawing2->setCoordinates('B'.$row_idx);
 
  $objDrawing2->setWorksheet($objPHPExcel->getActiveSheet());
  unset($objDrawing2);
 
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
$objWriter->save($dir.$lotdir.'/'.$lotdir.'_MAP.xls');