How to put two decimals in cell with type of percent

Question

Recently I spent some time solving this issue. If you face same problem this might be helpful and you will get a clue how to solve the problem.

I have openpyxl version 2.2.2 I need to write percent value with 2 digits after dot into a cell. Value should be seen in Excel as '3.14%' - type of percentage, two decimals digits after dot Here is my first attempt:

from openpyxl import Workbook

wb = Workbook(guess_types=True)
ws = wb.create_sheet(index = 0, title = "MyTable")
ws['A1'].value = '3.14%'
ws['A1'].number_format

>> '0%'

Seems fine unless you do not know what does '0%' mean Here how it looks in excel:

Answer 1

All you need are:

• adding number formating
• not to use guess_types parameter

Here is the final state:

wb = Workbook()
ws = wb.create_sheet(index = 0, title = "MyTable")
ws['A1'].value = '3.14%'
ws['A1'].number_format = '0.00%'

Here is documentation for other used formats: http://openpyxl.readthedocs.org/en/latest/styles.html

Answer 2

You can also use built-in style numbers.

wb = Workbook()
ws = wb.create_sheet(index = 0, title = "MyTable")
ws['A1'].value = '0.0314'

from openpyxl.styles.numbers import FORMAT_PERCENTAGE_00
# with two decimal places. Will output 3.14%
ws['A1'].number_format = FORMAT_PERCENTAGE_00

from openpyxl.styles.numbers import FORMAT_PERCENTAGE
# with no decimal places. Will output 3%
ws['A1'].number_format = FORMAT_PERCENTAGE

Or use build it styles. it will output 3%

ws['A1'].style = "Percent"