Generic excel importer
authorMichael Spahn <m.spahn@metaways.de>
Fri, 30 Jun 2017 12:45:36 +0000 (14:45 +0200)
committerMichael Spahn <m.spahn@metaways.de>
Tue, 4 Jul 2017 11:25:42 +0000 (13:25 +0200)
Change-Id: Ida1f50dc96905b961775e41800dfa863a6e02a60
Reviewed-on: http://gerrit.tine20.com/customers/4986
Reviewed-by: Michael Spahn <m.spahn@metaways.de>
Tested-by: Michael Spahn <m.spahn@metaways.de>
tine20/Tinebase/Import/Xls/Abstract.php [new file with mode: 0644]
tine20/Tinebase/Import/Xls/Generic.php [new file with mode: 0644]
tine20/composer.json
tine20/composer.lock

diff --git a/tine20/Tinebase/Import/Xls/Abstract.php b/tine20/Tinebase/Import/Xls/Abstract.php
new file mode 100644 (file)
index 0000000..fc5ff5e
--- /dev/null
@@ -0,0 +1,144 @@
+<?php
+/**
+ * Tine 2.0
+ *
+ * @package     Tinebase
+ * @license     http://www.gnu.org/licenses/agpl.html AGPL Version 3
+ * @author      Michael Spahn <m.spahn@metaways.de>
+ * @copyright   Copyright (c) 2017 Metaways Infosystems GmbH (http://www.metaways.de)
+ */
+use PhpOffice\PhpSpreadsheet\Cell;
+use PhpOffice\PhpSpreadsheet\IOFactory;
+use PhpOffice\PhpSpreadsheet\Spreadsheet;
+use PhpOffice\PhpSpreadsheet\Worksheet;
+use PhpOffice\PhpSpreadsheet\Worksheet\Row;
+use PhpOffice\PhpSpreadsheet\Worksheet\RowIterator;
+
+/**
+ * @package     Tinebase
+ * @subpackage  Import
+ */
+abstract class Tinebase_Import_Xls_Abstract extends Tinebase_Import_Abstract
+{
+
+    /**
+     * Additional options
+     *
+     *  - sheet: define which sheet is supposed to be imported, the first sheet is 0
+     *
+     * @var array
+     */
+    protected $_additionalOptions = [
+        'sheet' => 0,
+        'startRow' => 2,
+        'endRow' => null,
+        'startColumn' => 'A',
+        'endColumn' => null,
+        'headlineRow' => null
+    ];
+
+    /**
+     * @var Spreadsheet
+     */
+    protected $_spreadsheet = null;
+
+    /**
+     * @var Worksheet
+     */
+    protected $_worksheet = null;
+
+    /**
+     * Set controller, wasn't brave enough to do it in the abstract :(
+     *
+     * Offertory_Import_OffertoryPlanXlsImport constructor.
+     * @param array $_options
+     */
+    public function __construct(array $_options = array())
+    {
+        parent::__construct($_options);
+        $this->_setController();
+    }
+
+    /**
+     * @param RowIterator $_resource
+     * @return array|boolean
+     */
+    protected function _getRawData(&$_resource)
+    {
+        if (false === $_resource->valid()) {
+            return false;
+        }
+
+        $row = $this->_rowToArray($_resource->current());
+        $_resource->next();
+
+        return $row;
+    }
+
+    /**
+     * Converts a row to a simple array
+     *
+     * @param Row $row
+     * @return array
+     */
+    protected function _rowToArray(Row $row)
+    {
+        $rowArray = [];
+
+        foreach ($row->getCellIterator($this->_options['startColumn'], $this->_options['endColumn']) as $cell) {
+            /* @var $cell Cell */
+            $rowArray[] = $cell->getValue();
+        }
+
+        return $rowArray;
+    }
+
+    public function importFile($_filename, $_clientRecordData = [])
+    {
+        if (!file_exists($_filename)) {
+            throw new Tinebase_Exception_NotFound("File $_filename not found.");
+        }
+
+        $this->_spreadsheet = IOFactory::load($_filename);
+        $this->_worksheet = $this->_spreadsheet->getSheet($this->_options['sheet']);
+        $iterator = $this->_worksheet->getRowIterator($this->_options['startRow'], $this->_options['endRow']);
+
+        return $this->import($iterator, $_clientRecordData);
+    }
+
+    /**
+     * @param  $_resource RowIterator
+     * @param array $_clientRecordData
+     * @return array
+     */
+    public function import($_resource = null, $_clientRecordData = [])
+    {
+        if (!($_resource instanceof RowIterator)) {
+            throw new InvalidArgumentException('Expected RowIterator as $_resource.');
+        }
+
+        if (Tinebase_Core::isLogLevel(Zend_Log::INFO)) {
+            Tinebase_Core::getLogger()->info(__METHOD__ . '::' . __LINE__
+                . ' Starting import of ' . ((!empty($this->_options['model'])) ? $this->_options['model'] . 's' : ' records'));
+        }
+
+        $this->_initImportResult();
+        $this->_beforeImport($_resource);
+        $this->_doImport($_resource, $_clientRecordData);
+        $this->_logImportResult();
+        $this->_afterImport();
+
+        return $this->_importResult;
+    }
+
+    /**
+     * Import from given data
+     *
+     * @param string $_data
+     * @param array $_clientRecordData
+     */
+    public function importData($_data, $_clientRecordData = [])
+    {
+        throw new Tinebase_Exception_NotImplemented('importData is not yet implemented.');
+    }
+}
diff --git a/tine20/Tinebase/Import/Xls/Generic.php b/tine20/Tinebase/Import/Xls/Generic.php
new file mode 100644 (file)
index 0000000..b219cf9
--- /dev/null
@@ -0,0 +1,74 @@
+<?php
+/**
+ * Tine 2.0
+ *
+ * @package     Tinebase
+ * @license     http://www.gnu.org/licenses/agpl.html AGPL Version 3
+ * @author      Michael Spahn <m.spahn@metaways.de>
+ * @copyright   Copyright (c) 2017 Metaways Infosystems GmbH (http://www.metaways.de)
+ */
+
+/**
+ * @package     Tinebase
+ * @subpackage  Import
+ */
+class Tinebase_Import_Xls_Generic extends Tinebase_Import_Xls_Abstract
+{
+    /**
+     * Used to store mapping information
+     *
+     * @var array
+     */
+    protected $_mapping = [];
+
+    /**
+     * creates a new importer from an importexport definition
+     *
+     * @param  Tinebase_Model_ImportExportDefinition $_definition
+     * @param  array $_config
+     * @return Tinebase_Import_Abstract
+     */
+    public static function createFromDefinition(
+        Tinebase_Model_ImportExportDefinition $_definition,
+        array $_config = []
+    ) {
+        return new Tinebase_Import_Xls_Generic(self::getOptionsArrayFromDefinition($_definition, $_config));
+    }
+
+    /**
+     * @param  $_resource
+     */
+    protected function _beforeImport($_resource = null)
+    {
+        if (null === $this->_options['headlineRow']) {
+            return;
+        }
+
+        $rowIterator = $this->_worksheet->getRowIterator($this->_options['headlineRow'],
+            $this->_options['headlineRow']);
+
+        foreach ($rowIterator->current()->getCellIterator($this->_options['startColumn'],
+            $this->_options['endColumn']) as $cell) {
+
+            /* @var $cell Cell */
+            $this->_mapping[] = $cell->getValue();
+        }
+    }
+
+    /**
+     * do the mapping and replacements
+     *
+     * @param array $_data
+     * @return array
+     */
+    protected function _doMapping($_data)
+    {
+        $mappedData = [];
+
+        foreach ($_data as $index => $data) {
+            $mappedData[$this->_mapping[$index]] = $data;
+        }
+
+        return $mappedData;
+    }
+}
index 7da5587..520342a 100644 (file)
@@ -34,7 +34,8 @@
         "metaways/timezoneconvert": "1.*",
         "zendframework/zend-http": "2.4.*",
         "doctrine/orm": "2.5.*",
