✨ imports: manage ods, xls and xlsx

Convert them to CSV and store the resulting file in a specific field.

1 files changed, 87 insertions, 19 deletions

diff --git a/ishtar_common/models_imports.py b/ishtar_common/models_imports.py
index 3f687b93d..a240f4326 100644
--- a/ishtar_common/models_imports.py
+++ b/ishtar_common/models_imports.py
@@ -19,6 +19,7 @@
 
 import csv
 import datetime
+import random
 from pathlib import Path
 
 import fiona
@@ -1754,6 +1755,7 @@ class ImportGroup(BaseImport):
                     pass
                 setattr(self, attr, None)
         sub_import_file_attr.append("imported_file")
+        sub_import_file_attr.append("imported_values")
         if profile.delete_image_zip_on_archive:
             sub_import_file_attr.append("imported_images")
         for sub_import in import_list:
@@ -1784,6 +1786,9 @@ class ImportGroup(BaseImport):
     def get_all_updated(self):
         return self._get_all_related("import_updated_")
 
+    def get_imported_values(self):
+        return self.imported_file
+
     def save(self, *args, **kwargs):
         add = self._state.adding
         super().save(*args, **kwargs)
@@ -1838,6 +1843,14 @@ class Import(BaseImport):
             "If a group is selected, target key saved in this group will be used."
         ),
     )
+    imported_values = models.FileField(
+        _("Imported values"),
+        upload_to="upload/imports/%Y/%m/",
+        max_length=220,
+        help_text=max_size_help(),
+        blank=True,
+        null=True,
+    )
     error_file = models.FileField(
         _("Error file"),
         upload_to="upload/imports/%Y/%m/",
@@ -1910,6 +1923,11 @@ class Import(BaseImport):
     def __str__(self):
         return "{} | {}".format(self.name or "-", self.importer_type)
 
+    def __init__(self, *args, **kwargs):
+        returned = super().__init__(*args, **kwargs)
+        self._initial_imported_file = self.imported_file.path if self.imported_file else ""
+        return returned
+
     def is_available(self, ishtar_user) -> bool:
         if ishtar_user.is_superuser or self.user == ishtar_user:
             return True
@@ -2060,14 +2078,56 @@ class Import(BaseImport):
         self._pre_import_values = values
         return values
 
+    def get_imported_values(self):
+        if self.imported_values:
+            return self.imported_values
+        return self.imported_file
+
+    def set_imported_values(self):
+        if not settings.USE_LIBREOFFICE or not self.imported_file or not UnoCalc:
+            return
+        name = self.imported_file.name.lower()
+        if not name.endswith(".ods") and not name.endswith(".xls") and not name.endswith(".xlsx"):
+            return
+        imported_file_path = os.path.abspath(self.imported_file.path)
+        media_root = os.path.abspath(settings.MEDIA_ROOT)
+        if not imported_file_path.startswith(media_root):
+            return
+        uno = UnoCalc()
+
+        calc = uno.open_calc(imported_file_path)
+        if not calc:
+            return
+        try:
+            sheet = uno.get_sheet(calc, (self.importer_type.tab_number or 1) - 1)
+        except Exception:
+            return
+        col_numbers = [c.col_number for c in self.importer_type.columns.all()]
+        if not col_numbers:
+            return
+        last_column = max(col_numbers)
+        filename = ".".join(imported_file_path.split('.')[:-1]) + f"-{random.randint(1, 10000):05d}.csv"
+
+        with open(filename, "w") as result_file:
+            w = csv.writer(result_file)
+            w.writerows(data for data in uno.sheet_get_data(sheet, last_column=last_column))
+
+        name = filename[len(media_root):]
+        if name.startswith(os.sep):
+            name = name[1:]
+        self.imported_values.name = name
+        self._initial_imported_file = self.imported_file.path
+        return True
+
     def get_number_of_lines(self):
         if self.number_of_line:
             return self.number_of_line
         if self.importer_type.type == "gis":
             return
-        if not self.imported_file or not self.imported_file.path:
+        imported_values = self.get_imported_values()
+        if not imported_values or not imported_values.path:
             return
-        filename = self.imported_file.path
+        filename = imported_values.path
         encodings = [self.encoding]
         encodings += [coding for coding, c in ENCODINGS if coding != self.encoding]
         for encoding in encodings:
@@ -2194,10 +2254,10 @@ class Import(BaseImport):
         )
 
     def _data_table_tab(self):
-        imported_file = self.imported_file.path
+        imported_values = self.get_imported_values().path
         tmpdir = None
