Write Dataframe into ODS format

Question

I am reading a .xlxs file using pandas library and extracting the dataframe. Now I am trying to create a file with .ods extension and write the dataframe into it using pyexcel_ods library. Here is my code :--

import pandas as pd
from pyexcel_ods import save_data
from collections import OrderedDict

self.current_df = pd.read_excel('filename')
data = OrderedDict()
data.update(df)
save_data("as.ods", data)

It is throwing Error

{TypeError}'int' object is not iterable

Feel free to ask for more code.

Note :-- I am using Python 3.

score 11 · Answer 1 · edited Jun 28 '22 at 08:08

11

Please note that in recent versions of Pandas (current is 1.1) has been implemented support for ODS format in functions like pd.ExcelWriter() and pd.read_excel(). You only need to specify the propper engine "odf" to be able of working with OpenDocument file formats (.odf, .ods, .odt)

You will need the odfpy package installed.

edited Jun 28 '22 at 08:08

buhtz

10,774
18
76
149

answered Aug 02 '20 at 22:25

ragan

203
2
11

Gideon Maina · Accepted Answer · 2019-11-19T08:10:26.270

Soham try this, I think the issue with your solution is that the save_data function from py_ods is not getting the data in the format that it needs.

The explanation is in the comments.

# coding: utf-8

import pandas as pd
from pyexcel_ods import save_data
from collections import OrderedDict

def save_ods_from_excel(excel_file, target_ods_file):
    # First read into dataframe
    df = pd.read_excel(excel_file)
    # Change everything to string since we're just writing
    df = df.astype(str)
    # Initiliaze data to be written as an empty list, as pyods needs a list to write
    whole_data_list = []
    # Initiliaze the empty dict() for the data
    d = OrderedDict()
    # Write the columns first to be the first entries 
    whole_data_list.append(list(df.columns))
    # loop through data frame and update the data list
    for index, row in df.iterrows():
        whole_data_list.append(list(row.values))
    # Populate dict() with updated data list
    d.update({"Moved sheet": whole_data_list})
    # Finally call save_data() from pyods to store the ods file
    save_data(target_ods_file, d)

Tried the above with this data from microsoft

>>> save_ods_from_excel('/Users/gkm/Downloads/Financial Sample.xlsx', '/tmp/sample-financial.ods')

More info the pyexcel ods docs

Thanks Man.Works perfectly,saved my day. – Soham Nov 19 '19 at 08:01 — Soham, Nov 19 '19 at 08:01
Great, glad I could help – Gideon Maina Nov 19 '19 at 08:03 — Gideon Maina, Nov 19 '19 at 08:03

oppressionslayer · Answer 3 · 2019-11-19T06:53:36.040

1

Try this:

from collections import OrderedDict
from pyexcel_ods import get_data 

current_df = pd.read_excel('array.xlsx')
data = OrderedDict()
data.update({ "Sheet_1": current_df.to_numpy().tolist() })
save_data("as.ods", data)

data = get_data("as.ods") 
data
#OrderedDict([('Sheet_1',
#             [['b', 121], ['c', 98], ['d', 9], ['e', 100], ['f', 45]])])

edited Nov 19 '19 at 06:53

answered Nov 19 '19 at 06:36

oppressionslayer

6,942
2
7
24

Getting same error -- TypeError: 'int' object is not iterable – Soham Nov 19 '19 at 06:44

Write Dataframe into ODS format

3 Answers3