PHP export Excel data using CSV instead of XLS format
There is always a need to export some background data for the operator to view because they all use Excel. So the original choice is to use phpexcel this toolkit to achieve the export data to the XLS format, later found that the data volume is larger than the case (more than 1W), it is slower.
Http://www.zroran.com/it/php/9.html
The previous process of using Phpexcel is as follows
$objPHPExcel = new Phpexcel (), $excelobj = $objPHPExcel->setactivesheetindex (0)->setcellvalue (' A1 ', ' id ') ->setcellvalue (' B1 ', ' column 1 ')->setcellvalue (' C1 ', ' column 2 ')->setcellvalue (' D1 ', ' column 3 ') )->setcellvalue (' E1 ', ' column 4 ');//omit ... $i = 2;foreach ($list as $item) {$excelobj = $objPHPExcel->setac Tivesheetindex (0)->setcellvalueexplicit (' A '. $i, $item [' ID '],phpexcel_cell_datatype::type_string) ->setcellvalueexplicit (' B '. $i, $item [' C1 '],phpexcel_cell_datatype::type_string)->setcellval Ueexplicit (' C '. $i, $item [' C2 '],phpexcel_cell_datatype::type_string)->setcellvalueexplicit (' D '. $i, $item [' C3 '],phpexcel_cell_datatype::type_string)->setcellvalueexplicit (' E '. $i, Date (' y-m-d h:i:s ', $item [' C4 ' ]), phpexcel_cell_datatype::type_string); $i + +;} Set sheet title $objphpexcel->getactivesheet ()->settitle (' Activity record '); $objPHPExcel->setactivesHeetindex (0); header (' Content-type:application/vnd.ms-excel '); header (' Content-disposition:attachment;filename= ') Activity record. xls "'); header (' cache-control:max-age=0 '); header (' cache-control:max-age=1 '); header (' Expires:mon, 1997 Jul 05:00:00 GMT '); Date in the Pastheader (' last-modified: '. Gmdate (' d, D M Y h:i:s '). ' GMT '); Always Modifiedheader (' Cache-control:cache, Must-revalidate '); Http/1.1header (' pragma:public '); Http/1.0$objwriter = Phpexcel_iofactory::createwriter ($objPHPExcel, ' Excel5 '); $objWriter->save (' Php://output '); exit;
The logic is simple: Create a Phpexcel object, and then loop into the data and format it.
At first, the amount of data is not large, generally 1000 data, until this time, the need to export more than 2W of data, I found that the light waiting for the page has been more than 10 seconds, this is quite bad ...
Later, after consideration, decided to use the CSV format, considering the Phpexcel is too heavy, performance may not be good, no time to delve into optimization, or CSV simple, without the help of third-party class library, a few lines of code.
The implementation code is as follows
Header (' Content-type:application/vnd.ms-excel '); header (' content-disposition:attachment;filename= ' activity record. csv "'); Header (' cache-control:max-age=0 '), header (' cache-control:max-age=1 '), header (' Expires:mon, Jul 1997 05:00:00 GMT ') ; Date in the Pastheader (' last-modified: '. Gmdate (' d, D M Y h:i:s '). ' GMT '); Always Modifiedheader (' Cache-control:cache, Must-revalidate '); Http/1.1header (' pragma:public '); Http/1.0ob_flush (); flush ();//create temporary storage memory $fp = fopen (' php://memory ', ' W '); Fputcsv ($fp, array (' ID ', ' column 1 ', ' column 2 ', ' column 3 ', ' column 4 ‘),‘;‘); foreach ($list as $item) { fputcsv ($fp, Array ($item [' id '), $item [' C1 '], $item [' C2 '], $item [' C3 '],date (' y-m-d h:i:s ') , $item [' C4 ']), '; ');} Rewind ($fp); $content = "", while (!feof ($fp)) { $content. = Fread ($fp, 1024);} Fclose ($fp); $content = Iconv (' utf-8 ', ' GBK ', $content);//turn into GBK, otherwise Excel opens garbled echo $content; exit;
After testing, the original need to wait for 10 seconds phpexcel Export, now only 1-2 seconds.
PHP export Excel data using CSV instead of XLS format