Adding row data in spreadsheet using pyexcel

Issue

I’m trying to add row data using pyexcel. In the cookbook I have found a method called update_rows() it takes three arguments (fileToRead, list/dictionary, outputFile)
I get the following error: NotImplementedError: We do not overwrite files

I can see that this is not the method I’m looking for. I’m open to any module making use of .ods format if it better suits my needs.

import os
import pyexcel
import pyexcel.ext.ods
from pyexcel.cookbook import update_rows
import datetime


def mkExcel(dataList, tDate, pathToFile):
    whereToGo = os.path.join(os.path.expanduser(pathToFile), "Archive_%s.ods") % tDate

    if not os.path.exists(whereToGo):
        dataList = pyexcel.utils.dict_to_array(dataList)
        # "output.xls" "output.xlsx" "output.ods" "output.xlsm"
        dataList = pyexcel.Sheet(dataList)
        print
        dataList
        dataList.save_as(whereToGo)
    else:
        dSheet = pyexcel.load(whereToGo, name_columns_by_row=0)
        dataList = pyexcel.utils.dict_to_array(dataList)

        custom_row = {"Row -1": [11, 12, 13]}
        ## update_rows(existing.ods, custom_row, new.ods)
        update_rows(dSheet, custom_row, whereToGo)


now = datetime.datetime.now()
now = '%s-%s-%s' % (now.year, now.month, now.day)

example_dict = {"Column 1": [1, 2, 3], "Column 2": [4, 5, 6], "Column 3": [7, 8, 9]}

here = os.getcwd()
mkExcel(example_dict, now, here)

Solution

I am the owner of pyexcel and have updated documentation regarding your use case. More details on row manipulation can be found in api referance Sheet.Row.

Here’s a copy of the example code to add a new row:

>>> import pyexcel as pe
>>> import pyexcel.ext.xls
>>> sheet = pe.get_sheet(file_name="example.xls")
>>> sheet # just to show what the sheet contains
Sheet Name: pyexcel
+----------+----------+----------+
| Column 1 | Column 2 | Column 3 |
+----------+----------+----------+
| 1        | 4        | 7        |
+----------+----------+----------+
| 2        | 5        | 8        |
+----------+----------+----------+
| 3        | 6        | 9        |
+----------+----------+----------+
>>> sheet.row += [12, 11, 10] # now add it to its row
>>> sheet.save_as("new_example.xls") # save it to a new file
>>> pe.get_sheet(file_name="new_example.xls") # read it back
Sheet Name: pyexcel
+----------+----------+----------+
| Column 1 | Column 2 | Column 3 |
+----------+----------+----------+
| 1        | 4        | 7        |
+----------+----------+----------+
| 2        | 5        | 8        |
+----------+----------+----------+
| 3        | 6        | 9        |
+----------+----------+----------+
| 12       | 11       | 10       |
+----------+----------+----------+

If you would like to use other libraries, odfpy and ezodf are what you might consider.

Answered By – chfw

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published