python字典的键设置条件格式_openpyxl条件格式设置数字格式

我试图以条件格式设置数字格式。我已经找到了如何设置字体，边框和填充颜色，但我无法设置数字格式。我公司目前拥有的代码是：openpyxl条件格式设置数字格式

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))

我想设置的格式与前4种格式没有小数点的百分比，我想将它设置为“+” 0.0的最后两个“X” 。我查看了源代码，找不到一种方法来做到这一点。任何帮助，将不胜感激。我使用Python 3，和Openpyxl 2.3.0与Excel 2010中

更新1：

我用查理的建议更新了我的代码。我补充说：

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))

它给了我下面的错误信息。

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

更新2：

事实证明，在差分类型的numFmt选项需要NumFmt，其在openpyxl.style.differential定义的一个类的实例。我改变了我的更新1的代码如下：

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)

现在，这些工作就好了，但是，当我打电话

wb.save("test.xlsx")

我收到以下错误消息

File "C:/TestProj/openpyxl_test.py", line 90, in

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'

2015-11-12

tornesi