Require_once dirname (__file__). '/.. /classes/phpexcel/iofactory.php ';
Class Phpexeclcore extends phpexcel_iofactory{
public static function Summercreateexecl ($Head, $data) { Self::summercreateexeclhead ($Head, $data, "Excel2007"); }
public static function Summerreadexecl ($dir) { if (!file_exists ($dir)) { echo "Execl not Exist"; } Else { $PHPEXECLOBJ = Self::load ($dir);
$sheetCount = $PHPEXECLOBJ->getsheetcount (); Get the number of sheet workbooks included in the EXECL
for ($i =0; $i < $sheetCount; $i + +) { $ActiveSheet = $PHPEXECLOBJ->getsheet ($i); $highestRow = $ActiveSheet->gethighestrow (); Total number of columns obtained $allColumn = $ActiveSheet->gethighestcolumn ();
To read the contents of a 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;
}
/* * Write data to the datasheet * $Data Array indicates that you want to insert into EXECL data * $RuleData array of rules that represent data formats * $i int represents inserting data from the first few lines * **/
public static 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 ( Array ' 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 data validation for a cell 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 the current Workbook object * $Cell cells that need to set properties * $content Notes 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 function Setvaluerange ($sheet, $Cell, $ValueRange) { Sets the data type of the cell to be 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 valid access settings $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 hint information $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 Tip title $objValidation->setprompt (' Please enter data range from '. $ValueRange [0]. ' To '. $ValueRange [1]. ' All values between the '); Hint Content $objValidation->setformula1 ($ValueRange [' 0 ']); Set maximum value $objValidation->SETFORMULA2 ($ValueRange [' 1 ']); Set minimum value
}
private static function Outinputheader ($objWriter) { $fileName = Str_replace ('. php ', '. xlsx ', PathInfo (__file__, 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, setting cell data in an optional azimuth class private static function Setselectionrange ($sheet, $Cell, $rangeStr, $Title = "Data type") {
$objValidation = $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 (' input value is incorrect ') -> SetError (' The value you entered is not in the list of Drop-down boxes. ') -> setprompttitle (' "'. $Title. '") -> setFormula1 ('; $rangeStr. '); }
/* * Building the table 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 Office 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; Represents a row
$beginCol = Phpexcel_cell::stringfromcolumnindex ($col). $row;
$sheet->getcell ($beginCol)->setvalue ($cells [' value ']);
Set Table Styles $sheet->getstyle ($beginCol)->getalignment ()->applyfromarray ( Array ' 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) ;
Set the width of a cell if (Isset ($cells [' width ']) { $Cell = $sheet->getcolumndimension (phpexcel_cell::stringfromcolumnindex ($col)); $Cell->setwidth ($cells [' width ']); }
The elements of the gothic mark if (Isset ($cells [' Content ']) { Self::setcomment ($sheet, $beginCol, $cells [' Content ']); }
$merge = false; Merging 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 are children nodes 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's 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 ( 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 ' => ' The next day ', ' col ' =>2, ' Row ' =>1, ' width ' =>20, ' Children ' => Array Array (' value ' => ' Morning ', ' width ' =>20), Array (' value ' => ' afternoon ', ' width ' =>20), ), ), );
$data = Array ( Array (' PHP Development Engineer ', ' 12 ', ' Eat 1 ', ' Sleep 1 ', ' Get up and brush your teeth 2 ', ' Eat Sleep 2 '), Array (' PHP Development Engineer ', ' 25 ', ' Eat 1 ', ' Sleep 1 ', ' Get up and brush your teeth 2 ', ' Eat 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); |