✨ imports: manage ods, xls and xlsx

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

12 files changed, 136 insertions, 47 deletions

diff --git a/archaeological_context_records/migrations/0113_contextrecord_imports_updated.py b/archaeological_context_records/migrations/0113_contextrecord_imports_updated.py
index fc365c6a6..6c9a5e085 100644
--- a/archaeological_context_records/migrations/0113_contextrecord_imports_updated.py
+++ b/archaeological_context_records/migrations/0113_contextrecord_imports_updated.py
@@ -1,4 +1,4 @@
-# Generated by Django 2.2.24 on 2023-10-05 16:50
+# Generated by Django 2.2.24 on 2023-10-18 15:51
 
 from django.db import migrations, models
 
@@ -6,7 +6,7 @@ from django.db import migrations, models
 class Migration(migrations.Migration):
 
     dependencies = [
-        ('ishtar_common', '0230_auto_20231005_1650'),
+        ('ishtar_common', '0230_auto_20231018_1551'),
         ('archaeological_context_records', '0112_migrate_created'),
     ]
 
diff --git a/archaeological_files/migrations/0112_file_imports_updated.py b/archaeological_files/migrations/0112_file_imports_updated.py
index b44132649..d227e0487 100644
--- a/archaeological_files/migrations/0112_file_imports_updated.py
+++ b/archaeological_files/migrations/0112_file_imports_updated.py
@@ -1,4 +1,4 @@
-# Generated by Django 2.2.24 on 2023-10-05 16:50
+# Generated by Django 2.2.24 on 2023-10-18 15:53
 
 from django.db import migrations, models
 
@@ -6,7 +6,7 @@ from django.db import migrations, models
 class Migration(migrations.Migration):
 
     dependencies = [
-        ('ishtar_common', '0230_auto_20231005_1650'),
+        ('ishtar_common', '0230_auto_20231018_1551'),
         ('archaeological_files', '0111_migrate_created'),
     ]
 
diff --git a/archaeological_finds/migrations/0113_auto_20231005_1650.py b/archaeological_finds/migrations/0113_auto_20231018_1553.py
index 0c761315b..e9bfad7fd 100644
--- a/archaeological_finds/migrations/0113_auto_20231005_1650.py
+++ b/archaeological_finds/migrations/0113_auto_20231018_1553.py
@@ -1,4 +1,4 @@
-# Generated by Django 2.2.24 on 2023-10-05 16:50
+# Generated by Django 2.2.24 on 2023-10-18 15:53
 
 from django.db import migrations, models
 
@@ -6,7 +6,7 @@ from django.db import migrations, models
 class Migration(migrations.Migration):
 
     dependencies = [
-        ('ishtar_common', '0230_auto_20231005_1650'),
+        ('ishtar_common', '0230_auto_20231018_1551'),
         ('archaeological_finds', '0112_migrate_created'),
     ]
 
diff --git a/archaeological_operations/migrations/0113_auto_20231005_1650.py b/archaeological_operations/migrations/0113_auto_20231018_1551.py
index 581ec6c9a..11d1143d0 100644
--- a/archaeological_operations/migrations/0113_auto_20231005_1650.py
+++ b/archaeological_operations/migrations/0113_auto_20231018_1551.py
@@ -1,4 +1,4 @@
-# Generated by Django 2.2.24 on 2023-10-05 16:50
+# Generated by Django 2.2.24 on 2023-10-18 15:51
 
 from django.db import migrations, models
 
@@ -6,7 +6,7 @@ from django.db import migrations, models
 class Migration(migrations.Migration):
 
     dependencies = [
-        ('ishtar_common', '0230_auto_20231005_1650'),
+        ('ishtar_common', '0230_auto_20231018_1551'),
         ('archaeological_operations', '0112_migrate_created'),
     ]
 
diff --git a/archaeological_warehouse/migrations/0119_auto_20231005_1650.py b/archaeological_warehouse/migrations/0119_auto_20231018_1553.py
index 7f7cdd749..b84564711 100644
--- a/archaeological_warehouse/migrations/0119_auto_20231005_1650.py
+++ b/archaeological_warehouse/migrations/0119_auto_20231018_1553.py
@@ -1,4 +1,4 @@
-# Generated by Django 2.2.24 on 2023-10-05 16:50
+# Generated by Django 2.2.24 on 2023-10-18 15:53
 
 from django.db import migrations, models
 
