This article mainly describes how to use PHPExcel to export Excel files in thinkPHP. It is a summary of many other people's posts after the blogger has practiced, which can be used for personal testing. thinkPHP version is 3.2.0.

My blog: Mine-MuYan Welcome to harassment. ThinkPHP Exports Excel Files with PHPExcle.

Dead work

Download and configure PHPExcel extensions

1. go PHPExcel's official website perhaps github Go download the latest PHPExcel and put it in ThinkPHP/Library/Vendor directory. Vendor directory is the default folder for placing PHP extension packages. Create PHPExcel folder under Vendor directory and extract the downloaded files to this place.

2. Rename the PHPExcel.class.php file to PHPExcel.class.php.

So far, the plug-in installation has been completed, the directory structure is as follows, followed by the functional part.

Creating Excel Method

Method of Generating Excel Files

/**
     * Data export to. xls format
     * @param string $fileName Exported File Name
     * @param $expCellName     array -> Database fields and field annotations
     * @param $expTableData    Model -> Connected database
     */
    public function exportExcel($fileName='table',$expCellName,$expTableData){
        $xlsTitle = iconv('utf-8', 'gb2312', $fileName);//File name
        $xlsName = $fileName.date("_Y.m.d_H.i.s"); //or $xlsTitle file name can be set according to your situation
        $cellNum = count($expCellName);
        $dataNum = count($expTableData);

        import("Vendor.PHPExcel.PHPExcel");
        import("Vendor.PHPExcel.Writer.Excel5");
        import("Vendor.PHPExcel.IOFactory.php");

        $objPHPExcel = new \PHPExcel();
        $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');

        $objPHPExcel->getActiveSheet(0)->mergeCells('A1:'.$cellName[$cellNum-1].'1');//merge cell
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $fileName.'  Export time:'.date('Y-m-d H:i:s'));
        for($i=0;$i<$cellNum;$i++){
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'2', $expCellName[$i][1]);
        }
        // Miscellaneous glyphs, UTF-8
        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]]);
            }
        }

        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');
        header("Content-Disposition:attachment;filename=$xlsName.xls");//attachment new window print inline this window print
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }

Method Generation Description

1. Line 9

$xlsName = $fileName.date("_Y.m.d_H.i.s"); //or $xlsTitle file name can be set according to your situation

It i s to set the file name, which is the file name _Y.m.d_H.i.s, _Y.m.d_H.i.s., which is the time of export, because under windows, the file name can not contain:, so even if written as _Y.m.d_H:i:s, the exported file is also in the style of _Y.m.d_H.i.s.

1. Lines 13 to 15

   import("Vendor.PHPExcel.PHPExcel");
   import("Vendor.PHPExcel.Writer.Excel5");
   import("Vendor.PHPExcel.IOFactory.php");

These three lines are the introduction of PHPExcel, because the file directory structure at that time was ThinkPHP/Library/Vendor/PHPExcel, so import("Vendor.PHPExcel.PHPExcel"); pay attention to the directory structure, as long as the location of the introduction is the same as the directory where PHPExcel is placed;

1. Lines 20 and 21

   $objPHPExcel->getActiveSheet(0)->mergeCells('A1:'.$cellName[$cellNum-1].'1');//merge cell
   $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $fileName.'  Export time:'.date('Y-m-d H:i:s'));

This function is to merge one row of cells in the first line of an Excel file to be used as the title or introduction of the table. As shown in the following figure:

You can set the output file format, which is written here as. xls and can be modified to. xlsx.

1. Three parameters need to be passed in when this method is invoked (as already mentioned in the comment at the beginning of the method). Here's how to specify:

    File name of file exported by $fileName
    expCellName database fields and field annotations (arrays) 
    ExpTable Data Connected Database

Method calls

