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
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
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_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
def test_page_margins(worksheet, write_worksheet): ws = worksheet ws.page_margins.left = 2.0 ws.page_margins.right = 2.0 ws.page_margins.top = 2.0 ws.page_margins.bottom = 2.0 ws.page_margins.header = 1.5 ws.page_margins.footer = 1.5 xml = write_worksheet(ws, None) expected = """ <worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> <sheetPr> <outlinePr summaryRight="1" summaryBelow="1"/> </sheetPr> <dimension ref="A1:A1"/> <sheetViews> <sheetView workbookViewId="0"> <selection sqref="A1" activeCell="A1"/> </sheetView> </sheetViews> <sheetFormatPr baseColWidth="10" defaultRowHeight="15"/> <sheetData/> <pageMargins left="2" right="2" top="2" bottom="2" header="1.5" footer="1.5"/> </worksheet> """ diff = compare_xml(xml, expected) assert diff is None, diff
def test_close(DumpWorksheet): ws = DumpWorksheet ws.write_header() header = ws.get_temporary_file(ws._fileobj_header_name) header.write(b"<sheetData>") # well-formed XML needed ws.close() with open(ws.filename) as content: xml = content.read() expected = """ <worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"> <sheetPr> <outlinePr summaryRight="1" summaryBelow="1"/> </sheetPr> <dimension ref="A1:A1"/> <sheetViews> <sheetView workbookViewId="0"> <selection activeCell="A1" sqref="A1"/> </sheetView> </sheetViews> <sheetFormatPr defaultRowHeight="15" baseColWidth="10"/> <sheetData/> </worksheet> """ diff = compare_xml(xml, expected) 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_cell_comment(WriteOnlyWorksheet): ws = WriteOnlyWorksheet from openpyxl.comments import Comment from .. write_only import WriteOnlyCell cell = WriteOnlyCell(ws, 1) comment = Comment('hello', 'me') cell.comment = comment ws.append([cell]) assert ws._comments == [comment] ws.close() with open(ws.filename) as src: xml = src.read() expected = """ <worksheet xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> <sheetPr> <outlinePr summaryRight="1" summaryBelow="1"/> <pageSetUpPr/> </sheetPr> <sheetViews> <sheetView workbookViewId="0"> <selection sqref="A1" activeCell="A1"/> </sheetView> </sheetViews> <sheetFormatPr baseColWidth="10" defaultRowHeight="15"/> <sheetData> <row r="1" spans="1:1"><c r="A1" t="n"><v>1</v></c></row> </sheetData> <legacyDrawing r:id="commentsvml"></legacyDrawing> </worksheet> """ diff = compare_xml(xml, expected) assert diff is None, diff
def test_frozen_panes(WriteOnlyWorksheet): ws = WriteOnlyWorksheet ws.freeze_panes = 'D4' ws.close() with open(ws.filename) as src: xml = src.read() expected = """ <worksheet xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> <sheetPr> <outlinePr summaryRight="1" summaryBelow="1"/> <pageSetUpPr/> </sheetPr> <sheetViews> <sheetView workbookViewId="0"> <pane xSplit="3" ySplit="3" topLeftCell="D4" activePane="bottomRight" state="frozen"/> <selection pane="topRight"/> <selection pane="bottomLeft"/> <selection pane="bottomRight" activeCell="A1" sqref="A1"/> </sheetView> </sheetViews> <sheetFormatPr baseColWidth="10" defaultRowHeight="15"/> <sheetData/> </worksheet> """ diff = compare_xml(xml, expected) assert diff is None, diff
def test_write_string_table(): table = {'hello': 1, 'wor': 2, 'nice': 3} content = write_string_table(table) reference_file = os.path.join(DATADIR, 'writer', 'expected', 'sharedStrings.xml') with open(reference_file) as expected: diff = compare_xml(content, expected.read()) assert diff is None, diff
def test_dirty_cell(WriteOnlyWorksheet): ws = WriteOnlyWorksheet def _writer(doc): with xmlfile(doc) as xf: with xf.element('sheetData'): try: while True: body = (yield) xf.write(body) except GeneratorExit: pass doc = BytesIO() ws.writer = _writer(doc) next(ws.writer) ws.append((datetime.date(2001, 1, 1), 1)) ws.writer.close() xml = doc.getvalue() expected = """ <sheetData> <row r="1" spans="1:2"> <c r="A1" t="n" s="1"><v>36892</v></c> <c r="B1" t="n"><v>1</v></c> </row> </sheetData> """ diff = compare_xml(xml, expected) assert diff is None, diff
def test_write_auto_filter(): wb = Workbook() ws = wb.worksheets[0] ws.cell('F42').value = 'hello' ws.auto_filter = 'A1:F1' content = write_worksheet(ws, {'hello': 0}, {}) reference_file = os.path.join(DATADIR, 'writer', 'expected', 'sheet1_auto_filter.xml') with open(reference_file) as expected: diff = compare_xml(content, expected.read()) assert diff is None content = write_workbook(wb) reference_file = os.path.join(DATADIR, 'writer', 'expected', 'workbook_auto_filter.xml') with open(reference_file) as expected: diff = compare_xml(content, expected.read()) assert diff is None, diff
def test_write_style(self): root = Element("{%s}test" % CHART_DRAWING_NS) self.sw._write_style(root) xml = tostring(root) expected = """<cdr:test xmlns:cdr="http://schemas.openxmlformats.org/drawingml/2006/chartDrawing"><cdr:style><a:lnRef idx="2" xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"><a:schemeClr val="accent1"><a:shade val="50000" /></a:schemeClr></a:lnRef><a:fillRef idx="1" xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"><a:schemeClr val="accent1" /></a:fillRef><a:effectRef idx="0" xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"><a:schemeClr val="accent1" /></a:effectRef><a:fontRef idx="minor" xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"><a:schemeClr val="lt1" /></a:fontRef></cdr:style></cdr:test>""" diff = compare_xml(xml, expected) assert diff is None, diff
def test_write_workbook(datadir): datadir.chdir() wb = Workbook() content = write_workbook(wb) with open('workbook.xml') as expected: diff = compare_xml(content, expected.read()) assert diff is None, diff
def test_write_text(self): root = Element("{%s}test" % CHART_DRAWING_NS) self.sw._write_text(root, self.shape) xml = tostring(root) expected = """<cdr:test xmlns:cdr="http://schemas.openxmlformats.org/drawingml/2006/chartDrawing"><cdr:txBody><a:bodyPr vertOverflow="clip" xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" /><a:lstStyle xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" /><a:p xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"><a:r><a:rPr lang="en-US"><a:solidFill><a:srgbClr val="000000" /></a:solidFill></a:rPr><a:t>My first chart</a:t></a:r></a:p></cdr:txBody></cdr:test>""" diff = compare_xml(xml, expected) assert diff is None, diff
def test_write_chart(self): from openpyxl.drawing import Drawing root = Element("{%s}wsDr" % SHEET_DRAWING_NS) chart = DummyChart() drawing = Drawing() chart.drawing = drawing self.dw._write_chart(root, chart, 1) drawing_schema.assertValid(root) xml = tostring(root) expected = """<xdr:wsDr xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:c="http://schemas.openxmlformats.org/drawingml/2006/chart"> <xdr:absoluteAnchor> <xdr:pos x="0" y="0"/> <xdr:ext cx="200025" cy="1828800"/> <xdr:graphicFrame macro=""> <xdr:nvGraphicFramePr> <xdr:cNvPr id="2" name="Chart 1"/> <xdr:cNvGraphicFramePr/> </xdr:nvGraphicFramePr> <xdr:xfrm> <a:off x="0" y="0"/> <a:ext cx="0" cy="0"/> </xdr:xfrm> <a:graphic> <a:graphicData uri="http://schemas.openxmlformats.org/drawingml/2006/chart"> <c:chart xmlns:c="http://schemas.openxmlformats.org/drawingml/2006/chart" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" r:id="rId1"/> </a:graphicData> </a:graphic> </xdr:graphicFrame> <xdr:clientData/> </xdr:absoluteAnchor> </xdr:wsDr>""" diff = compare_xml(xml, expected) assert diff is None, diff
def test_write_chart(self, scatter_chart): cw = ScatterChartWriter(scatter_chart) cw._write_chart() xml = tostring(cw.root) expected = """<c:chartSpace xmlns:c="http://schemas.openxmlformats.org/drawingml/2006/chart"><c:chart><c:plotArea><c:layout><c:manualLayout><c:layoutTarget val="inner" /><c:xMode val="edge" /><c:yMode val="edge" /><c:x val="0.03375" /><c:y val="0.31" /><c:w val="0.6" /><c:h val="0.6" /></c:manualLayout></c:layout><c:scatterChart><c:scatterStyle val="lineMarker" /><c:ser><c:idx val="0" /><c:order val="0" /><c:xVal><c:numRef><c:f>\'Scatter\'!$B$1:$B$11</c:f><c:numCache><c:formatCode>General</c:formatCode><c:ptCount val="11" /><c:pt idx="0"><c:v>0</c:v></c:pt><c:pt idx="1"><c:v>1</c:v></c:pt><c:pt idx="2"><c:v>2</c:v></c:pt><c:pt idx="3"><c:v>3</c:v></c:pt><c:pt idx="4"><c:v>4</c:v></c:pt><c:pt idx="5"><c:v>5</c:v></c:pt><c:pt idx="6"><c:v>6</c:v></c:pt><c:pt idx="7"><c:v>7</c:v></c:pt><c:pt idx="8"><c:v>8</c:v></c:pt><c:pt idx="9"><c:v>9</c:v></c:pt><c:pt idx="10"><c:v></c:v></c:pt></c:numCache></c:numRef></c:xVal><c:yVal><c:numRef><c:f>\'Scatter\'!$A$1:$A$11</c:f><c:numCache><c:formatCode>General</c:formatCode><c:ptCount val="11" /><c:pt idx="0"><c:v>0</c:v></c:pt><c:pt idx="1"><c:v>1</c:v></c:pt><c:pt idx="2"><c:v>2</c:v></c:pt><c:pt idx="3"><c:v>3</c:v></c:pt><c:pt idx="4"><c:v>4</c:v></c:pt><c:pt idx="5"><c:v>5</c:v></c:pt><c:pt idx="6"><c:v>6</c:v></c:pt><c:pt idx="7"><c:v>7</c:v></c:pt><c:pt idx="8"><c:v>8</c:v></c:pt><c:pt idx="9"><c:v>9</c:v></c:pt><c:pt idx="10"><c:v></c:v></c:pt></c:numCache></c:numRef></c:yVal></c:ser><c:axId val="60871424" /><c:axId val="60873344" /></c:scatterChart><c:valAx><c:axId val="60871424" /><c:scaling><c:orientation val="minMax" /><c:max val="10.0" /><c:min val="0.0" /></c:scaling><c:axPos val="b" /><c:majorGridlines /><c:numFmt formatCode="General" sourceLinked="1" /><c:tickLblPos val="nextTo" /><c:crossAx val="60873344" /><c:crosses val="autoZero" /><c:auto val="1" /><c:lblAlgn val="ctr" /><c:lblOffset val="100" /><c:crossBetween val="midCat" /><c:majorUnit val="2" /></c:valAx><c:valAx><c:axId val="60873344" /><c:scaling><c:orientation val="minMax" /><c:max val="10.0" /><c:min val="0.0" /></c:scaling><c:axPos val="l" /><c:majorGridlines /><c:numFmt formatCode="General" sourceLinked="1" /><c:tickLblPos val="nextTo" /><c:crossAx val="60871424" /><c:crosses val="autoZero" /><c:crossBetween val="midCat" /><c:majorUnit val="2" /></c:valAx></c:plotArea><c:legend><c:legendPos val="r" /><c:layout /></c:legend><c:plotVisOnly val="1" /></c:chart></c:chartSpace>""" diff = compare_xml(xml, expected) assert diff is None, diff
def test_printer_settings(worksheet, write_worksheet): ws = worksheet ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE ws.page_setup.paperSize = ws.PAPERSIZE_TABLOID ws.page_setup.fitToPage = True ws.page_setup.fitToHeight = 0 ws.page_setup.fitToWidth = 1 ws.page_setup.horizontalCentered = True ws.page_setup.verticalCentered = True xml = write_worksheet(ws, None) expected = """ <worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> <sheetPr> <outlinePr summaryRight="1" summaryBelow="1"/> <pageSetUpPr fitToPage="1"/> </sheetPr> <dimension ref="A1:A1"/> <sheetViews> <sheetView workbookViewId="0"> <selection sqref="A1" activeCell="A1"/> </sheetView> </sheetViews> <sheetFormatPr baseColWidth="10" defaultRowHeight="15"/> <sheetData/> <printOptions horizontalCentered="1" verticalCentered="1"/> <pageMargins left="0.75" right="0.75" top="1" bottom="1" header="0.5" footer="0.5"/> <pageSetup orientation="landscape" paperSize="3" fitToHeight="0" fitToWidth="1"/> </worksheet> """ diff = compare_xml(xml, expected) assert diff is None, diff
def test_write_workbook(): wb = Workbook() content = write_workbook(wb) reference_file = os.path.join(DATADIR, 'writer', 'expected', 'workbook.xml') with open(reference_file) as expected: diff = compare_xml(content, expected.read()) 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) cfs = write_conditional_formatting(worksheet) xml = b"" for cf in cfs: xml += tostring(cf) 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_ctor(self, SortCondition): cond = SortCondition(ref='A2:A3', descending=True) expected = """ <sortCondtion descending="1" ref="A2:A3"></sortCondtion> """ xml = tostring(cond.to_tree()) diff = compare_xml(xml, expected)
def test_from_tree(self): from ..series import Series, attribute_mapping src = """ <ser> <idx val="0"/> <order val="0"/> <spPr> <a:ln xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"> <a:prstDash val="solid" /> </a:ln> </spPr> <val> <numRef> <f>Blatt1!$A$1:$A$12</f> </numRef> </val> </ser> """ node = fromstring(src) ser = Series.from_tree(node) assert ser.idx == 0 assert ser.order == 0 assert ser.val.numRef.ref == 'Blatt1!$A$1:$A$12' ser.__elements__ = attribute_mapping['area'] xml = tostring(ser.to_tree()) diff = compare_xml(xml, src) assert diff is None, diff
def test_ctor(self, ComplexObject): style = ComplexObject() ft1 = Font() ft2 = Font(bold=True) style.fonts = [ft1, ft2] expected = """ <style> <fonts count="2"> <font> <name val="Calibri"></name> <family val="2"></family> <color rgb="00000000"></color> <sz val="11"></sz> </font> <font> <name val="Calibri"></name> <family val="2"></family> <b val="1"></b> <color rgb="00000000"></color> <sz val="11"></sz> </font> </fonts> </style> """ tree = style.__class__.fonts.to_tree('fonts', style.fonts) tree = style.to_tree() xml = tostring(tree) diff = compare_xml(xml, expected) assert diff is None, diff
def test_write_comments(worksheet, write_worksheet): ws = worksheet worksheet._comment_count = 1 xml = write_worksheet(ws, None) expected = """ <worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"> <sheetPr> <outlinePr summaryBelow="1" summaryRight="1"/> <pageSetUpPr/> </sheetPr> <dimension ref="A1:A1"/> <sheetViews> <sheetView workbookViewId="0"> <selection activeCell="A1" sqref="A1"/> </sheetView> </sheetViews> <sheetFormatPr baseColWidth="10" defaultRowHeight="15"/> <sheetData/> <pageMargins bottom="1" footer="0.5" header="0.5" left="0.75" right="0.75" top="1"/> <legacyDrawing r:id="commentsvml"></legacyDrawing> </worksheet> """ diff = compare_xml(xml, expected) assert diff is None, diff
def test_lots_cols(write_cols, ColumnDimension, DummyWorksheet): from openpyxl.styles import Font ws = DummyWorksheet from openpyxl.cell import get_column_letter for i in range(1, 15): label = get_column_letter(i) cd = ColumnDimension(worksheet=ws) cd.font = Font(name=label) dict(cd) # create style_id in order for test ws.column_dimensions[label] = cd cols = write_cols(ws) xml = tostring(cols) expected = """<cols> <col max="1" min="1" style="1"></col> <col max="2" min="2" style="2"></col> <col max="3" min="3" style="3"></col> <col max="4" min="4" style="4"></col> <col max="5" min="5" style="5"></col> <col max="6" min="6" style="6"></col> <col max="7" min="7" style="7"></col> <col max="8" min="8" style="8"></col> <col max="9" min="9" style="9"></col> <col max="10" min="10" style="10"></col> <col max="11" min="11" style="11"></col> <col max="12" min="12" style="12"></col> <col max="13" min="13" style="13"></col> <col max="14" min="14" style="14"></col> </cols> """ diff = compare_xml(xml, expected) assert diff is None, diff
def test_header_footer(worksheet): ws = worksheet ws.header_footer.left_header.text = "Left Header Text" ws.header_footer.center_header.text = "Center Header Text" ws.header_footer.center_header.font_name = "Arial,Regular" ws.header_footer.center_header.font_size = 6 ws.header_footer.center_header.font_color = "445566" ws.header_footer.right_header.text = "Right Header Text" ws.header_footer.right_header.font_name = "Arial,Bold" ws.header_footer.right_header.font_size = 8 ws.header_footer.right_header.font_color = "112233" ws.header_footer.left_footer.text = "Left Footer Text\nAnd &[Date] and &[Time]" ws.header_footer.left_footer.font_name = "Times New Roman,Regular" ws.header_footer.left_footer.font_size = 10 ws.header_footer.left_footer.font_color = "445566" ws.header_footer.center_footer.text = "Center Footer Text &[Path]&[File] on &[Tab]" ws.header_footer.center_footer.font_name = "Times New Roman,Bold" ws.header_footer.center_footer.font_size = 12 ws.header_footer.center_footer.font_color = "778899" ws.header_footer.right_footer.text = "Right Footer Text &[Page] of &[Pages]" ws.header_footer.right_footer.font_name = "Times New Roman,Italic" ws.header_footer.right_footer.font_size = 14 ws.header_footer.right_footer.font_color = "AABBCC" from .. worksheet import write_header_footer hf = write_header_footer(ws) xml = tostring(hf) expected = """ <headerFooter> <oddHeader>&L&"Calibri,Regular"&K000000Left Header Text&C&"Arial,Regular"&6&K445566Center Header Text&R&"Arial,Bold"&8&K112233Right Header Text</oddHeader> <oddFooter>&L&"Times New Roman,Regular"&10&K445566Left Footer Text_x000D_And &D and &T&C&"Times New Roman,Bold"&12&K778899Center Footer Text &Z&F on &A&R&"Times New Roman,Italic"&14&KAABBCCRight Footer Text &P of &N</oddFooter> </headerFooter> """ diff = compare_xml(xml, expected) assert diff is None, diff
def test_conditional_font(worksheet_with_cf, write_conditional_formatting): """Test to verify font style written correctly.""" # Create cf rule from openpyxl.styles import PatternFill, Font, Color from openpyxl.formatting.rule import CellIsRule redFill = PatternFill(start_color=Color('FFEE1111'), end_color=Color('FFEE1111'), patternType='solid') whiteFont = Font(color=Color("FFFFFFFF")) ws = worksheet_with_cf ws.conditional_formatting.add('A1:A3', CellIsRule(operator='equal', formula=['"Fail"'], stopIfTrue=False, font=whiteFont, fill=redFill)) cfs = write_conditional_formatting(ws) xml = b"" for cf in cfs: xml += tostring(cf) diff = compare_xml(xml, """ <conditionalFormatting sqref="A1:A3"> <cfRule operator="equal" priority="1" type="cellIs" dxfId="0" stopIfTrue="0"> <formula>"Fail"</formula> </cfRule> </conditionalFormatting> """) assert diff is None, diff
def test_title_maker(): """ Create a title element from a string preserving line breaks. """ from ..title import title_maker text = "Two-line\nText" title = title_maker(text) xml = tostring(title.to_tree()) expected = """ <title xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"> <tx> <rich> <a:bodyPr /> <a:p> <a:r> <a:t>Two-line</a:t> </a:r> </a:p> <a:p> <a:r> <a:t>Text</a:t> </a:r> </a:p> </rich> </tx> </title> """ 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._save_styles(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
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 = BytesIO() doc = XMLGenerator(out=temp_buffer) 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_ctor(self, StockChart): from openpyxl.chart.series import Series chart = StockChart(ser=[Series(), Series(), Series()]) xml = tostring(chart.to_tree()) expected = """ <stockChart xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"> <ser> <idx val="0" /> <order val="0" /> <spPr> <a:ln > <a:prstDash val="solid" /> </a:ln> </spPr> <marker> <symbol val="none"/> <spPr> <a:ln> <a:prstDash val="solid" /> </a:ln> </spPr> </marker> </ser> <ser> <idx val="1" /> <order val="1" /> <spPr> <a:ln> <a:prstDash val="solid" /> </a:ln> </spPr> <marker> <symbol val="none"/> <spPr> <a:ln> <a:prstDash val="solid" /> </a:ln> </spPr> </marker> </ser> <ser> <idx val="2"></idx> <order val="2"></order> <spPr> <a:ln> <a:prstDash val="solid" /> </a:ln> </spPr> <marker> <symbol val="none"/> <spPr> <a:ln> <a:prstDash val="solid" /> </a:ln> </spPr> </marker> </ser> <axId val="10"></axId> <axId val="100"></axId> </stockChart> """ diff = compare_xml(xml, expected) assert diff is None, diff
def test_write(self, AreaChart): s1 = Series(values="Sheet1!$A$1:$A$12") s2 = Series(values="Sheet1!$B$1:$B$12") chart = AreaChart(ser=[s1, s2]) xml = tostring(chart._write()) expected = """ <chartSpace xmlns="http://schemas.openxmlformats.org/drawingml/2006/chart"> <chart> <plotArea> <areaChart> <grouping val="standard"></grouping> <ser> <idx val="0"></idx> <order val="0"></order> <spPr> <a:ln xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"> <a:prstDash val="solid" /> </a:ln> </spPr> <val> <numRef> <f>Sheet1!$A$1:$A$12</f> </numRef> </val> </ser> <ser> <idx val="1"></idx> <order val="1"></order> <spPr> <a:ln xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"> <a:prstDash val="solid" /> </a:ln> </spPr> <val> <numRef> <f>Sheet1!$B$1:$B$12</f> </numRef> </val> </ser> <axId val="10"></axId> <axId val="100"></axId> </areaChart> <valAx> <axId val="100"></axId> <scaling> <orientation val="minMax"></orientation> </scaling> <axPos val="l" /> <majorGridlines/> <crossAx val="10"></crossAx> </valAx> <catAx> <axId val="10"></axId> <scaling> <orientation val="minMax"></orientation> </scaling> <axPos val="l" /> <crossAx val="100"></crossAx> <lblOffset val="100"></lblOffset> </catAx> </plotArea> <legend> <legendPos val="r"></legendPos> </legend> <dispBlanksAs val="gap"></dispBlanksAs> </chart> </chartSpace> """ diff = compare_xml(xml, expected) 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 = 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="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_ctor(self): from ..layout import Layout layout = Layout() xml = tostring(layout.to_tree()) diff = compare_xml(xml, "<layout />") assert diff is None, diff
def test_from_tree(self): from ..series import Series, attribute_mapping src = """ <ser> <idx val="0"/> <order val="0"/> <spPr> <a:ln xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"> <a:prstDash val="solid" /> </a:ln> </spPr> <xVal> <numRef> <f>Blatt1!$A$1:$A$12</f> </numRef> </xVal> <yVal> <numRef> <f>Blatt1!$B$1:$B$12</f> </numRef> </yVal> <bubbleSize> <numLit> <formatCode>General</formatCode> <ptCount val="12"/> <pt idx="0"> <v>1.1</v> </pt> <pt idx="1"> <v>1.1</v> </pt> <pt idx="2"> <v>1.1</v> </pt> <pt idx="3"> <v>1.1</v> </pt> <pt idx="4"> <v>1.1</v> </pt> <pt idx="5"> <v>1.1</v> </pt> <pt idx="6"> <v>1.1</v> </pt> <pt idx="7"> <v>1.1</v> </pt> <pt idx="8"> <v>1.1</v> </pt> <pt idx="9"> <v>1.1</v> </pt> <pt idx="10"> <v>1.1</v> </pt> <pt idx="11"> <v>1.1</v> </pt> </numLit> </bubbleSize> </ser> """ node = fromstring(src) ser = Series.from_tree(node) assert ser.idx == 0 assert ser.order == 0 assert ser.xVal.numRef.ref == 'Blatt1!$A$1:$A$12' assert ser.yVal.numRef.ref == 'Blatt1!$B$1:$B$12' assert ser.bubbleSize.numLit.ptCount == 12 assert ser.bubbleSize.numLit.pt[0].v == 1.1 ser.__elements__ = attribute_mapping['bubble'] xml = tostring(ser.to_tree()) diff = compare_xml(xml, src) assert diff is None, diff
def test_write_style_table(self, datadir): datadir.chdir() with open('simple-styles.xml') as reference_file: xml = self.writer.write_table() diff = compare_xml(xml, reference_file.read()) assert diff is None, diff
def test_write(self): xml = self.sw.write(0) tree = fromstring(xml) chart_schema.assertValid(tree) expected = """ <c:userShapes xmlns:c="http://schemas.openxmlformats.org/drawingml/2006/chart"> <cdr:relSizeAnchor xmlns:cdr="http://schemas.openxmlformats.org/drawingml/2006/chartDrawing"> <cdr:from> <cdr:x>1</cdr:x> <cdr:y>1</cdr:y> </cdr:from> <cdr:to> <cdr:x>1</cdr:x> <cdr:y>1</cdr:y> </cdr:to> <cdr:sp macro="" textlink=""> <cdr:nvSpPr> <cdr:cNvPr id="0" name="shape 0" /> <cdr:cNvSpPr /> </cdr:nvSpPr> <cdr:spPr> <a:xfrm xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"> <a:off x="0" y="0" /> <a:ext cx="0" cy="0" /> </a:xfrm> <a:prstGeom prst="rect" xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"> <a:avLst /> </a:prstGeom> <a:solidFill xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"> <a:srgbClr val="FFFFFF" /> </a:solidFill> <a:ln w="0" xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"> <a:solidFill> <a:srgbClr val="000000" /> </a:solidFill> </a:ln> </cdr:spPr> <cdr:style> <a:lnRef idx="2" xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"> <a:schemeClr val="accent1"> <a:shade val="50000" /> </a:schemeClr> </a:lnRef> <a:fillRef idx="1" xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"> <a:schemeClr val="accent1" /> </a:fillRef> <a:effectRef idx="0" xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"> <a:schemeClr val="accent1" /> </a:effectRef> <a:fontRef idx="minor" xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"> <a:schemeClr val="lt1" /> </a:fontRef> </cdr:style> <cdr:txBody> <a:bodyPr vertOverflow="clip" xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" /> <a:lstStyle xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" /> <a:p xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"> <a:r> <a:rPr lang="en-US"> <a:solidFill> <a:srgbClr val="000000" /> </a:solidFill> </a:rPr> <a:t>My first chart</a:t> </a:r> </a:p> </cdr:txBody> </cdr:sp> </cdr:relSizeAnchor> </c:userShapes> """ diff = compare_xml(xml, expected) assert diff is None, diff
def test_serialise(self, FormatObject): cfvo = FormatObject(type="percent", val=4) xml = tostring(cfvo.to_tree()) expected = """<cfvo type="percent" val="4"/>""" diff = compare_xml(xml, expected) assert diff is None, diff
def test_no_colors(self, ColorList): colors = ColorList() xml = tostring(colors.to_tree()) expected = """<colors/>""" diff = compare_xml(xml, expected) assert diff is None, diff
def test_write(self): xml = self.dw.write() expected = """<xdr:wsDr xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing"> </xdr:wsDr>""" diff = compare_xml(xml, expected) assert diff is None, diff