php导出excel表格综合示例

3. include("config.php");

4. // load library
5. require 'php-excel.class.php';
6. // create a simple 2-dimensional array
7. //@mysql_query("set character set gb2312");
8. $strSql="select * from booklist";
9. $result=mysql_query($strSql);

10. //print_r($result);

11. $data = array(

12. 1 => array ('id', "书名"),//标题使用中文则到表格里面值为空，待解决
13. );
14. while($row=mysql_fetch_array($result))
15. {
16. array_push($data,array($row['bookid'], $row['bookname']));
17. }
18. // generate file (constructor parameters are optional)
19. $xls = new Excel_XML('UTF-8', false, 'My Test Sheet');
20. $xls->addArray($data);
21. $xls->generateXML('my-test');
22. ?>
24. /**
25. * Simple excel generating from PHP5
26. *
27. * @version 1.0
28. */

29. /**

30. * Generating excel documents on-the-fly from PHP5
31. *
32. * Uses the excel XML-specification to generate a native
33. * XML document, readable/processable by excel.
34. *
35. * @package Utilities
36. * @version 1.1
37. *
38. * @todo Issue #4: Internet Explorer 7 does not work well with the given header
39. * @todo Add option to give out first line as header (bold text)
40. * @todo Add option to give out last line as footer (bold text)
41. * @todo Add option to write to file
42. */
43. class Excel_XML
44. {

45. /**

46. * Header (of document)
47. * @var string
48. */
49. private $header = "\n";

50. /**

51. * Footer (of document)
52. * @var string
53. */
54. private $footer = "
";

55. /**

56. * Lines to output in the excel document
57. * @var array
58. */
59. private $lines = array();

60. /**

61. * Used encoding
62. * @var string
63. */
64. private $sEncoding;

65. /**

66. * Convert variable types
67. * @var boolean
68. */
69. private $bConvertTypes;

70. /**

71. * Worksheet title
72. * @var string
73. */
74. private $sWorksheetTitle;

75. /**

76. * Constructor
77. *
78. * The constructor allows the setting of some additional
79. * parameters so that the library may be configured to
80. * one's needs.
81. *
82. * On converting types:
83. * When set to true, the library tries to identify the type of
84. * the variable value and set the field specification for Excel
85. * accordingly. Be careful with article numbers or postcodes
86. * starting with a '0' (zero)!
87. *
88. * @param string $sEncoding Encoding to be used (defaults to UTF-8)
89. * @param boolean $bConvertTypes Convert variables to field specification
90. * @param string $sWorksheetTitle Title for the worksheet
91. */
92. public function __construct($sEncoding = 'UTF-8', $bConvertTypes = false, $sWorksheetTitle = 'Table1')
93. {
94. $this->bConvertTypes = $bConvertTypes;
95. $this->setEncoding($sEncoding);
96. $this->setWorksheetTitle($sWorksheetTitle);
97. }

98. /**

99. * Set encoding
100. * @param string Encoding type to set
101. */
102. public function setEncoding($sEncoding)
103. {
104. $this->sEncoding = $sEncoding;
105. }

106. /**

107. * Set worksheet title
108. *
109. * Strips out not allowed characters and trims the
110. * title to a maximum length of 31.
111. *
112. * @param string $title Title for worksheet
113. */
114. public function setWorksheetTitle ($title)
115. {
116. $title = preg_replace ("/[\\\|:|\/|\?|\*|\[|\]]/", "", $title);
117. $title = substr ($title, 0, 31);
118. $this->sWorksheetTitle = $title;
119. }

120. /**

121. * Add row
122. *
123. * Adds a single row to the document. If set to true, self::bConvertTypes
124. * checks the type of variable and returns the specific field settings
125. * for the cell.
126. *
127. * @param array $array One-dimensional array with row content
128. */
129. private function addRow ($array)
130. {
131. $cells = "";
132. foreach ($array as $k => $v):
133. $type = 'String';
134. if ($this->bConvertTypes === true && is_numeric($v)):
135. $type = 'Number';
136. endif;
137. $v = htmlentities($v, ENT_COMPAT, $this->sEncoding);
138. $cells .= "" . $v . "\n";
139. endforeach;
140. $this->lines[] = "\n" . $cells . "\n";
141. }

142. /**

143. * Add an array to the document
144. * @param array 2-dimensional array
145. */
146. public function addArray ($array)
147. {
148. foreach ($array as $k => $v)
149. $this->addRow ($v);
150. }
152. /**
153. * Generate the excel file
154. * @param string $filename Name of excel file to generate (...xls)
155. */
156. public function generateXML ($filename = 'excel-export')
157. {
158. // correct/validate filename
159. $filename = preg_replace('/[^aA-zZ0-9\_\-]/', '', $filename);

160. // deliver header (as recommended in php manual)

161. header("Content-Type: application/vnd.ms-excel; charset=" . $this->sEncoding);
162. header("Content-Disposition: inline; filename=\"" . $filename . ".xls\"");

163. // print out document to the browser

164. // need to use stripslashes for the damn ">"
165. echo stripslashes (sprintf($this->header, $this->sEncoding));
166. echo "\nsWorksheetTitle . "\">\n\n";
167. foreach ($this->lines as $line)
168. echo $line;

169. echo "

\n\n";
170. echo $this->footer;
171. }
172. }
173. ?>

复制代码