def write_data(self):
"""Write the various xml files into the zip archive."""
# cleanup all worksheets
archive = self._archive
archive.writestr(ARC_ROOT_RELS, write_root_rels(self.workbook))
props = ExtendedProperties()
archive.writestr(ARC_APP, tostring(props.to_tree()))
archive.writestr(ARC_CORE, tostring(self.workbook.properties.to_tree()))
if self.workbook.loaded_theme:
archive.writestr(ARC_THEME, self.workbook.loaded_theme)
else:
archive.writestr(ARC_THEME, write_theme())
self._write_worksheets()
self._write_chartsheets()
self._write_images()
self._write_charts()
self._archive.writestr(ARC_SHARED_STRINGS,
write_string_table(self.workbook.shared_strings))
self._write_external_links()
stylesheet = write_stylesheet(self.workbook)
archive.writestr(ARC_STYLE, tostring(stylesheet))
archive.writestr(ARC_WORKBOOK, write_workbook(self.workbook))
archive.writestr(ARC_WORKBOOK_RELS, write_workbook_rels(self.workbook))
self._merge_vba()
self.manifest._write(archive, self.workbook)
def write_data(self, archive, as_template=False):
"""Write the various xml files into the zip archive."""
# cleanup all worksheets
archive.writestr(ARC_ROOT_RELS, write_root_rels(self.workbook))
archive.writestr(ARC_WORKBOOK_RELS, write_workbook_rels(self.workbook))
archive.writestr(ARC_APP, write_properties_app(self.workbook))
archive.writestr(ARC_CORE, tostring(self.workbook.properties.to_tree()))
if self.workbook.loaded_theme:
archive.writestr(ARC_THEME, self.workbook.loaded_theme)
else:
archive.writestr(ARC_THEME, write_theme())
archive.writestr(ARC_WORKBOOK, write_workbook(self.workbook))
if self.workbook.vba_archive:
vba_archive = self.workbook.vba_archive
for name in vba_archive.namelist():
for s in ARC_VBA:
if match(s, name):
archive.writestr(name, vba_archive.read(name))
break
self._write_charts(archive)
self._write_images(archive)
self._write_worksheets(archive)
self._write_chartsheets(archive)
self._write_string_table(archive)
self._write_external_links(archive)
stylesheet = write_stylesheet(self.workbook)
archive.writestr(ARC_STYLE, tostring(stylesheet))
manifest = write_content_types(self.workbook, as_template=as_template)
archive.writestr(ARC_CONTENT_TYPES, tostring(manifest.to_tree()))
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
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_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 write_header(self):
fobj = self.get_temporary_file(filename=self._fileobj_header_name)
doc = XMLGenerator(fobj)
start_tag(doc, 'worksheet',
{
'xmlns': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main',
'xmlns:r': 'http://schemas.openxmlformats.org/officeDocument/2006/relationships'})
start_tag(doc, 'sheetPr')
tag(doc, 'outlinePr',
{'summaryBelow': '1',
'summaryRight': '1'})
end_tag(doc, 'sheetPr')
tag(doc, 'dimension', {'ref': 'A1:%s' % (self.get_dimensions())})
start_tag(doc, 'sheetViews')
start_tag(doc, 'sheetView', {'workbookViewId': '0'})
tag(doc, 'selection', {'activeCell': 'A1',
'sqref': 'A1'})
end_tag(doc, 'sheetView')
end_tag(doc, 'sheetViews')
fmt = write_format(self)
fobj.write(tostring(fmt))
cols = write_cols(self)
if cols is not None:
fobj.write(tostring(cols))
return doc
def _write_chartsheets(self, archive):
from openpyxl.packaging.relationship import Relationship, RelationshipList
from openpyxl.worksheet.drawing import Drawing
for idx, sheet in enumerate(self.workbook.chartsheets, 1):
if sheet._charts:
drawing = SpreadsheetDrawing()
drawing.charts = sheet._charts
self.workbook._drawings.append(drawing)
drawing_id = len(self.workbook._drawings)
drawingpath = "{0}/drawing{1}.xml".format(PACKAGE_DRAWINGS, drawing_id)
archive.writestr(drawingpath, tostring(drawing._write()))
archive.writestr(
"{0}/_rels/drawing{1}.xml.rels".format(
PACKAGE_DRAWINGS, drawing_id),
tostring(drawing._write_rels())
)
rel = Relationship(type="drawing", target="/" + drawingpath)
rels = RelationshipList()
rels.append(rel)
tree = rels.to_tree()
sheet.drawing.id = "rId{0}".format(len(rels))
archive.writestr(PACKAGE_CHARTSHEETS +
'/_rels/sheet%d.xml.rels' % idx, tostring(tree)
)
xml = tostring(sheet.to_tree())
archive.writestr(PACKAGE_CHARTSHEETS + '/sheet%d.xml' % idx, xml)
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 _write_external_links(self, archive):
"""Write links to external workbooks"""
wb = self.workbook
for idx, book in enumerate(wb._external_links, 1):
el = write_external_link(book.links)
rel = write_external_book_rel(book)
archive.writestr(
"{0}/externalLinks/externalLink{1}.xml".format(PACKAGE_XL, idx),
tostring(el)
)
archive.writestr(
"{0}/externalLinks/_rels/externalLink{1}.xml.rels".format(PACKAGE_XL, idx),
tostring(rel)
)
def _write_external_links(self):
# delegate to object
"""Write links to external workbooks"""
wb = self.workbook
for idx, link in enumerate(wb._external_links, 1):
link._id = idx
rels_path = get_rels_path(link.path[1:])
xml = link.to_tree()
self._archive.writestr(link.path[1:], tostring(xml))
rels = RelationshipList()
rels.append(link.file_link)
self._archive.writestr(rels_path, tostring(rels.to_tree()))
self.manifest.append(link)
def _write_external_links(self, archive):
"""Write links to external workbooks"""
wb = self.workbook
for idx, link in enumerate(wb._external_links, 1):
link._path = "{0}{1}.xml".format(link._rel_type, idx)
arc_path = "{0}/{1}s/{2}".format(PACKAGE_XL, link._rel_type, link._path)
rels_path = get_rels_path(arc_path)
xml = link.to_tree()
archive.writestr(arc_path, tostring(xml))
rels = RelationshipList()
rels.append(link.file_link)
archive.writestr(rels_path, tostring(rels.to_tree()))
def test_write_legend(self, scatter_chart, root_xml):
cw = ScatterChartWriter(scatter_chart)
cw._write_legend(root_xml)
expected = """<test xmlns:c="http://schemas.openxmlformats.org/drawingml/2006/chart"><c:legend><c:legendPos val="r" /><c:layout /></c:legend></test>"""
xml = tostring(root_xml)
diff = compare_xml(xml, expected)
assert diff is None, diff
def _write_charts(self, archive):
for idx, ref in enumerate(self.workbook._charts, 1):
chart = ref()
if not chart:
continue
chart._id = idx
archive.writestr(chart._path, tostring(chart._write()))
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(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_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_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, 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_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_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_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_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_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_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, 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 write_root_rels(workbook):
"""Write the relationships xml."""
rels = RelationshipList()
rel = Relationship(type="officeDocument", target=ARC_WORKBOOK, id="rId1")
rels.append(rel)
rel = Relationship("", target=ARC_CORE, id='rId2',)
rel.type = "%s/metadata/core-properties" % PKG_REL_NS
rels.append(rel)
rel = Relationship("extended-properties", target=ARC_APP, id='rId3')
rels.append(rel)
if workbook.vba_archive is not None:
relation_tag = '{%s}Relationship' % PKG_REL_NS
# See if there was a customUI relation and reuse its id
arc = fromstring(workbook.vba_archive.read(ARC_ROOT_RELS))
rel_tags = arc.findall(relation_tag)
rId = None
for rel in rel_tags:
if rel.get('Target') == ARC_CUSTOM_UI:
rId = rel.get('Id')
break
if rId is not None:
vba = Relationship("", target=ARC_CUSTOM_UI, id=rId)
vba.type = CUSTOMUI_NS
rels.append(vba)
return tostring(rels.to_tree())
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 write_properties_app(workbook):
"""Write the properties xml."""
worksheets_count = len(workbook.worksheets)
root = Element('{%s}Properties' % XPROPS_NS)
SubElement(root, '{%s}Application' % XPROPS_NS).text = 'Microsoft Excel'
SubElement(root, '{%s}DocSecurity' % XPROPS_NS).text = '0'
SubElement(root, '{%s}ScaleCrop' % XPROPS_NS).text = 'false'
SubElement(root, '{%s}Company' % XPROPS_NS)
SubElement(root, '{%s}LinksUpToDate' % XPROPS_NS).text = 'false'
SubElement(root, '{%s}SharedDoc' % XPROPS_NS).text = 'false'
SubElement(root, '{%s}HyperlinksChanged' % XPROPS_NS).text = 'false'
SubElement(root, '{%s}AppVersion' % XPROPS_NS).text = '12.0000'
# heading pairs part
heading_pairs = SubElement(root, '{%s}HeadingPairs' % XPROPS_NS)
vector = SubElement(heading_pairs, '{%s}vector' % VTYPES_NS,
{'size': '2', 'baseType': 'variant'})
variant = SubElement(vector, '{%s}variant' % VTYPES_NS)
SubElement(variant, '{%s}lpstr' % VTYPES_NS).text = 'Worksheets'
variant = SubElement(vector, '{%s}variant' % VTYPES_NS)
SubElement(variant, '{%s}i4' % VTYPES_NS).text = '%d' % worksheets_count
# title of parts
title_of_parts = SubElement(root, '{%s}TitlesOfParts' % XPROPS_NS)
vector = SubElement(title_of_parts, '{%s}vector' % VTYPES_NS,
{'size': '%d' % worksheets_count, 'baseType': 'lpstr'})
for ws in workbook.worksheets:
SubElement(vector, '{%s}lpstr' % VTYPES_NS).text = '%s' % ws.title
return tostring(root)
def write_workbook(workbook):
"""Write the core workbook xml."""
root = Element('{%s}workbook' % SHEET_MAIN_NS)
if LXML:
_nsmap = {'r':REL_NS}
root = Element('{%s}workbook' % SHEET_MAIN_NS, nsmap=_nsmap)
wb_props = {}
if workbook.code_name is not None:
wb_props['codeName'] = workbook.code_name
SubElement(root, '{%s}workbookPr' % SHEET_MAIN_NS, wb_props)
# book views
book_views = SubElement(root, '{%s}bookViews' % SHEET_MAIN_NS)
SubElement(book_views, '{%s}workbookView' % SHEET_MAIN_NS,
{'activeTab': '%d' % workbook._active_sheet_index}
)
# worksheets
sheets = SubElement(root, '{%s}sheets' % SHEET_MAIN_NS)
for idx, sheet in enumerate(workbook.worksheets + workbook.chartsheets, 1):
sheet_node = SubElement(
sheets, '{%s}sheet' % SHEET_MAIN_NS,
{'name': sheet.title, 'sheetId': '%d' % idx,
'{%s}id' % REL_NS: 'rId%d' % idx})
if not sheet.sheet_state == 'visible':
if len(workbook._sheets) == 1:
raise ValueError("The only worksheet of a workbook cannot be hidden")
sheet_node.set('state', sheet.sheet_state)
# external references
if getattr(workbook, '_external_links', []):
external_references = SubElement(root, '{%s}externalReferences' % SHEET_MAIN_NS)
# need to match a counter with a workbook's relations
counter = len(workbook.worksheets) + 3 # strings, styles, theme
if workbook.vba_archive:
counter += 1
for idx, _ in enumerate(workbook._external_links, counter+1):
ext = Element("{%s}externalReference" % SHEET_MAIN_NS, {"{%s}id" % REL_NS:"rId%d" % idx})
external_references.append(ext)
# Defined names
defined_names = SubElement(root, '{%s}definedNames' % SHEET_MAIN_NS)
_write_defined_names(workbook, defined_names)
# Defined names -> autoFilter
for i, sheet in enumerate(workbook.worksheets):
auto_filter = sheet.auto_filter.ref
if not auto_filter:
continue
name = SubElement(
defined_names, '{%s}definedName' % SHEET_MAIN_NS,
dict(name='_xlnm._FilterDatabase', localSheetId=str(i), hidden='1'))
name.text = "'%s'!%s" % (sheet.title.replace("'", "''"),
absolute_coordinate(auto_filter))
SubElement(root, '{%s}calcPr' % SHEET_MAIN_NS,
{'calcId': '124519', 'fullCalcOnLoad': '1'})
return tostring(root)
def _write_worksheets(self, archive):
drawing_id = 1
comments_id = 1
for i, sheet in enumerate(self.workbook.worksheets, 1):
header_doc = sheet.write_header() # written after worksheet body to include dimensions
start_tag(header_doc, 'sheetData')
sheet.close()
archive.write(sheet.filename, PACKAGE_WORKSHEETS + '/sheet%d.xml' % i)
sheet._cleanup()
# write comments
if sheet._comments:
rels = write_rels(sheet, drawing_id, comments_id)
archive.writestr( PACKAGE_WORKSHEETS +
'/_rels/sheet%d.xml.rels' % i, tostring(rels) )
cw = DumpCommentWriter(sheet)
archive.writestr(PACKAGE_XL + '/comments%d.xml' % comments_id,
cw.write_comments())
archive.writestr(PACKAGE_XL + '/drawings/commentsDrawing%d.vml' % comments_id,
cw.write_comments_vml())
comments_id += 1
def test_title_from_data(self, Series):
series = Series("Sheet1!A1:A10", title_from_data=True)
series.__elements__ = ('tx', 'val')
xml = tostring(series.to_tree(idx=0))
expected = """
<ser>
<tx>
<strRef>
<f>'Sheet1'!A1</f>
</strRef>
</tx>
<val>
<numRef>
<f>'Sheet1'!$A$2:$A$10</f>
</numRef>
</val>
</ser>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_write_hyperlink_rels(datadir):
from .. relations import write_rels
wb = Workbook()
ws = wb.active
assert 0 == len(ws.relationships)
ws.cell('A1').value = "test"
ws.cell('A1').hyperlink = "http://test.com/"
assert 1 == len(ws.relationships)
ws.cell('A2').value = "test"
ws.cell('A2').hyperlink = "http://test2.com/"
assert 2 == len(ws.relationships)
el = write_rels(ws, 1, 1, 1)
xml = tostring(el)
datadir.chdir()
with open('sheet1_hyperlink.xml.rels') as expected:
diff = compare_xml(xml, expected.read())
assert diff is None, diff
def test_three_colors(self, ColorScaleRule):
cfRule = ColorScaleRule(start_type='percentile', start_value=10,
start_color='FFAA0000', mid_type='percentile', mid_value=50,
mid_color='FF0000AA', end_type='percentile', end_value=90,
end_color='FF00AA00')
xml = tostring(cfRule.to_tree())
expected = """
<cfRule priority="0" type="colorScale">
<colorScale>
<cfvo type="percentile" val="10"></cfvo>
<cfvo type="percentile" val="50"></cfvo>
<cfvo type="percentile" val="90"></cfvo>
<color rgb="FFAA0000"></color>
<color rgb="FF0000AA"></color>
<color rgb="FF00AA00"></color>
</colorScale>
</cfRule>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_xfs_borders(self):
cell = self.worksheet.cell('A1')
cell.border=Border(top=Side(border_style=borders.BORDER_THIN,
color=Color(colors.DARKYELLOW)))
_ = cell.style_id # update workbook styles
w = StyleWriter(self.workbook)
w._write_cell_styles()
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 borderId="1" fillId="0" fontId="0" numFmtId="0" xfId="0"/>
</cellXfs>
</styleSheet>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def write(self, root):
if not hasattr(root, "findall"):
root = Element("xml")
# Remove any existing comment shapes
comments = root.findall("{%s}shape[@type='#_x0000_t202']" % vmlns)
for c in comments:
root.remove(c)
# check whether comments shape type already exists
shape_types = root.find("{%s}shapetype[@id='_x0000_t202']" % vmlns)
if not shape_types:
self.add_comment_shapetype(root)
for idx, (coord, comment) in enumerate(self.comments, 1026):
self.add_comment_shape(root, idx, coord, comment.height,
comment.width)
return tostring(root)
def test_write_charts(self, Chartsheet):
class DummyChart:
pass
cs = Chartsheet(parent=DummyWorkbook())
cs.add_chart(DummyChart())
expected = """
<chartsheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<sheetViews>
<sheetView workbookViewId="0"></sheetView>
</sheetViews>
<drawing r:id="rId1" />
</chartsheet>
"""
xml = tostring(cs.to_tree())
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_serialise(DifferentialStyle):
cond = DifferentialStyle()
cond.font = Font()
cond.fill = PatternFill()
xml = tostring(cond.to_tree())
expected = """
<dxf>
<font>
<name val="Calibri"></name>
<family val="2"></family>
<color rgb="00000000"></color>
<sz val="11"></sz>
</font>
<fill>
<patternFill />
</fill>
</dxf>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_two_colors(self, ColorScaleRule):
cfRule = ColorScaleRule(start_type='min',
start_value=None,
start_color='FFAA0000',
end_type='max',
end_value=None,
end_color='FF00AA00')
xml = tostring(cfRule.to_tree())
expected = """
<cfRule priority="0" type="colorScale">
<colorScale>
<cfvo type="min"/>
<cfvo type="max"/>
<color rgb="FFAA0000"/>
<color rgb="FF00AA00"/>
</colorScale>
</cfRule>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_serialise(self, IconSet, FormatObject):
fo1 = FormatObject(type="num", val="2")
fo2 = FormatObject(type="num", val="4")
fo3 = FormatObject(type="num", val="6")
fo4 = FormatObject(type="percent", val="0")
icon = IconSet(cfvo=[fo1, fo2, fo3, fo4],
iconSet="4ArrowsGray",
reverse=True,
showValue=False)
xml = tostring(icon.to_tree())
expected = """
<iconSet iconSet="4ArrowsGray" showValue="0" reverse="1">
<cfvo type="num" val="2"/>
<cfvo type="num" val="4"/>
<cfvo type="num" val="6"/>
<cfvo type="percent" val="0"/>
</iconSet>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_protection(self):
cell = self.worksheet.cell('A1')
cell.protection = Protection(locked=True, hidden=True)
_ = cell.style_id
w = StyleWriter(self.workbook)
w._write_cell_styles()
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 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_to_tree(self, ValueSequence):
class Dummy(Serialisable):
tagname = "el"
size = ValueSequence(expected_type=int)
dummy = Dummy()
dummy.size = [1, 2, 3]
xml = tostring(dummy.to_tree())
expected = """
<el>
<size val="1"></size>
<size val="2"></size>
<size val="3"></size>
</el>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_conditional_formatting_customRule(worksheet_with_cf, write_conditional_formatting):
from .. lxml_worksheet import write_conditional_formatting
ws = worksheet_with_cf
ws.conditional_formatting.add('C1:C10', {'type': 'expression', 'formula': ['ISBLANK(C1)'],
'stopIfTrue': '1', 'dxf': {}})
cfs = write_conditional_formatting(ws)
xml = b""
for cf in cfs:
xml += tostring(cf)
diff = compare_xml(xml, """
<conditionalFormatting sqref="C1:C10">
<cfRule type="expression" stopIfTrue="1" priority="1">
<formula>ISBLANK(C1)</formula>
</cfRule>
</conditionalFormatting>
""")
assert diff is None, diff
def write_properties(props):
"""Write the core properties to xml."""
root = Element('{%s}coreProperties' % COREPROPS_NS)
for attr in ("creator", "title", "description", "subject", "identifier",
"language"):
SubElement(root, '{%s}%s' % (DCORE_NS, attr)).text = getattr(props, attr)
for attr in ("created", "modified"):
value = datetime_to_W3CDTF(getattr(props, attr))
SubElement(root, '{%s}%s' % (DCTERMS_NS, attr),
{'{%s}type' % XSI_NS:'%s:W3CDTF' % DCTERMS_PREFIX}).text = value
for attr in ("lastModifiedBy", "category", "contentStatus", "version",
"revision", "keywords"):
SubElement(root, '{%s}%s' % (COREPROPS_NS, attr)).text = getattr(props, attr)
if props.lastPrinted is not None:
SubElement(root, "{%s}lastPrinted" % COREPROPS_NS).text = datetime_to_W3CDTF(props.lastPrinted
)
return tostring(root)
def test_internal_hyperlink(worksheet):
from ..worksheet import write_hyperlinks
from openpyxl.worksheet.hyperlink import Hyperlink
ws = worksheet
cell = ws['A1']
cell.hyperlink = Hyperlink(ref="",
location="'STP nn000TL-10, PKG 2.52'!A1")
ws._hyperlinks.append(cell.hyperlink)
hyper = write_hyperlinks(ws)
xml = tostring(hyper.to_tree())
expected = """
<hyperlinks>
<hyperlink location="'STP nn000TL-10, PKG 2.52'!A1" ref="A1"/>
</hyperlinks>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_write_external_link():
from .. external import ExternalRange
from .. external import write_external_link
link1 = ExternalRange('r1', 'over_there!$A$1:$B$2')
link2 = ExternalRange('r2', 'somewhere_else!$C$10:$D$12')
links = [link1, link2]
el = write_external_link(links)
xml = tostring(el)
expected = """
<externalLink xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<externalBook xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" r:id="rId1">
<definedNames>
<definedName name="r1" refersTo="over_there!$A$1:$B$2"/>
<definedName name="r2" refersTo="somewhere_else!$C$10:$D$12"/>
</definedNames>
</externalBook>
</externalLink>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_write(self, Chartsheet):
sheetview = ChartsheetView(tabSelected=True, zoomScale=80, workbookViewId=0, zoomToFit=True)
chartsheetViews = ChartsheetViewList(sheetView=[sheetview])
pageMargins = PageMargins(left=0.7, right=0.7, top=0.75, bottom=0.75, header=0.3, footer=0.3)
drawing = Drawing("rId1")
item = Chartsheet(sheetViews=chartsheetViews, pageMargins=pageMargins, drawing=drawing)
expected = """
<chartsheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<sheetViews>
<sheetView tabSelected="1" zoomScale="80" workbookViewId="0" zoomToFit="1"/>
</sheetViews>
<pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>
<drawing r:id="rId1"/>
</chartsheet>
"""
xml = tostring(item.to_tree())
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_external_hyperlink(worksheet):
from ..worksheet import write_hyperlinks
ws = worksheet
cell = ws['A1']
cell.value = "test"
cell.hyperlink = "http://test.com"
ws._hyperlinks.append(cell.hyperlink)
hyper = write_hyperlinks(ws)
assert len(worksheet._rels) == 1
assert worksheet._rels["rId1"].Target == "http://test.com"
xml = tostring(hyper.to_tree())
expected = """
<hyperlinks xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<hyperlink r:id="rId1" ref="A1"/>
</hyperlinks>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_formula_rule(worksheet_with_cf, write_conditional_formatting):
from openpyxl.formatting import FormulaRule
ws = worksheet_with_cf
ws.conditional_formatting.add(
'C1:C10', FormulaRule(formula=['ISBLANK(C1)'], stopIfTrue=True))
cfs = write_conditional_formatting(ws)
xml = b""
for cf in cfs:
xml += tostring(cf)
diff = compare_xml(
xml, """
<conditionalFormatting sqref="C1:C10">
<cfRule type="expression" stopIfTrue="1" priority="1">
<formula>ISBLANK(C1)</formula>
</cfRule>
</conditionalFormatting>
""")
assert diff is None, diff
def test_data_validation(worksheet):
from ..worksheet import write_datavalidation
from openpyxl.worksheet.datavalidation import DataValidation, ValidationType
ws = worksheet
dv = DataValidation(ValidationType.LIST, formula1='"Dog,Cat,Fish"')
dv.add_cell(ws['A1'])
ws.add_data_validation(dv)
xml = write_datavalidation(worksheet)
xml = tostring(xml)
expected = """
<dataValidations xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="1">
<dataValidation allowBlank="0" showErrorMessage="1" showInputMessage="1" sqref="A1" type="list">
<formula1>"Dog,Cat,Fish"</formula1>
</dataValidation>
</dataValidations>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_ctor(self, CustomWorkbookView):
view = CustomWorkbookView(
name="custom view",
guid="{00000000-5BD2-4BC8-9F70-7020E1357FB2}",
windowWidth=800,
windowHeight=600,
activeSheetId=1,
)
xml = tostring(view.to_tree())
expected = """
<customWorkbookView activeSheetId="1"
guid="{00000000-5BD2-4BC8-9F70-7020E1357FB2}"
name="custom view"
showComments="commIndicator"
showObjects="all"
windowHeight="600"
windowWidth="800" />
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_write_serial(ten_row_sheet, LineChart, Series, Reference, root_xml):
ws = ten_row_sheet
chart = LineChart()
for idx, l in enumerate("ABCDEF", 1):
ws.cell(row=idx, column=1).value = l
ref = Reference(ws, (1, 1), (10, 1))
series = Series(ref)
chart.add_serie(series)
cw = BaseChartWriter(chart)
cw._write_serial(cw.root, ref)
xml = tostring(cw.root)
expected = """
<c:chartSpace xmlns:c="http://schemas.openxmlformats.org/drawingml/2006/chart">
<c:strRef>
<c:f>'data'!$A$1:$A$10</c:f>
</c:strRef>
</c:chartSpace>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_write(self, CustomChartsheetViews):
from ..custom import CustomChartsheetView
pageMargins = PageMargins(left=0.2362204724409449, right=0.2362204724409449, top=0.7480314960629921,
bottom=0.7480314960629921, header=0.3149606299212598, footer=0.3149606299212598)
customChartsheetView = CustomChartsheetView(guid="{C43F44F8-8CE9-4A07-A9A9-0646C7C6B826}", scale=88,
zoomToFit=1,
pageMargins=pageMargins)
customChartsheetViews = CustomChartsheetViews(customSheetView=[customChartsheetView])
expected = """
<customSheetViews>
<customSheetView guid="{C43F44F8-8CE9-4A07-A9A9-0646C7C6B826}" scale="88" state="visible" zoomToFit="1">
<pageMargins left="0.2362204724409449" right="0.2362204724409449" top="0.7480314960629921" bottom="0.7480314960629921" header="0.3149606299212598" footer="0.3149606299212598" />
</customSheetView>
</customSheetViews>
"""
xml = tostring(customChartsheetViews.to_tree())
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_write_chart(self, SpreadsheetDrawing):
from openpyxl.chart._chart import ChartBase
class Chart(ChartBase):
anchor = "E15"
width = 15
height = 7.5
drawing = SpreadsheetDrawing()
drawing.charts.append(Chart())
xml = tostring(drawing._write())
expected = """
<wsDr xmlns="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing"
xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main">
<oneCellAnchor>
<from>
<col>4</col>
<colOff>0</colOff>
<row>14</row>
<rowOff>0</rowOff>
</from>
<ext cx="5400000" cy="2700000"/>
<graphicFrame>
<nvGraphicFramePr>
<cNvPr id="1" name="Chart 1"/>
<cNvGraphicFramePr/>
</nvGraphicFramePr>
<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>
</graphicFrame>
<clientData/>
</oneCellAnchor>
</wsDr>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_write_only_cell_vml(datadir):
from openpyxl.xml.functions import Element, tostring
datadir.chdir()
wb = Workbook()
ws = wb.active
cell = ws['A1'] # write-only cells are always A1
cell.comment = Comment("Some text", "an author")
cell.col_idx = 2
cell.row = 2
writer = CommentWriter(ws)
root = Element("root")
xml = writer._write_comment_shape(cell.comment, 1)
xml = tostring(xml)
expected = """
<v:shape
xmlns:v="urn:schemas-microsoft-com:vml"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:o="urn:schemas-microsoft-com:office:office"
fillcolor="#ffffe1"
id="_x0000_s0001"
style="position:absolute; margin-left:59.25pt;margin-top:1.5pt;width:108pt;height:59.25pt;z-index:1;visibility:hidden"
type="#_x0000_t202"
o:insetmode="auto">
<v:fill color2="#ffffe1"/>
<v:shadow color="black" obscured="t"/>
<v:path o:connecttype="none"/>
<v:textbox style="mso-direction-alt:auto">
<div style="text-align:left"/>
</v:textbox>
<x:ClientData ObjectType="Note">
<x:MoveWithCells/>
<x:SizeWithCells/>
<x:AutoFill>False</x:AutoFill>
<x:Row>1</x:Row>
<x:Column>1</x:Column>
</x:ClientData>
</v:shape>
"""
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)
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 _write_comment(self, ws):
cs = CommentSheet.from_comments(ws._comments)
self._comments.append(cs)
cs._id = len(self._comments)
self._archive.writestr(cs.path[1:], tostring(cs.to_tree()))
self.manifest.append(cs)
if ws.legacy_drawing is None:
ws.legacy_drawing = 'xl/drawings/commentsDrawing{0}.vml'.format(cs._id)
vml = None
else:
vml = fromstring(self.workbook.vba_archive.read(ws.legacy_drawing))
vml = cs.write_shapes(vml)
self._archive.writestr(ws.legacy_drawing, vml)
self.vba_modified.add(ws.legacy_drawing)
comment_rel = Relationship(Id="comments", type=cs._rel_type, Target=cs.path)
ws._rels.append(comment_rel)
def test_ctor(self, GroupShapeProperties):
xfrm = GroupTransform2D(
off=Point2D(x=2222500, y=0),
ext=PositiveSize2D(cx=2806700, cy=825500),
chOff=Point2D(x=303, y=0),
chExt=PositiveSize2D(cx=321, cy=111),
)
props = GroupShapeProperties(bwMode="auto", xfrm=xfrm)
xml = tostring(props.to_tree())
expected = """
<grpSpPr bwMode="auto" xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main">
<a:xfrm rot="0">
<a:off x="2222500" y="0"/>
<a:ext cx="2806700" cy="825500"/>
<a:chOff x="303" y="0"/>
<a:chExt cx="321" cy="111"/>
</a:xfrm>
</grpSpPr>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_to_tree(self, Dummy):
dummy = Dummy([1, '2', 3])
root = Element("root")
for node in Dummy.value.to_tree(
"el",
dummy.value,
):
root.append(node)
xml = tostring(root)
expected = """
<root>
<el>1</el>
<el>2</el>
<el>3</el>
</root>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_ctor(self, CharacterProperties):
from ..text import Font
normal_font = Font(typeface='Arial')
text = CharacterProperties(latin=normal_font,
sz=900,
b=False,
solidFill='FFC000')
xml = tostring(text.to_tree())
expected = ("""
<a:defRPr xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"
b="0" sz="900">
<a:solidFill>
<a:srgbClr val="FFC000"/>
</a:solidFill>
<a:latin typeface="Arial"/>
</a:defRPr>
""")
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_ctor(self, PivotFilter, Autofilter):
flt = PivotFilter(fld=0,
id=6,
evalOrder=-1,
type="dateBetween",
autoFilter=Autofilter)
xml = tostring(flt.to_tree())
expected = """
<filter fld="0" type="dateBetween" evalOrder="-1" id="6">
<autoFilter ref="A1">
<filterColumn colId="0">
<customFilters and="1">
<customFilter operator="greaterThanOrEqual" val="1"/>
<customFilter operator="lessThanOrEqual" val="2"/>
</customFilters>
</filterColumn>
</autoFilter>
</filter>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff