CODEWITHSUNDEEP
python-3.xconditional-formattingopenpyxl
tornesi
tornesi

Reputation: 269

openpyxl conditional formatting set number format

I'm trying to set the number format in a conditional format. I have found how to set the font, border and fill colors, but I can't set the number format. The code I currently have is:

from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Font, Border, Side
from openpyxl.formatting.rule import FormulaRule, DifferentialStyle, Rule

wb = load_workbook(filename='Excel_Templates\MyTemplate.xlsx')
ws = wb.get_sheet_by_name('Sheet1')
red_fill = PatternFill(start_color='FFFF0000', end_color='FFFF0000', fill_type='solid')
pink_fill = PatternFill(start_color='FFFF5050', end_color='FFFF5050', fill_type='solid')
light_pink_fill = PatternFill(start_color='FFFF9B99', end_color='FFFF9B99', fill_type='solid')
blue_fill = PatternFill(start_color='FF0000FF', end_color='FF0000FF', fill_type='solid')
light_blue_fill = PatternFill(start_color='FF0066FF', end_color='FF0066FF', fill_type='solid')
v_light_blue_fill = PatternFill(start_color='FF9BC2E6', end_color='FF9BC2E6', fill_type='solid')

side = Side(style='thin', color='FF000000')
myBorder = Border(left=side, right=side, bottom=side, top=side)
my_white_font = Font(color='FFFFFFFF', size=12)
my_black_font = Font(size=12)
p='E3'
num = 'E8'
denom = 'E9'
ws['{0}'.format(p)] = '=IF({0}/{1} >= 2, {0}/{1}, IF(AND({0}/{1}>=0, {0}/{1} <2),{0}/{1}-1, 1-{0}/{1}))'.format(
    num, denom)

    ws.conditional_formatting.add('{0}'.format(p),
                              FormulaRule(formula=['{0} < -0.5'.format(p)], fill=red_fill, font=my_black_font))

ws.conditional_formatting.add('{0}'.format(p),
                              FormulaRule(formula=['AND({0} < -0.25, {0} >=-0.5)'.format(p)], fill=pink_fill,
                                          font=my_black_font))

ws.conditional_formatting.add('{0}'.format(p),
                              FormulaRule(formula=['AND({0} < 0, {0} >=-0.25)'.format(p)], fill=light_pink_fill,
                                          font=my_black_font))

ws.conditional_formatting.add('{0}'.format(p),
                              FormulaRule(formula=['AND({0} >=0, {0} < 1)'.format(p)], fill=v_light_blue_fill,
                                          font=my_black_font))

ws.conditional_formatting.add('{0}'.format(p),
                              FormulaRule(formula=['AND({0} >=1, {0} <= 3)'.format(p)], fill=light_blue_fill,
                                          font=my_white_font))

ws.conditional_formatting.add('{0}'.format(p),
                              FormulaRule(formula=['{0} > 3'.format(p)], fill=blue_fill,
                                          font=my_white_font))

I want to set the format to be percentage with no decimal points for the first 4 formats and I want to set it to "+"0.0"X" for the last two. I've looked through the source code and can't find a way to do this. Any help would be appreciated. I'm using Python 3, and Openpyxl 2.3.0 with Excel 2010.

Update 1:

I updated my code using Charlie's suggestions. I added:

dxf = DifferentialStyle(fill=blue_fill, font=my_white_font, numFmt='0.00%')
ws.conditional_formatting.add('{0}'.format(p), Rule(formula=['{0} > 3'.format(p)], dxf=dxf))

which gives me the following error message.

File "C:\WinPython-32bit-3.4.3.3\python-3.4.3\lib\site-packages\openpyxl\styles\differential.py", line 57, in __init__
self.numFmt = numFmt
File "C:\WinPython-32bit-3.4.3.3\python-3.4.3\lib\site-packages\openpyxl\descriptors\base.py", line 42, in __set__
raise TypeError('expected ' + str(self.expected_type))
TypeError: expected <class 'openpyxl.styles.differential.NumFmt'>

Update 2:

It turns out that the numFmt option in Differential Style requires a class instance of NumFmt, which is defined in openpyxl.style.differential. I changed my Update 1 code to the following:

from openpyxl.styles.differential import NumFmt
dxf = DifferentialStyle(fill=blue_fill, font=my_white_font, numFmt=NumFmt(10, '0.00%'))
r = Rule('expression', formula=['{0} > 3'.format(p)], dxf=dxf)
ws.conditional_formatting.add('{0}'.format(p), r)

