save_spreadsheets
- pyhelpers.store.save_spreadsheets(data, path_to_file, sheet_names, mode='w', if_sheet_exists=None, verbose=False, **kwargs)[source]
Save data to a multi-sheet Microsoft Excel or OpenDocument format file.
The file extension can be “.xlsx” (or “.xls”) or “.ods”.
- Parameters:
data (list | tuple | iterable) – a sequence of
pandas.DataFrame
path_to_file (str | os.PathLike) – path where a spreadsheet is saved
sheet_names (list | tuple | 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’; note that the ‘append’ mode is not supported with OpenDocumentif_sheet_exists (None | str) – indicate the behaviour when trying to write to an existing sheet; see also the parameter
if_sheet_exists
of pandas.ExcelWriterverbose (bool | 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.ods") >>> save_spreadsheets(dat, pathname, sheets, verbose=True) Saving "dat.ods" to "tests\data\" ... 'TestSheet1' ... Done. 'TestSheet2' ... Done. >>> 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.