欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  后端开发

php导出excel表格综合示例

程序员文章站 2022-05-25 18:49:50
...
  1. include("config.php");

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

  9. $data = array(

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

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

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

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

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

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

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

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

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

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

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

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

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

  158. header("Content-Type: application/vnd.ms-excel; charset=" . $this->sEncoding);
  159. header("Content-Disposition: inline; filename=\"" . $filename . ".xls\"");
  160. // print out document to the browser

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

  167. \n
    \n";
  168. echo $this->footer;
  169. }
  170. }
  171. ?>
复制代码