yii2 excel 导出 (图片加设置宽高水平垂直居中)

   composer require "phpoffice/phpexcel"    用这个命令下载就可以(等一会时间),前提是你已经安装的composer 如果没有,就安装一个,就不多说了。

   安装完成后 window 下打开命令窗口 输入compposer 查看是否安装成功,然后打开你的yii2.0(高级版)框架的verdor 这个文件夹 Shift+右键,打开命令窗口,运行上面那个下载命令他就会自动下载并配置好了。 如果出现phpoffice这个文件夹就对了(里面有PHPExcel 这个文件)。

   然后在Controller里面引入  use \PHPExcel;  记得要加“\”哦。   然后在方法里实例化就可以了,在具体的excel方法前面也要加‘\’不然会报错。

   最后就是导出了, 这个比较简单   这 只是一个简单的例子 

/**
     * 导出excel
     */
    public function actionExcel()
    {
        $time = date('Y-m-d H-i-s', time());
        $names = $time . '设备数据' . '.xls';
        $objPHPExcel = new \PHPExcel();
        $query = new \yii\db\Query();
        $query = $query->select("t1.*, t2.series_name, t2.series_model, t2.series_rows")
            ->from(['t1' => 'ss_equipment'])
            ->leftJoin(['t2' => 'ss_series'], "t1.series_id = t2.id")
            ->where(1);
        $list = $query->all();
        /*以下就是对处理Excel里的数据, 横着取数据,主要是这一步,其他基本都不要改*/
        foreach($list as $k=>$v) {
            $num= $k+1;
            $objPHPExcel->setActiveSheetIndex(0)
//Excel的第A列,uid是你查出数组的键值,下面以此类推
                ->setCellValue('A'.$num, $v['id'])
                ->setCellValue('B'.$num, $v['series_id'])
                ->setCellValue('C'.$num, $v['serial_number'])
                ->setCellValue('D'.$num, $v['series_name'])
                ->setCellValue('E'.$num, $v['series_model'])
                ->setCellValue('F'.$num, $v['series_rows'])
                ->setCellValue('G'.$num, $v['status'])
                ->setCellValue('H'.$num, $v['created'])
                ->setCellValue('I'.$num, $v['updated']);
        }
        $objPHPExcel->getActiveSheet()->setTitle('User');
        $objPHPExcel->setActiveSheetIndex(0);
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $names . '"'); //excel表格名称
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        return $this->returnData('', 1, '导出成功');
    }

上面是快速导出,下面是(图片加设置宽高水平垂直居中)

/**
     * 导出excel
     */
    public static function actionExcel($equipment_numbers = "")
    {
        //设置文件名称
        $time = date('Y-m-d H-i-s', time());
        $names = $time . '新增设备规则数据' . '.xls';
        $query = new \yii\db\Query();
        $query = $query->select("t1.*, t2.series_name, t2.series_model, t2.series_rows")
            ->from(['t1' => 'ss_equipment'])
            ->leftJoin(['t2' => 'ss_series'], "t1.series_id = t2.id")
            ->where(1);
        if($equipment_numbers != null){
            $query ->andWhere(['in','serial_number',$equipment_numbers]);
        }
        $list = $query->all();
        // 导出Exl
        $objPHPExcel = new \PHPExcel();
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objActSheet = $objPHPExcel->getActiveSheet();

        // 水平居中(位置很重要,建议在最初始位置)
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('A')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('B')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('C')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('D')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('E')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('F')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('G')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('H')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('I')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('J')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objActSheet->setCellValue('A1', 'id');
        $objActSheet->setCellValue('B1', '设备id');
        $objActSheet->setCellValue('C1', '设备号');
        $objActSheet->setCellValue('D1', '系列名称');
        $objActSheet->setCellValue('E1', '设备型号');
        $objActSheet->setCellValue('F1', '排数');
        $objActSheet->setCellValue('G1', '状态');
        $objActSheet->setCellValue('H1', '创建时间');
        $objActSheet->setCellValue('I1', '更新时间');
        $objActSheet->setCellValue('J1', '图片地址');

        // 设置个表格宽度
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(12);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(12);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(12);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(12);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(12);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(12);
        $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(12);
        $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(12);
        $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(80);

        // 垂直居中
        $objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('C')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('E')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('F')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('G')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('H')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('I')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('J')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

        /*以下就是对处理Excel里的数据, 横着取数据,主要是这一步,其他基本都不要改*/
        foreach ($list as $k => $v) {
            $k +=2;
            $v['created'] = isset($v['created']) ?$v['created'] :"";
            $v['created'] = date('Y-m-d h:i:s',$v['created']);
            $v['updated'] = isset($v['updated']) ?$v['updated'] :"";
            $v['updated'] = date('Y-m-d h:i:s',$v['updated']);

            $objActSheet->setCellValue('A'.$k, $v['id']);
            $objActSheet->setCellValue('B'.$k, $v['series_id']);
            $objActSheet->setCellValue('C'.$k, $v['serial_number']);
            $objActSheet->setCellValue('D'.$k, $v['series_name']);
            $objActSheet->setCellValue('E'.$k, $v['series_model']);
            $objActSheet->setCellValue('F'.$k, $v['series_rows']);
            $objActSheet->setCellValue('G'.$k, $v['status']);
            $objActSheet->setCellValue('H'.$k, $v['created']);
            $objActSheet->setCellValue('I'.$k, $v['updated']);
            $objActSheet->setCellValue('J'.$k);

           // 图片生成
            $img=new \PHPExcel_Worksheet_Drawing();
            $img->setPath(ROOT_DIR.$v['img']);//写入图片路径

            // 设置宽度高度
            $img->setHeight(80);//照片高度
            $img->setWidth(80); //照片宽度
            /*设置图片要插入的单元格*/
            $img->setCoordinates('J'.$k);
            // 图片偏移距离
            $img->setOffsetX(12);
            $img->setOffsetY(12);
            $img->setWorksheet($objPHPExcel->getActiveSheet());


            // 表格高度
            $objActSheet->getRowDimension($k)->setRowHeight(80);

        }
        //设置活动单指数到第一个表,所以Excel打开这是第一个表
        $objPHPExcel->setActiveSheetIndex(0);
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $names . '"'); //excel表格名称
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');//文件通过浏览器下载
    }

大海技术博客
请先登录后发表评论
  • latest comments
  • 总共1条评论
大海技术博客 大海技术博客

大海大海技术博客

2020-04-17 17:12:38 回复