phpexcel 生成表格
/* * 导出excel表格 * 根据统计标题信息、日期信息和excel表格标示信息 * 先循环统计标题信息在循环每条统计下面按日期的具体信息 * 进行单元格设置 合并 添加样式 */ function _export($tipMsg = null, $date = null, $excelTitle = null){ $this->autoRender = false; App::import('Vendor', 'phpexcel', array('file' => 'PHPExcel.php')); App::import('Vendor', 'phpexcelwriter', array('file' => 'PHPExcel'.DS.'Writer'.DS.'Excel2007.php')); // loads PHPExcel/Writer/Excel2007.php $objPHPExcel = new PHPExcel(); $excelName = 'speiyou_'.date('y-m-d H:i:s').'.xls';//文件名字 // 设置属性 $objPHPExcel->getProperties()->setCreator("Maarten Balliauw") ->setLastModifiedBy("Maarten Balliauw") ->setTitle("Office 2007 XLSX Test Document") ->setSubject("Office 2007 XLSX Test Document") ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.") ->setKeywords("office 2007 openxml php") ->setCategory("Test result file"); //样式声明 $objActSheet = $objPHPExcel->getActiveSheet(); //设置样式字体 $sharedStyle1 = new PHPExcel_Style(); $sharedStyle1->applyFromArray( array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('argb' => 'FFCCFFCC')), 'borders' => array('bottom'=> array('style' => PHPExcel_Style_Border::BORDER_THIN), 'right'=> array('style' => PHPExcel_Style_Border::BORDER_THIN), 'top' => array('style' => PHPExcel_Style_Border::BORDER_THIN), 'left' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'font' => array('bold' => true,'color'=>array('argb' => '00000000')), )); // 添加头部数据 $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A1', '模块分类') ->setCellValue('B1', '点击代码') ->setCellValue('C1', '代码名称'); foreach ($date as $tdk => $tdv) { $objPHPExcel->setActiveSheetIndex(0) ->setCellValue($excelTitle[$tdk].'1', $date[$tdk]); } /*添加主要内容 * 分类处理 tou(头部导航) banji(班级分类) fonepage(首页一屏) ftwopage(首页二屏) fthreepage(首页三屏) * bottom(底部) teacher(名师) ad(广告) */ $num = 2;//循环开始标记位 $datenum = count($date) - 1; foreach($tipMsg as $k => $v) { if ($k == 'tou') { $tounum = $num + 1; $newnum = $num-1; $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A2','头部导航'); //合并单元格 $objActSheet->mergeCells('A'.$num.':C'.$num); $objActSheet->mergeCells('A2'.':A'.$newnum); $objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A2:A".$newnum); $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A'.$num, '本类汇总'); $objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A".$num.":".$excelTitle[count($date) - 1].$num); $objActSheet->getStyle('A'.$num)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //求和 foreach($date as $datek => $datev) { $objPHPExcel->setActiveSheetIndex(0) ->setCellValue($excelTitle[$datek].$num,'=SUM('.$excelTitle[$datek].'2:'.$excelTitle[$datek].$newnum.')'); } } elseif ($k == 'banji') { $banjinum = $num + 1; $newnum = $num-1; $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A'.$tounum,'班级分类'); //合并单元格 $objActSheet->mergeCells('A'.$num.':C'.$num); $objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A".$tounum.":A".$newnum); $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A'.$num, '本类汇总'); $objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A".$num.":".$excelTitle[count($date) - 1].$num); $objActSheet->getStyle('A'.$num)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //求和 foreach($date as $datek => $datev) { $objPHPExcel->setActiveSheetIndex(0) ->setCellValue($excelTitle[$datek].$num,'=SUM('.$excelTitle[$datek].$tounum.':'.$excelTitle[$datek].$newnum.')'); } } elseif ($k == 'fonepage') { $fonepagenum = $num + 1; $newnum = $num-1; $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A'.$banjinum,'首页一屏'); //合并单元格 $objActSheet->mergeCells('A'.$num.':C'.$num); $objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A".$banjinum.":A".$newnum); $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A'.$num, '本类汇总'); $objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A".$num.":".$excelTitle[count($date) - 1].$num); $objActSheet->getStyle('A'.$num)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //求和 foreach($date as $datek => $datev) { $objPHPExcel->setActiveSheetIndex(0) ->setCellValue($excelTitle[$datek].$num,'=SUM('.$excelTitle[$datek].$banjinum.':'.$excelTitle[$datek].$newnum.')'); } } elseif ($k == 'ftwopage') { $ftwopagenum = $num + 1; $newnum = $num-1; $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A'.$fonepagenum,'首页二屏'); //合并单元格 $objActSheet->mergeCells('A'.$num.':C'.$num); $objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A".$fonepagenum.":A".$newnum); $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A'.$num, '本类汇总'); $objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A".$num.":".$excelTitle[count($date) - 1].$num); $objActSheet->getStyle('A'.$num)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //求和 foreach($date as $datek => $datev) { $objPHPExcel->setActiveSheetIndex(0) ->setCellValue($excelTitle[$datek].$num,'=SUM('.$excelTitle[$datek].$fonepagenum.':'.$excelTitle[$datek].$newnum.')'); } } elseif ($k == 'fthreepage') { $fthreepage = $num + 1; $newnum = $num-1; $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A'.$ftwopagenum,'首页三屏'); //合并单元格 $objActSheet->mergeCells('A'.$num.':C'.$num); $objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A".$ftwopagenum.":A".$newnum); $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A'.$num, '本类汇总'); $objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A".$num.":".$excelTitle[count($date) - 1].$num); $objActSheet->getStyle('A'.$num)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //求和 foreach($date as $datek => $datev) { $objPHPExcel->setActiveSheetIndex(0) ->setCellValue($excelTitle[$datek].$num,'=SUM('.$excelTitle[$datek].$ftwopagenum.':'.$excelTitle[$datek].$newnum.')'); } } elseif ($k == 'bottom') { $bottomnum = $num + 1; $newnum = $num-1; $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A'.$fthreepage,'底部'); //合并单元格 $objActSheet->mergeCells('A'.$num.':C'.$num); $objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A".$fthreepage.":A".$newnum); $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A'.$num, '本类汇总'); $objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A".$num.":".$excelTitle[count($date) - 1].$num); $objActSheet->getStyle('A'.$num)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //求和 foreach($date as $datek => $datev) { $objPHPExcel->setActiveSheetIndex(0) ->setCellValue($excelTitle[$datek].$num,'=SUM('.$excelTitle[$datek].$fthreepage.':'.$excelTitle[$datek].$newnum.')'); } } elseif ($k == 'teacher') { $teachernum = $num + 1; $newnum = $num-1; $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A'.$bottomnum,'名师频道'); //合并单元格 $objActSheet->mergeCells('A'.$num.':C'.$num); $objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A".$bottomnum.":A".$newnum); $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A'.$num, '本类汇总'); $objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A".$num.":".$excelTitle[count($date) - 1].$num); $objActSheet->getStyle('A'.$num)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //求和 foreach($date as $datek => $datev) { $objPHPExcel->setActiveSheetIndex(0) ->setCellValue($excelTitle[$datek].$num,'=SUM('.$excelTitle[$datek].$bottomnum.':'.$excelTitle[$datek].$newnum.')'); } } elseif ($k == 'ad') { $adnum = $num + 1; $newnum = $num-1; $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A'.$teachernum,'通栏广告'); //合并单元格 $objActSheet->mergeCells('A'.$num.':C'.$num); $objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A".$teachernum.":A".$newnum); $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A'.$num, '本类汇总'); $objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A".$num.":".$excelTitle[count($date) - 1].$num); $objActSheet->getStyle('A'.$num)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //求和 foreach($date as $datek => $datev) { $objPHPExcel->setActiveSheetIndex(0) ->setCellValue($excelTitle[$datek].$num,'=SUM('.$excelTitle[$datek].$teachernum.':'.$excelTitle[$datek].$newnum.')'); } } else { $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A'.$num, '') ->setCellValue('B'.$num, $v['codetip']) ->setCellValue('C'.$num, $v['codename']); foreach($date as $tdk => $tdv) { $objPHPExcel->setActiveSheetIndex(0) ->setCellValue($excelTitle[$tdk].$num, $v['nums'][$tdk]); } } ++$num; } //添加样式 $objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A1:".$excelTitle[count($date) - 1]."1");// $objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A1:A".$num); //冻结列 $objPHPExcel->getActiveSheet()->freezePane('A1'); $objPHPExcel->getActiveSheet()->freezePane('B1'); $objPHPExcel->getActiveSheet()->freezePane('C1'); $objPHPExcel->getActiveSheet()->freezePane('D2'); //设置居中 $objActSheet->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objActSheet->getStyle('A'.$num)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //设置列宽 $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20); //设置底部总数统计信息 $ttotal = $tounum - 1; $banjitotal = $banjinum - 1; $fototal = $fonepagenum - 1; $fttotal = $ftwopagenum - 1; $frtotal = $fthreepage - 1; $btotal = $bottomnum - 1; $chtotal = $teachernum - 1; $adtotal = $adnum - 1; $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A'.$num, '总计') ->setCellValue('B'.$num, '') ->setCellValue('C'.$num, '日统计'); foreach ($date as $totalk => $totalv) { $objPHPExcel->setActiveSheetIndex(0) ->setCellValue($excelTitle[$totalk].$num, '=SUM('.$excelTitle[$totalk].$ttotal.','.$excelTitle[$totalk].$banjitotal.','.$excelTitle[$totalk].$fototal.','.$excelTitle[$totalk].$fttotal.','.$excelTitle[$totalk].$frtotal.','.$excelTitle[$totalk].$btotal.','.$excelTitle[$totalk].$chtotal.','.$excelTitle[$totalk].$adtotal.')'); } $objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, 'A'.$num.':'.$excelTitle[$datenum].$num); $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('B'.$num, '=SUM(D'.$num.':'.$excelTitle[$datenum].$num.')'); $objActSheet->getStyle('A'.$num)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objActSheet->getStyle('C'.$num)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); // 设置切换标签的名字 $objPHPExcel->getActiveSheet()->setTitle('培优网用户点击统计'); //直接输出到浏览器 header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="'.$excelName.'"'); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save('php://output'); exit; } 
HTML space setting method
IIS unexpected error 0x8ffe2740 solution
Implementation method of js barrage function
A complete list of linux server operation and maintenance commands
C language random function usage
What is the mobile service password?
How to lock screen on oppo11
How to adjust computer screen brightness
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
