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}
예제 #3
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_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_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
예제 #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
예제 #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'
예제 #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
 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}
예제 #12
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
예제 #13
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
    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
예제 #15
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
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