Import and export Excel format files using Phpexcel

Export an Excel format file using Phpexcel Import
Export an Excel format file using Phpexcel Import
Author: zccst

Because the export is used more, the following is the export implementation process.

The first step is to copy the source code of Phpexcel to the project Lib.

Files include: phpexcel.php and Folder Phpexcel
source code See attachment

Note 1: Source code is in ZIP format and can be used in Windows and Linux.
Note 2:phpexcel.zip is a clean code that can be referenced directly. Phpexcel2.zip has SVN records and is not suitable for direct referencing.

Step two: Reference in the handler page that needs to be exported

1, introduction of three files in the head

Set environment variables (new Phpexcel) Set_include_path ('. '). Path_separator. Yii::app ()->basepath. ' /lib/phpexcel '. Path_separator. Get_include_path ());//Note: In Yii, you can also directly yii::import ("application.lib.phpexcel.*");//introduce phpexcel related files require_once " phpexcel.php "; require_once ' phpexcel/iofactory.php '; require_once ' phpexcel/writer/excel5.php ';

2. Put the data in an Excel table

Create new $resultphpexcel= new Phpexcel ();//Set parameter//setting value $resultphpexcel->getactivesheet ()->setcellvalue (' A1 ', ' Quarter '); $ Resultphpexcel->getactivesheet ()->setcellvalue (' B1 ', ' name '); $resultPHPExcel->getactivesheet () Setcellvalue (' C1 ', ' quantity '); $i = 2;foreach ($data as $item) {$resultPHPExcel->getactivesheet ()->setcellvalue (' A '. $ I, $item [' Quarter ']); $resultPHPExcel->getactivesheet ()->setcellvalue (' B '. $i, $item [' name ']); $ Resultphpexcel->getactivesheet ()->setcellvalue (' C '. $i, $item [' number ']); $i + +;}

3. Set some parameters for export
Version One:

Set Export file name $outputfilename = ' Total.xls '; $xlsWriter = new Phpexcel_writer_excel5 ($resultPHPExcel);//ob_start ();  Ob_flush (); Header ("Content-type:application/force-download"); Header ("Content-type:application/octet-stream"); Header ("Content-type:application/download"); header (' Content-disposition:inline;filename= '. $outputFileName. ' '); Header ("Content-transfer-encoding:binary"), Header ("Expires:mon, Jul 1997 05:00:00 GMT"), Header ("Last-modified:". Gmdate ("D, D M Y h:i:s"). "GMT"), Header ("Cache-control:must-revalidate, Post-check=0, pre-check=0"), Header ("Pragma:no-cache"); $xlsWriter- >save ("Php://output");

Version two:

$writer = Phpexcel_iofactory::createwriter ($resultPHPExcel, ' Excel5 '); $writer->settempdir (Yii::app () BasePath. ' /runtime '); $filename = "single. xls"; Header ("Pragma:public"); Header ("Cache-control:must-revalidate, post-check=0, Pre-check=0 "); Header (" Content-type:application/force-download "); header (' Content-type:application/vnd.ms-excel ') ); header (' Content-disposition:attachment;filename= '. $filename. "'"); header (' cache-control:max-age=0 '); $writer->save (' php://output ');

4. Add attachments (not required)

$objPHPExcel = new Phpexcel (); $objReader = Phpexcel_iofactory::createreader (' Excel5 '); $objReader Setreaddataonly (True); foreach ($data 2 as $item) {$fp = DirName (__file__). "/.. /.. /.. /" . $item [' File_path ']; $objPHPExcel = $objReader->load ($fp); $objWorksheet = $objPHPExcel->getactivesheet (); $ Resultphpexcel->addexternalsheet ($objWorksheet);}

Advanced Topics
The difference between 1,excel5 and excel2007
ziparchive.php
There are many PHP (php.ini) that do not support excel2007, so it is recommended to use EXCEL5.


2. Set temporary file location
Change the temporary file path if you report a temporary filename error
Xlswriter.setdirname (Yii::app ()->basepath. ' /runtime ');


3. Formatting Excel cells
Filter

4, the output is wrong.
Default $xlswriter->save ("Php://output"), perhaps because the cache is not large enough, and the display is not complete, so do a relay, the way is as follows:

$finalFileName = (Yii::app ()->basepath. ' /runtime/'. Time (). XLS '; $xlsWriter->save ($finalFileName); Echo file_get_contents ($finalFileName)  ; The file_get_contents () function reads the entire file into a string. As with file (), the difference is that file_get_contents () reads a file into a string.

Other settings are available for reference:
http://www.zeali.net/entry/556

