php生成excel

1、用类生成

<?php
    /**
     * Simple excel generating from PHP5
     * 
     * This is one of my utility-classes.
     * 
     * The MIT License
     * 
     * Copyright (c) 2007 Oliver Schwarz
     * 
     * Permission is hereby granted, free of charge, to any person
     * obtaining a copy of this software and associated documentation
     * files (the "Software"), to deal in the Software without
     * restriction, including without limitation the rights to use,
     * copy, modify, merge, publish, distribute, sublicense, and/or
     * sell copies of the Software, and to permit persons to whom the
     * Software is furnished to do so, subject to the following
     * conditions:
     *
     * The above copyright notice and this permission notice shall be
     * included in all copies or substantial portions of the Software.
     * 
     * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
     * EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
     * OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
     * NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
     * HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
     * WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
     * FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
     * OTHER DEALINGS IN THE SOFTWARE.
     *
     * @package Utilities
     * @author Oliver Schwarz <oliver.schwarz@gmail.com>
     * @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 <oliver.schwarz@vaicon.de>
     * @version 1.0
     *
      * @todo Add error handling (array corruption etc.)
     * @todo Write a wrapper method to do everything on-the-fly
     */
    class ExcelNew
    {
     
        /**
         * Header of excel document (prepended to the rows)
         * 
         * Copied from the excel xml-specs.
         * 
         * @access private
         * @var string
         */
        private $header = "<?xml version=\"1.0\" encoding=\"GBK\"?\>
    <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 excel document (appended to the rows)
         * 
         * Copied from the excel xml-specs.
         * 
         * @access private
         * @var string
         */
        private $footer = "</Workbook>";
     
        /**
         * Document lines (rows in an array)
         * 
         * @access private
         * @var array
         */
        private $lines = array ();
        
        private $Index = 1;
     
        /**
         * Worksheet title
         *
         * Contains the title of a single worksheet
         *
         * @access private 
         * @var string
         */
        private $worksheet_title = "Table1";
     
        /**
         * Add a single row to the $document string
         * 
         * @access private
         * @param array 1-dimensional array
         * @todo Row-creation should be done by $this->addArray
         */
        private function addRow ($array)
        {
     
            // initialize all cells for this row
            $cells = "";
     
            // foreach key -> write value into cells
            foreach ($array as $k => $v):
     
                $cells .= "<Cell><Data ss:Type=\"String\">" . $v . "</Data></Cell>\n"; 
     
            endforeach;
     
            // transform $cells content into one row
            $this->lines[] = "<Row>\n" . $cells . "</Row>\n";
     
        }
     
        /**
         * Add an array to the document
         * 
         * This should be the only method needed to generate an excel
         * document.
         * 
         * @access public
         * @param array 2-dimensional array
         * @todo Can be transfered to __construct() later on
         */
        public function addArray ($array)
        {
     
            // run through the array and add them into rows
            foreach ($array as $k => $v):
                $this->addRow ($v);
            endforeach;
     
        }
     
        /**
         * Set the worksheet title
         * 
         * Checks the string for not allowed characters (:\/?*),
         * cuts it to maximum 31 characters and set the title. Damn
         * why are not-allowed chars nowhere to be found? Windows
         * help's no help...
         *
         * @access public
         * @param string $title Designed title
         */
        public function setWorksheetTitle ($title)
        {
     
            // strip out special chars first
            $title = preg_replace ("/[\\\|:|\/|\?|\*|\[|\]]/", "", $title);
     
            // now cut it to the allowed length
            $title = substr ($title, 0, 31);
     
            // set title
            $this->worksheet_title = $title;
     
        }
     
        /**
         * Generate the excel file
         * 
         * Finally generates the excel file and uses the header() function
         * to deliver it to the browser.
         * 
         * @access public
         * @param string $filename Name of excel file to generate (...xls)
         */
        function generateXML ($filename)
        {
     
            // deliver header (as recommended in php manual)
            header("Content-Type: application/vnd.ms-excel; charset=UTF-8");
            header("Content-Disposition: inline; filename=\"" . $filename . ".xls\"");
     
            //add by sun
            header("Content-Transfer-Encoding: binary"); 
            header("Pragma: public"); 
            header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); 
            // end
     
            // print out document to the browser
            // need to use stripslashes for the damn ">"
            echo stripslashes ($this->header);
            echo "\n<Worksheet ss:Name=\"" . $this->worksheet_title . "\">\n<Table>\n";
            echo "<Column ss:Index=\"1\" ss:AutoFitWidth=\"0\" />\n";
            echo implode ("\n", $this->lines);
            echo "</Table>\n</Worksheet>\n";
            echo $this->footer;
     
        }
        
        /**
        * Add an array to the document
         * 
         * This should be the only method needed to generate an excel
         * document.
         * 
         * @access public
         * @param array 2-dimensional array
         * @todo Can be transfered to __construct() later on
         */
        public function addallArray ($array)
        {
            // run through the array and add them into rows
            foreach ($array as $k => $v):
                $this->addallRow ($v);
            endforeach;
     
        }
        /*
            $arr = array(
                'row'=>array(
                    'cell'=>array(
                        'MergeAcross'=>'1',
                        'MergeDown'=>'3',
                        'Type'=>'Number',
                        'data'=>'网络房源总量',
                    ),
                ),
                'row'=>array(
                    'cell'=>array(
                        'data'=>'网络房源总量',
                    ),
                ),
            );
        */
        public function addUMERows($arr)
        {
            if(is_array($arr))
            {
                $cells = '';
                foreach($arr as $Row)
                {
                    foreach($Row as $Cell)
                    {
                        $type = $Cell['Type']?$Cell['Type']:'String';
                        $MergeAcross = $Cell['MergeAcross']?" ss:MergeAcross=\"".$Cell['MergeAcross']."\"":'';
                        $MergeDown = $Cell['MergeDown']?" ss:MergeDown=\"".$Cell['MergeDown']."\"":'';
                        $cells .= "<Cell".$MergeAcross.$MergeDown."><Data ss:Type=\"".$type."\">" . $Cell['data'] . "</Data></Cell>\n";
                    }
                    $this->lines[] = "<Row>\n" . $cells . "</Row>\n";
                    $cells = '';
                }
                
            }
        }
        /**
     * Add a single row to the $document string
         * 
         * @access private
         * @param array 1-dimensional array
         * @todo Row-creation should be done by $this->addArray
         */
        private function addallRow ($array)
        {
     
            // initialize all cells for this row
            $cells = "";
            $flag = false;
            // foreach key -> write value into cells
            foreach ($array as $k => $v):
                if (gettype($v) == 'array')
                {
                    foreach ($v as $k1 => $v1):
                       if ($flag)
                       {
                              $cells .= "<Cell ss:Index=\"".$this->Index."\"><Data ss:Type=\"String\">" . $v1 . "</Data></Cell>\n"; 
                              $flag = false;
                       }
                       else 
                       {
                               $cells .= "<Cell><Data ss:Type=\"String\">" . $v1 . "</Data></Cell>\n"; 
                       }
                       
                    endforeach;
                }
                else {
                     if ($v != "") 
                     {
                         if ($k =='mergedown' || $k == 'mergedown1' || $k == 'mergedown2' || $k == 'mergedown3')
                         {
                             $cells .= "<Cell ss:MergeDown=\"2\" ><Data ss:Type=\"String\">" . $v . "</Data></Cell>\n";
                         }
                         else 
                         {
                              $cells .= "<Cell><Data ss:Type=\"String\">" . $v . "</Data></Cell>\n"; 
                         }
                         
                         if ($k == 'mergedown')  $this->Index = 2;
                         if ($k == 'mergedown1') $this->Index = 3;
                         if ($k == 'mergedown2') $this->Index = 4;
                         if ($k == 'mergedown3') $this->Index = 5;
                         
                     }
                     else {
                         $flag = true;
                         
                     }
                }
     
            endforeach;
     
            // transform $cells content into one row
            $this->lines[] = "<Row>\n" . $cells . "</Row>\n"; 
     
        }
     
    }
     
    ?>

2.简单的输出excel文件

header("Content-type: application/vnd.ms-excel");
    header("Content-Disposition: attachment; filename=test.xml"); 
    header("Pragma:no-cache");
    header('Expires:0');
    echo "world"."\t";
    echo "\t\n";
      
    /*start of second line*/  
    echo "this is second line"."\t";  
    echo "Hi,pretty girl"."\t";  
    echo "\t\n";

Search result.

php 完整版获取客户端ip地址

PHP 二维数组根据某个字段排序