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