示例#1
0
    def test_xfs_fills(self):
        st = Style(fill=PatternFill(fill_type='solid',
                                    start_color=Color(colors.DARKYELLOW)))
        self.worksheet.cell('A1').style = st
        w = StyleWriter(self.workbook)
        nft = borders = fonts = DummyElement()
        fills = Element("fills")
        w._write_cell_xfs(nft, fonts, fills, borders)

        xml = get_xml(w._root)
        expected = """ <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
        <cellXfs count="2">
          <xf borderId="0" fillId="0" fontId="0" numFmtId="0" xfId="0"/>
          <xf applyFill="1" borderId="0" fillId="2" fontId="0" numFmtId="0" xfId="0"/>
        </cellXfs>
        </styleSheet>
        """
        diff = compare_xml(xml, expected)
        assert diff is None, diff

        expected = """<fills count="3">
            <fill>
              <patternFill patternType="solid">
                <fgColor rgb="00808000"></fgColor>
               </patternFill>
            </fill>
          </fills>
        """
        xml = get_xml(fills)
        diff = compare_xml(xml, expected)
        assert diff is None, diff
    def test_protection(self):
        prot = Protection(locked=True,
                          hidden=True)
        self.worksheet.cell('A1').style = Style(protection=prot)
        w = StyleWriter(self.workbook)
        w._write_protection(w._root, prot)
        xml = tostring(w._root)
        expected = """
        <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
          <protection hidden="1" locked="1"/>
        </styleSheet>
                """
        diff = compare_xml(xml, expected)
        assert diff is None, diff

        nft = fonts = borders = fills = Element('empty')
        w._write_cell_xfs(nft, fonts, fills, borders)
        xml = tostring(w._root)
        expected = """
        <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
          <protection hidden="1" locked="1"/>
          <cellXfs count="2">
            <xf borderId="0" fillId="0" fontId="0" numFmtId="0" xfId="0"/>
            <xf applyProtection="1" borderId="0" fillId="0" fontId="0" numFmtId="0" xfId="0">
              <protection hidden="1" locked="1"/>
            </xf>
          </cellXfs>
        </styleSheet>
        """
        diff = compare_xml(xml, expected)
        assert diff is None, diff
示例#3
0
def test_write_font():
    wb = DummyWorkbook()
    from openpyxl.styles import Font
    ft = Font(name='Calibri',
              charset=204,
              vertAlign='superscript',
              underline=Font.UNDERLINE_SINGLE)
    writer = StyleWriter(wb)
    writer._write_font(writer._root, ft)
    xml = get_xml(writer._root)
    expected = """
    <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
        <font>
          <vertAlign val="superscript"></vertAlign>
          <sz val="11.0"></sz>
          <color rgb="00000000"></color>
          <name val="Calibri"></name>
          <family val="2"></family>
          <u></u>
          <charset val="204"></charset>
         </font>
    </styleSheet>
    """
    diff = compare_xml(xml, expected)
    assert diff is None, diff
示例#4
0
 def test_borders(self):
     self.worksheet.cell(
         'A1').style.borders.top.border_style = Border.BORDER_THIN
     self.worksheet.cell(
         'A1').style.borders.top.color.index = Color.DARKYELLOW
     w = StyleWriter(self.workbook)
     w._write_borders()
     xml = get_xml(w._root)
     diff = compare_xml(
         xml, """
     <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
       <borders count="2">
         <border>
           <left />
           <right />
           <top />
           <bottom />
           <diagonal />
         </border>
         <border>
           <left />
           <right />
           <top style="thin">
             <color rgb="FF808000" />
           </top>
           <bottom />
           <diagonal />
         </border>
       </borders>
     </styleSheet>
     """)
     assert diff is None, diff
def test_parse_dxfs(datadir):
    datadir.chdir()
    reference_file = '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 = '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 = PatternFill(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 = tostring(w._root)
    read_style_prop = read_style_table(write_xml)
    assert len(read_style_prop[2]) == len(wb.style_properties['dxf_list'])
    for i, dxf in enumerate(read_style_prop[2]):
        assert repr(wb.style_properties['dxf_list'][i] == dxf)
def test_parse_dxfs():
    reference_file = os.path.join(DATADIR, 'reader', 'conditional-formatting.xlsx')
    wb = load_workbook(reference_file)
    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 cond_styles['font']['bold'] == False
    assert cond_styles['font']['italic'] == False
    f = Fill()
    f.end_color = Color('FFFFC7CE')
    assert cond_styles['fill'][0] == 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)