Now those work just fine, however, when I call

wb.save("test.xlsx")

I get the following error message

    File "C:/TestProj/openpyxl_test.py", line 90, in <module>
    wb.save("test.xlsx")
  File "C:\WinPython-32bit-3.4.3.3\python-3.4.3\lib\site-packages\openpyxl\workbook\workbook.py", line 263, in save
    save_workbook(self, filename)
  File "C:\WinPython-32bit-3.4.3.3\python-3.4.3\lib\site-packages\openpyxl\writer\excel.py", line 230, in save_workbook
    writer.save(filename, as_template=as_template)
  File "C:\WinPython-32bit-3.4.3.3\python-3.4.3\lib\site-packages\openpyxl\writer\excel.py", line 213, in save
    self.write_data(archive, as_template=as_template)
  File "C:\WinPython-32bit-3.4.3.3\python-3.4.3\lib\site-packages\openpyxl\writer\excel.py", line 87, in write_data
    self._write_worksheets(archive)
  File "C:\WinPython-32bit-3.4.3.3\python-3.4.3\lib\site-packages\openpyxl\writer\excel.py", line 158, in _write_worksheets
    xml = sheet._write(self.workbook.shared_strings)
  File "C:\WinPython-32bit-3.4.3.3\python-3.4.3\lib\site-packages\openpyxl\worksheet\worksheet.py", line 772, in _write
    return write_worksheet(self, shared_strings)
  File "C:\WinPython-32bit-3.4.3.3\python-3.4.3\lib\site-packages\openpyxl\writer\worksheet.py", line 223, in write_worksheet
    for cf in cfs:
  File "C:\WinPython-32bit-3.4.3.3\python-3.4.3\lib\site-packages\openpyxl\writer\worksheet.py", line 119, in write_conditional_formatting
    if rule.dxf != DifferentialStyle():
  File "C:\WinPython-32bit-3.4.3.3\python-3.4.3\lib\site-packages\openpyxl\styles\hashable.py", line 77, in __ne__
    return not self == other
  File "C:\WinPython-32bit-3.4.3.3\python-3.4.3\lib\site-packages\openpyxl\styles\hashable.py", line 73, in __eq__
    return self.key == other.key
  File "C:\WinPython-32bit-3.4.3.3\python-3.4.3\lib\site-packages\openpyxl\styles\hashable.py", line 65, in key
    self._key = hash(tuple(fields))
TypeError: unhashable type: 'NumFmt'

Upvotes: 1

Views: 3499

Answers (2)

JCVanHamme
JCVanHamme

Reputation: 1050

I strongly suspect that the source of your issue is a bug in openpyxl. The problem seems to be that the NumFmt class is not hashable. I think this might stem from the fact that it is derived from Serialisable, rather than HashableObject. As such, it's essentially missing the __hash__ function. I think you can fix this by adding a __hash__ function to the NumFmt class like so:

class NumFmt(Serialisable):

    numFmtId = Integer()
    formatCode = String()

    def __init__(self,
                 numFmtId=None,
                 formatCode=None,
                ):
        self.numFmtId = numFmtId
        self.formatCode = formatCode

    def __hash__(self):
        return hash((self.numFmtId, self.formatCode))

With this, I was able to get the following example to work: from openpyxl import load_workbook from openpyxl.styles import PatternFill from openpyxl.formatting.rule import DifferentialStyle, Rule from openpyxl.styles.differential import NumFmt

wb = load_workbook(filename=r'C:\users\vanhamme\Desktop\foobar.xlsx')
ws = wb['Sheet1']

blue_fill = PatternFill(start_color='FF0000FF', end_color='FF0000FF', fill_type='solid')

dxf = DifferentialStyle(fill=blue_fill, numFmt=NumFmt(10, '0.00%'))
rule = Rule('expression', formula=['E3 > 3'], dxf=dxf)
ws.conditional_formatting.add('E3', rule)

wb.save(r'C:\users\vanhamme\Desktop\foobar2.xlsx')

This is maybe not the most elegant solution. Unfortunately, I don't quite understand the openpyxl class hierarchy well enough to know if there's a shorter, cleaner route.

Upvotes: 2

Charlie Clark
Charlie Clark

Reputation: 19537

Use openpyxl.formatting.rule.Rule directly for full control. FormulaRule was kept for backwards compatibility only and does not expose all the possibilities but if you look at its source you'll see that you can pass in any instance of a DifferentialStyle

Upvotes: 1

Related Questions