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:

defto_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 notin 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 = Noneexcept TypeError:
        pass# Write manually to avoid overwriting formats.# Column names.
    ws.cell(1, 1).value = df.columns.name
    for icol, col_name inzip(range(2, len(df.columns) + 2), df.columns):
        ws.cell(1, icol).value = col_name

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

    # Body cells.for row, irow inzip([x[1] for x in df.iloc[start_row - 2:].iterrows()], list(range(start_row, len(df.index) + 2))):
        for cell, icol inzip([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.')
    whileTrue:
        try:
            writer.save()
            breakexcept 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.