functions.py 文件源码

def update_excel(case,
                 file,
                 ws_dict={},
                 sheets_to_remove=None):
    '''Read an excel file, optionally remove worksheet(s), add worksheets
    or overwrite worksheets with a dictionary of ws_name, dataframe key, value
    pairs, and write the excel file back to disk
    inputs
        case (string)
            the data model case name
        file (string)
            the excel file name without the .xlsx extension
        ws_dict (dictionary)
            dictionary of worksheet names as keys and pandas dataframes as
            values.  The items in this dictionary will be passed into the
            excel file as worksheets. The worksheet name keys may be the
            same as some or all of the worksheet names in the excel file.
            In the case of matching names, the data from the input dict will
            overwrite the existing data (worksheet) in the excel file.
            Non-overlapping worksheet names/dataframe values will be added
            as new worksheets.
        sheets_to_remove (list)
            a list of worksheet names (strings) representing worksheets to
            remove from the excel workbook.  It is not necessary to remove
            sheets which are being replaced by worksheet with the same name.
    '''
    # read a single or multi-sheet excel file
    # (returns dict of sheetname(s), dataframe(s))
    path = 'excel/' + case + '/' + file + '.xlsx'

    # make a copy of file before modifying
    copy_excel_file(case, file, verbose=False)

    # get a dictionary from the excel file consisting of worksheet name keys
    # and worksheet contents as values (as dataframes)
    try:
        dict0 = pd.read_excel(path, sheetname=None)
    except OSError:
        print('Error: Unable to find "' + path + '"')
        return
    # all worksheets are now accessible as dataframes.
    # drop worksheets which match an element in the sheets_to_remove:
    if sheets_to_remove is not None:
        for ws_name in sheets_to_remove:
            dict0.pop(ws_name, None)

    # update worksheet dictionary with ws_dict (ws_dict values will override
    # existing values in the case of matching worksheet name keys):
    dict0.update(ws_dict)

    # write the updated dictionary back to excel...
    with pd.ExcelWriter(path,
                        engine='xlsxwriter',
                        datetime_format='yyyy-mm-dd',
                        date_format='yyyy-mm-dd') as writer:

        for sheet_name, df_sheet in dict0.items():
            df_sheet.to_excel(writer, sheet_name=sheet_name)