1: <?php
  2: /**
  3:  * Magento
  4:  *
  5:  * NOTICE OF LICENSE
  6:  *
  7:  * This source file is subject to the Open Software License (OSL 3.0)
  8:  * that is bundled with this package in the file LICENSE.txt.
  9:  * It is also available through the world-wide-web at this URL:
 10:  * http://opensource.org/licenses/osl-3.0.php
 11:  * If you did not receive a copy of the license and are unable to
 12:  * obtain it through the world-wide-web, please send an email
 13:  * to license@magentocommerce.com so we can send you a copy immediately.
 14:  *
 15:  * DISCLAIMER
 16:  *
 17:  * Do not edit or add to this file if you wish to upgrade Magento to newer
 18:  * versions in the future. If you wish to customize Magento for your
 19:  * needs please refer to http://www.magentocommerce.com for more information.
 20:  *
 21:  * @category    Mage
 22:  * @package     Mage_Dataflow
 23:  * @copyright   Copyright (c) 2012 Magento Inc. (http://www.magentocommerce.com)
 24:  * @license     http://opensource.org/licenses/osl-3.0.php  Open Software License (OSL 3.0)
 25:  */
 26: 
 27: 
 28: /**
 29:  * Convert excel xml parser
 30:  *
 31:  * @category   Mage
 32:  * @package    Mage_Dataflow
 33:  * @author      Magento Core Team <core@magentocommerce.com>
 34:  */
 35: class Mage_Dataflow_Model_Convert_Parser_Xml_Excel extends Mage_Dataflow_Model_Convert_Parser_Abstract
 36: {
 37:     /**
 38:      * Simple Xml object
 39:      *
 40:      * @var SimpleXMLElement
 41:      */
 42:     protected $_xmlElement;
 43: 
 44:     /**
 45:      * Field list
 46:      *
 47:      * @var array
 48:      */
 49:     protected $_parseFieldNames;
 50: 
 51:     public function parse()
 52:     {
 53:         $adapterName   = $this->getVar('adapter', null);
 54:         $adapterMethod = $this->getVar('method', 'saveRow');
 55: 
 56:         if (!$adapterName || !$adapterMethod) {
 57:             $message = Mage::helper('dataflow')->__('Please declare "adapter" and "method" nodes first.');
 58:             $this->addException($message, Mage_Dataflow_Model_Convert_Exception::FATAL);
 59:             return $this;
 60:         }
 61: 
 62:         try {
 63:             $adapter = Mage::getModel($adapterName);
 64:         }
 65:         catch (Exception $e) {
 66:             $message = Mage::helper('dataflow')->__('Declared adapter %s was not found.', $adapterName);
 67:             $this->addException($message, Mage_Dataflow_Model_Convert_Exception::FATAL);
 68:             return $this;
 69:         }
 70: 
 71:         if (!is_callable(array($adapter, $adapterMethod))) {
 72:             $message = Mage::helper('dataflow')->__('Method "%s" was not defined in adapter %s.', $adapterMethod, $adapterName);
 73:             $this->addException($message, Mage_Dataflow_Model_Convert_Exception::FATAL);
 74:             return $this;
 75:         }
 76: 
 77:         $batchModel = $this->getBatchModel();
 78:         $batchIoAdapter = $this->getBatchModel()->getIoAdapter();
 79: 
 80:         if (Mage::app()->getRequest()->getParam('files')) {
 81:             $file = Mage::app()->getConfig()->getTempVarDir().'/import/'
 82:                 . urldecode(Mage::app()->getRequest()->getParam('files'));
 83:             $this->_copy($file);
 84:         }
 85: 
 86:         $batchIoAdapter->open(false);
 87: 
 88:         $isFieldNames = $this->getVar('fieldnames', '') == 'true' ? true : false;
 89:         if (!$isFieldNames && is_array($this->getVar('map'))) {
 90:             $this->_parseFieldNames = $this->getVar('map');
 91:         }
 92: 
 93:         $worksheet = $this->getVar('single_sheet', '');
 94: 
 95:         $xmlString = $xmlRowString = '';
 96:         $countRows = 0;
 97:         $isWorksheet = $isRow = false;
 98:         while (($xmlOriginalString = $batchIoAdapter->read()) !== false) {
 99:             $xmlString .= $xmlOriginalString;
100:             if (!$isWorksheet) {
101:                 $strposS = strpos($xmlString, '<Worksheet');
102:                 $substrL = 10;
103:                 //fix for OpenOffice
104:                 if ($strposS === false) {
105:                     $strposS = strpos($xmlString, '<ss:Worksheet');
106:                     $substrL = 13;
107:                 }
108:                 if ($strposS === false) {
109:                     $xmlString = substr($xmlString, -13);
110:                     continue;
111:                 }
112: 
113:                 $xmlTmpString = substr($xmlString, $strposS);
114:                 $strposF = strpos($xmlTmpString, '>');
115: 
116:                 if ($strposF === false) {
117:                     $xmlString = $xmlTmpString;
118:                     continue;
119:                 }
120: 
121:                 if (!$worksheet) {
122:                     $xmlString = substr($xmlTmpString, $strposF);
123:                     $isWorksheet = true;
124:                     continue;
125:                 }
126:                 else {
127:                     if (preg_match('/ss:Name=\"'.preg_quote($worksheet).'\"/siU', substr($xmlTmpString, 0, $strposF))) {
128:                         $xmlString = substr($xmlTmpString, $strposF);
129:                         $isWorksheet = true;
130:                         continue;
131:                     }
132:                     else {
133:                         $xmlString = '';
134:                         continue;
135:                     }
136:                 }
137:             }
138:             else {
139:                 $xmlString = $this->_parseXmlRow($xmlString);
140: 
141:                 $strposS = strpos($xmlString, '</Worksheet>');
142:                 $substrL = 12;
143:                 //fix for OpenOffice
144:                 if ($strposS === false) {
145:                     $strposS = strpos($xmlString, '</ss:Worksheet>');
146:                     $substrL = 15;
147:                 }
148:                 if ($strposS !== false) {
149:                     $xmlString = substr($xmlString, $strposS + $substrL);
150:                     $isWorksheet = false;
151: 
152:                     continue;
153:                 }
154:             }
155:         }
156: 
157:         $this->addException(Mage::helper('dataflow')->__('Found %d rows.', $this->_countRows));
158:         $this->addException(Mage::helper('dataflow')->__('Starting %s :: %s', $adapterName, $adapterMethod));
159: 
160:         $batchModel->setParams($this->getVars())
161:             ->setAdapter($adapterName)
162:             ->save();
163: 
164: //        $adapter->$adapterMethod();
165: 
166:         return $this;
167: 
168:         $dom = new DOMDocument();
169: //        $dom->loadXML($this->getData());
170:         if (Mage::app()->getRequest()->getParam('files')) {
171:             $path = Mage::app()->getConfig()->getTempVarDir().'/import/';
172:             $file = $path.urldecode(Mage::app()->getRequest()->getParam('files'));
173:             if (file_exists($file)) {
174:                 $dom->load($file);
175:             }
176:         } else {
177: 
178:             $this->validateDataString();
179:             $dom->loadXML($this->getData());
180:         }
181: 
182:         $worksheets = $dom->getElementsByTagName('Worksheet');
183:         if ($this->getVar('adapter') && $this->getVar('method')) {
184:             $adapter = Mage::getModel($this->getVar('adapter'));
185:         }
186:         foreach ($worksheets as $worksheet) {
187:             $wsName = $worksheet->getAttribute('ss:Name');
188:             $rows = $worksheet->getElementsByTagName('Row');
189:             $firstRow = true;
190:             $fieldNames = array();
191:             $wsData = array();
192:             $i = 0;
193:             foreach ($rows as $rowSet) {
194:                 $index = 1;
195:                 $cells = $rowSet->getElementsByTagName('Cell');
196:                 $rowData = array();
197:                 foreach ($cells as $cell) {
198:                     $value = $cell->getElementsByTagName('Data')->item(0)->nodeValue;
199:                     $ind = $cell->getAttribute('ss:Index');
200:                     if (!is_null($ind) && $ind>0) {
201:                         $index = $ind;
202:                     }
203:                     if ($firstRow && !$this->getVar('fieldnames')) {
204:                         $fieldNames[$index] = 'column'.$index;
205:                     }
206:                     if ($firstRow && $this->getVar('fieldnames')) {
207:                         $fieldNames[$index] = $value;
208:                     } else {
209:                         $rowData[$fieldNames[$index]] = $value;
210:                     }
211:                     $index++;
212:                 }
213:                 $row = $rowData;
214:                 if ($row) {
215:                     $loadMethod = $this->getVar('method');
216:                     $adapter->$loadMethod(compact('i', 'row'));
217:                 }
218:                 $i++;
219: 
220:                 $firstRow = false;
221:                 if (!empty($rowData)) {
222:                     $wsData[] = $rowData;
223:                 }
224:             }
225:             $data[$wsName] = $wsData;
226:             $this->addException('Found worksheet "'.$wsName.'" with '.sizeof($wsData).' row(s)');
227:         }
228:         if ($wsName = $this->getVar('single_sheet')) {
229:             if (isset($data[$wsName])) {
230:                 $data = $data[$wsName];
231:             } else {
232:                 reset($data);
233:                 $data = current($data);
234:             }
235:         }
236:         $this->setData($data);
237:         return $this;
238:     }
239: 
240:     /**
241:      * Parse MS Excel XML string
242:      *
243:      * @param string $xmlString
244:      * @return string
245:      */
246:     protected function _parseXmlRow($xmlString)
247:     {
248:         $found = true;
249:         while ($found === true) {
250:             $strposS = strpos($xmlString, '<Row');
251: 
252:             if ($strposS === false) {
253:                 $found = false;
254:                 continue;
255:             }
256: 
257:             $xmlTmpString = substr($xmlString, $strposS);
258:             $strposF = strpos($xmlTmpString, '</Row>');
259: 
260:             if ($strposF !== false) {
261:                 $xmlRowString = substr($xmlTmpString, 0, $strposF + 6);
262: 
263:                 $this->_saveParsedRow($xmlRowString);
264: 
265:                 $xmlString = substr($xmlTmpString, $strposF + 6);
266:             }
267:             else {
268:                 $found = false;
269:                 continue;
270:             }
271:         }
272: 
273:         return $xmlString;
274:     }
275: 
276:     protected function _saveParsedRow($xmlString)
277:     {
278:         $xml = '<'.'?xml version="1.0"?'.'><'.'?mso-application progid="Excel.Sheet"?'
279:             .'><Workbook'
280:             .' xmlns="urn:schemas-microsoft-com:office:spreadsheet"'
281:             .' xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"'
282:             .' xmlns:x="urn:schemas-microsoft-com:office:excel"'
283:             .' xmlns:x2="http://schemas.microsoft.com/office/excel/2003/xml"'
284:             .' xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"'
285:             .' xmlns:o="urn:schemas-microsoft-com:office:office"'
286:             .' xmlns:html="http://www.w3.org/TR/REC-html40"'
287:             .' xmlns:c="urn:schemas-microsoft-com:office:component:spreadsheet">'
288:             . $xmlString
289:             .'</Workbook>';
290: 
291:         try {
292:             $xmlElement = new SimpleXMLElement($xml);
293:         }
294:         catch (Exception $e) {
295:             $message = 'Invalid XML row';
296:             $this->addException($message, Mage_Dataflow_Model_Convert_Exception::ERROR);
297:             return $this;
298:         }
299: 
300:         $xmlData  = array();
301:         $itemData = array();
302:         $cellIndex = 0;
303:         foreach ($xmlElement->Row->children() as $cell) {
304:             if (is_null($this->_parseFieldNames)) {
305:                 $xmlData[(string)$cell->Data] = (string)$cell->Data;
306:             } else {
307:                 $attributes = $cell->attributes('urn:schemas-microsoft-com:office:spreadsheet');
308:                 if ($attributes && isset($attributes['Index'])) {
309:                     $cellIndex = $attributes['Index'] - 1;
310:                 }
311:                 $xmlData[$cellIndex] = (string)$cell->Data;
312:                 $cellIndex ++;
313:             }
314:         }
315: 
316:         if (is_null($this->_parseFieldNames)) {
317:             $this->_parseFieldNames = $xmlData;
318:             return $this;
319:         }
320: 
321:         $this->_countRows ++;
322: 
323:         $i = 0;
324:         foreach ($this->_parseFieldNames as $field) {
325:             $itemData[$field] = isset($xmlData[$i]) ? $xmlData[$i] : null;
326:             $i ++;
327:         }
328: 
329:         $batchImportModel = $this->getBatchImportModel()
330:             ->setId(null)
331:             ->setBatchId($this->getBatchModel()->getId())
332:             ->setBatchData($itemData)
333:             ->setStatus(1)
334:             ->save();
335: 
336:         return $this;
337:     }
338: 
339:     public function unparse()
340:     {
341:         $batchExport = $this->getBatchExportModel()
342:             ->setBatchId($this->getBatchModel()->getId());
343:         $fieldList = $this->getBatchModel()->getFieldList();
344:         $batchExportIds = $batchExport->getIdCollection();
345: 
346:         if (!is_array($batchExportIds)) {
347:             return $this;
348:         }
349: 
350:         $io = $this->getBatchModel()->getIoAdapter();
351:         $io->open();
352: 
353:         $xml = '<'.'?xml version="1.0"?'.'><'.'?mso-application progid="Excel.Sheet"?'
354:             .'><Workbook'
355:             .' xmlns="urn:schemas-microsoft-com:office:spreadsheet"'
356:             .' xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"'
357:             .' xmlns:x="urn:schemas-microsoft-com:office:excel"'
358:             .' xmlns:x2="http://schemas.microsoft.com/office/excel/2003/xml"'
359:             .' xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"'
360:             .' xmlns:o="urn:schemas-microsoft-com:office:office"'
361:             .' xmlns:html="http://www.w3.org/TR/REC-html40"'
362:             .' xmlns:c="urn:schemas-microsoft-com:office:component:spreadsheet">'
363:             .'<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">'
364:             .'</OfficeDocumentSettings>'
365:             .'<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">'
366:             .'</ExcelWorkbook>';
367:         $io->write($xml);
368: 
369:         $wsName = htmlspecialchars($this->getVar('single_sheet'));
370:         $wsName = !empty($wsName) ? $wsName : Mage::helper('dataflow')->__('Sheet 1');
371: 
372:         $xml = '<Worksheet ss:Name="' . $wsName . '"><Table>';
373:         $io->write($xml);
374: 
375:         if ($this->getVar('fieldnames')) {
376:             $xml = $this->_getXmlString($fieldList);
377:             $io->write($xml);
378:         }
379: 
380:         foreach ($batchExportIds as $batchExportId) {
381:             $xmlData = array();
382:             $batchExport->load($batchExportId);
383:             $row = $batchExport->getBatchData();
384: 
385:             foreach ($fieldList as $field) {
386:                 $xmlData[] = isset($row[$field]) ? $row[$field] : '';
387:             }
388:             $xmlData = $this->_getXmlString($xmlData);
389:             $io->write($xmlData);
390:         }
391: 
392:         $xml = '</Table></Worksheet></Workbook>';
393:         $io->write($xml);
394:         $io->close();
395: 
396:         return $this;
397: 
398: //        if ($wsName = $this->getVar('single_sheet')) {
399: //            $data = array($wsName => $this->getData());
400: //        } else {
401: //            $data = $this->getData();
402: //        }
403: //
404: //        $this->validateDataGrid();
405: //
406: //        $xml = '<'.'?xml version="1.0"?'.'><'.'?mso-application progid="Excel.Sheet"?'.'><Workbook'
407: //            .' xmlns="urn:schemas-microsoft-com:office:spreadsheet"'
408: //            .' xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"'
409: //            .' xmlns:x="urn:schemas-microsoft-com:office:excel"'
410: //            .' xmlns:x2="http://schemas.microsoft.com/office/excel/2003/xml"'
411: //            .' xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"'
412: //            .' xmlns:o="urn:schemas-microsoft-com:office:office"'
413: //            .' xmlns:html="http://www.w3.org/TR/REC-html40"'
414: //            .' xmlns:c="urn:schemas-microsoft-com:office:component:spreadsheet">'
415: //            .'<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">'
416: //            .'</OfficeDocumentSettings>'
417: //            .'<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">'
418: //            .'</ExcelWorkbook>';
419: //
420: //        if (is_array($data)) {
421: //            foreach ($data as $wsName=>$wsData) {
422: //                if (!is_array($wsData)) {
423: //                    continue;
424: //                }
425: //                $fields = $this->getGridFields($wsData);
426: //
427: //                $xml .= '<ss:Worksheet ss:Name="'.$wsName.'"><Table>';
428: //                if ($this->getVar('fieldnames')) {
429: //                    $xml .= '<ss:Row>';
430: //                    foreach ($fields as $fieldName) {
431: //                        $xml .= '<ss:Cell><Data ss:Type="String">'.$fieldName.'</Data></ss:Cell>';
432: //                    }
433: //                    $xml .= '</ss:Row>';
434: //                }
435: //                foreach ($wsData as $i=>$row) {
436: //                    if (!is_array($row)) {
437: //                        continue;
438: //                    }
439: //                    $xml .= '<ss:Row>';
440: //                    foreach ($fields as $fieldName) {
441: //                        $data = isset($row[$fieldName]) ? $row[$fieldName] : '';
442: //                        $xml .= '<ss:Cell><Data ss:Type="String">'.$data.'</Data></ss:Cell>';
443: //                    }
444: //                    $xml .= '</ss:Row>';
445: //                }
446: //                $xml .= '</Table></ss:Worksheet>';
447: //            }
448: //        }
449: //
450: //        $xml .= '</Workbook>';
451: //
452: //        $this->setData($xml);
453: //
454: //        return $this;
455:     }
456: 
457:     /**
458:      * Prepare and return XML string for MS Excel XML from array
459:      *
460:      * @param array $fields
461:      * @return string
462:      */
463:     protected function _getXmlString(array $fields = array())
464:     {
465:         $xmlHeader = '<?xml version="1.0"?>' . "\n";
466:         $xmlRegexp = '/^<cell><row>(.*)?<\/row><\/cell>\s?$/ms';
467: 
468:         if (is_null($this->_xmlElement)) {
469:             $xmlString = $xmlHeader . '<cell><row></row></cell>';
470:             $this->_xmlElement = new SimpleXMLElement($xmlString, LIBXML_NOBLANKS);
471:         }
472: 
473:         $xmlData = array();
474:         $xmlData[] = '<Row>';
475:         foreach ($fields as $value) {
476:             $this->_xmlElement->row = htmlspecialchars($value);
477:             $value = str_replace($xmlHeader, '', $this->_xmlElement->asXML());
478:             $value = preg_replace($xmlRegexp, '\\1', $value);
479:             if (is_numeric($value)) {
480:                 $value = trim($value);
481:                 $dataType = 'Number';
482:             } else {
483:                 $dataType = 'String';
484:             }
485:             $value = str_replace(array("\r\n", "\r", "\n"), '&#10;', $value);
486: 
487:             $xmlData[] = '<Cell><Data ss:Type="' . $dataType . '">' . $value . '</Data></Cell>';
488:         }
489:         $xmlData[] = '</Row>';
490: 
491:         return join('', $xmlData);
492:     }
493: }
494: