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 |