Write dataframe to new sheet in existing ODS
Posted: Mon Feb 03, 2025 3:17 am
We can write dataframe into a new excel file:
"/tmp/sample.xlsx" is a new created xlsx file with worksheet--sheet1 containing "df1".
Now we can add another dataframe into the "/tmp/sample.xlsx" with an existing worksheet "sheet1".
"engine" can be "xlsxwriter" or "openpyxl" for "xlsx" type file .
Replace the "xlsx" with "ods",i can't write dataframe into an openoffice-ods excel with an existing file.
It works fine to create new "ods" file containing a dataframe:
Now add a new sheet in the "/tmp/sample.ods":
It encounter error:
Try another engine "odf":
Same error:
Without the "mode='a'" argument,sheet "sheet1" will be overlayed,only sheet "sheet2" left.
Is there a smart way to write dataframe into an new sheet(sheet2) in ods which already containing old sheet(sheet1)?
Code: Select all
x1 = np.random.randn(100, 2)
df1 = pd.DataFrame(x1)
with pd.ExcelWriter('/tmp/sample.xlsx') as writer:
df1.to_excel(writer, sheet_name='sheet1')
Now we can add another dataframe into the "/tmp/sample.xlsx" with an existing worksheet "sheet1".
Code: Select all
x2 = np.random.randn(100, 2)
df2 = pd.DataFrame(x2)
with pd.ExcelWriter('/tmp/sample.xlsx', engine='xlsxwriter',mode='a') as writer:
df2.to_excel(writer, sheet_name='sheet2')
Replace the "xlsx" with "ods",i can't write dataframe into an openoffice-ods excel with an existing file.
It works fine to create new "ods" file containing a dataframe:
Code: Select all
x1 = np.random.randn(100, 2)
df1 = pd.DataFrame(x1)
with pd.ExcelWriter('/tmp/sample.ods') as writer:
df1.to_excel(writer, sheet_name='sheet1')
Code: Select all
x2 = np.random.randn(100, 2)
df2 = pd.DataFrame(x2)
with pd.ExcelWriter('/tmp/sample.ods', engine='xlsxwriter',mode='a') as writer:
df2.to_excel(writer, sheet_name='sheet2')
Code: Select all
ValueError: Append mode is not supported with xlsxwriter!
Try another engine "odf":
Code: Select all
x2 = np.random.randn(100, 2)
df2 = pd.DataFrame(x2)
with pd.ExcelWriter('/tmp/sample.ods', engine='odf',mode='a') as writer:
df2.to_excel(writer, sheet_name='sheet2')
Code: Select all
ValueError: Append mode is not supported with odf!
Is there a smart way to write dataframe into an new sheet(sheet2) in ods which already containing old sheet(sheet1)?
Edit: Changed subject, was Smart way to write dataframe into an new sheet(sheet2) in ods which already containing old sheet(sheet1)? Make your post understandable by others The topic subject should be a summary, not a complete statement of the problem -- MrProgrammer, forum moderator |