Examples of common methods of Phpexcel

/** * Phpexcel Usage Example * by bbs.it-home.org * ################ */ To set the include path for the Phpexcel class library Set_include_path ('. '). Path_separator. ' D:\Zeal\PHP_LIBS '. Path_separator. Get_include_path ()); /** * The following are examples of use, for lines beginning with////are optional, depending on your needs * Open the comment for the corresponding line. * If you use Excel5, the content of the output should be GBK encoded. */ Require_once ' phpexcel.php '; Uncomment Require_once ' phpexcel/writer/excel5.php '; For other low version xls Or Require_once ' phpexcel/writer/excel2007.php '; For excel-2007 format Create a Processing object instance $objExcel = new Phpexcel (); Create a file format to write to an object instance, uncomment $objWriter = new Phpexcel_writer_excel5 ($objExcel); For other version formats Or $objWriter = new phpexcel_writer_excel2007 ($objExcel); For 2007 formats $objWriter->setoffice2003compatibility (TRUE); Set document basic Properties $objProps = $objExcel->getproperties (); $objProps->setcreator ("Zeal Li"); $objProps->setlastmodifiedby ("Zeal Li"); $objProps->settitle ("Office XLS Test Document"); $objProps->setsubject ("Office XLS Test Document, Demo"); $objProps->setdescription ("Test document, generated by Phpexcel."); $objProps->setkeywords ("Office Excel Phpexcel"); $objProps->setcategory ("Test"); Sets the current sheet index for subsequent content operations. It is generally only necessary to display a call when multiple sheet are used. By default, Phpexcel automatically creates the first sheet to be set sheetindex=0 $objExcel->setactivesheetindex (0); $objActSheet = $objExcel->getactivesheet (); Sets the name of the currently active sheet $objActSheet->settitle (' Test sheet '); Set cell contents // Automatically determine cell content type by phpexcel based on incoming content $objActSheet->setcellvalue (' A1 ', ' string content '); String content $objActSheet->setcellvalue (' A2 ', 26); Numerical $objActSheet->setcellvalue (' A3 ', true); Boolean value $objActSheet->setcellvalue (' A4 ', ' =sum (A2:A2) '); Formula Explicitly specifying a content type $objActSheet->setcellvalueexplicit (' A5 ', ' 847475847857487584 ', phpexcel_cell_datatype::type_string); Merge cells $objActSheet->mergecells (' b1:c22 '); Separating cells $objActSheet->unmergecells (' b1:c22 '); Set cell style Set width $objActSheet->getcolumndimension (' B ')->setautosize (true); $objActSheet->getcolumndimension (' A ')->setwidth (30); $objStyleA 5 = $objActSheet->getstyle (' A5 '); Sets the number format for the contents of the cell. // If you use Phpexcel_writer_excel5 to generate content, It is important to note that the const variable in the Phpexcel_style_numberformat class defines the In various custom formats, other types can be used normally, but when the Setformatcode For Format_number, the actual effect was not formatted as "0". Need Modify the GETXF ($style) method in the source code of the Phpexcel_writer_excel5_format class, Add one in front of if ($this->_biff_version = = 0x0500) {(Near line No. 363) Line code: if ($ifmt = = = ' 0 ') $ifmt = 1; // Format as Phpexcel_style_numberformat::format_number to avoid some large numbers is displayed using scientific notation, with the following Setautosize method to let the contents of each line Are all displayed according to the original content. $objStyleA 5 ->getnumberformat () ->setformatcode (Phpexcel_style_numberformat::format_number); Set font $objFontA 5 = $objStyleA 5->getfont (); $objFontA 5->setname (' Courier New '); $objFontA 5->setsize (10); $objFontA 5->setbold (TRUE); $objFontA 5->setunderline (Phpexcel_style_font::underline_single); $objFontA 5->getcolor ()->setargb (' FF999999 '); Set alignment $objAlignA 5 = $objStyleA 5->getalignment (); $objAlignA 5->sethorizontal (phpexcel_style_alignment::horizontal_right); $objAlignA 5->setvertical (Phpexcel_style_alignment::vertical_center); Set border $objBorderA 5 = $objStyleA 5->getborders (); $objBorderA 5->gettop ()->setborderstyle (Phpexcel_style_border::border_thin); $objBorderA 5->gettop ()->getcolor ()->setargb (' FFFF0000 '); Color $objBorderA 5->getbottom ()->setborderstyle (Phpexcel_style_border::border_thin); $objBorderA 5->getleft ()->setborderstyle (Phpexcel_style_border::border_thin); $objBorderA 5->getright ()->setborderstyle (Phpexcel_style_border::border_thin); Set Fill Color $objFillA 5 = $objStyleA 5->getfill (); $objFillA 5->setfilltype (phpexcel_style_fill::fill_solid); $objFillA 5->getstartcolor ()->setargb (' ffeeeeee '); Copies the style information from the specified cell. $objActSheet->duplicatestyle ($objStyleA 5, ' b1:c22 '); Add a picture $objDrawing = new phpexcel_worksheet_drawing (); $objDrawing->setname (' zealimg '); $objDrawing->setdescription (' Image inserted by Zeal '); $objDrawing->setpath ('./zeali.net.logo.gif '); $objDrawing->setheight (36); $objDrawing->setcoordinates (' C23 '); $objDrawing->setoffsetx (10); $objDrawing->setrotation (15); $objDrawing->getshadow ()->setvisible (true); $objDrawing->getshadow ()->setdirection (36); $objDrawing->setworksheet ($objActSheet); Add a new worksheet $objExcel->createsheet (); $objExcel->getsheet (1)->settitle (' Test 2 '); Protect cells $objExcel->getsheet (1)->getprotection ()->setsheet (true); $objExcel->getsheet (1)->protectcells (' A1:c22 ', ' phpexcel '); Output content // $outputFileName = "Output.xls"; to a file $objWriter->save ($outputFileName); Or to the browser Header ("Content-type:application/force-download"); Header ("Content-type:application/octet-stream"); Header ("Content-type:application/download"); Header (' Content-disposition:inline;filename= '. $outputFileName. ' '); Header ("Content-transfer-encoding:binary"); Header ("Expires:mon, Jul 1997 05:00:00 GMT"); Header ("last-modified:".) Gmdate ("D, D M Y h:i:s"). "GMT"); Header ("Cache-control:must-revalidate, Post-check=0, pre-check=0"); Header ("Pragma:no-cache"); $objWriter->save (' php://output '); ?> Copy Code

