php-excel —— PHP 生成 Excel 类
前段时间在弄问卷系统，为了能够实现问卷数据自动统计并生成一份 Excel 文档，找了个易用的 PHP 生成 Excel 的类 —— php-excel （项目地址：https://code.google.com/p/php-excel/）
这个类很小，代码不到两百行，很适合新手阅读。
要使用它首先到项目地址下载 php-excel.class.php 这个文件，目前最新版是v1.1(php-excel-v1.1-20090910.zip, 挺古老了的)。考虑到可能会打不开项目地址，所以我在下面附上 php-excel.class.php 的所有代码吧。
<?php
/**
* Simple excel generating from PHP5
*
* @package Utilities
* @license http://www.opensource.org/licenses/mit-license.php
* @author Oliver Schwarz <[email protected]>
* @version 1.0
*/
/**
* Generating excel documents on-the-fly from PHP5
*
* Uses the excel XML-specification to generate a native
* XML document, readable/processable by excel.
*
* @package Utilities
* @subpackage Excel
* @author Oliver Schwarz <[email protected]>
* @version 1.1
*
* @todo Issue #4: Internet Explorer 7 does not work well with the given header
* @todo Add option to give out first line as header (bold text)
* @todo Add option to give out last line as footer (bold text)
* @todo Add option to write to file
*/
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 UTF-8)
* @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);
}
/**
* 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;
}
}
?>首先在你的页面中引用 php-excel 类的文件：
require 'php-excel.class.php';新建一个对象
$xls = new Excel_XML();Excel_XML(); 的可选参数有三个：
- 编码。
$xls = new Excel_XML('UTF-8');//设置成 UTF-8 编码2.类型检查
$xls = new Excel_XML('UTF-8', true);//true 表示需要验证类型，false （默认值）表示不需要验证但是我发现好像这两个参数生成的 xls 文件没什么不同。
3.工作表名
$xls = new Excel_XML('UTF-8', true, 'Testsheet');//设置工作表名为 Testsheet效果如下图
![工作表名][1]
好了，新建了对象之后就要往里面加内容了。
添加的数据必须是二维数组，即使你要生成的 xls 文件里只有一行！一维或者多维数组会报错！
$datas = array(0 => array(0 => "第一列", 1 => "第二列", 2 => "第三列", 3 => "第四列") );上面的代码表示只有一行、有四列数据，效果如下图
如果需要更多行，则继续添加 $datas[1][]、$datas[2][] 等等。
$datas = array(0 => array(0 => "第一行第一列", 1 => "第一行第二列", 2 => "第一行第三列", 3 => "第一行第四列"),1 => array(0 => "第二行第一列", 1 => "第二行第二列", 2 => "第二行第三列", 3 => "第二行第四列"),2 => array(0 => "第三行第一列", 1 => "第三行第二列", 2 => "第三行第三列", 3 => "第三行第四列") );然后再用下面这句代码把它加入到对象中：
$xls->addArray($datas);有时候我们的数据是动态增加的，那怎么办呢？很简单，直接把你的新数据（也必须是二维数组）用相同的办法把它追加到对象中去：
$xls->addArray($datas);//新增/追加数据都是用这句在某些时候，我们不能抹掉 $datas 原有的数据，但是直接追加到 $datas[3][] 再把它加到对象中的话，你会发现 $datas[0][]、$datas[1][]、$datas[2][] 这几行重复了。这时候我们可以用别的名字的变量，只要是二维数组就行了
$xls->addArray($new_datas);好了，万事具备，只差东风
$xls->generateXML('my-xls');//这样，浏览器就会弹出一个下载，可以下载文件名为 my-xls.xls 的文件了php-excel 类里已经帮我们设置 Content-Type: application/vnd.ms-excel ，所以在你生成 xls 文件的页面里不要有其他的输出，以免造成一些不要的麻烦。我们可以把生成 xls 的 PHP 文件命名为 download.php 什么的，单纯用来下载 xls 文件。