-        "twig/twig": "~1.0"
+        "twig/twig": "~1.0",
+        "phpoffice/phpspreadsheet": "dev-master"
     },
     "require-dev": {
         "phpunit/phpunit": "3.7.*",
index 013a419..6d571a0 100644 (file)
@@ -4,8 +4,7 @@
         "Read more about it at https://getcomposer.org/doc/01-basic-usage.md#composer-lock-the-lock-file",
         "This file is @generated automatically"
     ],
-    "hash": "d98d732a2328e86c84f6fa8174b42e8d",
-    "content-hash": "d2b2517319121ffa041f6c40d8ab329c",
+    "content-hash": "1c9f4588350bdb69d46c2e0da0a44a9e",
     "packages": [
         {
             "name": "doctrine/annotations",
@@ -73,7 +72,7 @@
                 "docblock",
                 "parser"
             ],
-            "time": "2015-08-31 12:32:49"
+            "time": "2015-08-31T12:32:49+00:00"
         },
         {
             "name": "doctrine/cache",
                 "cache",
                 "caching"
             ],
-            "time": "2015-12-31 16:37:02"
+            "time": "2015-12-31T16:37:02+00:00"
         },
         {
             "name": "doctrine/collections",
                 "collections",
                 "iterator"
             ],
-            "time": "2015-04-14 22:21:58"
+            "time": "2015-04-14T22:21:58+00:00"
         },
         {
             "name": "doctrine/common",
                 "persistence",
                 "spl"
             ],
