Use PHPExcel to export data to an excel file in xls format _ PHP Tutorial

Use PHPExcel to export data to excel files in xls format. Use PHPExcel to export data to excel files in xls format. when developing a business license management system in a certain place, export the data to an excel file, A year ago, ER from a group used PHPExcel to export data to excel files in xls format.
When developing a business license management system in a certain place, you need to export the data to an excel file. although a group's ERP system was used to import and export data once a year ago, however, at that time, I was too busy to write my blog, and I forgot about it a year later. so I wrote this application to my blog today as a reference for future development, in this case, you do not need to export the required information when using it again, but also provide a reference for other colleagues. Author: DragonDean, blog address: http://www.cnblogs.com/dragondean/

What is PHPExcel?

PHPExcel is a PHP class library used to operate Office Excel documents. it is based on Microsoft's OpenXML standard and PHP language. You can use it to read and write workbooks in different formats, such as Excel (BIFF ). xls, Excel 2007 (OfficeOpenXML ). xlsx, CSV, Libre/OpenOffice Calc. ods, Gnumeric, PDF, HTML, etc.

Download PHPExcel

The official phpexcel website address is http://phpexcel.codeplex.com/. you can download your file from. I downloaded version 1.8. Decompress the downloaded package to the root directory of the website.

Development Background

This development uses the ThinkPHP framework to export all the content according to the query conditions. In addition, I will write an article about the import function. For more details, see the comments in the code section.

The exported code is as follows:

// Import related files

Require_once $ _ SERVER ['document _ root']. '/PHPExcel/Classes/PHPExcel. php ';

// Instantiate

$ Phpexcel = new PHPExcel ();

// Set the ratio title

$ Phpexcel-> getActiveSheet ()-> setTitle ('yichuang tech tips technical support ');

// Set the header

$ Phpexcel-> getActiveSheet ()-> setCellValue ('A1', 'Meal certificate word ')

-> SetCellValue ('b1 ', 'unit name ')

-> SetCellValue ('C1 ', 'legal representative ')

-> SetCellValue ('d1 ', 'city ')

-> SetCellValue ('e1 ', 'region ')

-> SetCellValue ('F1', 'address ')

-> SetCellValue ('G1', 'category ')

-> SetCellValue ('h1 ', 'Remarks (business scope )')

-> SetCellValue ('i1', 'Issuing authorization ')

-> SetCellValue ('j1', 'Start date ')

-> SetCellValue ('k1 ', 'end date ')

-> SetCellValue ('l1', 'food safety manager ')

-> SetCellValue ('M1 ', 'Do you want to authenticate ')

-> SetCellValue ('n1 ', 'issue date ')

-> SetCellValue ('o1 ', 'Contact number ')

-> SetCellValue ('p1', 'area used ')

-> SetCellValue ('q1', 'number of practitioners ')

-> SetCellValue ('R1', 'change information ')

-> SetCellValue ('S1', 'certification information ')

-> SetCellValue ('T1', 'Department of supervision ');

// Obtain the data to be exported from the database

$ List = $ db-> where ($ where)-> select ();

// Use foreach to write data from the second row, because the first row is the header

$ I = 2;

Foreach ($ list as $ val ){

$ Phpexcel-> getActiveSheet ()-> setCellValue ('A '. $ I, 'GI '. $ val ['czz _ nian ']. $ val ['czz _ hao'])

-> SetCellValue ('B'. $ I, $ val ['danwei'])

-> SetCellValue ('C'. $ I, $ val ['faren'])

-> SetCellValue ('D'. $ I, $ val ['dz _ chengshi '])

-> SetCellValue ('E'. $ I, $ val ['dz _ diqu'])

-> SetCellValue ('F'. $ I, $ val ['dizhi'])

-> SetCellValue ('g'. $ I, $ val ['leibie'])

-> SetCellValue ('H'. $ I, $ val ['beizhu'])

-> SetCellValue ('I'. $ I, $ val ['fazheng'])

-> SetCellValue ('J '. $ I, $ val ['QS _ nian ']. '-'. $ val ['QS _ yue ']. '-'. $ val ['QS _ Ri'])

-> SetCellValue ('k '. $ I, $ val ['zz _ nian ']. '-'. $ val ['zz _ yue ']. '-'. $ val ['zz _ Ri'])

-> SetCellValue ('l'. $ I, $ val ['anquanc'])

-> SetCellValue ('M'. $ I, $ val ['zhizheng'])

-> SetCellValue ('N '. $ I, $ val ['fz _ nian ']. '-'. $ val ['fz _ yue ']. '-'. $ val ['fz _ Ri'])

-> SetCellValue ('o'. $ I, $ val ['dianhua'])

-> SetCellValue ('P'. $ I, $ val ['shiyongmianji '])

-> SetCellValue ('q'. $ I, $ val ['renshu'])

-> SetCellValue ('R'. $ I, $ val ['biangeng'])

-> SetCellValue ('s '. $ I, $ val ['chizheng'])

-> SetCellValue ('t'. $ I, $ val ['keshi']);

$ I ++;

}

