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
Ejemplo n.º 6
0
 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
Ejemplo n.º 7
0
 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'
Ejemplo n.º 9
0
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
Ejemplo n.º 10
0
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)
Ejemplo n.º 11
0
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)
Ejemplo n.º 15
0
 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)
Ejemplo n.º 16
0
    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
Ejemplo n.º 17
0
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
Ejemplo n.º 18
0
    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
Ejemplo n.º 21
0
 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