PHPFixing
  • Privacy Policy
  • TOS
  • Ask Question
  • Contact Us
  • Home
  • PHP
  • Programming
  • SQL Injection
  • Web3.0

Monday, August 29, 2022

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

 August 29, 2022     csv, excel, openpyxl, python     No comments   

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

enter image description here



Answered By - Redox
Answer Checked By - Senaida (PHPFixing Volunteer)
  • Share This:  
  •  Facebook
  •  Twitter
  •  Stumble
  •  Digg
Newer Post Older Post Home

0 Comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Total Pageviews

Featured Post

Why Learn PHP Programming

Why Learn PHP Programming A widely-used open source scripting language PHP is one of the most popular programming languages in the world. It...

Subscribe To

Posts
Atom
Posts
Comments
Atom
Comments

Copyright © PHPFixing