MS Excel XML PHP Class
Views:
A PHP class for creating microsoft excel XML format documents. I doubt i implemented very much functionality in it, but a useful starting point perhaps?
msExcelXml PHP Class
<?php
/* Copyright kieran whitbread 2005, 2006 */
class msExcelXml
{
// Requires domxml extension in php4
// This class will NOT work under php5 as it has new xml extensions.
// Based on office xp excel xml format - looks good for the beta MS Open Office SpreadsheetML
// ATTRIBUTES:
var $doc = NULL;
var $documentProperties = NULL;
var $excelWorkbook = NULL;
var $officeDocumentSettings = NULL;
var $styles = NULL;
var $workbook = NULL;
var $worksheets = array();
var $xmlnsAttr = 'xmlns';
var $xmlnsVal = 'urn:schemas-microsoft-com:office:excel';
// CONSTRUCTOR:
function msExcelXml()
{
$this->__construct();
}
function __construct()
{
$this->newWorkbook();
}
// METHODS:
function dump()
{
// For your debugging pleasure
print '<pre>'. htmlentities($this->doc->dump_mem(true)).'</pre>';
}
function appendElement($element, $newElement, $contents='', $attrs=array())
// Private
{
if (is_object($element) && $newElement)
{
if (!is_object($newElement) && is_string($newElement))
{
$newElement = $this->doc->create_element($newElement);
}
else
{
return false;
}
if ($attrs && is_array($attrs))
{
foreach ($attrs as $name => $value)
{
$newElement->set_attribute($name, $value);
}
}
if ($contents)
{
if (is_string($contents))
{
$contents = $this->doc->create_text_node($contents);
}
if (is_object($contents))
{
$newElement->append_child($contents);
}
}
return $element->append_child($newElement);
}
return false;
}
function delDocumentProperty($property)
{
$elements = $this->documentProperties->get_elements_by_tagname($property);
if ($elements)
{
return $this->documentProperties->remove_child($elements[0]);
}
else
{
return false;
}
}
function delOfficeDocumentSetting($setting)
{
$elements = $this->officeDocumentSettings->get_elements_by_tagname($setting);
if ($elements)
{
return $this->officedocumentSettings->remove_child($elements[0]);
}
else
{
return false;
}
}
function getDocumentProperty($property)
{
$elements = $this->documentProperties->get_elements_by_tagname($property);
if ($elements)
{
return $elements[0]->get_content();
}
else
{
return false;
}
}
function getOfficeDocumentSetting($setting)
{
$elements = $this->officeDocumentSettings->get_elements_by_tagname($setting);
if ($elements)
{
return $elements[0]->get_content();
}
else
{
return false;
}
}
function newWorkBook()
// Private
{
$this->doc = domxml_new_doc('1.0');
$this->workbook = $this->doc->add_root('Workbook');
$this->workbook->set_attribute('xmlns', 'urn:schemas-microsoft-com:office:spreadsheet');
$this->workbook->set_attribute('xmlns:o', 'urn:schemas-microsoft-com:office:office');
$this->workbook->set_attribute('xmlns:x', 'urn:schemas-microsoft-com:office:excel');
$this->workbook->set_attribute('xmlns:html', 'http://www.w3.org/TR/REC-html40');
$this->workbook->set_attribute('xmlns:ss', 'urn:schemas-microsoft-com:office:spreadsheet');
$this->documentProperties = $this->appendElement(
$this->workbook,
'DocumentProperties',
NULL,
array($this->xmlnsAttr => $this->xmlnsVal)
);
$this->officeDocumentSettings = $this->appendElement(
$this->workbook,
'OfficeDocumentSettings',
NULL,
array($this->xmlnsAttr => $this->xmlnsVal)
);
$this->excelWorkbook = $this->appendElement(
$this->workbook,
'ExcelWorkbook',
NULL,
array($this->xmlnsAttr => $this->xmlnsVal)
);
$this->styles = $this->appendElement($this->workbook, 'Styles');
$this->newWorksheet();
}
function newWorksheet($name='')
{
if (!$name)
{
$name = 'Sheet'.(count($this->worksheets) + 1);
}
if (!array_key_exists($name, $this->worksheets))
{
$worksheet = $this->appendElement($this->workbook, 'Worksheet', NULL, array('ss:Name' => $name));
$table = $this->appendElement(
$worksheet,
'Table',
NULL,
array(
'ss:DefaultColumnWidth' => "65.0",
'ss:DefaultRowHeight' => "12.0",
)
);
$worksheetOptions = $this->appendElement(
$worksheet,
'WorksheetOptions',
NULL,
array($this->xmlnsAttr => $this->xmlnsVal)
);
$this->worksheets[$name] = array(
'worksheet' => $worksheet,
'table' => $table,
'worksheetOptions' => $worksheetOptions,
);
return $name;
}
else
{
return false;
}
}
function newRow($worksheetName, $cells=array(), $index='')
{
if (array_key_exists($worksheetName, $this->worksheets))
{
$rows = $this->worksheets[$worksheetName]['table']->get_elements_by_tagname('Row');
if (!$index || !is_numeric($index))
{
if ($rows)
{
$index = count($rows) + 1;
}
else
{
$index = 1;
}
}
$row = $this->doc->create_element('Row');
if ($rows)
{
$preceedingRow = $rows[$index - 1];
$preceedingRow->append_sibling($row);
}
else
{
$this->worksheets[$worksheetName]['table']->append_child($row);
}
return $index;
}
return false;
}
function setDocumentProperty($property, $value)
{
$value = $this->doc->create_text_node($value);
$elements = $this->documentProperties->get_elements_by_tagname($property);
if ($elements)
{
$oldValue = $elements[0]->first_child();
$elements[0]->replace_child($value, $oldValue);
}
else
{
$property = $this->doc->create_element($property);
$property->append_child($value);
$this->documentProperties->append_child($property);
}
}
function setOfficeDocumentSetting($setting, $value)
{
$value = $this->doc->create_text_node($value);
$elements = $officeDocumentSettings->get_elements_by_tagname($setting);
if ($elements)
{
$oldValue = $elements[0]->first_child();
$elements[0]->replace_child($value, $oldValue);
}
else
{
$setting = $this->doc->create_element($setting);
$setting->append_child($value);
$this->officeDocumentSettings->append_child($setting);
}
}
}
?>
Example Usage Creating an Excel XML FIle in PHP
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
<title>Excel XML Prototype</title>
</head>
<?php
require_once 'msExcelXml.class.php';
$excelxml = new msExcelXml();
$excelxml->setDocumentProperty('Author','Kieran Whitbread');
$excelxml->setDocumentProperty('Author','kraftycuts');
echo '<p>'.$excelxml->getDocumentProperty('Author').'</p>';
$excelxml->delDocumentProperty('Author');
$excelxml->newRow('Sheet1');
$excelxml->newRow('Sheet1');
$excelxml->newRow('Sheet1');
$excelxml->dump();
?>
<body>
</body>
</html>
