def test_conditional_formatting_update(self):
        class WS():
            conditional_formatting = ConditionalFormatting()
        worksheet = WS()
        rules = {'A1:A4': [{'type': 'colorScale', 'priority': 13,
                            'colorScale': {'cfvo': [{'type': 'min'}, {'type': 'max'}], 'color':
                                           [Color('FFFF7128'), Color('FFFFEF9C')]}}]}
        worksheet.conditional_formatting.update(rules)

        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:A4">
          <cfRule type="colorScale" priority="1">
            <colorScale>
              <cfvo type="min" />
              <cfvo type="max" />
              <color rgb="FFFF7128" />
              <color rgb="FFFFEF9C" />
            </colorScale>
          </cfRule>
        </conditionalFormatting>
        """)
        assert diff is None, diff
    def test_conditional_formatting_customRule(self):
        class WS():
            conditional_formatting = ConditionalFormatting()

        worksheet = WS()
        worksheet.conditional_formatting.add(
            'C1:C10', {
                'type': 'expression',
                'formula': ['ISBLANK(C1)'],
                'stopIfTrue': '1',
                'dxf': {}
            })
        worksheet.conditional_formatting.setDxfStyles(self.workbook)
        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="C1:C10">
          <cfRule dxfId="0" type="expression" stopIfTrue="1" priority="1">
            <formula>ISBLANK(C1)</formula>
          </cfRule>
        </conditionalFormatting>
        """)
        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
    def test_conditional_formatting_addCustomRule(self):
        class WS():
            conditional_formatting = ConditionalFormatting()
        worksheet = WS()
        dxfId = worksheet.conditional_formatting.addDxfStyle(self.workbook, None, None, None)
        worksheet.conditional_formatting.addCustomRule('C1:C10',  {'type': 'expression', 'dxfId': dxfId, 'formula': ['ISBLANK(C1)'], 'stopIfTrue': '1'})

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

        assert dxfId == 0
        expected = '<conditionalFormatting sqref="C1:C10"><cfRule dxfId="0" type="expression" stopIfTrue="1" priority="1"><formula>ISBLANK(C1)</formula></cfRule></conditionalFormatting>'
        diff = compare_xml(xml, expected)
        assert diff is None, diff
    def test_conditional_formatting_update(self):
        class WS():
            conditional_formatting = ConditionalFormatting()

        worksheet = WS()
        rules = {
            'A1:A4': [{
                'type': 'colorScale',
                'priority': 13,
                'colorScale': {
                    'cfvo': [{
                        'type': 'min'
                    }, {
                        'type': 'max'
                    }],
                    'color': [Color('FFFF7128'),
                              Color('FFFFEF9C')]
                }
            }]
        }
        worksheet.conditional_formatting.update(rules)

        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:A4">
          <cfRule type="colorScale" priority="1">
            <colorScale>
              <cfvo type="min" />
              <cfvo type="max" />
              <color rgb="FFFF7128" />
              <color rgb="FFFFEF9C" />
            </colorScale>
          </cfRule>
        </conditionalFormatting>
        """)
        assert diff is None, diff
Exemplo n.º 6
0
def write_string_table(string_table):
    """Write the string table xml."""
    temp_buffer = StringIO()
    doc = XMLGenerator(out=temp_buffer, encoding='utf-8')
    start_tag(doc, 'sst', {'xmlns':
            'http://schemas.openxmlformats.org/spreadsheetml/2006/main',
            'uniqueCount': '%d' % len(string_table)})
    strings_to_write = sorted(string_table.items(),
            key=lambda pair: pair[1])
    for key in [pair[0] for pair in strings_to_write]:
        start_tag(doc, 'si')
        if key.strip() != key:
            attr = {'xml:space': 'preserve'}
        else:
            attr = {}
        tag(doc, 't', attr, key)
        end_tag(doc, 'si')
    end_tag(doc, 'sst')
    string_table_xml = temp_buffer.getvalue()
    temp_buffer.close()
    return string_table_xml
    def test_formula_rule(self):
        class WS():
            conditional_formatting = ConditionalFormatting()
        worksheet = WS()
        worksheet.conditional_formatting.add('C1:C10', FormulaRule(formula=['ISBLANK(C1)'], stopIfTrue=True))
        worksheet.conditional_formatting.setDxfStyles(self.workbook)
        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="C1:C10">
          <cfRule dxfId="0" type="expression" stopIfTrue="1" priority="1">
            <formula>ISBLANK(C1)</formula>
          </cfRule>
        </conditionalFormatting>
        """)
        assert diff is None, diff