-            "time": "2015-12-25 13:18:31"
+            "time": "2015-12-25T13:18:31+00:00"
         },
         {
             "name": "doctrine/dbal",
                 "persistence",
                 "queryobject"
             ],
-            "time": "2016-01-05 22:11:12"
+            "time": "2016-01-05T22:11:12+00:00"
         },
         {
             "name": "doctrine/inflector",
                 "singularize",
                 "string"
             ],
-            "time": "2015-11-06 14:35:42"
+            "time": "2015-11-06T14:35:42+00:00"
         },
         {
             "name": "doctrine/instantiator",
                 "constructor",
                 "instantiate"
             ],
-            "time": "2015-06-14 21:17:01"
+            "time": "2015-06-14T21:17:01+00:00"
         },
         {
             "name": "doctrine/lexer",
                 "lexer",
                 "parser"
             ],
-            "time": "2014-09-09 13:34:57"
+            "time": "2014-09-09T13:34:57+00:00"
         },
         {
             "name": "doctrine/orm",
                 "database",
                 "orm"
             ],
-            "time": "2016-01-05 21:34:58"
+            "time": "2016-01-05T21:34:58+00:00"
         },
         {
             "name": "ezyang/htmlpurifier",
             "keywords": [
                 "html"
             ],
-            "time": "2013-11-30 08:25:19"
+            "time": "2013-11-30T08:25:19+00:00"
         },
         {
             "name": "metaways/opendocument",
                 "OpenDocument",
                 "Template"
             ],
-            "time": "2016-04-01 11:09:24"
+            "time": "2016-04-01T11:09:24+00:00"
         },
         {
             "name": "metaways/timezoneconvert",
                 "Timezones",
                 "UTC"
             ],
-            "time": "2016-04-25 08:20:13"
+            "time": "2016-04-25T08:20:13+00:00"
         },
         {
             "name": "pclzip/pclzip",
                 "php",
                 "zip"
             ],
-            "time": "2014-06-05 11:42:24"
+            "time": "2014-06-05T11:42:24+00:00"
         },
         {
             "name": "phpoffice/common",
                 "office",
                 "php"
             ],
-            "time": "2016-07-07 17:26:55"
+            "time": "2016-07-07T17:26:55+00:00"
         },
         {
             "name": "phpoffice/phpexcel",
                 "xls",
                 "xlsx"
             ],
-            "time": "2015-05-01 07:00:55"
+            "time": "2015-05-01T07:00:55+00:00"
+        },
+        {
+            "name": "phpoffice/phpspreadsheet",
+            "version": "dev-master",
+            "source": {
+                "type": "git",
+                "url": "https://github.com/PHPOffice/PhpSpreadsheet.git",
+                "reference": "c94539c86c9e2790656947b87d1aa1ef211a4584"
+            },
+            "dist": {
+                "type": "zip",
+                "url": "https://api.github.com/repos/PHPOffice/PhpSpreadsheet/zipball/c94539c86c9e2790656947b87d1aa1ef211a4584",
+                "reference": "c94539c86c9e2790656947b87d1aa1ef211a4584",
+                "shasum": ""
+            },
+            "require": {
+                "ext-iconv": "*",
+                "ext-mbstring": "*",
+                "ext-xml": "*",
+                "ext-xmlwriter": "*",
+                "php": "^5.5|^7.0"
+            },
+            "require-dev": {
+                "friendsofphp/php-cs-fixer": "^1.11",
+                "mikey179/vfsstream": "1.5.*",
+                "phpunit/phpunit": "4.6.*",
+                "squizlabs/php_codesniffer": "2.*"
+            },
+            "suggest": {
+                "dompdf/dompdf": "Option for rendering PDF with PDF Writer",
+                "ext-dom": "Option to read and write HTML files",
+                "ext-gd": "*",
+                "ext-zip": "*",
+                "jpgraph/jpgraph": "Option for rendering charts, or including charts with PDF or HTML Writers",
+                "mpdf/mpdf": "Option for rendering PDF with PDF Writer",
+                "tecnick.com/tcpdf": "Option for rendering PDF with PDF Writer"
+            },
+            "type": "library",
+            "autoload": {
+                "psr-4": {
+                    "PhpOffice\\PhpSpreadsheet\\": "src/PhpSpreadsheet"
+                }
+            },
+            "notification-url": "https://packagist.org/downloads/",
+            "license": [
+                "LGPL-2.1"
+            ],
+            "authors": [
+                {
+                    "name": "Maarten Balliauw",
+                    "homepage": "http://blog.maartenballiauw.be"
+                },
+                {
+                    "name": "Erik Tilt"
+                },
+                {
+                    "name": "Franck Lefevre",
+                    "homepage": "http://rootslabs.net"
+                },
+                {
+                    "name": "Mark Baker",
+                    "homepage": "http://markbakeruk.net"
+                }
+            ],
+            "description": "PHPSpreadsheet - Read, Create and Write Spreadsheet documents in PHP - Spreadsheet engine",
+            "homepage": "https://github.com/PHPOffice/PhpSpreadsheet",
+            "keywords": [
+                "OpenXML",
+                "excel",
+                "gnumeric",
+                "ods",
+                "php",
+                "spreadsheet",
+                "xls",
+                "xlsx"
+            ],
+            "time": "2016-10-03 08:18:38"
         },
         {
             "name": "phpoffice/phpword",
                 "word",
                 "writer"
             ],
-            "time": "2016-07-31 08:53:39"
+            "time": "2016-07-31T08:53:39+00:00"
         },
         {
             "name": "sabre/dav",
                 "framework",
                 "iCalendar"
             ],
-            "time": "2015-01-21 21:01:09"
+            "time": "2015-01-21T21:01:09+00:00"
         },
         {
             "name": "sabre/vobject",
                 "jCard",
                 "vCard"
             ],
-            "time": "2016-10-07 03:20:40"
+            "time": "2016-10-07T03:20:40+00:00"
         },
         {
             "name": "symfony/console",
             ],
             "description": "Symfony Console Component",
             "homepage": "https://symfony.com",
-            "time": "2016-06-29 07:02:31"
+            "time": "2016-06-29T07:02:31+00:00"
         },
         {
             "name": "symfony/polyfill-mbstring",
                 "portable",
                 "shim"
             ],
-            "time": "2016-05-18 14:26:46"
+            "time": "2016-05-18T14:26:46+00:00"
         },
         {
             "name": "syncroton/syncroton",
             ],
             "description": "Library to sync mobile devices",
             "homepage": "http://www.syncroton.org",
-            "time": "2017-01-03 16:14:20"
+            "time": "2017-01-03T16:14:20+00:00"
         },
         {
             "name": "tine20/composerapploader",
                     "Tine20\\ComposerAppLoader\\": "src/"
                 }
             },
