0

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
Vivek0056
  • 1
  • 4
  • 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 May 23 '22 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 May 23 '22 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 May 24 '22 at 03:40
  • Also posted at https://ask.libreoffice.org/t/how-to-write-pandas-dataframe-in-libreoffice-calc/77908. – Jim K May 25 '22 at 17:21

2 Answers2

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)
Jim K
  • 12,824
  • 2
  • 22
  • 51
  • 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). – Vivek0056 May 24 '22 at 17:26
  • 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 May 24 '22 at 17:51
  • "extra-time"—do you mean that with so much data, Calc gets very slow? In that case, [setDataArray()](https://api.libreoffice.org/docs/idl/ref/interfacecom_1_1sun_1_1star_1_1sheet_1_1XCellRangeData.html) 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. – Jim K May 24 '22 at 18:03
  • 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..? – Vivek0056 May 24 '22 at 18:11
  • 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](https://stackoverflow.com/a/11818001/5100564) 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. – Jim K May 24 '22 at 18:58
  • Instead of worrying about how to understand or explain this, why not go with the perfectly workable solution I described? What disadvantage does it have? It's fast, easy to understand, and doesn't require much code. – Jim K May 24 '22 at 19:26
  • I want to do the same in LibreOffice calc. I compile data to Big data to Datarame, to avoid compilation errors. Hence my requirement is specific. – Vivek0056 May 25 '22 at 00:42
  • I am very well sure of what I am asking for.. Please refer to above xlwings example code for reference. – Vivek0056 May 25 '22 at 00:54
  • See my edited answer. The question finally shows your goal specifically, which is simple code. Hopefully you can see now that the code in my solution is very simple, possibly even shorter than your xlwings code if you have a lot of dataframes. The only difference between my solution and xlwings is the syntax, and I don't see how that would be a problem for anyone. – Jim K May 25 '22 at 15:59
  • Thank for the reply. @Jim K - I am in the learning phase. Can you please help by providing the code for putting the index and heading along with the data value? Also, please guide if any value in data is null what will be the result, will there be any error..? – Vivek0056 May 26 '22 at 09:12
  • My main purpose was - Is there any simple way (as in xlwings) that already exists that I am not aware of, for writing pandas dataframe in LibreOffice calc? (prerequisite - UNO, Python, and Linux os) – Vivek0056 May 26 '22 at 09:18
0
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
Arun Kumar Khattri
  • 1,519
  • 15
  • 25