tp5 phpexcel

//导出配送安装
function DistributionExcel($xlsName, $list) {
    include './data/extend/phpexcel_classes/PHPExcel.php';
    //xldebug($list);
    $fileName = $xlsName . '-' . date('_YmdHis');
    $objPHPExcel = new \PHPExcel();

    //设置当前的sheet
    $objPHPExcel->setActiveSheetIndex(0);
    $objActSheet = $objPHPExcel->getActiveSheet();

    //设置sheet的name
    $objPHPExcel->getActiveSheet()->setTitle('Simple');

    //设置单元格的值
    //第一行
    // 图片生成
    $objDrawing = new \PHPExcel_Worksheet_Drawing();
    $objDrawing->setPath('./static/v1/images/logo_164x34.png');
    $objDrawing->setWidth(164); //照片宽度
    $objDrawing->setHeight(34); //照片高度
    $objDrawing->setCoordinates('A1'); //设置图片要插入的单元格
    $objDrawing->setOffsetX(0);
    $objDrawing->setOffsetY(0);
    $objDrawing->setWorksheet($objActSheet);
    unset($objDrawing);

    $objActSheet->mergeCells('A1:C1');
    $bigtitle = "万米家居家具精品物流配送单（" .$list['city_name']. "  ".$list['receiver_name'] . "）";
    $objActSheet->setCellValue('C1', $bigtitle);
    $objActSheet->mergeCells('A' . '1' .':B'. '1');
    $objActSheet->mergeCells('C' . '1' .':I'. '1');
    //设置字体大小加粗
    $objActSheet->getStyle('C1')->getFont()->setName('宋体')->setSize(18)->setBold(true);

    //第二行
    $rowNum = 2;
    //表格填入值
    $objActSheet->setCellValue('A' . $rowNum, '业务类型');
    $objActSheet->setCellValue('B' . $rowNum, '');
    $objActSheet->setCellValue('C' . $rowNum, '项目经理');
    $objActSheet->setCellValue('D' . $rowNum, '');
    $objActSheet->setCellValue('E' . $rowNum, '');
    $objActSheet->setCellValue('F' . $rowNum, '');
    $objActSheet->setCellValue('H' . $rowNum, '单据号');
    $objActSheet->setCellValue('I' . $rowNum, '');

    //第三行
    $rowNum = 3;
    $objActSheet->setCellValue('A' . $rowNum, '订单日期');
    $objActSheet->setCellValue('B' . $rowNum, date('Y-m-d',$list['pay_time']));
    $objActSheet->setCellValue('C' . $rowNum, '安装日期');
    $objActSheet->setCellValue('D' . $rowNum, date('Y-m-d',$list['installation_time']));
    $objActSheet->setCellValue('E' . $rowNum, '工程监理');
    $objActSheet->setCellValue('F' . $rowNum, '');
    $objActSheet->setCellValue('H' . $rowNum, '合同编号');
    $objActSheet->setCellValue('I' . $rowNum, '');
    //设置表格高
    $objPHPExcel->getActiveSheet()->getRowDimension('3')->setRowHeight(40);

    //第四行
    $rowNum = 4;
    $objActSheet->setCellValue('A' . $rowNum, '业主姓名 ');
    $objActSheet->setCellValue('B' . $rowNum, $list['receiver_name']);
    $objActSheet->setCellValue('C' . $rowNum, '设计师');
    $objActSheet->setCellValue('H' . $rowNum, $list['designer_name']);
    $objActSheet->setCellValue('I' . $rowNum, $list['designer_mobile']);
    //合并单元格
    $objActSheet->mergeCells('E' . $rowNum.':G' . $rowNum);

    //第五行
    $rowNum = 5;
    $objActSheet->setCellValue('A' . $rowNum, '施工地址 ');
        $objActSheet->setCellValue('B' . $rowNum, $list['receiver_address']);

    //合并单元格
    $objActSheet->mergeCells('B' . $rowNum.':I' . $rowNum);

    //第六行
    $rowNum = 6;
    $objActSheet->setCellValue('A' . $rowNum, '序号');
    $objActSheet->setCellValue('B' . $rowNum, '产品图片');
    $objActSheet->setCellValue('C' . $rowNum, '品名');
    $objActSheet->setCellValue('D' . $rowNum, '型号');
    $objActSheet->setCellValue('E' . $rowNum, '规格/尺寸');
    $objActSheet->setCellValue('F' . $rowNum, '体积');
    $objActSheet->setCellValue('G' . $rowNum, '物流包装件数');
    $objActSheet->setCellValue('H' . $rowNum, '数量');
    $objActSheet->setCellValue('I' . $rowNum, '备注');

    //设置表格高
    $objPHPExcel->getActiveSheet()->getRowDimension($rowNum)->setRowHeight(40);

    $goods_order['start'] = 7;
    //第七行开始循环
    foreach ($list['goods_list'] as $goods_info){
        //第六行
        $rowNum += 1;
        $objActSheet->setCellValue('A' . $rowNum, $rowNum-6);
//        $objActSheet->setCellValue('B' . $rowNum, $goods_info['goods_picture']);
        if (file_exists($goods_info['pic_cover_small'])) {

            $objDrawing = new \PHPExcel_Worksheet_Drawing();
            $objDrawing->setPath($goods_info['pic_cover_small']);
            $objDrawing->setWidthAndHeight(80, 60); //照片宽度
            //$objDrawing[$k2]->setHeight(43);//照片高度
            /*设置图片要插入的单元格*/
            $objDrawing->setCoordinates('B' . $rowNum);
            // 图片偏移距离
            $objDrawing->setOffsetX(18);
            $objDrawing->setOffsetY(5);
            $objDrawing->setWorksheet($objActSheet);
            unset($objDrawing);
        }
        $objActSheet->setCellValue('C' . $rowNum, $goods_info['goods_name']);
        $objActSheet->setCellValue('D' . $rowNum, $goods_info['goods_sn']);
        $objActSheet->setCellValue('E' . $rowNum, $goods_info['attr_value_name']);
        $objActSheet->setCellValue('F' . $rowNum, $goods_info['total_volume']);
        $objActSheet->setCellValue('G' . $rowNum, $goods_info['total_num']);
        $objActSheet->setCellValue('H' . $rowNum, $goods_info['num']);
        $objActSheet->setCellValue('I' . $rowNum, $goods_info['memo']);
        //设置表格高
        $objPHPExcel->getActiveSheet()->getRowDimension($rowNum)->setRowHeight(70);
    }
    $goods_order['end'] = $rowNum;


    //第n+1行
    $rowNum += 1;
    $objActSheet->setCellValue('A' . $rowNum, '产品合计');
    $objActSheet->setCellValue('F' . $rowNum, '=SUM(F'. $goods_order['start'].':F'. $goods_order['end'].')');
    $objActSheet->setCellValue('G' . $rowNum, '=SUM(G'. $goods_order['start'].':G'. $goods_order['end'].')');
    $objActSheet->setCellValue('H' . $rowNum, '=SUM(H'. $goods_order['start'].':H'. $goods_order['end'].')');
    $objActSheet->setCellValue('I' . $rowNum, '');

    //合并单元格
    $objActSheet->mergeCells('A' . $rowNum.':E' . $rowNum);


    //第n+2行
    $rowNum += 1;
    $objActSheet->setCellValue('A' . $rowNum, '配送费');
    $objActSheet->setCellValue('E' . $rowNum, '长途物流费');
    //合并单元格
    $objActSheet->mergeCells('A' . $rowNum.':B' . $rowNum);
    $objActSheet->mergeCells('C' . $rowNum.':D' . $rowNum);
    $objActSheet->mergeCells('F' . $rowNum.':I' . $rowNum);



    //第n+3行
    $rowNum += 1;
    $objActSheet->setCellValue('A' . $rowNum, '安装费');
    $objActSheet->setCellValue('E' . $rowNum, '远程费');
    //合并单元格
    $objActSheet->mergeCells('A' . $rowNum.':B' . $rowNum);
    $objActSheet->mergeCells('C' . $rowNum.':D' . $rowNum);

    //第n+4行
    $rowNum += 1;
    $objActSheet->setCellValue('A' . $rowNum, '特殊费用');
    $objActSheet->setCellValue('E' . $rowNum, '其 他');
    //合并单元格
    $objActSheet->mergeCells('A' . $rowNum.':B' . $rowNum);
    $objActSheet->mergeCells('C' . $rowNum.':D' . $rowNum);
    $objActSheet->mergeCells('F' . $rowNum.':I' . $rowNum);

    //第n+5行
    $rowNum += 1;
    $objActSheet->setCellValue('A' . $rowNum, '总    计');
    //合并单元格
    $objActSheet->mergeCells('A' . $rowNum.':B' . $rowNum);
    $objActSheet->mergeCells('C' . $rowNum.':I' . $rowNum);

    //第n+6行
    $rowNum += 1;
    $objActSheet->setCellValue('A' . $rowNum, '交付中心 确认');
    //合并单元格
    $objActSheet->mergeCells('A' . $rowNum.':B' . $rowNum);
    $objActSheet->mergeCells('C' . $rowNum.':E' . $rowNum);
    $objActSheet->mergeCells('F' . $rowNum.':I' . $rowNum);

    //第n+7行
    $rowNum += 1;
    $objActSheet->setCellValue('A' . $rowNum, '客户签字（很重要）');
    $objActSheet->setCellValue('C' . $rowNum, '2019年      月      日');

    //合并单元格
    $objActSheet->mergeCells('A' . $rowNum.':B' . $rowNum);
    $objActSheet->mergeCells('C' . $rowNum.':I' . $rowNum);
    $objPHPExcel->getActiveSheet()->getRowDimension($rowNum)->setRowHeight(40);



//    echo $rowNum;die;
    //设置宽width
    // Set column widths
    $objActSheet->getColumnDimension('A')->setWidth('12');
    $objActSheet->getColumnDimension('B')->setWidth('18');
    $objActSheet->getColumnDimension('C')->setWidth('25');
    $objActSheet->getColumnDimension('D')->setWidth('15');
    $objActSheet->getColumnDimension('E')->setWidth('15');
    $objActSheet->getColumnDimension('F')->setWidth('10');
    $objActSheet->getColumnDimension('G')->setWidth('10');
    $objActSheet->getColumnDimension('H')->setWidth('10');
    $objActSheet->getColumnDimension('I')->setWidth('18');



    // 设置单元格高度
    // 所有单元格默认高度
    $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(25);
    // 第一行的默认高度
    $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(40);
    //边框
    $style_array = array(
        'borders' => array(
            'allborders' => array(
                'style' => \PHPExcel_Style_Border::BORDER_THIN,
            ),
        ));
    //居中
    //设置全部单元格水平/垂直居中
    for ($i =0;$i<=$rowNum;$i++){
        $objActSheet->getStyle('A'.$i.':I' .$i)->getAlignment()->applyFromArray(array(
            'horizontal' => 'center',
            'vertical' => 'center',
        ));
        //边框
        $objActSheet->getStyle("A1:I".$rowNum )->applyFromArray($style_array);
        //内容过长自动换行
        $objPHPExcel->getActiveSheet()->getStyle('A1:I' . $i)->getAlignment()->setWrapText(true);
    }
    $objActSheet->getStyle('C'.$rowNum.':I' .$rowNum)->getAlignment()->applyFromArray(array(
        'horizontal' => 'right',
        'vertical' => 'bottom',
    ));

    ob_end_clean();
    $filename = str_replace(array("-", " ", "~", "!", "@", "#", "$", "%", "^", "&", "(", ")", "+", ",", " （", "）", "？", "！", "《", "》", "：", "；", "——"), '', $fileName) . '.xlsx'; //删除文件名非法字符
    //$filename = iconv("utf-8", "gb2312", $filename);
    $objPHPExcel->setActiveSheetIndex(0);
    ob_end_clean(); //清除缓冲区,避免乱码
    header('pragma:public');
    header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . $filename . '.xls"');
    header("Content-Disposition:attachment;filename=$fileName.xls"); // attachment新窗口打印inline本窗口打印
    $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter->save('php://output');
    exit;

}

