前段时间在弄问卷系统,为了能够实现问卷数据自动统计并生成一份 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(); 的可选参数有三个:

  1. 编码。
$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 文件。

标签: php

添加新评论