PHP export Excel data using CSV instead of XLS format

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.