-        if zipfile.is_zipfile(imported_file):
-            z = zipfile.ZipFile(imported_file)
+        if zipfile.is_zipfile(imported_values):
+            z = zipfile.ZipFile(imported_values)
             filename = None
             for name in z.namelist():
                 # get first CSV file found
@@ -2207,13 +2267,13 @@ class Import(BaseImport):
             if not filename:
                 return []
             tmpdir = tempfile.mkdtemp(prefix="tmp-ishtar-")
-            imported_file = z.extract(filename, tmpdir)
+            imported_values = z.extract(filename, tmpdir)
 
         encodings = [self.encoding]
         encodings += [coding for coding, c in ENCODINGS if coding != self.encoding]
         for encoding in encodings:
             try:
-                with open(imported_file, encoding=encoding) as csv_file:
+                with open(imported_values, encoding=encoding) as csv_file:
                     vals = [
                         line for line in csv.reader(csv_file, delimiter=self.csv_sep)
                     ]
@@ -2233,12 +2293,12 @@ class Import(BaseImport):
 
     def _data_table_gis(self, get_gis_attr=False):
         self.gis_attr = None
-        imported_file = self.imported_file.path
+        imported_values = self.get_imported_values().path
         tmp_dir = None
         file_type = "gpkg"
-        if zipfile.is_zipfile(imported_file):
-            z = zipfile.ZipFile(imported_file)
-            imported_file = None
+        if zipfile.is_zipfile(imported_values):
+            z = zipfile.ZipFile(imported_values)
+            imported_values = None
             filenames = []
             for name in z.namelist():
                 # get first CSV file found
@@ -2261,22 +2321,22 @@ class Import(BaseImport):
                 if filename.lower().endswith(".shp") or filename.lower().endswith(
                     ".gpkg"
                 ):
-                    imported_file = z.extract(filename, tmp_dir)
+                    imported_values = z.extract(filename, tmp_dir)
                 else:
                     z.extract(filename, tmp_dir)
-        elif imported_file.endswith(".csv"):
+        elif imported_values.endswith(".csv"):
             return self._data_table_tab()
-        elif not imported_file.endswith(".gpkg"):
+        elif not imported_values.endswith(".gpkg"):
             raise ImporterError(_("Invalid GIS file."))
 
-        if not imported_file:
+        if not imported_values:
             raise ImporterError(_("Invalid GIS file."))
 
         kwargs = {}
         if self.importer_type.layer_name:
             kwargs["layer"] = self.importer_type.layer_name
         try:
-            with fiona.open(imported_file, **kwargs) as collection:
+            with fiona.open(imported_values, **kwargs) as collection:
                 schema = collection.schema
                 geometry = schema["geometry"]
                 if geometry not in IMPORT_GEOMETRY:
@@ -2292,7 +2352,7 @@ class Import(BaseImport):
                 if not crs:
                     driver_type = {"shp": "ESRI Shapefile", "gpkg": "GPKG"}
                     driver = ogr.GetDriverByName(driver_type[file_type])
-                    shape = driver.Open(imported_file)
+                    shape = driver.Open(imported_values)
                     layer = shape.GetLayer()
                     crs = layer.GetSpatialRef()
                     auth = crs.GetAttrValue("AUTHORITY", 0)
@@ -2470,7 +2530,7 @@ class Import(BaseImport):
             )
         except IOError:
             error_message = str(_("Error on imported file: {}")).format(
-                self.imported_file
+                self.get_imported_values()
             )
             importer.errors = [error_message]
             if session_key:
@@ -2570,7 +2630,7 @@ class Import(BaseImport):
         return True
 
     def _archive(self):
-        file_attr = ["imported_file", "error_file", "result_file", "match_file"]
+        file_attr = ["imported_file", "imported_values", "error_file", "result_file", "match_file"]
         files = [
             (k, getattr(self, k).path, getattr(self, k).name.split(os.sep)[-1])
             for k in file_attr
@@ -2626,6 +2686,14 @@ class Import(BaseImport):
     def get_all_updated(self):
         return self._get_all_related("import_updated_")
 
+    def save(self, *args, **kwargs):
+        if self.imported_file:
+            if self._initial_imported_file != self.imported_file.path or not self.imported_values:
+                self.set_imported_values()
+        elif self.imported_values:
+            self.imported_values = None
+        super().save(*args, **kwargs)
+
 
 def pre_delete_import(sender, **kwargs):
     # deleted imported items when an import is delete