PHPExcel simplest example tutorial

PHPExcel most simple example tutorial PHPExcel is quite powerful MS Office Excel document generation class library, when you need to output more complex format data, PHPExcel is a good choice. However, the method is relatively cumbersome. List to remember.

PHP code

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 ());
7. /**
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 ';
13. // Uncomment
15. // Or
18. // Create a processing object instance
19. $ ObjExcel = new PHPExcel ();
21. // Create a file format to write the object instance, uncomment
23. // Or
25. // $ ObjWriter-> setOffice2003Compatibility (true );
27. //*************************************
28. // Set the basic attributes of the document
29. $ ObjProps = $ objExcel-> getProperties ();
30. $ ObjProps-> setCreator ("Zeal Li ");
31. $ ObjProps-> setLastModifiedBy ("Zeal Li ");
32. $ ObjProps-> setTitle ("Office XLS Test Document ");
33. $ ObjProps-> setSubject ("Office XLS Test Document, Demo ");
34. $ ObjProps-> setDescription ("Test document, generated by PHPExcel .");
35. $ ObjProps-> setKeywords ("office excel PHPExcel ");
36. $ ObjProps-> setCategory ("Test ");
38. //*************************************
39. // Set the current sheet index for subsequent content operations.
40. // Display the call only when multiple sheets are used.
41. // By default, PHPExcel will automatically create the first sheet and set SheetIndex = 0
42. $ ObjExcel-> setActiveSheetIndex (0 );
45. $ ObjActSheet = $ objExcel-> getActiveSheet ();
47. // Set the name of the current active sheet
48. $ ObjActSheet-> setTitle ('test Sheet ');
50. //*************************************
51. // Set the cell content
52. //
53. // PHPExcel automatically determines the cell content type based on the input content
54. $ ObjActSheet-> setCellValue ('A1', 'string content'); // String content
55. $ ObjActSheet-> setCellValue ('A2 ', 26); // value
56. $ ObjActSheet-> setCellValue ('A3 ', true); // boolean value
57. $ ObjActSheet-> setCellValue ('A4 ',' = SUM (A2: A2) '); // formula
59. // Explicitly specify the content type
60. $ ObjActSheet-> setCellValueExplicit ('a5 ', '123 ',
61. PHPExcel_Cell_DataType: TYPE_STRING );
63. // Merge cells
64. $ ObjActSheet-> mergeCells ('b1: c22 ');
66. // Separate cells
67. $ ObjActSheet-> unmergeCells ('b1: c22 ');
69. //*************************************
70. // Set the cell style
71. //
73. // Set the width
74. $ ObjActSheet-> getColumnDimension ('B')-> setAutoSize (true );
75. $ ObjActSheet-> getColumnDimension ('A')-> setWidth (30 );
77. $ ObjStyleA5 = $ objActSheet-> getStyle ('a5 ');
79. // Set the numeric format of the cell content.
80. //
81. // If PHPExcel_Writer_Excel5 is used to generate the content,
83. // Other types of custom formatting methods can be used normally, but when setFormatCode
84. // When FORMAT_NUMBER is used, the actual effect is not set to "0 ". Yes
87. // Line of code:
88. // If ($ ifmt = '0') $ ifmt = 1;
89. //
92. // All are displayed based on the original content.
93. $ ObjStyleA5
94. -> GetNumberFormat ()
95. -> SetFormatCode (PHPExcel_Style_NumberFormat: FORMAT_NUMBER );
97. // Set the font
98. $ ObjFontA5 = $ objStyleA5-> getFont ();
99. $ ObjFontA5-> setName ('courier new ');
100. $ ObjFontA5-> setSize (10 );
101. $ ObjFontA5-> setBold (true );
102. $ ObjFontA5-> setUnderline (PHPExcel_Style_Font: UNDERLINE_SINGLE );
103. $ ObjFontA5-> getColor ()-> setARGB ('ff9999999999 ');
105. // Set alignment
106. $ ObjAlignA5 = $ objStyleA5-> getAlignment ();
107. $ ObjAlignA5-> setHorizontal (PHPExcel_Style_Alignment: HORIZONTAL_RIGHT );
108. $ ObjAlignA5-> setVertical (PHPExcel_Style_Alignment: VERTICAL_CENTER );
110. // Set the border
111. $ ObjBorderA5 = $ objStyleA5-> getBorders ();
112. $ ObjBorderA5-> getTop ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN );
113. $ ObjBorderA5-> getTop ()-> getColor ()-> setARGB ('ffff000000'); // color
114. $ ObjBorderA5-> getBottom ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN );
115. $ ObjBorderA5-> getLeft ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN );
116. $ ObjBorderA5-> getRight ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN );
118. // Set the fill color
119. $ ObjFillA5 = $ objStyleA5-> getFill ();
120. $ ObjFillA5-> setFillType (PHPExcel_Style_Fill: FILL_SOLID );
121. $ ObjFillA5-> getStartColor ()-> setARGB ('ffeeeeeee ');
123. // Copy the style information from the specified cell.
124. $ ObjActSheet-> duplicateStyle ($ objStyleA5, 'b1: C22 ');
127. //*************************************
128. // Add an image
129. $ ObjDrawing = new PHPExcel_Worksheet_Drawing ();
130. $ ObjDrawing-> setName ('zealimg ');
131. $ ObjDrawing-> setDescription ('image inserted by zeal ');
132. $ ObjDrawing-> setPath ('./zeali.net.logo.gif ');
133. $ ObjDrawing-> setHeight (36 );
134. $ ObjDrawing-> setCoordinates ('c23 ');
135. $ ObjDrawing-> setOffsetX (10 );
136. $ ObjDrawing-> setRotation (15 );
137. $ ObjDrawing-> getShadow ()-> setVisible (true );
138. $ ObjDrawing-> getShadow ()-> setDirection (36 );
139. $ ObjDrawing-> setWorksheet ($ objActSheet );
142. // Add a new worksheet
143. $ ObjExcel-> createSheet ();
144. $ ObjExcel-> getSheet (1)-> setTitle ('test 2 ');
146. // Protect cells
147. $ ObjExcel-> getSheet (1)-> getProtection ()-> setSheet (true );
148. $ ObjExcel-> getSheet (1)-> protectCells ('A1: c22', 'phpexcel ');
151. //*************************************
152. // Output Content
153. //
154. $ OutputFileName = "output.xls ";
155. // To the file
156. /// $ ObjWriter-> save ($ outputFileName );
157. // Or
158. // Go to the browser
159. /// Header ("Content-Type: application/force-download ");
160. /// Header ("Content-Type: application/octet-stream ");
161. /// Header ("Content-Type: application/download ");
163. /// Header ("Content-Transfer-Encoding: binary ");
164. /// Header ("Expires: Mon, 26 Jul 1997 05:00:00 GMT ");
165. /// Header ("Last-Modified:". gmdate ("D, d m y h: I: s"). "GMT ");
167. /// Header ("Pragma: no-cache ");
168. //// $ ObjWriter-> save ('php: // output ');
170. ?>

The following is an example of using PHPExcel to read an Excel file:

PHP code

1. $ FileName = 'excel.xls ';
2. $ Path = 'path ';
3. $ FilePath = $ path. $ fileName;
4. $ PHPExcel = new PHPExcel ();
5. $ PHPReader = new PHPExcel_Reader_Excel2007 ();
6. If (! $ PHPReader-> canRead ($ filePath )){
7. $ PHPReader = new PHPExcel_Reader_Excel5 ();
8. If (! $ PHPReader-> canRead ($ filePath )){
9. Echo 'no Excel ';
10. Return;
11. }
12. }
13. $ PHPExcel = $ PHPReader-> load ($ filePath );
14. $ CurrentSheet = $ PHPExcel-> getSheet (0 );
15. /** Obtain the total number of columns */
16. $ AllColumn = $ currentSheet-> getHighestColumn ();
17. /** Obtain the total number of rows */
18. $ AllRow = array ($ currentSheet-> getHighestRow ());
20. For ($ currentRow = 1; $ currentRow <= $ allRow; $ currentRow ++ ){
21. For ($ currentColumn = 'a'; $ currentColumn <= $ allColumn; $ currentColumn ++ ){
22. $ Address = $ currentColumn. $ currentRow;
24. Echo $ currentSheet-> getCell ($ address)-> getValue (). "\ t ";
25. }
26. Echo "\ n ";
27. }