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

摘要：

 */
class Excel_XML
{

        /**
         * MicrosoftXML Header for Excel
         * @var string
         */
        const sHeader = "
";

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

        /**
         * 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)) . "
";
                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("
    
", $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, "    

") === false)
                {
                        throw new Exception(sprintf("Error writing to file"));
                }
        }

        /**
         * Generate the single row
         * @param array Item with row data
         */
        private function generateRow($item)
        {
                $row = "        
";
                foreach ($item as $k => $v)
                {
                        $row .= $this->generateCell($v);
                }
                $row .= "        
";
                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("'", "'", htmlspecialchars($item, ENT_QUOTES));
                return sprintf("            %s
", $type, $item);
        }

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