sciris.sc_fileio.Spreadsheet

class Spreadsheet(*args, **kwargs)

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)

Shortcut method to create a new openpyxl workbook

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

Legacy method to load from 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())