-            "time": "2017-02-13 11:26:52"
+            "time": "2017-02-13T11:26:52+00:00"
         },
         {
             "name": "twig/twig",
                 "escaper",
                 "zf2"
             ],
-            "time": "2015-05-07 14:55:31"
+            "time": "2015-05-07T14:55:31+00:00"
         },
         {
             "name": "zendframework/zend-http",
                 "http",
                 "zf2"
             ],
-            "time": "2015-09-14 16:11:20"
+            "time": "2015-09-14T16:11:20+00:00"
         },
         {
             "name": "zendframework/zend-loader",
                 "loader",
                 "zf2"
             ],
-            "time": "2015-05-07 14:55:31"
+            "time": "2015-05-07T14:55:31+00:00"
         },
         {
             "name": "zendframework/zend-stdlib",
                 "stdlib",
                 "zf2"
             ],
-            "time": "2015-07-21 13:55:46"
+            "time": "2015-07-21T13:55:46+00:00"
         },
         {
             "name": "zendframework/zend-uri",
                 "uri",
                 "zf2"
             ],
-            "time": "2015-09-14 16:17:10"
+            "time": "2015-09-14T16:17:10+00:00"
         },
         {
             "name": "zendframework/zend-validator",
                 "validator",
                 "zf2"
             ],
-            "time": "2015-09-08 21:04:17"
+            "time": "2015-09-08T21:04:17+00:00"
         },
         {
             "name": "zendframework/zendframework1",
                 "New BSD License"
             ],
             "description": "Metaways Infosystems GmbH - Patched Zendframework1",
-            "time": "2017-05-15 08:19:50"
+            "time": "2017-05-15T08:19:50+00:00"
         }
     ],
     "packages-dev": [
                 "task",
                 "tool"
             ],
-            "time": "2016-03-10 21:39:23"
+            "time": "2016-03-10T21:39:23+00:00"
         },
         {
             "name": "phpunit/php-code-coverage",
                 "testing",
                 "xunit"
             ],
-            "time": "2014-09-02 10:13:14"
+            "time": "2014-09-02T10:13:14+00:00"
         },
         {
             "name": "phpunit/php-file-iterator",
                 "filesystem",
                 "iterator"
             ],
-            "time": "2015-06-21 13:08:43"
+            "time": "2015-06-21T13:08:43+00:00"
         },
         {
             "name": "phpunit/php-text-template",
             "keywords": [
                 "template"
             ],
-            "time": "2015-06-21 13:50:34"
+            "time": "2015-06-21T13:50:34+00:00"
         },
         {
             "name": "phpunit/php-timer",
             "keywords": [
                 "timer"
             ],
-            "time": "2016-05-12 18:03:57"
+            "time": "2016-05-12T18:03:57+00:00"
         },
         {
             "name": "phpunit/php-token-stream",
             "keywords": [
                 "tokenizer"
             ],
-            "time": "2014-03-03 05:10:30"
+            "time": "2014-03-03T05:10:30+00:00"
         },
         {
             "name": "phpunit/phpunit",
                 "testing",
                 "xunit"
             ],
-            "time": "2014-10-17 09:04:17"
+            "time": "2014-10-17T09:04:17+00:00"
         },
         {
             "name": "phpunit/phpunit-mock-objects",
                 "mock",
                 "xunit"
             ],
-            "time": "2013-01-13 10:24:48"
+            "time": "2013-01-13T10:24:48+00:00"
         },
         {
             "name": "symfony/yaml",
             ],
             "description": "Symfony Yaml Component",
             "homepage": "https://symfony.com",
-            "time": "2016-06-29 05:29:29"
+            "time": "2016-06-29T05:29:29+00:00"
         },
         {
             "name": "tedivm/jshrink",
                 "javascript",
                 "minifier"
             ],
-            "time": "2015-07-04 07:35:09"
+            "time": "2015-07-04T07:35:09+00:00"
         }
     ],
     "aliases": [
         }
     ],
     "minimum-stability": "stable",
-    "stability-flags": [],
+    "stability-flags": {
+        "phpoffice/phpspreadsheet": 20
+    },
     "prefer-stable": false,
     "prefer-lowest": false,
     "platform": [],