🐛 import - extract data from xls, ods, etc. with pandas instead of LO

3 files changed, 34 insertions, 16 deletions

diff --git a/ishtar_common/forms_common.py b/ishtar_common/forms_common.py
index 993b03766..effe8b722 100644
--- a/ishtar_common/forms_common.py
+++ b/ishtar_common/forms_common.py
@@ -394,9 +394,7 @@ class NewImportForm(BaseImportForm):
                 raise forms.ValidationError(
                     _('"Associated images" field must be a valid zip file.')
                 )
-        types = [".csv"]
-        if settings.USE_LIBREOFFICE:
-            types += [".ods", ".xls", ".xlsx", ".xlsm"]
+        types = [".csv", ".ods", ".xls", ".xlsx", ".xlsm"]
         self._clean_imported_file(types=types)
         archive_required = self._need_archive(data)
         if archive_required and (
diff --git a/ishtar_common/models_imports.py b/ishtar_common/models_imports.py
index 6cb95b0ee..b56f69bb4 100644
--- a/ishtar_common/models_imports.py
+++ b/ishtar_common/models_imports.py
@@ -19,6 +19,7 @@
 
 import csv
 import datetime
+import pandas
 import random
 from pathlib import Path
 
@@ -67,6 +68,7 @@ from ishtar_common.model_managers import SlugModelManager
 
 from ishtar_common.utils import (
     create_slug,
+    format_int_float,
     generate_dict_from_list,
     get_all_related_m2m_objects_with_model,
     get_session_var,
@@ -2116,7 +2118,7 @@ class Import(BaseImport):
         return self.imported_file
 
     def set_imported_values(self):
-        if not settings.USE_LIBREOFFICE or not self.imported_file or not UnoCalc:
+        if not self.imported_file:
             return
         name = self.imported_file.name.lower()
         ext = name.split(".")[-1]
@@ -2126,25 +2128,21 @@ class Import(BaseImport):
         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)
+            data = pandas.read_excel(imported_file_path,
+                                     sheet_name=(self.importer_type.tab_number or 1) - 1)
         except Exception:
             return
+        data = data.dropna(how="all")  # drop empty rows
+        if data.empty:
+            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))
-
+        data.to_csv(filename, index=False, columns=data.columns[range(last_column)], float_format=format_int_float)
         name = filename[len(media_root):]
         if name.startswith(os.sep):
             name = name[1:]
@@ -2734,14 +2732,20 @@ class Import(BaseImport):
                 ImportLineError.objects.get_or_create(import_item=self, line=idx)
 
     def save(self, *args, **kwargs):
-        if self.imported_file:
+        maj_imported_file = False
+        if getattr(self, "_maj_imported_file", False):
+            pass
+        elif self.imported_file:
             if self._initial_imported_file != self.imported_file.path or not self.imported_values:
-                self.set_imported_values()
+                maj_imported_file = True
         elif self.imported_values:
             self.imported_values = None
         super().save(*args, **kwargs)
         if not getattr(self, "_no_parse_error_file", False):
             self.parse_error_file()
+        if maj_imported_file and self.set_imported_values():
+            self._maj_imported_file = True
+            self.save()
 
 
 def pre_delete_import(sender, **kwargs):
diff --git a/ishtar_common/utils.py b/ishtar_common/utils.py
index e0a670d56..35289145c 100644
--- a/ishtar_common/utils.py
+++ b/ishtar_common/utils.py
@@ -31,6 +31,7 @@ from jinja2 import Template
 from jinja2.filters import FILTERS, environmentfilter
 import locale
 import math
+import numpy
 import os
 import random
 import re
@@ -1224,6 +1225,21 @@ def _post_save_geo(sender, **kwargs):
     return
 
 
+def format_int_float(values):
+    """
+    Numpy array: format integer with not "."
+    """
+    new_values = []
+    for value in values:
+        if numpy.isnan(value):
+            new_values.append("")
+        elif int(value) == value:
+            new_values.append(str(int(value)))
+        else:
+            new_values.append(value)
+    return new_values
+
+
 def create_slug(model, name, slug_attr="slug", max_length=100):
     base_slug = slugify(name)
     slug = base_slug[:max_length]