PHP生成两个sheet的excel

ThinkPHP5.1中生成一个两个sheet，并且可以插入图片的excel。其中的PHPexcel是通过composer安装的

进入到项目目录，将composer.json放在与public同级中，然后执行composer命令：composer require phpoffice/phpexcel

也可以不使用composer安装，如果不使用composer安装的话，需要自己引入PHPexcel。具体例子如：

require_once __DIR__ . '/../../../vendor/phpoffice/phpexcel/Classes/PHPExcel.php';

这个是我自己的地址引入的，需要自行更改

    /**
     * 生成两个sheet，带有图片的excel
     * @throws Exception
     * @throws \PHPExcel_Exception
     * @throws \PHPExcel_Reader_Exception
     * @throws \PHPExcel_Writer_Exception
     */
    public function downDemoExcel() {
//        echo "<pre>";
        $order_id = $this->request->param('order_id', 0, 'intval');
        $fileName = '订单：' . $order_id;
//            序号，商品图片，商品名称，单价，数量。合计
        //查询订单商品
        $where = [
            ['a.orderid', '=', $order_id],
        ];
        $order_goods_list = Db::connect('db_mini_mall')->table('ims_ewei_shop_order_goods')->alias('a')->leftJoin('ims_ewei_shop_goods b', 'a.goodsid = b.id')->field('a.id,a.price,a.total,a.realprice,b.title,b.thumb,b.goods_code')->where($where)->order('b.title desc')->select();
        if (!empty(count($order_goods_list))) {
            $count_total = 0;
            $count_money = 0;
            $goods_list = array();
            foreach ($order_goods_list as $key => $value) {
                //根据code查询品牌
                $data[] = [
//                    $value['id'],
                    $key+1,
                    $value['title'],
                    $value['realprice'],
                    $value['total'],
                    $value['price'],
                ];
                $count_total = $count_total + $value['total'];
                $count_money = $count_money + $value['price'];
            }
//            $data[] = [];
            $data[] = [
                '',
                '',
                '合计',
                $count_total,
                $count_money,
            ];
        }
        //查询店铺名称
        $s_where = [
            ['b.id', '=', $order_id],
        ];
        $shop_name_arr = Db::connect('db_mini_mall')->table("ims_yd_supplier")->alias('a')->leftJoin('ims_ewei_shop_order b', 'a.id = b.supplier_id')->field('a.name')->where($s_where)->limit(1)->select();
        $shop_name = !empty($shop_name_arr[0]['name']) ? $shop_name_arr[0]['name'] : '';
        //查询订单信息
        if (!empty($shop_name)) {
            $where[] = ['b.name', 'like', '%' . $shop_name . '%'];
        }
        $where = [
            ['s.id', '=', $order_id],
        ];
        $shop_order_data = Db::connect('db_mini_mall')->table('ims_ewei_shop_order')->alias('s')->leftJoin('ims_yd_supplier b', 's.openid = b.openid')->field('s.supplier_id,s.openid,s.id,s.ordersn,s.createtime,b.name,b.nickname,s.price,s.address,s.delivery_type,s.remark,b.address as shop_address')->where($where)->select();

        $user_name = array();
        if (!empty($shop_order_data)) {
            $db_mini_mall = new StoreModel();
            $db_btj = new SignModel();
            //查询参考点位
            $shop_dianwei = '';
            $shop_unionid = $db_mini_mall->querySql("SELECT unionid,un_id from ims_yd_supplier where id = '{$shop_order_data[0]['supplier_id']}'");
            if(!empty(count($shop_unionid))){
                $shop_unionid_one = !empty($shop_unionid[0]['unionid']) ? $shop_unionid[0]['unionid'] : $shop_unionid[0]['un_id'];
                if(!empty($shop_unionid_one)){
                    //查询点位
                    $shop_unionid_one_arr = $db_btj->querySql("SELECT address from potential_customer where union_id = '{$shop_unionid_one}'");
                    $shop_dianwei = !empty($shop_unionid_one_arr[0]['address']) ? $shop_unionid_one_arr[0]['address'] : '';
                }
            }

            //查询业务员
            $user_unionid = $db_mini_mall->querySql("SELECT unionid from ims_ewei_shop_member where openid = '{$shop_order_data[0]['openid']}'");
            if (!empty($user_unionid[0]['unionid'])) {
                $user_admin_id = $db_btj->querySql("SELECT service_id from potential_customer where union_id = '{$user_unionid[0]['unionid']}'");
                if (!empty($user_admin_id[0]['service_id'])) {
                    $user_name = $db_btj->querySql("SELECT `name`,user_name from btj_admin_user where user_id = {$user_admin_id[0]['service_id']}");
                }
            }

            try {
                $address = unserialize($shop_order_data[0]['address']);
            } catch (Exception $exceptione) {
                $address = '';
            }
            if (!empty($address)) {
                $new_order['realname'] = $address['realname'];//接收人真实姓名
                $new_order['phone'] = $address['mobile'];//接收人真实联系电话
                $new_order['address'] = $address['address'];//接收人配送地址
            } else {
                $new_order['realname'] = '';//接收人真实姓名
                $new_order['phone'] = '';//接收人真实联系电话
                $new_order['address'] = '';//接收人配送地址
            }
            $shop_data = [
                [
                    '订单时间',
                    !empty($shop_order_data[0]['createtime']) ? date('Y/m/d H:i', $shop_order_data[0]['createtime']) : '',
                    '姓名',
                    !empty($new_order['realname']) ? $new_order['realname'] : '',
                ],
                [
                    '订单号',
                    !empty($shop_order_data[0]['ordersn']) ? $shop_order_data[0]['ordersn'] : '',
                    '电话',
                    !empty($new_order['phone']) ? $new_order['phone'] : '',
                    //'地址','',
                    //!empty($new_order['address']) ? $new_order['address'] : '',
                ],
            ];
        }

        //表头
        $title = ['序号', '商品名称', '单价', '数量', '金额'];

        //生成excel
        $obj = new PHPExcel();

        $obj->getDefaultStyle()->getFont()->setName('DengXian');//字体样式
//        $obj->getDefaultStyle()->getFont()->setSize(16);//字体大小
        //横向单元格标识
        $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');
        $obj->getActiveSheet()->setTitle('司机联');   //设置第一个sheet名称
        $_row = 1;   //设置纵向单元格标识
        if ($title) {
            $_cnt = count($title);
//            $obj->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//设置水平居中
            $obj->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);//设置垂直居中
            $obj->getActiveSheet()->mergeCells('A' . $_row . ':' . $cellName[$_cnt - 1] . $_row);   //合并单元格
            $obj->setActiveSheetIndex()->setCellValue('A' . $_row, '社区派配货单：' . $order_id . '(司机联)');  //设置合并后的单元格内容
            $obj->getActiveSheet()->getRowDimension()->setRowHeight(100);
            $obj->getActiveSheet()->getColumnDimension('B')->setWidth(40);
            //设置店铺信息
            $_row++;
            if (!empty(count($shop_data))) {
                $styleBackground = array(
                    'fill' => array(
                        'type' => PHPExcel_Style_Fill::FILL_SOLID,
//                        'color' => array('rgb' => 'AEEEEE')
                    ),
                );
                foreach ($shop_data as $key => $value) {
                    $i = 0;
                    foreach ($value AS $k => $v) {
//                        $obj->setActiveSheetIndex()->setCellValue($cellName[$i] . $_row, $v);
                        if ($k == 0 || $k == 2) {
                            $obj->setActiveSheetIndex()->setCellValue($cellName[$i] . $_row, $v);
                            $obj->setActiveSheetIndex()->getStyle($cellName[$i] . $_row)->applyFromArray($styleBackground);
                        } else {
                            $obj->setActiveSheetIndex()->setCellValue($cellName[$i] . $_row, $v);
                        }
                        $i++;
                    }
                    $_row++;
                }
            }
            //设置列标题
            $i = 0;
            $_t_row = 9;
            foreach ($title AS $v) {
                $obj->setActiveSheetIndex()->setCellValue($cellName[$i] . $_t_row, $v);
                $i++;
            }
            $_t_row++;
        }
        $obj->setActiveSheetIndex()->setCellValue('A5' , '地址');  //设置单元格内容
        $obj->getActiveSheet()->mergeCells('B5' . ':E' . 5);   //合并单元格
        $obj->setActiveSheetIndex()->setCellValue('B' . 5, $new_order['address']);  //设置单元格内容

        //参考点位
        $obj->setActiveSheetIndex()->setCellValue('A6' , '参考点位');  //设置单元格内容
        $obj->getActiveSheet()->mergeCells('B6' . ':E' . 6);   //合并单元格
        $obj->setActiveSheetIndex()->setCellValue('B' . 6, $shop_dianwei);  //设置单元格内容

        $obj->getActiveSheet()->mergeCells('D2' . ':E2');   //合并单元格
        $obj->getActiveSheet()->mergeCells('D3' . ':E3');   //合并单元格
        $obj->getActiveSheet()->mergeCells('D4' . ':E4');   //合并单元格
        $obj->getActiveSheet()->mergeCells('D5' . ':E5');   //合并单元格

        //填写数据
        if ($data) {
            //设置样式，
            $styleThinBlackBorderOutline = array(
                'borders' => array(
                    'allborders' => array( //设置全部边框
                        'style' => PHPExcel_Style_Border::BORDER_THIN //粗的是thick
                    ),
                ),
            );
            $border = $_t_row + count($data) - 1;
            $obj->getActiveSheet()->getStyle('A1:' . $cellName[(count($title) - 1)] . $border)->applyFromArray($styleThinBlackBorderOutline);
            $i = 0;
            foreach ($data AS $_v) {
                $j = 0;
                foreach ($_v AS $_cell) {
                    $obj->getActiveSheet(0)->setCellValue($cellName[$j] . ($i + $_t_row), $_cell);//一条一条数据加入到单元格中
                    $j++;
                }
                $i++;
            }
        }

        $send = count($data) + 10;
        $obj->setActiveSheetIndex()->setCellValue('A' . ($send + 1), '业务员');  //设置单元格内容
        $obj->getActiveSheet()->mergeCells('B' . ($send + 1) . ':E' . ($send + 1));   //合并单元格
        $obj->setActiveSheetIndex()->setCellValue('B' . ($send + 1), !empty($user_name[0]) ? $user_name[0]['name'] . '(' . $user_name[0]['user_name'] . ')' : '');  //设置单元格内容

        $obj->setActiveSheetIndex()->setCellValue('A' . ($send + 2), '分拣人');  //设置单元格内容
        $obj->setActiveSheetIndex()->setCellValue('B' . ($send + 2), '');  //设置单元格内容
        $obj->setActiveSheetIndex()->setCellValue('C' . ($send + 2), '排线');  //设置单元格内容
        $obj->setActiveSheetIndex()->setCellValue('D' . ($send + 2), '');  //设置单元格内容

        $obj->setActiveSheetIndex()->setCellValue('A' . ($send + 3), '司机');  //设置单元格内容
        $obj->setActiveSheetIndex()->setCellValue('B' . ($send + 3), '');  //设置单元格内容
        $obj->setActiveSheetIndex()->setCellValue('C' . ($send + 3), '装车号');  //设置单元格内容
        $obj->setActiveSheetIndex()->setCellValue('D' . ($send + 3), '');  //设置单元格内容

        $obj->getActiveSheet()->getStyle('A1:E' . ($send + 3))->applyFromArray($styleThinBlackBorderOutline);//设置边框

        //            $data['qrCode_siji'] = 'https://btj.yundian168.com/biz/bd1/index.html#/driver?orderno=' . $order_res['ordersn'];//司机二维码 链接
        //            $data['qrCode_dianzhu'] = 'http://ydxqtptest.yundian168.com?orderId='.$order_res['id'].'&order_no='.$order_res['ordersn'];//店主二维码 链接
        //查询订单号
        $order_where = [
            ['id', '=', $order_id],
        ];
        $order_no = Db::connect('db_mini_mall')->table('ims_ewei_shop_order')->where($order_where)->find();
        $qr_url = 'https://btj.yundian168.com/biz/bd1/index.html#/driver?orderno=' . $order_no['ordersn'];
        require_once __DIR__ . '/../../../vendor/phpqrcode/phpqrcode.php';

        $filename = './static/qrcode/' . $order_id . '_' . $order_no['ordersn'] . '1.png';
        \QRcode::png($qr_url, $filename);//生成二维码图片

        // 图片生成
        $img = 1;
        $objDrawing[$img] = new \PHPExcel_Worksheet_Drawing();//将图片放在excel中
        $objDrawing[$img]->setPath($filename);//设置图片路径
        // 设置宽度高度
        $objDrawing[$img]->setHeight(100);//照片高度
        $objDrawing[$img]->setWidth(100); //照片宽度
        /*设置图片要插入的单元格*/
        $objDrawing[$img]->setCoordinates('D'.$img);
        // 图片偏移距离
        $objDrawing[$img]->setOffsetX(5);
        $objDrawing[$img]->setOffsetY(15);
        $objDrawing[$img]->setWorksheet($obj->getActiveSheet());//将这个图片放在第一个sheet中
