Method
Use the `numbers` module and the `number_format` property of the cell object to set number formats.
>>> from openpyxl.styles import numbers
There are two ways to set the number format:
1. Using the built-in constants of OpenPyXl:
>> ws[“D2”].number_format=numbers.FORMAT_GENERAL
2. Using a string representation of the number format:
>> ws[“D6″].number_format=”yy-mm-dd”
>> ws[“D8″].number_format=”d-mmm-yy”
To display numbers in scientific notation:
>> ws[“D4”].number_format = ‘0.00E+00’
The available format constants and strings in OpenPyXl are as follows:
- FORMAT_GENERAL=”General”
- FORMAT_TEXT=”@”
- FORMAT_NUMBER=”0″
- FORMAT_NUMBER_00=”0.00″
- FORMAT_NUMBER_COMMA_SEPARATED1=”#,##0.00″
- FORMAT_NUMBER_COMMA_SEPARATED2=”#,##0.00_-“
- FORMAT_PERCENTAGE=”0%”
- FORMAT_PERCENTAGE_00=”0.00%”
- FORMAT_DATE_YYYYMMDD2=”yyyy-mm-dd”
- FORMAT_DATE_YYMMDD=”yy-mm-dd”
- FORMAT_DATE_DDMMYY=”dd/mm/yy”
- FORMAT_DATE_DMYSLASH=”d/m/y”
- ……
Sample Code
#Cell Style: Setting Number Formats
#Import load_workbook function
from openpyxl import Workbook
#Create a new workbook
wb=Workbook()
#Get the active worksheet
ws=wb.active
#Import numbers class
from openpyxl.styles import numbers
import datetime
#Set the number format
ws['D2']='Test123'
ws['D6']=datetime.datetime.today()
ws['D8']=datetime.datetime.today()
ws['D4']=123.456789
ws['D2'].number_format=numbers.FORMAT_GENERAL
ws['D6'].number_format='yy-mm-dd'
ws['D8'].number_format='d-mmm-yy'
ws['D4'].number_format='0.00E+00'
wb.save('test.xlsx')
wb.close()