Use PHPExcel to import and export excel files

Source: Internet
Author: User
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 source code of the PHPExcel folder. see the appendix for the source code. Note 1: the source code is in zip format and can be used in windows and linux. Note 2: 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

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.