phpexcel 產生報表

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; } 

?

