[FIXED] Force pandas.to_excel to write data as 'Text' instead 'General' format

Issue

I've already set cell format as 'Text' in the target Excel column. However pandas.to_excel changes the format to 'General' when writing strings to this column, eventually the column ends up with blank cells are formatted as 'Text' and non-blank ones as 'General'. Is there a way to write data as 'Text' instead of 'General'?

def exportData(df, dstfile, sheet):
    book = load_workbook(dstfile)
    writer = pd.ExcelWriter(dstfile, engine='openpyxl', date_format='dd/mm/yyyy', datetime_format='mm/dd/yyyy hh:mm')
    writer.book = book
    writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
    df.to_excel(writer, header=False, startrow=2, index=False, sheet_name=sheet)
    writer.save()

Solution

You can iterate over the cells using the following method.

The cells you want to format as TEXT just use:

cell.number_format = '@'

This will set the cell formatting as TEXT in Excel.

There might be a way to do it straight from the ExcelWriter within Pandas but I'm unfarmiliar with it, maybe someone who knows better will edit the answer for that option as well.

Answered By - Dror Av.