XLSX-Konverter: Bessere Fehlermeldung bei inkorrektem Typ in Spalte, zusätzlicher Spalte

Summary

Type and Validation errors are now collected and formatted as a table. The error raised when there is an additional column not present in the schema now describes the problem. Addresses https://gitlab.indiscale.com/caosdb/customers/dimr/management/-/issues/251

Focus

• Does this solution improve readability and error identification? (is it a 'better output'?)
• Are all errors covered?

Test Environment

Manual testing.

Check List for the Author

• All automated tests pass
• Reference related issues
• Up-to-date CHANGELOG.md (not necessary)
• Appropriate user and developer documentation (or not necessary)
• Annotations in code (Gitlab comments)

Check List for the Reviewer

• I understand the intent of this MR
• All automated tests pass
• Up-to-date CHANGELOG.md (or not necessary)
• Appropriate user and developer documentation (or not necessary)
• The test environment setup works and the intended behavior is reproducible in the test environment
• In-code documentation and comments are up-to-date.
• Check: Are there specifications? Are they satisfied?

src/caosadvancedtools/table_json_conversion/convert.py

@@ -46,9 +46,17 @@ def _strict_bool(value: Any) -> bool:
     raise TypeError(f"Not a good boolean: {repr(value)}")
 
 
-def format_exception_table(exceptions: list(tuple), worksheet_title: str,
-                           column_names: Optional[dict, list] = None,
-                           max_line_length: Optional[int] = 120) -> str:
+def _column_id_to_chars(num):
+    """Converts a column id (zero based) to the corresponding string
+    representation, e.g. 0 -> 'A', 97 -> 'CT'"""
+    if num < 0:
+        return ""
+    return _column_id_to_chars(int(num / 26) - 1) + chr(int(num % 26) + 65)
+
+
+def _format_exception_table(exceptions: list[tuple], worksheet_title: str,
+                            column_names: Optional[Union[dict, list]] = None,
+                            max_line_length: int = 120) -> str:
     """
     Given a list of tuples containing a row and column number as well as an
     exception in that order, and the title of the current worksheet, returns
@@ -69,7 +77,7 @@ def format_exception_table(exceptions: list(tuple), worksheet_title: str,
                         column_names[column_num] should return the name of
                         column column_names.
                         If given, exceptions will be clustered by column.
-    max_line_length:    int
+    max_line_length:    int, default=120
                         Soft cap for the line length of the resulting table
 
     Return
@@ -77,10 +85,6 @@ def format_exception_table(exceptions: list(tuple), worksheet_title: str,
     string_rep:         str
                         Table containing the given exceptions
     """
-    def to_char(num):
-        if num < 0:
-            return ""
-        return to_char(int(num / 26) - 1) + chr(int(num % 26) + 65)
     max_line_length -= 40             # Estimate of Field + Type space use
 
     headers = {"loc": "Location", "type": "Error Type", "mess": ["Message"]}
@@ -103,9 +107,9 @@ def format_exception_table(exceptions: list(tuple), worksheet_title: str,
         new_data.append(row)
         # Field
         if isinstance(row_i, int):
-            row["loc"] = f"Cell {to_char(col_i)}{row_i + 1}"
+            row["loc"] = f"Cell {_column_id_to_chars(col_i)}{row_i + 1}"
         else:
-            row["loc"] = f"Column {to_char(col_i)}"
+            row["loc"] = f"Column {_column_id_to_chars(col_i)}"
         lengths["loc"] = max(lengths["loc"], len(row["loc"]))
         # Code
         row["type"] = type(excep).__name__
@@ -296,12 +300,17 @@ class XLSXConverter:
           If True, do not fail with unresolvable foreign definitions, but collect all errors.
         """
         row_type_column = xlsx_utils.get_row_type_column_index(sheet)
+        col_type_row = xlsx_utils.get_column_type_row_index(sheet)
         foreign_columns = xlsx_utils.get_foreign_key_columns(sheet)
         foreign_column_paths = {col.index: col.path for col in foreign_columns.values()}
         data_columns = xlsx_utils.get_data_columns(sheet)
         data_column_paths = {col.index: col.path for col in data_columns.values()}
         # Parent path, insert in correct order.
-        parent, proper_name = xlsx_utils.get_path_position(sheet)
+        try:
+            parent, proper_name = xlsx_utils.get_path_position(sheet)
+        except UnboundLocalError as e:
+            raise jsonschema.ValidationError(f"Malformed metadata: Cannot parse "
+                                             f"paths in worksheet '{sheet.title}'.") from e
         if parent:
             parent_sheetname = xlsx_utils.get_worksheet_for_path(parent, self._defining_path_index)
             if parent_sheetname not in self._handled_sheets:
@@ -349,6 +358,8 @@ class XLSXConverter:
                             value = self._validate_and_convert(value, path)
                             _set_in_nested(mydict=data, path=path, value=value, prefix=parent, skip=1)
                         continue
+                    elif sheet.cell(col_type_row+1, col_idx+1).value is None:
+                        warn(f"No metadata configured for column {_column_id_to_chars(col_idx)}.")
                 except (ValueError, KeyError, jsonschema.ValidationError) as e:
                     # Append error for entire column only once
                     if isinstance(e, KeyError) and 'column' in str(e):
@@ -371,9 +382,9 @@ class XLSXConverter:
                     raise
                 self._errors[(sheet.title, row_idx)] = kerr.definitions
 
-        if exceptions != []:
-            exception_table = format_exception_table(exceptions, sheet.title,
-                                                     col_names)
+        if exceptions:
+            exception_table = _format_exception_table(exceptions, sheet.title,
+                                                      col_names)
             raise jsonschema.ValidationError(exception_table)
 
         self._handled_sheets.add(sheet.title)