CODEWITHSUNDEEP
excelpython-3.xpandasformatxlsxwriter
user5525061
user5525061

Reputation:

how to set numeric format of a cell in XLSX file using python

I want to set numeric format for a column or cell in XLSX file using python script.

The conversion script takes CSV file and converts it to XLSX. I deliberately treat header as a regular line, because final script does in the end of the conversion, in various ways according to specified command line parameters.

The example below shows only my attempt to set numeric format to a column or cell.

What do I do wrong?

With this code I manage to set alignment to the right. But any of the ways to set up numeric format fail. The XLSX file still keep that green triangle in the left upper corner of the cell and refuse to see it as a numeric cell. Attached screenshot shows "wrong" result.

enter image description here

---- data file ----

a,b,c,d,e
q,1,123,0.4,1
w,2,897346,.786876,-1.1
e,3,9872346,7896876.098098,2.098
r,4,65,.3,1322
t,5,1,0.897897978,-786

---- python script ----

#!/usr/bin/env python3
# -*- coding: UTF-8 -*-

import os
import pandas
import xlsxwriter

def is_type( value ):
    '''Function to identify true type of the value passed
        Input parameters:   value - some value which type need to be identified
        Returned values:    Type of the value
    '''
    try:
        int(value)
        return "int"
    except:
        try:
            float(value)
            return "float"
        except:
            return "str"

csv_file_name = "test37.csv"
xls_file_name = "test37.xlsx"

# Read CSV file to DataFrame
df = pandas.read_csv(csv_file_name, header=None, low_memory=False, quotechar='"', encoding="ISO-8859-1")
# Output DataFrame to Excel file
df.to_excel(xls_file_name, header=None, index=False, encoding="utf-8")
# Create writer object for output of XLSX file
writer = pandas.ExcelWriter(xls_file_name, engine="xlsxwriter")
# Write our Data Frame object to newly created file
xls_sheet_name = os.path.basename(xls_file_name).split(".")[0]
df.to_excel(writer, header=None, index=False, sheet_name=xls_sheet_name, float_format="%0.2f")
# get objects for workbook and worksheet
wb = writer.book
ws = writer.sheets[xls_sheet_name]
ws.set_zoom(120)

num_format1 = wb.add_format({
    'align': 'right'
})
num_format2 = wb.add_format({
    'align': 'right',
    'num_format': '0.00'
})
num_format3 = wb.add_format()
num_format3.set_num_format('0.00')

ws.set_column('D:D', None, num_format1)
ws.set_column('D:D', None, num_format2)

for column in df.columns:
    for row in range(1,len(df[column])):
        if is_type(df[column][row]) == "int":
            #print("int "+str(df.iloc[row][column]))
            ws.write( row, column, df.iloc[row][column], num_format2 )
        elif is_type(df[column][row]) == "float":
            #print("float "+str(df.iloc[row][column]))
            ws.write( row, column, df.iloc[row][column], num_format2 )
        else:
            pass


wb.close()
writer.save()

exit(0)

Upvotes: 1

Views: 2704

Answers (1)

Niels Henkens
Niels Henkens

Reputation: 2696

The problem has nothing to do with your xlsxwriter script, but lies in the way you import the csv in Pandas. Your csv-file has a header, but you specify in pd.read_csv() that there isn't a header. Therefore, Pandas also parses the header row as data. Because the header is a string, the entire column gets imported as a string (instead of integer or float).

Just remove the 'header=None' in pd.read_csv and df.to_excel() and it should work fine.

so:

...<first part of your code>

# Read CSV file to DataFrame
df = pandas.read_csv(csv_file_name, low_memory=False, quotechar='"', encoding="ISO-8859-1")
# Output DataFrame to Excel file
df.to_excel(xls_file_name, index=False, encoding="utf-8")

<rest of your code>...

Upvotes: 1

Related Questions