Export A Dataframe To Excel, Values Only Without The Formatting

I need to write a pandas DataFrame to an Excel worksheet. There are currencies, percentages and text. This script is expected to be run periodically, updating the data without chan

Solution 1:

Replacement for .to_excel(), writes values only:

def to_excel(filename, data_ws, df, start_row=2, start_col=2):
    """Replacement for pandas .to_excel(). 

    For .xlsx and .xls formats only.

    args:
        start_row: df row +2; does not include header and is 1 based indexed.
    """
    writer = pd.ExcelWriter(filename.lower(), engine='openpyxl')
    import openpyxl
    try:
        wb = openpyxl.load_workbook(filename)
    except FileNotFoundError:
        wb = openpyxl.Workbook()
    if data_ws not in wb.sheetnames:
        wb.create_sheet(data_ws)

    # Create the worksheet if it does not yet exist.
    writer.book = wb
    writer.sheets = {x.title: x for x in wb.worksheets}

    ws = writer.sheets[data_ws]
    # Fill with blanks.
    try:
        for row in ws:
            for cell in row:
                cell.value = None
    except TypeError:
        pass

    # Write manually to avoid overwriting formats.

    # Column names.
    ws.cell(1, 1).value = df.columns.name
    for icol, col_name in zip(range(2, len(df.columns) + 2), df.columns):
        ws.cell(1, icol).value = col_name

    # Row headers.
    for irow, row_name in zip(range(2, len(df.index) + 2), df.index):
        ws.cell(irow, 1).value = row_name

    # Body cells.
    for row, irow in zip([x[1] for x in df.iloc[start_row - 2:].iterrows()], list(range(start_row, len(df.index) + 2))):
        for cell, icol in zip([x[1] for x in row.iloc[start_col - 2:].items()], list(range(start_col, len(df.columns) + 2))):
            ws.cell(irow, icol).value = cell  # Skip the index.

    for row in ws.values:
        print('\t'.join(str(x or '') for x in row))
    print('Saving.')
    while True:
        try:
            writer.save()
            break
        except PermissionError:
            print(f'Please close {filename} before we can write to it!')
            time.sleep(2)
    writer.close()
    print('Done saving df.')

If anyone comes to this question in the future, this code seems to work. It can be cleaned up a little though, possibly by converting the DataFrame to a list of lists, to avoid the DataFrame overhead and to avoid processing the column and index names separately.