Summary of common methods based on Phpexcel (Phpexcel class Library instance)

Share a detailed summary and analysis of the common methods of phpexcel.

For those unfamiliar with the Phpexcel class library, you can read the contents of the Phpexcel Chinese help manual, and we can phpexcel the relevant examples in the Quick Development Guide for specific examples.

Code Listing 1:

// usually Phpexcel objects have two ways of instantiating//1. Create a blank document with the New keyword $phpexcel = newphpexcel (); // 2. Create $phpexcel =phpexcel_iofactory::createreader ("Excel5")->load ("Template.xls") by reading the existing template;

Code:

<?//to set the include path for the Phpexcel class librarySet_include_path(‘.‘. Path_separator. ' D:\Zeal\PHP_LIBS '. Path_separator.Get_include_path());/** * The following is an example of use, for the lines beginning with////are different options, please open the corresponding line of comments according to actual needs *. * 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 versions xls//or////require_once ' phpexcel/writer/excel2007.php ';//for excel-2007 format//Create a Processing object instance$objExcel=NewPhpexcel ();//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 base 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////The cell content type is automatically determined by phpexcel based on the incoming content$objActSheet->setcellvalue (' A1 ', ' string content ');//string Content$objActSheet->setcellvalue (' A2 ', 26);//value$objActSheet->setcellvalue (' A3 ',true);//Boolean value$objActSheet->setcellvalue (' A4 ', ' =sum (A2:A2) ');//Formula//explicitly specify 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,//You need to note that in the Phpexcel_style_numberformat class, the const variable definition//Various custom formatting methods, Other types can be used normally, but when setformatcode//is format_number, the actual effect is 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 the IF ($this->_biff_version = = 0x0500) {(Near line No. 363) Line code://if ($ifmt = = = ' 0 ') $ifmt = 1;//www.jbxue.com//format to Phpexcel_style_numberformat::format_number, avoid some large numbers// By using scientific notation, the following Setautosize method allows the contents//of each line to be displayed in 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=Newphpexcel_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 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 ');?>