示例#7
0
 def test_fonts(self):
     self.worksheet.cell('A1').style.font.size = 12
     self.worksheet.cell('A1').style.font.bold = True
     w = StyleWriter(self.workbook)
     w._write_fonts()
     xml = get_xml(w._root)
     diff = compare_xml(xml, """
     <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
       <fonts count="2">
         <font>
           <sz val="11" />
           <color theme="1" />
           <name val="Calibri" />
           <family val="2" />
           <scheme val="minor" />
         </font>
         <font>
           <sz val="12" />
           <color rgb="FF000000" />
           <name val="Calibri" />
           <family val="2" />
           <b />
         </font>
       </fonts>
     </styleSheet>
     """)
     assert diff is None, diff
示例#8
0
def test_simple_styles(datadir):
    wb = Workbook(guess_types=True)
    ws = wb.active
    now = datetime.datetime.now()
    for idx, v in enumerate(
        ['12.34%', now, 'This is a test', '31.31415', None], 1):
        ws.append([v])
        _ = ws.cell(column=1, row=idx).style_id

    # set explicit formats
    ws['D9'].number_format = numbers.FORMAT_NUMBER_00
    ws['D9'].protection = Protection(locked=True)
    ws['D9'].style_id
    ws['E1'].protection = Protection(hidden=True)
    ws['E1'].style_id

    assert len(wb._cell_styles) == 5
    writer = StyleWriter(wb)

    datadir.chdir()
    with open('simple-styles.xml') as reference_file:
        expected = reference_file.read()
    xml = writer.write_table()
    diff = compare_xml(xml, expected)
    assert diff is None, diff
示例#9
0
 def test_borders(self):
     st = Style(border=Border(top=Side(border_style=borders.BORDER_THIN,
                                       color=Color(colors.DARKYELLOW))))
     self.worksheet.cell('A1').style = st
     w = StyleWriter(self.workbook)
     w._write_borders()
     xml = get_xml(w._root)
     diff = compare_xml(
         xml, """
     <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
       <borders count="2">
         <border>
           <left />
           <right />
           <top />
           <bottom />
           <diagonal />
         </border>
         <border>
           <left />
           <right />
           <top style="thin">
             <color rgb="0000FF00" />
           </top>
           <bottom />
           <diagonal />
         </border>
       </borders>
     </styleSheet>
     """)
     assert diff is None, diff
示例#10
0
    def test_borders(self):

        self.worksheet.cell('A1').style.borders.top.border_style = Border.BORDER_THIN
        self.worksheet.cell('A1').style.borders.top.color.index = Color.DARKYELLOW
        w = StyleWriter(self.workbook)
        w._write_borders()
        eq_(get_xml(w._root), '<?xml version=\'1.0\' encoding=\'UTF-8\'?><styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><borders count="2"><border><left /><right /><top /><bottom /><diagonal /></border><border><left style="none"><color rgb="FF000000" /></left><right style="none"><color rgb="FF000000" /></right><top style="thin"><color rgb="FF808000" /></top><bottom style="none"><color rgb="FF000000" /></bottom><diagonal style="none"><color rgb="FF000000" /></diagonal></border></borders></styleSheet>')
示例#11
0
    def test_fonts(self):

        self.worksheet.cell('A1').style.font.size = 12
        self.worksheet.cell('A1').style.font.bold = True
        w = StyleWriter(self.workbook)
        w._write_fonts()
        eq_(get_xml(w._root), '<?xml version=\'1.0\' encoding=\'UTF-8\'?><styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><fonts count="2"><font><sz val="11" /><color theme="1" /><name val="Calibri" /><family val="2" /><scheme val="minor" /></font><font><sz val="12" /><color rgb="FF000000" /><name val="Calibri" /><family val="2" /><b /></font></fonts></styleSheet>')
    def test_xfs_number_format(self):
        for o in range(1, 4):
            for i in range(1, 4):
                # Two of these are custom, 0.0% and 0.000%. 0.00% is a built in format ID
                self.worksheet.cell(row=o, column=i).number_format = '0.' + '0' * i + '%'
        w = StyleWriter(self.workbook)
        fonts = borders = fills = DummyElement()
        nft = SubElement(w._root, 'numFmts')
        w._write_cell_xfs(nft, fonts, fills, borders)

        expected = """
        <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
            <numFmts count="2">
                <numFmt formatCode="0.0%" numFmtId="165"/>
                <numFmt formatCode="0.000%" numFmtId="166"/>
            </numFmts>
            <cellXfs count="4">
                <xf borderId="0" fillId="0" fontId="0" numFmtId="0" xfId="0"/>
                <xf applyNumberFormat="1" borderId="0" fillId="0" fontId="0" numFmtId="165" xfId="0"/>
                <xf applyNumberFormat="1" borderId="0" fillId="0" fontId="0" numFmtId="10" xfId="0"/>
                <xf applyNumberFormat="1" borderId="0" fillId="0" fontId="0" numFmtId="166" xfId="0"/>
            </cellXfs>
        </styleSheet>
        """

        xml = tostring(w._root)
        diff = compare_xml(xml, expected)
        assert diff is None, diff
