00001 <?php
00002
00003
00004
00005
00006
00007
00008
00009
00010
00011
00012
00013
00014
00015
00016
00017
00018
00019
00020
00021
00022
00023
00024
00025
00026
00027
00028
00029
00030
00031
00032
00033
00034
00035 class Mage_Dataflow_Model_Convert_Parser_Xml_Excel extends Mage_Dataflow_Model_Convert_Parser_Abstract
00036 {
00037
00038
00039
00040
00041
00042 protected $_xmlElement;
00043
00044
00045
00046
00047
00048
00049 protected $_parseFieldNames;
00050
00051 public function parse()
00052 {
00053 $adapterName = $this->getVar('adapter', null);
00054 $adapterMethod = $this->getVar('method', 'saveRow');
00055
00056 if (!$adapterName || !$adapterMethod) {
00057 $message = Mage::helper('dataflow')->__('Please declare "adapter" and "method" node first');
00058 $this->addException($message, Mage_Dataflow_Model_Convert_Exception::FATAL);
00059 return $this;
00060 }
00061
00062 try {
00063 $adapter = Mage::getModel($adapterName);
00064 }
00065 catch (Exception $e) {
00066 $message = Mage::helper('dataflow')->__('Declared adapter %s not found', $adapterName);
00067 $this->addException($message, Mage_Dataflow_Model_Convert_Exception::FATAL);
00068 return $this;
00069 }
00070
00071 if (!is_callable(array($adapter, $adapterMethod))) {
00072 $message = Mage::helper('dataflow')->__('Method "%s" not defined in adapter %s', $adapterMethod, $adapterName);
00073 $this->addException($message, Mage_Dataflow_Model_Convert_Exception::FATAL);
00074 return $this;
00075 }
00076
00077 $batchModel = $this->getBatchModel();
00078 $batchIoAdapter = $this->getBatchModel()->getIoAdapter();
00079
00080 if (Mage::app()->getRequest()->getParam('files')) {
00081 $file = Mage::app()->getConfig()->getTempVarDir().'/import/'
00082 . urldecode(Mage::app()->getRequest()->getParam('files'));
00083 $this->_copy($file);
00084 }
00085
00086 $batchIoAdapter->open(false);
00087
00088 $isFieldNames = $this->getVar('fieldnames', '') == 'true' ? true : false;
00089 if (!$isFieldNames && is_array($this->getVar('map'))) {
00090 $this->_parseFieldNames = $this->getVar('map');
00091 }
00092
00093 $worksheet = $this->getVar('single_sheet', '');
00094
00095 $xmlString = $xmlRowString = '';
00096 $countRows = 0;
00097 $isWorksheet = $isRow = false;
00098 while (($xmlOriginalString = $batchIoAdapter->read()) !== false) {
00099 $xmlString .= $xmlOriginalString;
00100 if (!$isWorksheet) {
00101 $strposS = strpos($xmlString, '<Worksheet');
00102 $substrL = 10;
00103
00104 if ($strposS === false) {
00105 $strposS = strpos($xmlString, '<ss:Worksheet');
00106 $substrL = 13;
00107 }
00108 if ($strposS === false) {
00109 $xmlString = substr($xmlString, -13);
00110 continue;
00111 }
00112
00113 $xmlTmpString = substr($xmlString, $strposS);
00114 $strposF = strpos($xmlTmpString, '>');
00115
00116 if ($strposF === false) {
00117 $xmlString = $xmlTmpString;
00118 continue;
00119 }
00120
00121 if (!$worksheet) {
00122 $xmlString = substr($xmlTmpString, $strposF);
00123 $isWorksheet = true;
00124 continue;
00125 }
00126 else {
00127 if (preg_match('/ss:Name=\"'.preg_quote($worksheet).'\"/siU', substr($xmlTmpString, 0, $strposF))) {
00128 $xmlString = substr($xmlTmpString, $strposF);
00129 $isWorksheet = true;
00130 continue;
00131 }
00132 else {
00133 $xmlString = '';
00134 continue;
00135 }
00136 }
00137 }
00138 else {
00139 $xmlString = $this->_parseXmlRow($xmlString);
00140
00141 $strposS = strpos($xmlString, '</Worksheet>');
00142 $substrL = 12;
00143
00144 if ($strposS === false) {
00145 $strposS = strpos($xmlString, '</ss:Worksheet>');
00146 $substrL = 15;
00147 }
00148 if ($strposS !== false) {
00149 $xmlString = substr($xmlString, $strposS + $substrL);
00150 $isWorksheet = false;
00151
00152 continue;
00153 }
00154 }
00155 }
00156
00157 $this->addException(Mage::helper('dataflow')->__('Found %d rows', $this->_countRows));
00158 $this->addException(Mage::helper('dataflow')->__('Starting %s :: %s', $adapterName, $adapterMethod));
00159
00160 $batchModel->setParams($this->getVars())
00161 ->setAdapter($adapterName)
00162 ->save();
00163
00164
00165
00166 return $this;
00167
00168 $dom = new DOMDocument();
00169
00170 if (Mage::app()->getRequest()->getParam('files')) {
00171 $path = Mage::app()->getConfig()->getTempVarDir().'/import/';
00172 $file = $path.urldecode(Mage::app()->getRequest()->getParam('files'));
00173 if (file_exists($file)) {
00174 $dom->load($file);
00175 }
00176 } else {
00177
00178 $this->validateDataString();
00179 $dom->loadXML($this->getData());
00180 }
00181
00182 $worksheets = $dom->getElementsByTagName('Worksheet');
00183 if ($this->getVar('adapter') && $this->getVar('method')) {
00184 $adapter = Mage::getModel($this->getVar('adapter'));
00185 }
00186 foreach ($worksheets as $worksheet) {
00187 $wsName = $worksheet->getAttribute('ss:Name');
00188 $rows = $worksheet->getElementsByTagName('Row');
00189 $firstRow = true;
00190 $fieldNames = array();
00191 $wsData = array();
00192 $i = 0;
00193 foreach ($rows as $rowSet) {
00194 $index = 1;
00195 $cells = $rowSet->getElementsByTagName('Cell');
00196 $rowData = array();
00197 foreach ($cells as $cell) {
00198 $value = $cell->getElementsByTagName('Data')->item(0)->nodeValue;
00199 $ind = $cell->getAttribute('ss:Index');
00200 if (!is_null($ind) && $ind>0) {
00201 $index = $ind;
00202 }
00203 if ($firstRow && !$this->getVar('fieldnames')) {
00204 $fieldNames[$index] = 'column'.$index;
00205 }
00206 if ($firstRow && $this->getVar('fieldnames')) {
00207 $fieldNames[$index] = $value;
00208 } else {
00209 $rowData[$fieldNames[$index]] = $value;
00210 }
00211 $index++;
00212 }
00213 $row = $rowData;
00214 if ($row) {
00215 $loadMethod = $this->getVar('method');
00216 $adapter->$loadMethod(compact('i', 'row'));
00217 }
00218 $i++;
00219
00220 $firstRow = false;
00221 if (!empty($rowData)) {
00222 $wsData[] = $rowData;
00223 }
00224 }
00225 $data[$wsName] = $wsData;
00226 $this->addException('Found worksheet "'.$wsName.'" with '.sizeof($wsData).' row(s)');
00227 }
00228 if ($wsName = $this->getVar('single_sheet')) {
00229 if (isset($data[$wsName])) {
00230 $data = $data[$wsName];
00231 } else {
00232 reset($data);
00233 $data = current($data);
00234 }
00235 }
00236 $this->setData($data);
00237 return $this;
00238 }
00239
00240
00241
00242
00243
00244
00245
00246 protected function _parseXmlRow($xmlString)
00247 {
00248 $found = true;
00249 while ($found === true) {
00250 $strposS = strpos($xmlString, '<Row');
00251
00252 if ($strposS === false) {
00253 $found = false;
00254 continue;
00255 }
00256
00257 $xmlTmpString = substr($xmlString, $strposS);
00258 $strposF = strpos($xmlTmpString, '</Row>');
00259
00260 if ($strposF !== false) {
00261 $xmlRowString = substr($xmlTmpString, 0, $strposF + 6);
00262
00263 $this->_saveParsedRow($xmlRowString);
00264
00265 $xmlString = substr($xmlTmpString, $strposF + 6);
00266 }
00267 else {
00268 $found = false;
00269 continue;
00270 }
00271 }
00272
00273 return $xmlString;
00274 }
00275
00276 protected function _saveParsedRow($xmlString)
00277 {
00278 $xml = '<'.'?xml version="1.0"?'.'><'.'?mso-application progid="Excel.Sheet"?'
00279 .'><Workbook'
00280 .' xmlns="urn:schemas-microsoft-com:office:spreadsheet"'
00281 .' xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"'
00282 .' xmlns:x="urn:schemas-microsoft-com:office:excel"'
00283 .' xmlns:x2="http://schemas.microsoft.com/office/excel/2003/xml"'
00284 .' xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"'
00285 .' xmlns:o="urn:schemas-microsoft-com:office:office"'
00286 .' xmlns:html="http://www.w3.org/TR/REC-html40"'
00287 .' xmlns:c="urn:schemas-microsoft-com:office:component:spreadsheet">'
00288 . $xmlString
00289 .'</Workbook>';
00290
00291 try {
00292 $xmlElement = new SimpleXMLElement($xml);
00293 }
00294 catch (Exception $e) {
00295 $message = 'Invalid XML row';
00296 $this->addException($message, Mage_Dataflow_Model_Convert_Exception::ERROR);
00297 return $this;
00298 }
00299
00300 $xmlData = array();
00301 $itemData = array();
00302 $cellIndex = 0;
00303 foreach ($xmlElement->Row->children() as $cell) {
00304 if (is_null($this->_parseFieldNames)) {
00305 $xmlData[(string)$cell->Data] = (string)$cell->Data;
00306 }
00307 else {
00308 if (($attributes = $cell->attributes('urn:schemas-microsoft-com:office:spreadsheet')) && isset($attributes['Index'])) {
00309 $cellIndex = $attributes['Index'] - 1;
00310 }
00311 $xmlData[$cellIndex] = (string)$cell->Data;
00312 $cellIndex ++;
00313 }
00314 }
00315
00316 if (is_null($this->_parseFieldNames)) {
00317 $this->_parseFieldNames = $xmlData;
00318 return $this;
00319 }
00320
00321 $this->_countRows ++;
00322
00323 $i = 0;
00324 foreach ($this->_parseFieldNames as $field) {
00325 $itemData[$field] = isset($xmlData[$i]) ? $xmlData[$i] : null;
00326 $i ++;
00327 }
00328
00329 $batchImportModel = $this->getBatchImportModel()
00330 ->setId(null)
00331 ->setBatchId($this->getBatchModel()->getId())
00332 ->setBatchData($itemData)
00333 ->setStatus(1)
00334 ->save();
00335
00336 return $this;
00337 }
00338
00339 public function unparse()
00340 {
00341 $batchExport = $this->getBatchExportModel()
00342 ->setBatchId($this->getBatchModel()->getId());
00343 $fieldList = $this->getBatchModel()->getFieldList();
00344 $batchExportIds = $batchExport->getIdCollection();
00345
00346 if (!$batchExportIds) {
00347 return $this;
00348 }
00349
00350 $io = $this->getBatchModel()->getIoAdapter();
00351 $io->open();
00352
00353 $xml = '<'.'?xml version="1.0"?'.'><'.'?mso-application progid="Excel.Sheet"?'
00354 .'><Workbook'
00355 .' xmlns="urn:schemas-microsoft-com:office:spreadsheet"'
00356 .' xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"'
00357 .' xmlns:x="urn:schemas-microsoft-com:office:excel"'
00358 .' xmlns:x2="http://schemas.microsoft.com/office/excel/2003/xml"'
00359 .' xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"'
00360 .' xmlns:o="urn:schemas-microsoft-com:office:office"'
00361 .' xmlns:html="http://www.w3.org/TR/REC-html40"'
00362 .' xmlns:c="urn:schemas-microsoft-com:office:component:spreadsheet">'
00363 .'<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">'
00364 .'</OfficeDocumentSettings>'
00365 .'<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">'
00366 .'</ExcelWorkbook>';
00367 $io->write($xml);
00368
00369 $wsName = htmlspecialchars($this->getVar('single_sheet'));
00370 $wsName = !empty($wsName) ? $wsName : Mage::helper('dataflow')->__('Sheet 1');
00371
00372 $xml = '<Worksheet ss:Name="' . $wsName . '"><Table>';
00373 $io->write($xml);
00374
00375 if ($this->getVar('fieldnames')) {
00376 $xml = $this->_getXmlString($fieldList);
00377 $io->write($xml);
00378 }
00379
00380 foreach ($batchExportIds as $batchExportId) {
00381 $xmlData = array();
00382 $batchExport->load($batchExportId);
00383 $row = $batchExport->getBatchData();
00384
00385 foreach ($fieldList as $field) {
00386 $xmlData[] = isset($row[$field]) ? $row[$field] : '';
00387 }
00388 $xmlData = $this->_getXmlString($xmlData);
00389 $io->write($xmlData);
00390 }
00391
00392 $xml = '</Table></Worksheet></Workbook>';
00393 $io->write($xml);
00394 $io->close();
00395
00396 return $this;
00397
00398
00399
00400
00401
00402
00403
00404
00405
00406
00407
00408
00409
00410
00411
00412
00413
00414
00415
00416
00417
00418
00419
00420
00421
00422
00423
00424
00425
00426
00427
00428
00429
00430
00431
00432
00433
00434
00435
00436
00437
00438
00439
00440
00441
00442
00443
00444
00445
00446
00447
00448
00449
00450
00451
00452
00453
00454
00455 }
00456
00457
00458
00459
00460
00461
00462
00463 protected function _getXmlString(array $fields = array())
00464 {
00465 $xmlHeader = '<'.'?xml version="1.0"?'.'>' . "\n";
00466 $xmlRegexp = '/^<cell><row>(.*)?<\/row><\/cell>\s?$/ms';
00467
00468 if (is_null($this->_xmlElement)) {
00469 $xmlString = $xmlHeader . '<cell><row></row></cell>';
00470 $this->_xmlElement = new SimpleXMLElement($xmlString, LIBXML_NOBLANKS);
00471 }
00472
00473 $xmlData = array();
00474 $xmlData[] = '<Row>';
00475 foreach ($fields as $value) {
00476 $this->_xmlElement->row = htmlspecialchars($value);
00477 $value = str_replace($xmlHeader, '', $this->_xmlElement->asXML());
00478 $value = preg_replace($xmlRegexp, '\\1', $value);
00479 $dataType = "String";
00480 if (is_numeric($value)) {
00481 $dataType = "Number";
00482 }
00483 $value = str_replace("\r\n", ' ', $value);
00484 $value = str_replace("\r", ' ', $value);
00485 $value = str_replace("\n", ' ', $value);
00486
00487 $xmlData[] = '<Cell><Data ss:Type="'.$dataType.'">'.$value.'</Data></Cell>';
00488 }
00489 $xmlData[] = '</Row>';
00490
00491 return join('', $xmlData);
00492 }
00493 }