sciris.sc_fileio.Spreadsheet

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

Bases: sciris.sc_fileio.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.

Methods

freshbytes

Refresh the bytes object to accept new data

load

This function loads the spreadsheet from a file or object.

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

Return a book as opened by xlrd

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

Return a book as opened by 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())