Phpexcel Basic Operation:
Defining Excel Entities
Defines a Phpexcel object and sets the contents of the display within the Excel object
Excel starts//prepares Excel for include file//Error reporting error_reporting (0);//Phpexcel require_once dirname (__file__). ' phpexcel.php ';//Generate new Excel Object $objphpexcel = new Phpexcel ();//Set properties of Excel document $objphpexcel->getproperties () Setcreator ("sam.c") ->setlastmodifiedby ("Sam.c Test") ->settitle ("Microsoft Office Excel Document") ->setsubject ("Test")->setdescription ("test") ->setkeywords ("test") ->setcategory ("Test result file");//Start operation of Excel table//Operation first sheet $objphpexcel->setactivesheetindex (0);//Set workbook name $objphpexcel-> Getactivesheet ()->settitle (Iconv (' GBK ', ' utf-8 ', ' phpexcel test '));//set default font and size $objphpexcel->getdefaultstyle () ->getfont ()->setname (Iconv (' GBK ', ' utf-8 ', ' Arial ')); $objPHPExcel->getdefaultstyle ()->getfont () SetSize (10);
Three, output file
If you need to output Excel format if ($m _exporttype== "Excel") {$objWriter = Phpexcel_iofactory::createwriter ($objPHPExcel, ' Excel5 '); Output the $filename header directly from the browser ("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-excel;"); Header ("Content-type:application/octet-stream"); Header ("Content-type:application/download"); Header ("Content-disposition:attachment;filename=". $filename); Header ("Content-transfer-encoding:binary"); $objWriter->save ("Php://output"); }//If you need to export the PDF format if ($m _exporttype== "pdf") {$objWriter = Phpexcel_iofactory::createwriter ($objPHPExcel, ' pdf '); $objWriter->setsheetindex (0); 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/pdf"); Header ("Content-type:application/octet-stream"); Header ("Content-type:application/download"); Header ("Content-disposition:attachment;filename=". $m _stroutputpdffilename); Header ("Content-transfer-encoding:binary"); $objWriter->save ("Php://output"); }
Set the width of a column:
$objPHPExcel->getactivesheet ()->getcolumndimension (' A ')->setwidth (15);
Set the height of a row:
$objPHPExcel->getactivesheet ()->getrowdimension (' 6 ')->setrowheight (30);
Merge cells:
$objPHPExcel->getactivesheet ()->mergecells (' A1:p1 ');
Set A1 cell bold, centered:
$styleArray 1 = array (' font ' = = Array ( ' bold ' = = True, ' size ' =>12, ' Color ' =>array ( ' Argb ' = ' 00000000 ', ), ' alignment ' = = Array ( ' horizontal ' = = Phpexcel_style_alignment:: Horizontal_center, ), or//set A1 cell to bold, center $objphpexcel->getactivesheet ()->getstyle (' A1 ') Applyfromarray ($styleArray 1); $objPHPExcel->getactivesheet ()->getstyle (' B1 ')->getfont ()->setbold ( true);
To write content to a specific cell:
$objPHPExcel->getactivesheet ()->setcellvalue (' A1 ', ' Hello Baby ');
Set cell style (Horizontal/vertical Center):
$objPHPExcel->getactivesheet ()->getstyle (' A1 ')->getalignment ()->sethorizontal (phpexcel_style_ Alignment::horizontal_center); $objPHPExcel->getactivesheet ()->getstyle (' A1 ')->getalignment ()->setvertical (phpexcel_style_ Alignment::vertical_center);
Set cell style (black font):
$objPHPExcel->getactivesheet ()->getstyle (' H5 ')->getfont ()->getcolor ()->setargb (phpexcel_style_ Color::color_black); Black
formatting cells (background):
$objPHPExcel->getactivesheet ()->getstyle (' H5 ')->getfill ()->getstartcolor ()->setargb (' 00ff99cc ') ; Set the background to light pink
Format cells (number format):
$objPHPExcel->getactivesheet ()->getstyle (' F '. $iLineNumber)->getnumberformat ()->setformatcode (' 0.000 ');
To put a picture in a cell:
Place the data center picture in J1 cell $objdrawing = new phpexcel_worksheet_drawing (); $objDrawing->setname (' Logo '); $objDrawing SetDescription (' Logo '), $objDrawing->setpath (' test.jpg '), $objDrawing->setwidth ($objDrawing); SetHeight (123), $objDrawing->setcoordinates (' J1 '); $objDrawing->setworksheet ($objPHPExcel- Getactivesheet ());
To set a hyperlink in a cell:
$objPHPExcel->getactivesheet ()->setcellvalue (' H8 ', iconv (' GBK ', ' utf-8 ', ' Yan nan Tian ')); $objPHPExcel Getactivesheet ()->getcell (' H8 ')->gethyperlink ()->seturl (' http://www.bitsCN.com/');
Set cell borders
$styleThinBlackBorderOutline = Array (' borders ' = = Array (' outline ' = = Array ( ' style ' = = Phpexcel _style_border::border_thin, //Set Border style //' style ' = Phpexcel_style_border::border_thick, another style ' Color ' = = Array (' ARGB ' = ' FF000000 '), //Set Border color),) , $objPHPExcel->getactivesheet ()- >getstyle (' A4:e10 ')->applyfromarray ($styleThinBlackBorderOutline);//Add a new worksheet $objExcel- Createsheet (); $objActSheet = $objExcel->getsheet ($s); $objActSheet->settitle (' table '. $GSheet);