The usage and introduction of Phpexcel _php Tutorial

Phpexcel is a PHP plugin used to generate Excel, and he can easily manipulate Excel data, such as generating Excel, modifying Excel data, and so on.

I. Introduction of PHPEXCEL

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

Although Phpexcel is powerful, it is a good choice if you need to output a more complex format. can go to the official download to the source code.

Part Two, Phpexcel function

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

Merge cells, return the Cell object, and the following example merges the first row of column A with the cell in the second row:

The code is as follows	Copy Code
$excelSheet->mergecells (' a1:a2 '); Set the cell's 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

Phpexcel Usage Introduction

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 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 "); Describe $objPHPExcel->getproperties ()->setdescription ("Test document for Office-XLSX, generated using PHP classes."); Key words $objPHPExcel->getproperties ()->setkeywords ("Office 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) '); Merge cells $objPHPExcel->getactivesheet ()->mergecells (' a18:e22′); Separating cells $objPHPExcel->getactivesheet ()->unmergecells (' a28:b28′);

Three, phpexcel example application

The entire code is as follows (note that the header uses $ordercelldata to record the order of each merchant number in order to take the corresponding data out of the table body):

The code is as follows

Copy Code

< 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 Processing object instance (this object is the same for 2003 2007) $objExcel = new Phpexcel (); Set properties (This code doesn't matter, 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 system ', ' to quote time ', ' water level ', ' Waters '); $count = count ($k); $arrs = Array (' A ', ' B ', ' C ', ' D ', ' E ', ' F '); Add a 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 content-------------*/ $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 the 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 use 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 a table $objExcel->getactivesheet ()->settitle (' real-time tidal condition '); Set Active sheet Index to the first sheet, so Excel opens this as the first sheet $objExcel->setactivesheetindex (0); Redirect output to a client ' s Web browser (EXCEL5) saved to excel2003 format Set the name of Excel $excelName = ' real-time tidal conditions ('. $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 ?>

http://www.bkjia.com/PHPjc/630735.html www.bkjia.com true http://www.bkjia.com/PHPjc/630735.html techarticle Phpexcel is a PHP plugin used to generate Excel, and he can easily manipulate Excel data, such as generating Excel, modifying Excel data, and so on. I. Phpexcel profile Phpexcel offers a ...

