Saturday 12 August 2023

Excel sheet is corrupted after using sheet.insert_rows() in python

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