openpyxl set nunmber format to cell but doesn't work

Question

I want to create an excel by reading data from the csv files. So, I use Openpyxl and CSV in python. However, no matter number_format I put in the code the cell value from the csv remain in 'text' format

from openpyxl import load_workbook
import csv
from openpyxl.styles import numbers

ws= load_workbook('myworkbook.xlsx')    
with open("myfile.csv", "r", newline="", encoding="utf16") as f:
    mydata=csv.reader(f,delimiter='|')
    for row in mydata:
        for col_index,col_data in enumerate(row,3):
            ws.cell(row=new800row_count, column=col_index, value=col_data).number_format = numbers.FORMAT_NUMBER

let's say my csv file contains 800, 200, 100 data value. After using my code above. Instead of showing 800, 200, 100 in number format (excel) it's told me that this cell contain number stored as text even the actual excel format told me that it's a number

Thank you for any help in advance.

Answer 1

Loop through your data set, convert the data if its a string to float:

str = '1234.0'
data = float(str)
row[col] = data
row[col].number_format = '#,##0.00'

The last line of code ensures its formatted as a number. don't forget to save the file after making the above changes.

Answer 2

CSV reader is intended to be simple and will always give you text. Openpyxl also will not do the conversion implicitly. You need to convert the text into number first. For example, under the loop of each row:

row = [float(cell) if cell.isdigit() else cell for cell in row]

and then use the row as you wish.

Answer 3

This is what worked for me, first, convert the value itself into the datatype that you need; i.e int/float. Then apply builtin format from the openpyxl.styles.numbers.

In case of int:

cell=ws.cell(row=new800row_count, column=col_index, value=int(col_data))
cell.number_format = '0'

In case of float:

cell=ws.cell(row=new800row_count, column=col_index, value=float(col_data))
cell.number_format = '0.00'

You can find more details on the builtin format for openpyxl.styles.numbers here!.