Home Backend Development PHP Tutorial Classic example of phpexcel exporting excel

Classic example of phpexcel exporting excel

Jul 25, 2016 am 08:56 AM

  1. /**
  2. * phpexcel class library to export excel files
  3. * edit: bbs.it-home.org
  4. *
  5. */
  6. require 'php-excel.class.php' ;//Refer to Google's phpexcel class
  7. $result = mysql_query("SELECT * FROM dingdan"); //Query a data table and return a record set
  8. $data = array( 1 => array ('Order number', 'Line name'),);
  9. while($row = mysql_fetch_array($result)){//Loop the query results to EXCEL
  10. array_push($data,array($row["dd_bh"], $row ["dd_xianlu_name"]));
  11. }
  12. // generate file (constructor parameters are optional)
  13. $xls = new Excel_XML('GB2312', false, 'Financial Statement');
  14. $xls->addArray($data );
  15. $xls->generateXML('2011010320');
  16. ?>
Copy code

Attached is the phpexcel class php-excel.class.php file code.

  1. class Excel_XML

  2. {
  3. /**
  4. * Header (of document)
  5. * @var string
  6. */
  7. private $header = "/n";
  8. /**
  9. * Footer (of document)
  10. * @var string
  11. */
  12. private $footer = "";
  13. /**
  14. * Lines to output in the excel document
  15. * @var array
  16. */
  17. private $lines = array();
  18. /**
  19. * Used encoding
  20. * @var string
  21. */
  22. private $sEncoding;
  23. /**
  24. * Convert variable types
  25. * @var boolean
  26. */
  27. private $bConvertTypes;
  28. /**
  29. * Worksheet title
  30. * @var string
  31. */
  32. private $sWorksheetTitle;
  33. /**
  34. * Constructor
  35. *
  36. * The constructor allows the setting of some additional
  37. * parameters so that the library may be configured to
  38. * one's needs.
  39. *
  40. * On converting types:
  41. * When set to true, the library tries to identify the type of
  42. * the variable value and set the field specification for Excel
  43. * accordingly. Be careful with article numbers or postcodes
  44. * starting with a '0' (zero)!
  45. *
  46. * @param string $sEncoding Encoding to be used (defaults to GBK)
  47. * @param boolean $bConvertTypes Convert variables to field specification
  48. * @param string $sWorksheetTitle Title for the worksheet
  49. */
  50. public function __construct($sEncoding = 'UTF-8', $bConvertTypes = false, $sWorksheetTitle = 'Table1')
  51. {
  52. $this->bConvertTypes = $bConvertTypes;
  53. $this->setEncoding($sEncoding);
  54. $this->setWorksheetTitle($sWorksheetTitle);
  55. }
  56. /**
  57. * Set encoding
  58. * @param string Encoding type to set
  59. */
  60. public function setEncoding($sEncoding)
  61. {
  62. $this->sEncoding = $sEncoding;
  63. }
  64. /**
  65. * Set worksheet title
  66. *
  67. * Strips out not allowed characters and trims the
  68. * title to a maximum length of 31.
  69. *
  70. * @param string $title Title for worksheet
  71. */
  72. public function setWorksheetTitle ($title)
  73. {
  74. $title = preg_replace ("/[///|:|//|/?|/*|/[|/]]/", "", $title);
  75. $title = substr ($title, 0, 31);
  76. $this->sWorksheetTitle = $title;
  77. }
  78. /**
  79. * Add row
  80. *
  81. * Adds a single row to the document. If set to true, self::bConvertTypes
  82. * checks the type of variable and returns the specific field settings
  83. * for the cell.
  84. *
  85. * @param array $array One-dimensional array with row content
  86. */
  87. private function addRow ($array)
  88. {
  89. $cells = "";
  90. foreach ($array as $k => $v):
  91. $type = 'String';
  92. if ($this->bConvertTypes === true && is_numeric($v)):
  93. $type = 'Number';
  94. endif;
  95. $v = htmlentities($v, ENT_COMPAT, $this->sEncoding);
  96. $cells .= "" . $v . "/n";
  97. endforeach;
  98. $this->lines[] = "/n" . $cells . "/n";
  99. }
  100. /**
  101. * Add an array to the document
  102. * @param array 2-dimensional array
  103. */
  104. public function addArray ($array)
  105. {
  106. foreach ($array as $k => $v)
  107. $this->addRow ($v);
  108. }

  109. /**

  110. * Generate the excel file
  111. * @param string $filename Name of excel file to generate (...xls)
  112. */
  113. public function generateXML ($filename = 'excel-export')
  114. {
  115. // correct/validate filename
  116. $filename = preg_replace('/[^aA-zZ0-9/_/-]/', '', $filename);
  117. // deliver header (as recommended in php manual)
  118. header("Content-Type: application/vnd.ms-excel; charset=" . $this->sEncoding);
  119. header("Content-Disposition: inline; filename=/"" . $filename . ".xls/"");
  120. // print out document to the browser
  121. // need to use stripslashes for the damn ">"
  122. echo stripslashes (sprintf($this->header, $this->sEncoding));
  123. echo "/nsWorksheetTitle . "/">/n/n";
  124. foreach ($this->lines as $line)
  125. echo $line;
  126. echo "
  127. /n
    /n";
  128. echo $this->footer;
  129. }
  130. }
  131. ?>

