I wrote this simple program for writing column headers to empty cells above a data table in a pre-existing Excel .xlsx file. I don't get any errors when I run this, but when I open the file (with a single sheet), the whole table is gone and it's blank; there's not even any of the headers that it was supposed to write in. Can anyone please help me figure out why this happened? I can get the data again, I just need this to work.
import pandas as pd
from openpyxl import load_workbook
headers = []
# code not shown, but just prompts user for column headers and saves in list 'headers'
#open .xlsx file
book = load_workbook(r'path.xlsx')
writer = pd.ExcelWriter(r'path.xlsx', engine='xlsxwriter')
#write column headers from list into empty cells
writer.columns = headers[:]
#save and close
writer.save()
writer.close()
book.close()
You can try out this code
import pandas as pd
# Read excel file (.xlsx)
book_df = pd.read_excel(r'path.xlsx')
# headers is list of header names like ['header_1','header_2','header_3']
book_df.columns = headers
book_df.to_excel(r'modified_file_name.xlsx',index=False)
# In case you want the file in the same name , make sure the file is not open else you may get permission error
book_df.to_excel(r'path.xlsx',index=False)