Write dataframe to new sheet in existing ODS

[luofeiyu]

We can write dataframe into a new excel file:

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

"/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".

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

"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:

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

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

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

It encounter error:

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

Same error:

Code: Select all

    ValueError: Append mode is not supported with odf!

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)?

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

[karolus]

Code: Select all

import numpy as np

doc = XSCRIPTCONTEXT.getDocument()
sheet = doc.Sheets[0]
R,C = 100, 2
x1 = np.random.randn(R, C)
out = x1.tolist()
sheet[:R,:C].DataArray = out

[luofeiyu]

Enter into script directory:

Code: Select all

cd  .config/libreoffice/4/user/Scripts/python

Edit the python function which only contain two lines:

Code: Select all

vim  write.py
def write():
    import numpy as np

Call it in macro:

numpy.png (70.73 KiB) Viewed 8157 times

It can't fix the issue:

Code: Select all

sudo  python3 -m pip install numpy --upgrade   --target=/opt/libreoffice24.8/program