Multiple choice arrays for table json converter

Summary

Handling multiple choice arrays.

See caosdb/customers/dimr/management#217 and caosdb/customers/dimr/management#219

Focus

• Does the test case look good?

Test Environment

• Run unit test.

Check List for the Author

Please, prepare your MR for a review. Be sure to write a summary and a focus and create gitlab comments for the reviewer. They should guide the reviewer through the changes, explain your changes and also point out open questions. For further good practices have a look at our review guidelines

• All automated tests pass
• Reference related issues
• Up-to-date CHANGELOG.md (or not necessary)
• Up-to-date JSON schema (or not necessary)
• Appropriate user and developer documentation (or not necessary)
  • How do I use the software? Assume "stupid" users.
  • How do I develop or debug the software? Assume novice developers.
• Annotations in code (Gitlab comments)
  • Intent of new code
  • Problems with old code
  • Why this implementation?

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?

For further good practices have a look at our review guidelines.

src/caosadvancedtools/table_json_conversion/fill_xlsx.py

@@ -206,6 +206,7 @@ class TemplateFiller:
         Index the sheets by all path arrays leading to them.  Also create a simple column index by
         column type and path.
 
+        This method creates and populates the dict ``self._sheet_index``.
         """
         self._sheet_index = {}
         for sheetname in self._workbook.sheetnames:
@@ -227,7 +228,8 @@ class TemplateFiller:
                         path.append(col[path_idx].value)
                 # col_key = p2s([col[coltype_idx].value] + path)
                 # col_index[col_key] = SimpleNamespace(column=col, col_index=col_idx)
-                if col[coltype_idx].value not in [ColumnType.SCALAR.name, ColumnType.LIST.name]:
+                if col[coltype_idx].value not in [ColumnType.SCALAR.name, ColumnType.LIST.name,
+                                                  ColumnType.MULTIPLE_CHOICE.name]:
                     continue
 
                 path_str = p2s(path)
@@ -308,16 +310,20 @@ out: union[dict, None]
 
             # collecting the data
             assert isinstance(content, list)
-            if len(content) > 1:
-                content = [ILLEGAL_CHARACTERS_RE.sub("", str(x)) for x in content]
-                value = ";".join(content)  # TODO we need escaping of values
-            else:
-                value = content[0]
-                if isinstance(value, str):
-                    value = ILLEGAL_CHARACTERS_RE.sub("", value)
-            path_str = p2s(path)
-            assert path_str not in insertables
-            insertables[path_str] = value
+            to_insert = self._try_multiple_choice(path, values=content)
+            if not to_insert:
+                if len(content) > 1:
+                    content = [ILLEGAL_CHARACTERS_RE.sub("", str(x)) for x in content]
+                    value = ";".join(content)  # TODO we need escaping of values
+                else:
+                    value = content[0]
+                    if isinstance(value, str):
+                        value = ILLEGAL_CHARACTERS_RE.sub("", value)
+
+                path_str = p2s(path)
+                assert path_str not in insertables
+                to_insert = {path_str: value}
+            insertables.update(to_insert)
         if only_collect_insertables:
             return insertables
         if not current_path:  # Top level returns, because there are only sheets for the children.
@@ -353,6 +359,40 @@ out: union[dict, None]
 
         return None
 
+    def _try_multiple_choice(self, path: list[str], values: list[str]) -> Optional[dict[str, str]]:
+        """Try to create sheet content for a multiple choice property.
+
+Parameters
+----------
+path: list[str]
+  The Path to this property.
+values: list[str]
+  A list of possible choices, should be unique.
+
+Returns
+-------
+to_insert: Optional[dict[str, str]]
+  A path-value dict.  None if this doesn't seem to be a multiple choice data set.
+        """
+        try:
+            assert len(set(values)) == len(values)
+            to_insert = {}
+            found_sheet = None
+            for value in values:
+                assert isinstance(value, str)
+                path_str = p2s(path + [value])
+                assert path_str in self._sheet_index
+                sheet_meta = self._sheet_index[path_str]
+                # All matches shall be on the same sheet
+                assert found_sheet is None or found_sheet == sheet_meta.sheetname
+                found_sheet = sheet_meta.sheetname
+                # Correct type
+                assert sheet_meta.col_type == ColumnType.MULTIPLE_CHOICE.name
+                to_insert[path_str] = "x"
+        except AssertionError:
+            return None
+        return to_insert
+
 
 def fill_template(data: Union[dict, str, TextIO], template: str, result: str,
                   validation_schema: Union[dict, str, TextIO] = None) -> None: