Phpexcel report generation

Phpexcel generate a table ** export an excel table * according to the statistics title information, date information, and excel table labeling information * First Cycle statistics title information under each Statistics cycle by date specific information * cell settings merge add style * function_export ($ tipMsgnull, $ datenull phpexcel generate a table

/** Export an excel table * Set the title information according to the statistics title, date information, and excel table marking information. * collect the title information cyclically and set the specific information by date under each Statistics *. merge and add a style */function _ export ($ tipMsg = null, $ date = null, $ excelTitle = null) {$ this-> autoRender = false; App: import ('bad', 'phpexcel ', array ('file' => 'phpexcel. php '); App: import ('upload', 'phpexcelwrite', 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 attributes $ 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 "); // style Declaration $ objActSheet = $ objPHPExcel-> getActiveSheet (); // set the style font $ sharedStyle1 = new PHPExcel_Style (); $ sharedStyle1-> applyFromArray (array ('fill' => array ('type' => PHPExcel_Style_Fill: FILL_SOLID, 'color' => array ('Arg' => '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 ('Arg' => '123'),); // add header data $ objPHPExcel-> setActiveSheetIndex (0) -> setCellValue ('A1', 'Module category')-> 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 * category processing tou (head navigation) banji (class classification) fonepage (homepage one screen) ftwopage (home page 2) fthreepage (home page 3) * bottom (bottom) teacher (famous teacher) ad (advertisement) */$ num = 2; // The starting mark of the loop $ datenum = count ($ date)-1; foreach ($ tipMsg as $ k => $ v) {if ($ k = 'tou ') {$ tounum = $ num + 1; $ newnum = $ num-1; $ objPHPExcel-> setActiveSheetIndex (0)-> setCellValue ('A2 ', 'navigation head '); // merge cells $ objActSheet-> mergeCells ('A '. $ num. ': C '. $ num); $ objActSheet-> mergeCells ('A2 '. ': '. $ newnum); $ objPHPExcel-> getActiveSheet ()-> setSharedStyle ($ sharedStyle1, "A2: ". $ newnum); $ objPHPExcel-> setActiveSheetIndex (0)-> setCellValue ('A '. $ num, 'Summary of this class'); $ objPHPExcel-> getActiveSheet ()-> setSharedStyle ($ sharedStyle1, "". $ 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) -> setCellValue ('A '. $ tounum, 'class category'); // merge cells $ objActSheet-> mergeCells ('A '. $ num. ': C '. $ num); $ objPHPExcel-> getActiveSheet ()-> setSharedStyle ($ sharedStyle1, "". $ tounum. ": ". $ newnum); $ objPHPExcel-> setActiveSheetIndex (0)-> setCellValue ('A '. $ num, 'Summary of this class'); $ objPHPExcel-> getActiveSheet ()-> setSharedStyle ($ sharedStyle1, "". $ 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) -> setCellValue ('A '. $ banjinum, 'homepage one screen'); // merge cells $ objActSheet-> mergeCells ('A '. $ num. ': C '. $ num); $ objPHPExcel-> getActiveSheet ()-> setSharedStyle ($ sharedStyle1, "". $ banjinum. ": ". $ newnum); $ objPHPExcel-> setActiveSheetIndex (0)-> setCellValue ('A '. $ num, 'Summary of this class'); $ objPHPExcel-> getActiveSheet ()-> setSharedStyle ($ sharedStyle1, "". $ 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) -> setCellValue ('A '. $ fonepagenum, 'Home page 2 screen'); // merge cells $ objActSheet-> mergeCells ('A '. $ num. ': C '. $ num); $ objPHPExcel-> getActiveSheet ()-> setSharedStyle ($ sharedStyle1, "". $ fonepagenum. ": ". $ newnum); $ objPHPExcel-> setActiveSheetIndex (0)-> setCellValue ('A '. $ num, 'Summary of this class'); $ objPHPExcel-> getActiveSheet ()-> setSharedStyle ($ sharedStyle1, "". $ 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) -> setCellValue ('A '. $ ftwopagenum, 'homepage three screen'); // merge cells $ objActSheet-> mergeCells ('A '. $ num. ': C '. $ num); $ objPHPExcel-> getActiveSheet ()-> setSharedStyle ($ sharedStyle1, "". $ ftwopagenum. ": ". $ newnum); $ objPHPExcel-> setActiveSheetIndex (0)-> setCellValue ('A '. $ num, 'Summary of this class'); $ objPHPExcel-> getActiveSheet ()-> setSharedStyle ($ sharedStyle1, "". $ 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) -> setCellValue ('A '. $ fthreepage, 'bottom'); // merge cells $ objActSheet-> mergeCells ('A '. $ num. ': C '. $ num); $ objPHPExcel-> getActiveSheet ()-> setSharedStyle ($ sharedStyle1, "". $ fthreepage. ": ". $ newnum); $ objPHPExcel-> setActiveSheetIndex (0)-> setCellValue ('A '. $ num, 'Summary of this class'); $ objPHPExcel-> getActiveSheet ()-> setSharedStyle ($ sharedStyle1, "". $ 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) -> setCellValue ('A '. $ bottomnum, 'famous teachers' Channel'); // merge cells $ objActSheet-> mergeCells ('A '. $ num. ': C '. $ num); $ objPHPExcel-> getActiveSheet ()-> setSharedStyle ($ sharedStyle1, "". $ bottomnum. ": ". $ newnum); $ objPHPExcel-> setActiveSheetIndex (0)-> setCellValue ('A '. $ num, 'Summary of this class'); $ objPHPExcel-> getActiveSheet ()-> setSharedStyle ($ sharedStyle1, "". $ 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, 'Bar advertisement '); // merge cells $ objActSheet-> mergeCells ('A '. $ num. ': C '. $ num); $ objPHPExcel-> getActiveSheet ()-> setSharedStyle ($ sharedStyle1, "". $ teachernum. ": ". $ newnum); $ objPHPExcel-> setActiveSheetIndex (0)-> setCellValue ('A '. $ num, 'Summary of this class'); $ objPHPExcel-> getActiveSheet ()-> setSharedStyle ($ sharedStyle1, "". $ 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 a style $ objPHPExcel-> getActiveSheet () -> setSharedStyle ($ sharedStyle1, "A1 :". $ excelTitle [count ($ date)-1]. "1"); // $ objPHPExcel-> getActiveSheet ()-> setSharedStyle ($ sharedStyle1, "A1: ". $ num); // freeze the 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 (Region: HORIZONTAL_CENTER); // Set the column width $ objPHPExcel-> getActiveSheet ()-> getColumnDimension ('C ') -> setWidth (20); // set the total number of statistics at the bottom $ 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, 'total')-> setCellValue ('B '. $ num, '')-> setCellValue ('C '. $ num, 'Daily Statistics'); 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); // Set the name of the switch tag $ objPHPExcel-> getActiveSheet () -> setTitle ('Statistics on user clicks of the excellent network users'); // directly output 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 ;}

?