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

Welcome to StackOverflow! It looks like your code successfully outputs the contents of a dataframe into a cell. So what is the problem? Please edit the question to precisely describe the output you desire. — Jim K
– Jim K, Commented May 23, 2022 at 18:05
Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. — Community
– Community Bot, Commented May 23, 2022 at 18:09
I want to write pandas dataframe table in libreoffice calc. It should be in tabular form spread over multiple cells, but here it is being written as a string in a single cell. — Vivek0056
– Vivek0056, Commented May 24, 2022 at 3:40

Arun Kumar Khattri · Accepted Answer · 2023-07-10 07:51:29Z

2

import pandas as pd

data = {
    'name': ['Anil', 'Raju', 'Arun'],
    'age' : [32, 34, 45]
}

df = pd.DataFrame(data)

with pd.ExcelWriter("pd_to_ods.ods", engine="odf") as writer:
    df.to_excel(writer)

and don't forget to install "odfpy", if not already there...

pip install odfpy

answered Jul 10, 2023 at 7:51

Arun Kumar Khattri

1,66218 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jim K · Accepted Answer · 2022-05-25 16:32:16Z

0

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)

edited May 25, 2022 at 16:32

answered May 24, 2022 at 16:09

Jim K

14.1k2 gold badges26 silver badges54 bronze badges

11 Comments

Vivek0056 Over a year ago

Thanks for the reply. My data is much larger than the above example. If I have to enter list directly it will take extra-time for each list and there will be no use of pandas dataframe. Is there any way to write pandas data frame directly in LibreOffice calc.? (I have used xlwings with python and pandas in window os. There it write dataframe directly in excel sheet).

Jim K Over a year ago

Not sure what you mean by "directly" (and I'm not familiar with xlwings). It seems like all you need to do is make your own python method that takes a dataframe parameter and writes it to Calc in the way you want. Then call the method for all of your dataframes, no matter how large or complex they may be. You could even extend pandas.DataFrame to implement this functionality if you want something elegant. "...and there will be no use of pandas dataframe." Okay, so if you don't need it, then don't use it, right? Or is this a homework assignment?

Jim K Over a year ago

"extra-time"—do you mean that with so much data, Calc gets very slow? In that case, setDataArray() is probably your fastest option using LibreOffice. I would think that the pyoo code in my answer is simply a wrapper around that. But it would be much faster to create a CSV file instead and dump all the dataframe data into it. When finished, open the CSV in LibreOffice. Also if you have so much data, then instead of Calc, consider using LibreOffice Base with PostgreSQL.

Vivek0056 Over a year ago

Sorry, I may not have framed it correctly. I want to enter pandas dataframe as it is, to LibreOffice calc, without breaking it into list or array. Is there any method for the same..?

Jim K Over a year ago

Sorry, I feel like you aren't sure what you're asking, and you probably don't know what xlwings really does. "...enter pandas dataframe as it is..."—a dataframe is an object in the computer's memory, so it must be serialized in some way to put it into a file. In this case, you can write your own method to store the dataframe into a spreadsheet, and storing by groups of values (arrays) is faster than storing each value into each cell individually.

|

Collectives™ on Stack Overflow

how to write pandas dataframe in libreoffice calc using python?

2 Answers 2

Comments

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related