PHP and Excel Phpexcel

1, Phpexcel Introduction

Phpexcel is a PHP class library that is used to manipulate office Excel documents, based on Microsoft's OPENXML Standard and PHP language. You can use it to read and write spreadsheets in different formats, such as Excel (BIFF). xls, Excel 2007 (Officeopenxml). xlsx, CSV, Libre/openoffice Calc. ODS, Gnumeric, PDF , HTML, and so on.

2. Excel Export File

Include ' phpexcel.php ';
Include ' phpexcel/writer/excel2007.php '; Or include ' phpexcel/writer/excel5.php ';
For output. xls create an Excel $objPHPExcel = new Phpexcel ();
Save excel-2007 Format $objWriter = new phpexcel_writer_excel2007 ($objPHPExcel); or $objwriter = new Phpexcel_writer_excel5 ($objPHPExcel);
Non-2007 format $objWriter->save ("xxx.xlsx");
Direct output to the browser $objWriter = new Phpexcel_writer_excel5 ($objPHPExcel); Header ("PRAGMA;
Public "); Header ("Expires;
0″);
Header ("Cache-control;must-revalidate, post-check=0, Pre-check=0″);"
Header ("Content-type;application/force-download");
Header ("Content-type;application/vnd.ms-execl");
Header ("Content-type;application/octet-stream");
Header ("Content-type;application/download");;
Header (' Content-disposition;attachment;filename= "Resume.xls");
Header ("Content-transfer-encoding;binary");
$objWriter->save (' php;//output '); —————————————————————————————————————— – Set properties for Excel: Create a person $objPHPExcel->getproperties ()->setcreator ("Maarten
Balliauw "); Last modified person $objPHPExcel->gEtproperties ()->setlastmodifiedby ("Maarten Balliauw");
Title $objPHPExcel->getproperties ()->settitle ("Office 2007 XLSX Test Document");
Title $objPHPExcel->getproperties ()->setsubject ("Office 2007 XLSX Test Document"); Description $objPHPExcel->getproperties ()->setdescription ("Test document for Office 2007 XLSX, generated using PHP classes."
);
Keyword $objPHPExcel->getproperties ()->setkeywords ("Office 2007 OPENXML PHP");
Kind $objPHPExcel->getproperties ()->setcategory ("Test result file");
—————————————————————————————————————— – Set the current sheet $objPHPExcel->setactivesheetindex (0);
Sets the name of the sheet $objPHPExcel->getactivesheet ()->settitle (' simple ');
Sets the value of the cell $objPHPExcel->getactivesheet ()->setcellvalue (' A1 ', ' String ');
$objPHPExcel->getactivesheet ()->setcellvalue (' A2 ', 12);
$objPHPExcel->getactivesheet ()->setcellvalue (' A3 ', true); $objPHPExcel->getactivesheet ()->setcellvalue (' C5 ', ' =sum (C2;
C4) '); $objPHPExcel->getactivesheet ()->setcellvalue (' B8 '), ' =min (B2;
C5) '); Merged cells $objPHPExcel->getactivesheet ()->mergecells (' A18;
E22 '); Separate cells $objPHPExcel->getactivesheet ()->mergecells (' A18;
E22 '); $objPHPExcel->getactivesheet ()->mergecells (' A28;
B28 '); $objPHPExcel->getactivesheet ()->unmergecells (' A28;
B28 '); Protection cell $objPHPExcel->getactivesheet ()->getprotection ()->setsheet (true);
Needs to is set to true in order to enable any worksheet protection! $objPHPExcel->getactivesheet ()->protectcells (' A3;
E13 ', ' phpexcel '); Format//Set cell number formats echo date (' H;i;s ').
"Set cell number formats\n"; $objPHPExcel->getactivesheet ()->getstyle (' E4 ')->getnumberformat ()->setformatcode (phpexcel_style_ NumberFormat;;
Format_currency_eur_simple); $objPHPExcel->getactivesheet ()->duplicatestyle ($objPHPExcel->getactivesheet ()->getstyle (' E4 '), ' E5;
E13 ');
Sets the width width//Set column widths $objPHPExcel->getactivesheet ()->getcolumndimension (' B ')->setautosize (true); $objPHPExcel->getactivesheET ()->getcolumndimension (' D ')->setwidth (12);
Set Font $objPHPExcel->getactivesheet ()->getstyle (' B1 ')->getfont ()->setname (' Candara ');
$objPHPExcel->getactivesheet ()->getstyle (' B1 ')->getfont ()->setsize (20);
$objPHPExcel->getactivesheet ()->getstyle (' B1 ')->getfont ()->setbold (true); $objPHPExcel->getactivesheet ()->getstyle (' B1 ')->getfont ()->setunderline (phpexcel_style_font;;
Underline_single); $objPHPExcel->getactivesheet ()->getstyle (' B1 ')->getfont ()->getcolor () ()->setargb (phpexcel_style_ Color;;
Color_white); $objPHPExcel->getactivesheet ()->getstyle (' E1 ')->getfont ()->getcolor () ()->setargb (phpexcel_style_ Color;;
Color_white);
$objPHPExcel->getactivesheet ()->getstyle (' D13 ')->getfont ()->setbold (true);
$objPHPExcel->getactivesheet ()->getstyle (' E13 ')->getfont ()->setbold (true); Set align $objPHPExcel->getactivesheet ()->getstyle (' D11 ')->getalignment ()->sethorizontal (phpexcel_ Style_alignment;;
Horizontal_right); $objPHPExcel->getactivesheet ()->getstyle (' D12 ')->getalignment ()->sethorizontal (phpexcel_style_ alignment;;
Horizontal_right); $objPHPExcel->getactivesheet ()->getstyle (' D13 ')->getalignment ()->sethorizontal (phpexcel_style_ alignment;;
Horizontal_right); $objPHPExcel->getactivesheet ()->getstyle (' A18 ')->getalignment ()->sethorizontal (phpexcel_style_ alignment;;
Horizontal_justify); Center vertically $objPHPExcel->getactivesheet ()->getstyle (' A18 ')->getalignment ()->setvertical (phpexcel_style_ alignment;;
Vertical_center); Set column Border $objPHPExcel->getactivesheet ()->getstyle (' A4 ')->getborders ()->gettop ()-> Setborderstyle (Phpexcel_style_border;;
Border_thin); $objPHPExcel->getactivesheet ()->getstyle (' B4 ')->getborders ()->gettop () ()->setborderstyle (PHPExcel _style_border;;
Border_thin); $objPHPExcel->getactivesheet ()->getstyle (' C4 ')->getborders ()->gettop () ()->setborderstyle (PHPExcel _style_border;; Border_thiN); $objPHPExcel->getactivesheet ()->getstyle (' D4 ')->getborders ()->gettop () ()->setborderstyle (PHPExcel _style_border;;
Border_thin); $objPHPExcel->getactivesheet ()->getstyle (' E4 ')->getborders ()->gettop () ()->setborderstyle (PHPExcel _style_border;;
Border_thin); Set Border color $objPHPExcel->getactivesheet ()->getstyle (' D13 ')->getborders ()->getleft ()->
GetColor ()->setargb (' FF993300 '); $objPHPExcel->getactivesheet ()->getstyle (' D13 ')->getborders ()->gettop () ()->getcolor ()->setargb
(' FF993300 '); $objPHPExcel->getactivesheet ()->getstyle (' D13 ')->getborders ()->getbottom () ()->getcolor ()->
Setargb (' FF993300 '); $objPHPExcel->getactivesheet ()->getstyle (' E13 ')->getborders ()->gettop () ()->getcolor ()->setargb
(' FF993300 '); $objPHPExcel->getactivesheet ()->getstyle (' E13 ')->getborders ()->getbottom () ()->getcolor ()->
Setargb (' FF993300 '); $objPHPExcel->getactivesheet ()->getstyle (' E13 ')->getborders ()->gEtright ()->getcolor ()->setargb (' FF993300 '); Set the fill Color $objPHPExcel->getactivesheet ()->getstyle (' A1 ')->getfill ()->setfilltype (Phpexcel_style_fill;;
Fill_solid); $objPHPExcel->getactivesheet ()->getstyle (' A1 ')->getfill ()->getstartcolor ("->setargb")
; $objPHPExcel->getactivesheet ()->getstyle (' B1 ')->getfill ()->setfilltype (Phpexcel_style_fill;;
Fill_solid); $objPHPExcel->getactivesheet ()->getstyle (' B1 ')->getfill ()->getstartcolor ("->setargb")
;
Add Picture $objDrawing = new phpexcel_worksheet_drawing ();
$objDrawing->setname (' Logo ');
$objDrawing->setdescription (' Logo ');
$objDrawing->setpath ('./images/officelogo.jpg ');
$objDrawing->setheight (36);
$objDrawing->setworksheet ($objPHPExcel->getactivesheet ());
$objDrawing = new phpexcel_worksheet_drawing ();
$objDrawing->setname (' Paid ');
$objDrawing->setdescription (' Paid ');
$objDrawing->setpath ('./images/paid.png ');
$objDrawing->setcoordinates (' B15 '); $objDrAwing->setoffsetx (110);
$objDrawing->setrotation (25);
$objDrawing->getshadow ()->setvisible (true);
$objDrawing->getshadow ()->setdirection (45);
$objDrawing->setworksheet ($objPHPExcel->getactivesheet ()); After the default sheet, create a worksheet echo date (' H;i;s ').
"Create new worksheet object\n";
 $objPHPExcel->createsheet ();

3. Excel Import

Require_once ' phpexcel/iofactory.php ';
$inputFileName = ' Ceshi.xls ';
$objReader = new Phpexcel_reader_excel5 ();
$objPHPExcel = $objReader->load ($inputFileName);
$sheet = $objPHPExcel->getsheet (0); $highestRow = $sheet->gethighestrow (); Get the total number $highestColumn = $sheet->gethighestcolumn ();
Gets the total number of columns $tempArray = Array (); For ($j =2 $j <= $highestRow; $j + +) {for ($k = ' A '; $k <= $highestColumn; $k + +) {if ($k = = ' M ' $k = = ' o ')//m columns and O columns are time $tempAr
	Ray[] = Exceltime ($objPHPExcel->getactivesheet ()->getcell ("$k $j")->getvalue ());
	else $tempArray [] = $objPHPExcel->getactivesheet ()->getcell ("$k $j")->getvalue ();
	} print_r ($tempArray);
Unset ($tempArray);  The function exceltime ($date, $time = False) {if (function_exists (' Gregoriantojd ')) {if (Is_numeric ($date)) {$JD =
		GREGORIANTOJD (1, 1, 1970);
		$gregorian = Jdtogregorian ($jd + intval ($date)-25569);
		$date = explode ('/', $gregorian); $date _str = Str_pad ($date [2], 4, ' 0 ', str_pad_left). " -". Str_pad ($date [0], 2, ' 0 ', str_pad_left). " -". Str_pad ($date [1], 2, ' 0 ', str_pad_left). ($time?
		"00:00:00": "");
		return $date _str;
		}else{$date = $date >25568 $date +1:25569;
		/*there was a bug if converting date before 1-1-1970 (tstamp 0) */$ofs = (70 * 365 + 17+2) * 86400; $date = Date ("Y-m-d", ($date * 86400)-$ofs). ($time?
	"00:00:00": "");
return $date; }