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