Simple operation example of exporting Excel file by thinkPHP5 framework

In this paper, the simple operation of exporting Excel file by thinkPHP5 framework is described as an example. Share it for your reference, as follows:

1. Install PHPExcel first composer Installation

composer require phpoffice/phpexcel

2. Control class references

use PHPExcel_IOFactory;
use PHPExcel;

3. Write the export method in the control

/**
 *  Export Late Data 
 */
public function export_later()
{
  $date = explode("-",input("get.date"));
  $_opt['year'] = $date[0];
  $_opt['month'] = $date[1];
  $laterArray = Db::name('user')->select();// Find user Table data 
  $xlsName = $_opt['month']." Statistics of monthly lateness and early departure ";
  $xlsCell = array(
    array('user_name',' Name '),
    array('user_accounts',' Job number '),
    array('later_times',' Number of lateness '),
    array('total_later',' Cumulative lateness (minutes) '),
    array('early_times',' Frequency of leaving early '),
    array('total_early',' Cumulative early leave (minutes) '),
  );// Find out the corresponding field output Excel Corresponding column name 
  // Public method call 
  export_excel($xlsName,$xlsCell,$laterArray);
}
/**
 *  Common data export implementation function 
 * @param $expTitle  Export file name 
 * @param $expCellName  Export file column name 
 * @param $expTableData  Export data 
 */
function export_excel($expTitle,$expCellName,$expTableData)
{
  $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);// File name 
  $fileName = $expTitle . date('_Ymd');//or $xlsTitle  The file name can be set according to your own situation 
  $cellNum = count($expCellName);
  $dataNum = count($expTableData);
  $objPHPExcel = new PHPExcel();// Method 1
  $cellName = array('A','B', 'C','D', 'E', 'F','G','H','I', 'J', 'K','L','M', 'N', 'O', 'P', 'Q','R','S', 'T','U','V', 'W', 'X','Y', 'Z', 'AA',
    'AB', 'AC','AD','AE', 'AF','AG','AH','AI', 'AJ', 'AK', 'AL','AM','AN','AO','AP','AQ','AR', 'AS', 'AT','AU', 'AV','AW', 'AX',
    'AY', 'AZ');
  // Set header export time comments 
  $objPHPExcel->getActiveSheet(0)->mergeCells('A1:' . $cellName[$cellNum - 1] . '1');// Merge cells 
  $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle . '  Export time :' . date('Y-m-d H:i:s'));
  // Set column name 
  for ($i = 0; $i < $cellNum; $i++) {
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i] . '2', $expCellName[$i][1]);
  }
  // Assignment 
  for ($i = 0; $i < $dataNum; $i++) {
    for ($j = 0; $j < $cellNum; $j++) {
      $objPHPExcel->getActiveSheet(0)->setCellValue(
        $cellName[$j] . ($i + 3), $expTableData[$i][$expCellName[$j][0]]
      );
    }
  }
  ob_end_clean();// This 1 Step is critical to clear the buffer to prevent the export excel Garbled code 
  header('pragma:public');
  header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . $xlsTitle . '.xls"');
  header("Content-Disposition:attachment;filename=$fileName.xls");//"xls" Under reference 1 Article remarks 
  $objWriter = \PHPExcel_IOFactory::createWriter(
    $objPHPExcel, 'Excel5'
  );//"Excel2007" Generate 2007 Version of xlsx , "Excel5" Generate 2003 Version of xls
  $objWriter->save('php://output');
}

Readers who are interested in thinkPHP can check the topics of this site: "ThinkPHP Introduction Tutorial", "thinkPHP Template Operation Skills Summary", "ThinkPHP Common Methods Summary", "codeigniter Introduction Tutorial", "CI (CodeIgniter) Framework Advanced Tutorial", "Zend FrameWork Framework Introduction Tutorial" and "PHP Template Technology Summary".

I hope this article is helpful to PHP programming based on ThinkPHP framework.