Summary of common PHPExcel Methods

 

1. <?
2. // Set the include path of the PHPExcel class library
3. Set_include_path ('.'. PATH_SEPARATOR.
4. 'D: \ Zeal \ PHP_LIBS '. PATH_SEPARATOR.
5. Get_include_path ());
6. /** Www.2cto.com
7. * The following is an example. The optional methods are different for rows starting //.
8. * Open the comment of the corresponding row.
9. * If Excel5 is used, the output content should be GBK encoded.
10. */
11. Require_once 'phpexcel. php ';
12. // Uncomment
13. /// Require_once 'phpexcel/Writer/excel5.php'; // used for other earlier versions of xls
14. // Or
15. /// Require_once 'phpexcel/Writer/excel2007.php'; // used for excel-2007 format
16. // Create a processing object instance
17. $ ObjExcel = new PHPExcel ();
18. // Create a file format to write the object instance, uncomment
19. //// $ ObjWriter = new PHPExcel_Writer_Excel5 ($ objExcel); // used for other version formats
20. // Or
21. //// $ ObjWriter = new PHPExcel_Writer_Excel2007 ($ objExcel); // used in 2007 format
22. // $ ObjWriter-> setOffice2003Compatibility (true );
23. //*************************************
24. // Set the basic attributes of the document
25. $ ObjProps = $ objExcel-> getProperties ();
26. $ ObjProps-> setCreator ("Zeal Li ");
27. $ ObjProps-> setLastModifiedBy ("Zeal Li ");
28. $ ObjProps-> setTitle ("Office XLS Test Document ");
29. $ ObjProps-> setSubject ("Office XLS Test Document, Demo ");
30. $ ObjProps-> setDescription ("Test document, generated by PHPExcel .");
31. $ ObjProps-> setKeywords ("office excel PHPExcel ");
32. $ ObjProps-> setCategory ("Test ");
33. //*************************************
34. // Set the current sheet index for subsequent content operations.
35. // Display the call only when multiple sheets are used.
36. // By default, PHPExcel will automatically create the first sheet and set SheetIndex = 0
37. $ ObjExcel-> setActiveSheetIndex (0 );
38. $ ObjActSheet = $ objExcel-> getActiveSheet ();
39. // Set the name of the current active sheet
40. $ ObjActSheet-> setTitle ('test Sheet ');
41. //*************************************
42. // Set the cell content
43. //
44. // PHPExcel automatically determines the cell content type based on the input content
45. $ ObjActSheet-> setCellValue ('a1', 'string content'); // string content
46. $ ObjActSheet-> setCellValue ('a2 ', 26); // Value
47. $ ObjActSheet-> setCellValue ('a3 ', true); // Boolean Value
48. $ ObjActSheet-> setCellValue ('a4 ',' = SUM (A2: A2) '); // Formula
49. // Explicitly specify the content type
50. $ ObjActSheet-> setCellValueExplicit ('a5 ', '123 ',
51. PHPExcel_Cell_DataType: TYPE_STRING );
52. // Merge Cells
53. $ ObjActSheet-> mergeCells ('b1: c22 ');
54. // Separate cells
55. $ ObjActSheet-> unmergeCells ('b1: c22 ');
56. //*************************************
57. // Set the cell style
58. //
59. // Set the width
60. $ ObjActSheet-> getColumnDimension ('B')-> setAutoSize (true );
61. $ ObjActSheet-> getColumnDimension ('A')-> setWidth (30 );
62. $ ObjStyleA5 = $ objActSheet-> getStyle ('a5 ');
63. // Set the numeric format of the cell content.
64. //
65. // If PHPExcel_Writer_Excel5 is used to generate the content,
66. // Note that the const variable defined in the PHPExcel_Style_NumberFormat class
67. // Other types of custom formatting methods can be used normally, but when setFormatCode
68. // When FORMAT_NUMBER is used, the actual effect is not set to "0 ". Yes
69. // Modify the getXf ($ style) method in the source code of the PHPExcel_Writer_Excel5_Format class,
70. // Add one before if ($ this-> _ BIFF_version = 0x0500) {(near row 363rd)
71. // Line of code:
72. // If ($ ifmt = '0') $ ifmt = 1;
73. //
74. // Set the format to PHPExcel_Style_NumberFormat: FORMAT_NUMBER to avoid large numbers.
75. // The data is displayed in scientific notation. The following setAutoSize method can be used to display the content of each row.
76. // All are displayed based on the original content.
77. $ ObjStyleA5
78. -> GetNumberFormat ()
79. -> SetFormatCode (PHPExcel_Style_NumberFormat: FORMAT_NUMBER );
80. // Set the font
81. $ ObjFontA5 = $ objStyleA5-> getFont ();
82. $ ObjFontA5-> setName ('courier new ');
83. $ ObjFontA5-> setSize (10 );
84. $ ObjFontA5-> setBold (true );
85. $ ObjFontA5-> setUnderline (PHPExcel_Style_Font: UNDERLINE_SINGLE );
86. $ ObjFontA5-> getColor ()-> setARGB ('ff9999999999 ');
87. // Set alignment
88. $ ObjAlignA5 = $ objStyleA5-> getAlignment ();
89. $ ObjAlignA5-> setHorizontal (PHPExcel_Style_Alignment: HORIZONTAL_RIGHT );
90. $ ObjAlignA5-> setVertical (PHPExcel_Style_Alignment: VERTICAL_CENTER );
91. // Set the border
92. $ ObjBorderA5 = $ objStyleA5-> getBorders ();
93. $ ObjBorderA5-> getTop ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN );
94. $ ObjBorderA5-> getTop ()-> getColor ()-> setARGB ('ffff000000'); // color
95. $ ObjBorderA5-> getBottom ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN );
96. $ ObjBorderA5-> getLeft ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN );
97. $ ObjBorderA5-> getRight ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN );
98. // Set the fill color
99. $ ObjFillA5 = $ objStyleA5-> getFill ();
100. $ ObjFillA5-> setFillType (PHPExcel_Style_Fill: FILL_SOLID );
101. $ ObjFillA5-> getStartColor ()-> setARGB ('ffeeeeeee ');
102. // Copy the style information from the specified cell.
103. $ ObjActSheet-> duplicateStyle ($ objStyleA5, 'b1: C22 ');
104. //*************************************
105. // Add an image
106. $ ObjDrawing = new PHPExcel_Worksheet_Drawing ();
107. $ ObjDrawing-> setName ('zealimg ');
108. $ ObjDrawing-> setDescription ('image inserted by zeal ');
109. $ ObjDrawing-> setPath ('./zeali.net.logo.gif ');
110. $ ObjDrawing-> setHeight (36 );
111. $ ObjDrawing-> setCoordinates ('c23 ');
112. $ ObjDrawing-> setOffsetX (10 );
113. $ ObjDrawing-> setRotation (15 );
114. $ ObjDrawing-> getShadow ()-> setVisible (true );
115. $ ObjDrawing-> getShadow ()-> setDirection (36 );
116. $ ObjDrawing-> setWorksheet ($ objActSheet );
117. // Add a new worksheet
118. $ ObjExcel-> createSheet ();
119. $ ObjExcel-> getSheet (1)-> setTitle ('test 2 ');
120. // Protect cells
121. $ ObjExcel-> getSheet (1)-> getProtection ()-> setSheet (true );
122. $ ObjExcel-> getSheet (1)-> protectCells ('a1: c22', 'phpexcel ');
123. //*************************************
124. // Output content
125. //
126. $ OutputFileName = "output.xls ";
127. // To the file
128. /// $ ObjWriter-> save ($ outputFileName );
129. // Or
130. // Go to the browser
131. /// Header ("Content-Type: application/force-download ");
132. /// Header ("Content-Type: application/octet-stream ");
133. /// Header ("Content-Type: application/download ");
134. /// Header ('content-Disposition: inline; filename = "'. $ outputFileName .'"');
135. /// Header ("Content-Transfer-Encoding: binary ");
136. /// Header ("Expires: Mon, 26 Jul 1997 05:00:00 GMT ");
137. /// Header ("Last-Modified:". gmdate ("D, d m y h: I: s"). "GMT ");
138. /// Header ("Cache-Control: must-revalidate, post-check = 0, pre-check = 0 ");
139. /// Header ("Pragma: no-cache ");
140. //// $ ObjWriter-> save ('php: // output ');
141. ?>
142. From: zeroplace.cn