示例#13
0
    def test_fills(self):

        self.worksheet.cell('A1').style.fill.fill_type = 'solid'
        self.worksheet.cell('A1').style.fill.start_color.index = Color.DARKYELLOW
        w = StyleWriter(self.workbook)
        w._write_fills()
        eq_(get_xml(w._root), '<?xml version=\'1.0\' encoding=\'UTF-8\'?><styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><fills count="3"><fill><patternFill patternType="none" /></fill><fill><patternFill patternType="gray125" /></fill><fill><patternFill patternType="solid"><fgColor rgb="FF808000" /></patternFill></fill></fills></styleSheet>')
示例#14
0
    def test_fills(self):

        self.worksheet.cell('A1').style.fill.fill_type = 'solid'
        self.worksheet.cell('A1').style.fill.start_color.index = Color.DARKYELLOW
        w = StyleWriter(self.workbook)
        w._write_fills()
        eq_(get_xml(w._root), '<?xml version=\'1.0\' encoding=\'UTF-8\'?><styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><fills count="3"><fill><patternFill patternType="none" /></fill><fill><patternFill patternType="gray125" /></fill><fill><patternFill patternType="solid"><fgColor rgb="FF808000" /></patternFill></fill></fills></styleSheet>')
示例#15
0
 def test_borders(self):
     st = Style(border=Border(top=Side(border_style=borders.BORDER_THIN,
                                           color=Color(colors.DARKYELLOW))))
     self.worksheet.cell('A1').style = st
     w = StyleWriter(self.workbook)
     w._write_borders()
     xml = get_xml(w._root)
     diff = compare_xml(xml, """
     <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
       <borders count="2">
         <border>
           <left />
           <right />
           <top />
           <bottom />
           <diagonal />
         </border>
         <border>
           <left />
           <right />
           <top style="thin">
             <color rgb="0000FF00" />
           </top>
           <bottom />
           <diagonal />
         </border>
       </borders>
     </styleSheet>
     """)
     assert diff is None, diff
示例#16
0
 def test_fills(self):
     st = Style(fill=PatternFill(fill_type='solid',
                          start_color=Color(colors.DARKYELLOW)))
     self.worksheet.cell('A1').style = st
     w = StyleWriter(self.workbook)
     w._write_fills()
     xml = get_xml(w._root)
     diff = compare_xml(xml, """
     <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
       <fills count="3">
         <fill>
           <patternFill patternType="none" />
         </fill>
         <fill>
           <patternFill patternType="gray125" />
         </fill>
         <fill>
           <patternFill patternType="solid">
             <fgColor rgb="0000FF00" />
           </patternFill>
         </fill>
       </fills>
     </styleSheet>
     """)
     assert diff is None, diff
示例#17
0
 def test_borders(self):
     self.worksheet.cell('A1').style.borders.top.border_style = Border.BORDER_THIN
     self.worksheet.cell('A1').style.borders.top.color.index = Color.DARKYELLOW
     w = StyleWriter(self.workbook)
     w._write_borders()
     xml = get_xml(w._root)
     diff = compare_xml(xml, """
     <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
       <borders count="2">
         <border>
           <left />
           <right />
           <top />
           <bottom />
           <diagonal />
         </border>
         <border>
           <left />
           <right />
           <top style="thin">
             <color rgb="FF808000" />
           </top>
           <bottom />
           <diagonal />
         </border>
       </borders>
     </styleSheet>
     """)
     assert diff is None, diff
