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, 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 the PHPExcelphpexcel official website at: 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. The development background shows that the ThinkPHP framework is used for this development and all content is exported 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: copy the code // import the relevant file require_once $ _ SERVER ['document _ root']. '/PHPExcel/Classes/PHPExcel. php '; // instantiate $ phpexcel = new PHPExcel (); // set the title to $ phpexcel-> getActiveSheet ()-> setTitle ('technical support for yichuang tech hs '); // 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 phone ') -> setCellValue ('p1', 'area used ')-> setCellValue ('q1', 'number of practitioners ')-> setCellValue ('r1', 'change information ') -> setCellValue ('s1 ', 'certification information')-> setCellValue ('T1', 'regulatory authorization'); // obtain the data to be exported from the database $ li St = $ 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 _ r I '])-> 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 headerheader (" Content-T Ype: 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 (); // preview the exported file by executing the copy code: for more information about the settings of relevant properties, see copy the Code (from network materials) // set the basic attributes of the document $ objProps = $ phpexcel-> getProperties (); $ objProps-> setCreator ("Zhang Gong District Medical Insurance Bureau"); $ objProps-> setLastModifiedBy ("Zhang Gong District Medical Insurance Bureau "); $ objProps-> setTitle ("Zhang Gong District Medical Insurance Bureau monthly employee increase and decrease Change Report"); $ objProps-> setSubject ("Zhang Gong District Medical Insurance Bureau monthly employee increase and decrease Change Report "); $ objProps-> setDescription ("Zhang Gong District Medical Insurance Bureau monthly employee increase and decrease Change Report"); $ objProps-> setKeywords ("Zhang Gong District Medical Insurance Bureau monthly employee increase and decrease Change Report"); $ objPro Ps-> setCategory ("Change Report"); copy the code PHPExcel to merge/detach cells $ 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); PHPExce L how to set style copy code $ objStyleA1 = $ phpexcel-> getActiveSheet ()-> getStyle ('a1'); $ objStyleA1-> getAlignment ()-> setHorizontal (PHPExcel_Style_Alignment :: HORIZONTAL_CENTER); // set the level to its $ objStyleA1-> getAlignment ()-> setVertical (PHPExcel_Style_Alignment: VERTICAL_CENTER ); // set vertical to its $ objFontA1 = $ objStyleA1-> getFont (); $ objFontA1-> setName (' '); $ objFontA1-> setSize (18 ); $ objFontA1-> setBold (true); copy code PHPExcel how to set the border copy code $ o BjActSheet-> 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 (PHPExc El_Style_Border: BORDER_THIN); $ objPHPExcel-> getActiveSheet ()-> getStyle ('d13')-> getBorders ()-> getLeft ()-> getColor () -> setARGB ('ff993300 '); // set the border color to copy the Code. For more information, please refer to Baidu. If you have any questions, please correct them!