Python Xlwings

FillData.py

import json
import xlwings as xw
import pandas as pd

fileName = 'Sample_Exhibit_01.xlsx'
metadata = "metadata.json"


sample_data = {
    "ExhibitName01":{
        "Tags":{
            "companyName":"Lorem ipsum",
            "CR":"5.0",
            "ORT":"1",
            "ORR":"1",
            "Summary":"Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. ",
            "Review":"Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. ",
        },
        "DataTable":[{"Name":"Mona Williamson","Type":"vitae","version":11095678.74,"Device":"eum","Info":"ut"},{"Name":"Virgie Goyette","Type":"repellat","version":6.014166,"Device":"voluptas","Info":"voluptas"},{"Name":"Effie Hoppe","Type":"culpa","version":26.398998351,"Device":"ad","Info":"quia"},{"Name":"Olga Labadie","Type":"reprehenderit","version":19.418648,"Device":"velit","Info":"occaecati"},{"Name":"Zechariah Stehr","Type":"debitis","version":80.5,"Device":"nulla","Info":"qui"}],
    },
    "ExhibitName02":{
        "DataTable":[{"EmployeeName":"Tara Mueller","Employee Age":30,"Employee DOB":"2017-04-30T06:24:50 -06:-30"},{"EmployeeName":"Harrison Davidson","Employee Age":38,"Employee DOB":"2015-03-07T10:34:02 -06:-30"},{"EmployeeName":"Bean Solomon","Employee Age":37,"Employee DOB":"2015-05-06T12:22:33 -06:-30"},{"EmployeeName":"Higgins Fox","Employee Age":29,"Employee DOB":"2021-05-26T01:47:10 -06:-30"},{"EmployeeName":"Carrillo Fields","Employee Age":39,"Employee DOB":"2019-01-20T11:10:33 -06:-30"},{"EmployeeName":"Mercedes Rivas","Employee Age":38,"Employee DOB":"2018-11-09T06:32:32 -06:-30"},{"EmployeeName":"Bernadine Haney","Employee Age":23,"Employee DOB":"2017-12-07T06:14:07 -06:-30"},{"EmployeeName":"Love Frederick","Employee Age":30,"Employee DOB":"2015-06-26T02:50:28 -06:-30"},{"EmployeeName":"Ruby Cain","Employee Age":36,"Employee DOB":"2020-11-04T08:05:23 -06:-30"},{"EmployeeName":"Ewing Rice","Employee Age":23,"Employee DOB":"2015-08-18T04:12:38 -06:-30"},{"EmployeeName":"Mclean Saunders","Employee Age":21,"Employee DOB":"2014-01-16T06:35:57 -06:-30"},{"EmployeeName":"Hammond Reilly","Employee Age":32,"Employee DOB":"2020-12-16T03:16:55 -06:-30"},{"EmployeeName":"Ellis Witt","Employee Age":37,"Employee DOB":"2014-07-09T02:51:25 -06:-30"},{"EmployeeName":"Tommie Graves","Employee Age":32,"Employee DOB":"2020-08-18T12:30:16 -06:-30"},{"EmployeeName":"Small Bond","Employee Age":37,"Employee DOB":"2021-07-12T05:54:26 -06:-30"},{"EmployeeName":"Charmaine Hess","Employee Age":20,"Employee DOB":"2019-09-25T11:19:14 -06:-30"},{"EmployeeName":"Goldie Hines","Employee Age":37,"Employee DOB":"2014-05-26T11:28:21 -06:-30"},{"EmployeeName":"Jerri Rose","Employee Age":35,"Employee DOB":"2021-01-09T11:26:41 -06:-30"}]
    }
}

wb = xw.Book(fileName)
sheet = wb.sheets.active

data = json.load(open(metadata,"r"))

for exhibit in data['Exhibits']:
    tags = exhibit['Tags']
    for tag in tags:
        sheet.range(tag['TagCellAddress']).value = sample_data[exhibit['ExhibitName']]['Tags'][tag['TagName']]
        
    datatable = exhibit['Datatable']
    for dt in datatable:
        sheet.range(dt['DataTableCellAddress']).value = [list(row.values()) for row in sample_data[exhibit['ExhibitName']]['DataTable']]

wb.save("Sample_generated.xlsx")

GenerateMetaData.py

import xlwings as xw
import json
import re

fileName = "Sample_Exhibit_01.xlsx"
tag = re.compile(r"{[a-zA-Z0-9]*}")
datatable = re.compile(r"{(dt){1}_[0-9]*_[0-9]*_[a-zA-Z0-9]*}")

wb = xw.Book(fileName)
sheet = wb.sheets.active
cellRange = list(sheet.used_range)

Exhibits = []

# Value Traversal
for row in cellRange:
    # Check for Exhibits
    if row.value and row.value.startswith("Exhibit #:"):
        #Create a new Exhibit
        Exhibits.append({
            "ExhibitName": " ".join(row.value.split(":")[1:]).strip(),
            "ExhibitRangeName":"",
            "ExhibitRangeStartAddress":str(row.address).replace("$",""),
            "ExhibitRangeEndAddress":"",
        })
        
        lastCell = [cellRange.index(cellRange[i]) - 1 for i in range(cellRange.index(row)+1, len(cellRange)) if cellRange[i].value and cellRange[i].value.startswith("Exhibit #:")]
        if len(lastCell) == 0:
            lastCell.append(-1)
            
        #Update End of Exhibits
        Exhibits[-1]['ExhibitRangeEndAddress'] = str(cellRange[lastCell[0]].address).replace("$","")
        
        #Get or Set Range Name
        range_name = xw.Range(f"{Exhibits[-1]['ExhibitRangeStartAddress']}:{Exhibits[-1]['ExhibitRangeEndAddress']}").name
        if not range_name:
            range_name = Exhibits[-1]['ExhibitName'] + "_range"
        Exhibits[-1]['ExhibitRangeName'] = range_name

Tags = {}
DataTable = {}

for exhibit in Exhibits:
    tags = []
    dt = []
    
    for row in xw.Range(f"{exhibit['ExhibitRangeStartAddress']}:{exhibit['ExhibitRangeEndAddress']}"):
        if row.value:
            #Find Tag
            if tag.match(row.value):
                tags.append({
                    "TagName": row.name or row.value.replace("{","").replace("}",""),
                    "TagCellAddress": str(row.address).replace("$",""),
                    "RowNumber": str(row.address).replace("$","")[0],
                    "ColumnNumber": str(row.address).replace("$","")[1],
                    "ApiEndPoint":"",
                    "CellContent":row.value
                })
                
            #Find Datatable
            elif datatable.match(row.value):
                dt_name = row.value.replace("{","").replace("}","").split("_")[-1].strip()
                dt.append({
                    "DataTableName": dt_name,
                    "DataTableCellAddress": str(row.address).replace("$",""),
                    "RowNumber": str(row.address).replace("$","")[0],
                    "ColumnNumber": str(row.address).replace("$","")[1],
                    "MaxRows": str(row.value).split("dt_")[1].split("_")[0],
                    "MaxCols": str(row.value).split("dt_")[1].split("_")[1],
                    "ApiEndPoint":"",
                    "DataTableRepresentation":"",
                })
                
    exhibit['Tags'] = tags
    exhibit['Datatable'] = dt
    # Tags[exhibit['ExhibitName']] = tags
    # DataTable[exhibit['ExhibitName']] = dt


json.dump({
    "Exhibits":Exhibits
},open("metadata.json","w"),indent=2)

Sample_Exhibit_01.xlsx