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_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_drawing(self, drawing):
"""
Write a drawing
"""
self._drawings.append(drawing)
drawing._id = len(self._drawings)
for chart in drawing.charts:
self._charts.append(chart)
chart._id = len(self._charts)
for img in drawing.images:
self._images.append(img)
img._id = len(self._images)
rels_path = get_rels_path(drawing.path)[1:]
self._archive.writestr(drawing.path[1:], tostring(drawing._write()))
self._archive.writestr(rels_path, tostring(drawing._write_rels()))
self.manifest.append(drawing)
def test_write(self, WebPublishItems):
from ..publish import WebPublishItem
webPublish_6433 = WebPublishItem(id=6433,
divId="Views_6433",
sourceType="chart",
sourceRef="",
sourceObject="Chart 1",
destinationFile=r"D:\Publish.mht",
title="First Chart",
autoRepublish=False)
webPublish_64487 = WebPublishItem(id=64487,
divId="Views_64487",
sourceType="chart",
sourceRef="Ref_545421",
sourceObject="Chart 15",
destinationFile=r"D:\Publish_12.mht",
title="Second Chart",
autoRepublish=True)
webPublishItems = WebPublishItems(
webPublishItem=[webPublish_6433, webPublish_64487])
expected = r"""
<WebPublishItems count="2">
<webPublishItem id="6433" divId="Views_6433" sourceType="chart" sourceRef=""
sourceObject="Chart 1" destinationFile="D:\Publish.mht" title="First Chart" autoRepublish="0"/>
<webPublishItem id="64487" divId="Views_64487" sourceType="chart" sourceRef="Ref_545421"
sourceObject="Chart 15" destinationFile="D:\Publish_12.mht" title="Second Chart" autoRepublish="1"/>
</WebPublishItems>
"""
xml = tostring(webPublishItems.to_tree())
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_ctor(self, MergeCell):
cell = MergeCell("A1")
node = cell.to_tree()
xml = tostring(node)
expected = "<mergeCell ref='A1' />"
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_ctor(self, ManualLayout):
layout = ManualLayout(layoutTarget="inner",
xMode="edge",
yMode="factor",
wMode="factor",
hMode="edge",
x=10,
y=50,
w=4,
h=100)
xml = tostring(layout.to_tree())
expected = """
<manualLayout>
<layoutTarget val="inner"></layoutTarget>
<xMode val="edge"></xMode>
<yMode val="factor"></yMode>
<wMode val="factor"></wMode>
<hMode val="edge"></hMode>
<x val="10"></x>
<y val="50"></y>
<w val="4"></w>
<h val="100"></h>
</manualLayout>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_empty_dv(self, DataValidationList, DataValidation):
dv = DataValidation()
dvs = DataValidationList(dataValidation=[dv])
xml = tostring(dvs.to_tree())
expected = '<dataValidations count="0"/>'
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_from_xml(self, PictureFrame):
src = """
<pic xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main">
<nvPicPr>
<cNvPr descr="Picture" id="1" name="Image 1"/>
<cNvPicPr/>
</nvPicPr>
<blipFill>
<a:blip xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" cstate="print" r:embed="rId1"/>
<a:stretch>
<a:fillRect/>
</a:stretch>
</blipFill>
<spPr>
<a:xfrm>
<a:off x="303" y="0"/>
<a:ext cx="321" cy="88"/>
</a:xfrm>
<a:prstGeom prst="rect"/>
<a:ln>
<a:prstDash val="solid" />
</a:ln>
</spPr>
</pic>
"""
node = fromstring(src)
graphic = PictureFrame.from_tree(node)
xml = tostring(graphic.to_tree())
diff = compare_xml(xml, src)
assert diff is None, diff
def test_zvalues(self, Series):
series = Series("Sheet!A2:A5",
xvalues="Sheet!B2:B5",
zvalues="Sheet!C2:C5")
series.__elements__ = ('xVal', 'yVal', 'bubbleSize')
xml = tostring(series.to_tree())
expected = """
<ser>
<xVal>
<numRef>
<f>Sheet!$B$2:$B$5</f>
</numRef>
</xVal>
<yVal>
<numRef>
<f>Sheet!$A$2:$A$5</f>
</numRef>
</yVal>
<bubbleSize>
<numRef>
<f>Sheet!$C$2:$C$5</f>
</numRef>
</bubbleSize>
</ser>
"""
diff = compare_xml(xml, expected)
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_ctor(self, ComplexObject):
style = ComplexObject()
ft1 = Font(family=2, sz=11, name="Arial")
ft2 = Font(bold=True)
style.fonts = [ft1, ft2]
expected = """
<style>
<fonts count="2">
<font>
<name val="Arial" />
<family val="2"></family>
<sz val="11"></sz>
</font>
<font>
<b val="1"></b>
</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 _write(self, archive, manifest):
"""
Write to zipfile and update manifest
"""
xml = tostring(self.to_tree())
archive.writestr(self.path[1:], xml)
manifest.append(self)
def test_simple_styles(datadir):
import datetime
from openpyxl2 import Workbook
from ..protection import Protection
from .. import numbers
from ..stylesheet import write_stylesheet
wb = Workbook()
wb.guess_types = True
ws = wb.active
now = datetime.date.today()
for idx, v in enumerate(
['12.34%', now, 'This is a test', '31.31415', None], 1):
ws.append([v])
_ = ws.cell(column=1, row=idx).style_id
# set explicit formats
ws['D9'].number_format = numbers.FORMAT_NUMBER_00
ws['D9'].protection = Protection(locked=True)
ws['D9'].style_id
ws['E1'].protection = Protection(hidden=True)
ws['E1'].style_id
assert len(wb._cell_styles) == 5
stylesheet = write_stylesheet(wb)
datadir.chdir()
with open('simple-styles.xml') as reference_file:
expected = reference_file.read()
xml = tostring(stylesheet)
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 openpyxl2.styles import PatternFill, Font, Color
from openpyxl2.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_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_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_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_ctor(SampleProperties):
expected = """
<coreProperties
xmlns="http://schemas.openxmlformats.org/package/2006/metadata/core-properties"
xmlns:dc="http://purl.org/dc/elements/1.1/"
xmlns:dcterms="http://purl.org/dc/terms/"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<dc:creator>TEST_USER</dc:creator>
<dc:title>The title</dc:title>
<dc:description>The description</dc:description>
<dc:subject>The subject</dc:subject>
<dc:identifier>The identifier</dc:identifier>
<dc:language>The language</dc:language>
<dcterms:created xsi:type="dcterms:W3CDTF">2010-04-01T20:30:00Z</dcterms:created>
<dcterms:modified xsi:type="dcterms:W3CDTF">2010-04-05T14:05:30Z</dcterms:modified>
<lastModifiedBy>SOMEBODY</lastModifiedBy>
<category>The category</category>
<contentStatus>The status</contentStatus>
<version>2.5</version>
<revision>0</revision>
<keywords>one, two, three</keywords>
<lastPrinted>2014-10-14T10:30:00Z</lastPrinted>
</coreProperties>
"""
xml = tostring(SampleProperties.to_tree())
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_serialise(self, Border, Side):
medium_blue = Side(border_style='medium', color=Color(colors.BLUE))
bd = Border(
left=medium_blue,
right=medium_blue,
top=medium_blue,
bottom=medium_blue,
outline=False,
diagonalDown=True,
)
xml = tostring(bd.to_tree())
expected = """
<border diagonalDown="1" outline="0">
<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>
<diagonal />
</border>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_conditional_font(self):
"""Test to verify font style written correctly."""
ws = self.ws
cf = ConditionalFormattingList()
ws.conditional_formatting = cf
# Create cf rule
redFill = PatternFill(start_color=Color('FFEE1111'),
end_color=Color('FFEE1111'),
patternType=fills.FILL_SOLID)
whiteFont = Font(color=Color("FFFFFFFF"))
ws.conditional_formatting.add('A1:A3',
CellIsRule(operator='equal', formula=['"Fail"'], stopIfTrue=False,
font=whiteFont, fill=redFill))
from openpyxl2.writer.worksheet import write_conditional_formatting
# First, verify conditional formatting xml
cfs = write_conditional_formatting(ws)
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" stopIfTrue="0">
<formula>"Fail"</formula>
</cfRule>
</conditionalFormatting>
""")
assert diff is None, diff
def write(self, shape_id):
root = Element('{%s}userShapes' % CHART_NS)
for shape in self._shapes:
anchor = SubElement(root, '{%s}relSizeAnchor' % CHART_DRAWING_NS)
xstart, ystart, xend, yend = shape.coordinates
_from = SubElement(anchor, '{%s}from' % CHART_DRAWING_NS)
SubElement(_from, '{%s}x' % CHART_DRAWING_NS).text = str(xstart)
SubElement(_from, '{%s}y' % CHART_DRAWING_NS).text = str(ystart)
_to = SubElement(anchor, '{%s}to' % CHART_DRAWING_NS)
SubElement(_to, '{%s}x' % CHART_DRAWING_NS).text = str(xend)
SubElement(_to, '{%s}y' % CHART_DRAWING_NS).text = str(yend)
sp = SubElement(anchor, '{%s}sp' % CHART_DRAWING_NS, {
'macro': '',
'textlink': ''
})
nvspr = SubElement(sp, '{%s}nvSpPr' % CHART_DRAWING_NS)
SubElement(nvspr, '{%s}cNvPr' % CHART_DRAWING_NS, {
'id': str(shape_id),
'name': 'shape %s' % shape_id
})
SubElement(nvspr, '{%s}cNvSpPr' % CHART_DRAWING_NS)
sppr = SubElement(sp, '{%s}spPr' % CHART_DRAWING_NS)
frm = SubElement(
sppr,
'{%s}xfrm' % DRAWING_NS,
)
# no transformation
SubElement(frm, '{%s}off' % DRAWING_NS, {'x': '0', 'y': '0'})
SubElement(frm, '{%s}ext' % DRAWING_NS, {'cx': '0', 'cy': '0'})
prstgeom = SubElement(sppr, '{%s}prstGeom' % DRAWING_NS,
{'prst': str(shape.style)})
SubElement(prstgeom, '{%s}avLst' % DRAWING_NS)
fill = SubElement(
sppr,
'{%s}solidFill' % DRAWING_NS,
)
SubElement(fill, '{%s}srgbClr' % DRAWING_NS, {'val': shape.color})
border = SubElement(sppr, '{%s}ln' % DRAWING_NS,
{'w': str(shape._border_width)})
sf = SubElement(border, '{%s}solidFill' % DRAWING_NS)
SubElement(sf, '{%s}srgbClr' % DRAWING_NS,
{'val': shape.border_color})
self._write_style(sp)
self._write_text(sp, shape)
shape_id += 1
return tostring(root)
def test_write(self, DrawingHF):
drawingHF = DrawingHF(lho=7, lhf=6, id='rId3')
expected = """
<drawingHF lho="7" lhf="6" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" r:id="rId3" />
"""
xml = tostring(drawingHF.to_tree("drawingHF"))
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_write(self, SheetBackgroundPicture):
sheetBackgroundPicture = SheetBackgroundPicture(id="rId5")
expected = """
<picture r:id="rId5" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" />
"""
xml = tostring(sheetBackgroundPicture.to_tree())
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_ctor(self, PictureOptions):
picture = PictureOptions()
xml = tostring(picture.to_tree())
expected = """
<pictureOptions />
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_ctor(self, PlotArea):
plot = PlotArea()
xml = tostring(plot.to_tree())
expected = """
<plotArea />
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_ctor(self, DataTable):
table = DataTable()
xml = tostring(table.to_tree())
expected = """
<dTable />
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_ctor(self, ConditionalFormatting):
cf = ConditionalFormatting(sqref="A1:B5")
xml = tostring(cf.to_tree())
expected = """
<conditionalFormatting sqref="A1:B5" />
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_ctor(self, FunctionGroupList):
function_group = FunctionGroupList()
xml = tostring(function_group.to_tree())
expected = """
<functionGroups builtInGroupCount="16"/>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
