PHP example-thinkPHP+phpexcel implements excel report output function example

This article mainly introduces the excel report output function of thinkPHP+phpexcel, and analyzes the related operation skills of thinkPHP integrating PHPExcel for Excel files based on specific examples. Friends in need can refer to the following

The examples of this article are described Use thinkPHP+phpexcel to realize the excel report output function. Share it with everyone for your reference, the details are as follows:

Preparation work:

1. Download phpexcel1.7.6 class Package;

2. Unzip it to the ThinkPHP\Vendor directory of the TP framework, change the class package folder name to PHPExcel176, Directory structure is as shown below;

Write code (take an ordersummarized data as an example):

1. Create databaseAnd table;
2. Create the tp project and configure the project's database connection. I won't go into these basics;
3. Create a new class file ExportStatisticsAction.class under the project's Lib\Action. php, and then implement excel export in the index method;
4. Steps of the export method:
①Query data
②Import phpexcel class library
③Create an excel object and set the properties of the excel object
④Set the row and column styles of excel (font, height, width, color, border, merge, etc.)
⑤Draw the report header
⑥Write the query data to excel
⑦Set the name of the excel sheet
⑧Set the initial sheet after opening the excel report
⑨Set the header parameters and file name of the output excel
⑩Call the method to create excel to generate the excel file

The code is as follows ：

<?php
/**
 * Created by lonm.shi.
 * Date: 2012-02-09
 * Time: 下午4:54
 * To change this template use File | Settings | File Templates.
 */
