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

Issue

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

Solution

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

Answered By - Redox

Answer Checked By - Senaida (PHPFixing Volunteer)