Writing nested dictionary in specific column, row in excel using Xlsx Writer

Question

Hello I'm trying to write data from dictionary to Excel using XlsxWriter.

My progress and the expected result:

I want to render the rest of data in this dictionary to Excel in same format and order anyone can help please.

import datetime
import xlsxwriter

workbook = xlsxwriter.Workbook("ExcelData.xlsx")
worksheet = workbook.add_worksheet()

dic_data = {
    "Client 1": "ACCESS DENIED",
    "Client 2": {
        "last_session_start_date": datetime.datetime(2022, 1, 19, 22, 53, 57),
        "last_session_end_date": datetime.datetime(2022, 1, 19, 22, 55, 40),
        "last_session_order_count": 1,
        "last_session_order_total": 0.0,
        "active_session_start_date": datetime.datetime(2022, 1, 20, 11, 9, 43),
        "active_session_end_date": False,
        "active_session_order_count": 3,
        "Active_session_order_total": 20.0,
    },
}

cell = workbook.add_format(
    {
        "bold": 1,
        "border": 1,
        "fg_color": "green",
        "color": "white",
    }
)

worksheet.merge_range("A1:H1", "Activity Report", cell)
worksheet.merge_range("A2:A3", "client Name", cell)
worksheet.merge_range("B2:E2", "Last Session ", cell)
worksheet.merge_range("F2:H2", " Active Session", cell)
rest_of_rows = [
    "start date",
    "end date",
    "order count",
    "order total",
    "start date",
    "order count",
    "order total",
]

worksheet.write_row(2, 1, rest_of_rows, cell)


for i, (k, v) in enumerate(dic_data.items(), start=3):
    worksheet.write(i, 0, k)


workbook.close()

Answer 1

You don't have a mapping between the column names and the data keys, so you'll need to change that:

rest_of_rows = [
    # header / data field
    ("start date", "last_session_start_date"),
    ("end date", "last_session_end_date"),
    ("order count", "last_session_order_count"),
    ("order total", "last_session_order_total"),
    ("start date", "active_session_start_date"),
    ("order count", "active_session_order_count"),
    ("order total", "active_session_order_total"),
]

worksheet.write_row(2, 1, [row[0] for row in rest_of_rows], cell)

Then, instead of the for i, (k, v) loop, something like this with more descriptive names and an inner loop to find the values for each column.

for y, (name, info) in enumerate(dic_data.items(), start=3):
    worksheet.write(y, 0, name)  # write name
    if isinstance(info, str):  # only a string?
        worksheet.write(y, 1, info)  # write it out
    elif isinstance(info, dict):
        for x, (_header, key) in enumerate(rest_of_rows, 1):  # otherwise look for data per column
            value = info.get(key)
            if value is not None:
                if isinstance(value, datetime.datetime):
                    value = value.isoformat()
                worksheet.write(y, x, value)

The output (rendered by my Mac's Numbers):