The usage and introduction of Phpexcel

Introduction of Phpexcel

Phpexcel provides a series of APIs to parse and generate documents such as Excel,pdf.

Phpexcel Although powerful, but the use of relatively some of the lock, if you need to output more complex format, is a good choice. Can be downloaded to the official source.

Second, phpexcel part function

Sets the current workbook and returns the Workbook object:
$excelSheet = $excel->setactivesheetindex (0);

Merges a cell, returns the Cell object, and the following example merges the first row of column A and the cell in the second row:

The code is as follows	Copy Code
$excelSheet->mergecells (' a1:a2 '); Set cell value, parameter: cell name, Value: $excelSheet->setcellvalue (' A1 ', ' string content '); $excelSheet->setcellvalue (' A2 ', 26); Numerical $excelSheet->setcellvalue (' A3 ', true); Boolean value $excelSheet->setcellvalue (' A4 ', ' =sum (A2:A2) '); Formula

Introduction to Phpexcel Usage

The code is as follows

Copy Code

Include ' phpexcel.php '; Include ' phpexcel/writer/excel2007.php '; Or include ' phpexcel/writer/excel5.php '; For the 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 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 ');

——————————————————————————————————————–

To set properties for Excel:

　　

The code is as follows

Copy Code

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"); Topic $objPHPExcel->getproperties ()->setsubject ("Office 2007 XLSX Test Document"); Describe $objPHPExcel->getproperties ()->setdescription ("Test document for Office 2007 XLSX, generated using PHP classes."); Key words $objPHPExcel->getproperties ()->setkeywords ("Office 2007 OPENXML PHP"); Kinds $objPHPExcel->getproperties ()->setcategory ("Test result file"); ——————————————————————————————————————– Set the current sheet $objPHPExcel->setactivesheetindex (0); Set the name of the sheet $objPHPExcel->getactivesheet ()->settitle (' simple '); Set the value of a 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) '); Merging cells $objPHPExcel->getactivesheet ()->mergecells (' a18:e22′); Detach cells $objPHPExcel->getactivesheet ()->unmergecells (' a28:b28′);

III. Application of Phpexcel examples

The entire code is as follows (it is noteworthy that the header used $ordercelldata to record the order of each merchant number in order to remove the corresponding data in the table body):

The code is as follows

Copy Code