Exemplo n.º 8
0
def write_string_table(string_table):
    """Write the string table xml."""
    temp_buffer = StringIO()
    doc = XMLGenerator(out=temp_buffer, encoding='utf-8')
    start_tag(
        doc, 'sst', {
            'xmlns':
            'http://schemas.openxmlformats.org/spreadsheetml/2006/main',
            'uniqueCount': '%d' % len(string_table)
        })
    strings_to_write = sorted(string_table.items(), key=lambda pair: pair[1])
    for key in [pair[0] for pair in strings_to_write]:
        start_tag(doc, 'si')
        if key.strip() != key:
            attr = {'xml:space': 'preserve'}
        else:
            attr = {}
        tag(doc, 't', attr, key)
        end_tag(doc, 'si')
    end_tag(doc, 'sst')
    string_table_xml = temp_buffer.getvalue()
    temp_buffer.close()
    return string_table_xml
Exemplo n.º 9
0
def write_worksheet(worksheet, string_table, style_table):
    """Write a worksheet to an xml file."""
    if worksheet.xml_source:
        vba_root = fromstring(worksheet.xml_source)
    else:
        vba_root = None
    xml_file = StringIO()
    doc = XMLGenerator(out=xml_file, encoding='utf-8')
    start_tag(doc, 'worksheet',
              {'xmlns': SHEET_MAIN_NS,
               'xmlns:r': REL_NS})
    if vba_root is not None:
        el = vba_root.find('{%s}sheetPr' % SHEET_MAIN_NS)
        if el is not None:
            codename =el.get('codeName', worksheet.title)
            start_tag(doc, 'sheetPr', {"codeName": codename})
        else:
            start_tag(doc, 'sheetPr')
    else:
        start_tag(doc, 'sheetPr')
    tag(doc, 'outlinePr',
        {'summaryBelow': '%d' % (worksheet.show_summary_below),
         'summaryRight': '%d' % (worksheet.show_summary_right)})
    if worksheet.page_setup.fitToPage:
        tag(doc, 'pageSetUpPr', {'fitToPage':'1'})
    end_tag(doc, 'sheetPr')
    tag(doc, 'dimension', {'ref': '%s' % worksheet.calculate_dimension()})
    write_worksheet_sheetviews(doc, worksheet)
    tag(doc, 'sheetFormatPr', {'defaultRowHeight': '15'})
    write_worksheet_cols(doc, worksheet, style_table)
    write_worksheet_data(doc, worksheet, string_table, style_table)
    if worksheet.protection.enabled:
        tag(doc, 'sheetProtection',
            {'objects': '1', 'scenarios': '1', 'sheet': '1'})
    write_worksheet_autofilter(doc, worksheet)
    write_worksheet_mergecells(doc, worksheet)
    write_worksheet_datavalidations(doc, worksheet)
    write_worksheet_hyperlinks(doc, worksheet)
    write_worksheet_conditional_formatting(doc, worksheet)


    options = worksheet.page_setup.options
    if options:
        tag(doc, 'printOptions', options)

    margins = worksheet.page_margins.margins
    if margins:
        tag(doc, 'pageMargins', margins)

    setup = worksheet.page_setup.setup
    if setup:
        tag(doc, 'pageSetup', setup)

    if worksheet.header_footer.hasHeader() or worksheet.header_footer.hasFooter():
        start_tag(doc, 'headerFooter')
        if worksheet.header_footer.hasHeader():
            tag(doc, 'oddHeader', None, worksheet.header_footer.getHeader())
        if worksheet.header_footer.hasFooter():
            tag(doc, 'oddFooter', None, worksheet.header_footer.getFooter())
        end_tag(doc, 'headerFooter')

    if worksheet._charts or worksheet._images:
        tag(doc, 'drawing', {'r:id':'rId1'})

    # If vba is being preserved then add a legacyDrawing element so
    # that any controls can be drawn.
    if vba_root is not None:
        el = vba_root.find('{%s}legacyDrawing' % SHEET_MAIN_NS)
        if el is not None:
            rId = el.get('{%s}id' % REL_NS)
            tag(doc, 'legacyDrawing', {'r:id': rId})

    breaks = worksheet.page_breaks
    if breaks:
        start_tag(doc, 'rowBreaks', {'count': str(len(breaks)), 'manualBreakCount': str(len(breaks))})
        for b in breaks:
            tag(doc, 'brk', {'id': str(b), 'man': 'true', 'max': '16383', 'min': '0'})
        end_tag(doc, 'rowBreaks')

    # add a legacyDrawing so that excel can draw comments
    if worksheet._comment_count > 0:
        tag(doc, 'legacyDrawing', {'r:id':'commentsvml'})

    end_tag(doc, 'worksheet')
    doc.endDocument()
    xml_string = xml_file.getvalue()
    xml_file.close()
    return xml_string
