sciris.sc_fileio.Spreadsheet

class Spreadsheet(*args, **kwargs)

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)

Return a book as opened by xlrd

openpyxl(reload=False, store=True, **kwargs)

Return a book as opened by openpyxl

openpyexcel(*args, **kwargs)

Legacy name for openpyxl()

pandas(reload=False, store=True, **kwargs)

Return a book as opened by pandas

update(book)

Updated the stored spreadsheet with book instead

readcells(wbargs=None, *args, **kwargs)

Alias to loadspreadsheet()

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

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())