Examples of common PHPExcel methods

Examples of common PHPExcel methods

/** * Phpexcel usage example * By bbs.it-home.org *################ */ // Set the include path of the PHPExcel class library Set_include_path ('.'. PATH_SEPARATOR. 'D: \ Zeal \ PHP_LIBS '. PATH_SEPARATOR. Get_include_path ()); /** * The following is an example. the optional methods are different for rows starting //. * Open the comment of the corresponding row. * If Excel5 is used, the output content should be GBK encoded. */ Require_once 'phpexcel. php '; // Uncomment /// Require_once 'phpexcel/Writer/excel5.php'; // used for other earlier versions of xls // Or /// Require_once 'phpexcel/Writer/excel2007.php'; // used for excel-2007 format // Create a processing object instance $ ObjExcel = new PHPExcel (); // Create a file format to write the object instance, uncomment //// $ ObjWriter = new PHPExcel_Writer_Excel5 ($ objExcel); // used for other version formats // Or //// $ ObjWriter = new PHPExcel_Writer_Excel2007 ($ objExcel); // used in 2007 format // $ ObjWriter-> setOffice2003Compatibility (true ); // Set the basic attributes of the document $ 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 "); // Set the current sheet index for subsequent content operations. // Display the call only when multiple sheets are used. // By default, PHPExcel will automatically create the first sheet and set SheetIndex = 0 $ ObjExcel-> setActiveSheetIndex (0 ); $ ObjActSheet = $ objExcel-> getActiveSheet (); // Set the name of the current active sheet $ ObjActSheet-> setTitle ('test Sheet '); // Set the cell content // // PHPExcel automatically determines the cell content type based on the input 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 the content type $ ObjActSheet-> setCellValueExplicit ('a5 ', '123 ', PHPExcel_Cell_DataType: TYPE_STRING ); // Merge cells $ ObjActSheet-> mergeCells ('b1: c22 '); // Separate cells $ ObjActSheet-> unmergeCells ('b1: c22 '); // Set the cell style // Set the width $ ObjActSheet-> getColumnDimension ('B')-> setAutoSize (true ); $ ObjActSheet-> getColumnDimension ('A')-> setWidth (30 ); $ ObjStyleA5 = $ objActSheet-> getStyle ('a5 '); // Set the numeric format of the cell content. // // If PHPExcel_Writer_Excel5 is used to generate the content, // Note that the const variable defined in the PHPExcel_Style_NumberFormat class // Other types of custom formatting methods can be used normally, but when setFormatCode // When FORMAT_NUMBER is used, the actual effect is not set to "0 ". Yes // Modify the getXf ($ style) method in the source code of the PHPExcel_Writer_Excel5_Format class, // Add one before if ($ this-> _ BIFF_version = 0x0500) {(near row 363rd) // Line of code: // If ($ ifmt = '0') $ ifmt = 1; // // Set the format to PHPExcel_Style_NumberFormat: FORMAT_NUMBER to avoid large numbers. // The data is displayed in scientific notation. the following setAutoSize method can be used to display the content of each row. // All are displayed based on the original content. $ ObjStyleA5 -> GetNumberFormat () -> SetFormatCode (PHPExcel_Style_NumberFormat: FORMAT_NUMBER ); // Set the font $ ObjFontA5 = $ objStyleA5-> getFont (); $ ObjFontA5-> setName ('courier new '); $ ObjFontA5-> setSize (10 ); $ ObjFontA5-> setBold (true ); $ ObjFontA5-> setUnderline (PHPExcel_Style_Font: UNDERLINE_SINGLE ); $ ObjFontA5-> getColor ()-> setARGB ('ff9999999999 '); // Set alignment $ ObjAlignA5 = $ objStyleA5-> getAlignment (); $ ObjAlignA5-> setHorizontal (PHPExcel_Style_Alignment: HORIZONTAL_RIGHT ); $ ObjAlignA5-> setVertical (PHPExcel_Style_Alignment: VERTICAL_CENTER ); // Set the border $ ObjBorderA5 = $ objStyleA5-> getBorders (); $ ObjBorderA5-> getTop ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN ); $ ObjBorderA5-> getTop ()-> getColor ()-> setARGB ('ffff000000'); // color $ ObjBorderA5-> getBottom ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN ); $ ObjBorderA5-> getLeft ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN ); $ ObjBorderA5-> getRight ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN ); // Set the fill color $ ObjFillA5 = $ objStyleA5-> getFill (); $ ObjFillA5-> setFillType (PHPExcel_Style_Fill: FILL_SOLID ); $ ObjFillA5-> getStartColor ()-> setARGB ('ffeeeeeee '); // Copy the style information from the specified cell. $ ObjActSheet-> duplicateStyle ($ objStyleA5, 'b1: C22 '); // Add an image $ 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 the file /// $ ObjWriter-> save ($ outputFileName ); // Or // Go 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, 26 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 '); ?>