Yii2框架控制器中利用Phpexcel将数据导出excel文档

Yii2框架控制器中利用Phpexcel将数据导出excel文档

安装：

composer require "phpoffice/phpexcel": "*"

使用：
在控制器中use PHPExcel;
控制器导出方法:

public function actionExport()
{
    //要使用的数据
    $g_model = new Gifts;
    $gifts = $g_model::find()->asArray()->all();
    //实例化
    $objPHPExcel = new \PHPExcel();
    $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
    //phpexcel在使用时必须先设置值再进行其他设置
    //设置标题
    $objPHPExcel->getActiveSheet()->setCellValue('A1',  '2017年学术礼品会议情况');
    //设置居中 
    $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    //合并单元格
    $objPHPExcel->getActiveSheet()->mergeCells('A1:G1');
    //设置宽度
    //$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true); 
    //也可以设置为固定     
    $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
    //设置表头的值还有颜色
    $objPHPExcel->getActiveSheet()->setCellValue('A2',  '序号');//这里是设置A2单元格的内容
    $objPHPExcel->getActiveSheet()->getStyle('A2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('#ADDE79');
    $objPHPExcel->getActiveSheet()->setCellValue('B2',  '名称');
    $objPHPExcel->getActiveSheet()->getStyle('B2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('#ADDE79');
    $objPHPExcel->getActiveSheet()->setCellValue('C2',  '单价（元）');
    $objPHPExcel->getActiveSheet()->getStyle('C2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('#ADDE79');
    $objPHPExcel->getActiveSheet()->setCellValue('D2',  '库存');
    $objPHPExcel->getActiveSheet()->getStyle('D2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('#ADDE79');
    //动态生成列，从f开始，f对应的ascii吗为69
    $r_model = new Records;
    $records = $r_model::find()->asArray()->all();
    $ascii=69;
    foreach ($records as $key => $value) {
        $cell=chr($ascii).'2';
        $objPHPExcel->getActiveSheet()->setCellValue($cell,  $value['name']);
        $objPHPExcel->getActiveSheet()->getStyle($cell)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('#ADDE79');
        $data[chr($ascii)]=json_decode($value['used'],true);
        $last_count[chr($ascii)]['price']=$value['price'];
        $last_count[chr($ascii)]['stock']=$value['num'];
        $ascii+=1;    
    }    
    //在最后一列加上库存
    $objPHPExcel->getActiveSheet()->setCellValue(chr($ascii).'2',  '');
    $objPHPExcel->getActiveSheet()->getStyle(chr($ascii).'2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('#ADDE79');
    $objPHPExcel->getActiveSheet()->setCellValue(chr($ascii+1).'2',  '库存');
    $objPHPExcel->getActiveSheet()->getStyle(chr($ascii+1).'2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('#ADDE79');
    $last['price']='';
    $last['stock']='';
    //遍历数据
    foreach ($gifts as $key => $value) {
        $i=$key+3;
        $temp_num='0';
        $objPHPExcel->getActiveSheet()->setCellValue('A'.$i,  $key+1);
        $objPHPExcel->getActiveSheet()->setCellValue('B'.$i,  $value['name']);
        $objPHPExcel->getActiveSheet()->setCellValue('C'.$i,  $value['price']);
        $objPHPExcel->getActiveSheet()->setCellValue('D'.$i,  $value['stock']);
        //处理used字段中的记录
        foreach ($data as $k => $v) {
            $objPHPExcel->getActiveSheet()->setCellValue($k.$i,  isset($v[$value['id']])?'-'.$v[$value['id']]:0);
            if(isset($v[$value['id']])){
                $temp_num+=$v[$value['id']];
            }
        }
        //计算单行的库存
        $objPHPExcel->getActiveSheet()->setCellValue(chr($ascii+1).$i,  $value['stock']-$temp_num);
        $last['price']+=$value['stock']*$value['price'];
        $last['stock']+=$value['stock'];
    }
    //合并单元格,最后两行的统计
     $temp_l=$i+3;
     $temp_l2=$i+4;
     $objPHPExcel->getActiveSheet()->setCellValue('A'.$temp_l,  '合计');
     $objPHPExcel->getActiveSheet()->setCellValue('B'.$temp_l,  '金额');
     $objPHPExcel->getActiveSheet()->setCellValue('C'.$temp_l,  $last['price']);
     $objPHPExcel->getActiveSheet()->setCellValue('B'.$temp_l2,  '数量');
     $objPHPExcel->getActiveSheet()->setCellValue('D'.$temp_l2,  $last['stock']);
     $str='A'.($temp_l).':A'.($temp_l2);
     $objPHPExcel->getActiveSheet()->mergeCells($str);
     //计算单列会议的消耗
     $allprice='';
     $allstock='';
     foreach ($last_count as $key => $value) {
        $objPHPExcel->getActiveSheet()->setCellValue($key.$temp_l, '-'.$value['price']);
        $objPHPExcel->getActiveSheet()->setCellValue($key.$temp_l2, '-'.$value['stock']);
        $allprice += $value['price'];
        $allstock += $value['stock'];
     }
    //计算最后一列的统计
    $objPHPExcel->getActiveSheet()->setCellValue(chr($ascii+1).$temp_l,  $allprice);
    $objPHPExcel->getActiveSheet()->setCellValue(chr($ascii+1).$temp_l2,   $allstock);    
    //下载这个表格，在浏览器输出
    $file_name = '院外预算会议评估';
    header("Pragma: public");
    header("Expires: 0");
    header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
    header("Content-Type:application/force-download");
    header("Content-Type:application/vnd.ms-execl");
    header("Content-Type:application/octet-stream");
    header("Content-Type:application/download");;
    header('Content-Disposition:attachment;filename='.$file_name.'.xls');
    header("Content-Transfer-Encoding:binary");
    $objWriter->save('php://output');
}

效果图:

yii2_db_to_excel

    /**
     * @数据导出excel文档
     * @return string
     * @throws \PHPExcel_Exception
     * @throws \PHPExcel_Reader_Exception
     * @throws \PHPExcel_Writer_Exception
     */
    public function actionUpexcel()
    {

        $data = Models::find()->asArray()->all();//取得要导出的数据

        $objPHPExcel = new \PHPExcel();

        //设置文件的一些属性，在xls文件——>属性——>详细信息里可以看到这些值，xml表格里是没有这些值的
        $objPHPExcel
            ->getProperties()  //获得文件属性对象，给下文提供设置资源
            ->setCreator( "MaartenBalliauw")             //设置文件的创建者
            ->setLastModifiedBy( "MaartenBalliauw")       //设置最后修改者
            ->setTitle( "Office2007 XLSX Test Document" )    //设置标题
            ->setSubject( "Office2007 XLSX Test Document" )  //设置主题
            ->setDescription( "Test document for Office2007 XLSX, generated using PHP classes.") //设置备注
            ->setKeywords( "office 2007 openxmlphp")        //设置标记
            ->setCategory( "Test resultfile");                //设置类别
        // 位置aaa *为下文代码位置提供锚
        //给表格添加数据


        $objPHPExcel->setActiveSheetIndex(0)//表头的信息
        ->setCellValue('A1', "商家ID")
            ->setCellValue('B1', "用户ID")
            ->setCellValue('C1', "姓名/公司")
            ->setCellValue('D1', "电话")
            ->setCellValue('E1', "店铺名称")
            ->setCellValue('F1', "入驻时间")
            ->setCellValue('G1', "审核状态")
            ->setCellValue('H1', "商品数量")
            ->setCellValue('I1', "商品图片数量")
            ->setCellValue('J1', "订单数量")
            ->setCellValue('K1', "商家一级分类")
            ->setCellValue('L1', "商家二级分类")
            ->setCellValue('M1', "分站");
        $i=2;
        foreach ($data as $key => $value) {
            $objPHPExcel->getActiveSheet()             //     设置第一个内置表（一个xls文件里可以有多个表）为活动的
            ->setCellValue( 'A'.$i, $value['id'] )       //给表的单元格设置数据
            ->setCellValue( 'B'.$i, $value['uid'] )      //数据格式可以为字符串
            ->setCellValue( 'C'.$i, $value['name'])            //数字型
            ->setCellValue( 'D'.$i, " ".$value['mobile'] )            //
            ->setCellValue( 'E'.$i, $value['shop_name'] )           //布尔型
            ->setCellValue( 'F'.$i, $value['created_at'] )
                ->setCellValue( 'G'.$i, $value['status'] ? '已审核':'未审核' )
                ->setCellValue( 'H'.$i, $value['goods_total'] )
                ->setCellValue( 'I'.$i, $value['goods_img_total'])
                ->setCellValue( 'J'.$i, $value['order_total'] )
                ->setCellValue( 'K'.$i, ShopCategory::findOne($value['shop_cate_parent'])->name )
                ->setCellValue( 'L'.$i, ShopCategory::findOne($value['shop_cate'])->name)
                ->setCellValue( 'M'.$i, \common\models\Fenzhan::find()->where(['site_id'=>$value['site_id']])->one()->site_name );
            $i++;
        }
        //合计
        $objPHPExcel->getActiveSheet()             //     设置第一个内置表（一个xls文件里可以有多个表）为活动的
        ->setCellValue( 'A'.$i, '' )       //给表的单元格设置数据
        ->setCellValue( 'B'.$i, '' )      //数据格式可以为字符串
        ->setCellValue( 'C'.$i, '')            //数字型
        ->setCellValue( 'D'.$i, '' )            //
        ->setCellValue( 'E'.$i, '共'.count($data).'条记录')           //布尔型
        ->setCellValue( 'F'.$i, '' )
            ->setCellValue( 'G'.$i, '' )
            ->setCellValue( 'H'.$i, '' )
            ->setCellValue( 'I'.$i, '' )
            ->setCellValue( 'J'.$i, '' )
            ->setCellValue( 'K'.$i, '' );

        //公式

        //得到当前活动的表,注意下文教程中会经常用到$objActSheet
        $objActSheet =$objPHPExcel->getActiveSheet();
        // 位置bbb *为下文代码位置提供锚
        //给当前活动的表设置名称
        $objActSheet->setTitle('统计表');
        //代码还没有结束，可以复制下面的代码来决定我们将要做什么

        //我们将要做的是
        //1,直接生成一个文件
        $objWriter =\PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save('myexchel.xlsx');
        ob_end_clean();//清空缓冲区
        header('Content-Type:application/vnd.ms-excel');
        header("Content-Disposition:attachment;filename=记录表".date('YmdHis').'.xls');
        header('Cache-Control:max-age=0');
        $objWriter =\PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        return $this->render($this->actionID);
    }