def to_excel(self, filename=None):
'''
Export results as XLSX
Args:
filename (str): if None, return string; else, write to file
Returns:
An sc.Spreadsheet with an Excel file, or writes the file to disk
'''
resdict = self.export_results(for_json=False)
result_df = pd.DataFrame.from_dict(resdict)
result_df.index = self.tvec
result_df.index.name = 'Day'
par_df = pd.DataFrame.from_dict(sc.flattendict(self.pars, sep='_'),
orient='index',
columns=['Value'])
par_df.index.name = 'Parameter'
spreadsheet = sc.Spreadsheet()
spreadsheet.freshbytes()
with pd.ExcelWriter(spreadsheet.bytes, engine='xlsxwriter') as writer:
result_df.to_excel(writer, sheet_name='Results')
par_df.to_excel(writer, sheet_name='Parameters')
spreadsheet.load()
if filename is None:
output = spreadsheet
else:
output = spreadsheet.save(filename)
return output
def to_excel(self, filename=None):
'''
Export results as XLSX
Args:
filename (str): if None, return string; else, write to file
Returns:
An sc.Spreadsheet with an Excel file, or writes the file to disk
'''
spreadsheet = sc.Spreadsheet()
spreadsheet.freshbytes()
with pd.ExcelWriter(spreadsheet.bytes, engine='xlsxwriter') as writer:
for key in self.result_keys():
result_df = pd.DataFrame.from_dict(
sc.flattendict(self.results[key], sep='_'))
result_df.to_excel(writer, sheet_name=key)
spreadsheet.load()
if filename is None:
output = spreadsheet
else:
output = spreadsheet.save(filename)
return output
'big': {'bg_color':'#ffcccc'}}
formatdata = pl.zeros((nrows+1, ncols), dtype=object) # Format data needs to be the same size
formatdata[1:,:] = 'plain' # Format data
formatdata[1:,:][testdata[1:,:]>0.7] = 'big' # Find "big" numbers and format them differently
formatdata[0,:] = 'header' # Format header
sc.savespreadsheet(filename=files.excel, data=testdata, formats=formats, formatdata=formatdata)
# Test loading
if check('loadspreadsheet'):
data = sc.loadspreadsheet(files.excel)
print(data)
if check('readcells'):
wb = sc.Spreadsheet(filename=filedir+'exampledata.xlsx') # Load a sample databook to try pulling cells from
celltest = wb.readcells(method='xlrd', sheetname='Baseline year population inputs', cells=[[46, 2], [47, 2]]) # Grab cells using xlrd
celltest2 = wb.readcells(method='openpyexcel', wbargs={'data_only': True}, sheetname='Baseline year population inputs', cells=[[46, 2], [47, 2]]) # Grab cells using openpyexcel. You have to set wbargs={'data_only': True} to pull out cached values instead of formula strings
print('xlrd output: %s' % celltest)
print('openpyxl output: %s' % celltest2)
if check('Blobject'):
blob = sc.Blobject(files.excel)
f = blob.tofile()
wb = openpyxl.load_workbook(f)
ws = wb.active
ws['B7'] = 'Hi! '
wb.save(f)
blob.load(f)
blob.tofile(output=False)
def test_legacy():
'''
Preserved for completeness, but too fragile to be used in automated unit testing
due to reliance on openpyxl (which is not a required Sciris dependency).
'''
# Define filenames
filedir = 'files' + os.sep
files = sc.prettyobj()
files.excel = filedir + 'test.xlsx'
files.binary = filedir + 'test.obj'
files.text = filedir + 'text.txt'
files.zip = filedir + 'test.zip'
tidyup = True
# Define the test data
nrows = 15
ncols = 3
testdata = pl.zeros((nrows + 1, ncols),
dtype=object) # Includes header row
testdata[0, :] = ['A', 'B', 'C'] # Create header
testdata[1:, :] = pl.rand(nrows, ncols) # Create data
# Test spreadsheet writing, and create the file for later
formats = {
'header': {
'bold': True,
'bg_color': '#3c7d3e',
'color': '#ffffff'
},
'plain': {},
'big': {
'bg_color': '#ffcccc'
}
}
formatdata = pl.zeros(
(nrows + 1, ncols),
dtype=object) # Format data needs to be the same size
formatdata[1:, :] = 'plain' # Format data
formatdata[1:, :][testdata[
1:, :] > 0.7] = 'big' # Find "big" numbers and format them differently
formatdata[0, :] = 'header' # Format header
sc.savespreadsheet(filename=files.excel,
data=testdata,
formats=formats,
formatdata=formatdata)
# Test loading
sc.heading('Loading spreadsheet')
data = sc.loadspreadsheet(files.excel)
print(data)
excel_path = filedir + 'exampledata.xlsx'
if os.path.exists(excel_path):
sc.heading('Reading cells')
wb = sc.Spreadsheet(
filename=excel_path
) # Load a sample databook to try pulling cells from
celltest = wb.readcells(method='xlrd',
sheetname='Baseline year population inputs',
cells=[[46, 2], [47,
2]]) # Grab cells using xlrd
celltest2 = wb.readcells(
method='openpyexcel',
wbargs={'data_only': True},
sheetname='Baseline year population inputs',
cells=[[46, 2], [47, 2]]
) # Grab cells using openpyexcel. You have to set wbargs={'data_only': True} to pull out cached values instead of formula strings
print('xlrd output: %s' % celltest)
print('openpyxl output: %s' % celltest2)
else:
print(f'{excel_path} not found, skipping...')
sc.heading('Loading a blobject')
blob = sc.Blobject(files.excel)
f = blob.tofile()
wb = openpyexcel.load_workbook(f)
ws = wb.active
ws['B7'] = 'Hi! '
wb.save(f)
blob.load(f)
blob.tofile(output=False)
data = sc.loadspreadsheet(fileobj=blob.bytes)
print(blob)
sc.pp(data)
# Test spreadsheet saving
sc.heading('Using a Spreadsheet')
S = sc.Spreadsheet(files.excel)
S.writecells(cells=['A6', 'B7', 'C8', 'D9'],
vals=['This', 'is', 'a', 'test']) # Method 1
S.writecells(cells=[pl.array([7, 1]) + i for i in range(4)],
vals=['And', 'so', 'is', 'this']) # Method 2
newdata = (pl.rand(3, 3) * 100).round()
S.writecells(startrow=14, startcol=1, vals=newdata,
verbose=True) # Method 3
S.save()
data = S.readcells(header=False)
print(S)
sc.pp(data)
sc.heading('Saveobj/loadobj')
sc.saveobj(files.binary, testdata)
obj = sc.loadobj(files.binary)
print(obj)
sc.heading('Savetext/loadtext')
sc.savetext(files.text, testdata)
obj = sc.loadtext(files.text)
print(obj)
sc.heading('Get files')
print('Files in current folder:')
sc.pp(sc.getfilelist())
sc.heading('Save zip')
sc.savezip(files.zip, [files.text, files.excel])
'''
Check that loading an object with a non-existent class works. The file
deadclass.obj was created with:
deadclass.py:
-------------------------------------------------
class DeadClass():
def __init__(self, x):
self.x = x
-------------------------------------------------
then:
-------------------------------------------------
import deadclass as dc
import sciris as sc
deadclass = dc.DeadClass(238473)
sc.saveobj('deadclass.obj', deadclass)
-------------------------------------------------
'''
dead_path = filedir + 'deadclass.obj'
if os.path.exists(dead_path):
sc.heading('Intentionally loading corrupted file')
obj = sc.loadobj(dead_path)
print('Loading corrupted object succeeded, x=%s' % obj.x)
else:
print(f'{dead_path} not found, skipping...')
# Tidy up
if tidyup:
sc.blank()
sc.heading('Tidying up')
for fn in [files.excel, files.binary, files.text, files.zip]:
try:
os.remove(fn)
print('Removed %s' % fn)
except:
pass
print('Done, all fileio tests succeeded')
return S