class ExportStatisticsAction extends Action {
  public function index(){
    $model= D("OrdersView");
    $OrdersData= $model->select(); //查询数据得到$OrdersData二维数组
    vendor("PHPExcel176.PHPExcel");
    // 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(&#39;A&#39;)->setWidth(8);
    $objPHPExcel->getActiveSheet()->getColumnDimension(&#39;B&#39;)->setWidth(10);
    $objPHPExcel->getActiveSheet()->getColumnDimension(&#39;C&#39;)->setWidth(25);
    $objPHPExcel->getActiveSheet()->getColumnDimension(&#39;D&#39;)->setWidth(12);
    $objPHPExcel->getActiveSheet()->getColumnDimension(&#39;E&#39;)->setWidth(50);
    $objPHPExcel->getActiveSheet()->getColumnDimension(&#39;F&#39;)->setWidth(10);
    $objPHPExcel->getActiveSheet()->getColumnDimension(&#39;G&#39;)->setWidth(12);
    $objPHPExcel->getActiveSheet()->getColumnDimension(&#39;H&#39;)->setWidth(12);
    $objPHPExcel->getActiveSheet()->getColumnDimension(&#39;I&#39;)->setWidth(12);
    $objPHPExcel->getActiveSheet()->getColumnDimension(&#39;J&#39;)->setWidth(30);
    //设置行高度
    $objPHPExcel->getActiveSheet()->getRowDimension(&#39;1&#39;)->setRowHeight(22);
    $objPHPExcel->getActiveSheet()->getRowDimension(&#39;2&#39;)->setRowHeight(20);
    //set font size bold
    $objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(10);
    $objPHPExcel->getActiveSheet()->getStyle(&#39;A2:J2&#39;)->getFont()->setBold(true);
    $objPHPExcel->getActiveSheet()->getStyle(&#39;A2:J2&#39;)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
    $objPHPExcel->getActiveSheet()->getStyle(&#39;A2:J2&#39;)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
    //设置水平居中
    $objPHPExcel->getActiveSheet()->getStyle(&#39;A1&#39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
    $objPHPExcel->getActiveSheet()->getStyle(&#39;A&#39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getActiveSheet()->getStyle(&#39;B&#39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getActiveSheet()->getStyle(&#39;D&#39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getActiveSheet()->getStyle(&#39;F&#39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getActiveSheet()->getStyle(&#39;G&#39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getActiveSheet()->getStyle(&#39;H&#39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getActiveSheet()->getStyle(&#39;I&#39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    //合并cell
    $objPHPExcel->getActiveSheet()->mergeCells(&#39;A1:J1&#39;);
    // set table header content
    $objPHPExcel->setActiveSheetIndex(0)
      ->setCellValue(&#39;A1&#39;, &#39;订单数据汇总 时间:&#39;.date(&#39;Y-m-d H:i:s&#39;))
      ->setCellValue(&#39;A2&#39;, &#39;订单ID&#39;)
      ->setCellValue(&#39;B2&#39;, &#39;下单人&#39;)
      ->setCellValue(&#39;C2&#39;, &#39;客户名称&#39;)
      ->setCellValue(&#39;D2&#39;, &#39;下单时间&#39;)
      ->setCellValue(&#39;E2&#39;, &#39;需求机型&#39;)
      ->setCellValue(&#39;F2&#39;, &#39;需求数量&#39;)
      ->setCellValue(&#39;G2&#39;, &#39;需求交期&#39;)
      ->setCellValue(&#39;H2&#39;, &#39;确认BOM料号&#39;)
      ->setCellValue(&#39;I2&#39;, &#39;PMC确认交期&#39;)
      ->setCellValue(&#39;J2&#39;, &#39;PMC交货备注&#39;);
    // Miscellaneous glyphs, UTF-8
    for($i=0;$i<count($OrdersData)-1;$i++){
      $objPHPExcel->getActiveSheet(0)->setCellValue(&#39;A&#39;.($i+3), $OrdersData[$i][&#39;id&#39;]);
      $objPHPExcel->getActiveSheet(0)->setCellValue(&#39;B&#39;.($i+3), $OrdersData[$i][&#39;realname&#39;]);
      $objPHPExcel->getActiveSheet(0)->setCellValue(&#39;C&#39;.($i+3), $OrdersData[$i][&#39;customer_name&#39;]);
      $objPHPExcel->getActiveSheet(0)->setCellValue(&#39;D&#39;.($i+3), toDate($OrdersData[$i][&#39;create_time&#39;])); //这里调用了common.php的时间戳转换函数
      $objPHPExcel->getActiveSheet(0)->setCellValue(&#39;E&#39;.($i+3), $OrdersData[$i][&#39;require_product&#39;]);
      $objPHPExcel->getActiveSheet(0)->setCellValue(&#39;F&#39;.($i+3), $OrdersData[$i][&#39;require_count&#39;]);
      $objPHPExcel->getActiveSheet(0)->setCellValue(&#39;G&#39;.($i+3), $OrdersData[$i][&#39;require_time&#39;]);
      $objPHPExcel->getActiveSheet(0)->setCellValue(&#39;H&#39;.($i+3), $OrdersData[$i][&#39;product_bom_encoding&#39;]);
      $objPHPExcel->getActiveSheet(0)->setCellValue(&#39;I&#39;.($i+3), $OrdersData[$i][&#39;delivery_time&#39;]);
      $objPHPExcel->getActiveSheet(0)->setCellValue(&#39;J&#39;.($i+3), $OrdersData[$i][&#39;delivery_memo&#39;]);
      $objPHPExcel->getActiveSheet()->getStyle(&#39;A&#39;.($i+3).&#39;:J&#39;.($i+3))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
      $objPHPExcel->getActiveSheet()->getStyle(&#39;A&#39;.($i+3).&#39;:J&#39;.($i+3))->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
      $objPHPExcel->getActiveSheet()->getRowDimension($i+3)->setRowHeight(16);
    }
    // sheet命名
    $objPHPExcel->getActiveSheet()->setTitle(&#39;订单汇总表&#39;);
    // Set active sheet index to the first sheet, so Excel opens this as the first sheet
    $objPHPExcel->setActiveSheetIndex(0);
    // excel头参数
    header(&#39;Content-Type: application/vnd.ms-excel&#39;);
    header(&#39;Content-Disposition: attachment;filename="订单汇总表(&#39;.date(&#39;Ymd-His&#39;).&#39;).xls"&#39;); //日期为文件名后缀
    header(&#39;Cache-Control: max-age=0&#39;);
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, &#39;Excel5&#39;); //excel5为xls格式，excel2007为xlsx格式
    $objWriter->save(&#39;php://output&#39;);
  }
}

5. Call the export method directly http://project/index.php/ExportStatistics/index, call APP/ExportStatistics/index directly in the project, and the generated report is the download method to save. No coding problems were found in phpexcel1.7.6, and the speed is very fast. Please note that there cannot be any page output information or debugging information in the export method, otherwise the exported excel will prompt that the format is incorrect. The effect is as follows:

Export report

The above is the detailed content of PHP example-thinkPHP+phpexcel implements excel report output function example. For more information, please follow other related articles on the PHP Chinese website!