CODEWITHSUNDEEP
pythonexcelformattingdecimal
girl7
girl7

Reputation: 71

Format excel numbers at two decimals with python

I would like to format numbers in excel export at 2 decimal places but only for view, and keep original values in behind as in the picture, number to be shown with two decimal places but when you click on the cell to be shown whole value. I have used XlsxWriter and add_format,and it is formating at 2 decimals but do not keep original values. Can someone suggest how it can be done and if it is posssible?

Picture example in excel:

picture example in excel

Upvotes: 2

Views: 6015

Answers (2)

Shahidul Islam Molla
Shahidul Islam Molla

Reputation: 640

Below code is working for me. if you are getting error of "ModuleNotFoundError: No module named 'xlsxwriter'"

pip install xlsxwriter


with pd.ExcelWriter(r'D:\Necessary_file\Split.xlsx') as writer:  #change path
df.to_excel(writer, sheet_name='Foglio3',index=False)

#below code use for cloumn formating
wb = writer.book  # get workbook
ws = writer.sheets['Foglio3']  # worksheet
# below code used for two decimal   
two_decimal = wb.add_format({'num_format': '0.00'})
ws.set_column('F:F', None, two_decimal)
# below code used for back ground color 
bg_color = wb.add_format({'bg_color':   '#FFEB9C','font_color': '#9C6500'})
ws.conditional_format('F2:F6000', {'type':     'cell',
                                    'criteria': '<=',
                                    'value':    10,
                                    'format':   bg_color})

Output: enter image description here

Upvotes: 0

jmcnamara
jmcnamara

Reputation: 41644

I have used XlsxWriter and add_format,and it is formating at 2 decimals but do not keep original values.

That can't be the case. XlsxWriter doesn't truncate the numbers that it handles in any way, apart from formatting them with %.16G in the same way that Excel does.

For example:

import xlsxwriter

workbook = xlsxwriter.Workbook('test.xlsx')
worksheet = workbook.add_worksheet()

my_format = workbook.add_format({'num_format': '0.00'})

worksheet.write(0, 0, 29.159573953, my_format)

workbook.close()

Output:

enter image description here

As you can see the full number is displayed in the formula bar.

Upvotes: 3

Related Questions