-
-
Save gokulraja/cdd70304131e526fc0f9b04bcdd6aaf9 to your computer and use it in GitHub Desktop.
Python Xlwings
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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") |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| { | |
| "Exhibits": [ | |
| { | |
| "ExhibitName": "ExhibitName01", | |
| "ExhibitRangeName": "ExhibitName01_range", | |
| "ExhibitRangeStartAddress": "B1", | |
| "ExhibitRangeEndAddress": "F15", | |
| "Tags": [ | |
| { | |
| "TagName": "companyName", | |
| "TagCellAddress": "C4", | |
| "RowNumber": "C", | |
| "ColumnNumber": "4", | |
| "ApiEndPoint": "", | |
| "CellContent": "{companyName}" | |
| }, | |
| { | |
| "TagName": "CR", | |
| "TagCellAddress": "D4", | |
| "RowNumber": "D", | |
| "ColumnNumber": "4", | |
| "ApiEndPoint": "", | |
| "CellContent": "{CR}" | |
| }, | |
| { | |
| "TagName": "ORT", | |
| "TagCellAddress": "E4", | |
| "RowNumber": "E", | |
| "ColumnNumber": "4", | |
| "ApiEndPoint": "", | |
| "CellContent": "{ORT}" | |
| }, | |
| { | |
| "TagName": "ORR", | |
| "TagCellAddress": "F4", | |
| "RowNumber": "F", | |
| "ColumnNumber": "4", | |
| "ApiEndPoint": "", | |
| "CellContent": "{ORR}" | |
| }, | |
| { | |
| "TagName": "Summary", | |
| "TagCellAddress": "C6", | |
| "RowNumber": "C", | |
| "ColumnNumber": "6", | |
| "ApiEndPoint": "", | |
| "CellContent": "{Summary}" | |
| }, | |
| { | |
| "TagName": "Review", | |
| "TagCellAddress": "C8", | |
| "RowNumber": "C", | |
| "ColumnNumber": "8", | |
| "ApiEndPoint": "", | |
| "CellContent": "{Review}" | |
| } | |
| ], | |
| "Datatable": [ | |
| { | |
| "DataTableName": "OSInfo", | |
| "DataTableCellAddress": "B10", | |
| "RowNumber": "B", | |
| "ColumnNumber": "1", | |
| "MaxRows": "5", | |
| "MaxCols": "5", | |
| "ApiEndPoint": "", | |
| "DataTableRepresentation": "" | |
| } | |
| ] | |
| }, | |
| { | |
| "ExhibitName": "ExhibitName02", | |
| "ExhibitRangeName": "ExhibitName02_range", | |
| "ExhibitRangeStartAddress": "B16", | |
| "ExhibitRangeEndAddress": "F35", | |
| "Tags": [], | |
| "Datatable": [ | |
| { | |
| "DataTableName": "EmployeeInfo", | |
| "DataTableCellAddress": "B18", | |
| "RowNumber": "B", | |
| "ColumnNumber": "1", | |
| "MaxRows": "18", | |
| "MaxCols": "5", | |
| "ApiEndPoint": "", | |
| "DataTableRepresentation": "" | |
| } | |
| ] | |
| } | |
| ] | |
| } |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| { | |
| "Exhibits": [ | |
| { | |
| "ExhibitName": "ExhibitName01", | |
| "ExhibitRangeName": "ExhibitName01_range", | |
| "ExhibitRangeStartAddress": "B1", | |
| "ExhibitRangeEndAddress": "F15", | |
| "Tags": [ | |
| { | |
| "TagName": "companyName", | |
| "TagCellAddress": "C4", | |
| "RowNumber": "C", | |
| "ColumnNumber": "4", | |
| "ApiEndPoint": "", | |
| "CellContent": "{companyName}" | |
| }, | |
| { | |
| "TagName": "CR", | |
| "TagCellAddress": "D4", | |
| "RowNumber": "D", | |
| "ColumnNumber": "4", | |
| "ApiEndPoint": "", | |
| "CellContent": "{CR}" | |
| }, | |
| { | |
| "TagName": "ORT", | |
| "TagCellAddress": "E4", | |
| "RowNumber": "E", | |
| "ColumnNumber": "4", | |
| "ApiEndPoint": "", | |
| "CellContent": "{ORT}" | |
| }, | |
| { | |
| "TagName": "ORR", | |
| "TagCellAddress": "F4", | |
| "RowNumber": "F", | |
| "ColumnNumber": "4", | |
| "ApiEndPoint": "", | |
| "CellContent": "{ORR}" | |
| }, | |
| { | |
| "TagName": "Summary", | |
| "TagCellAddress": "C6", | |
| "RowNumber": "C", | |
| "ColumnNumber": "6", | |
| "ApiEndPoint": "", | |
| "CellContent": "{Summary}" | |
| }, | |
| { | |
| "TagName": "Review", | |
| "TagCellAddress": "C8", | |
| "RowNumber": "C", | |
| "ColumnNumber": "8", | |
| "ApiEndPoint": "", | |
| "CellContent": "{Review}" | |
| } | |
| ], | |
| "Datatable": [ | |
| { | |
| "DataTableName": "OSInfo", | |
| "DataTableCellAddress": "B10", | |
| "RowNumber": "B", | |
| "ColumnNumber": "1", | |
| "MaxRows": "5", | |
| "MaxCols": "5", | |
| "ApiEndPoint": "", | |
| "DataTableRepresentation": "" | |
| } | |
| ] | |
| }, | |
| { | |
| "ExhibitName": "ExhibitName02", | |
| "ExhibitRangeName": "ExhibitName02_range", | |
| "ExhibitRangeStartAddress": "B16", | |
| "ExhibitRangeEndAddress": "F35", | |
| "Tags": [], | |
| "Datatable": [ | |
| { | |
| "DataTableName": "EmployeeInfo", | |
| "DataTableCellAddress": "B18", | |
| "RowNumber": "B", | |
| "ColumnNumber": "1", | |
| "MaxRows": "18", | |
| "MaxCols": "5", | |
| "ApiEndPoint": "", | |
| "DataTableRepresentation": "" | |
| } | |
| ] | |
| } | |
| ] | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment