ThinkPHP + phpexcel: Example of excel report output function, thinkphpphpexcel

ThinkPHP + phpexcel: Example of excel report output function, thinkphpphpexcel

This example describes how to use thinkPHP + phpexcel to output excel reports. We will share this with you for your reference. The details are as follows:

Preparations:

1. Download The phpexcel1.7.6 class package;

2. decompress the package to the ThinkPHP \ Vendor directory of the TP framework, and change the class package folder name to PHPExcel176. The directory structure is as follows;

Write code (take an order as an example ):

1. create databases and tables;
2. Create a tp project and configure the target database connection;
3. Create a new class file ExportStatisticsAction. class. php under Lib \ Action of the project, and export the file in excel in the index method;
4. Export method steps:
① Query data
② Import the phpexcel class library
③ Create an excel Object and set attributes of the excel Object
④ Set the row and column styles of excel (font, height and width, color, border, and merge)
⑤ Draw the report Header
⑥ Write query data into excel
7. Set the sheet name for excel.
Sheet sets the initial sheet after an excel report is opened
⑨ Sets the output excel header parameters and file names.
⑩ Call the excel creation method to generate an excel file

The Code is as follows:

<? Php/*** Created by lonm. shi. * Date: * Time: * To change this template use File | Settings | File Templates. */class ExportStatisticsAction extends Action {public function index () {$ model = D ("OrdersView"); $ OrdersData = $ model-> select (); // obtain the $ OrdersData two-dimensional array vendor ("PHPExcel176.PHPExcel") from the data query. // Create new PHPExcel object $ objPHPExcel = new PHPExcel (); // Set properties $ objPHPExcel-> getProperties ()-> setCreator ("ctos")-> setLastModifiedBy ("ctos")-> setTitle ("Office 2007 XLSX Test Document ") -> setSubject ("Office 2007 XLSX Test Document")-> setDescription ("Test document for Office 2007 XLSX, generated using PHP classes. ")-> setKeywords (" office 2007 openxml php ")-> setCategory (" Test result file "); // set width $ objPHPExcel-> getActiveSheet () -> getColumnDimension ('A')-> setWidth (8); $ objPHPExcel-> getActiveSheet ()-> getColumnDimension ('B')-> setWidth (10 ); $ objPHPExcel-> getActiveSheet ()-> getColumnDimension ('C')-> setWidth (25); $ objPHPExcel-> getActiveSheet ()-> getColumnDimension ('D ') -> setWidth (12); $ objPHPExcel-> getActiveSheet ()-> getColumnDimension ('E')-> setWidth (50); $ objPHPExcel-> getActiveSheet () -> getColumnDimension ('F')-> setWidth (10); $ objPHPExcel-> getActiveSheet ()-> getColumnDimension ('G')-> setWidth (12 ); $ objPHPExcel-> getActiveSheet ()-> getColumnDimension ('H')-> setWidth (12); $ objPHPExcel-> getActiveSheet ()-> getColumnDimension ('I ') -> setWidth (12); $ objPHPExcel-> getActiveSheet ()-> getColumnDimension ('J')-> setWidth (30 ); // set the Row Height $ objPHPExcel-> getActiveSheet ()-> getRowDimension ('1')-> setRowHeight (22); $ objPHPExcel-> getActiveSheet () -> getRowDimension ('2')-> setRowHeight (20); // set font size bold $ objPHPExcel-> getActiveSheet ()-> getDefaultStyle ()-> getFont () -> setSize (10); $ objPHPExcel-> getActiveSheet ()-> getStyle ('a2: j1')-> getFont ()-> setBold (true ); $ objPHPExcel-> getActiveSheet ()-> getStyle ('a2: j1')-> getAlignment ()-> setVertical (Subject: VERTICAL_CENTER); $ objPHPExcel-> getActiveSheet () -> getStyle ('a2: j1')-> getBorders ()-> getAllBorders ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN ); // set the horizontal center $ objPHPExcel-> getActiveSheet ()-> getStyle ('a1')-> getAlignment ()-> setHorizontal (PHPExcel_Style_Alignment: HORIZONTAL_LEFT ); $ objPHPExcel-> getActiveSheet ()-> getStyle ('A')-> getAlignment ()-> setHorizontal (Region: HORIZONTAL_CENTER); $ objPHPExcel-> getActiveSheet () -> getStyle ('B')-> getAlignment ()-> setHorizontal (PHPExcel_Style_Alignment: HORIZONTAL_CENTER); $ objPHPExcel-> getActiveSheet ()-> getStyle ('d ') -> getAlignment ()-> setHorizontal (PHPExcel_Style_Alignment: HORIZONTAL_CENTER); $ objPHPExcel-> getActiveSheet ()-> getStyle ('F')-> getAlignment () -> setHorizontal (Region: HORIZONTAL_CENTER); $ objPHPExcel-> getActiveSheet ()-> getStyle ('G')-> getAlignment ()-> setHorizontal (Region: HORIZONTAL_CENTER ); $ objPHPExcel-> getActiveSheet ()-> getStyle ('H')-> getAlignment ()-> setHorizontal (Region: HORIZONTAL_CENTER); $ objPHPExcel-> getActiveSheet () -> getStyle ('I)-> getAlignment ()-> setHorizontal (PHPExcel_Style_Alignment: HORIZONTAL_CENTER); // merge cell $ objPHPExcel-> getActiveSheet () -> mergeCells ('a1: j1'); // set table header content $ objPHPExcel-> setActiveSheetIndex (0)-> setCellValue ('a1', 'order data aggregation time: '. date ('Y-m-d H: I: s')-> setCellValue ('a2 ', 'order id')-> setCellValue ('b2 ', 'Submit')-> setCellValue ('c2 ', 'customer name')-> setCellValue ('d2', 'order Time')-> setCellValue ('E2 ', 'Demand model ')-> setCellValue ('F2', 'demand quantity')-> setCellValue ('g2 ', 'demand deadline')-> setCellValue ('h2 ', 'Confirm BOM ')-> setCellValue ('i2', 'pmc confirm delivery schedule ')-> setCellValue ('j2', 'pmc delivery note '); // Miscellaneous glyphs, UTF-8 for ($ I = 0; $ I <count ($ OrdersData)-1; $ I ++) {$ objPHPExcel-> getActiveSheet (0) -> setCellValue ('A '. ($ I + 3), $ OrdersData [$ I] ['id']); $ objPHPExcel-> getActiveSheet (0)-> setCellValue ('B '. ($ I + 3), $ OrdersData [$ I] ['realname']); $ objPHPExcel-> getActiveSheet (0)-> setCellValue ('C '. ($ I + 3), $ OrdersData [$ I] ['customer _ name']); $ objPHPExcel-> getActiveSheet (0)-> setCellValue ('D '. ($ I + 3), toDate ($ OrdersData [$ I] ['create _ time']); // common is called here. php timestamp Conversion Function $ objPHPExcel-> getActiveSheet (0)-> setCellValue ('E '. ($ I + 3), $ OrdersData [$ I] ['require _ product']); $ objPHPExcel-> getActiveSheet (0)-> setCellValue ('F '. ($ I + 3), $ OrdersData [$ I] ['require _ count']); $ objPHPExcel-> getActiveSheet (0)-> setCellValue ('G '. ($ I + 3), $ OrdersData [$ I] ['require _ time']); $ objPHPExcel-> getActiveSheet (0)-> setCellValue ('H '. ($ I + 3), $ OrdersData [$ I] ['product _ bom_encoding ']); $ objPHPExcel-> getActiveSheet (0)-> setCellValue (' I '. ($ I + 3), $ OrdersData [$ I] ['delivery _ time']); $ objPHPExcel-> getActiveSheet (0)-> setCellValue ('J '. ($ I + 3), $ OrdersData [$ I] ['delivery _ memo']); $ objPHPExcel-> getActiveSheet ()-> getStyle ('A '. ($ I + 3 ). ': J '. ($ I + 3)-> getAlignment ()-> setVertical (PHPExcel_Style_Alignment: VERTICAL_CENTER); $ objPHPExcel-> getActiveSheet ()-> getStyle ('A '. ($ I + 3 ). ': J '. ($ I + 3)-> getBorders ()-> getAllBorders ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN); $ objPHPExcel-> getActiveSheet () -> getRowDimension ($ I + 3)-> setRowHeight (16);} // sheet Name $ objPHPExcel-> getActiveSheet ()-> setTitle ('order summary table '); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $ objPHPExcel-> setActiveSheetIndex (0); // header parameter ('content-Type: application/vnd. ms-excel '); header ('content-Disposition: attachment; filename = "Order ((('.date('ymd-his'{.'{.xls"'); // The date is the file name suffix header ('cache-Control: max-age = 0'); $ objWriter = PHPExcel_IOFactory: createWriter ($ objPHPExcel, 'excel5'); // Excel5 is in xls format, excel2007 is in xlsx format $ objWriter-> save ('php: // output ');}}

5. call the export method directly http: // Project/index. php/ExportStatistics/index. The project calls directly _ APP _/ExportStatistics/index. The generated report is saved by downloading. Phpexcel1.7.6 has not found any Encoding Problems and is very fast. Note that the exported method cannot contain any page output or debugging information. Otherwise, the exported excel file will prompt that the format is incorrect. The effect is as follows:

Export report