How to write pandas dataframe in libreoffice calc?

[vivek_kishor2000]

I am using the following code in ubuntu 20.

import pyoo
import os
import uno
import pandas as pd
os.system("/usr/lib/libreoffice/program/soffice.bin --headless --invisible --nocrashreport --nodefault --nofirststartwizard --nologo --norestore --accept='socket,host=localhost,port=2002,tcpNoDelay=1;urp;StarOffice.ComponentContext'")
df=pd.Dataframe()
df['Name']=['Anil','Raju','Arun']
df['Age']=['32','34','45']
desktop = pyoo.Desktop('localhost', 2002)
doc = desktop.open_spreadsheet("/home/vivek/Documents/Libre python trial/oi_data.ods")
sh1=doc.sheets['oi_data']
sh1[1,4].value=df
doc.save()

It gives all data in a single cell as a string:

'Name age0 Anil 321 Raju 342 Arun 45'

I want to write a DataFrame in LibreOffice Calc in columns & rows of sheet like this :

   Name  age
0  Anil  32
1  Raju  34
2  Arun  45

[jimk]

Also posted at https://stackoverflow.com/questions/72352576/how-to-write-pandas-dataframe-in-libreoffice-calc-using-python along with my answer.

[elmau]

Because df is a pandas DataFrame:

print(type(df))
<class 'pandas.core.frame.DataFrame'>

You need get list or tuples for set in cell range.

data = (('Name', 'Age'),)
data += tuple(df.itertuples(index=False, name=None))

print(type(data))
print(data)

<class 'tuple'>
(('Name', 'Age'), ('Anil', '32'), ('Raju', '34'), ('Arun', '45'))

[vib]

Pandas for LibreOffice extension can also be used. It comes with OOO Development Tools (OooDev) as well.

On GitHub the Pandas extension can be installed and run in LibreOffice in a Codespace directly in a web browser.

Using OooDev the result can easily be added to the spreadsheet.

from __future__ import annotations
import pandas as pd

from ooodev.macro.macro_loader import MacroLoader
from ooodev.office.calc import Calc
from ooodev.utils.info import Info
from ooodev.utils.lo import Lo

def _do_work() -> None:
    # create a data frame and add some data to it.
    df = pd.DataFrame()
    df["Name"] = ["Anil", "Raju", "Arun"]
    df["Age"] = ["32", "34", "45"]

    data = (("Name", "Age"),)  # Column names
    # convert data frame values into something that Calc can use.
    data += tuple(df.itertuples(index=False, name=None))

    sheet = Calc.get_active_sheet()
    # set the data starting at cell A1
    Calc.set_array(values=data, sheet=sheet, name="A1")

def demo(*args) -> None:
    with MacroLoader():
        # using MacroLoader so we can use OooDev in macros
        _do_work()

g_exportedScripts = (demo,)