马上注册,免费下载更多dz插件网资源。
您需要 登录 才可以下载或查看,没有账号?立即注册
×
需求:运营需要实现导出上面报表,格式为excel,方便作二次统计。
一看到这个报表当场就懵了,又是行合并,又是列合并,又是背景色。HTML页面的话,还好说。但导出报表为Excel就……唉,老板的需求,硬着头皮也要完工。
接招吧,码农:- 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, [1,3,7, 8, 15, 16, 17]) ? 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();
复制代码
唉,终于可以小小的休息一下了,明天继续加班。
©DZ插件网所发布的一切资源仅限用于学习和研究目的;不得将上述内容用于商业或者非法用途,否则,一切后果请用户自负。 网站部分内容来源于网络,版权争议与本站无关。您必须在下载后的24个小时之内,从您的电脑中彻底删除上述内容。如果您喜欢该程序,请支持正版软件,得到更好的正版服务。 您在本站任何的赞助购买、下载、查阅、回复等行为等均表示接受并同意签订《DZ插件网免责声明协议》。 如有侵权请邮件与我们联系处理: discuzaddons@vip.qq.com 并出示相关证明以便删除。敬请谅解!
|
|