/Main_Page

::You must have ninja focus to complete your mission::NinjaFocus::

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>


Main Menu

Personal tools

Toolbox