@@ -6,7 +6,7 @@ from django.db import migrations, models
 class Migration(migrations.Migration):
 
     dependencies = [
-        ('ishtar_common', '0230_auto_20231005_1650'),
+        ('ishtar_common', '0230_auto_20231018_1551'),
         ('archaeological_warehouse', '0118_auto_20230807_1106'),
     ]
 
diff --git a/ishtar_common/forms_common.py b/ishtar_common/forms_common.py
index 5ea90a8d5..dd3a83971 100644
--- a/ishtar_common/forms_common.py
+++ b/ishtar_common/forms_common.py
@@ -274,16 +274,23 @@ class BaseImportForm(IshtarForm, forms.ModelForm):
 
     BAD_CHARS = ["é", "³", "ô", "Ã\xa0", "é"]
 
-    def _clean_csv(self, is_csv=False):
+    def _clean_imported_file(self, types=None):
         imported_file = self.cleaned_data.get("imported_file", None)
+        if not imported_file:
+            return
+        imported_file_name = imported_file.name.lower()
+        if types:
+            if not [1 for tpe in types if imported_file_name.endswith(tpe)]:
+                if len(types) == 1:
+                    msg = str(_("Bad format. Extension of the file must be {}.")).format(types[0][1:])
+                else:
+                    msg = str(_("Bad format. Extension of the file must be: {} or {}.")).format(
+                        ", ".join([tpe[1:] for tpe in types[:-1]]), types[-1][1:]
+                    )
+                raise forms.ValidationError(msg)
         encoding = self.cleaned_data.get("encoding", None)
-        if imported_file and encoding:
+        if encoding and imported_file_name.endswith(".csv"):
             try:
-                if not imported_file.name.lower().endswith(".csv"):
-                    if is_csv:
-                        raise ValueError()
-                    else:
-                        return
                 imported_file.seek(0)
                 reader = csv.reader(StringIO(imported_file.read().decode(encoding)))
                 idx = 0
@@ -375,7 +382,10 @@ class NewImportForm(BaseImportForm):
                 raise forms.ValidationError(
                     _('"Associated images" field must be a valid zip file.')
                 )
-        self._clean_csv(is_csv=True)
+        types = [".csv"]
+        if settings.USE_LIBREOFFICE:
+            types += [".ods", ".xls", ".xlsx"]
+        self._clean_imported_file(types=types)
         archive_required = self._need_archive(data)
         if archive_required and (
             not data.get("imported_images", None)
@@ -466,7 +476,10 @@ class NewImportGISForm(BaseImportForm):
 
     def clean(self):
         data = super().clean()
-        self._clean_csv()
+        types = ["zip", "gpkg", "csv"]
+        if settings.USE_LIBREOFFICE:
+            types += [".ods", ".xls", ".xlsx"]
+        self._clean_imported_file(types=types)
         return data
 
     def save(self, user, commit=True):
diff --git a/ishtar_common/libreoffice.py b/ishtar_common/libreoffice.py
index 7dbb36e30..3869ca81a 100644
--- a/ishtar_common/libreoffice.py
+++ b/ishtar_common/libreoffice.py
@@ -1,6 +1,5 @@
 #!/usr/bin/env python
 # -*- coding: utf-8 -*-
-
 import time
 
 import uno
@@ -9,14 +8,14 @@ from com.sun.star.beans import PropertyValue
 from com.sun.star.connection import NoConnectException
 from com.sun.star.sheet.ValidationType import LIST
 
-from com.sun.star.table import CellRangeAddress, CellAddress
+#from com.sun.star.table import CellRangeAddress, CellAddress
 
 from ishtar_common.utils import num2col
 
 from django.conf import settings
 
 
-RETRY = 1
+RETRY = 5
 
 
 class UnoClient:
@@ -45,7 +44,7 @@ class UnoClient:
         if self.remote_context and self.desktop:
             return
         try_nb = 0
-        while not self.service_manager or try_nb > RETRY:
+        while not self.service_manager and try_nb <= RETRY:
             self.connect()
             try_nb += 1
         if not self.service_manager:
@@ -64,6 +63,8 @@ class UnoClient:
 
     def get_document(self, filename, propval=None):
         self.create_context()
+        if not self.remote_context or not self.desktop:
+            return
         url = "file://{}".format(filename)
         if not propval:
             propval = ()
diff --git a/ishtar_common/migrations/0230_auto_20231005_1650.py b/ishtar_common/migrations/0230_auto_20231018_1551.py
index 9eb1f478a..c2fc2abe0 100644
--- a/ishtar_common/migrations/0230_auto_20231005_1650.py
+++ b/ishtar_common/migrations/0230_auto_20231018_1551.py
@@ -1,4 +1,4 @@
-# Generated by Django 2.2.24 on 2023-10-05 16:50
+# Generated by Django 2.2.24 on 2023-10-18 15:51
 
 import django.core.validators
 from django.db import migrations, models
@@ -49,6 +49,11 @@ class Migration(migrations.Migration):
         ),
         migrations.AddField(
             model_name='import',
+            name='imported_values',
+            field=models.FileField(blank=True, help_text='La taille maximale supportée pour le fichier est de 100 Mo.', max_length=220, null=True, upload_to='upload/imports/%Y/%m/', verbose_name='Imported values'),
+        ),
+        migrations.AddField(
+            model_name='import',
             name='next_import',
             field=models.ForeignKey(blank=True, null=True, on_delete=django.db.models.deletion.SET_NULL, related_name='imports', to='ishtar_common.Import', verbose_name='Next import'),
         ),
