Use PHPExcel to import and export excel files

Use PHPExcel to import and export excel format files: zccst due to the use of a large number of export, the following is the export implementation process. Step 1: copy the source code of PHPExcel to the lib file of the project, including PHPExcel. php and the PHPExce folder. use PHPExcel to import and export excel files.
Use PHPExcel to import and export excel files
Author: zccst

The export implementation process is as follows.

Step 1: copy the source code of PHPExcel to the lib of the project.

The files include PHPExcel. php and PHPExcel.
See attachment for source code

Note 1: the source code is in zip format and can be used in windows and linux.
Note 2: PHPExcel.zip is a clean code and can be directly referenced. PHPExcel2.zip has svn records and is not suitable for direct reference.

Step 2: Reference it on the handler page to be exported

1. introduce three files in the header

// Set the environment variable (new PHPExcel) set_include_path ('. '. PATH_SEPARATOR. yii: app ()-> basePath. '/lib/PHPExcel '. PATH_SEPARATOR. get_include_path (); // Note: in yii, you can also directly use Yii: import ("application. lib. PHPExcel. * "); // introduce the PHPExcel file require_once" PHPExcel. php "; require_once 'phpexcel/IOFactory. php'; require_once 'phpexcel/Writer/excel5.php ';

2. put the data in an excel table

// Create $ resultPHPExcel = new PHPExcel (); // set the parameter // set the value $ resultPHPExcel-> getActiveSheet ()-> setCellValue ('A1', 'quarterly '); $ 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 export parameters.
Version 1:

// Set the exported 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, 26 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 2:

$ Writer = PHPExcel_IOFactory: createWriter ($ resultPHPExcel, 'excel5'); $ writer-> setTempDir (Yii: app ()-> basePath. '/runtime'); $ filename = "sing.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($data2 as $item){$fp = dirname(__FILE__) . "/../../../" . $item['file_path'] ;$objPHPExcel = $objReader->load($fp);$objWorksheet = $objPHPExcel->getActiveSheet();$resultPHPExcel->addExternalSheet($objWorksheet);}

Advanced Topic
1. differences between excel5 and excel2007
ZipArchive. php
Many php (php. ini) do not support excel2007, so we recommend that you use excel5.


2. set the location of the temporary file
If a temporary file name error is reported, change the path of the temporary file.
XlsWriter. setDirName (Yii: app ()-> basePath. '/runtime ');


3. set the excel cell format
Filter

4. the output is incorrect.
The default value is $ xlsWriter-> save ("php: // output"). the cache may not be large enough, but the cache is incomplete. Therefore, you can use the following method:

$ 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. Like file (), the difference is that file_get_contents () reads the file into a string.

For other settings, see:
Http://www.zeali.net/entry/556