$ Obj_Writer = PHPExcel_IOFactory: createWriter ($ phpexcel, 'excel5 ');

$ Filename = 'port'. date ('Y-m-D'). ". xls"; // file name

// Set the header

Header ("Content-Type: application/force-download ");

Header ("Content-Type: application/octet-stream ");

Header ("Content-Type: application/download ");

Header ('content-Disposition: inline; filename = "'. $ filename .'"');

Header ("Content-Transfer-Encoding: binary ");

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 ");

$ Obj_Writer-> save ('php: // output'); // output

Die (); // planting execution

Preview the exported file:

For details about setting other related properties, refer to network reference)

// Set the basic attributes of the document

$ ObjProps = $ phpexcel-> getProperties ();

$ ObjProps-> setCreator ("Zhanggong District Medical Insurance Bureau ");

$ ObjProps-> setLastModifiedBy ("Zhanggong District Medical Insurance Bureau ");

$ ObjProps-> setTitle ("monthly employee increase/decrease change report of Zhanggong District Medical Insurance Bureau ");

$ ObjProps-> setSubject ("Zhang Gong District Medical Insurance Bureau monthly employee increase/decrease Change Report ");

$ ObjProps-> setDescription ("monthly employee increase/decrease change report of Zhanggong District Medical Insurance Bureau ");

$ ObjProps-> setKeywords ("Zhang Gong District Medical Insurance Bureau monthly employee increase/decrease Change Report ");

$ ObjProps-> setCategory ("Change Report ");

How to merge/separate cells in PHPExcel

$ ObjPHPExcel-> getActiveSheet ()-> mergeCells ('a18: e22 ');

$ ObjPHPExcel-> getActiveSheet ()-> unmergeCells ('a18: e22 ');

How to set column width/row height in PHPExcel

$ ObjPHPExcel-> getActiveSheet ()-> getColumnDimension ('B')-> setAutoSize (true );

$ ObjPHPExcel-> getActiveSheet ()-> getColumnDimension ('D')-> setWidth (12 );

$ ObjPHPExcel-> getActiveSheet ()-> getRowDimension (3)-> setRowHeight (16 );

How to set styles in PHPExcel

$ ObjStyleA1 = $ phpexcel-> getActiveSheet ()-> getStyle ('A1 ');

$ ObjStyleA1-> getAlignment ()-> setHorizontal (PHPExcel_Style_Alignment: HORIZONTAL_CENTER); // set horizontal

$ ObjStyleA1-> getAlignment ()-> setVertical (PHPExcel_Style_Alignment: VERTICAL_CENTER); // you can set VERTICAL_CENTER

$ ObjFontA1 = $ objStyleA1-> getFont ();

$ ObjFontA1-> setName (' ');

$ ObjFontA1-> setSize (18); $ objFontA1-> setBold (true );

How to set borders in PHPExcel

$ ObjActSheet-> getStyle ('A2 ')-> getBorders ()-> getTop ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN );

$ ObjActSheet-> getStyle ('A2 ')-> getBorders ()-> getLeft ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN );

$ ObjActSheet-> getStyle ('A2 ')-> getBorders ()-> getRight ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN );

$ ObjActSheet-> getStyle ('A2 ')-> getBorders ()-> getBottom ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN );

$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('d13')-> getBorders ()-> getLeft ()-> getColor ()-> setARGB ('ff993300 '); // set the border color

For more information, please contact Baidu. if you have any questions, please correct them!

When developing a business license management system in a certain place, the supervisor needs to export data to an excel file, although he was a group's ER...