I have the following code that appends a newly generated data to an already existing Excel Worksheet:
def save_data(data, filename, worksheet_name):
try:
# Load existing Excel file into a dictionary of DataFrames
try:
existing_data = pd.read_excel(filename, sheet_name=None)
except FileNotFoundError:
existing_data = {}
# Check if the worksheet_name already exists
if worksheet_name in existing_data:
existing_data[worksheet_name] = pd.concat([existing_data[worksheet_name], data], ignore_index=True)
else:
existing_data[worksheet_name] = data
# Write the combined data to Excel with multiple sheets
while True:
try:
with pd.ExcelWriter(filename, engine='xlsxwriter') as writer:
for sheet_name, sheet_data in existing_data.items():
sheet_data.to_excel(writer, index=False, sheet_name=sheet_name)
break
except PermissionError:
input('Dashboard open. Please close and press "Enter"')
print(f"Data saved successfully to {filename} - Worksheet: {worksheet_name}")
except Exception as e:
print(f"An error occurred: {e}")
The data is appended succesfully, without overwriting an old data. However, the code changes the properties of the cells in the whole Workbook (the Workbook has few Worksheets) such as:
- date changes from short form into a longer form (with time)
- the width of the columns changes, so that one cannot see the whole text Even though I change it every time and save the changes, after conducting the save function, it always come back to, what I think, 'default settings'. How can I deal with it?
I haven not tried anything, as in my code there is nothing that suggests these changes. I expect the data to be succesfully appended, without changing other properties of the Worksheets.