PHPは2つのsheetのexcelを生成する

ThinkPHP5.1で2つの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 "
";
        $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';
    }