PHP and Excel Phpexcel

1, Phpexcel Introduction

Phpexcel is a PHP class library for working with Office Excel documents, based on the Microsoft OPENXML Standard and PHP language. You can use it to read and write spreadsheets in different formats, such as Excel (BIFF). xls, Excel (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 a 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 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 person $objphpexcel-> GetProperties ()->setcreator ("Maarten Balliauw"); Last modified person $objphpexcel->getproperties ()->setlastModifiedBy ("Maarten Balliauw"); title $objphpexcel->getproperties ()->settitle ("Office XLSX Test Document"); topic $objPHPExcel->getproperties ()->setsubject ("Office" XLSX Test Document); Description $objphpexcel->getproperties ()->setdescription ("Test document for Office" XLSX, generated using PHP classes. "); Keyword $objphpexcel->getproperties ()->setkeywords ("Office openxml PHP"); kind $objphpexcel->getproperties () ->setcategory ("Test result file"); —————————————————————————————————————— – Sets the current sheet$objphpexcel-> Setactivesheetindex (0); set sheet Name$objphpexcel->getactivesheet ()->settitle (' simple '); Set cell Value $ Objphpexcel->getactivesheet ()->setcellvalue (' A1 ', ' String '); $objPHPExcel->getactivesheet () Setcellvalue (' A2 ', ' n '), $objPHPExcel->getactivesheet ()->setcellvalue (' A3 ', true); $objPHPExcel Getactivesheet ()->setcellvalue (' C5 ', ' =sum (C2; C4), $objPHPExcel->getactivesheet ()->setcellvalue (' B8 ', ' =min (B2; C5) '); Merge cell $objphpexcel->getactiveSheet ()->mergecells (' A18; E22 '); separate cell $objphpexcel->getactivesheet ()->mergecells (' A18; E22 '); $objPHPExcel->getactivesheet ()->mergecells (' A28; B28 '); $objPHPExcel->getactivesheet ()->unmergecells (' A28; B28 '); protect Cell$objphpexcel->getactivesheet ()->getprotection ()->setsheet (true); Needs to BES set to true in order to enable any worksheet protection! $objPHPExcel->getactivesheet ()->protectcells ( ' A3; E13 ', ' phpexcel '); format//Set cell number Formatsecho 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 '), set 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 (), $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);//Vertical Center $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's 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 fill Color $objphpexcel->getactivesheet ()->gEtstyle (' A1 ')->getfill ()->setfilltype (Phpexcel_style_fill;; Fill_solid); $objPHPExcel->getactivesheet ()->getstyle (' A1 ')->getfill ()->getstartcolor ()->setargb (' FF808080 '); $objPHPExcel->getactivesheet ()->getstyle (' B1 ')->getfill ()->setfilltype (Phpexcel_style _fill;; Fill_solid); $objPHPExcel->getactivesheet ()->getstyle (' B1 ')->getfill ()->getstartcolor ()->setargb (' FF808080 '); 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, $objDrawing->setrotation ( $objDrawing->getshadow ()->setvisible (true); $objDRawing->getshadow (->setdirection), $objDrawing->setworksheet ($objPHPExcel->getactivesheet ()); After the default sheet, create a worksheetecho 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 (); Total number of rows $highestcolumn = $sheet->gethighestcolumn (); Total number of columns obtained $temparray = array (); for ($j =2; $j <= $highestRow; $j + +) {for ($k = ' A '; $k <= $highestColumn; $k + +) {if ($k = = ' M ' | | $k = = ' o ')//m column and O column is time $temparray[] = Exceltime ($objPHPExcel->getactivesheet ()->getcell ("$k $j")->getvalue () ); else$temparray[] = $objPHPExcel->getactivesheet ()->getcell ("$k $j")->getvalue ();} Print_r ($tempArray); unset ($tempArray);} function Exceltime ($date, $time = False) {if (function_exists (' Gregoriantojd ')) {if (Is_numeric ($date)) {$JD = Gregoriant OJD (1, 1, 1970); $gregorian = Jdtogregorian ($jd + intval ($date)-25569); $date = explode ('/', $gregorian); $date _st R = 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;}

PHP and Excel Phpexcel