PHPExcel导出300多列 - 占CPU的50％

Question

I am trying to export data from a table which is around 300 columns with around 12100 rows. When I try to export with PHPExcel it literally loads forever and at the same time the process HTTPD.EXE (APACHE) eats up 50% of the CPU and eventually fails after around 30 minutes.

I have researched and put all the improvement tips in place and still no difference.

The query that pulls the data out of the database takes 0.3180 seconds so I know that isn't the issue, the issue is writing to excel.

Here is a snippet of my code

$objPHPExcel = new PHPExcel();



                $sql = "SELECT * from LAPDATATABLE order by file_modified";
                $lrs = CDB::ExecuteQuery($sql);
                $i = 2;
                $objPHPExcel->setActiveSheetIndex(0);

                $exceldata = $objPHPExcel->getActiveSheet();


                $exceldata->setCellValue("A" . 1 , "ITEM" );
                $exceldata->setCellValue("B" . 1 , "attachment" );
                $exceldata->setCellValue("C" . 1 , "ITEM_DETAIL" );
....
....
....



                while ($rows = CDB::GetAssoc($lrs)) 
                {

                    $exceldata->setCellValue("A" . $i , $rows['ITEM']);
                    $exceldata->setCellValue("B" . $i , $rows['attachment']);
                    $exceldata->setCellValue("C" . $i , $rows['ITEM_DETAIL']);
...
...
...

                      $i++;
                 }


                // Rename worksheet
                $objPHPExcel->getActiveSheet()->setTitle('LAP-TAB-DATA');


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


                // Redirect output to a client’s web browser (Excel5)
                header('Content-Type: application/vnd.ms-excel');
                header('Content-Disposition: attachment;filename="Raw-Lap-Data.xlsx"');
                header('Cache-Control: max-age=0');
                // If you're serving to IE 9, then the following may be needed
                header('Cache-Control: max-age=1');

                // If you're serving to IE over SSL, then the following may be needed
                header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
                header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
                header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
                header ('Pragma: public'); // HTTP/1.0

                $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
                $objWriter->save('php://output');
                exit;

Maybe someone can see something wrong in my code or can advise that this is just the way it is with having so many columns to write?

Thanks

Answer 1

It's normal (well... "normal" :P). I've had the same problem with dealing with PHPExcel and huge datasets. After plenty of time spent attempting to optimize as much as possible and gaining seconds here and there it was still not enough.

In the end I decided to just export to a CSV file. I had to lose some features I had expected to have in the generated file (filters, validations) but it was a bazillion times faster and we taught the person that would use it the most how to do it in Excel.