How can I convert Cell of Openpyxl from Text to Number format?

Question

I wrote a code to convert a text file into excel file using Openpyxl extension of Python.

Although the value are setting properly into the column but they are showing as a text instead of number. Although I tried to convert, seems like it is not working.

Can anyone please correct the code?

import csv
import openpyxl
import openpyxl as oxl

input_file = r'C:\Python\Test.txt'
output_file = r'C:\Python\Test.xlsx'

wb = oxl.Workbook()
ws = wb.active
ws.number_format = 'General'
ws.title = "Waveform"
#ws = wb.create_sheet(title='Waveform')


with open(input_file, 'r') as data:
    reader = csv.reader(data, delimiter='\t')
    for row in reader:
        ws.append(row)


for row in range(2, ws.max_row+1):
    ws["{}{}".format("A", row)].number_format = 'General'
    ws["{}{}".format("B", row)].number_format = 'General'

wb.save(output_file)

Here is the output excel file

You may want to try with a more specific number format? https://stackoverflow.com/questions/12387212/openpyxl-setting-number-format#26334956 — Anonymous, Jun 14 '22 at 22:53
General is the wrong format, because the numbers are read as strings from the csv. You could also convert them to floats, using `ws.append(list(map(float, row)))` — jezza_99, Jun 15 '22 at 02:05
Any data in a CSV is text. You need to convert it to the relevant Python types before you add it to a worksheet. — Charlie Clark, Jun 15 '22 at 07:41

score 0 · Accepted Answer · answered Jun 15 '22 at 14:04

the read data from txt file will be in string. So, as suggested by jezza, you need to convert list to float. You don't need the 'number_format` lines you have. Updated code is here. Note that the conversion map assumes all data can be converted to float (no text). The try/catch will basically skip the row if there is text on any row

import csv
#import openpyxl
import openpyxl as oxl

input_file = r'C:\Python\Test.txt'
output_file = r'C:\Python\Test.xlsx'

wb = oxl.Workbook()
ws = wb.active
#ws.number_format = 'General'
ws.title = "Waveform"
#ws = wb.create_sheet(title='Waveform')

with open(input_file, 'r') as data:
    reader = csv.reader(data, delimiter='\t')
    for row in reader:
        try:
            row = list(map(float, row))
            ws.append(row)
        except:
            print("Skipping row ", row)
            pass

#for row in range(2, ws.max_row+1):
#    ws["{}{}".format("A", row)].number_format = 'General'
#    ws["{}{}".format("B", row)].number_format = 'General'

wb.save(output_file)

Output

How can I convert Cell of Openpyxl from Text to Number format?

1 Answers1