Thinkphp+phpexcel implementing an Excel Report output feature example

This article mainly introduces Thinkphp+phpexcel implementation of Excel report output function, combined with specific instance form analysis thinkphp Integration Phpexcel Implementation of Excel file related operation skills, need friends can refer to the next

The example in this article describes the Thinkphp+phpexcel implementation of Excel report output function. Share to everyone for your reference, as follows:

Preparatory work:

1. Download the phpexcel1.7.6 class package;

2. Extract to the TP frame Thinkphp\vendor directory, the package folder named PHPExcel176, directory structure such as;

Write code (take an order summary data as an example):

1. Create databases and tables;
2. Create a TP project, configure the database connection of the project, these basic will not say;
3. Create a new class file ExportStatisticsAction.class.php under the lib\action of the project, and then implement Excel export in the index method;
4. Steps to export the method:
① Querying data
② Importing Phpexcel class libraries
③ creating an Excel object and setting properties for an Excel object
④ set the Excel row and column styles (font, aspect, color, border, merge, and so on)
⑤ Drawing Report Headers
⑥ writing query data to Excel
⑦ setting the name of the sheet for Excel
⑧ Setting the Excel report open after the initial sheet
⑨ setting the output of Excel header parameters and file names
⑩ calling the Create Excel method to generate an Excel file

The code is as follows:

<?php/** * Created by Lonm.shi. * date:2012-02-09 * Time: PM 4:54 * To change this template with File | Settings | File Templates.    */class Exportstatisticsaction extends Action {public Function index () {$model = D ("Ordersview"); $OrdersData = $model->select ();    Query data get $ordersdata two-dimensional array vendor ("Phpexcel176.phpexcel");    Create New Phpexcel Object $objPHPExcel = new Phpexcel (); Set Properties $objPHPExcel->getproperties ()->setcreator ("CTOs")->setlastmodifiedby ("CTOs")-&gt ; Settitle ("Office of XLSX Test Document")->setsubject ("Office" xlsx Test Document ")->setdescriptio      N ("Test document for Office" XLSX, generated using PHP classes. ")    ->setkeywords ("Office 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:j2')->getfont ()->setbold (true); $objPHPExcel->getactivesheet ()->getstyle (' A2:j2 ')->getalignment ()->setvertical (phpexcel_style_    Alignment::vertical_center); $objPHPExcel->getactivesheet ()->getstyle (' A2:j2 ')->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 (phpexcel_style_    Alignment::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 (Phpexcel_style_aligNment::horizontal_center); $objPHPExcel->getactivesheet ()->getstyle (' G ')->getalignment ()->sethorizontal (phpexcel_style_    Alignment::horizontal_center); $objPHPExcel->getactivesheet ()->getstyle (' H ')->getalignment ()->sethorizontal (phpexcel_style_    Alignment::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 Summary time: '. Date (' Y-m      -D h:i:s ')->setcellvalue (' A2 ', ' Order ID ')->setcellvalue (' B2 ', ' next person ')->setcellvalue (' C2 ', ' Customer name ') ->setcellvalue (' D2 ', ' Order Time ')->setcellvalue (' E2 ', ' demand Model ')->setcellvalue (' F2 ', ' demand quantity ')->s Etcellvalue (' G2 ', ' demand Delivery ')->setcellvalue (' H2 ', ' Confirm BOM material number ')->setcellvalue (' I2 ', ' PMC confirmation delivery ')Setcellvalue (' J2 ', ' PMC delivery notes '); Miscellaneous glyphs, UTF-8 for ($i =0; $i <count ($OrdersData)-1; $i + +) {$objPHPExcel->getactivesheet (0)->s Etcellvalue (' 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 ']); This invokes the timestamp conversion function of the common.php $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->getactivesh(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 named $objPHPExcel->getactivesheet ()->settitle (' Order Summary table '); Set Active sheet Index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setactivesheetind    EX (0);    Excel Header Parameter header (' Content-type:application/vnd.ms-excel '); Header (' Content-disposition:attachment;filename= ' Order Summary table ('. Date (' ymd-his ') '). XLS "'); Date is the filename suffix header (' cache-control:max-age=0 '); $objWriter = Phpexcel_iofactory::createwriter ($objPHPExcel, ' Excel5 ');  Excel5 for XLS format, excel2007 for xlsx format $objWriter->save (' php://output '); }}

5. Call the Export method directly to http://Project/index.php/exportstatistics/index, the project calls the direct __app__/exportstatistics/index, the generated report is downloaded as a way to save. phpexcel1.7.6 did not find any coding problems, the speed is also very fast, note that the exported method cannot have any page output information or debugging information, otherwise the exported Excel will prompt the format is not correct. The effect is as follows:

Export a report