def test_parse_dxfs():
    reference_file = os.path.join(DATADIR, 'reader',
                                  'conditional-formatting.xlsx')
    wb = load_workbook(reference_file)
    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 cond_styles['font']['bold'] == False
    assert cond_styles['font']['italic'] == False
    f = Fill()
    f.end_color = Color('FFFFC7CE')
    assert cond_styles['fill'][0] == 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_xfs_fills(self):
        st = Style(fill=PatternFill(
            fill_type='solid',
            start_color=Color(colors.DARKYELLOW))
                   )
        self.worksheet.cell('A1').style = st
        w = StyleWriter(self.workbook)
        nft = borders = fonts = DummyElement()
        fills = Element("fills")
        w._write_cell_xfs(nft, fonts, fills, borders)

        xml = tostring(w._root)
        expected = """ <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
        <cellXfs count="2">
          <xf borderId="0" fillId="0" fontId="0" numFmtId="0" xfId="0"/>
          <xf applyFill="1" borderId="0" fillId="2" fontId="0" numFmtId="0" xfId="0"/>
        </cellXfs>
        </styleSheet>
        """
        diff = compare_xml(xml, expected)
        assert diff is None, diff

        expected = """<fills count="3">
            <fill>
              <patternFill patternType="solid">
                <fgColor rgb="00808000"></fgColor>
               </patternFill>
            </fill>
          </fills>
        """
        xml = tostring(fills)
        diff = compare_xml(xml, expected)
        assert diff is None, diff
示例#20
0
 def test_fonts(self):
     self.worksheet.cell('A1').style.font.size = 12
     self.worksheet.cell('A1').style.font.bold = True
     w = StyleWriter(self.workbook)
     w._write_fonts()
     xml = get_xml(w._root)
     diff = compare_xml(
         xml, """
     <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
       <fonts count="2">
         <font>
           <sz val="11" />
           <color theme="1" />
           <name val="Calibri" />
           <family val="2" />
           <scheme val="minor" />
         </font>
         <font>
           <sz val="12" />
           <color rgb="FF000000" />
           <name val="Calibri" />
           <family val="2" />
           <b />
         </font>
       </fonts>
     </styleSheet>
     """)
     assert diff is None, diff
示例#21
0
    def test_borders(self):

        self.worksheet.cell('A1').style.borders.top.border_style = Border.BORDER_THIN
        self.worksheet.cell('A1').style.borders.top.color.index = Color.DARKYELLOW
        w = StyleWriter(self.workbook)
        w._write_borders()
        eq_(get_xml(w._root), '<?xml version=\'1.0\' encoding=\'UTF-8\'?><styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><borders count="2"><border><left /><right /><top /><bottom /><diagonal /></border><border><left /><right /><top style="thin"><color rgb="FF808000" /></top><bottom /><diagonal /></border></borders></styleSheet>')
示例#22
0
    def test_xfs_fonts(self):
        st = Style(font=Font(size=12, bold=True))
        self.worksheet.cell('A1').style = st
        w = StyleWriter(self.workbook)

        nft = borders = fills = DummyElement()
        fonts = Element("fonts")
        w._write_cell_xfs(nft, fonts, fills, borders)
        xml = get_xml(w._root)
        assert """applyFont="1" """ in xml
        assert """fontId="1" """ in xml

        expected = """
        <fonts count="2">
        <font>
            <sz val="12.0" />
            <color rgb="00000000"></color>
            <name val="Calibri" />
            <family val="2" />
            <b></b>
        </font>
        </fonts>
        """
        xml = get_xml(fonts)
        diff = compare_xml(xml, expected)
        assert diff is None, diff
