PHP生成EXCEL，可区分数值与字符串

 

这是个在别人的基础上做修改的代码，添加了 判断单元格值是否为数值，是的话，将该单元格的类型转成 Number，避免生产的EXCEL中没法进行数值的加减。。。

 

 

<?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 MyClass_ExcelXML { /** * 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 (); /** * 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 //2009-5-28 bai_huo add : if the value is a number,set the type "Number", else set the type "String" foreach ($array as $k => $v): if(is_numeric($v)) { $type="Number"; } else { $type="String"; } $cells .= "<Cell><Data ss:Type=/"$type/">" . $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=GBK"); header("Content-Disposition: inline; filename=/"" . $filename . ".xls/""); // 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/" ss:Width=/"110/"/>/n"; echo implode ("/n", $this->lines); echo "</Table>/n</Worksheet>/n"; echo $this->footer; } } ?>