Call method

 /**Export**/
    public function msgOut(){
        $excel = A('Excel');
        $xlsCell = array(
            array('id', 'ID'),
            array('staff_name', 'Nickname?'),
            array('staff_real', 'Real name'),
            array('mobile', 'Cell-phone number'),
            array('card_id', 'ID number'),
            array('referee', 'Recommender'),
            array('game_id', 'Game ID'),
            array('money', 'balance'),
            array('consume_coin', 'Consumption currency'),
            array('create_time', 'Registration time'),
            array('status', 'state'),
        );
        $field = null;
        foreach ($xlsCell as $key => $value) {
            if($key == 0){
                $field = $value[0];
            }else{
                $field .= "," . $value[0];
            }
        }
        $xlsModel = M('Staff');
        if (IS_POST) {
            $map = $this -> _queryTime();
            $staff_name = I('staff_name');
            if(is_numeric($staff_name)){
                $map["id|staff_name"] =   array(intval($staff_name),array('like','%'.$staff_name.'%'),'_multi'=>true);
            }else{
                $map['staff_name']    =   array('like', '%'.(string)$staff_name.'%');
            }
            $end_time = $map['create_time'];
            if(empty($end_time) && empty($staff_name)){
                $xlsName = 'Staff Full Table Export';
                $xlsData = $xlsModel->Field($field)->order('id DESC')->select();
            }elseif(empty($end_time) && $staff_name){
                $xlsName = 'Staff Table Commissioner's Search Results Export';
                $where['id|staff_name'] = array(intval($staff_name), array('like', '%' . $staff_name . '%'), '_multi' => true);
                $xlsData = $xlsModel->Field($field)->where($where)->order('id DESC')->select();
            }else {
                $xlsName = 'Staff Table search result export';
                $xlsData = $xlsModel->Field($field)->where($map)->order('id DESC')->select();
            }
        }
        foreach ($xlsData as $k => $v) {
            $xlsData[$k]['create_time'] = $v['create_time'] == null ? '-' : date("Y-m-d H:i",$v['create_time']);
            $xlsData[$k]['status'] = $v['status'] == 1 ? 'normal' : 'Prohibit';
        }
        $excel->exportExcel($xlsName,$xlsCell,$xlsData);
    }

Call description

1. Line 3
   $excel = A('Excel');

This method I wrote in Application/Admin/Controller/Excel Controller.class.php, the current controller is MainController.class.php, calling methods in other controllers, using the A() that comes with TP, so line 51 uses $excel-> exportExcel ($xlsName, $xlsCell, $xlsData); if your exportExcel() method and the method currently invoked are in the same controller In that case, the third line can not be written, and the fifty-first line can be changed to $this - > exportExcel ($xlsName, $xlsCell, $xlsData);.

1. Line 4
   xlsCell is an export of all fields in this table, as well as field annotations. After export, you can't use fields as column names of the table. Users who don't know what database fields mean don't know why they read this table.

2. Lines 17-24
   The field name is extracted from the $xlsCell array as a string for use in queries. Of course, you can also write out the string of the field name by hand. I use foreach here.

3. Lines 26-46
   If you want to export the whole table without query conditions, you can use $xlsData = xlsModel - > Field ($field) - > order ('id DESC') - > select (); if you want to export the whole table, you can use $xlsData = xlsModel - > Field ($field) - > order ('id DESC') - > select ().

4. Lines 47-50
   To process the result of $xlsData, I write two meanings:

     1. When the `create_time` field is empty, the value is `-', and when it is valuable, the `date()` function is used to process the timestamp into a date;
     2. When the value of the `status'field is 1, it is displayed as `normal', and `disabled' when the value is 0.
   

A little wordy, forgive me.

template file

Template files are simple, just one submission.

<form action="{:U('Main/msgOut')}" method="post" class="form-horizontal">
    <div class="search-form fr cf">
        <div class="sleft">
            <input type="text" class="search-input" onClick="WdatePicker()" name="start_time" style="cursor: pointer;" value="{:I('start_time')}" id="start" placeholder="Start date" />
            <input type="text" class="search-input" onClick="WdatePicker()" name="end_time" style="cursor: pointer;" value="{:I('end_time')}" id="end" placeholder="End date" />
            <input type="text" name="staff_name" class="search-input" value="{:I('staff_name')}" id="name" placeholder="Enter the nickname of the Promotion Commissioner or ID">
            <button type="submit" class="btn" id="out">export</button>
        </div>
    </div>
</form>

The three input s in the form form are search criteria. If not, just write the button.

At the end of the article, you are welcome to reprint it.