From 054019f7afb7aeeac680493c0b176eb35a832c96 Mon Sep 17 00:00:00 2001
From: Trygve Laugstøl <trygvis@inamo.no>
Date: Tue, 30 Apr 2019 07:49:14 +0200
Subject: part: o Experimental importer from Excel-like files.

---
 requirements.txt                |  4 +++
 setup.py                        |  4 +++
 src/ee/part/excel.py            | 61 +++++++++++++++++++++++++++++++++++++++++
 src/ee/tools/part_from_excel.py | 27 ++++++++++++++++++
 4 files changed, 96 insertions(+)
 create mode 100644 src/ee/part/excel.py
 create mode 100644 src/ee/tools/part_from_excel.py

diff --git a/requirements.txt b/requirements.txt
index b9d58f8..6fb9de8 100644
--- a/requirements.txt
+++ b/requirements.txt
@@ -14,6 +14,10 @@ parsec==3.3
 Pillow==5.2.0
 pytest==3.6.3
 tox==3.1.2
+pyexcel==0.5.13
+pyexcel-ods
+pyexcel-xls
+pyexcel-xlsx
 
 selenium==3.141.0
 generateds==2.30.11
diff --git a/setup.py b/setup.py
index 55a4def..0919712 100644
--- a/setup.py
+++ b/setup.py
@@ -23,6 +23,10 @@ setup(name='ee',
           'pandas',
           'parsec',
           'Pillow',
+          'pyexcel',
+          'pyexcel-ods',
+          'pyexcel-xls',
+          'pyexcel-xlsx',
           'pytest',
           'pyyaml',
           'selenium',
diff --git a/src/ee/part/excel.py b/src/ee/part/excel.py
new file mode 100644
index 0000000..cbbffde
--- /dev/null
+++ b/src/ee/part/excel.py
@@ -0,0 +1,61 @@
+from pathlib import Path
+from typing import Optional, Mapping
+
+from pyexcel import get_book
+
+from ee.money import Money
+from ee.part import Part, PartDb
+from ee.xml import types
+
+__all__ = [
+    "from_excel",
+]
+
+
+def from_excel(path: Path, sheet_name: Optional[str]) -> PartDb:
+    wb = get_book(file_name=str(path))
+
+    db = PartDb()
+
+    sheet = wb.sheet_by_index(0) if sheet_name is None else wb.sheet_by_name(sheet_name)
+
+    sheet = list(sheet)
+
+    header: Mapping[str, int] = {name.lower(): idx for idx, name in enumerate(sheet[0]) if len(name.strip())}
+    print(header)
+
+    mpn_idx = header.get("mpn", None)
+    desc_idx = header.get("description", None)
+    price_idx = header.get("price", None)
+    url_idx = header.get("url", None)
+
+    print("mpn: {}, desc: {}, price: {}, url: {}".format(mpn_idx, desc_idx, price_idx, url_idx))
+
+    for idx, row in enumerate(sheet[1:], 1):
+        mpn = row[mpn_idx] if mpn_idx is not None else ""
+        desc = row[desc_idx].strip() if desc_idx is not None else ""
+        price = row[price_idx] if price_idx is not None else None
+        url = row[url_idx] if url_idx is not None else None
+
+        print(row[desc_idx])
+
+        if len(mpn) == 0 and len(desc) == 0:
+            print("Bad part, line #{}. MPN or description is required".format(idx))
+            continue
+
+        part = Part(types.Part())
+
+        if not mpn:
+            mpn = desc.replace(" ", "-").lower()
+
+        part.add_mpn(mpn)
+        part.xml.descriptionProp = desc
+
+        if price:
+            part.add_price_break(1, Money(price))
+        if url:
+            part.get_links().append(types.Link(url=url, relation="canonical"))
+
+        db.add_entry(part, False)
+
+    return db
diff --git a/src/ee/tools/part_from_excel.py b/src/ee/tools/part_from_excel.py
new file mode 100644
index 0000000..63c7b65
--- /dev/null
+++ b/src/ee/tools/part_from_excel.py
@@ -0,0 +1,27 @@
+import argparse
+from pathlib import Path
+
+from ee.part import save_db
+from ee.part.excel import from_excel
+
+parser = argparse.ArgumentParser()
+
+parser.add_argument("--in",
+                    dest="in_path",
+                    required=True,
+                    metavar="EXCEL")
+
+parser.add_argument("--out",
+                    dest="out_path",
+                    required=True,
+                    metavar="PART DB")
+
+parser.add_argument("--sheet",
+                    metavar="NAME")
+
+args = parser.parse_args()
+
+out_path = Path(args.out_path)
+part_db = from_excel(Path(args.in_path), args.sheet)
+print("len={}".format(part_db.size()))
+save_db(out_path, part_db)
-- 
cgit v1.2.3