PHPExcel 行合并、列合并、设置背景颜色实战
<p><img alt="PHPExcel-colmerge-linemerge.png" src="data/attachment/forum/202302/23/2023-02-23_18-48-56_664.png" style="width:1100px" title="1488536779796994.png" /></p><p>需求：运营需要实现导出上面报表，格式为excel，方便作二次统计。</p>
<p>一看到这个报表当场就懵了，又是行合并，又是列合并，又是背景色。HTML页面的话，还好说。但导出报表为Excel就……唉，老板的需求，硬着头皮也要完工。</p>
<p> </p>
<p>接招吧，码农：</p>
<pre>
import('ORG.phpexcel.PHPExcel', '', '.php');
$objPHPExcel = new PHPExcel();
$objWorksheet = $objPHPExcel->getActiveSheet();
$objWorksheet->setTitle('群组数据'); //给当前sheet设置名称
//设置列宽
$istart = ord('A') - 65;
$iend = ord('R') - 65;
for($i = $istart; $i <= $iend; $i++){
$col_width = in_array($i, ) ? 19 : 11;
$objWorksheet->getColumnDimension( chr(ord('A') + $i) )->setWidth($col_width);//setAutoSize(true)不起作用
}
$hcenter = PHPExcel_Style_Alignment::HORIZONTAL_CENTER;//水平居中
$vcenter = PHPExcel_Style_Alignment::VERTICAL_CENTER;//上下居中
//搜索条件：
$objWorksheet->setCellValue('A1', '群组ID');
$objWorksheet->setCellValue('B1', $search['group_id'])->getStyle('B1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('f8cbad');
$objWorksheet->setCellValue('D1', '数据更新完成时间');
$objWorksheet->setCellValue('E1', $search['group_time'])->getStyle('E1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('f8cbad');
$objWorksheet->setCellValue('G1', '选取时间');
$objWorksheet->setCellValue('H1', $search['start_time'])->getStyle('H1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('f8cbad');
$objWorksheet->setCellValue('I1', $search['end_time'])->getStyle('I1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('f8cbad');
$objWorksheet->setCellValue('L1', '优惠券ID');
$objWorksheet->setCellValue('M1', $search['coupon_id'])->getStyle('M1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('f8cbad');
//数据表头
$bt = ['群组ID', '数据更新完成时间', '总人数', '回访人数', '用券人数', '订单量', '销售额', '使用券的销售额', '购买人数','订单价', '平均回访天数', '平均购买天数'];//
$bt_sub = ['0-20', '21-50', '51-70', '71-100', '101-200', '200以上'];
foreach($bt as $k => $v) {
$col = chr(65 + $k);
if($v == '平均回访天数'){
$col = 'P';
}elseif($v == '平均购买天数'){
$col = 'Q';
}
$mergeLine = $col.'3:'.$col.'4';
$celV = $col.'3';
$mergeCol = 'K3:O3';
if($v == '订单价'){
$objWorksheet->mergeCells($mergeCol)->setCellValue('K3', $v)->getStyle('K3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('f8cbad');
$objWorksheet->getStyle('K3')->getAlignment()->setHorizontal($hcenter);
foreach($bt_sub as $kk => $vv){
$cel = chr(ord('K') + $kk) . '4';
$objWorksheet->setCellValue($cel, $vv)->getStyle($cel)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('fce4d6');
$objWorksheet->getStyle($cel)->getAlignment()->setHorizontal($hcenter);
}
}
$objWorksheet->mergeCells($mergeLine)->setCellValue($celV, $v)->getStyle($celV)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('f8cbad');
$objWorksheet->getStyle($celV)->getAlignment()->setHorizontal($hcenter);
$objWorksheet->getStyle($celV)->getAlignment()->setVertical($vcenter);
}
//数据体
foreach($datalist as $k => $v){
$lineno3 = ($k == 0 ? 5 : ($k + 5));
$objWorksheet->setCellValue(chr(ord('A')).$lineno3, $v['qun_id']);
$objWorksheet->setCellValue(chr(ord('B')).$lineno3, $v['update_time']);
$objWorksheet->setCellValue(chr(ord('C')).$lineno3, $v['members']);
$objWorksheet->setCellValue(chr(ord('D')).$lineno3, $v['view_nums']);
$objWorksheet->setCellValue(chr(ord('E')).$lineno3, $v['coupon_user']);
$objWorksheet->setCellValue(chr(ord('F')).$lineno3, $v['orders']);
$objWorksheet->setCellValue(chr(ord('G')).$lineno3, $v['sales_money']);
$objWorksheet->setCellValue(chr(ord('H')).$lineno3, $v['coupon_money']);
$objWorksheet->setCellValue(chr(ord('I')).$lineno3, $v['buyers']);
$objWorksheet->setCellValue(chr(ord('J')).$lineno3, $v['buyer_0_20']);
$objWorksheet->setCellValue(chr(ord('K')).$lineno3, $v['buyer_21_50']);
$objWorksheet->setCellValue(chr(ord('L')).$lineno3, $v['buyer_51_70']);
$objWorksheet->setCellValue(chr(ord('M')).$lineno3, $v['buyer_71_100']);
$objWorksheet->setCellValue(chr(ord('N')).$lineno3, $v['buyer_101_200']);
$objWorksheet->setCellValue(chr(ord('O')).$lineno3, $v['buyer_200_']);
$objWorksheet->setCellValue(chr(ord('P')).$lineno3, $v['view_days_avg']);
$objWorksheet->setCellValue(chr(ord('Q')).$lineno3, $v['buy_days_avg']);
}
$filename = $sheet_title.".xlsx";
$ua = $_SERVER["HTTP_USER_AGENT"];
$encoded_filename = urlencode($filename);
$encoded_filename = str_replace("+", "%20",$encoded_filename);
header('Content-Type: application/octet-stream');
if (preg_match("/MSIE/", $ua)) {
$filename = $encoded_filename;
header('Content-Disposition: attachment;filename="' .$filename . '"');
}else if (preg_match("/Firefox/", $ua)){
header('Content-Disposition: attachment; filename*="utf8\'\'' . $filename . '"');
}else {
header('Content-Disposition: attachment; filename="' . $filename . '"');
}
////////////////////////////////////////
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');
// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit();</pre>
<p> </p>
<p>唉，终于可以小小的休息一下了，明天继续加班。</p>
页:
[1]