yii2.0 excel导出实例

以导出学生信息表为例
1.首先，下载相应的PHPExcel插件，放到项目合适的目录下，如common\extensions
2.创建StudentController
class StudentController extends \yii\web\Controller{
    
    public function actionExportStudentMessage(){
        StudentService::service()->exportStudentMessage();
    }

}

3.创建StudentExcelService,
class StudentExcelService extends \Common\services\BaseServices{

    public $filename;           //文件名称

    public function __construct($filename = '')
    {
        parent::__construct();
        $this->filename = $filename;
        require_once(xxxxx/phpexcel/PHPExcel.php');
        require_once(xxxxx/phpexcel/PHPExcel/IOFactory.php');
    }

    //生成Excel表格数据    
    public function exportListToFile($objPHPExcel)
    {
        header('Content-Type: application/vnd.ms-excel;charset=utf-8');
        header('Content-Disposition: attachment;filename=' . urlencode($this->filename . ".xls") . '');
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;//一定要紧跟exit 要不然会有列数限制或说文件损坏之类
    }

}

4.创建对应的StudentService
class StudentService extends StudentExcelService{

    public funciton exportStudentMessage()
    {
       $this->filename = "学生信息表".date("Y-m-d");     

        $first_row_array = array(
                "id"=>"编号",
                "name"=>"姓名",
                "sex"=>"性别",
                "age"=>"年龄"
            );

        #获取导出数据
        $list = $this->getStudentInfo();
        list($objPHPExcel, $excel_row_char, $line_num) = $this->setFirstLine($first_row_array, $title = '学生信息');

        #设置表格数据内容
        $this->setStudentExport($list, $excel_row_char, $objPHPExcel, $line_num);
        #导出excel文件
        $this->exportListToFile($objPHPExcel);

    }

    

    public function setFirstLine($first_row_array, $title)
    {
        $row_char = 'A';
        $first_line_num = '2';
        $objPHPExcel = new \PHPExcel();
        $objPHPExcel
            ->getProperties()
            ->setCreator($title . "导出")
            ->setLastModifiedBy($title . "导出")
            ->setTitle($title . "导出")
            ->setSubject($title . "导出")
            ->setDescription($title . "导出")
            ->setKeywords("excel")
            ->setCategory("result file");

    #设置样式，宽度，居中

    $dao = $objPHPExcel->setActiveSheetIndex(0);

    $excel_row_char = array();

    foreach ($first_row_array as $key => $value) {

        $excel_row_char[$key] = $row_char;

        $dao->setCellValue($row_char . $first_line_num, $value);

        $row_char++;

    }

    return array($objPHPExcel, $excel_row_char, $first_line_num);

}

public function setStudentExport($data, $excel_row_char, \PHPExcel $objPHPExcel, $line_num)

{

    $styleArray1 = array(

        'alignment' => array(

            'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER,

        ),

    );

    $dao = $objPHPExcel->setActiveSheetIndex(0);

    $dao->getDefaultColumnDimension()->setWidth(15);

    $dao->getColumnDimension('B')->setWidth(30);

    $dao->getColumnDimension('C')->setWidth(60);

    $dao->getRowDimension('1')->setRowHeight(22);

    $dao->getRowDimension('2')->setRowHeight(20);

    $dao->getStyle('A1')->getFont()->setBold(true);

    $dao->getStyle('A1')->getFont()->setSize(16);

    $dao->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

    $dao->getStyle('A2:D2')->getFont()->setBold(true);

    $dao->getStyle('A2:D2')->getFont()->setSize(10);

    $dao->getStyle('A2:D2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

    $dao->getStyle('A2:D2')->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);

    $dao->mergeCells('A1:D1');

    $dao->setCellValue('A1', '学生信息表');

    $dao->freezePane('A3');

    $objPHPExcel->getActiveSheet()->getStyle('A:D')->applyFromArray($styleArray1);

    foreach ($data as $key => $list) {

        $line_num += 1;

        $dao->setCellValueExplicit($excel_row_char['id'] . $line_num, $list['id'])

            ->setCellValue($excel_row_char['name'] . $line_num, $list['name'])

            ->setCellValue($excel_row_char['age'] . $line_num, $list['age'])

            ->setCellValue($excel_row_char['sex'] . $line_num, $list['sex']);

        unset($data[$key]);

    }

}

}