复制代码


Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Hot Topics

Java Tutorial
1664
14
PHP Tutorial
1268
29
C# Tutorial
1246
24
PHP and Python: Comparing Two Popular Programming Languages PHP and Python: Comparing Two Popular Programming Languages Apr 14, 2025 am 12:13 AM

PHP and Python each have their own advantages, and choose according to project requirements. 1.PHP is suitable for web development, especially for rapid development and maintenance of websites. 2. Python is suitable for data science, machine learning and artificial intelligence, with concise syntax and suitable for beginners.

PHP in Action: Real-World Examples and Applications PHP in Action: Real-World Examples and Applications Apr 14, 2025 am 12:19 AM

PHP is widely used in e-commerce, content management systems and API development. 1) E-commerce: used for shopping cart function and payment processing. 2) Content management system: used for dynamic content generation and user management. 3) API development: used for RESTful API development and API security. Through performance optimization and best practices, the efficiency and maintainability of PHP applications are improved.

Explain secure password hashing in PHP (e.g., password_hash, password_verify). Why not use MD5 or SHA1? Explain secure password hashing in PHP (e.g., password_hash, password_verify). Why not use MD5 or SHA1? Apr 17, 2025 am 12:06 AM

In PHP, password_hash and password_verify functions should be used to implement secure password hashing, and MD5 or SHA1 should not be used. 1) password_hash generates a hash containing salt values ​​to enhance security. 2) Password_verify verify password and ensure security by comparing hash values. 3) MD5 and SHA1 are vulnerable and lack salt values, and are not suitable for modern password security.

How does PHP handle file uploads securely? How does PHP handle file uploads securely? Apr 10, 2025 am 09:37 AM

PHP handles file uploads through the $\_FILES variable. The methods to ensure security include: 1. Check upload errors, 2. Verify file type and size, 3. Prevent file overwriting, 4. Move files to a permanent storage location.

PHP: A Key Language for Web Development PHP: A Key Language for Web Development Apr 13, 2025 am 12:08 AM

PHP is a scripting language widely used on the server side, especially suitable for web development. 1.PHP can embed HTML, process HTTP requests and responses, and supports a variety of databases. 2.PHP is used to generate dynamic web content, process form data, access databases, etc., with strong community support and open source resources. 3. PHP is an interpreted language, and the execution process includes lexical analysis, grammatical analysis, compilation and execution. 4.PHP can be combined with MySQL for advanced applications such as user registration systems. 5. When debugging PHP, you can use functions such as error_reporting() and var_dump(). 6. Optimize PHP code to use caching mechanisms, optimize database queries and use built-in functions. 7

How does PHP type hinting work, including scalar types, return types, union types, and nullable types? How does PHP type hinting work, including scalar types, return types, union types, and nullable types? Apr 17, 2025 am 12:25 AM

PHP type prompts to improve code quality and readability. 1) Scalar type tips: Since PHP7.0, basic data types are allowed to be specified in function parameters, such as int, float, etc. 2) Return type prompt: Ensure the consistency of the function return value type. 3) Union type prompt: Since PHP8.0, multiple types are allowed to be specified in function parameters or return values. 4) Nullable type prompt: Allows to include null values ​​and handle functions that may return null values.

The Enduring Relevance of PHP: Is It Still Alive? The Enduring Relevance of PHP: Is It Still Alive? Apr 14, 2025 am 12:12 AM

PHP is still dynamic and still occupies an important position in the field of modern programming. 1) PHP's simplicity and powerful community support make it widely used in web development; 2) Its flexibility and stability make it outstanding in handling web forms, database operations and file processing; 3) PHP is constantly evolving and optimizing, suitable for beginners and experienced developers.

PHP vs. Python: Understanding the Differences PHP vs. Python: Understanding the Differences Apr 11, 2025 am 12:15 AM

PHP and Python each have their own advantages, and the choice should be based on project requirements. 1.PHP is suitable for web development, with simple syntax and high execution efficiency. 2. Python is suitable for data science and machine learning, with concise syntax and rich libraries.

See all articles