python - Smart way to write dataframe into an openoffice-ods excel with an existing file? - Stack Overflow

We can write dataframe into a new excel file:

x1 = np.random.randn(100, 2)
df1 = pd.DataFrame(x1)
with pd.ExcelWriter('/tmp/sample.xlsx') as writer:  
    df1.to_excel(writer, sheet_name='x1')

/tmp/sample.xlsx is a new created xlsx file containing df1.

Now we can add another dataframe into the /tmp/sample.xlsx with an existing file x1.

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='x2')

engine can be xlsxwriter or openpyxl for xlsx type file .

Replace the xlsx with ods,we can't write dataframe into an openoffice-ods excel with an existing file.

It works fine to create new ods file containing a dataframe:

x1 = np.random.randn(100, 2)
df1 = pd.DataFrame(x1)
with pd.ExcelWriter('/tmp/sample.ods') as writer:  
    df1.to_excel(writer, sheet_name='x1')

Now add a new sheet in the '/tmp/sample.ods'.

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='x2')

It encounter error:

ValueError: Append mode is not supported with xlsxwriter!

Try another engine odf:

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='x2')

Same error:

ValueError: Append mode is not supported with odf!

Without the mode='a' argument,sheet x1 will be overlayed,only sheet x2 left. Is there a smart way to write dataframe into an openoffice-ods excel with an existing file?