savespreadsheet#

savespreadsheet(filename=None, data=None, folder=None, sheetnames=None, close=True, workbook_args=None, formats=None, formatdata=None, verbose=False)[source]#

Semi-simple function to save data nicely to Excel.

Parameters:

  • filename (str) – Excel file to save to

  • data (list/array) – data to write to the spreadsheet

  • folder (str) – if supplied, merge with the filename to make a path

  • sheetnames (list) – if data is supplied as a list of arrays, save each entry to a different sheet

  • close (bool) – whether to close the workbook after saving

  • workbook_args (dict) – arguments passed to xlxwriter.Workbook()

  • formats (dict) – a definition of different types of formatting (see examples below)

  • formatdata (array) – an array of which formats go where

  • verbose (bool) – whether to print progress

Examples:

import numpy as np
import sciris as sc
import matplotlib.pyplot as plt

# Simple example
testdata1 = np.random.rand(8,3)
sc.savespreadsheet(filename='test1.xlsx', data=testdata1)

# Include column headers
test2headers = [['A','B','C']] # Need double brackets to get right shape
test2values = np.random.rand(8,3).tolist()
testdata2 = test2headers + test2values
sc.savespreadsheet(filename='test2.xlsx', data=testdata2)

# Multiple sheets
testdata3 = [np.random.rand(10,10), np.random.rand(20,5)]
sheetnames = ['Ten by ten', 'Twenty by five']
sc.savespreadsheet(filename='test3.xlsx', data=testdata3, sheetnames=sheetnames)

# Supply data as an odict
testdata4 = sc.odict([('First sheet', np.random.rand(6,2)), ('Second sheet', np.random.rand(3,3))])
sc.savespreadsheet(filename='test4.xlsx', data=testdata4)

# Include formatting
nrows = 15
ncols = 3
formats = {
    'header':{'bold':True, 'bg_color':'#3c7d3e', 'color':'#ffffff'},
    'plain': {},
    'big':   {'bg_color':'#ffcccc'}
}
testdata5  = np.zeros((nrows+1, ncols), dtype=object) # Includes header row
formatdata = np.zeros((nrows+1, ncols), dtype=object) # Format data needs to be the same size
testdata5[0,:] = ['A', 'B', 'C'] # Create header
testdata5[1:,:] = np.random.rand(nrows,ncols) # Create data
formatdata[1:,:] = 'plain' # Format data
formatdata[testdata5>0.7] = 'big' # Find "big" numbers and format them differently
formatdata[0,:] = 'header' # Format header
sc.savespreadsheet(filename='test5.xlsx', data=testdata5, formats=formats, formatdata=formatdata)

New version 2.0.0: allow arguments to be passed to the Workbook.

On this page