Detailed explanation of examples of excel report output function implemented by thinkPHP+phpexcel

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 the 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, and the directory structure is as shown below;

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

1. Create database and tables;
2. Create tp project, configure the database connection of the project, these basics are No more talking;
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:
①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 and 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 of creating excel to generate 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 directly __APP__/ExportStatistics/index in the project, and the generated report is saved by downloading. 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 Detailed explanation of examples of excel report output function implemented by thinkPHP+phpexcel. For more information, please follow other related articles on the PHP Chinese website!