示例#23
0
 def test_fonts_with_underline(self):
     st = Style(
         font=Font(size=12, bold=True, underline=Font.UNDERLINE_SINGLE))
     self.worksheet.cell('A1').style = st
     w = StyleWriter(self.workbook)
     w._write_fonts()
     xml = get_xml(w._root)
     diff = compare_xml(
         xml, """
     <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
       <fonts count="2">
         <font>
           <sz val="11" />
           <color theme="1" />
           <name val="Calibri" />
           <family val="2" />
           <scheme val="minor" />
         </font>
         <font>
           <sz val="12.0" />
           <color rgb="00000000" />
           <name val="Calibri" />
           <family val="2" />
           <b />
           <u />
         </font>
       </fonts>
     </styleSheet>
     """)
     assert diff is None, diff
    def test_write_dxf(self):
        redFill = PatternFill(start_color=Color('FFEE1111'),
                       end_color=Color('FFEE1111'),
                       fill_type=fills.FILL_SOLID)
        whiteFont = Font(color=Color("FFFFFFFF"),
                         bold=True, italic=True, underline='single',
                         strikethrough=True)
        medium_blue = Side(border_style='medium', color=Color(colors.BLUE))
        blueBorder = Border(left=medium_blue,
                             right=medium_blue,
                             top=medium_blue,
                             bottom=medium_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 = tostring(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="000000FF"></color>
                </left>
                <right style="medium">
                    <color rgb="000000FF"></color>
                </right>
                <top style="medium">
                    <color rgb="000000FF"></color>
                </top>
                <bottom style="medium">
                    <color rgb="000000FF"></color>
                </bottom>
            </border>
            </dxf>
          </dxfs>
        </styleSheet>
        """)
        assert diff is None, diff
    def test_xfs_fonts(self):
        st = Style(font=Font(size=12, bold=True))
        self.worksheet.cell('A1').style = st
        w = StyleWriter(self.workbook)

        nft = borders = fills = DummyElement()
        fonts = Element("fonts")
        w._write_cell_xfs(nft, fonts, fills, borders)
        xml = unicode(tostring(w._root))
        assert """applyFont="1" """ in xml
        assert """fontId="1" """ in xml

        expected = """
        <fonts count="2">
        <font>
            <sz val="12.0" />
            <color rgb="00000000"></color>
            <name val="Calibri" />
            <family val="2" />
            <b></b>
        </font>
        </fonts>
        """
        xml = tostring(fonts)
        diff = compare_xml(xml, expected)
        assert diff is None, diff
示例#26
0
 def test_fills(self):
     st = Style(fill=PatternFill(fill_type='solid',
                                 start_color=Color(colors.DARKYELLOW)))
     self.worksheet.cell('A1').style = st
     w = StyleWriter(self.workbook)
     w._write_fills()
     xml = get_xml(w._root)
     diff = compare_xml(
         xml, """
     <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
       <fills count="3">
         <fill>
           <patternFill patternType="none" />
         </fill>
         <fill>
           <patternFill patternType="gray125" />
         </fill>
         <fill>
           <patternFill patternType="solid">
             <fgColor rgb="0000FF00" />
           </patternFill>
         </fill>
       </fills>
     </styleSheet>
     """)
     assert diff is None, diff
def test_parse_dxfs(datadir):
    datadir.chdir()
    reference_file = '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 = '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 = PatternFill(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_conditional_font(self):
        """Test to verify font style written correctly."""
        class WS():
            conditional_formatting = ConditionalFormatting()

        worksheet = WS()

        # Create cf rule
        redFill = PatternFill(start_color=Color('FFEE1111'),
                              end_color=Color('FFEE1111'),
                              patternType=fills.FILL_SOLID)
        whiteFont = Font(color=Color("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
        cfs = write_conditional_formatting(worksheet)
        xml = b""
        for cf in cfs:
            xml += tostring(cf)

        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 = tostring(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
示例#29
0
 def test_fonts_with_underline(self):
     st = Style(font=Font(size=12, bold=True,
                          underline=Font.UNDERLINE_SINGLE))
     self.worksheet.cell('A1').style = st
     w = StyleWriter(self.workbook)
     w._write_fonts()
     xml = get_xml(w._root)
     diff = compare_xml(xml, """
     <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
       <fonts count="2">
         <font>
           <sz val="11" />
           <color theme="1" />
           <name val="Calibri" />
           <family val="2" />
           <scheme val="minor" />
         </font>
         <font>
           <sz val="12.0" />
           <color rgb="00000000" />
           <name val="Calibri" />
           <family val="2" />
           <b />
           <u />
         </font>
       </fonts>
     </styleSheet>
     """)
     assert diff is None, diff
示例#30
0
    def test_fonts(self):

        self.worksheet.cell('A1').style.font.size = 12
        self.worksheet.cell('A1').style.font.bold = True
        w = StyleWriter(self.workbook)
        w._write_fonts()
        eq_(get_xml(w._root), '<?xml version=\'1.0\' encoding=\'UTF-8\'?><styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><fonts count="2"><font><sz val="11" /><color theme="1" /><name val="Calibri" /><family val="2" /><scheme val="minor" /></font><font><sz val="12" /><color rgb="FF000000" /><name val="Calibri" /><family val="2" /><b /></font></fonts></styleSheet>')
示例#31
0
    def test_nb_style(self):
        for i in range(1, 6):
            self.worksheet.cell(row=1, column=i).style.font.size += i
        w = StyleWriter(self.workbook)
        assert len(w.style_table) == 5

        self.worksheet.cell('A10').style.borders.top = Border.BORDER_THIN
        w = StyleWriter(self.workbook)
        assert len(w.style_table) == 6
示例#32
0
 def test_fills(self):
     self.worksheet.cell('A1').style.fill.fill_type = 'solid'
     self.worksheet.cell('A1').style.fill.start_color.index = Color.DARKYELLOW
     w = StyleWriter(self.workbook)
     w._write_fills()
     expected = """<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><fills count="3"><fill><patternFill patternType="none" /></fill><fill><patternFill patternType="gray125" /></fill><fill><patternFill patternType="solid"><fgColor rgb="FF808000" /></patternFill></fill></fills></styleSheet>"""
     xml = get_xml(w._root)
     diff = compare_xml(xml, expected)
     assert diff is None, diff
示例#33
0
 def test_alignment(self):
     self.worksheet.cell('A1').style.alignment.horizontal = 'center'
     self.worksheet.cell('A1').style.alignment.vertical = 'center'
     w = StyleWriter(self.workbook)
     nft = w._write_number_formats()
     w._write_cell_xfs(nft, {}, {}, {})
     xml = get_xml(w._root)
     assert 'applyAlignment="1"' in xml
     assert 'horizontal="center"' in xml
     assert 'vertical="center"' in xml
示例#34
0
 def test_protection(self):
     self.worksheet.cell('A1').style.protection.locked = Protection.PROTECTION_UNPROTECTED
     self.worksheet.cell('A1').style.protection.hidden = Protection.PROTECTION_UNPROTECTED
     w = StyleWriter(self.workbook)
     nft = w._write_number_formats()
     w._write_cell_xfs(nft, {}, {}, {})
     xml = get_xml(w._root)
     assert 'protection' in xml
     assert 'locked="0"' in xml
     assert 'hidden="0"' in xml
示例#35
0
 def test_alignment(self):
     self.worksheet.cell('A1').style.alignment.horizontal = 'center'
     self.worksheet.cell('A1').style.alignment.vertical = 'center'
     w = StyleWriter(self.workbook)
     nft = w._write_number_formats()
     w._write_cell_xfs(nft, {}, {}, {})
     xml = get_xml(w._root)
     ok_('applyAlignment="1"' in xml)
     ok_('horizontal="center"' in xml)
     ok_('vertical="center"' in xml)
示例#36
0
 def test_alignment(self):
     st = Style(alignment=Alignment(horizontal='center', vertical='center'))
     self.worksheet.cell('A1').style = st
     w = StyleWriter(self.workbook)
     nft = w._write_number_formats()
     w._write_cell_xfs(nft, {}, {}, {})
     xml = get_xml(w._root)
     assert 'applyAlignment="1"' in xml
     assert 'horizontal="center"' in xml
     assert 'vertical="center"' in xml
    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
示例#38
0
 def test_alignment_rotation(self):
     self.worksheet.cell('A1').style.alignment.vertical = 'center'
     self.worksheet.cell('A1').style.alignment.text_rotation = 90
     self.worksheet.cell('A2').style.alignment.vertical = 'center'
     self.worksheet.cell('A2').style.alignment.text_rotation = 135
     w = StyleWriter(self.workbook)
     nft = w._write_number_formats()
     w._write_cell_xfs(nft, {}, {}, {})
     xml = get_xml(w._root)
     ok_('textRotation="90"' in xml)
     ok_('textRotation="135"' in xml)
示例#39
0
 def test_protection(self):
     self.worksheet.cell('A1').style = Style(
         protection=Protection(locked=Protection.PROTECTION_UNPROTECTED,
                               hidden=Protection.PROTECTION_UNPROTECTED))
     w = StyleWriter(self.workbook)
     nft = w._write_number_formats()
     w._write_cell_xfs(nft, {}, {}, {})
     xml = get_xml(w._root)
     assert 'protection' in xml
     assert 'locked="0"' in xml
     assert 'hidden="0"' in xml
示例#40
0
 def test_alignment_rotation(self):
     self.worksheet.cell('A1').style = Style(alignment=Alignment(vertical='center', text_rotation=90))
     self.worksheet.cell('A2').style = Style(alignment=Alignment(vertical='center', text_rotation=135))
     self.worksheet.cell('A3').style = Style(alignment=Alignment(text_rotation=-34))
     w = StyleWriter(self.workbook)
     nft = w._write_number_formats()
     w._write_cell_xfs(nft, {}, {}, {})
     xml = get_xml(w._root)
     assert 'textRotation="90"' in xml
     assert 'textRotation="135"' in xml
     assert 'textRotation="124"' in xml
示例#41
0
 def test_alignment_rotation(self):
     self.worksheet.cell('A1').style.alignment.vertical = 'center'
     self.worksheet.cell('A1').style.alignment.text_rotation = 90
     self.worksheet.cell('A2').style.alignment.vertical = 'center'
     self.worksheet.cell('A2').style.alignment.text_rotation = 135
     w = StyleWriter(self.workbook)
     nft = w._write_number_formats()
     w._write_cell_xfs(nft, {}, {}, {})
     xml = get_xml(w._root)
     ok_('textRotation="90"' in xml)
     ok_('textRotation="135"' in xml)
示例#42
0
    def test_nb_style(self):
        for i in range(1, 6):
            self.worksheet.cell(row=1,
                                column=i).style = Style(font=Font(size=i))
        w = StyleWriter(self.workbook)
        assert len(w.styles) == 6  # 5 + the default

        self.worksheet.cell('A10').style = Style(border=Border(top=Side(
            border_style=borders.BORDER_THIN)))
        w = StyleWriter(self.workbook)
        assert len(w.styles) == 7
示例#43
0
 def test_write_cell_xfs_1(self):
     self.worksheet.cell('A1').style.font.size = 12
     w = StyleWriter(self.workbook)
     ft = w._write_fonts()
     nft = w._write_number_formats()
     w._write_cell_xfs(nft, ft, {}, {})
     xml = get_xml(w._root)
     assert 'applyFont="1"' in xml
     assert 'applyFill="1"' not in xml
     assert 'applyBorder="1"' not in xml
     assert 'applyAlignment="1"' not in xml
 def test_alignment_default(self):
     w = StyleWriter(self.workbook)
     al = Alignment()
     w._write_alignment(w._root, al)
     xml = tostring(w._root)
     expected = """
     <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
     <alignment/>
     </styleSheet>
     """
     diff = compare_xml(xml, expected)
     assert diff is None, diff
示例#45
0
 def test_alignment_default(self):
     w = StyleWriter(self.workbook)
     al = Alignment()
     w._write_alignment(w._root, al)
     xml = get_xml(w._root)
     expected = """
     <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
     <alignment/>
     </styleSheet>
     """
     diff = compare_xml(xml, expected)
     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 = PatternFill(start_color=Color('FFEE1111'),
                       end_color=Color('FFEE1111'),
                       patternType=fills.FILL_SOLID)
        whiteFont = Font(color=Color("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
        cfs = write_conditional_formatting(worksheet)
        xml = b""
        for cf in cfs:
            xml += tostring(cf)

        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 = tostring(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
示例#47
0
def test_complex_styles(datadir):
    """Hold on to your hats"""
    from openpyxl import load_workbook
    datadir.join("..", "..", "..", "reader", "tests", "data").chdir()
    wb = load_workbook("complex-styles.xlsx")

    datadir.chdir()
    with open("complex-styles.xml") as reference:
        writer = StyleWriter(wb)
        xml = writer.write_table()
        expected = reference.read()
        diff = compare_xml(xml, expected)
        assert diff is None, diff
def test_complex_styles(datadir):
    """Hold on to your hats"""
    from openpyxl import load_workbook
    datadir.join("..", "..", "..", "reader", "tests", "data").chdir()
    wb = load_workbook("complex-styles.xlsx")

    datadir.chdir()
    with open("complex-styles.xml") as reference:
        writer = StyleWriter(wb)
        xml = writer.write_table()
        expected = reference.read()
        diff = compare_xml(xml, expected)
        assert diff is None, diff
示例#49
0
 def test_style_names(self):
     writer = StyleWriter(self.workbook)
     writer._write_style_names()
     xml = tostring(writer._root)
     expected = """
         <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
         <cellStyles count="1">
           <cellStyle name="Normal" xfId="0" builtinId="0" hidden="0"/>
         </cellStyles>
         </styleSheet>
         """
     diff = compare_xml(xml, expected)
     assert diff is None, diff
示例#50
0
 def test_named_styles(self):
     writer = StyleWriter(self.workbook)
     writer._write_named_styles()
     xml = tostring(writer._root)
     expected = """
     <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
     <cellStyleXfs count="1">
       <xf borderId="0" fillId="0" fontId="0" numFmtId="0"></xf>
     </cellStyleXfs>
     </styleSheet>
     """
     diff = compare_xml(xml, expected)
     assert diff is None, diff
示例#51
0
    def test_nb_style(self):
        for i in range(1, 6):
            cell = self.worksheet.cell(row=1, column=i)
            cell.font = Font(size=i)
            _ = cell.style_id
        w = StyleWriter(self.workbook)
        assert len(w.wb._cell_styles) == 6  # 5 + the default

        cell = self.worksheet.cell('A10')
        cell.border=Border(top=Side(border_style=borders.BORDER_THIN))
        _ = cell.style_id
        w = StyleWriter(self.workbook)
        assert len(w.wb._cell_styles) == 7
示例#52
0
 def test_alignment_indent(self):
     self.worksheet.cell('A1').style.alignment.indent = 1
     self.worksheet.cell('A2').style.alignment.indent = 4
     self.worksheet.cell('A3').style.alignment.indent = 0
     self.worksheet.cell('A3').style.alignment.indent = -1
     w = StyleWriter(self.workbook)
     nft = w._write_number_formats()
     w._write_cell_xfs(nft, {}, {}, {})
     xml = get_xml(w._root)
     ok_('indent="1"' in xml)
     ok_('indent="4"' in xml)
     #Indents not greater than zero are ignored when writing
     ok_('indent="0"' not in xml)
     ok_('indent="-1"' not in xml)
示例#53
0
 def test_alignment_indent(self):
     self.worksheet.cell('A1').style.alignment.indent = 1
     self.worksheet.cell('A2').style.alignment.indent = 4
     self.worksheet.cell('A3').style.alignment.indent = 0
     self.worksheet.cell('A3').style.alignment.indent = -1
     w = StyleWriter(self.workbook)
     nft = w._write_number_formats()
     w._write_cell_xfs(nft, {}, {}, {})
     xml = get_xml(w._root)
     assert 'indent="1"' in xml
     assert 'indent="4"' in xml
     #Indents not greater than zero are ignored when writing
     assert 'indent="0"' not in xml
     assert 'indent="-1"' not in xml
示例#54
0
 def test_default_xfs(self):
     w = StyleWriter(self.workbook)
     fonts = nft = borders = fills = DummyElement()
     w._write_cell_styles()
     xml = tostring(w._root)
     expected = """
     <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
     <cellXfs count="1">
       <xf borderId="0" fillId="0" fontId="0" numFmtId="0" xfId="0"/>
     </cellXfs>
     </styleSheet>
     """
     diff = compare_xml(xml, expected)
     assert diff is None, diff
 def test_default_xfs(self):
     w = StyleWriter(self.workbook)
     fonts = nft = borders = fills = DummyElement()
     w._write_cell_styles()
     xml = tostring(w._root)
     expected = """
     <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
     <cellXfs count="1">
       <xf borderId="0" fillId="0" fontId="0" numFmtId="0" xfId="0"/>
     </cellXfs>
     </styleSheet>
     """
     diff = compare_xml(xml, expected)
     assert diff is None, diff
示例#56
0
 def test_alignment_indent(self):
     self.worksheet.cell('A1').style = Style(alignment=Alignment(indent=1))
     self.worksheet.cell('A2').style = Style(alignment=Alignment(indent=4))
     self.worksheet.cell('A3').style = Style(alignment=Alignment(indent=0))
     self.worksheet.cell('A3').style = Style(alignment=Alignment(indent=-1))
     w = StyleWriter(self.workbook)
     nft = w._write_number_formats()
     w._write_cell_xfs(nft, {}, {}, {})
     xml = get_xml(w._root)
     assert 'indent="1"' in xml
     assert 'indent="4"' in xml
     #Indents not greater than zero are ignored when writing
     assert 'indent="0"' not in xml
     assert 'indent="-1"' not in xml
def test_write_number_formats():
    wb = DummyWorkbook()
    wb._number_formats = ['YYYY']
    writer = StyleWriter(wb)
    writer._write_number_formats()
    xml = tostring(writer._root)
    expected = """
    <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
       <numFmts count="1">
           <numFmt formatCode="YYYY" numFmtId="164"></numFmt>
        </numFmts>
    </styleSheet>
    """
    diff = compare_xml(xml, expected)
    assert diff is None, diff