save_spreadsheets
- pyhelpers.store.save_spreadsheets(spreadsheets_data, path_to_spreadsheet, sheet_names, mode='w', if_sheet_exists=None, verbose=False, **kwargs)
Save data to a multi-sheet Microsoft Excel file.
The file extension can be “.xlsx” or “.xls”.
- Parameters
spreadsheets_data (list or tuple or iterable) – a sequence of pandas.DataFrame
path_to_spreadsheet (str or os.PathLike[str]) – path where a spreadsheet is saved
sheet_names (list or tuple or iterable) – all sheet names of an Excel workbook
mode (str) – mode to write to an Excel file;
'w'
(default) for ‘write’ and'a'
for ‘append’if_sheet_exists (None or str) – indicate the behaviour when trying to write to an existing sheet; see also the parameter
if_sheet_exists
of pandas.ExcelWriterverbose (bool or int) – whether to print relevant information in console, defaults to
False
kwargs – [optional] parameters of pandas.DataFrame.to_excel
Examples:
>>> from pyhelpers.store import save_spreadsheets >>> from pyhelpers.dirs import cd >>> from pyhelpers._cache import example_dataframe >>> dat1 = example_dataframe() # Get an example dataframe >>> dat1 Longitude Latitude City London -0.127647 51.507322 Birmingham -1.902691 52.479699 Manchester -2.245115 53.479489 Leeds -1.543794 53.797418 >>> dat2 = dat1.T >>> dat2 City London Birmingham Manchester Leeds Longitude -0.127647 -1.902691 -2.245115 -1.543794 Latitude 51.507322 52.479699 53.479489 53.797418 >>> dat = [dat1, dat2] >>> sheets = ['TestSheet1', 'TestSheet2'] >>> pathname = cd("tests\data", "dat.xlsx") >>> save_spreadsheets(dat, pathname, sheets, verbose=True) Saving "dat.xlsx" to "tests\data\" ... 'TestSheet1' ... Done. 'TestSheet2' ... Done. >>> save_spreadsheets(dat, pathname, sheets, mode='a', verbose=True) Updating "dat.xlsx" at "tests\data\" ... 'TestSheet1' ... This sheet already exists; [pass]|new|replace: new saved as 'TestSheet11' ... Done. 'TestSheet2' ... This sheet already exists; [pass]|new|replace: new saved as 'TestSheet21' ... Done. >>> save_spreadsheets(dat, pathname, sheets, 'a', if_sheet_exists='replace', verbose=True) Updating "dat.xlsx" at "tests\data\" ... 'TestSheet1' ... Done. 'TestSheet2' ... Done. >>> save_spreadsheets(dat, pathname, sheets, 'a', if_sheet_exists='new', verbose=True) Updating "dat.xlsx" at "tests\data\" ... 'TestSheet1' ... saved as 'TestSheet12' ... Done. 'TestSheet2' ... saved as 'TestSheet22' ... Done.