def test_conditional_formatting_addCellIs_notEqual(self):
cf = ConditionalFormatting()
redFill = Fill()
redFill.start_color.index = 'FFEE1111'
redFill.end_color.index = 'FFEE1111'
redFill.fill_type = Fill.FILL_SOLID
cf.addCellIs('U10:U18', 'notEqual', ['U$7'], True, self.workbook,
None, None, redFill)
cf.addCellIs('V10:V18', '!=', ['V$7'], True, self.workbook, None,
None, redFill)
rules = cf.cf_rules
assert 'U10:U18' in rules
assert len(cf.cf_rules['U10:U18']) == 1
assert rules['U10:U18'][0]['priority'] == 1
assert rules['U10:U18'][0]['type'] == 'cellIs'
assert rules['U10:U18'][0]['dxfId'] == 0
assert rules['U10:U18'][0]['operator'] == 'notEqual'
assert rules['U10:U18'][0]['formula'][0] == 'U$7'
assert rules['U10:U18'][0]['stopIfTrue'] == '1'
assert 'V10:V18' in rules
assert len(cf.cf_rules['V10:V18']) == 1
assert rules['V10:V18'][0]['priority'] == 2
assert rules['V10:V18'][0]['type'] == 'cellIs'
assert rules['V10:V18'][0]['dxfId'] == 1
assert rules['V10:V18'][0]['operator'] == 'notEqual'
assert rules['V10:V18'][0]['formula'][0] == 'V$7'
assert rules['V10:V18'][0]['stopIfTrue'] == '1'
def test_parse_dxfs():
reference_file = os.path.join(DATADIR, 'reader', 'conditional-formatting.xlsx')
wb = load_workbook(reference_file)
assert isinstance(wb, Workbook)
archive = ZipFile(reference_file, 'r', ZIP_DEFLATED)
read_xml = archive.read(ARC_STYLE)
# Verify length
assert '<dxfs count="164">' in str(read_xml)
assert len(wb.style_properties['dxf_list']) == 164
# Verify first dxf style
reference_file = os.path.join(DATADIR, 'writer', 'expected', 'dxf_style.xml')
with open(reference_file) as expected:
diff = compare_xml(read_xml, expected.read())
assert diff is None, diff
cond_styles = wb.style_properties['dxf_list'][0]
assert cond_styles['font'].color == Color('FF9C0006')
assert not cond_styles['font'].bold
assert not cond_styles['font'].italic
f = Fill()
f.end_color = Color('FFFFC7CE')
assert cond_styles['fill'] == f
# Verify that the dxf styles stay the same when they're written and read back in.
w = StyleWriter(wb)
w._write_dxfs()
write_xml = get_xml(w._root)
read_style_prop = read_style_table(write_xml)
assert len(read_style_prop['dxf_list']) == len(wb.style_properties['dxf_list'])
for i, dxf in enumerate(read_style_prop['dxf_list']):
assert repr(wb.style_properties['dxf_list'][i] == dxf)
def test_notEqual(self):
cf = ConditionalFormatting()
redFill = Fill()
redFill.start_color.index = 'FFEE1111'
redFill.end_color.index = 'FFEE1111'
redFill.fill_type = Fill.FILL_SOLID
cf.add('U10:U18', CellIsRule(operator='notEqual', formula=['U$7'], stopIfTrue=True, fill=redFill))
cf.add('V10:V18', CellIsRule(operator='!=', formula=['V$7'], stopIfTrue=True, fill=redFill))
cf.setDxfStyles(self.workbook)
rules = cf.cf_rules
assert 'U10:U18' in rules
assert len(cf.cf_rules['U10:U18']) == 1
assert rules['U10:U18'][0]['priority'] == 1
assert rules['U10:U18'][0]['type'] == 'cellIs'
assert rules['U10:U18'][0]['dxfId'] == 0
assert rules['U10:U18'][0]['operator'] == 'notEqual'
assert rules['U10:U18'][0]['formula'][0] == 'U$7'
assert rules['U10:U18'][0]['stopIfTrue'] == '1'
assert 'V10:V18' in rules
assert len(cf.cf_rules['V10:V18']) == 1
assert rules['V10:V18'][0]['priority'] == 2
assert rules['V10:V18'][0]['type'] == 'cellIs'
assert rules['V10:V18'][0]['dxfId'] == 1
assert rules['V10:V18'][0]['operator'] == 'notEqual'
assert rules['V10:V18'][0]['formula'][0] == 'V$7'
assert rules['V10:V18'][0]['stopIfTrue'] == '1'
def test_conditional_formatting_setDxfStyle(self):
cf = ConditionalFormatting()
fill = Fill()
fill.start_color.index = 'FFEE1111'
fill.end_color.index = 'FFEE1111'
fill.fill_type = Fill.FILL_SOLID
font = Font()
font.name = 'Arial'
font.size = 12
font.bold = True
font.underline = Font.UNDERLINE_SINGLE
borders = Borders()
borders.top.border_style = Border.BORDER_THIN
borders.top.color.index = Color.DARKYELLOW
borders.bottom.border_style = Border.BORDER_THIN
borders.bottom.color.index = Color.BLACK
cf.add(
'C1:C10',
FormulaRule(formula=['ISBLANK(C1)'],
font=font,
border=borders,
fill=fill))
cf.add('D1:D10', FormulaRule(formula=['ISBLANK(D1)'], fill=fill))
cf.setDxfStyles(self.workbook)
assert len(self.workbook.style_properties['dxf_list']) == 2
assert self.workbook.style_properties['dxf_list'][0] == {
'font': font,
'border': borders,
'fill': fill
}
assert self.workbook.style_properties['dxf_list'][1] == {'fill': fill}
def test_conditional_font(self):
"""Test to verify font style written correctly."""
class WS():
conditional_formatting = ConditionalFormatting()
worksheet = WS()
# Create cf rule
redFill = Fill()
redFill.start_color.index = 'FFEE1111'
redFill.end_color.index = 'FFEE1111'
redFill.fill_type = Fill.FILL_SOLID
whiteFont = Font()
whiteFont.color.index = "FFFFFFFF"
worksheet.conditional_formatting.add('A1:A3', CellIsRule(operator='equal', formula=['"Fail"'], stopIfTrue=False,
font=whiteFont, fill=redFill))
worksheet.conditional_formatting.setDxfStyles(self.workbook)
# First, verify conditional formatting xml
temp_buffer = StringIO()
doc = XMLGenerator(out=temp_buffer, encoding='utf-8')
write_worksheet_conditional_formatting(doc, worksheet)
doc.endDocument()
xml = temp_buffer.getvalue()
temp_buffer.close()
diff = compare_xml(xml, """
<conditionalFormatting sqref="A1:A3">
<cfRule dxfId="0" operator="equal" priority="1" type="cellIs">
<formula>"Fail"</formula>
</cfRule>
</conditionalFormatting>
""")
assert diff is None, diff
# Second, verify conditional formatting dxf styles
w = StyleWriter(self.workbook)
w._write_dxfs()
xml = get_xml(w._root)
diff = compare_xml(xml, """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<dxfs count="1">
<dxf>
<font>
<color rgb="FFFFFFFF" />
</font>
<fill>
<patternFill patternType="solid">
<fgColor rgb="FFEE1111" />
<bgColor rgb="FFEE1111" />
</patternFill>
</fill>
</dxf>
</dxfs>
</styleSheet>
""")
assert diff is None, diff
def parse_fill(self, fill_node):
"""Read individual fill"""
patternFill = fill_node.find('{%s}patternFill' % SHEET_MAIN_NS)
if patternFill is not None:
newFill = Fill()
newFill.fill_type = patternFill.get('patternType')
fgColor = patternFill.find('{%s}fgColor' % SHEET_MAIN_NS)
if fgColor is not None:
newFill.start_color.index = self._get_relevant_color(fgColor)
bgColor = patternFill.find('{%s}bgColor' % SHEET_MAIN_NS)
if bgColor is not None:
newFill.end_color.index = self._get_relevant_color(bgColor)
return newFill
def parse_fill(self, fill_node):
"""Read individual fill"""
patternFill = fill_node.find('{%s}patternFill' % SHEET_MAIN_NS)
if patternFill is not None:
newFill = Fill()
newFill.fill_type = patternFill.get('patternType')
fgColor = patternFill.find('{%s}fgColor' % SHEET_MAIN_NS)
if fgColor is not None:
newFill.start_color.index = self._get_relevant_color(fgColor)
bgColor = patternFill.find('{%s}bgColor' % SHEET_MAIN_NS)
if bgColor is not None:
newFill.end_color.index = self._get_relevant_color(bgColor)
return newFill
def test_equal(self):
cf = ConditionalFormatting()
redFill = Fill()
redFill.start_color.index = 'FFEE1111'
redFill.end_color.index = 'FFEE1111'
redFill.fill_type = Fill.FILL_SOLID
cf.add(
'U10:U18',
CellIsRule(operator='equal',
formula=['U$7'],
stopIfTrue=True,
fill=redFill))
cf.add(
'V10:V18',
CellIsRule(operator='=',
formula=['V$7'],
stopIfTrue=True,
fill=redFill))
cf.add(
'W10:W18',
CellIsRule(operator='==',
formula=['W$7'],
stopIfTrue=True,
fill=redFill))
cf.setDxfStyles(self.workbook)
rules = cf.cf_rules
assert 'U10:U18' in rules
assert len(cf.cf_rules['U10:U18']) == 1
assert rules['U10:U18'][0]['priority'] == 1
assert rules['U10:U18'][0]['type'] == 'cellIs'
assert rules['U10:U18'][0]['dxfId'] == 0
assert rules['U10:U18'][0]['operator'] == 'equal'
assert rules['U10:U18'][0]['formula'][0] == 'U$7'
assert rules['U10:U18'][0]['stopIfTrue'] == '1'
assert 'V10:V18' in rules
assert len(cf.cf_rules['V10:V18']) == 1
assert rules['V10:V18'][0]['priority'] == 2
assert rules['V10:V18'][0]['type'] == 'cellIs'
assert rules['V10:V18'][0]['dxfId'] == 1
assert rules['V10:V18'][0]['operator'] == 'equal'
assert rules['V10:V18'][0]['formula'][0] == 'V$7'
assert rules['V10:V18'][0]['stopIfTrue'] == '1'
assert 'W10:W18' in rules
assert len(cf.cf_rules['W10:W18']) == 1
assert rules['W10:W18'][0]['priority'] == 3
assert rules['W10:W18'][0]['type'] == 'cellIs'
assert rules['W10:W18'][0]['dxfId'] == 2
assert rules['W10:W18'][0]['operator'] == 'equal'
assert rules['W10:W18'][0]['formula'][0] == 'W$7'
assert rules['W10:W18'][0]['stopIfTrue'] == '1'
def parse_fills(root, color_index, skip_find=False):
"""Read in the list of fills"""
fill_list = []
if skip_find:
fills = root
else:
fills = root.find('{%s}fills' % SHEET_MAIN_NS)
count = 0
if fills is not None:
fillNodes = fills.findall('{%s}fill' % SHEET_MAIN_NS)
for fill in fillNodes:
# Rotation is unset
patternFill = fill.find('{%s}patternFill' % SHEET_MAIN_NS)
if patternFill is not None:
newFill = Fill()
newFill.fill_type = patternFill.get('patternType')
fgColor = patternFill.find('{%s}fgColor' % SHEET_MAIN_NS)
if fgColor is not None:
if fgColor.get('indexed') is not None and 0 <= int(fgColor.get('indexed')) < len(color_index):
newFill.start_color.index = color_index[int(fgColor.get('indexed'))]
elif fgColor.get('indexed') is not None:
# Invalid color - out of range of color_index, set to white
newFill.start_color.index = 'FFFFFFFF'
elif fgColor.get('theme') is not None:
if fgColor.get('tint') is not None:
newFill.start_color.index = 'theme:%s:%s' % (fgColor.get('theme'), fgColor.get('tint'))
else:
newFill.start_color.index = 'theme:%s:' % fgColor.get('theme') # prefix color with theme
else:
newFill.start_color.index = fgColor.get('rgb')
bgColor = patternFill.find('{%s}bgColor' % SHEET_MAIN_NS)
if bgColor is not None:
if bgColor.get('indexed') is not None and 0 <= int(bgColor.get('indexed')) < len(color_index):
newFill.end_color.index = color_index[int(bgColor.get('indexed'))]
elif bgColor.get('indexed') is not None:
# Invalid color - out of range of color_index, set to white
newFill.end_color.index = 'FFFFFFFF'
elif bgColor.get('theme') is not None:
if bgColor.get('tint') is not None:
newFill.end_color.index = 'theme:%s:%s' % (bgColor.get('theme'), bgColor.get('tint'))
else:
newFill.end_color.index = 'theme:%s:' % bgColor.get('theme') # prefix color with theme
elif bgColor.get('rgb'):
newFill.end_color.index = bgColor.get('rgb')
count += 1
fill_list.append(newFill)
return fill_list
class Style:
font: Font = Font()
border: Border = Border()
fill: Fill = Fill()
alignment: Alignment = Alignment()
@classmethod
def from_json(cls,
style_json: Dict,
default_style: 'Style' = None) -> 'Style':
default_style = default_style or Style()
font_data = style_json.get("font", {})
font_data = {**vars(default_style.font), **font_data}
font = Font(**font_data)
sides_data = style_json.get("border", {})
border_data = {
side: Side(**side_data)
for side, side_data in sides_data.items()
}
border_data = {**vars(default_style.border), **border_data}
border = Border(**border_data)
fill_data = style_json.get("fill", {})
fill_data = {**vars(default_style.fill), **fill_data}
fill = PatternFill(**fill_data)
alignment_data = style_json.get("alignment", {})
alignment_data = {**vars(default_style.alignment), **alignment_data}
alignment = Alignment(**alignment_data)
return cls(font, border, fill, alignment)
def styled_sheet():
from openpyxl import Workbook
from openpyxl.styles import Font, Style, Fill, Color, colors
wb = Workbook()
ws = wb.active
ws.title = 'Test 1'
red_fill = Fill()
red_fill.fill_type = 'solid'
red_fill.start_color = Color(Color.RED),
red_fill.end_color = Color(Color.RED)
empty_fill = Fill()
styles = []
# pregenerate relevant styles
for row in range(ROWS):
_row = []
for col in range(COLUMNS):
cell = ws.cell(row=row + 1, column=col + 1)
cell.value = 1
font = Font()
fill = Fill()
if formatData[row][col] & BOLD:
font.bold = True
if formatData[row][col] & ITALIC:
font.italic = True
if formatData[row][col] & UNDERLINE:
font.underline = 'single'
if formatData[row][col] & RED_BG:
fill = red_fill
style = Style()
style.font = font
style.fill = fill
ws._styles[cell.address] = style
def test_conditional_formatting_addDxfStyle(self):
cf = ConditionalFormatting()
fill = Fill()
fill.start_color.index = 'FFEE1111'
fill.end_color.index = 'FFEE1111'
fill.fill_type = Fill.FILL_SOLID
font = Font()
font.name = 'Arial'
font.size = 12
font.bold = True
font.underline = Font.UNDERLINE_SINGLE
borders = Borders()
borders.top.border_style = Border.BORDER_THIN
borders.top.color.index = Color.DARKYELLOW
borders.bottom.border_style = Border.BORDER_THIN
borders.bottom.color.index = Color.BLACK
dxfId = cf.addDxfStyle(self.workbook, font, borders, fill)
assert dxfId == 0
dxfId = cf.addDxfStyle(self.workbook, None, None, fill)
assert dxfId == 1
assert len(self.workbook.style_properties['dxf_list']) == 2
def test_conditional_formatting_setDxfStyle(self):
cf = ConditionalFormatting()
fill = Fill()
fill.start_color.index = 'FFEE1111'
fill.end_color.index = 'FFEE1111'
fill.fill_type = Fill.FILL_SOLID
font = Font()
font.name = 'Arial'
font.size = 12
font.bold = True
font.underline = Font.UNDERLINE_SINGLE
borders = Borders()
borders.top.border_style = Border.BORDER_THIN
borders.top.color.index = Color.DARKYELLOW
borders.bottom.border_style = Border.BORDER_THIN
borders.bottom.color.index = Color.BLACK
cf.add('C1:C10', FormulaRule(formula=['ISBLANK(C1)'], font=font, border=borders, fill=fill))
cf.add('D1:D10', FormulaRule(formula=['ISBLANK(D1)'], fill=fill))
cf.setDxfStyles(self.workbook)
assert len(self.workbook.style_properties['dxf_list']) == 2
assert self.workbook.style_properties['dxf_list'][0] == {'font': font, 'border': borders, 'fill': fill}
assert self.workbook.style_properties['dxf_list'][1] == {'fill': fill}
def test_parse_dxfs():
reference_file = os.path.join(DATADIR, 'reader',
'conditional-formatting.xlsx')
wb = load_workbook(reference_file)
assert isinstance(wb, Workbook)
archive = ZipFile(reference_file, 'r', ZIP_DEFLATED)
read_xml = archive.read(ARC_STYLE)
# Verify length
assert '<dxfs count="164">' in str(read_xml)
assert len(wb.style_properties['dxf_list']) == 164
# Verify first dxf style
reference_file = os.path.join(DATADIR, 'writer', 'expected',
'dxf_style.xml')
with open(reference_file) as expected:
diff = compare_xml(read_xml, expected.read())
assert diff is None, diff
cond_styles = wb.style_properties['dxf_list'][0]
assert cond_styles['font'].color == Color('FF9C0006')
assert not cond_styles['font'].bold
assert not cond_styles['font'].italic
f = Fill()
f.end_color = Color('FFFFC7CE')
assert cond_styles['fill'] == f
# Verify that the dxf styles stay the same when they're written and read back in.
w = StyleWriter(wb)
w._write_dxfs()
write_xml = get_xml(w._root)
read_style_prop = read_style_table(write_xml)
assert len(read_style_prop['dxf_list']) == len(
wb.style_properties['dxf_list'])
for i, dxf in enumerate(read_style_prop['dxf_list']):
assert repr(wb.style_properties['dxf_list'][i] == dxf)
def parse_fills(self):
"""Read in the list of fills"""
fills = self.root.findall('{%s}fills/{%s}fill' % (SHEET_MAIN_NS, SHEET_MAIN_NS))
for fill in fills:
yield Fill.from_tree(fill)
def test_write_dxf(self):
redFill = Fill()
redFill.start_color.index = 'FFEE1111'
redFill.end_color.index = 'FFEE1111'
redFill.fill_type = Fill.FILL_SOLID
whiteFont = Font()
whiteFont.color.index = "FFFFFFFF"
whiteFont.bold = True
whiteFont.italic = True
whiteFont.underline = 'single'
whiteFont.strikethrough = True
blueBorder = Borders()
blueBorder.left.border_style = 'medium'
blueBorder.left.color = Color(Color.BLUE)
blueBorder.right.border_style = 'medium'
blueBorder.right.color = Color(Color.BLUE)
blueBorder.top.border_style = 'medium'
blueBorder.top.color = Color(Color.BLUE)
blueBorder.bottom.border_style = 'medium'
blueBorder.bottom.color = Color(Color.BLUE)
cf = ConditionalFormatting()
cf.add(
'A1:A2',
FormulaRule(formula="[A1=1]",
font=whiteFont,
border=blueBorder,
fill=redFill))
cf.setDxfStyles(self.workbook)
assert len(self.workbook.style_properties['dxf_list']) == 1
assert 'font' in self.workbook.style_properties['dxf_list'][0]
assert 'border' in self.workbook.style_properties['dxf_list'][0]
assert 'fill' in self.workbook.style_properties['dxf_list'][0]
w = StyleWriter(self.workbook)
w._write_dxfs()
xml = get_xml(w._root)
diff = compare_xml(
xml, """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<dxfs count="1">
<dxf>
<font>
<color rgb="FFFFFFFF" />
<b val="1" />
<i val="1" />
<u val="single" />
<strike />
</font>
<fill>
<patternFill patternType="solid">
<fgColor rgb="FFEE1111" />
<bgColor rgb="FFEE1111" />
</patternFill>
</fill>
<border>
<left style="medium">
<color rgb="FF0000FF"></color>
</left>
<right style="medium">
<color rgb="FF0000FF"></color>
</right>
<top style="medium">
<color rgb="FF0000FF"></color>
</top>
<bottom style="medium">
<color rgb="FF0000FF"></color>
</bottom>
</border>
</dxf>
</dxfs>
</styleSheet>
""")
assert diff is None, diff
def __create_fill__(ARGB_color_string):
fill = Fill()
fill.start_color.index = ARGB_color_string
fill.end_color.index = ARGB_color_string
fill.fill_type = Fill.FILL_SOLID
return fill
def test_write_dxf(self):
redFill = Fill()
redFill.start_color.index = 'FFEE1111'
redFill.end_color.index = 'FFEE1111'
redFill.fill_type = Fill.FILL_SOLID
whiteFont = Font()
whiteFont.color.index = "FFFFFFFF"
whiteFont.bold = True
whiteFont.italic = True
whiteFont.underline = 'single'
whiteFont.strikethrough = True
blueBorder = Borders()
blueBorder.left.border_style = 'medium'
blueBorder.left.color = Color(Color.BLUE)
blueBorder.right.border_style = 'medium'
blueBorder.right.color = Color(Color.BLUE)
blueBorder.top.border_style = 'medium'
blueBorder.top.color = Color(Color.BLUE)
blueBorder.bottom.border_style = 'medium'
blueBorder.bottom.color = Color(Color.BLUE)
cf = ConditionalFormatting()
cf.add('A1:A2', FormulaRule(formula="[A1=1]", font=whiteFont, border=blueBorder, fill=redFill))
cf.setDxfStyles(self.workbook)
assert len(self.workbook.style_properties['dxf_list']) == 1
assert 'font' in self.workbook.style_properties['dxf_list'][0]
assert 'border' in self.workbook.style_properties['dxf_list'][0]
assert 'fill' in self.workbook.style_properties['dxf_list'][0]
w = StyleWriter(self.workbook)
w._write_dxfs()
xml = get_xml(w._root)
diff = compare_xml(xml, """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<dxfs count="1">
<dxf>
<font>
<color rgb="FFFFFFFF" />
<b val="1" />
<i val="1" />
<u val="single" />
<strike />
</font>
<fill>
<patternFill patternType="solid">
<fgColor rgb="FFEE1111" />
<bgColor rgb="FFEE1111" />
</patternFill>
</fill>
<border>
<left style="medium">
<color rgb="FF0000FF"></color>
</left>
<right style="medium">
<color rgb="FF0000FF"></color>
</right>
<top style="medium">
<color rgb="FF0000FF"></color>
</top>
<bottom style="medium">
<color rgb="FF0000FF"></color>
</bottom>
</border>
</dxf>
</dxfs>
</styleSheet>
""")
assert diff is None, diff
cell.font = Font(size=12, bold=True, color=Color.GREEN)
#Highlight row background if the cell value is Kavitha
#Change row background to yellow
for row in range(1, rcount):
if not any(cell.value for cell in wsheet[row]):
print("The %d row is empty" % row)
break
for col in range(0, ccount):
if (wsheet[row][col].value != None
and wsheet[row][col].value == "Kavitha"):
print((wsheet[row][col].fill))
print((str(wsheet[row][col].value)))
for cell in wsheet[row:row]:
cell.fill = Fill(fill_type="solid", start_color=Color.YELLOW)
#Highlight column background in case cell value is > 70000
for row in range(1, rcount):
if (wsheet[row][4].value != None
and int(wsheet[row][4].value) > int(70000)):
print((str(wsheet[row][4].value)))
cell = wsheet[row][4]
cell.fill = Fill(fill_type="solid", start_color=Color.GREEN)
for word in dir(wsheet['C3']):
print("print \"%-20s :\", cell.%s" % (word, word))
wb.save('shared/revenue.xlsx')
#dump_cell.dump_cell_properties(wsheet[1][0])
def test_conditional_font(self):
"""Test to verify font style written correctly."""
class WS():
conditional_formatting = ConditionalFormatting()
worksheet = WS()
# Create cf rule
redFill = Fill()
redFill.start_color.index = 'FFEE1111'
redFill.end_color.index = 'FFEE1111'
redFill.fill_type = Fill.FILL_SOLID
whiteFont = Font()
whiteFont.color.index = "FFFFFFFF"
worksheet.conditional_formatting.add(
'A1:A3',
CellIsRule(operator='equal',
formula=['"Fail"'],
stopIfTrue=False,
font=whiteFont,
fill=redFill))
worksheet.conditional_formatting.setDxfStyles(self.workbook)
# First, verify conditional formatting xml
temp_buffer = StringIO()
doc = XMLGenerator(out=temp_buffer, encoding='utf-8')
write_worksheet_conditional_formatting(doc, worksheet)
doc.endDocument()
xml = temp_buffer.getvalue()
temp_buffer.close()
diff = compare_xml(
xml, """
<conditionalFormatting sqref="A1:A3">
<cfRule dxfId="0" operator="equal" priority="1" type="cellIs">
<formula>"Fail"</formula>
</cfRule>
</conditionalFormatting>
""")
assert diff is None, diff
# Second, verify conditional formatting dxf styles
w = StyleWriter(self.workbook)
w._write_dxfs()
xml = get_xml(w._root)
diff = compare_xml(
xml, """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<dxfs count="1">
<dxf>
<font>
<color rgb="FFFFFFFF" />
</font>
<fill>
<patternFill patternType="solid">
<fgColor rgb="FFEE1111" />
<bgColor rgb="FFEE1111" />
</patternFill>
</fill>
</dxf>
</dxfs>
</styleSheet>
""")
assert diff is None, diff
def parse_fills(self):
"""Read in the list of fills"""
fills = self.root.findall('{%s}fills/{%s}fill' % (SHEET_MAIN_NS, SHEET_MAIN_NS))
for fill in fills:
yield Fill.from_tree(fill)
def __create_fill__(ARGB_color_string):
fill = Fill()
fill.start_color.index=ARGB_color_string
fill.end_color.index=ARGB_color_string
fill.fill_type=Fill.FILL_SOLID
return fill