Using list which depends on another list in excel using python

I have a requirement to create dependent dropdowns in excel using python.

import os
import openpyxl
from openpyxl.styles import Border, Side, PatternFill, Font, Alignment, numbers
from openpyxl.worksheet.datavalidation import DataValidation
from openpyxl.workbook.defined_name import DefinedName


def create_dynamic_excel_bytes(sheets_config, file_name):
    wb = openpyxl.Workbook()
    ws_hidden = wb.create_sheet("Hidden")
    hidden_col = 1
    path_url = "/home/downloads/dravoka-downloadexcel/"
    file_path = os.path.join(path_url, file_name)

    # ✅ Add global EmptyRange placeholder
    empty_col_letter = openpyxl.utils.get_column_letter(hidden_col + 100)
    ws_hidden.cell(row=1, column=hidden_col + 100, value="")
    wb.defined_names.add(
        DefinedName("EmptyRange", attr_text=f"Hidden!${empty_col_letter}$1")
    )

    for sheet_idx, sheet_conf in enumerate(sheets_config):
        if sheet_idx == 0:
            ws = wb.active
            ws.title = sheet_conf["sheet_name"]
        else:
            ws = wb.create_sheet(sheet_conf["sheet_name"])

        columns = sheet_conf["columns"]
        num_rows = sheet_conf.get("num_rows", 100)

        # Create border style
        border = Border(
            left=Side(border_style="thin"),
            right=Side(border_style="thin"),
            top=Side(border_style="thin"),
            bottom=Side(border_style="thin"),
        )

        # Create header style
        header_fill = PatternFill(start_color="475E75", end_color="475E75", fill_type="solid")
        header_font = Font(bold=True, color="FFFFFF")  # White color
        header_alignment = Alignment(horizontal="center", vertical="center")

        for col_idx, col in enumerate(columns, start=1):
            cell = ws.cell(row=1, column=col_idx, value=col["header"])
            cell.fill = header_fill
            cell.font = header_font
            cell.alignment = header_alignment
            cell.border = border

        for col_idx, col in enumerate(columns, start=1):
            col_letter = openpyxl.utils.get_column_letter(col_idx)

            if col["type"] == "dropdown":
                for i, val in enumerate(col["dropdown_values"], start=1):
                    ws_hidden.cell(row=i, column=hidden_col, value=val)
                range_name = f"{sheet_conf['sheet_name']}_{col['header']}".replace(" ", "_")
                range_ref = (
                    f"Hidden!${openpyxl.utils.get_column_letter(hidden_col)}$1:"
                    f"${openpyxl.utils.get_column_letter(hidden_col)}${len(col['dropdown_values'])}"
                )
                wb.defined_names.add(DefinedName(name=range_name, attr_text=range_ref))
                dv = DataValidation(type="list", formula1=f"={range_name}", allow_blank=True)
                ws.add_data_validation(dv)
                for row in range(2, num_rows + 2):
                    dv.add(ws[f"{col_letter}{row}"])
                hidden_col += 1

            elif col["type"] == "dependent_dropdown":
                parent_col = col["dependent_on"]
                dep_map = col["dependent_map"]

                for parent_val, child_vals in dep_map.items():
                    range_name = parent_val.replace(" ", "_")

                    if child_vals:  # ✅ Non-empty children
                        for i, val in enumerate(child_vals, start=1):
                            ws_hidden.cell(row=i, column=hidden_col, value=val)
                        range_ref = (
                            f"Hidden!${openpyxl.utils.get_column_letter(hidden_col)}$1:"
                            f"${openpyxl.utils.get_column_letter(hidden_col)}${len(child_vals)}"
                        )
                        wb.defined_names.add(
                            DefinedName(name=range_name, attr_text=range_ref, localSheetId=None)
                        )
                        hidden_col += 1
                    else:  # ✅ Empty children → map to EmptyRange
                        wb.defined_names.add(
                            DefinedName(name=range_name, attr_text="EmptyRange", localSheetId=None)
                        )

                parent_col_idx = next(
                    i + 1 for i, c in enumerate(columns) if c["header"] == parent_col
                )

                for row in range(2, num_rows + 2):
                    formula = f'=INDIRECT(SUBSTITUTE({openpyxl.utils.get_column_letter(parent_col_idx)}{row}," ","_"))'
                    dv = DataValidation(type="list", formula1=formula, allow_blank=True)
                    ws.add_data_validation(dv)
                    dv.add(ws[f"{col_letter}{row}"])

            elif col["type"] == "date":
                for row in range(2, num_rows + 2):
                    ws[f"{col_letter}{row}"].number_format = "yyyy-mm-dd"

            elif col["type"] == "currency":
                for row in range(2, num_rows + 2):
                    ws[f"{col_letter}{row}"].number_format = numbers.FORMAT_CURRENCY_USD_SIMPLE

            elif col["type"] == "number":
                dv = DataValidation(type="decimal", allow_blank=True)
                ws.add_data_validation(dv)
                for row in range(2, num_rows + 2):
                    dv.add(ws[f"{col_letter}{row}"])

    ws_hidden.sheet_state = "hidden"

    # Check if directory exists, if not create it
    if not os.path.exists(path_url):
        os.makedirs(path_url)

    wb.save(file_path)
    return file_path

this code works fine when i download the file and open it in libre office. But when i try top open it the mac machine it throws following error.

How to resolve this. this happens only when i have a depedent dropdown in my sheets config.

Here is a sample sheets config.

sheets_config = [
        {
            "sheet_name": "Form",
            "columns": [
                {
                    "header": "Employee",
                    "type": "dropdown",
                    "dropdown_values": employee_list,
                },
                {
                    "header": "Department",
                    "type": "dependent_dropdown",
                    "dependent_on": "Vendor",
                    "dependent_map": department_map,
                },
                {
                    "header": "Function Name",
                    "type": "dropdown",
                    "dropdown_values": functions,
                },
                {"header": "Allocation", "type": "text"},
                {"header": "Joining Date", "type": "date"},
            ],
            "num_rows": 100,
        }
    ]

Вернуться на верх