Slow Report Generation

Topics: Developer Forum
Apr 29, 2014 at 6:27 PM
I have a report with roughly 1000 rows and 27 columns of data that takes around 45 minutes to generate. I originally thought the slowness could be due to the MySQL query, but I cleaned it up and it's now executing in under a second. I've tried totally disabling formatting for the cells, thinking that could be slowing things down, but the improvement is minimal. Can anyone see anything I am doing wrong? Thanks in advance!
/** PHPExcel */
    include 'PHPExcel.php';
    
    // Create new PHPExcel object
    $objPHPExcel = new PHPExcel();
    
    // Set properties
    $objPHPExcel->getProperties()->setCreator("duhshuh");
    $objPHPExcel->getProperties()->setLastModifiedBy("duhshuh");
    $objPHPExcel->getProperties()->setTitle("XX Report");
    $objPHPExcel->getProperties()->setSubject("XX Report");
    $objPHPExcel->getProperties()->setDescription("XX Report");
    $objPHPExcel->setActiveSheetIndex(0);
    
    $sheet = $objPHPExcel->getActiveSheet();
    $sheet->getColumnDimension('A')->setAutoSize(true);
    $sheet->getColumnDimension('B')->setAutoSize(true);
    $sheet->getColumnDimension('C')->setAutoSize(true);
    $sheet->getColumnDimension('D')->setAutoSize(true);
    $sheet->getColumnDimension('E')->setAutoSize(true);
    $sheet->getColumnDimension('F')->setAutoSize(true);
    $sheet->getColumnDimension('G')->setAutoSize(true);
    $sheet->getColumnDimension('H')->setAutoSize(true);
    $sheet->getColumnDimension('I')->setAutoSize(true);
    $sheet->getColumnDimension('J')->setAutoSize(true);
    $sheet->getColumnDimension('K')->setAutoSize(true);
    $sheet->getColumnDimension('L')->setAutoSize(true);
    $sheet->getColumnDimension('M')->setAutoSize(true);
    $sheet->getColumnDimension('N')->setAutoSize(true);
    $sheet->getColumnDimension('O')->setAutoSize(true);
    $sheet->getColumnDimension('P')->setAutoSize(true);
    $sheet->getColumnDimension('Q')->setAutoSize(true);
    $sheet->getColumnDimension('R')->setAutoSize(true);
    $sheet->getColumnDimension('S')->setAutoSize(true);
    $sheet->getColumnDimension('T')->setAutoSize(true);
    $sheet->getColumnDimension('U')->setAutoSize(true);
    $sheet->getColumnDimension('V')->setAutoSize(true);

// Formatting
            $sheet->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(1, 1);
            $sheet->freezePane('A2');

            $letter = 'A';
            $sheet->SetCellValue($letter.'1', 'Nomenclature'); $letter++;
            $sheet->SetCellValue($letter.'1', 'P/N'); $letter++;
            $sheet->SetCellValue($letter.'1', 'NSN'); $letter++;
            $sheet->SetCellValue($letter.'1', 'CAGE'); $letter++;
            $sheet->SetCellValue($letter.'1', 'Model'); $letter++;
            //$sheet->SetCellValue($letter.'1', 'Manufacturer'); $letter++;
            $sheet->SetCellValue($letter.'1', 'System'); $letter++;
            $sheet->SetCellValue($letter.'1', 'UOI'); $letter++;
            $sheet->SetCellValue($letter.'1', 'Vendor'); $letter++;
            $sheet->SetCellValue($letter.'1', 'Criticality'); $letter++;
            $sheet->SetCellValue($letter.'1', 'Current Inv.'); $letter++;
            $sheet->SetCellValue($letter.'1', 'Current Conus Inv.'); $letter++;
            $sheet->SetCellValue($letter.'1', 'Current KNB Inv.'); $letter++;
            $sheet->SetCellValue($letter.'1', 'Re-order Point'); $letter++;
            $sheet->SetCellValue($letter.'1', 'Stock Level'); $letter++;
            $sheet->SetCellValue($letter.'1', 'Lead Time'); $letter++;
            $sheet->SetCellValue($letter.'1', 'Weighted Avg.\nAnnual Demand'); 
             $sheet->getStyle($letter.'1')->getAlignment()->setWrapText(true);   $letter++;
            $sheet->SetCellValue($letter.'1', 'Weighted Value'); $letter++;
            $sheet->SetCellValue($letter.'1', 'Avg. Cost'); $letter++;
            $sheet->SetCellValue($letter.'1', 'Quantity to Order'); $letter++;
            $sheet->SetCellValue($letter.'1', 'Ext. Order Cost'); $letter++;
            for($y=0; $y<=max($years_purch,$years_inv); $y++)
            {
              if($y<$years_purch)
              {
                $sheet->SetCellValue($letter.'1', "Demand Met Thru\nPurchases FY".$FYpurch[$y]);
                $sheet->getStyle($letter.'1')->getAlignment()->setWrapText(true); 
                $letter++; 
              }
              if($y<$years_inv)
              {
                $sheet->SetCellValue($letter.'1', "Inventory Demand\nFY".$FYinv[$y]); 
                $sheet->getStyle($letter.'1')->getAlignment()->setWrapText(true); 
                $letter++; 
              }  
            }                                                        
            $sheet->SetCellValue($letter.'1', 'Deactivate'); 

            $i++;
          }
               
          // Values and Formatting
          $letter = 'A';
          // A
          $sheet->SetCellValue($letter.$i, trim($row['part_name'])); 
          //  $sheet->getStyle($letter.$i)->getNumberFormat()->setFormatCode('@'); 
          $letter++;
          // B
          $sheet->getCell($letter.$i)->setValueExplicit(trim($row['part_number']), PHPExcel_Cell_DataType::TYPE_STRING); // to avoid scientific notation for long strings
          //  $sheet->getStyle($letter.$i)->getNumberFormat()->setFormatCode('@'); 
          $letter++;
          // C
          $sheet->SetCellValue($letter.$i, trim($row['part_nsn'])); 
           // $sheet->getStyle($letter.$i)->getNumberFormat()->setFormatCode('@'); 
           $letter++;
          // D
          $sheet->SetCellValue($letter.$i, trim($row['part_cage_code'])); 
           // $sheet->getStyle($letter.$i)->getNumberFormat()->setFormatCode('@'); 
           $letter++;
          // E
          $sheet->SetCellValue($letter.$i, trim($row['part_model']));
           // $sheet->getStyle($letter.$i)->getNumberFormat()->setFormatCode('@'); 
           $letter++;
          // F
          $sheet->SetCellValue($letter.$i, trim($row['system']));
           // $sheet->getStyle($letter.$i)->getNumberFormat()->setFormatCode('@'); 
           $letter++;
          // G
          $sheet->SetCellValue($letter.$i, trim($row['code']));
            //$sheet->getStyle($letter.$i)->getNumberFormat()->setFormatCode('@'); 
            $letter++;    
          // H
          $sheet->SetCellValue($letter.$i, trim($row['vendor_name']));
           // $sheet->getStyle($letter.$i)->getNumberFormat()->setFormatCode('@'); 
           $letter++;
          // I
          $sheet->SetCellValue($letter.$i, trim($readiness_impact));
           // $sheet->getStyle($letter.$i)->getNumberFormat()->setFormatCode('@'); 
           $letter++;    
          // J
          $sheet->SetCellValue($letter.$i, trim($row['part_oh_qty'])); 
           // $sheet->getStyle($letter.$i)->getNumberFormat()->setFormatCode('0'); 
           $letter++;
          // K
          $sheet->SetCellValue($letter.$i, '=J'.$i.'-L'.$i); 
            //$sheet->getStyle($letter.$i)->getNumberFormat()->setFormatCode('0'); 
            $letter++;
          // L
          $sheet->SetCellValue($letter.$i, trim($row['part_oh_qty_knb'])); 
            //$sheet->getStyle($letter.$i)->getNumberFormat()->setFormatCode('0'); 
            $letter++;
          // M  -- Reorder Point
          $sheet->SetCellValue($letter.$i, '=IF(Q'.$i.'<62.5,0,IF(AND(Q'.$i.'>=62.5,Q'.$i.'<134.5),1,IF(AND(Q'.$i.'>=134.5,Q'.$i.'<363.6),2,IF(AND(Q'.$i.'>=363.6,Q'.$i.'<505.7),3,IF(AND(Q'.$i.'>=505.7,Q'.$i.'<745.2),4,IF(AND(Q'.$i.'>=745.2,Q'.$i.'<1180.4),5,6))))))');
            //$sheet->getStyle($letter.$i)->getNumberFormat()->setFormatCode('0'); 
            $letter++;
          // N
          $sheet->SetCellValue($letter.$i, '=IF(Q'.$i.'<14.5,0,IF(AND(Q'.$i.'>=14.5,Q'.$i.'<34.5),1,IF(AND(Q'.$i.'>=34.5,Q'.$i.'<73.6),2,IF(AND(Q'.$i.'>=73.6,Q'.$i.'<116.5),3,IF(AND(Q'.$i.'>=116.5,Q'.$i.'<291.2),4,IF(AND(Q'.$i.'>=291.2,Q'.$i.'<505.7),5,IF(AND(Q'.$i.'>=505.7,Q'.$i.'<673.2),6,IF(AND(Q'.$i.'>=673.2,Q'.$i.'<745.2),7,IF(AND(Q'.$i.'>=745.2,Q'.$i.'<1180.4),8,10))))))))))');
            //$sheet->getStyle($letter.$i)->getNumberFormat()->setFormatCode('0'); 
            $letter++;
          // O
          $sheet->SetCellValue($letter.$i, trim($part_lead_time));
           // $sheet->getStyle($letter.$i)->getNumberFormat()->setFormatCode('0'); 
           $letter++;
          // P
          $sheet->SetCellValue($letter.$i, trim($row['part_weighted_annual_demand']));
            //$sheet->getStyle($letter.$i)->getNumberFormat()->setFormatCode('0');
             $letter++;
          // Q
         $sheet->SetCellValue($letter.$i, '=SUM((IF(I'.$i.'="HIGH",15*0.4,IF(I'.$i.'="MEDIUM",8*0.4,IF(I'.$i.'="LOW",0.4,0))))+(IF(O'.$i.'<=5,1*0.4,IF(AND(O'.$i.'>5,O'.$i.'<=9),2*0.4,IF(AND(O'.$i.'>9,O'.$i.'<=15),5*0.4,IF(AND(O'.$i.'>15,O'.$i.'<=29),10*0.4,IF(AND(O'.$i.'>29,O'.$i.'<=59),20*0.4,25*0.4))))))+(IF(R'.$i.'<=1000,5*0.2,IF(AND(R'.$i.'>1000,R'.$i.'<=3000),4*0.2,IF(AND(R'.$i.'>3000,R'.$i.'<=5000),3*0.2,IF(AND(R'.$i.'>5000,R'.$i.'<=20000),2*0.2,1*0.2)))))+(P'.$i.'*2))))');     
            //$sheet->getStyle($letter.$i)->getNumberFormat()->setFormatCode('0'); 
            $letter++;
          // R
          $sheet->SetCellValue($letter.$i, trim($row['part_avg_cost'])); 
           // $sheet->getStyle($letter.$i)->getNumberFormat()->setFormatCode('$#,##0.00'); 
           $letter++;
          // S
          $sheet->SetCellValue($letter.$i, '=IF(J'.$i.'<=M'.$i.',N'.$i.'-J'.$i.',0)');  
            //$sheet->getStyle($letter.$i)->getNumberFormat()->setFormatCode('0'); 
            $letter++;
          // T
          $sheet->SetCellValue($letter.$i, '=R'.$i.'*S'.$i);
            //$sheet->getStyle($letter.$i)->getNumberFormat()->setFormatCode('$#,##0.00');
             $letter++;
continued below
Apr 29, 2014 at 6:28 PM
          for($y=0; $y<=max($years_purch,$years_inv); $y++)
          {
            if($y<$years_purch)
            {
              $sheet->SetCellValue($letter.$i, trim($row['FY'.$FYpurch[$y].'purchdemand']));
              //$sheet->getStyle($letter.$i)->getNumberFormat()->setFormatCode('0');  
              $letter++; 
            }
            if($y<$years_inv)
            {
              $sheet->SetCellValue($letter.$i, trim($row['FY'.$FYinv[$y].'invdemand']));
             // $sheet->getStyle($letter.$i)->getNumberFormat()->setFormatCode('0'); 
              $letter++; 
            }   
          } 
          $sheet->SetCellValue($letter.$i, trim($row['part_obsolete_next12mo']));
         //   $sheet->getStyle($letter.$i)->getNumberFormat()->setFormatCode('@');  
          $i++; 
          
          if($result['num_rows']==($i))
          {
            $sheet->getStyle('A1:'.$letter.'1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('000066FF');
            $sheet->getStyle('A1:'.$letter.'1')->getFont()->setBold(true);
            $sheet->getStyle('A1:'.$letter.'1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);  
          } 
May 13, 2014 at 6:47 PM
Any ideas?