/*********************************************************************************/
        $sheet2 = $obj->createSheet();//创建第二个sheet
        $obj->setactivesheetindex(1);//区别于第一个sheet，
//        $obj->getDefaultStyle()->getFont()->setSize(16);
        //横向单元格标识
        $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');
        $obj->getActiveSheet(1)->setTitle('店主联');   //设置sheet2的名称
        $_row = 1;   //设置纵向单元格标识
        if ($title) {
            $_cnt = count($title);
//            $obj->getActiveSheet(1)->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//设置水平居中
            $obj->getActiveSheet(1)->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);//设置垂直居中
//            $obj->getActiveSheet(1)->mergeCells('A' . $_row . ':' . $cellName[$_cnt - 1] . $_row);   //合并单元格
            $obj->getActiveSheet(1)->mergeCells('A1:E1');   //合并单元格
//            $obj->setActiveSheetIndex(1)->setCellValue('A' . $_row, '社区派配货单：' . $order_id . '(店主联)');  //设置合并后的单元格内容
            $obj->setActiveSheetIndex(1)->setCellValue('A1', '社区派配货单：' . $order_id . '(店主联)');  //设置合并后的单元格内容
            $obj->getActiveSheet(1)->getRowDimension()->setRowHeight(100);
            $obj->getActiveSheet(1)->getColumnDimension('B')->setWidth(40);
            //设置店铺信息
            $_row++;
            if (!empty(count($shop_data))) {
                $styleBackground = array(
                    'fill' => array(
                        'type' => PHPExcel_Style_Fill::FILL_SOLID,
//                        'color' => array('rgb' => 'AEEEEE')
                    ),
                );
                foreach ($shop_data as $key => $value) {
                    $i = 0;
                    foreach ($value AS $k => $v) {
//                        $obj->setActiveSheetIndex()->setCellValue($cellName[$i] . $_row, $v);
                        if ($k == 0 || $k == 2) {
                            $obj->setActiveSheetIndex(1)->setCellValue($cellName[$i] . $_row, $v);
                            $obj->setActiveSheetIndex(1)->getStyle($cellName[$i] . $_row)->applyFromArray($styleBackground);
                        } else {
                            $obj->setActiveSheetIndex(1)->setCellValue($cellName[$i] . $_row, $v);
                        }
                        $i++;
                    }
                    $_row++;
                }
            }
            //设置列标题
            $i = 0;
            $_t_row = 9;
            foreach ($title AS $v) {
                $obj->setActiveSheetIndex(1)->setCellValue($cellName[$i] . $_t_row, $v);
                $i++;
            }
            $_t_row++;
        }
        $obj->setActiveSheetIndex(1)->setCellValue('A5' , '地址');  //设置单元格内容
        $obj->getActiveSheet(1)->mergeCells('B5' . ':E' . 5);   //合并单元格
        $obj->setActiveSheetIndex(1)->setCellValue('B' . 5, $new_order['address']);  //设置单元格内容

        //参考点位
        $obj->setActiveSheetIndex(1)->setCellValue('A6' , '参考点位');  //设置单元格内容
        $obj->getActiveSheet(1)->mergeCells('B6' . ':E' . 6);   //合并单元格
        $obj->setActiveSheetIndex(1)->setCellValue('B' . 6, $shop_dianwei);  //设置单元格内容

        $obj->getActiveSheet(1)->mergeCells('D2' . ':E2');   //合并单元格
        $obj->getActiveSheet(1)->mergeCells('D3' . ':E3');   //合并单元格
        $obj->getActiveSheet(1)->mergeCells('D4' . ':E4');   //合并单元格
        $obj->getActiveSheet(1)->mergeCells('D5' . ':E5');   //合并单元格

        //填写数据
        if ($data) {
            $styleThinBlackBorderOutline = array(
                'borders' => array(
                    'allborders' => array( //设置全部边框
                        'style' => PHPExcel_Style_Border::BORDER_THIN //粗的是thick
                    ),
                ),
            );
            $border = $_t_row + count($data) - 1;
            $obj->getActiveSheet(1)->getStyle('A1:' . $cellName[(count($title) - 1)] . $border)->applyFromArray($styleThinBlackBorderOutline);
            $i = 0;
            foreach ($data AS $_v) {
                $j = 0;
                foreach ($_v AS $_cell) {
                    $obj->getActiveSheet(1)->setCellValue($cellName[$j] . ($i + $_t_row), $_cell);
                    $j++;
                }
                $i++;
            }
        }
        //            $data['qrCode_siji'] = 'https://btj.yundian168.com/biz/bd1/index.html#/driver?orderno=' . $order_res['ordersn'];//司机二维码 链接
        //            $data['qrCode_dianzhu'] = 'http://ydxqtptest.yundian168.com?orderId='.$order_res['id'].'&order_no='.$order_res['ordersn'];//店主二维码 链接
        //查询订单号
        $order_where = [
            ['id', '=', $order_id],
        ];
        $order_no = Db::connect('db_mini_mall')->table('ims_ewei_shop_order')->where($order_where)->find();
        $qr_url = 'http://ydxqtptest.yundian168.com?orderId=' . $order_id.'&order_no='.$order_no['ordersn'];
        require_once __DIR__ . '/../../../vendor/phpqrcode/phpqrcode.php';

        $filename = './static/qrcode/' . $order_id . '_' . $order_no['ordersn'] . '2.png';
        \QRcode::png($qr_url, $filename);

        // 图片生成
        $img = 1;
        $objDrawing[$img] = new \PHPExcel_Worksheet_Drawing();
        $objDrawing[$img]->setPath($filename);
        // 设置宽度高度
        $objDrawing[$img]->setHeight(100);//照片高度
        $objDrawing[$img]->setWidth(100); //照片宽度
        /*设置图片要插入的单元格*/
        $objDrawing[$img]->setCoordinates('D'.$img);
        // 图片偏移距离
        $objDrawing[$img]->setOffsetX(5);
        $objDrawing[$img]->setOffsetY(15);
        $objDrawing[$img]->setWorksheet($obj->getActiveSheet(1));

        //文件名处理
        if (!$fileName) {
            $fileName = uniqid(time(), true);
        }
        $objWrite = PHPExcel_IOFactory::createWriter($obj, 'Excel5');
        ob_end_clean();
        header('pragma:public');
        header("Content-Disposition:attachment;filename=$fileName.xls");
        $objWrite->save('php://output');
    }