设置excel名称

   $objPHPExcel->getProperties()->setTitle($xlsName); //标题
    $objPHPExcel->getProperties()->setSubject($xlsName); //题目

//导出订单
function orderDataFactoryExcel($xlsName, $list) {
    include './data/extend/phpexcel_classes/PHPExcel.php';
    //xldebug($list);
    $fileName = $xlsName . '-' . date('_YmdHis');
    $objPHPExcel = new \PHPExcel();

    //设置文档属性
    $objPHPExcel->getProperties()->setTitle($xlsName); //标题
    $objPHPExcel->getProperties()->setSubject($xlsName); //题目
    $columnNumber = 15;
    $sheetindex = 0; //当前工作表编号

    //供应商列表
    foreach ($list as $k => $v) {

        //xldebug($v);
        $objPHPExcel->createSheet();

        $objPHPExcel->setActiveSheetIndex($sheetindex); //设置当前的sheet
        $objActSheet = $objPHPExcel->getActiveSheet();
        $sheetTitle = $v['supplier_no'];
        $objActSheet->setTitle($sheetTitle);

        //第一行
        // 图片生成
        $objDrawing[$k] = new \PHPExcel_Worksheet_Drawing();
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(51.5);
        $objDrawing[$k]->setPath('./static/v1/images/logo_164x34.png');
        $objDrawing[$k]->setWidth(164); //照片宽度
        $objDrawing[$k]->setHeight(44); //照片高度
        $objDrawing[$k]->setCoordinates('A1'); //设置图片要插入的单元格
        $objDrawing[$k]->setOffsetX(0);
        $objDrawing[$k]->setOffsetY(0);
        $objDrawing[$k]->setWorksheet($objActSheet);
        unset($objDrawing[$k]);

        $objActSheet->mergeCells('A1:C1');
        $bigtitle = $v['supplier_no'] . '-' . ' 下单表';
        $objActSheet->setCellValue('D1', $bigtitle);
        $objActSheet->mergeCells('D' . '1' . ':J' . '1');
        $order_num = 1;
        $rowNum = 0;
        //工厂列表
        foreach ($v['list'] as $ok => $ov) {

            //第二行
            $rowNum +=2;
            $objActSheet->setCellValue('A' . $rowNum, htmlspecialchars_decode($ov['receiver_addr']) . '-' . $ov['receiver_name'] . '-' . $ov['receiver_mobile']);

            $objActSheet->getStyle('A'.$rowNum)->applyFromArray(
                array(
                    'fill' => array(
                        'type' => PHPExcel_Style_Fill::FILL_SOLID,
                        'color' => array('rgb' => 'CFCFCF'),

                    )
                )
            );
            $objActSheet->mergeCells('A' . $rowNum . ':J' . $rowNum);

            //第三行
            $rowNum +=1 ;
            $objActSheet->setCellValue('A' . $rowNum, '出货时间：');
            $objActSheet->setCellValue('B' . $rowNum, '');
            $objActSheet->setCellValue('C' . $rowNum, '物流信息：');
            $objActSheet->setCellValue('D' . $rowNum, '');

            $objActSheet->mergeCells('D' . $rowNum . ':J' . $rowNum);

            //第四行
            $rowNum +=1 ;

            $objActSheet->setCellValue('A' . $rowNum, '下单时间：');
            $objActSheet->setCellValue('B' . $rowNum, date('Y-m-d H', $ov['create_time']));
            $objActSheet->setCellValue('C' . $rowNum, '客户地址：');
            $objActSheet->setCellValue('D' . $rowNum, $ov['receiver_address']);

            $objActSheet->mergeCells('D' . $rowNum . ':J' . $rowNum);
            //第五行
            $rowNum +=1 ;

            $objActSheet->setCellValue('A' . $rowNum, '序号');
            $objActSheet->setCellValue('B' . $rowNum, '产品图片');
            $objActSheet->setCellValue('C' . $rowNum, '品名');
            $objActSheet->setCellValue('D' . $rowNum, '型号');
            $objActSheet->setCellValue('E' . $rowNum, '规格/尺寸');
            $objActSheet->setCellValue('F' . $rowNum, '选项');
            $objActSheet->setCellValue('G' . $rowNum, '单价');
            $objActSheet->setCellValue('H' . $rowNum, '数量');
            $objActSheet->setCellValue('I' . $rowNum, '小计');
            $objActSheet->setCellValue('J' . $rowNum, '备注');

            //商品列表
            $goods_num = 0;
            $goods_order['start']=$goods_order['end'] = $rowNum;
            foreach ($ov['list'] as $gk => $v1) {
                ++$goods_order['end'];
//              dump($v1['goods_picture']);die;
                $rowNum += 1;
                    $xh = $goods_num + 1;
                    $objActSheet->setCellValue('A' . $rowNum, $xh); //序号
                    $objActSheet->setCellValue('B' . $rowNum, $v1['space']); //空间
                    //$v1['picture']['pic_cover_micro'] = './static/v1/pic/demo.jpg';
                    //xldebug($v1['picture']['pic_cover_micro']);
                    if (file_exists($v1['pic_cover_small'])) {
                        $k2 = $k . '_' . $k1;
                        $objDrawing[$k2] = new \PHPExcel_Worksheet_Drawing();
                        $objDrawing[$k2]->setPath($v1['pic_cover_small']);
                        $objDrawing[$k2]->setWidthAndHeight(80, 60); //照片宽度
                        //$objDrawing[$k2]->setHeight(43);//照片高度
                        /*设置图片要插入的单元格*/
                        $objDrawing[$k2]->setCoordinates('B' . $rowNum);
                        // 图片偏移距离
                        $objDrawing[$k2]->setOffsetX(18);
                        $objDrawing[$k2]->setOffsetY(5);
                        $objDrawing[$k2]->setWorksheet($objActSheet);
                        unset($objDrawing[$k2]);
                    }

                    //$objActSheet->setCellValue('C'.$rowNum, '图样');
                    //$total = $v1['sell_price']*$v1['num'];
                    $objActSheet->setCellValue('C' . $rowNum, $v1['goods_name']); //品名
                    $objActSheet->setCellValue('D' . $rowNum, $v1['goods_sn']); //型号
                    $objActSheet->setCellValue('E' . $rowNum, $v1['attr_value_name']); //规格/尺寸
                    $objActSheet->setCellValue('F' . $rowNum, $v1['sku_name']); //选项
                    $objActSheet->setCellValue('G' . $rowNum, $v1['price']); //单价
                    $objActSheet->setCellValue('H' . $rowNum, $v1['num']); //数量
                    $objActSheet->setCellValue('I' . $rowNum, $v1['goods_money']); //小计
                    $objActSheet->setCellValue('J' . $rowNum, ''); //备注

                    $objActSheet->getRowDimension($rowNum)->setRowHeight(52);

                //设置全部单元格水平/垂直居中
                $objActSheet->getStyle('A1:J' . $rowNum)->getAlignment()->applyFromArray(array(
                    'horizontal' => 'center',
                    'vertical' => 'center',
                ));
                $goods_num++;
            }
            //合计
             $goods_order['end']++;
            $objActSheet->setCellValue('I'. $goods_order['end'], '=SUM(I'. $goods_order['start'].':I'. $goods_order['end'].')');

            $objActSheet->setCellValue('A' . $goods_order['end'], '合计');
//          $objActSheet->setCellValue('I' . $goods_order['end'], $count);
            $objActSheet->setCellValue('J' . $goods_order['end'], '');
            $objActSheet->mergeCells('A' . $goods_order['end'] . ':H' .$goods_order['end']);

            //设置边框
            $styleArray = array(
                'borders' => array(
                    'allborders' => array(
                        'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框
                    ),
                ),
            );
            $objActSheet->getStyle('A'.$goods_order['start'].':J'. $goods_order['end'])->applyFromArray($styleArray);

            $rowNum = $goods_order['end']+1;

            //空行
            $objActSheet->setCellValue('A' . $rowNum, '');
            $objActSheet->mergeCells('A' . $rowNum . ':J' .$rowNum);
            $objActSheet->getRowDimension($rowNum)->setRowHeight(50);
            $order_num++;


        }
        $rowNum = 0;
        //第五行
        $order_num=0;
        //设置全部单元格水平/垂直居中
        $objActSheet->getStyle('A1'.':J' . $rowNum)->getAlignment()->applyFromArray(array(
            'horizontal' => 'center',
            'vertical' => 'center',
        ));

            $objActSheet->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objActSheet->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);


            //设置字体/大小/样式
            $objActSheet->getStyle('A'.$rowNum.':J' . $rowNum)->getFont()->setName('宋体');
            $objActSheet->getStyle('D1')->getFont()->setSize(16);
            $objActSheet->getStyle('D1')->getFont()->setBold(true);
            $objActSheet->getStyle('A'.$rowNum.':J' . $rowNum)->getFont()->setSize(10);

            $objActSheet->getDefaultRowDimension()->setRowHeight(26);
            $objActSheet->getColumnDimension('A')->setWidth('12.6');
            $objActSheet->getColumnDimension('B')->setWidth('16.1');
            $objActSheet->getColumnDimension('C')->setWidth('20.6');
            $objActSheet->getColumnDimension('D')->setWidth('14');
            $objActSheet->getColumnDimension('E')->setWidth('18.6');
            $objActSheet->getColumnDimension('F')->setWidth('13.6');
            $objActSheet->getColumnDimension('G')->setWidth('17.6');
            $objActSheet->getColumnDimension('H')->setWidth('17.8');
            $objActSheet->getColumnDimension('I')->setWidth('18.2');
            $objActSheet->getColumnDimension('J')->setWidth('18.3');

            // 设置边距为1厘米 (1英寸 = 2.54厘米)
            $margin = 1 / 2.54; //phpexcel 中是按英寸来计算的,所以这里换算了一下
            //$objActSheet->getPageMargins()->setTop($margin);       //上边距
            //$objActSheet->getPageMargins()->setBottom($margin); //下
            $objActSheet->getPageMargins()->setLeft($margin); //左
            $objActSheet->getPageMargins()->setRight($margin); //右

            $sheetindex += 1;

        }


    ob_end_clean();
    $filename = str_replace(array("-", " ", "~", "!", "@", "#", "$", "%", "^", "&", "(", ")", "+", ",", " （", "）", "？", "！", "《", "》", "：", "；", "——"), '', $fileName) . '.xlsx'; //删除文件名非法字符
    //$filename = iconv("utf-8", "gb2312", $filename);
    $objPHPExcel->setActiveSheetIndex(0);
    ob_end_clean(); //清除缓冲区,避免乱码
    header('pragma:public');
    header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . $filename . '.xls"');
    header("Content-Disposition:attachment;filename=$fileName.xls"); // attachment新窗口打印inline本窗口打印
    $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter->save('php://output');
    exit;

}