sciris.sc_fileio.Spreadsheet

class Spreadsheet(*args, **kwargs)[source]

Bases: Blobject

A class for reading and writing Excel files in binary format. No disk IO needs to happen to manipulate the spreadsheets with openpyxl (or xlrd or pandas).

New in version 1.3.0: Changed default from xlrd to openpyxl and added self.wb attribute to avoid the need to reload workbooks.

Examples:

.. rubric:: Methods

freshbytes

Refresh the bytes object to accept new data

load

This function loads the spreadsheet from a file or object.

new

Shortcut method to create a new openpyxl workbook

openpyexcel

Legacy name for openpyxl()

openpyxl

Return a book as opened by openpyxl

pandas

Return a book as opened by pandas

readcells

Alias to loadspreadsheet()

save

tofile

Return a file-like object with the contents of the file.

update

Updated the stored spreadsheet with book instead

writecells

Specify cells to write.

xlrd

Legacy method to load from xlrd

new(**kwargs)[source]

Shortcut method to create a new openpyxl workbook

xlrd(reload=False, store=True, **kwargs)[source]

Legacy method to load from xlrd

openpyxl(reload=False, store=True, **kwargs)[source]

Return a book as opened by openpyxl

openpyexcel(*args, **kwargs)[source]

Legacy name for openpyxl()

pandas(reload=False, store=True, **kwargs)[source]

Return a book as opened by pandas

update(book)[source]

Updated the stored spreadsheet with book instead

readcells(wbargs=None, *args, **kwargs)[source]

Alias to loadspreadsheet()

writecells(cells=None, startrow=None, startcol=None, vals=None, sheetname=None, sheetnum=None, verbose=False, wbargs=None)[source]

Specify cells to write. Can supply either a list of cells of the same length as the values, or else specify a starting row and column and write the values from there.

Examples:

S = sc.Spreadsheet()
S.writecells(cells=['A6','B7'], vals=['Cat','Dog']) # Method 1
S.writecells(cells=[np.array([2,3])+i for i in range(2)], vals=['Foo', 'Bar']) # Method 2
S.writecells(startrow=14, startcol=1, vals=np.random.rand(3,3)) # Method 3
S.save('myfile.xlsx')
freshbytes()

Refresh the bytes object to accept new data

load(source=None)

This function loads the spreadsheet from a file or object. If no input argument is supplied, then it will read self.bytes, assuming it exists.

tofile(output=True)

Return a file-like object with the contents of the file.

This can then be used to open the workbook from memory without writing anything to disk e.g.

  • book = openpyxl.load_workbook(self.tofile())

  • book = xlrd.open_workbook(file_contents=self.tofile().read())