PHP-5.3向更高版本迁移系列博客使用的php-excel文件类

<?php
/**
 * Excel_XML
 */

/**
 * Class Excel_XML
 * 
 * A simple export library for dumping array data into an excel
 * readable format. Supports OpenOffice Calc as well.
 * 
 * @author    Oliver Schwarz <[e-mail]>
 */
class Excel_XML
{

        /**
         * MicrosoftXML Header for Excel
         * @var string
         */
        const sHeader = "<?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\">";

        /**
         * MicrosoftXML Footer for Excel
         * @var string
         */
        const sFooter = "</Workbook>";

        /**
         * Worksheet & Data
         * @var array
         */
        private $aWorksheetData;
        /**
         * Encoding to be used
         * @var string
         */
        private $sEncoding;

        /**
         * write xls file handle resouce
         *
         */
        private $rHandle = null;
        /**
         * Constructor
         *
         * Instanciates the class allowing a user-defined encoding.
         *
         * @param string $sEncoding Charset encoding to be used
         */
        public function __construct($sEncoding = 'UTF-8')
        {
                $this->sEncoding = $sEncoding;
                $this->sOutput = '';
        }

        /**
         * Add a worksheet
         *
         * Creates a new worksheet and adds the given data to it.
         * @param string $title Title of worksheet
         * @param array $data 2-dimensional array of data
         */
        public function addWorksheet($title, $data)
        {
                $this->aWorksheetData[] = array(
                        'title' => $this->getWorksheetTitle($title),
                        'data'  => $data
                );
        }
        /**
         * Write workbook to file
         *
         * Writes the workbook into the file/path given as a parameters.
         * The method checks whether the directory is writable and the
         * file is not existing and writes the file.
         *
         * @param string $filename Filename to use for writing (must contain mimetype)
         * @param string $path Path to use for writing [optional]
         */
        public function writeWorkbook($filename, $path = '')
        {
                $filename = $this->getWorkbookTitle($filename);
                //open file check
                if (!$this->rHandle = fopen($path . $filename, 'w+'))
                {
                        throw new Exception(sprintf("Not allowed to write to file %s", $path . $filename));
                }
                //write header
                $sheader = stripslashes(sprintf(self::sHeader, $this->sEncoding)) . "\n";
                if (fwrite($this->rHandle, $sheader) === false)
                {
                        throw new Exception(sprintf("Error writing to file %s", $path . $filename));
                }
                //write coentent
                $this->generateWorkbook();
                //wirte footer
                if (fwrite($this->rHandle, self::sFooter) === false)
                {
                        throw new Exception(sprintf("Error writing to file %s", $path . $filename));
                }
                fclose($this->rHandle);
                return sprintf("File %s written", $path . $filename);
        }
        /**
         * Workbook title correction
         *
         * Corrects filename (if necessary) stripping out non-allowed
         * characters.
         *
         * @param string $filename Desired filename
         * @return string Corrected filename
         */
        private function getWorkbookTitle($filename)
        {
                return preg_replace('/[^aA-zZ0-9\_\-\.]/', '', $filename);
        }

        /**
         * Worksheet title correction
         *
         * Corrects the worksheet title (given by the user) by the allowed
         * characters by Excel.
         *
         * @param string $title Desired worksheet title
         * @return string Corrected worksheet title
         */
        private function getWorksheetTitle($title)
        {
                $title = preg_replace ("/[\\\|:|\/|\?|\*|\[|\]]/", "", $title);
                return substr ($title, 0, 31);
        }

        /**
         * Generate the workbook
         *
         * This is the main wrapper to generate the workbook.
         * It will invoke the creation of worksheets, rows and
         * columns.
         */
        private function generateWorkbook()
        {
                foreach ($this->aWorksheetData as $item):
                        $this->generateWorksheet($item);
                endforeach;
        }

        /**
         * Generate the Worksheet
         *
         * The second wrapper generates the worksheet. When the worksheet
         * data seems to be more than the excel allowed maximum lines, the
         * array is sliced.
         *
         * @param array $item Worksheet data
         * @todo Add a security check to testify whether this is an array
         */
        private function generateWorksheet($item)
        {
                $ssheet= sprintf("<Worksheet ss:Name=\"%s\">\n    <Table>\n", $item['title']);
                if (fwrite($this->rHandle, $ssheet) === false)
                {
                        throw new Exception(sprintf("Error writing to file"));
                }
                $i = 0;
                foreach ($item['data'] as $k => $v)
                {
                    if($i > 65536)
                    {
                        break;
                    }
                    $this->generateRow($v);
                    $i++;
                }
                if (fwrite($this->rHandle, "    </Table>\n</Worksheet>\n") === false)
                {
                        throw new Exception(sprintf("Error writing to file"));
                }
        }

        /**
         * Generate the single row
         * @param array Item with row data
         */
        private function generateRow($item)
        {
                $row = "        <Row>\n";
                foreach ($item as $k => $v)
                {
                        $row .= $this->generateCell($v);
                }
                $row .= "        </Row>\n";
                if (fwrite($this->rHandle, $row) === false)
                {
                        throw new Exception(sprintf("Error writing to file"));
                }
        }

        /**
         * Generate the single cell
         * @param string $item Cell data
         */
        private function generateCell($item)
        {
                $type = 'String';
                if (is_numeric($item))
                {
                        $type = 'Number';
                        if ($item{0} == '0' && strlen($item) > 1 && $item{1} != '.')
                        {
                                $type = 'String';
                        }
                }
                $item = str_replace('&#039;', '&apos;', htmlspecialchars($item, ENT_QUOTES));
                return sprintf("            <Cell><Data ss:Type=\"%s\">%s</Data></Cell>\n", $type, $item);
        }

        /**
         * Deconstructor
         * Resets the main variables/objects
         */
        public function __destruct()
        {
                unset($this->aWorksheetData);
                unset($this->sOutput);
        }
}