0

I am trying to complete a script to store all the trail reports my company gets from various clearing houses. As part of this script I rip the data from multiple excel sheets (over 20 a month) and an amalgamate it in a series of pandas dataframes(organized in a timeline). Unfortunately when I try to output a new spreadsheet with the amalgamated summaries, I get a 'number stored as text' error from excel.

    FinalFile = Workbook()
    FinalFile.create_sheet(title='Summary')   ### This will hold a summary table eventually
    for i in Timeline:
        index = Timeline.index(i)
        sheet = FinalFile.create_sheet(title=i)
        sheet[i].number_format = 'Currency'
        df = pd.DataFrame(Output[index])
        df.columns = df.iloc[0]
        df = df.iloc[1:].reset_index(drop=True)
        df.head()
        df = df.set_index('Payment Type')
        for r in dataframe_to_rows(df, index=True,header=True):
            sheet.append(r)
        for cell in sheet['A'] + sheet[1]:
            cell.style='Pandas'
    SavePath = SaveFolder+'/'+CurrentDate+'.xlsx'
    FinalFile.save(SavePath)

using number_format = 'Currency' to format as currency did not resolve this, nor did my attempt to use the write only methond on the openpyxl documentation page https://openpyxl.readthedocs.io/en/stable/pandas.html

Fundamentally this code is outputting the right index, headers, sheetname and formatting the only issue issue is the numbers stored as text from B3:D7.

Attached is an example month Output

example dataframe of the same month

    0                      Total Paid    Net   GST
    Payment Type                                  
    Adjustments                 -2800  -2546  -254
    Agency Upfront              23500  21363  2135
    Agency Trail                46980  42708  4270
    Referring Office Trail      16003  14548  1454
    NilTrailPayment                 0      0     0
bokka501
  • 1
  • 1
  • 2
    Please check this once - [Python df.to_excel() storing numbers as text in excel. How to store as Value?](https://stackoverflow.com/questions/41080999/python-df-to-excel-storing-numbers-as-text-in-excel-how-to-store-as-value) – Subhajit May 27 '22 at 04:26
  • Hi thanks, that provided the information i needed to solve the problem. Wish it had shown up on the similar questions list. I inserted for col in df.columns[0:]: df[col] = df[col].astype(float) – bokka501 May 27 '22 at 05:03

0 Answers0