<<?php Require_once '.. /.. /.. /libs/phpexcel/classes/phpexcel.php '; Require_once '.. /.. /.. /libs/phpexcel/classes/phpexcel/writer/excel5.php '; Include_once '.. /.. /.. /libs/phpexcel/classes/phpexcel/iofactory.php '; Include '.. /common/config.php '; Create a Process object instance (this object is the same for 2003 2007) $objExcel = new Phpexcel (); Set properties (This code doesn't matter, and the content can be replaced with what you need) $objExcel->getproperties ()->setcreator ("Office 2003 Excel"); $objExcel->getproperties ()->setlastmodifiedby ("Office 2003 Excel"); $objExcel->getproperties ()->settitle ("Office 2003 XLS Test Document"); $objExcel->getproperties ()->setsubject ("Office 2003 XLS Test Document"); $objExcel->getproperties ()->setdescription ("Test document for Office 2003 XLS, generated using PHP classes."); $objExcel->getproperties ()->setkeywords ("Office 2003 OPENXML PHP"); $objExcel->getproperties ()->setcategory ("Test result file"); Start processing data (index starting from 0) $objExcel->setactivesheetindex (0); $conn = Mssql_connect ($config [' MSSQL '] [' host '], $config [' MSSQL '] [' user '], $config [' MSSQL '] [' password ']); mssql_select_db ($config [' MSSQL '] [' dbname '], $conn); $tm =$_request[' TM ']; $sql = "exec hnow05_getttspace", ' ". $tm." ', ', 1 '; $sql =mb_convert_encoding ($sql, ' GBK ', ' UTF-8 '); $res =mssql_query ($sql); $i = 0; $k = Array (' station code ', ' Station name ', ' River stream ', ' to report time ', ' water level ', ' potential '); $count = count ($k); $arrs = Array (' A ', ' B ', ' C ', ' D ', ' E ', ' F '); Add Table Header for ($i =0; $i < $count; $i + +) { $objExcel->getactivesheet ()->setcellvalue ($arrs [$i]. " 1 "," $k [$i] "); } /*--------read data from the database-------* * $i = 0; while ($arr =mssql_fetch_array ($res)) { $STCD = $arr ["Stcd"]; $STNM = $arr ["STNM"]; $RVNM = $arr ["Rvnm"]; $tm = $arr ["TM"]; $tdz = $arr ["Tdz"]; $TDPTN = $arr ["TDPTN"]; if ($TDPTN = = ' 6 ') { $TDPTN = ' flat '; }else if ($tdptn = = ' 5 ') { $TDPTN = ' rise '; }else if ($tdptn = = ' 4 ') { $TDPTN = ' fall '; } $u 1= $i +2; $STNM =iconv ("GBK", "Utf-8", $STNM); $rvnm =iconv ("GBK", "Utf-8", $rvnm); $tm =iconv ("GBK", "Utf-8", $TM); /*----------Write-------------/* $objExcel->getactivesheet ()->setcellvalue (' a '. $u 1, "$stcd"); $objExcel->getactivesheet ()->setcellvalue (' B '. $u 1, "$stnm"); $objExcel->getactivesheet ()->setcellvalue (' C '. $u 1, "$rvnm"); $objExcel->getactivesheet ()->setcellvalue (' d ' $u 1, "$tm"); $objExcel->getactivesheet ()->setcellvalue (' E '. $u 1, "$tdz"); $objExcel->getactivesheet ()->setcellvalue (' F '. $u 1, "$TDPTN"); $i + +; } /*----------Set the cell border and color-------------* * $rows = Mssql_num_rows ($res); For ($i =0 $i < ($rows + 1); $i + +) { for ($j =0; $j < $count; $j + +) { $a = $i +1; $objExcel->getactivesheet ()->getstyle ($arrs [$j]. $a)->getborders ()->getallborders ()-> Setborderstyle (Phpexcel_style_border::border_thin); $objExcel->getactivesheet ()->getstyle ($arrs [$j]. $a)->getborders ()->getallborders ()->getcolor ()- >setargb (' ff00bbcc '); Center horizontally $objExcel->getactivesheet ()->getstyle ($arrs [$j]. $a)->getalignment ()->sethorizontal (phpexcel_style_ Alignment::horizontal_center); } } Width of high column $objExcel->getactivesheet ()->getcolumndimension (' A ')->setwidth (10); $objExcel->getactivesheet ()->getcolumndimension (' B ')->setwidth (15); $objExcel->getactivesheet ()->getcolumndimension (' C ')->setwidth (15); $objExcel->getactivesheet ()->getcolumndimension (' D ')->setwidth (20); $objExcel->getactivesheet ()->getcolumndimension (' E ')->setwidth (10); $objExcel->getactivesheet ()->getcolumndimension (' F ')->setwidth (10); Set up headers and footers. If there are no different headings odd/even if using a single head assumption. $objExcel->getactivesheet ()->getheaderfooter ()->setoddheader (' &l&bpersonal cash register& rprinted on &d '); $objExcel->getactivesheet ()->getheaderfooter ()->setoddfooter (' &l&b '. $objExcel-> GetProperties ()->gettitle (). ' &rpage &p of &n '); Set page orientation and size $objExcel->getactivesheet ()->getpagesetup ()->setorientation () (phpexcel_worksheet_pagesetup::orientation _portrait); $objExcel->getactivesheet ()->getpagesetup ()->setpapersize (phpexcel_worksheet_pagesetup::P apersize_a4) ; Renaming tables $objExcel->getactivesheet ()->settitle (' real-time tidal conditions '); Set Active sheet Index to the "the" the "sheet", so Excel opens this as the $objExcel->setactivesheetindex (0); Redirect output to a client ' s Web browser (EXCEL5) saved in excel2003 format Set the name of Excel $excelName = ' real-time tidal situation ('. $tm. ') '; $excelName = ' excel_ '. Date ("Ymdhis"); Header (' Content-type:application/vnd.ms-excel '); Header (' cache-control:max-age=0 '); Header (' Content-disposition:attachment filename= '. Iconv ("Utf-8", "GBK", $excelName). XLS '); $objWriter = Phpexcel_iofactory::createwriter ($objExcel, ' Excel5 '); $objWriter->save (' php://output '); Exit ?>