how to write pandas dataframe in libreoffice calc using python?

Question

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

example code used in xlwings in window os just for reference (I want to achieve same with simple code in Libreoffice calc in ubuntu/Linux, if possible..)
import pandas as pd
import xlwings as xlw

# Connecting with excel workbook
file=xlw.Book("data.xlsx") 
# connection with excel sheet
sh1=file.sheets('sheet1')

df=pd.DataFrame()
df['Name']=['Anil','Raju','Arun']
df['Age']=['32','34','45']

sh1.range('A4').value=df

Answer 1

From the pyoo documentation, a range of values is set with a list of lists.

sheet[1:3,0:2].values = [[3, 4], [5, 6]]

To get a list of lists from a dataframe, the following code is recommended at How to convert a Python Dataframe to List of Lists? :

lst = [df[i].tolist() for i in df.columns]

EDIT:

Write a function called insertDf() that does the two things above, calculating the required indices. Then instead of sh1.range('A4').value=df, write insertDf(df,'A4',sh1).

Or perhaps more elegant is to create a class called CalcDataFrame that extends pandas.DataFrame to add a method called writeCells().

Also, it would be easier to write location arguments as (row number, column number) instead of a 'column letters&row number' combined string.

df = CalcDataFrame()
df['Name']=['Anil','Raju','Arun']
df['Age']=['32','34','45']
df.writeCells(sh1,1,4)