Exemplo n.º 10
0
def write_worksheet(worksheet, string_table, style_table):
    """Write a worksheet to an xml file."""
    if worksheet.xml_source:
        vba_root = fromstring(worksheet.xml_source)
    else:
        vba_root = None
    xml_file = StringIO()
    doc = XMLGenerator(out=xml_file, encoding='utf-8')
    start_tag(doc, 'worksheet', {'xmlns': SHEET_MAIN_NS, 'xmlns:r': REL_NS})
    if vba_root is not None:
        el = vba_root.find('{%s}sheetPr' % SHEET_MAIN_NS)
        if el is not None:
            codename = el.get('codeName', worksheet.title)
            start_tag(doc, 'sheetPr', {"codeName": codename})
        else:
            start_tag(doc, 'sheetPr')
    else:
        start_tag(doc, 'sheetPr')
    tag(
        doc, 'outlinePr', {
            'summaryBelow': '%d' % (worksheet.show_summary_below),
            'summaryRight': '%d' % (worksheet.show_summary_right)
        })
    if worksheet.page_setup.fitToPage:
        tag(doc, 'pageSetUpPr', {'fitToPage': '1'})
    end_tag(doc, 'sheetPr')
    tag(doc, 'dimension', {'ref': '%s' % worksheet.calculate_dimension()})
    write_worksheet_sheetviews(doc, worksheet)
    tag(doc, 'sheetFormatPr', {'defaultRowHeight': '15'})
    write_worksheet_cols(doc, worksheet, style_table)
    write_worksheet_data(doc, worksheet, string_table, style_table)
    if worksheet.protection.enabled:
        tag(doc, 'sheetProtection', {
            'objects': '1',
            'scenarios': '1',
            'sheet': '1'
        })
    write_worksheet_autofilter(doc, worksheet)
    write_worksheet_mergecells(doc, worksheet)
    write_worksheet_datavalidations(doc, worksheet)
    write_worksheet_hyperlinks(doc, worksheet)
    write_worksheet_conditional_formatting(doc, worksheet)

    options = worksheet.page_setup.options
    if options:
        tag(doc, 'printOptions', options)

    margins = worksheet.page_margins.margins
    if margins:
        tag(doc, 'pageMargins', margins)

    setup = worksheet.page_setup.setup
    if setup:
        tag(doc, 'pageSetup', setup)

    if worksheet.header_footer.hasHeader(
    ) or worksheet.header_footer.hasFooter():
        start_tag(doc, 'headerFooter')
        if worksheet.header_footer.hasHeader():
            tag(doc, 'oddHeader', None, worksheet.header_footer.getHeader())
        if worksheet.header_footer.hasFooter():
            tag(doc, 'oddFooter', None, worksheet.header_footer.getFooter())
        end_tag(doc, 'headerFooter')

    if worksheet._charts or worksheet._images:
        tag(doc, 'drawing', {'r:id': 'rId1'})

    # If vba is being preserved then add a legacyDrawing element so
    # that any controls can be drawn.
    if vba_root is not None:
        el = vba_root.find('{%s}legacyDrawing' % SHEET_MAIN_NS)
        if el is not None:
            rId = el.get('{%s}id' % REL_NS)
            tag(doc, 'legacyDrawing', {'r:id': rId})

    breaks = worksheet.page_breaks
    if breaks:
        start_tag(doc, 'rowBreaks', {
            'count': str(len(breaks)),
            'manualBreakCount': str(len(breaks))
        })
        for b in breaks:
            tag(doc, 'brk', {
                'id': str(b),
                'man': 'true',
                'max': '16383',
                'min': '0'
            })
        end_tag(doc, 'rowBreaks')

    # add a legacyDrawing so that excel can draw comments
    if worksheet._comment_count > 0:
        tag(doc, 'legacyDrawing', {'r:id': 'commentsvml'})

    end_tag(doc, 'worksheet')
    doc.endDocument()
    xml_string = xml_file.getvalue()
    xml_file.close()
    return xml_string
    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