Openpyxl partially erases data when copying sheets to Excel
import pandas as pd
import openpyxl
df = pd.read_excel('file.xlsx', sheet_name='list1')
with pd.ExcelWriter('file.xlsx', engine="openpyxl", mode="a") as writer:
df.to_excel(writer, index=False, sheet_name='list2')
This code reads one sheet of the Excel file and copies all the data to the adjacent sheet, but then it turns out that some of the information from one column is lost in the original sheet and in the new sheet. And each time the code is executed, a different amount of information is erased. The problem is only with one column of all. Maybe the cells in this column are too big and have characters '<>/
But how is this possible? If the code has a clear command to "read the first sheet" - what makes it change? The 3 cell options for the problematic column are shown below. They should all be the same size, but after copying they became different.
If you remove 'engine="openpyxl"' from the code and change 'mode' to 'w', then the data is copied perfectly, but it erases the first sheet, and I need both sheets. Any work without 'engine="openpyxl"' goes well, but adding 'engine="openpyxl"' is where the problems start.
Versions I usually:
Pandas - 1.4.4
Openpyxl - 3.0.10
I write code in Jupyter Notebook