So I'm trying to write a python script that fills an excel sheet table with data(list of dictionaries). The problem is the table I'm trying to fill in the excel sheet is in the middle of the sheet (it doesnt start from A1 ).
So I wrote this method that locates from where the table starts and then fill it
def generateExcel(data_list):
file_path = 'Finaltemplatetesting.xlsx'
sheet_name = 'Main'
workbook = openpyxl.load_workbook(file_path)
# Select the sheet
sheet = workbook[sheet_name]
# Find the starting cell (top-left cell) of the table
i=0
for row in sheet.iter_rows(values_only=True):
i=i+1
if 'Project' in row:
header_row = row
break
header_col = header_row.index('Project')
#intializing the starting row
start_row=i+2
# Fill the table with the new data
for data in data_list:
sheet.insert_rows(start_row)
for row_data in data:
for col_idx, col_name in enumerate(header_row):
cell = sheet.cell(row=start_row, column=header_col + col_idx, value=row_data.get(col_name))
start_row += 1
workbook.save("Finaltempalterrrrrr.xlsx")
there is some cells under the table that I'm using for calculating... ect so my idea was to add a row just under the columns row using sheet.insert_rows()
and then fill it.
When I run this function it corrupts my Excel sheet like if i open the sheet i get
we found a problem with some content in 'finaltemplaterrrrr.xlsx' , do you want us to try to recover as much as we can ? if you trust the source of this workbook, click yes.
and then after I click yes it closes the sheet.
What is intresting is if I try to add only one row this way it works fine and it adds the row exactly like i want
sheet.insert_rows(start_row)
for row_data in data:
for col_idx, col_name in enumerate(header_row):
cell = sheet.cell(row=start_row, column=header_col + col_idx, value=row_data.get(col_name))
workbook.save("Finaltempalterrrrrr.xlsx")
I dont know why it doesn't work when I try to add multiple rows using a for loop just as shown in the code above
from Excel sheet is corrupted after using sheet.insert_rows() in python
No comments:
Post a Comment