*/
class Varien_Convert_Parser_Xml_Excel extends Varien_Convert_Parser_Abstract
{
/**
* XML instance for a cell data
*
* @var SimpleXMLElement
*/
protected $_xmlElement;
public function parse()
{
$this->validateDataString();
$dom = new DOMDocument();
$dom->loadXML($this->getData());
$worksheets = $dom->getElementsByTagName('Worksheet');
foreach ($worksheets as $worksheet) {
$wsName = $worksheet->getAttribute('ss:Name');
$rows = $worksheet->getElementsByTagName('Row');
$firstRow = true;
$fieldNames = array();
$wsData = array();
foreach ($rows as $row) {
$index = 1;
$cells = $row->getElementsByTagName('Cell');
$rowData = array();
foreach ($cells as $cell) {
$value = $cell->getElementsByTagName('Data')->item(0)->nodeValue;
$ind = $cell->getAttribute('ss:Index');
if (!is_null($ind) && $ind>0) {
$index = $ind;
}
if ($firstRow && !$this->getVar('fieldnames')) {
$fieldNames[$index] = 'column'.$index;
}
if ($firstRow && $this->getVar('fieldnames')) {
$fieldNames[$index] = $value;
} else {
$rowData[$fieldNames[$index]] = $value;
}
$index++;
}
$firstRow = false;
if (!empty($rowData)) {
$wsData[] = $rowData;
}
}
$data[$wsName] = $wsData;
$this->addException('Found worksheet "'.$wsName.'" with '.sizeof($wsData).' row(s)');
}
if ($wsName = $this->getVar('single_sheet')) {
if (isset($data[$wsName])) {
$data = $data[$wsName];
} else {
reset($data);
$data = current($data);
}
}
$this->setData($data);
return $this;
}
public function unparse()
{
if ($wsName = $this->getVar('single_sheet')) {
$data = array($wsName => $this->getData());
} else {
$data = $this->getData();
}
$this->validateDataGrid();
$xml = '<'.'?xml version="1.0"?'.'><'.'?mso-application progid="Excel.Sheet"?'.'>
';
if (is_array($data)) {
foreach ($data as $wsName=>$wsData) {
if (!is_array($wsData)) {
continue;
}
$fields = $this->getGridFields($wsData);
$xml .= '';
if ($this->getVar('fieldnames')) {
$xml .= '';
foreach ($fields as $fieldName) {
$xml .= ''.$fieldName.'';
}
$xml .= '';
}
foreach ($wsData as $i=>$row) {
if (!is_array($row)) {
continue;
}
$xml .= '';
foreach ($fields as $fieldName) {
$data = isset($row[$fieldName]) ? $row[$fieldName] : '';
$fieldType = is_numeric($data) ? 'Number' : 'String';
$xml .= '' . $data . '';
}
$xml .= '';
}
$xml .= '';
}
}
$xml .= '';
$this->setData($xml);
return $this;
}
/**
* Retrieve Excel 2003 XML Document header XML fragment
*
* @param string $sheetName the Worksheet name
* @return string
*/
public function getHeaderXml($sheetName = '')
{
if (empty($sheetName)) {
$sheetName = 'Sheet 1';
}
$sheetName = htmlspecialchars($sheetName);
$xml = '<'.'?xml version="1.0"?'.'><'.'?mso-application progid="Excel.Sheet"?'
. '>'
. ''
. ''
. ''
. ''
. ''
. '';
return $xml;
}
/**
* Retrieve Excel 2003 XML Document footer XML fragment
*
* @return string
*/
public function getFooterXml()
{
return '
';
}
/**
* Convert an array to Excel 2003 XML Document a Row XML fragment
*
* @param array $row
* @return string
*/
public function getRowXml(array $row)
{
$xmlHeader = '<'.'?xml version="1.0"?'.'>' . "\n";
$xmlRegexp = '/^(.*)?<\/row><\/cell>\s?$/ms';
if (is_null($this->_xmlElement)) {
$xmlString = $xmlHeader . '|
| ';
$this->_xmlElement = new SimpleXMLElement($xmlString, LIBXML_NOBLANKS);
}
$xmlData = array();
$xmlData[] = '';
foreach ($row as $value) {
$this->_xmlElement->row = htmlspecialchars($value);
$value = str_replace($xmlHeader, '', $this->_xmlElement->asXML());
$value = preg_replace($xmlRegexp, '\\1', $value);
$dataType = "String";
if (is_numeric($value)) {
$dataType = "Number";
// is_numeric(' 96000') returns true, but Excel argues about space
$value = trim($value);
}
$value = str_replace("\r\n", '
', $value);
$value = str_replace("\r", '
', $value);
$value = str_replace("\n", '
', $value);
$xmlData[] = '| '.$value.' | ';
}
$xmlData[] = ' ';
return join('', $xmlData);
}
}
|