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 write_worksheet_rels(worksheet, drawing_id, comments_id):
"""Write relationships for the worksheet to xml."""
root = Element('{%s}Relationships' % PKG_REL_NS)
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, '{%s}Relationship' % PKG_REL_NS, attrs)
if worksheet._charts or worksheet._images:
attrs = {'Id' : 'rId1',
'Type' : '%s/drawing' % REL_NS,
'Target' : '../drawings/drawing%s.xml' % drawing_id }
SubElement(root, '{%s}Relationship' % PKG_REL_NS, attrs)
if worksheet._comment_count > 0:
# there's only one comments sheet per worksheet,
# so there's no reason to call the Id rIdx
attrs = {'Id': 'comments',
'Type': COMMENTS_NS,
'Target' : '../comments%s.xml' % comments_id}
SubElement(root, '{%s}Relationship' % PKG_REL_NS, attrs)
attrs = {'Id': 'commentsvml',
'Type': VML_NS,
'Target': '../drawings/commentsDrawing%s.vml' % comments_id}
SubElement(root, '{%s}Relationship' % PKG_REL_NS, attrs)
return get_document_content(root)
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 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 test_write_comments_vml():
ws = _create_ws()[0]
cw = CommentWriter(ws)
reference_file = os.path.join(DATADIR, 'writer', 'expected',
'commentsDrawing1.vml')
content = cw.write_comments_vml()
with open(reference_file) as expected:
correct = fromstring(expected.read())
check = fromstring(content)
correct_ids = []
correct_coords = []
check_ids = []
check_coords = []
for i in correct.findall("{%s}shape" % vmlns):
correct_ids.append(i.attrib["id"])
row = i.find("{%s}ClientData" % excelns).find("{%s}Row" %
excelns).text
col = i.find("{%s}ClientData" % excelns).find("{%s}Column" %
excelns).text
correct_coords.append((row, col))
# blank the data we are checking separately
i.attrib["id"] = "0"
i.find("{%s}ClientData" % excelns).find("{%s}Row" %
excelns).text = "0"
i.find("{%s}ClientData" % excelns).find("{%s}Column" %
excelns).text = "0"
for i in check.findall("{%s}shape" % vmlns):
check_ids.append(i.attrib["id"])
row = i.find("{%s}ClientData" % excelns).find("{%s}Row" %
excelns).text
col = i.find("{%s}ClientData" % excelns).find("{%s}Column" %
excelns).text
check_coords.append((row, col))
# blank the data we are checking separately
i.attrib["id"] = "0"
i.find("{%s}ClientData" % excelns).find("{%s}Row" %
excelns).text = "0"
i.find("{%s}ClientData" % excelns).find("{%s}Column" %
excelns).text = "0"
assert set(correct_coords) == set(check_coords)
assert set(correct_ids) == set(check_ids)
diff = compare_xml(get_document_content(correct),
get_document_content(check))
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 get_document_content(root)
def write(self):
""" write a chart """
SubElement(self.root, '{%s}lang' % CHART_NS, {'val':self.chart.lang})
self._write_chart()
self._write_print_settings()
self._write_shapes()
return get_document_content(self.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_write_comments():
ws = _create_ws()[0]
reference_file = os.path.join(DATADIR, 'writer', 'expected',
'comments1.xml')
cw = CommentWriter(ws)
content = cw.write_comments()
with open(reference_file) as expected:
correct = fromstring(expected.read())
check = fromstring(content)
# check top-level elements have the same name
for i, j in zip(correct.getchildren(), check.getchildren()):
assert i.tag == j.tag
correct_comments = correct.find('{%s}commentList' %
SHEET_MAIN_NS).getchildren()
check_comments = check.find('{%s}commentList' %
SHEET_MAIN_NS).getchildren()
correct_authors = correct.find('{%s}authors' %
SHEET_MAIN_NS).getchildren()
check_authors = check.find('{%s}authors' % SHEET_MAIN_NS).getchildren()
# replace author ids with author names
for i in correct_comments:
i.attrib["authorId"] = correct_authors[int(
i.attrib["authorId"])].text
for i in check_comments:
i.attrib["authorId"] = check_authors[int(
i.attrib["authorId"])].text
# sort the comment list
correct_comments.sort(key=lambda tag: tag.attrib["ref"])
check_comments.sort(key=lambda tag: tag.attrib["ref"])
correct.find('{%s}commentList' % SHEET_MAIN_NS)[:] = correct_comments
check.find('{%s}commentList' % SHEET_MAIN_NS)[:] = check_comments
# sort the author list
correct_authors.sort(key=lambda tag: tag.text)
check_authors.sort(key=lambda tag: tag.text)
correct.find('{%s}authors' % SHEET_MAIN_NS)[:] = correct_authors
check.find('{%s}authors' % SHEET_MAIN_NS)[:] = check_authors
diff = compare_xml(get_document_content(correct),
get_document_content(check))
assert diff is None, diff
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 write_table(self):
number_format_table = self._write_number_formats()
fonts_table = self._write_fonts()
fills_table = self._write_fills()
borders_table = self._write_borders()
self._write_cell_style_xfs()
self._write_cell_xfs(number_format_table, fonts_table, fills_table,
borders_table)
self._write_cell_style()
self._write_dxfs()
self._write_table_styles()
return get_document_content(xml_node=self._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_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_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_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_theme():
"""Write the theme xml."""
xml_node = fromstring(
'<?xml version="1.0" ?>\n'
'<a:theme xmlns:a="http://schemas.openxmlformats.org/'
'drawingml/2006/main" name="Office Theme">'
'<a:themeElements>'
'<a:clrScheme name="Office">'
'<a:dk1><a:sysClr val="windowText" lastClr="000000"/></a:dk1>'
'<a:lt1><a:sysClr val="window" lastClr="FFFFFF"/></a:lt1>'
'<a:dk2><a:srgbClr val="1F497D"/></a:dk2>'
'<a:lt2><a:srgbClr val="EEECE1"/></a:lt2>'
'<a:accent1><a:srgbClr val="4F81BD"/></a:accent1>'
'<a:accent2><a:srgbClr val="C0504D"/></a:accent2>'
'<a:accent3><a:srgbClr val="9BBB59"/></a:accent3>'
'<a:accent4><a:srgbClr val="8064A2"/></a:accent4>'
'<a:accent5><a:srgbClr val="4BACC6"/></a:accent5>'
'<a:accent6><a:srgbClr val="F79646"/></a:accent6>'
'<a:hlink><a:srgbClr val="0000FF"/></a:hlink>'
'<a:folHlink><a:srgbClr val="800080"/></a:folHlink>'
'</a:clrScheme>'
'<a:fontScheme name="Office">'
'<a:majorFont>'
'<a:latin typeface="Cambria"/>'
'<a:ea typeface=""/>'
'<a:cs typeface=""/>'
'<a:font script="Jpan" typeface="ＭＳ Ｐゴシック"/>'
'<a:font script="Hang" typeface="맑은 고딕"/>'
'<a:font script="Hans" typeface="宋体"/>'
'<a:font script="Hant" typeface="新細明體"/>'
'<a:font script="Arab" typeface="Times New Roman"/>'
'<a:font script="Hebr" typeface="Times New Roman"/>'
'<a:font script="Thai" typeface="Tahoma"/>'
'<a:font script="Ethi" typeface="Nyala"/>'
'<a:font script="Beng" typeface="Vrinda"/>'
'<a:font script="Gujr" typeface="Shruti"/>'
'<a:font script="Khmr" typeface="MoolBoran"/>'
'<a:font script="Knda" typeface="Tunga"/>'
'<a:font script="Guru" typeface="Raavi"/>'
'<a:font script="Cans" typeface="Euphemia"/>'
'<a:font script="Cher" typeface="Plantagenet Cherokee"/>'
'<a:font script="Yiii" typeface="Microsoft Yi Baiti"/>'
'<a:font script="Tibt" typeface="Microsoft Himalaya"/>'
'<a:font script="Thaa" typeface="MV Boli"/>'
'<a:font script="Deva" typeface="Mangal"/>'
'<a:font script="Telu" typeface="Gautami"/>'
'<a:font script="Taml" typeface="Latha"/>'
'<a:font script="Syrc" typeface="Estrangelo Edessa"/>'
'<a:font script="Orya" typeface="Kalinga"/>'
'<a:font script="Mlym" typeface="Kartika"/>'
'<a:font script="Laoo" typeface="DokChampa"/>'
'<a:font script="Sinh" typeface="Iskoola Pota"/>'
'<a:font script="Mong" typeface="Mongolian Baiti"/>'
'<a:font script="Viet" typeface="Times New Roman"/>'
'<a:font script="Uigh" typeface="Microsoft Uighur"/>'
'</a:majorFont>'
'<a:minorFont>'
'<a:latin typeface="Calibri"/>'
'<a:ea typeface=""/>'
'<a:cs typeface=""/>'
'<a:font script="Jpan" typeface="ＭＳ Ｐゴシック"/>'
'<a:font script="Hang" typeface="맑은 고딕"/>'
'<a:font script="Hans" typeface="宋体"/>'
'<a:font script="Hant" typeface="新細明體"/>'
'<a:font script="Arab" typeface="Arial"/>'
'<a:font script="Hebr" typeface="Arial"/>'
'<a:font script="Thai" typeface="Tahoma"/>'
'<a:font script="Ethi" typeface="Nyala"/>'
'<a:font script="Beng" typeface="Vrinda"/>'
'<a:font script="Gujr" typeface="Shruti"/>'
'<a:font script="Khmr" typeface="DaunPenh"/>'
'<a:font script="Knda" typeface="Tunga"/>'
'<a:font script="Guru" typeface="Raavi"/>'
'<a:font script="Cans" typeface="Euphemia"/>'
'<a:font script="Cher" typeface="Plantagenet Cherokee"/>'
'<a:font script="Yiii" typeface="Microsoft Yi Baiti"/>'
'<a:font script="Tibt" typeface="Microsoft Himalaya"/>'
'<a:font script="Thaa" typeface="MV Boli"/>'
'<a:font script="Deva" typeface="Mangal"/>'
'<a:font script="Telu" typeface="Gautami"/>'
'<a:font script="Taml" typeface="Latha"/>'
'<a:font script="Syrc" typeface="Estrangelo Edessa"/>'
'<a:font script="Orya" typeface="Kalinga"/>'
'<a:font script="Mlym" typeface="Kartika"/>'
'<a:font script="Laoo" typeface="DokChampa"/>'
'<a:font script="Sinh" typeface="Iskoola Pota"/>'
'<a:font script="Mong" typeface="Mongolian Baiti"/>'
'<a:font script="Viet" typeface="Arial"/>'
'<a:font script="Uigh" typeface="Microsoft Uighur"/>'
'</a:minorFont>'
'</a:fontScheme>'
'<a:fmtScheme name="Office">'
'<a:fillStyleLst>'
'<a:solidFill><a:schemeClr val="phClr"/></a:solidFill>'
'<a:gradFill rotWithShape="1"><a:gsLst>'
'<a:gs pos="0"><a:schemeClr val="phClr"><a:tint val="50000"/>'
'<a:satMod val="300000"/></a:schemeClr></a:gs>'
'<a:gs pos="35000"><a:schemeClr val="phClr"><a:tint val="37000"/>'
'<a:satMod val="300000"/></a:schemeClr></a:gs>'
'<a:gs pos="100000"><a:schemeClr val="phClr"><a:tint val="15000"/>'
'<a:satMod val="350000"/></a:schemeClr></a:gs></a:gsLst>'
'<a:lin ang="16200000" scaled="1"/></a:gradFill>'
'<a:gradFill rotWithShape="1"><a:gsLst>'
'<a:gs pos="0"><a:schemeClr val="phClr"><a:shade val="51000"/>'
'<a:satMod val="130000"/></a:schemeClr></a:gs>'
'<a:gs pos="80000"><a:schemeClr val="phClr"><a:shade val="93000"/>'
'<a:satMod val="130000"/></a:schemeClr></a:gs>'
'<a:gs pos="100000"><a:schemeClr val="phClr">'
'<a:shade val="94000"/>'
'<a:satMod val="135000"/></a:schemeClr></a:gs></a:gsLst>'
'<a:lin ang="16200000" scaled="0"/></a:gradFill></a:fillStyleLst>'
'<a:lnStyleLst>'
'<a:ln w="9525" cap="flat" cmpd="sng" algn="ctr">'
'<a:solidFill><a:schemeClr val="phClr"><a:shade val="95000"/>'
'<a:satMod val="105000"/></a:schemeClr></a:solidFill>'
'<a:prstDash val="solid"/></a:ln>'
'<a:ln w="25400" cap="flat" cmpd="sng" algn="ctr"><a:solidFill>'
'<a:schemeClr val="phClr"/></a:solidFill>'
'<a:prstDash val="solid"/></a:ln>'
'<a:ln w="38100" cap="flat" cmpd="sng" algn="ctr"><a:solidFill>'
'<a:schemeClr val="phClr"/></a:solidFill>'
'<a:prstDash val="solid"/></a:ln></a:lnStyleLst>'
'<a:effectStyleLst><a:effectStyle><a:effectLst>'
'<a:outerShdw blurRad="40000" dist="20000" dir="5400000" '
'rotWithShape="0"><a:srgbClr val="000000">'
'<a:alpha val="38000"/></a:srgbClr></a:outerShdw></a:effectLst>'
'</a:effectStyle><a:effectStyle><a:effectLst>'
'<a:outerShdw blurRad="40000" dist="23000" dir="5400000" '
'rotWithShape="0"><a:srgbClr val="000000">'
'<a:alpha val="35000"/></a:srgbClr></a:outerShdw></a:effectLst>'
'</a:effectStyle><a:effectStyle><a:effectLst>'
'<a:outerShdw blurRad="40000" dist="23000" dir="5400000" '
'rotWithShape="0"><a:srgbClr val="000000">'
'<a:alpha val="35000"/></a:srgbClr></a:outerShdw></a:effectLst>'
'<a:scene3d><a:camera prst="orthographicFront">'
'<a:rot lat="0" lon="0" rev="0"/></a:camera>'
'<a:lightRig rig="threePt" dir="t">'
'<a:rot lat="0" lon="0" rev="1200000"/></a:lightRig>'
'</a:scene3d><a:sp3d><a:bevelT w="63500" h="25400"/>'
'</a:sp3d></a:effectStyle></a:effectStyleLst>'
'<a:bgFillStyleLst><a:solidFill><a:schemeClr val="phClr"/>'
'</a:solidFill><a:gradFill rotWithShape="1"><a:gsLst>'
'<a:gs pos="0"><a:schemeClr val="phClr"><a:tint val="40000"/>'
'<a:satMod val="350000"/></a:schemeClr></a:gs>'
'<a:gs pos="40000"><a:schemeClr val="phClr"><a:tint val="45000"/>'
'<a:shade val="99000"/><a:satMod val="350000"/>'
'</a:schemeClr></a:gs>'
'<a:gs pos="100000"><a:schemeClr val="phClr">'
'<a:shade val="20000"/><a:satMod val="255000"/>'
'</a:schemeClr></a:gs></a:gsLst>'
'<a:path path="circle">'
'<a:fillToRect l="50000" t="-80000" r="50000" b="180000"/>'
'</a:path>'
'</a:gradFill><a:gradFill rotWithShape="1"><a:gsLst>'
'<a:gs pos="0"><a:schemeClr val="phClr"><a:tint val="80000"/>'
'<a:satMod val="300000"/></a:schemeClr></a:gs>'
'<a:gs pos="100000"><a:schemeClr val="phClr">'
'<a:shade val="30000"/><a:satMod val="200000"/>'
'</a:schemeClr></a:gs></a:gsLst>'
'<a:path path="circle">'
'<a:fillToRect l="50000" t="50000" r="50000" b="50000"/></a:path>'
'</a:gradFill></a:bgFillStyleLst></a:fmtScheme>'
'</a:themeElements>'
'<a:objectDefaults/><a:extraClrSchemeLst/>'
'</a:theme>')
return get_document_content(xml_node)
def __repr__(self):
root = Element("{%s}Relationships" % PKG_REL_NS)
body = SubElement(root, "{%s}Relationship" % PKG_REL_NS, self.__dict__)
return get_document_content(root)
def write_workbook(workbook):
"""Write the core workbook xml."""
root = Element('{%s}workbook' % SHEET_MAIN_NS)
SubElement(
root, '{%s}fileVersion' % SHEET_MAIN_NS, {
'appName': 'xl',
'lastEdited': '4',
'lowestEdited': '4',
'rupBuild': '4505'
})
SubElement(root, '{%s}workbookPr' % SHEET_MAIN_NS, {
'defaultThemeVersion': '124226',
'codeName': 'ThisWorkbook'
})
# book views
book_views = SubElement(root, '{%s}bookViews' % SHEET_MAIN_NS)
SubElement(
book_views, '{%s}workbookView' % SHEET_MAIN_NS, {
'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, '{%s}sheets' % SHEET_MAIN_NS)
for i, sheet in enumerate(workbook.worksheets):
sheet_node = SubElement(
sheets, '{%s}sheet' % SHEET_MAIN_NS, {
'name': sheet.title,
'sheetId': '%d' % (i + 1),
'{%s}id' % REL_NS: 'rId%d' % (i + 1)
})
if not sheet.sheet_state == sheet.SHEETSTATE_VISIBLE:
sheet_node.set('state', sheet.sheet_state)
# Defined names
defined_names = SubElement(root, '{%s}definedNames' % SHEET_MAIN_NS)
# Defined names -> named ranges
for named_range in workbook.get_named_ranges():
name = SubElement(defined_names, '{%s}definedName' % SHEET_MAIN_NS,
{'name': named_range.name})
if named_range.scope:
name.set('localSheetId',
'%s' % workbook.get_index(named_range.scope))
if isinstance(named_range, NamedRange):
# as there can be many cells in one range, generate the list of ranges
dest_cells = []
for worksheet, range_name in named_range.destinations:
dest_cells.append("'%s'!%s" % (worksheet.title.replace(
"'", "''"), absolute_coordinate(range_name)))
# finally write the cells list
name.text = ','.join(dest_cells)
else:
assert isinstance(named_range, NamedRangeContainingValue)
name.text = named_range.value
# Defined names -> autoFilter
for i, sheet in enumerate(workbook.worksheets):
#continue
auto_filter = sheet.auto_filter
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',
'calcMode': 'auto',
'fullCalcOnLoad': '1'
})
return get_document_content(root)
def write_content_types(workbook):
"""Write the content-types xml."""
seen = set()
if workbook.vba_archive:
root = fromstring(workbook.vba_archive.read(ARC_CONTENT_TYPES))
for elem in root.findall('{%s}Override' % CONTYPES_NS):
seen.add(elem.attrib['PartName'])
else:
root = Element('{%s}Types' % CONTYPES_NS)
for setting_type, name, content_type in static_content_types_config:
if setting_type == 'Override':
tag = '{%s}Override' % CONTYPES_NS
attrib = {'PartName': '/' + name}
else:
tag = '{%s}Default' % CONTYPES_NS
attrib = {'Extension': name}
attrib['ContentType'] = content_type
SubElement(root, tag, attrib)
drawing_id = 1
chart_id = 1
comments_id = 1
for sheet_id, sheet in enumerate(workbook.worksheets):
name = '/xl/worksheets/sheet%d.xml' % (sheet_id + 1)
if name not in seen:
SubElement(
root, '{%s}Override' % CONTYPES_NS, {
'PartName':
name,
'ContentType':
'application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml'
})
if sheet._charts or sheet._images:
name = '/xl/drawings/drawing%d.xml' % drawing_id
if name not in seen:
SubElement(
root, '{%s}Override' % CONTYPES_NS, {
'PartName':
name,
'ContentType':
'application/vnd.openxmlformats-officedocument.drawing+xml'
})
drawing_id += 1
for chart in sheet._charts:
name = '/xl/charts/chart%d.xml' % chart_id
if name not in seen:
SubElement(
root, '{%s}Override' % CONTYPES_NS, {
'PartName':
name,
'ContentType':
'application/vnd.openxmlformats-officedocument.drawingml.chart+xml'
})
chart_id += 1
if chart._shapes:
name = '/xl/drawings/drawing%d.xml' % drawing_id
if name not in seen:
SubElement(
root, '{%s}Override' % CONTYPES_NS, {
'PartName':
name,
'ContentType':
'application/vnd.openxmlformats-officedocument.drawingml.chartshapes+xml'
})
drawing_id += 1
if sheet._comment_count > 0:
SubElement(
root, '{%s}Override' % CONTYPES_NS, {
'PartName':
'/xl/comments%d.xml' % comments_id,
'ContentType':
'application/vnd.openxmlformats-officedocument.spreadsheetml.comments+xml'
})
comments_id += 1
return get_document_content(root)