当前位置：Gxlcms > PHP教程 > php导出excel表格综合示例

php导出excel表格综合示例

时间：2021-07-01 10:21:17 帮助过：16人阅读

  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 "

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

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！