Adding row data in spreadsheet using pyexcel

Question

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)

chfw · Accepted Answer · 2015-03-30 09:22:35Z

11

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.

edited Mar 30, 2015 at 9:22

answered Mar 30, 2015 at 8:28

chfw

4,6122 gold badges32 silver badges32 bronze badges

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

4 Comments

bobZBy Over a year ago

One could not ask for a better answer. Nor from a better source than from the owner. I tried to vote up your answer but haven't the reputation points to do so. I will as soon as I do. As for another library, Im quite happy to stick with Pyexcel. I will eventually play with others im sure but not for this project. I find your lib to be easy to work with. Well documented to. I knew the answer would be so simple that I would kick myself for not seeing it! Funny thing, I was dreaming of code when I herd my phone whistle, awoke to find my problem solved. Thank you very much CHFW.

chfw Over a year ago

That's fine. My library is intended for data processing involving multiple excel files that usually are mixed(messed) up by different file formats, and accidentally it provides a simple interface for the same job involving a single file format. If you need to work with Fonts, Formulas, Styles and Images, you shall not use my library. Other than that, feature requests are welcome.

bobZBy Over a year ago

chfw, Where would one add a feature request? As in maintaining key order as declared order, not dictionary order?

Mark Over a year ago

chfw your library is excellent and has made my life and job a lot easier, thanks. @thisGuyDidSomethingOnce between pyexcel and openpyxl I've been able to get everything I've ever thought possible done when using them both in combination together. Functionality + Formatting with both.

Collectives™ on Stack Overflow

Adding row data in spreadsheet using pyexcel

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related