//导出配送安装
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;
}
