Classic example of phpexcel exporting excel-PHP Tutorial-php.cn

Home

Backend Development

PHP Tutorial

Classic example of phpexcel exporting excel

Jul 25, 2016 am 08:56 AM

<?php require_once('conn.php');//Link database?>
<?php
/**
* phpexcel class library to export excel files
* edit: bbs.it-home.org
*
*/
require 'php-excel.class.php' ;//Refer to Google's phpexcel class
$result = mysql_query("SELECT * FROM dingdan"); //Query a data table and return a record set
$data = array( 1 => array ('Order number', 'Line name'),);
while($row = mysql_fetch_array($result)){//Loop the query results to EXCEL
array_push($data,array($row["dd_bh"], $row ["dd_xianlu_name"]));
}
// generate file (constructor parameters are optional)
$xls = new Excel_XML('GB2312', false, 'Financial Statement');
$xls->addArray($data );
$xls->generateXML('2011010320');
?>

Copy code

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

<p><?php
class Excel_XML
{
/**
* Header (of document)
* @var string
*/
private $header = "<?xml version=/"1.0/" encoding=/"%s/"?/>/n<Workbook xmlns=/"urn:schemas-microsoft-com:office:spreadsheet/" xmlns:x=/"urn:schemas-microsoft-com:office:excel/" xmlns:ss=/"urn:schemas-microsoft-com:office:spreadsheet/" xmlns:html=/"http://www.w3.org/TR/REC-html40/">";
/**
* Footer (of document)
* @var string
*/
private $footer = "</Workbook>";
/**
* Lines to output in the excel document
* @var array
*/
private $lines = array();
/**
* Used encoding
* @var string
*/
private $sEncoding;
/**
* Convert variable types
* @var boolean
*/
private $bConvertTypes;
/**
* Worksheet title
* @var string
*/
private $sWorksheetTitle;
/**
* Constructor
*
* The constructor allows the setting of some additional
* parameters so that the library may be configured to
* one's needs.
*
* On converting types:
* When set to true, the library tries to identify the type of
* the variable value and set the field specification for Excel
* accordingly. Be careful with article numbers or postcodes
* starting with a '0' (zero)!
*
* @param string $sEncoding Encoding to be used (defaults to GBK)
* @param boolean $bConvertTypes Convert variables to field specification
* @param string $sWorksheetTitle Title for the worksheet
*/
public function __construct($sEncoding = 'UTF-8', $bConvertTypes = false, $sWorksheetTitle = 'Table1')
{
$this->bConvertTypes = $bConvertTypes;
$this->setEncoding($sEncoding);
$this->setWorksheetTitle($sWorksheetTitle);
}
/**
* Set encoding
* @param string Encoding type to set
*/
public function setEncoding($sEncoding)
{
$this->sEncoding = $sEncoding;
}
/**
* Set worksheet title
*
* Strips out not allowed characters and trims the
* title to a maximum length of 31.
*
* @param string $title Title for worksheet
*/
public function setWorksheetTitle ($title)
{
$title = preg_replace ("/[///|:|//|/?|/*|/[|/]]/", "", $title);
$title = substr ($title, 0, 31);
$this->sWorksheetTitle = $title;
}
/**
* Add row
*
* Adds a single row to the document. If set to true, self::bConvertTypes
* checks the type of variable and returns the specific field settings
* for the cell.
*
* @param array $array One-dimensional array with row content
*/
private function addRow ($array)
{
$cells = "";
foreach ($array as $k => $v):
$type = 'String';
if ($this->bConvertTypes === true && is_numeric($v)):
$type = 'Number';
endif;
$v = htmlentities($v, ENT_COMPAT, $this->sEncoding);
$cells .= "<Cell><Data ss:Type=/"$type/">" . $v . "</Data></Cell>/n";
endforeach;
$this->lines[] = "<Row>/n" . $cells . "</Row>/n";
}
/**
* Add an array to the document
* @param array 2-dimensional array
*/
public function addArray ($array)
{
foreach ($array as $k => $v)
$this->addRow ($v);
}</p>
<p> /**
* Generate the excel file
* @param string $filename Name of excel file to generate (...xls)
*/
public function generateXML ($filename = 'excel-export')
{
// correct/validate filename
$filename = preg_replace('/[^aA-zZ0-9/_/-]/', '', $filename);
// deliver header (as recommended in php manual)
header("Content-Type: application/vnd.ms-excel; charset=" . $this->sEncoding);
header("Content-Disposition: inline; filename=/"" . $filename . ".xls/"");
// print out document to the browser
// need to use stripslashes for the damn ">"
echo stripslashes (sprintf($this->header, $this->sEncoding));
echo "/n<Worksheet ss:Name=/"" . $this->sWorksheetTitle . "/">/n<Table>/n";
foreach ($this->lines as $line)
echo $line;
echo "</Table>/n</Worksheet>/n";
echo $this->footer;
}
}
?></p>

复制代码

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