How can I improve my PHPexcel File?

Topics: Developer Forum, Project Management Forum, User Forum
Feb 14, 2013 at 9:52 AM
Edited Feb 14, 2013 at 9:52 AM
Hi PHPExcel forum!!!

I have been working with PHPExcel now for quite some time and absolutely loving it, constantly finding way to improve my work i need help/suggestions on how I can improve the 'bulky' sector of my code, please see code sector:

$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('');
$objDrawing->setDescription('');
$objDrawing->setPath('../../media/catalog/product/cache/1/thumbnail/75x75/9df78eab33525d08d6e5fb8d27136e95'.$_product->getThumbnail());
$objDrawing->setCoordinates('A'.$sum_total);
$objDrawing->setOffsetX(10);
$objDrawing->setOffsetY(10);
$objDrawing->setHeight(110);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

$objPHPExcel->getActiveSheet()->setCellValue('A7', 'IMAGE');
$objPHPExcel->getActiveSheet()->setCellValue('B7', 'SKU');
$objPHPExcel->getActiveSheet()->setCellValue('C7', 'BRAND');
$objPHPExcel->getActiveSheet()->setCellValue('D7', 'GENDER');
$objPHPExcel->getActiveSheet()->setCellValue('E7', 'NAME');
$objPHPExcel->getActiveSheet()->setCellValue('F7', 'PRICE PER UNIT');
$objPHPExcel->getActiveSheet()->setCellValue('G7', 'RRP');
$objPHPExcel->getActiveSheet()->setCellValue('J7', 'TOTAL UNITS');
$objPHPExcel->getActiveSheet()->setCellValue('B'.$sum_total, $_product->getSku());
$objPHPExcel->getActiveSheet()->setCellValue('E'.$sum_total, $_product->getName());
$objPHPExcel->getActiveSheet()->setCellValue('C'.$sum_total, $_product->getAttributeText('manufacturer'));
$objPHPExcel->getActiveSheet()->setCellValue('D'.$sum_total, $_product->getAttributeText('gender'));
$objPHPExcel->getActiveSheet()->setCellValue('F'.$sum_total, '£'.number_format($_product->getPrice(),2));
$objPHPExcel->getActiveSheet()->setCellValue('G'.$sum_total, '£'.number_format($_product->getMsrp(),2));
$objPHPExcel->getActiveSheet()->setCellValue('I'.$sum_total, 'CLICK HERE TO BUY');
$objPHPExcel->getActiveSheet()->getCell('I'.$sum_total)->getHyperlink()->setUrl('$_product->getUrlPath());
$objPHPExcel->getActiveSheet()->getColumnDimension("A")->setWidth(23);




Here are the results:
(These two lines are externally related)
10:48:03 Collecting Product Data...
10:48:36 Data Collected...

Applying date is when the show code above starts to process
10:48:36 Applying Data to Document...

When the process finishes (5 seconds to save):
10:49:32 Writing to file
Create Writer OK
PreCalculate OK
Saving as OK
10:49:37Your file has been created

So Im looking at the roughly reducing the 1 minute processing time for the code above to be shortened if possible, at the moments its only 400+ rows in the spreadsheet baring in mind majorite is images.

I have cached it (Gzip).
If you need more info let me know and Il be happy to asist!
Coordinator
Feb 14, 2013 at 11:01 AM
Edited Feb 14, 2013 at 11:02 AM
Take advantage of PHPExcel's fluent interface:
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('')
    ->setDescription('')
    ->setPath('../../media/catalog/product/cache/1/thumbnail/75x75/9df78eab33525d08d6e5fb8d27136e95'.$_product->getThumbnail())
    ->setCoordinates('A'.$sum_total)
    ->setOffsetX(10)
    ->setOffsetY(10)
    ->setHeight(110)
    ->setWorksheet($objPHPExcel->getActiveSheet());
or
$objPHPExcel->getActiveSheet()->setCellValue('A7', 'IMAGE')
    ->setCellValue('B7', 'SKU');
    ->getActiveSheet()->setCellValue('C7', 'BRAND')
    ->getActiveSheet()->setCellValue('D7', 'GENDER')
    ->getActiveSheet()->setCellValue('E7', 'NAME')
    ->getActiveSheet()->setCellValue('F7', 'PRICE PER UNIT')
    ->getActiveSheet()->setCellValue('G7', 'RRP')
    ->getActiveSheet()->setCellValue('J7', 'TOTAL UNITS');
Avoid unnecessary duplication of nested method calls
$sheet = $objPHPExcel->getActiveSheet();
$sheet->setCellValue('A7', 'IMAGE');
$sheet->setCellValue('B7', 'SKU');
$sheet->setCellValue('C7', 'BRAND');
$sheet->setCellValue('D7', 'GENDER');
$sheet->setCellValue('E7', 'NAME');
$sheet->setCellValue('F7', 'PRICE PER UNIT');
$sheet->setCellValue('G7', 'RRP');
$sheet->setCellValue('J7', 'TOTAL UNITS');
Look at using the worksheet's fromArray() method
$data = array(
     'SKU', 'BRAND', 'GENDER', 'NAME', 'PRICE PER UNIT', 'RRP', 'TOTAL UNITS'
);
$objPHPExcel->getActiveSheet()->fromArray($data, NULL, 'A7');
Combine these techniques as appropriate
Feb 14, 2013 at 11:29 AM
Hi Mark,

First of all thanks for showing me a more simple way instead of copy and pasting and me just simply changing cell values!!

What is the difference between

$objPHPExcel->getActiveSheet()->setCellValue('A7', 'IMAGE')
->setCellValue('B7', 'SKU');

&

$objPHPExcel->getActiveSheet()->setCellValue('A7', 'IMAGE')
$objPHPExcel->getActiveSheet()->->setCellValue('B7', 'SKU');

In the way that it is processed?

Im going to look at array-ing everything which will hopefully increase processing!
Also ive started to apply the auto filter, and I have looked through the docs but cant find the information to select ascending or descending? is this functionality in yet?

Kind Regards
Adam
Coordinator
Feb 14, 2013 at 11:43 AM
Edited Feb 14, 2013 at 11:43 AM
The difference between
$objPHPExcel->getActiveSheet()->setCellValue('A7', 'IMAGE') 
$objPHPExcel->getActiveSheet()->setCellValue('B7', 'SKU'); 
&
$objPHPExcel->getActiveSheet()->setCellValue('A7', 'IMAGE') 
    ->setCellValue('B7', 'SKU'); 
is the elimination of a second call to
$objPHPExcel->getActiveSheet()
as setCellValue() returns the worksheet object, allowing you to chain the calls
Also ive started to apply the auto filter, and I have looked through the docs but cant find the information to select ascending or descending? is this functionality in yet?
No, and nor is it likely to be! I looked at implementing it when I was working on autofilter expressions; but sorting in an autofilter range is a massive overhead; and actually changes the content of each row to replicate the sort sequence, making it awkward for script writers who expect a degree of consistency in the data they store.... if I put "Hello" in cell A1 and "World" in cell A2, and have an autofilter sort descending; then cell A1 will subsequently contain "World" and cell A2 will contain "Hello".