PHP Yii2框架 Excel导出功能

PHP Yii2框架 Excel导出功能

第一步 框架中安装插件

composer require phpoffice/phpexcel "*" -v

第二步 直接使用

下面是已经封装

/**
     *  @DESC 数据导入
     *  @example
     *  $data = [[1, "小明", "20"],[2, "小王", "21"]];
     *  $header = ["id", "姓名", "年龄"];
     *  Myhelpers::exportEXCEL($data, $header);
     *  @return void, Browser direct output
     */
    static function exportEXCEL($data, $header, $title = "simple", $filename = "data"){
        if (!is_array($data) || !is_array($header)) return false;
        $objPHPExcel = new \PHPExcel();
        // Set properties
        $objPHPExcel->getProperties()->setCreator("Maarten Balliauw");
        $objPHPExcel->getProperties()->setLastModifiedBy("Maarten Balliauw");
        $objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Test Document");
        $objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document");
        $objPHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");
        
        //设置表头，也就是第一行数据
        foreach ($header as $k => $v){
            $column = \PHPExcel_Cell::stringFromColumnIndex($k);
            $objPHPExcel->setActiveSheetIndex(0) ->setCellValue($column."1", $v);
        }
        
        //设置行数据，从第二行开始
        foreach ($data as $key=>$item){
            foreach ($item as $key2=>$val){
                $column = \PHPExcel_Cell::stringFromColumnIndex($key2);  //获得列位置
                // 添加一行数据，A1、B1、C1.....N1的各个数据
                //设置为$key+2，因为key是从0开始，而我们的行数据第一个索引是“1”，而上面因为设置了表头，占了一行数据，所以就直接+2
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue($column.($key+2), $val);
            }
        }
        
        // Rename sheet
        $objPHPExcel->getActiveSheet()->setTitle($title);
        
        // Redirect output to a client’s web browser (Excel5)
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$filename.'.xls"');
        header('Cache-Control: max-age=0');
        // If you're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=1');
        
        // If you're serving to IE over SSL, then the following may be needed
        header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header ('Pragma: public'); // HTTP/1.0
        
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;//这个是防止导出之后office版本过高不兼容的问题
    }