php 导出excel数据,php 导出数据到 Excel

Class  Excel_XML

/**

* 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

* @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

* @version 1.0

*

* @todo Add error handling (array corruption etc.)

* @todo Write a wrapper method to do everything on-the-fly

*/

class Excel_XML

{

/**

* 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=/"UTF-8/"?/>

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 = "

";

/**

* 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

foreach ($array as $k => $v):

$cells .= "" . utf8_encode($v) . "/n";

endforeach;

// transform $cells content into one row

$this->lines[] = "/n" . $cells . "/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/"");

// print out document to the browser

// need to use stripslashes for the damn ">"

echo stripslashes ($this->header);

echo "/nworksheet_title . "/">/n

echo "/n";

echo implode ("/n", $this->lines);

echo "

/n/n";

echo $this->footer;

}

}

?>

Test Example:

/**

* php-excel

*

* 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.

*/

// include the php-excel class

require (dirname (__FILE__) . "/class-excel-xml.inc.php");

// create a dummy array

$doc = array (

1 => array ("Oliver", "Peter", "Paul"),

array ("Marlene", "Lucy", "Lina")

);

// generate excel file

$xls = new Excel_XML;

$xls->addArray ( $doc );

$xls->generateXML ("mytest");

?>