def test_write_text(self):
from openpyxl.drawing import Shape
root = Element("{%s}test" % CHART_DRAWING_NS)
self.sw._write_text(root, self.shape)
xml = get_xml(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 write_rels(self, drawing_id):
root = Element('Relationships', {'xmlns' : 'http://schemas.openxmlformats.org/package/2006/relationships'})
attrs = {'Id' : 'rId1',
'Type' : 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/chartUserShapes',
'Target' : '../drawings/drawing%s.xml' % drawing_id }
SubElement(root, 'Relationship', attrs)
return get_document_content(root)
def write_rels(self, drawing_id):
root = Element("{%s}Relationships" % PKG_REL_NS)
attrs = {'Id' : 'rId1',
'Type' : '%s/chartUserShapes' % REL_NS,
'Target' : '../drawings/drawing%s.xml' % drawing_id }
SubElement(root, '{%s}Relationship' % PKG_REL_NS, attrs)
return get_document_content(root)
def test_namespace_register():
from openpyxl.shared.xmltools import Element, tostring
from openpyxl.shared.ooxml import SHEET_MAIN_NS
e = Element('{%s}sheet' % SHEET_MAIN_NS)
xml = tostring(e)
if hasattr(xml, "decode"):
xml = xml.decode("utf-8")
assert xml.startswith("<s:sheet")
def write_rels(self, chart_id):
root = Element('Relationships',
{'xmlns' : 'http://schemas.openxmlformats.org/package/2006/relationships'})
for i, chart in enumerate(self._sheet._charts):
attrs = {'Id' : 'rId%s' % (i + 1),
'Type' : 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/chart',
'Target' : '../charts/chart%s.xml' % (chart_id + i) }
SubElement(root, 'Relationship', attrs)
return get_document_content(root)
def test_write_anchor(self):
from openpyxl.drawing import Image
path = os.path.join(DATADIR, "plain.png")
drawing = Image(path).drawing
root = Element("test")
self.dw._write_anchor(root, drawing)
xml = get_xml(root)
expected = """<test><xdr:absoluteAnchor xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing"><xdr:pos x="0" y="0"/><xdr:ext cx="1123950" cy="1123950"/></xdr:absoluteAnchor></test>"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def write(self):
""" write drawings for one sheet in one file """
root = Element('xdr:wsDr',
{'xmlns:xdr' : "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing",
'xmlns:a' : "http://schemas.openxmlformats.org/drawingml/2006/main"})
for i, chart in enumerate(self._sheet._charts):
drawing = chart.drawing
# anchor = SubElement(root, 'xdr:twoCellAnchor')
# (start_row, start_col), (end_row, end_col) = drawing.coordinates
# # anchor coordinates
# _from = SubElement(anchor, 'xdr:from')
# x = SubElement(_from, 'xdr:col').text = str(start_col)
# x = SubElement(_from, 'xdr:colOff').text = '0'
# x = SubElement(_from, 'xdr:row').text = str(start_row)
# x = SubElement(_from, 'xdr:rowOff').text = '0'
# _to = SubElement(anchor, 'xdr:to')
# x = SubElement(_to, 'xdr:col').text = str(end_col)
# x = SubElement(_to, 'xdr:colOff').text = '0'
# x = SubElement(_to, 'xdr:row').text = str(end_row)
# x = SubElement(_to, 'xdr:rowOff').text = '0'
# we only support absolute anchor atm (TODO: oneCellAnchor, twoCellAnchor
x, y, w, h = drawing.get_emu_dimensions()
anchor = SubElement(root, 'xdr:absoluteAnchor')
SubElement(anchor, 'xdr:pos', {'x':str(x), 'y':str(y)})
SubElement(anchor, 'xdr:ext', {'cx':str(w), 'cy':str(h)})
# graph frame
frame = SubElement(anchor, 'xdr:graphicFrame', {'macro':''})
name = SubElement(frame, 'xdr:nvGraphicFramePr')
SubElement(name, 'xdr:cNvPr', {'id':'%s' % i, 'name':'Graphique %s' % i})
SubElement(name, 'xdr:cNvGraphicFramePr')
frm = SubElement(frame, 'xdr:xfrm')
# no transformation
SubElement(frm, 'a:off', {'x':'0', 'y':'0'})
SubElement(frm, 'a:ext', {'cx':'0', 'cy':'0'})
graph = SubElement(frame, 'a:graphic')
data = SubElement(graph, 'a:graphicData',
{'uri':'http://schemas.openxmlformats.org/drawingml/2006/chart'})
SubElement(data, 'c:chart',
{ 'xmlns:c':'http://schemas.openxmlformats.org/drawingml/2006/chart',
'xmlns:r':'http://schemas.openxmlformats.org/officeDocument/2006/relationships',
'r:id':'rId%s' % (i + 1)})
SubElement(anchor, 'xdr:clientData')
return get_document_content(root)
def write_root_rels(workbook):
"""Write the relationships xml."""
root = Element('Relationships', {'xmlns':
'http://schemas.openxmlformats.org/package/2006/relationships'})
SubElement(root, 'Relationship', {'Id': 'rId1', 'Target': ARC_WORKBOOK,
'Type': 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument'})
SubElement(root, 'Relationship', {'Id': 'rId2', 'Target': ARC_CORE,
'Type': 'http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties'})
SubElement(root, 'Relationship', {'Id': 'rId3', 'Target': ARC_APP,
'Type': 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties'})
return get_document_content(root)
def write_workbook_rels(workbook):
"""Write the workbook relationships xml."""
root = Element('Relationships', {
'xmlns':
'http://schemas.openxmlformats.org/package/2006/relationships'
})
for i in range(len(workbook.worksheets)):
SubElement(
root, 'Relationship', {
'Id': 'rId%d' % (i + 1),
'Type':
'http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet',
'Target': 'worksheets/sheet%s.xml' % (i + 1)
})
rid = len(workbook.worksheets) + 1
SubElement(
root, 'Relationship', {
'Id':
'rId%d' % rid,
'Target':
'sharedStrings.xml',
'Type':
'http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings'
})
SubElement(
root, 'Relationship', {
'Id':
'rId%d' % (rid + 1),
'Target':
'styles.xml',
'Type':
'http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles'
})
SubElement(
root, 'Relationship', {
'Id':
'rId%d' % (rid + 2),
'Target':
'theme/theme1.xml',
'Type':
'http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme'
})
if workbook.vba_archive:
SubElement(
root, 'Relationship', {
'Id':
'rId%d' % (rid + 3),
'Target':
'vbaProject.bin',
'Type':
'http://schemas.microsoft.com/office/2006/relationships/vbaProject'
})
return get_document_content(root)
def write(self):
""" write drawings for one sheet in one file """
root = Element("{%s}wsDr" % SHEET_DRAWING_NS)
for idx, chart in enumerate(self._sheet._charts):
self._write_chart(root, chart, idx + 1)
for idx, img in enumerate(self._sheet._images):
self._write_image(root, img, idx + 1)
return get_document_content(root)
def setUp(self):
wb = Workbook()
ws = wb.get_active_sheet()
ws.title = 'data'
for i in range(10):
ws.cell(row=i, column=0).value = i
ws.cell(row=i, column=1).value = i
self.scatterchart = ScatterChart()
self.scatterchart.add_serie(Serie(Reference(ws, (0, 0), (10, 0)),
xvalues=Reference(ws, (0, 1), (10, 1))))
self.cw = ChartWriter(self.scatterchart)
self.root = Element('test')
def setUp(self):
wb = Workbook()
ws = wb.get_active_sheet()
ws.title = u'data'
for i in range(10):
ws.cell(row=i, column=0).value = i
self.chart = BarChart()
self.chart.title = 'TITLE'
self.chart.add_serie(Serie(Reference(ws, (0, 0), (10, 0))))
self.chart._series[-1].color = Color.GREEN
self.cw = ChartWriter(self.chart)
self.root = Element('test')
def test_write_anchor_onecell(self):
from openpyxl.drawing import Image
path = os.path.join(DATADIR, "plain.png")
drawing = Image(path).drawing
drawing.anchortype = "oneCell"
drawing.anchorcol = 0
drawing.anchorrow = 0
root = Element("test")
self.dw._write_anchor(root, drawing)
xml = get_xml(root)
expected = """<test><xdr:oneCellAnchor xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing"><xdr:from><xdr:col>0</xdr:col><xdr:colOff>0</xdr:colOff><xdr:row>0</xdr:row><xdr:rowOff>0</xdr:rowOff></xdr:from><xdr:ext cx="1123950" cy="1123950"/></xdr:oneCellAnchor></test>"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def write(self):
""" write a chart """
root = Element('c:chartSpace',
{'xmlns:c':"http://schemas.openxmlformats.org/drawingml/2006/chart",
'xmlns:a':"http://schemas.openxmlformats.org/drawingml/2006/main",
'xmlns:r':"http://schemas.openxmlformats.org/officeDocument/2006/relationships"})
SubElement(root, 'c:lang', {'val':self.chart.lang})
self._write_chart(root)
self._write_print_settings(root)
self._write_shapes(root)
return get_document_content(root)
def test_write_images(self):
from openpyxl.drawing import Image
path = os.path.join(DATADIR, "plain.png")
img = Image(path)
root = Element("{%s}wsDr" % SHEET_DRAWING_NS)
self.dw._write_image(root, img, 1)
drawing_schema.assertValid(root)
xml = get_xml(root)
expected = """<xdr:wsDr xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing">
<xdr:absoluteAnchor>
<xdr:pos x="0" y="0"/>
<xdr:ext cx="1123950" cy="1123950"/>
<xdr:pic>
<xdr:nvPicPr>
<xdr:cNvPr id="2" name="Picture 1"/>
<xdr:cNvPicPr>
<a:picLocks noChangeArrowheads="1" noChangeAspect="1"/>
</xdr:cNvPicPr>
</xdr:nvPicPr>
<xdr:blipFill>
<a:blip xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" cstate="print" r:embed="rId1"/>
<a:srcRect/>
<a:stretch>
<a:fillRect/>
</a:stretch>
</xdr:blipFill>
<xdr:spPr bwMode="auto">
<a:xfrm>
<a:off x="0" y="0"/>
<a:ext cx="0" cy="0"/>
</a:xfrm>
<a:prstGeom prst="rect">
<a:avLst/>
</a:prstGeom>
<a:noFill/>
<a:ln w="1">
<a:noFill/>
<a:miter lim="800000"/>
<a:headEnd/>
<a:tailEnd len="med" type="none" w="med"/>
</a:ln>
<a:effectLst/>
</xdr:spPr>
</xdr:pic>
<xdr:clientData/>
</xdr:absoluteAnchor>
</xdr:wsDr>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def write_worksheet_rels(worksheet, idx):
"""Write relationships for the worksheet to xml."""
root = Element('Relationships', {'xmlns': 'http://schemas.openxmlformats.org/package/2006/relationships'})
for rel in worksheet.relationships:
attrs = {'Id': rel.id, 'Type': rel.type, 'Target': rel.target}
if rel.target_mode:
attrs['TargetMode'] = rel.target_mode
SubElement(root, 'Relationship', attrs)
if worksheet._charts:
attrs = {'Id' : 'rId1',
'Type' : 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing',
'Target' : '../drawings/drawing%s.xml' % idx }
SubElement(root, 'Relationship', attrs)
return get_document_content(root)
def write_properties_core(properties):
"""Write the core properties to xml."""
root = Element('cp:coreProperties', {'xmlns:cp': NAMESPACES['cp'],
'xmlns:xsi': NAMESPACES['xsi'], 'xmlns:dc': NAMESPACES['dc'],
'xmlns:dcterms': NAMESPACES['dcterms'],
'xmlns:dcmitype': NAMESPACES['dcmitype'], })
SubElement(root, 'dc:creator').text = properties.creator
SubElement(root, 'cp:lastModifiedBy').text = properties.last_modified_by
SubElement(root, 'dcterms:created', \
{'xsi:type': 'dcterms:W3CDTF'}).text = \
datetime_to_W3CDTF(properties.created)
SubElement(root, 'dcterms:modified',
{'xsi:type': 'dcterms:W3CDTF'}).text = \
datetime_to_W3CDTF(properties.modified)
return get_document_content(root)
def write_comments_vml(self):
root = Element("xml")
shape_layout = SubElement(root, "{%s}shapelayout" % officens, {"{%s}ext" % vmlns: "edit"})
SubElement(shape_layout, "{%s}idmap" % officens, {"{%s}ext" % vmlns: "edit", "data": "1"})
shape_type=SubElement(root, "{%s}shapetype" % vmlns, {"id": "_x0000_t202",
"coordsize": "21600,21600",
"{%s}spt" % officens: "202",
"path": "m,l,21600r21600,l21600,xe"})
SubElement(shape_type, "{%s}stroke" % vmlns, {"joinstyle": "miter"})
SubElement(shape_type, "{%s}path" % vmlns, {"gradientshapeok": "t",
"{%s}connecttype" % officens: "rect"})
for i, comment in enumerate(self.comments):
self._write_comment_shape(root, comment, i)
return get_document_content(root)
def test_no_write_legend(self):
wb = Workbook()
ws = wb.get_active_sheet()
ws.title = 'data'
for i in range(10):
ws.cell(row=i, column=0).value = i
ws.cell(row=i, column=1).value = i
scatterchart = ScatterChart()
scatterchart.add_serie(Serie(Reference(ws, (0, 0), (10, 0)),
xvalues=Reference(ws, (0, 1), (10, 1))))
cw = ChartWriter(scatterchart)
root = Element('test')
scatterchart.show_legend = False
cw._write_legend(root)
eq_(get_xml(root), '<?xml version=\'1.0\' encoding=\'UTF-8\'?><test />')
def write_workbook(workbook):
"""Write the core workbook xml."""
root = Element('workbook', {'xmlns': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main',
'xml:space': 'preserve', 'xmlns:r': 'http://schemas.openxmlformats.org/officeDocument/2006/relationships'})
SubElement(root, 'fileVersion', {'appName': 'xl', 'lastEdited': '4',
'lowestEdited': '4', 'rupBuild': '4505'})
SubElement(root, 'workbookPr', {'defaultThemeVersion': '124226',
'codeName': 'ThisWorkbook'})
book_views = SubElement(root, 'bookViews')
SubElement(book_views, 'workbookView', {'activeTab': '%d' % workbook.get_index(workbook.get_active_sheet()),
'autoFilterDateGrouping': '1', 'firstSheet': '0', 'minimized': '0',
'showHorizontalScroll': '1', 'showSheetTabs': '1',
'showVerticalScroll': '1', 'tabRatio': '600',
'visibility': 'visible'})
# worksheets
sheets = SubElement(root, 'sheets')
for i, sheet in enumerate(workbook.worksheets):
sheet_node = SubElement(sheets, 'sheet', {'name': sheet.title,
'sheetId': '%d' % (i + 1), 'r:id': 'rId%d' % (i + 1)})
if not sheet.sheet_state == sheet.SHEETSTATE_VISIBLE:
sheet_node.set('state', sheet.sheet_state)
# named ranges
defined_names = SubElement(root, 'definedNames')
for named_range in workbook.get_named_ranges():
name = SubElement(defined_names, 'definedName',
{'name': named_range.name})
# as there can be many cells in one range, generate the list of ranges
dest_cells = []
cell_ids = []
for worksheet, range_name in named_range.destinations:
cell_ids.append(workbook.get_index(worksheet))
dest_cells.append("'%s'!%s" % (worksheet.title.replace("'", "''"),
absolute_coordinate(range_name)))
# for local ranges, we must check all the cells belong to the same sheet
base_id = cell_ids[0]
if named_range.local_only and all([x == base_id for x in cell_ids]):
name.set('localSheetId', '%s' % base_id)
# finally write the cells list
name.text = ','.join(dest_cells)
SubElement(root, 'calcPr', {'calcId': '124519', 'calcMode': 'auto',
'fullCalcOnLoad': '1'})
return get_document_content(root)
def write(self, shape_id):
root = Element('c:userShapes', {'xmlns:c' : 'http://schemas.openxmlformats.org/drawingml/2006/chart'})
for shape in self._shapes:
anchor = SubElement(root, 'cdr:relSizeAnchor',
{'xmlns:cdr' : "http://schemas.openxmlformats.org/drawingml/2006/chartDrawing"})
xstart, ystart, xend, yend = shape.get_coordinates()
_from = SubElement(anchor, 'cdr:from')
SubElement(_from, 'cdr:x').text = str(xstart)
SubElement(_from, 'cdr:y').text = str(ystart)
_to = SubElement(anchor, 'cdr:to')
SubElement(_to, 'cdr:x').text = str(xend)
SubElement(_to, 'cdr:y').text = str(yend)
sp = SubElement(anchor, 'cdr:sp', {'macro':'', 'textlink':''})
nvspr = SubElement(sp, 'cdr:nvSpPr')
SubElement(nvspr, 'cdr:cNvPr', {'id':str(shape_id), 'name':'shape %s' % shape_id})
SubElement(nvspr, 'cdr:cNvSpPr')
sppr = SubElement(sp, 'cdr:spPr')
frm = SubElement(sppr, 'a:xfrm', {'xmlns:a':self.schema})
# no transformation
SubElement(frm, 'a:off', {'x':'0', 'y':'0'})
SubElement(frm, 'a:ext', {'cx':'0', 'cy':'0'})
prstgeom = SubElement(sppr, 'a:prstGeom', {'xmlns:a':self.schema, 'prst':str(shape.style)})
SubElement(prstgeom, 'a:avLst')
fill = SubElement(sppr, 'a:solidFill', {'xmlns:a':self.schema})
SubElement(fill, 'a:srgbClr', {'val':shape.color})
border = SubElement(sppr, 'a:ln', {'xmlns:a':self.schema, 'w':str(shape._border_width)})
sf = SubElement(border, 'a:solidFill')
SubElement(sf, 'a:srgbClr', {'val':shape.border_color})
self._write_style(sp)
self._write_text(sp, shape)
shape_id += 1
return get_document_content(root)
def test_label_number_format(self, ten_column_sheet, Reference, Series,
BarChart):
ws = ten_column_sheet
labels = Reference(ws, (0, 0), (0, 9))
labels.number_format = 'd-mmm'
values = Reference(ws, (0, 0), (0, 9))
serie = Series(values=values, labels=labels)
c = BarChart()
c.add_serie(serie)
cw = BarChartWriter(c)
root = Element('test')
cw._write_serial(root, c.series[0].labels)
expected = """<test xmlns:c="http://schemas.openxmlformats.org/drawingml/2006/chart"><c:numRef><c:f>'data'!$A$1:$J$1</c:f><c:numCache><c:formatCode>d-mmm</c:formatCode><c:ptCount val="10" /><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:numCache></c:numRef></test>"""
xml = get_xml(root)
diff = compare_xml(xml, expected)
assert diff is None, diff
def write_properties_core(properties):
"""Write the core properties to xml."""
root = Element('{%s}coreProperties' % COREPROPS_NS)
SubElement(root, '{%s}creator' % DCORE_NS).text = properties.creator
SubElement(root, '{%s}lastModifiedBy' %
COREPROPS_NS).text = properties.last_modified_by
SubElement(root, '{%s}created' % DCTERMS_NS,
{'{%s}type' % XSI_NS: '%s:W3CDTF' % DCTERMS_PREFIX}).text = \
datetime_to_W3CDTF(properties.created)
SubElement(root, '{%s}modified' % DCTERMS_NS,
{'{%s}type' % XSI_NS: '%s:W3CDTF' % DCTERMS_PREFIX}).text = \
datetime_to_W3CDTF(properties.modified)
SubElement(root, '{%s}title' % DCORE_NS).text = properties.title
SubElement(root,
'{%s}description' % DCORE_NS).text = properties.description
SubElement(root, '{%s}subject' % DCORE_NS).text = properties.subject
SubElement(root, '{%s}keywords' % COREPROPS_NS).text = properties.keywords
SubElement(root, '{%s}category' % COREPROPS_NS).text = properties.category
return get_document_content(root)
def write_rels(self, chart_id, image_id):
root = Element("{%s}Relationships" % PKG_REL_NS)
i = 0
for i, chart in enumerate(self._sheet._charts):
attrs = {
'Id': 'rId%s' % (i + 1),
'Type': '%s/chart' % REL_NS,
'Target': '../charts/chart%s.xml' % (chart_id + i)
}
SubElement(root, '{%s}Relationship' % PKG_REL_NS, attrs)
for j, img in enumerate(self._sheet._images):
attrs = {
'Id': 'rId%s' % (i + j + 1),
'Type': '%s/image' % REL_NS,
'Target': '../media/image%s.png' % (image_id + j)
}
SubElement(root, '{%s}Relationship' % PKG_REL_NS, attrs)
return get_document_content(root)
def write_workbook_rels(workbook):
"""Write the workbook relationships xml."""
root = Element('{%s}Relationships' % PKG_REL_NS)
for i in range(1, len(workbook.worksheets) + 1):
SubElement(
root, '{%s}Relationship' % PKG_REL_NS, {
'Id': 'rId%d' % i,
'Target': 'worksheets/sheet%s.xml' % i,
'Type': '%s/worksheet' % REL_NS
})
rid = len(workbook.worksheets) + 1
SubElement(
root, '{%s}Relationship' % PKG_REL_NS, {
'Id': 'rId%d' % rid,
'Target': 'sharedStrings.xml',
'Type': '%s/sharedStrings' % REL_NS
})
SubElement(
root, '{%s}Relationship' % PKG_REL_NS, {
'Id': 'rId%d' % (rid + 1),
'Target': 'styles.xml',
'Type': '%s/styles' % REL_NS
})
SubElement(
root, '{%s}Relationship' % PKG_REL_NS, {
'Id': 'rId%d' % (rid + 2),
'Target': 'theme/theme1.xml',
'Type': '%s/theme' % REL_NS
})
if workbook.vba_archive:
SubElement(
root, '{%s}Relationship' % PKG_REL_NS, {
'Id':
'rId%d' % (rid + 3),
'Target':
'vbaProject.bin',
'Type':
'http://schemas.microsoft.com/office/2006/relationships/vbaProject'
})
return get_document_content(root)
def write_comments(self):
# produce xml
root = Element("{%s}comments" % SHEET_MAIN_NS)
authorlist_tag = SubElement(root, "{%s}authors" % SHEET_MAIN_NS)
for author in self.authors:
leaf = SubElement(authorlist_tag, "{%s}author" % SHEET_MAIN_NS)
leaf.text = author
commentlist_tag = SubElement(root, "{%s}commentList" % SHEET_MAIN_NS)
for comment in self.comments:
attrs = {'ref': comment._parent.get_coordinate(),
'authorId': self.author_to_id[comment.author],
'shapeId': '0'}
comment_tag = SubElement(commentlist_tag, "{%s}comment" % SHEET_MAIN_NS, attrs)
text_tag = SubElement(comment_tag, "{%s}text" % SHEET_MAIN_NS)
run_tag = SubElement(text_tag, "{%s}r" % SHEET_MAIN_NS)
SubElement(run_tag, "{%s}rPr" % SHEET_MAIN_NS)
t_tag = SubElement(run_tag, "{%s}t" % SHEET_MAIN_NS)
t_tag.text = comment.text
return get_document_content(root)
def write_properties_app(workbook):
"""Write the properties xml."""
worksheets_count = len(workbook.worksheets)
root = Element(
'Properties', {
'xmlns':
'http://schemas.openxmlformats.org/officeDocument/2006/extended-properties',
'xmlns:vt':
'http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes'
})
SubElement(root, 'Application').text = 'Microsoft Excel'
SubElement(root, 'DocSecurity').text = '0'
SubElement(root, 'ScaleCrop').text = 'false'
SubElement(root, 'Company')
SubElement(root, 'LinksUpToDate').text = 'false'
SubElement(root, 'SharedDoc').text = 'false'
SubElement(root, 'HyperlinksChanged').text = 'false'
SubElement(root, 'AppVersion').text = '12.0000'
# heading pairs part
heading_pairs = SubElement(root, 'HeadingPairs')
vector = SubElement(heading_pairs, 'vt:vector', {
'size': '2',
'baseType': 'variant'
})
variant = SubElement(vector, 'vt:variant')
SubElement(variant, 'vt:lpstr').text = 'Worksheets'
variant = SubElement(vector, 'vt:variant')
SubElement(variant, 'vt:i4').text = '%d' % worksheets_count
# title of parts
title_of_parts = SubElement(root, 'TitlesOfParts')
vector = SubElement(title_of_parts, 'vt:vector', {
'size': '%d' % worksheets_count,
'baseType': 'lpstr'
})
for ws in workbook.worksheets:
SubElement(vector, 'vt:lpstr').text = '%s' % ws.title
return get_document_content(root)
def write_content_types(workbook):
"""Write the content-types xml."""
root = Element('Types', {'xmlns': 'http://schemas.openxmlformats.org/package/2006/content-types'})
SubElement(root, 'Override', {'PartName': '/' + ARC_THEME, 'ContentType': 'application/vnd.openxmlformats-officedocument.theme+xml'})
SubElement(root, 'Override', {'PartName': '/' + ARC_STYLE, 'ContentType': 'application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml'})
SubElement(root, 'Default', {'Extension': 'rels', 'ContentType': 'application/vnd.openxmlformats-package.relationships+xml'})
SubElement(root, 'Default', {'Extension': 'xml', 'ContentType': 'application/xml'})
SubElement(root, 'Override', {'PartName': '/' + ARC_WORKBOOK, 'ContentType': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml'})
SubElement(root, 'Override', {'PartName': '/' + ARC_APP, 'ContentType': 'application/vnd.openxmlformats-officedocument.extended-properties+xml'})
SubElement(root, 'Override', {'PartName': '/' + ARC_CORE, 'ContentType': 'application/vnd.openxmlformats-package.core-properties+xml'})
SubElement(root, 'Override', {'PartName': '/' + ARC_SHARED_STRINGS, 'ContentType': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml'})
drawing_id = 1
chart_id = 1
for sheet_id, sheet in enumerate(workbook.worksheets):
SubElement(root, 'Override',
{'PartName': '/xl/worksheets/sheet%d.xml' % (sheet_id + 1),
'ContentType': 'application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml'})
if sheet._charts:
SubElement(root, 'Override',
{'PartName' : '/xl/drawings/drawing%d.xml' % (sheet_id + 1),
'ContentType' : 'application/vnd.openxmlformats-officedocument.drawing+xml'})
drawing_id += 1
for chart in sheet._charts:
SubElement(root, 'Override',
{'PartName' : '/xl/charts/chart%d.xml' % chart_id,
'ContentType' : 'application/vnd.openxmlformats-officedocument.drawingml.chart+xml'})
chart_id += 1
if chart._shapes:
SubElement(root, 'Override',
{'PartName' : '/xl/drawings/drawing%d.xml' % drawing_id,
'ContentType' : 'application/vnd.openxmlformats-officedocument.drawingml.chartshapes+xml'})
drawing_id += 1
return get_document_content(root)
def write_root_rels(workbook):
"""Write the relationships xml."""
root = Element('{%s}Relationships' % PKG_REL_NS)
relation_tag = '{%s}Relationship' % PKG_REL_NS
SubElement(root, relation_tag, {
'Id': 'rId1',
'Target': ARC_WORKBOOK,
'Type': '%s/officeDocument' % REL_NS
})
SubElement(
root, relation_tag, {
'Id': 'rId2',
'Target': ARC_CORE,
'Type': '%s/metadata/core-properties' % PKG_REL_NS
})
SubElement(root, relation_tag, {
'Id': 'rId3',
'Target': ARC_APP,
'Type': '%s/extended-properties' % REL_NS
})
if workbook.vba_archive is not None:
# See if there was a customUI relation and reuse its id
arc = fromstring(workbook.vba_archive.read(ARC_ROOT_RELS))
rels = arc.findall(relation_tag)
rId = None
for rel in rels:
if rel.get('Target') == ARC_CUSTOM_UI:
rId = rel.get('Id')
break
if rId is not None:
SubElement(root, relation_tag, {
'Id': rId,
'Target': ARC_CUSTOM_UI,
'Type': '%s' % CUSTOMUI_NS
})
return get_document_content(root)
def write_properties_core(properties):
"""Write the core properties to xml."""
root = Element(
'cp:coreProperties', {
'xmlns:cp': NAMESPACES['cp'],
'xmlns:xsi': NAMESPACES['xsi'],
'xmlns:dc': NAMESPACES['dc'],
'xmlns:dcterms': NAMESPACES['dcterms'],
'xmlns:dcmitype': NAMESPACES['dcmitype'],
})
SubElement(root, 'dc:creator').text = properties.creator
SubElement(root, 'cp:lastModifiedBy').text = properties.last_modified_by
SubElement(root, 'dcterms:created', \
{'xsi:type': 'dcterms:W3CDTF'}).text = \
datetime_to_W3CDTF(properties.created)
SubElement(root, 'dcterms:modified',
{'xsi:type': 'dcterms:W3CDTF'}).text = \
datetime_to_W3CDTF(properties.modified)
SubElement(root, 'dc:title').text = properties.title
SubElement(root, 'dc:description').text = properties.description
SubElement(root, 'dc:subject').text = properties.subject
SubElement(root, 'cp:keywords').text = properties.keywords
SubElement(root, 'cp:category').text = properties.category
return get_document_content(root)
