Phpexcel Generating Reports

Phpexcel Generating Tables

/* * Export Excel Table * According to statistics header information, date information and Excel table information * First cycle statistics header information in the loop below each statistic by date specific information * Cell settings merge Add Style * * 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 ';//file name//Set Property $objphpexcel->getproperties ()->setcreator ("Maarten Balliauw")->setlastmodifiedby (" Maarten Balliauw ")->settitle (Office" xlsx Test Document ")->setsubject (Office" xlsx Test Document ") SetDescription ("Test document for Office" XLSX, generated using PHP classes. ")->setkeywords (" Office OPENXML p HP ")->setcategory (" Test result file ");//Style Declaration $objactsheet = $objPHPExcel->getactivesheet ();//Set style font $ SharedStyle1 = new Phpexcel_style (); $sharedStyle 1->apPlyfromarray (' 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 ')));//Add Header Data $objphpexcel->setactivesheetindex (0)->setc Ellvalue (' A1 ', ' module classification ')->setcellvalue (' B1 ', ' click Code ')->setcellvalue (' C1 ', ' Code name '); foreach ($date as $TDK = + $TDV) {$objPHPExcel->setactivesheetindex (0)->setcellvalue ($excelTitle [$TDK]. ' 1 ', $date [$TDK]);  }/* Add main content * Classification processing tou (head navigation) Banji (class category) Fonepage (first screen) Ftwopage (home two screen) Fthreepage (first three screen) * Bottom (bottom) teacher (teacher) AD (AD) */$num = 2;//loop start tag bit $datenum = CounT ($date)-1; foreach ($tipMsg as $k = + $v) {if ($k = = ' tou ') {$tounum = $num + 1; $newnum = $num-1; $objPHPExcel->setactivesheeti Ndex (0)->setcellvalue (' A2 ', ' head navigation '); Merge cells $objActSheet->mergecells (' A '. $num. ': C '. $num); $objActSheet->mergecells (' A2 '. ': A '. $newnum); $objPHPExcel->getactivesheet ()->setsharedstyle ($sharedStyle 1, "a2:a". $newnum); $objPHPExcel->setactivesheetindex (0)->setcellvalue (' A ' $num, ' summary of this category '); $objPHPExcel->getactivesheet ()->setsharedstyle ($sharedStyle 1, "A". $num. ":". $excelTitle [Count ($date)-1].$ NUM); $objActSheet->getstyle (' A '. $num)->getalignment ()->sethorizontal (Phpexcel_style_alignment::horizontal_ CENTER); Sum 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)->setcellvalu E (' A'. $tounum, ' class classification '); Merge cells $objActSheet->mergecells (' A '. $num. ': C '. $num); $objPHPExcel->getactivesheet ()->setsharedstyle ($sharedStyle 1, "a". $tounum. ": a". $newnum); $objPHPExcel->setactivesheetindex (0)->setcellvalue (' A ' $num, ' summary of this category '); $objPHPExcel->getactivesheet ()->setsharedstyle ($sharedStyle 1, "A". $num. ":". $excelTitle [Count ($date)-1].$ NUM); $objActSheet->getstyle (' A '. $num)->getalignment ()->sethorizontal (Phpexcel_style_alignment::horizontal_ CENTER); Sum 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)->setce Llvalue (' a '. $banjinum, ' home one Screen '); Merge cells $objActSheet->mergecells (' A '. $num. ': C '. $num);  $objPHPExcel->getactivesheet ()->setsharedstyle ($sharedStyle 1, "a". $banjinum. ": a". $newnum); $objPHPexcel->setactivesheetindex (0)->setcellvalue (' A '. $num, ' Summary of this category '); $objPHPExcel->getactivesheet ()->setsharedstyle ($sharedStyle 1, "A". $num. ":". $excelTitle [Count ($date)-1].$ NUM); $objActSheet->getstyle (' A '. $num)->getalignment ()->sethorizontal (Phpexcel_style_alignment::horizontal_ CENTER); Sum 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)->setce Llvalue (' A '. $fonepagenum, ' home two screen '); Merge cells $objActSheet->mergecells (' A '. $num. ': C '. $num);  $objPHPExcel->getactivesheet ()->setsharedstyle ($sharedStyle 1, "a". $fonepagenum. ": a". $newnum); $objPHPExcel->setactivesheetindex (0)->setcellvalue (' A ' $num, ' summary of this category '); $objPHPExcel->getactivesheet ()->setsharedstyle ($sharedStyle 1, "A". $num. ":". $excelTitle [Count($date)-1]. $num); $objActSheet->getstyle (' A '. $num)->getalignment ()->sethorizontal (Phpexcel_style_alignment::horizontal_ CENTER); Sum 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)->setc Ellvalue (' A '. $ftwopagenum, ' first three screens '); Merge cells $objActSheet->mergecells (' A '. $num. ': C '. $num);  $objPHPExcel->getactivesheet ()->setsharedstyle ($sharedStyle 1, "a". $ftwopagenum. ": a". $newnum); $objPHPExcel->setactivesheetindex (0)->setcellvalue (' A ' $num, ' summary of this category '); $objPHPExcel->getactivesheet ()->setsharedstyle ($sharedStyle 1, "A". $num. ":". $excelTitle [Count ($date)-1].$ NUM); $objActSheet->getstyle (' A '. $num)->getalignment ()->sethorizontal (Phpexcel_style_alignment::horizontal_ CENTER); Sum 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)->setcellva Lue (' A '. $fthreepage, ' bottom '); Merge cells $objActSheet->mergecells (' A '. $num. ': C '. $num);  $objPHPExcel->getactivesheet ()->setsharedstyle ($sharedStyle 1, "a". $fthreepage. ": a". $newnum); $objPHPExcel->setactivesheetindex (0)->setcellvalue (' A ' $num, ' summary of this category '); $objPHPExcel->getactivesheet ()->setsharedstyle ($sharedStyle 1, "A". $num. ":". $excelTitle [Count ($date)-1].$ NUM); $objActSheet->getstyle (' A '. $num)->getalignment ()->sethorizontal (Phpexcel_style_alignment::horizontal_ CENTER); Sum 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)->setcell Value (' A '. $bottomnum, ' teacher Channel '); Merge cells $objActSheet->mergecells (' A '. $num. ': C '. $num);  $objPHPExcel->getactivesheet ()->setsharedstyle ($sharedStyle 1, "a". $bottomnum. ": a". $newnum); $objPHPExcel->setactivesheetindex (0)->setcellvalue (' A ' $num, ' summary of this category '); $objPHPExcel->getactivesheet ()->setsharedstyle ($sharedStyle 1, "A". $num. ":". $excelTitle [Count ($date)-1].$ NUM); $objActSheet->getstyle (' A '. $num)->getalignment ()->sethorizontal (Phpexcel_style_alignment::horizontal_ CENTER); Sum 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, ' banner ads '); Merge Cells $objActSheet->mErgecells (' A '. $num. ': C '. $num);  $objPHPExcel->getactivesheet ()->setsharedstyle ($sharedStyle 1, "a". $teachernum. ": a". $newnum); $objPHPExcel->setactivesheetindex (0)->setcellvalue (' A ' $num, ' summary of this category '); $objPHPExcel->getactivesheet ()->setsharedstyle ($sharedStyle 1, "A". $num. ":". $excelTitle [Count ($date)-1].$ NUM); $objActSheet->getstyle (' A '. $num)->getalignment ()->sethorizontal (Phpexcel_style_alignment::horizontal_ CENTER); Sum 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; }//Add Style $objphpexcel->getactiVesheet ()->setsharedstyle ($sharedStyle 1, "A1:". $excelTitle [Count ($date)-1]. " 1 ");//$objPHPExcel->getactivesheet ()->setsharedstyle ($sharedStyle 1," a1:a ". $num);//Frozen column $objphpexcel-> Getactivesheet ()->freezepane (' A1 '), $objPHPExcel->getactivesheet ()->freezepane (' B1 '); $objPHPExcel Getactivesheet ()->freezepane (' C1 '); $objPHPExcel->getactivesheet ()->freezepane (' D2 ');//Set Center $ Objactsheet->getstyle (' A2 ')->getalignment ()->setvertical (phpexcel_style_alignment::vertical_center); $ Objactsheet->getstyle (' A '. $num)->getalignment ()->sethorizontal (Phpexcel_style_alignment::horizontal_ CENTER);//Set column width $objphpexcel->getactivesheet ()->getcolumndimension (' C ')->setwidth (20);//Set Bottom Total statistics $ Ttotal = $tounum-1; $banjitotal = $banjinum-1; $fototal = $fonepagenum-1; $fttotal = $ftwopagenum-1; $frtotal = $fthr eepage-1; $btotal = $bottomnum-1; $chtotal = $teachernum-1; $adtotal = $adnum-1; $objPHPExcel->setactivesheetindex (0)->setcellvalue (' A '. $num,' Total ')->setcellvalue (' B '. $num, ')->setcellvalue (' C '. $num, ' daily Stats '); 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 ($sharedStyle 1, ' 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);//Set the name of the toggle Tag $objphpexcel->getactivesheet ()->settitLe (' Web users click Statistics ');//output directly to the browser 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;  }

?

