yii2 excel 導出方法

为什么80%的码农都做不了架构师？>>>   

1.前端頁面：

<?= Menu::isAction('/Test/test/export')?Html::a("<span class='text-center ml--5'>导出</span>", null,['id'=>'export']):'' ?>

<script>
    $(function () {
        $('#export').click(function () {
            layer.confirm('确定要导出***数据？',
                {
                    icon:2,
                    btn:['确定','取消']
                },
                function () {
                    if(window.location.href="<?= Url::to(['export',$search])?>"){
                        layer.closeAll();
                    }else{
                        layer.alert('导出****信息发生错误',{icon:0})
                    }
            },function () {
                    window.location.reload();
             });
        });
    })

</script>

2.控制器方法：

public function actionExport()
{
    $url = $this->findApiUrl() . $this->_url . "export";
    $queryParam = Yii::$app->request->queryParams;
    if (!empty($queryParam)) {
        $url .= "?" . http_build_query($queryParam);
    }
    $dataProvider = $this->findCurl()->get($url);
    $dataProvider=Json::decode($dataProvider);
    return  $this->getExcelData($dataProvider['rows']);
}

private function getExcelData($data)
    {
        //排除不要的数据
        foreach ($data as $key=>$val){
            unset($data[$key]['user_id']);    //獲取要導出數據表信息id
        }
        $headArr = [
            '**代碼',
            '**名稱',
            '**狀態',
            '**描述',
            '档案建立人',
            '建档日期',
            '最后修改人',
            '修改日期',
        ];
        $this->getExcels($headArr, $data);
    }
    private function getExcels($headArr, $data)
    {


        // 导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
//        import("Org.Util.PHPExcel");
//        import("Org.Util.PHPExcel.Writer.Excel5");
//        import("Org.Util.PHPExcel.IOFactory.php");
//        $staffModel=new StaffSearch();
        $date = date("Y_m_d", time()) . rand(0, 99);
        $fileName = "_{$date}.xls";
        // 创建PHPExcel对象，注意，不能少了\
        $objPHPExcel = new \PHPExcel();
        $objProps = $objPHPExcel->getProperties();

        // 设置表头
        $key = "A";

        foreach ($headArr as $v) {
            $colum = $key;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v);
            if ($key == "Z") {
                $key = "AA";
            } elseif ($key == "AZ") {
                $key = "BA";
            } else {
                $key++;
            }
        }
        $column = 2;
        $objActSheet = $objPHPExcel->getActiveSheet();


        foreach ($data as $key => $rows) { // 行写入
            $span = "A";
            foreach ($rows as $keyName => $value) { // 列写入

                $j = $span;
                $objActSheet->setCellValue($j . $column, $value);

                if ($span == "Z") {
                    $span = "AA";
                } elseif ($span == "AZ") {
                    $span = "BA";
                } else {
                    $span++;
                }
            }
            $column++;
        }

        $fileName = iconv("utf-8", "gb2312", $fileName);
        // 重命名表
        // $objPHPExcel->getActiveSheet()->setTitle('test');
        // 设置活动单指数到第一个表,所以Excel打开这是第一个表
        $objPHPExcel->setActiveSheetIndex(0);
        ob_end_clean(); // 清除缓冲区,避免乱码
        header('Content-Type: application/vnd.ms-excel');
        header("Content-Disposition: attachment;filename=" . $fileName);
        header('Cache-Control: max-age=0');

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output'); // 文件通过浏览器下载
        exit();
    }

3.後台模型：

public function actionExport()
{
    $searchModel = new testSearch();  //test是model中對應的表
    $queryParams=Yii::$app->request->queryParams;
    $dataProvider = $searchModel->export($queryParams);
    $model = $dataProvider->getModels();
    $list['rows'] = $model;
    $list['total'] = $dataProvider->totalCount;
    return $list;
}

public function export($params)
{
    $query = Test::find()->select('***,***,***,');  //導出對應的某張表中字段
    $dataProvider = new ActiveDataProvider([
        'query' => $query,
        'pagination' => [
            'pageSize' => false,
        ]
    ]);
    $this->load($params);
    if(!$this->validate())
    {
        return $dataProvider;
    }
    return $dataProvider;
}

转载于:https://my.oschina.net/SimTao/blog/1301759