phpexcel导出excel的经典实例

发布时间:2020-01-31编辑:脚本学堂
本文介绍一个使用phpexcel类库导出excel的例子,用来学习phpexcel的使用方法还是不错的,有兴趣的朋友建议研究下。

phpexcel导出excel
例子:
 

复制代码 代码示例:
<?php require_once('conn.php');//链接数据库 ?>
<?php
/**
* phpexcel类库导出excel文件
* edit: www.jb200.com
*
*/
require 'php-excel.class.php';//引用谷歌的phpexcel 类
$result = mysql_query("SELECT  *  FROM dingdan"); //查询一个数据表,并返回记录集
$data = array( 1 => array ('订单编号', '线路名称'),);
while($row = mysql_fetch_array($result)){//把查询的结果循环输出到EXCEL中
array_push($data,array($row["dd_bh"], $row["dd_xianlu_name"]));
}
// generate file (constructor parameters are optional)
$xls = new Excel_XML('GB2312', false, '财务报表');
$xls->addArray($data);
$xls->generateXML('2011010320');
?>

附,phpexcel 类 php-excel.class.php 文件代码。
 

复制代码 代码示例:

<?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);
        }

        /**
         * 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;
        }
}
?>

您可能感兴趣的文章:
PHPExcel常用方法举例
PHP导出EXCEL的简单范例 使用phpexcel类库导出excel
phpExcel类的使用方法分享
PHPExcel读取excel文件的例子
phpexcel类库实例 支持(excel2003 excel2007)
phpexcel导出数据的实例代码
phpexcel导入excel到数据库的代码
phpexcel快速开发指南(不错)
phpExcel中文帮助手册(知识点)
使用PHPExcel判别和格式化Excel中的日期格式的例子
phpexcel导出excel的颜色与网页中颜色不一致的解决方法
CI中使用PHPExcel导出数据到Excel