直接生成一个sheet的excel

    function exportExcel($title = array(), $data = array(), $fileName = '', $savePath = './', $isDown = true) {
//        include_once 'PHPExcel-1.8/Classes/PHPExcel.php';
        require_once __DIR__ . '/../../../vendor/phpoffice/phpexcel/Classes/PHPExcel.php';
        $obj = new \PHPExcel();
        //横向单元格标识
        $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');
        $obj->getActiveSheet(0)->setTitle('sheet名称');   //设置sheet名称
        $_row = 1;   //设置纵向单元格标识
        if ($title) {
            $_cnt = count($title);
            $obj->getActiveSheet(0)->mergeCells('A' . $_row . ':' . $cellName[$_cnt - 1] . $_row);   //合并单元格
            $obj->setActiveSheetIndex(0)->setCellValue('A' . $_row, '数据导出：' . date('Y-m-d H:i:s'));  //设置合并后的单元格内容
            $_row++;
            $i = 0;
            foreach ($title AS $v) {   //设置列标题
                $obj->setActiveSheetIndex(0)->setCellValue($cellName[$i] . $_row, $v);
                $i++;
            }
            $_row++;
        }
        //填写数据
        if ($data) {
            $i = 0;
            foreach ($data AS $_v) {
                $j = 0;
                foreach ($_v AS $_cell) {
                    $obj->getActiveSheet(0)->setCellValue($cellName[$j] . ($i + $_row), $_cell);
                    $j++;
                }
                $i++;
            }
        }
        //文件名处理
        if (!$fileName) {
            $fileName = uniqid(time(), true);
        }
        $objWrite = \PHPExcel_IOFactory::createWriter($obj, 'Excel5');
        if ($isDown) {   //网页下载
            ob_end_clean();
            header('pragma:public');
            header("Content-Disposition:attachment;filename=$fileName.xls");
            $objWrite->save('php://output');
            exit;
        }
        $_fileName = iconv("utf-8", "gb2312", $fileName);   //转码
        $_savePath = $savePath . $_fileName . '.xlsx';
        $objWrite->save($_savePath);
        return $savePath . $fileName . '.xlsx';
    }