diff --git a/ishtar_common/migrations/0231_default_mandatory_keys.py b/ishtar_common/migrations/0231_default_mandatory_keys.py
index 59624d00b..568e60b9a 100644
--- a/ishtar_common/migrations/0231_default_mandatory_keys.py
+++ b/ishtar_common/migrations/0231_default_mandatory_keys.py
@@ -30,7 +30,7 @@ def migrate(apps, __):
 class Migration(migrations.Migration):
 
     dependencies = [
-        ('ishtar_common', '0230_auto_20231005_1650'),
+        ('ishtar_common', '0230_auto_20231018_1551'),
     ]
 
     operations = [
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
diff --git a/ishtar_common/templates/ishtar/import_table.html b/ishtar_common/templates/ishtar/import_table.html
index be3af600b..08c949654 100644
--- a/ishtar_common/templates/ishtar/import_table.html
+++ b/ishtar_common/templates/ishtar/import_table.html
@@ -88,7 +88,7 @@
                 </select>
             </td>
             <td><ul class="simple table-import-files">
-                {% if import.imported_file %}<li class="p-1">
+                {% if import.get_imported_values %}<li class="p-1">
                 {% with file_label=source_label logo='fa fa-fw fa-file-text-o' file_type='source' file=import.imported_file %}
                 {% include "ishtar/blocks/import_table_buttons_view.html" %}
                 {% endwith %}
@@ -164,7 +164,7 @@
             <td id="status-{{sub.import_id}}">{{sub.status}}</td>
             <td></td>
             <td><ul class="simple table-import-files">
-                {% if sub.imported_file %}<li class="p-1">
+                {% if sub.get_imported_values %}<li class="p-1">
                 {% with file_label=source_label logo='fa fa-fw fa-file-text-o' file_type='source' file=sub.imported_file %}
                 {% include "ishtar/blocks/import_table_buttons_view.html" %}
                 {% endwith %}
diff --git a/ishtar_common/views.py b/ishtar_common/views.py
index aec8fa9f4..8f8ecb5a6 100644
--- a/ishtar_common/views.py
+++ b/ishtar_common/views.py
@@ -2146,7 +2146,7 @@ class ImportGroupDeleteView(IshtarMixin, LoginRequiredMixin, DeleteView):
 class ImportCSVView(IshtarMixin, LoginRequiredMixin, TemplateView):
     template_name = "ishtar/blocks/view_import_csv.html"
     ATTRIBUTES = {
-        "source": "imported_file",
+        "source": "get_imported_values",
         "error": "error_file",
         "result": "result_file",
         "match": "match_file"
@@ -2169,6 +2169,8 @@ class ImportCSVView(IshtarMixin, LoginRequiredMixin, TemplateView):
         self.import_item = q.all()[0]
         attribute = self.ATTRIBUTES[kwargs["target"]]
         self.csv_file = getattr(self.import_item, attribute)
+        if callable(self.csv_file):
+            self.csv_file = self.csv_file()
         if not self.csv_file:
             raise Http404()
         return super().get(request, *args, **kwargs)