Generating complex report header samples based on PHPEXECL class _php tips

The examples in this article describe the generation of complex report headers based on the Phpexecl class. Share to everyone for your reference, specific as follows:

There has always been a need to execl the data inside the database and to export the data inside the database to the EXECL.

Require_once dirname (__file__). '/..
/classes/phpexcel/iofactory.php '; Class Phpexeclcore extends phpexcel_iofactory{public static function Summercreateexecl ($Head, $data) {Self::summercrea
 Teexeclhead ($Head, $data, "Excel2007");
 public static function Summerreadexecl ($dir) {if (!file_exists ($dir)) {echo ' execl not Exist ';
 else {$PHPExeclObj = Self::load ($dir); $sheetCount = $PHPEXECLOBJ->getsheetcount ();
 Gets the number of sheet workbooks contained in execl for ($i =0; $i < $sheetCount; $i + +) {$ActiveSheet = $PHPExeclObj->getsheet ($i); $highestRow = $ActiveSheet->gethighestrow ();
 Total number of columns $allColumn = $ActiveSheet->gethighestcolumn (); Read the contents of the sheet workbook through a nested loop for ($Col = ' A '; $Col < $allColumn; $Col + +) {for ($Row =1; $Row < $highestRow; $Row + +) {$Data [$
 col][$Row] = $ActiveSheet->getcell ($Col. $Row)->getvalue ();
 return $Data; * * * writes data to the datasheet * $Data array represents an array of rules to be inserted into the EXECL data $RuleData array represents the data format $i int represents the insert data from the first few lines * **/public stat IC function SumMerinsertdatetoexecl ($sheet, $Head, $Data, $n =3, $RuleData =array ()) {$SimpleHead = Self::gethead ($Head);
 $row = $n;
 foreach ($Data as $key => $valueArr) {$m = 0;
 foreach ($valueArr as $k => $v) {$StartCol = Phpexcel_cell::stringfromcolumnindex ($m). $row;
 $sheet->getcell ($StartCol)->setvalue ($v); $sheet->getstyle ($StartCol)->getalignment ()->applyfromarray (' Horizontal ' => phpexcel_style_ Alignment::horizontal_center, ' vertical ' => phpexcel_style_alignment::vertical_center, ' rotation ' => 0, ' wrap ' =
 > TRUE));
 if (Isset ($SimpleHead [$k [' col ']) {$m = $m + $SimpleHead [$k] [' col ']-1;
 $endCol = Phpexcel_cell::stringfromcolumnindex ($m). $row;
 $sheet->mergecells ($StartCol. ":". $endCol);
 } $m + +;
 $type = false;
 if (Isset ($SimpleHead [$k] [' type ']) {$type = $SimpleHead [$k] [' type '];
 $AllowArray = $SimpleHead [$k] [' Allowarray ']; //Set cell data validation if ($type) {switch ($type) {case ' list ': Self::setselectionrange ($sheet, $StartCol, $AlloWarray);
 Break
 Case ' range ': Self::setvaluerange ($sheet, $StartCol, $AllowArray);
 Break
 + + $row}}} * * * Generate EXECL Cell notes * $sheet Current Workbook Object * $Cell cells that need to set properties * $content NOTE content * * */private static function setcomment
 ($sheet, $Cell, $content)
 {$sheet->getcomment ($Cell)->setauthor (' 4399om ');
 $objCommentRichText = $sheet->getcomment ($Cell)->gettext ()->createtextrun (' 4399om: ');
 $objCommentRichText->getfont ()->setbold (true);
 $sheet->getcomment ($Cell)->gettext ()->createtextrun ("\ r \ n");
 $sheet->getcomment ($Cell)->gettext ()->createtextrun ($content);
 $sheet->getcomment ($Cell)->setwidth (' 100pt ');
 $sheet->getcomment ($Cell)->setheight (' 100pt ');
 $sheet->getcomment ($Cell)->setmarginleft (' 150pt ');
 $sheet->getcomment ($Cell)->getfillcolor ()->setrgb (' eeeeee '); * * * Now the valid data range for the cell, temporarily limited to the number * $sheet the current Workbook object * $Cell cells that need to set properties * $ValueRange array allows access to input arrays/private static FU Nction Setvaluerange ($sheet, $Cell, $ValueRange) {//sets the data type of the cells is a number and retains the number of significant digits $sheet->getstyle ($Cell)->getnumberformat ()->setformatcode (
 PHPEXCEL_STYLE_NUMBERFORMAT::FORMAT_NUMBER_00);
 $ValueRange = Explode (",", $ValueRange);
 Start numeric effective access setting $objValidation = $sheet->getcell ($Cell)->getdatavalidation ();
 $objValidation->settype (phpexcel_cell_datavalidation:: Type_whole);
 $objValidation->seterrorstyle (phpexcel_cell_datavalidation:: Style_stop);
 $objValidation->setallowblank (TRUE); $objValidation->setshowinputmessage (TRUE); Set the display prompt $objValidation->setshowerrormessage (true); Set display error message $objValidation->seterrortitle (' input error '); Error title $objValidation->seterror (' Please enter data range from '. $ValueRange [0]. ' To '. $ValueRange [1]. ' All values between the '); Error content $objValidation->setprompttitle (' Allow input '); Set the hint title $objValidation->setprompt (' Enter data range from '. $ValueRange [0]. ' To '. $ValueRange [1]. ' All values between the '); Prompt content $objValidation->setformula1 ($ValueRange [' 0 ']); Set the maximum value $objValidation->setformula2 ($ValueRange [' 1 ']); Set minimum value} private static function Outinputheader ($objWriter) {$fileName = Str_replace ('. php ', '. xlsx ', PathInfo (__fi
 le__, Pathinfo_basename));
 Header ("Content-type:application/force-download");
 Header ("Content-type:application/octet-stream");
 Header ("Content-type:application/download");
 Header (' Content-disposition:inline;filename= '. $fileName. ' ");
 Header ("Content-transfer-encoding:binary"); Header ("last-modified:"). Gmdate ("D, D M Y h:i:s").
 "GMT");
 Header ("Cache-control:must-revalidate, Post-check=0, pre-check=0");
 Header ("Pragma:no-cache");
 $objWriter->save (' php://output ');
 Exit }//Data control, set cell data in an optional orientation class private static function Setselectionrange ($sheet, $Cell, $rangeStr, $Title = "data type") {$objValida
 tion = $sheet->getcell ($Cell)->getdatavalidation (); $objValidation-> SetType (phpexcel_cell_datavalidation::type_list)-> Seterrorstyle (Phpexcel_cell_ Datavalidation::style_stop)-> Setallowblank (True)-> Setshowinputmessage (true)-> sEtshowerrormessage (True)-> Setshowdropdown (True)-> seterrortitle (' incorrect input value ')-> seterror (' The value you entered is not in the list of Drop-down boxes. ')
 -> setprompttitle (' "'. $Title. '")
 -> setFormula1 ('; $rangeStr. ');
 * * * Build header */public static function Recursioncreateexecl ($head, $data) {$PHPExecl = new phpexcel ();
 $objWriter = Self::createwriter ($PHPExecl, ' Excel2007 '); $PHPExecl->getproperties ()->setcreator ("4399om")->setlastmodifiedby ("Summer")->settitle ("Office 2007 XLSX Test Document ")->setsubject (" Office 2007 XLSX Test Document ")->setdescription (" Test document for Offic
 E 2007 XLSX, generated using PHP classes. ")
 ->setkeywords ("Office 2007 OPENXML PHP")->setcategory ("Test result file");
 $PHPExecl->setactivesheetindex (0);
 $sheet = $PHPExecl->getactivesheet ();
 Self::handleheadtonode ($sheet, $head, 1,0,0);
 Self::summerinsertdatetoexecl ($sheet, $head, $data, 4);
 Self::outinputheader ($objWriter); private static function Handleheadtonode ($sheet, $HeaD, $beginRow, $col, $StartCol) {foreach ($Head as $key => $cells) {$row = $beginRow;//representing line $beginCol = Phpexcel_cell::
 Stringfromcolumnindex ($col). $row;
 $sheet->getcell ($beginCol)->setvalue ($cells [' value ']); Sets the table Style $sheet->getstyle ($beginCol)->getalignment ()->applyfromarray ("Horizontal" => phpexcel_ Style_alignment::horizontal_center, ' vertical ' => phpexcel_style_alignment::vertical_center, ' rotation ' => 0, '
 Wrap ' => TRUE,)); $sheet->getstyle ($beginCol)->getfont ()->getcolor ()->setargb (Phpexcel_style_color::color_darkgreen)
 ; Sets the width of the cell if (Isset ($cells [' width ']) {$Cell = $sheet->getcolumndimension (Phpexcel_cell::stringfromcolumnindex (
 $col));
 $Cell->setwidth ($cells [' width ']);
 }//Gothic element marked if (Isset ($cells [' content '])) {self::setcomment ($sheet, $beginCol, $cells [' content ']); } $merge = false;
 The merged cells if (Isset ($cells [' col '])) {$col + = $cells [' col ']-1;
 $merge = true; } if (Isset ($cells [' Row '])} {$row += $cells [' Row ']-1;
 $merge = true;
 } if ($merge) {$endCol = Phpexcel_cell::stringfromcolumnindex ($col). $row;
 $sheet->mergecells ($beginCol. ":". $endCol);
 } $row + +;
 $col + +;
 Indicates that there is a child node if (isset ($cells [' Children ']) && is_array ($cells [' Children '])) {$cols = $StartCol;
 if (!self::isexistchildren ($cells [' children ']) {$cols = $col-2;
 $StartCol = $col;
 Self::handleheadtonode ($sheet, $cells [' Children '], $row, $cols, $StartCol);
 }else{$StartCol = $col; }}//Determine if there is a grandchild node in your child node private static function Isexistchildren ($Data) {foreach ($Data as $key => $value) {if
 (Isset ($value [' Children ']) && is_array ($value [' Children '])
 {return true;
 return false; }//Get the underlying data private static function GetHead ($Head,& $Node =array ()) {foreach ($Head as $key => $value) {if isset (
 $value [' Children ']) && is_array ($value [' Children '])) {Self::gethead ($value [' Children '], $Node);
 else {$Node [] = $value;
 } return $Node; }} $Head = Array (' value ' => ' name, ' col ' =>2, ' Row ' =>2, ' width ' =>20, ' type ' => ' list ', ' Allowarray ' => ') PHP Development Engineer, PHP development '), array (' Value ' => ' first day ', ' col ' =>2, ' Row ' =>1, ' width ' =>20, ' Content ' => ' 2014-12-29 ', ' Children ' => Array (Array (' Value ' => ' a.m. ', ' col ' =>1, ' width ' =>20, ' type ' => ' range ', ' Allowarray ' => ') 10,100 '), Array (' value ' => ' afternoon ', ' width ' =>20),), Array (' Value ' => ' next day ', ' col ' =>2, ' Row ' =>1, ' width ' = 
>20, ' Children ' => Array (Array (' Value ' => ' Morning ', ' width ' =>20), array (' value ' => ' afternoon ', ' width ' =>20),),
); $data = Array (' PHP Development Engineer ', ' 12 ', ' Eat 1 ', ' Sleep 1 ', ' Get up and brush your teeth 2 ', ' Eat and sleep 2 ', Array (' PHP Development Engineer ', ' 25 ', ' Eat 1 ', ' Sleep 1 ', ' Get up and brush your teeth 2 ', '
 Eat and Sleep 2 '), Array (' PHP Development Engineer ', ' 50 ', ' eat 1 ', ' Sleep 1 ', ' Get up and brush your teeth 2 ', ' Eat Sleep 2 ', Array (' PHP Development Engineer ', ' 99 ', ' Eat 1 ', ' Sleep 1 ', ' Get up and brush your teeth 2 ', ' Eat Sleep 2 '),
Array (' PHP Development Engineer ', ' 10 ', ' Eat 1 ', ' Sleep 1 ', ' Get up and brush your teeth 2 ', ' Eat Sleep 2 ');

 $Node = Phpexeclcore::recursioncreateexecl ($Head, $data);

The results are also basically in line with the requirements:

More interested in PHP related content readers can view the site topics: "PHP operation Office Document Skills Summary (including word,excel,access,ppt)", "PHP Array" operation Skills Encyclopedia, "PHP Sorting algorithm Summary", " PHP commonly used traversal algorithm and skills summary, "PHP Data structure and algorithm tutorial", "PHP Programming Algorithm Summary", "PHP Mathematical Operation Skills Summary", "PHP Regular Expression Usage summary", "PHP operation and operator Usage Summary", "PHP string (String) Usage Summary" A summary of common PHP database operation techniques

I hope this article will help you with the PHP program design.