def _write_legend(self, chart):
if self.chart.show_legend:
legend = SubElement(chart, 'c:legend')
SubElement(legend, 'c:legendPos',
{'val': self.chart.legend.position})
SubElement(legend, 'c:layout')
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_number_formats(self):
number_format_table = {}
number_format_list = []
exceptions_list = []
num_fmt_id = 165 # start at a greatly higher value as any builtin can go
num_fmt_offset = 0
for style in self._style_list:
if not style.number_format in number_format_list :
number_format_list.append(style.number_format)
for number_format in number_format_list:
if number_format.is_builtin():
btin = number_format.builtin_format_id(number_format.format_code)
number_format_table[number_format] = btin
else:
number_format_table[number_format] = num_fmt_id + num_fmt_offset
num_fmt_offset += 1
exceptions_list.append(number_format)
num_fmts = SubElement(self._root, 'numFmts',
{'count':'%d' % len(exceptions_list)})
for number_format in exceptions_list :
SubElement(num_fmts, 'numFmt',
{'numFmtId':'%d' % number_format_table[number_format],
'formatCode':'%s' % number_format.format_code})
return number_format_table
def _write_cell_xfs(self, number_format_table, fonts_table, fills_table,
borders_table):
""" write styles combinations based on ids found in tables """
# writing the cellXfs
cell_xfs = SubElement(self._root, 'cellXfs',
{'count': '%d' % (len(self._style_list) + 1)})
# default
def _get_default_vals():
return dict(numFmtId='0',
fontId='0',
fillId='0',
xfId='0',
borderId='0')
SubElement(cell_xfs, 'xf', _get_default_vals())
for st in self._style_list:
vals = _get_default_vals()
if hash(st.font) != hash(style.DEFAULTS.font):
vals['fontId'] = fonts_table[hash(st.font)]
vals['applyFont'] = '1'
if hash(st.borders) != hash(style.DEFAULTS.borders):
vals['borderId'] = borders_table[hash(st.borders)]
vals['applyBorder'] = '1'
if hash(st.fill) != hash(style.DEFAULTS.fill):
vals['fillId'] = fills_table[hash(st.fill)]
vals['applyFillId'] = '1'
if st.number_format != style.DEFAULTS.number_format:
vals['numFmtId'] = '%d' % number_format_table[st.number_format]
vals['applyNumberFormat'] = '1'
if hash(st.alignment) != hash(style.DEFAULTS.alignment):
vals['applyAlignment'] = '1'
node = SubElement(cell_xfs, 'xf', vals)
if hash(st.alignment) != hash(style.DEFAULTS.alignment):
alignments = {}
for align_attr in ['horizontal', 'vertical']:
if hash(getattr(st.alignment, align_attr)) != hash(
getattr(style.DEFAULTS.alignment, align_attr)):
alignments[align_attr] = getattr(
st.alignment, align_attr)
if hash(st.alignment.wrap_text) != hash(
style.DEFAULTS.alignment.wrap_text):
alignments['wrapText'] = '1'
if st.alignment.text_rotation != 0:
alignments[
'textRotation'] = '%s' % st.alignment.text_rotation
SubElement(node, 'alignment', alignments)
def write_rels(self, chart_id, image_id):
root = Element(
'Relationships', {
'xmlns':
'http://schemas.openxmlformats.org/package/2006/relationships'
})
i = 0
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)
for j, img in enumerate(self._sheet._images):
attrs = {
'Id': 'rId%s' % (i + j + 1),
'Type':
'http://schemas.openxmlformats.org/officeDocument/2006/relationships/image',
'Target': '../media/image%s.png' % (image_id + j)
}
SubElement(root, 'Relationship', attrs)
return get_document_content(root)
def _write_print_settings(self):
settings = SubElement(self.root, '{%s}printSettings' % CHART_NS)
SubElement(settings, '{%s}headerFooter' % CHART_NS)
margins = dict([(k, safe_string(v)) for (k, v) in iteritems(self.chart.print_margins)])
SubElement(settings, '{%s}pageMargins' % CHART_NS, margins)
SubElement(settings, '{%s}pageSetup' % CHART_NS)
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)
# Defined names
defined_names = SubElement(root, 'definedNames')
# named ranges
for named_range in workbook.get_named_ranges():
name = SubElement(defined_names, 'definedName',
{'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
# autoFilter
for i, sheet in enumerate(workbook.worksheets):
#continue
auto_filter = sheet.auto_filter
if not auto_filter:
continue
name = SubElement(defined_names, 'definedName',
dict(name='_xlnm._FilterDatabase',
localSheetId=str(i),
hidden='1'))
name.text = "'%s'!%s" % (sheet.title.replace("'", "''"),
absolute_coordinate(auto_filter))
SubElement(root, 'calcPr', {'calcId': '124519', 'calcMode': 'auto',
'fullCalcOnLoad': '1'})
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_print_settings(self, root):
settings = SubElement(root, 'c:printSettings')
SubElement(settings, 'c:headerFooter')
margins = dict([(k, str(v)) for (k,v) in self.chart.print_margins.iteritems()])
SubElement(settings, 'c:pageMargins', margins)
SubElement(settings, 'c:pageSetup')
def _write_serial(self, node, serie, literal=False):
cache = serie._get_cache()
if isinstance(cache[0], basestring):
typ = 'str'
else:
typ = 'num'
if not literal:
if typ == 'num':
ref = SubElement(node, 'c:numRef')
else:
ref = SubElement(node, 'c:strRef')
SubElement(ref, 'c:f').text = serie._get_ref()
if typ == 'num':
data = SubElement(ref, 'c:numCache')
else:
data = SubElement(ref, 'c:strCache')
else:
data = SubElement(node, 'c:numLit')
if typ == 'num':
SubElement(data, 'c:formatCode').text = 'General'
if literal:
values = (1,)
else:
values = cache
SubElement(data, 'c:ptCount', {'val':str(len(values))})
for j, val in enumerate(values):
point = SubElement(data, 'c:pt', {'idx':str(j)})
SubElement(point, 'c:v').text = str(val)
def _write_fonts(self):
""" add fonts part to root
return {font.crc => index}
"""
fonts = SubElement(self._root, 'fonts')
# default
font_node = SubElement(fonts, 'font')
SubElement(font_node, 'sz', {'val':'11'})
SubElement(font_node, 'color', {'theme':'1'})
SubElement(font_node, 'name', {'val':'Calibri'})
SubElement(font_node, 'family', {'val':'2'})
SubElement(font_node, 'scheme', {'val':'minor'})
# others
table = {}
index = 1
for st in self._style_list:
if hash(st.font) != hash(style.DEFAULTS.font) and hash(st.font) not in table:
table[hash(st.font)] = str(index)
font_node = SubElement(fonts, 'font')
SubElement(font_node, 'sz', {'val':str(st.font.size)})
SubElement(font_node, 'color', {'rgb':str(st.font.color.index)})
SubElement(font_node, 'name', {'val':st.font.name})
SubElement(font_node, 'family', {'val':'2'})
SubElement(font_node, 'scheme', {'val':'minor'})
if st.font.bold:
SubElement(font_node, 'b')
if st.font.italic:
SubElement(font_node, 'i')
index += 1
fonts.attrib["count"] = str(index)
return table
def _write_style(self, node):
""" write style theme """
style = SubElement(node, 'cdr:style')
ln_ref = SubElement(style, 'a:lnRef', {
'xmlns:a': self.schema,
'idx': '2'
})
scheme_clr = SubElement(ln_ref, 'a:schemeClr', {'val': 'accent1'})
SubElement(scheme_clr, 'a:shade', {'val': '50000'})
fill_ref = SubElement(style, 'a:fillRef', {
'xmlns:a': self.schema,
'idx': '1'
})
SubElement(fill_ref, 'a:schemeClr', {'val': 'accent1'})
effect_ref = SubElement(style, 'a:effectRef', {
'xmlns:a': self.schema,
'idx': '0'
})
SubElement(effect_ref, 'a:schemeClr', {'val': 'accent1'})
font_ref = SubElement(style, 'a:fontRef', {
'xmlns:a': self.schema,
'idx': 'minor'
})
SubElement(font_ref, 'a:schemeClr', {'val': 'lt1'})
def _write_borders(self):
borders = SubElement(self._root, 'borders')
# default
border = SubElement(borders, 'border')
SubElement(border, 'left')
SubElement(border, 'right')
SubElement(border, 'top')
SubElement(border, 'bottom')
SubElement(border, 'diagonal')
# others
table = {}
index = 1
for st in self._style_list:
if hash(st.borders) != hash(style.DEFAULTS.borders) and hash(st.borders) not in table:
table[hash(st.borders)] = str(index)
border = SubElement(borders, 'border')
# caution: respect this order
for side in ('left', 'right', 'top', 'bottom', 'diagonal'):
obj = getattr(st.borders, side)
node = SubElement(border, side, {'style':obj.border_style})
SubElement(node, 'color', {'rgb':str(obj.color.index)})
index += 1
borders.attrib["count"] = str(index)
return table
def _write_cell_style(self):
cell_styles = SubElement(self._root, 'cellStyles', {'count': '1'})
cell_style = SubElement(cell_styles, 'cellStyle', {
'name': "Normal",
'xfId': "0",
'builtinId': "0"
})
def _write_cell_style_xfs(self):
cell_style_xfs = SubElement(self._root, 'cellStyleXfs', {'count': '1'})
xf = SubElement(cell_style_xfs, 'xf', {
'numFmtId': "0",
'fontId': "0",
'fillId': "0",
'borderId': "0"
})
def _write_anchor(self, node, drawing):
x, y, w, h = drawing.get_emu_dimensions()
if drawing.anchortype == "oneCell":
anchor = SubElement(node, '{%s}oneCellAnchor' % SHEET_DRAWING_NS)
xdrfrom = SubElement(anchor, '{%s}from' % SHEET_DRAWING_NS)
SubElement(xdrfrom, '{%s}col' %
SHEET_DRAWING_NS).text = safe_string(drawing.anchorcol)
SubElement(xdrfrom,
'{%s}colOff' % SHEET_DRAWING_NS).text = safe_string(x)
SubElement(xdrfrom, '{%s}row' %
SHEET_DRAWING_NS).text = safe_string(drawing.anchorrow)
SubElement(xdrfrom,
'{%s}rowOff' % SHEET_DRAWING_NS).text = safe_string(y)
else:
anchor = SubElement(node, '{%s}absoluteAnchor' % SHEET_DRAWING_NS)
SubElement(anchor, '{%s}pos' % SHEET_DRAWING_NS, {
'x': safe_string(x),
'y': safe_string(y)
})
SubElement(anchor, '{%s}ext' % SHEET_DRAWING_NS, {
'cx': safe_string(w),
'cy': safe_string(h)
})
return anchor
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_print_settings(self, root):
settings = SubElement(root, 'c:printSettings')
SubElement(settings, 'c:headerFooter')
try:
# Python 2
print_margins_items = iteritems(self.chart.print_margins)
except AttributeError:
# Python 3
print_margins_items = self.chart.print_margins.items()
margins = dict([(k, str(v)) for (k, v) in print_margins_items])
SubElement(settings, 'c:pageMargins', margins)
SubElement(settings, 'c:pageSetup')
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_serial(self, node, reference, literal=False):
is_ref = hasattr(reference, 'pos1')
data_type = reference.data_type
number_format = getattr(reference, 'number_format')
mapping = {'n':{'ref':'numRef', 'cache':'numCache'},
's':{'ref':'strRef', 'cache':'strCache'}}
if is_ref:
ref = SubElement(node, '{%s}%s' %(CHART_NS, mapping[data_type]['ref']))
SubElement(ref, '{%s}f' % CHART_NS).text = str(reference)
data = SubElement(ref, '{%s}%s' %(CHART_NS, mapping[data_type]['cache']))
values = reference.values
else:
data = SubElement(node, '{%s}numLit' % CHART_NS)
values = (1,)
if data_type == 'n':
SubElement(data, '{%s}formatCode' % CHART_NS).text = number_format or 'General'
SubElement(data, '{%s}ptCount' % CHART_NS, {'val':str(len(values))})
for j, val in enumerate(values):
point = SubElement(data, '{%s}pt' % CHART_NS, {'idx':str(j)})
val = safe_string(val)
SubElement(point, '{%s}v' % CHART_NS).text = val
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 _write_fonts(self):
""" add fonts part to root
return {font.crc => index}
"""
fonts = SubElement(self._root, 'fonts')
# default
font_node = SubElement(fonts, 'font')
SubElement(font_node, 'sz', {'val':'11'})
SubElement(font_node, 'color', {'theme':'1'})
SubElement(font_node, 'name', {'val':'Calibri'})
SubElement(font_node, 'family', {'val':'2'})
SubElement(font_node, 'scheme', {'val':'minor'})
# others
table = {}
index = 1
for st in self._style_list:
if hash(st.font) != hash(style.DEFAULTS.font) and hash(st.font) not in table:
table[hash(st.font)] = str(index)
font_node = SubElement(fonts, 'font')
SubElement(font_node, 'sz', {'val':str(st.font.size)})
if str(st.font.color.index).split(':')[0] == 'theme': # strip prefix theme if marked as such
if str(st.font.color.index).split(':')[2]:
SubElement(font_node, 'color', {'theme':str(st.font.color.index).split(':')[1],
'tint':str(st.font.color.index).split(':')[2]})
else:
SubElement(font_node, 'color', {'theme':str(st.font.color.index).split(':')[1]})
else:
SubElement(font_node, 'color', {'rgb':str(st.font.color.index)})
SubElement(font_node, 'name', {'val':st.font.name})
SubElement(font_node, 'family', {'val':'2'})
# Don't write the 'scheme' element because it appears to prevent
# the font name from being applied in Excel.
#SubElement(font_node, 'scheme', {'val':'minor'})
if st.font.bold:
SubElement(font_node, 'b')
if st.font.italic:
SubElement(font_node, 'i')
if st.font.underline == 'single':
SubElement(font_node, 'u')
index += 1
fonts.attrib["count"] = str(index)
return table
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 _write_text(self, node, shape):
""" write text in the shape """
tx_body = SubElement(node, 'cdr:txBody')
SubElement(tx_body, 'a:bodyPr', {'xmlns:a':self.schema, 'vertOverflow':'clip'})
SubElement(tx_body, 'a:lstStyle',
{'xmlns:a':self.schema})
p = SubElement(tx_body, 'a:p', {'xmlns:a':self.schema})
if shape.text:
r = SubElement(p, 'a:r')
rpr = SubElement(r, 'a:rPr', {'lang':'en-US'})
fill = SubElement(rpr, 'a:solidFill')
SubElement(fill, 'a:srgbClr', {'val':shape.text_color})
SubElement(r, 'a:t').text = shape.text
else:
SubElement(p, 'a:endParaRPr', {'lang':'en-US'})
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('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_error_bar(self, node, serie):
flag = {ErrorBar.PLUS_MINUS:'both',
ErrorBar.PLUS:'plus',
ErrorBar.MINUS:'minus'}
eb = SubElement(node, 'c:errBars')
SubElement(eb, 'c:errBarType', {'val':flag[serie.error_bar.type]})
SubElement(eb, 'c:errValType', {'val':'cust'})
plus = SubElement(eb, 'c:plus')
self._write_serial(plus, serie.error_bar.values,
literal=(serie.error_bar.type == ErrorBar.MINUS))
minus = SubElement(eb, 'c:minus')
self._write_serial(minus, serie.error_bar.values,
literal=(serie.error_bar.type == ErrorBar.PLUS))
def _write_table_styles(self):
table_styles = SubElement(
self._root, 'tableStyles', {
'count': '0',
'defaultTableStyle': 'TableStyleMedium9',
'defaultPivotStyle': 'PivotStyleLight16'
})
def _write_series(self, subchart):
for i, serie in enumerate(self.chart):
ser = SubElement(subchart, '{%s}ser' % CHART_NS)
SubElement(ser, '{%s}idx' % CHART_NS, {'val':safe_string(i)})
SubElement(ser, '{%s}order' % CHART_NS, {'val':safe_string(i)})
if serie.title:
tx = SubElement(ser, '{%s}tx' % CHART_NS)
SubElement(tx, '{%s}v' % CHART_NS).text = serie.title
if serie.color:
sppr = SubElement(ser, '{%s}spPr' % CHART_NS)
self._write_series_color(sppr, serie)
if serie.error_bar:
self._write_error_bar(ser, serie)
if serie.labels:
self._write_series_labels(ser, serie)
if serie.xvalues:
self._write_series_xvalues(ser, serie)
val = SubElement(ser, self.series_type)
self._write_serial(val, serie.reference)
def _write_text(self, node, shape):
""" write text in the shape """
tx_body = SubElement(node, '{%s}txBody' % CHART_DRAWING_NS)
SubElement(tx_body, '{%s}bodyPr' % DRAWING_NS,
{'vertOverflow': 'clip'})
SubElement(tx_body, '{%s}lstStyle' % DRAWING_NS)
p = SubElement(tx_body, '{%s}p' % DRAWING_NS)
if shape.text:
r = SubElement(p, '{%s}r' % DRAWING_NS)
rpr = SubElement(r, '{%s}rPr' % DRAWING_NS, {'lang': 'en-US'})
fill = SubElement(rpr, '{%s}solidFill' % DRAWING_NS)
SubElement(fill, '{%s}srgbClr' % DRAWING_NS,
{'val': shape.text_color})
SubElement(r, '{%s}t' % DRAWING_NS).text = shape.text
else:
SubElement(p, '{%s}endParaRPr' % DRAWING_NS, {'lang': 'en-US'})
def _write_error_bar(self, node, serie):
flag = {ErrorBar.PLUS_MINUS:'both',
ErrorBar.PLUS:'plus',
ErrorBar.MINUS:'minus'}
eb = SubElement(node, '{%s}errBars' % CHART_NS)
SubElement(eb, '{%s}errBarType' % CHART_NS, {'val':flag[serie.error_bar.type]})
SubElement(eb, '{%s}errValType' % CHART_NS, {'val':'cust'})
plus = SubElement(eb, '{%s}plus' % CHART_NS)
# apart from setting the type of data series the following has
# no effect on the writer
self._write_serial(plus, serie.error_bar.reference,
literal=(serie.error_bar.type == ErrorBar.MINUS))
minus = SubElement(eb, '{%s}minus' % CHART_NS)
self._write_serial(minus, serie.error_bar.reference,
literal=(serie.error_bar.type == ErrorBar.PLUS))
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_style(self, node):
""" write style theme """
style = SubElement(node, '{%s}style' % CHART_DRAWING_NS)
ln_ref = SubElement(style, '{%s}lnRef' % DRAWING_NS, {'idx': '2'})
scheme_clr = SubElement(ln_ref, '{%s}schemeClr' % DRAWING_NS,
{'val': 'accent1'})
SubElement(scheme_clr, '{%s}shade' % DRAWING_NS, {'val': '50000'})
fill_ref = SubElement(style, '{%s}fillRef' % DRAWING_NS, {'idx': '1'})
SubElement(fill_ref, '{%s}schemeClr' % DRAWING_NS, {'val': 'accent1'})
effect_ref = SubElement(style, '{%s}effectRef' % DRAWING_NS,
{'idx': '0'})
SubElement(effect_ref, '{%s}schemeClr' % DRAWING_NS,
{'val': 'accent1'})
font_ref = SubElement(style, '{%s}fontRef' % DRAWING_NS,
{'idx': 'minor'})
SubElement(font_ref, '{%s}schemeClr' % DRAWING_NS, {'val': 'lt1'})
def _write_borders(self):
borders = SubElement(self._root, 'borders')
# default
border = SubElement(borders, 'border')
SubElement(border, 'left')
SubElement(border, 'right')
SubElement(border, 'top')
SubElement(border, 'bottom')
SubElement(border, 'diagonal')
# others
table = {}
index = 1
for st in self._style_list:
if hash(st.borders) != hash(style.DEFAULTS.borders) and hash(st.borders) not in table:
table[hash(st.borders)] = str(index)
border = SubElement(borders, 'border')
# caution: respect this order
for side in ('left', 'right', 'top', 'bottom', 'diagonal'):
obj = getattr(st.borders, side)
if obj.border_style is None or obj.border_style == 'none':
node = SubElement(border, side)
attrs = {}
else:
node = SubElement(border, side, {'style':obj.border_style})
if str(obj.color.index).split(':')[0] == 'theme': # strip prefix theme if marked as such
if str(obj.color.index).split(':')[2]:
SubElement(node, 'color', {'theme':str(obj.color.index).split(':')[1],
'tint':str(obj.color.index).split(':')[2]})
else:
SubElement(node, 'color', {'theme':str(obj.color.index).split(':')[1]})
else:
SubElement(node, 'color', {'rgb':str(obj.color.index)})
index += 1
borders.attrib["count"] = str(index)
return table
def _write_fills(self):
fills = SubElement(self._root, 'fills', {'count':'2'})
fill = SubElement(fills, 'fill')
SubElement(fill, 'patternFill', {'patternType':'none'})
fill = SubElement(fills, 'fill')
SubElement(fill, 'patternFill', {'patternType':'gray125'})
table = {}
index = 2
for st in self._style_list:
if hash(st.fill) != hash(style.DEFAULTS.fill) and hash(st.fill) not in table:
table[hash(st.fill)] = str(index)
fill = SubElement(fills, 'fill')
if hash(st.fill.fill_type) != hash(style.DEFAULTS.fill.fill_type):
node = SubElement(fill, 'patternFill', {'patternType':st.fill.fill_type})
if hash(st.fill.start_color) != hash(style.DEFAULTS.fill.start_color):
if str(st.fill.start_color.index).split(':')[0] == 'theme': # strip prefix theme if marked as such
if str(st.fill.start_color.index).split(':')[2]:
SubElement(node, 'fgColor', {'theme':str(st.fill.start_color.index).split(':')[1],
'tint':str(st.fill.start_color.index).split(':')[2]})
else:
SubElement(node, 'fgColor', {'theme':str(st.fill.start_color.index).split(':')[1]})
else:
SubElement(node, 'fgColor', {'rgb':str(st.fill.start_color.index)})
if hash(st.fill.end_color) != hash(style.DEFAULTS.fill.end_color):
if str(st.fill.end_color.index).split(':')[0] == 'theme': # strip prefix theme if marked as such
if str(st.fill.end_color.index).split(':')[2]:
SubElement(node, 'bgColor', {'theme':str(st.fill.end_color.index).split(':')[1],
'tint':str(st.fill.end_color.index).split(':')[2]})
else:
SubElement(node, 'bgColor', {'theme':str(st.fill.end_color.index).split(':')[1]})
else:
SubElement(node, 'bgColor', {'rgb':str(st.fill.end_color.index)})
index += 1
fills.attrib["count"] = str(index)
return table
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_fills(self):
fills = SubElement(self._root, 'fills', {'count':'2'})
fill = SubElement(fills, 'fill')
SubElement(fill, 'patternFill', {'patternType':'none'})
fill = SubElement(fills, 'fill')
SubElement(fill, 'patternFill', {'patternType':'gray125'})
table = {}
index = 2
for st in self._style_list:
if hash(st.fill) != hash(style.DEFAULTS.fill) and hash(st.fill) not in table:
table[hash(st.fill)] = str(index)
fill = SubElement(fills, 'fill')
if hash(st.fill.fill_type) != hash(style.DEFAULTS.fill.fill_type):
node = SubElement(fill, 'patternFill', {'patternType':st.fill.fill_type})
if hash(st.fill.start_color) != hash(style.DEFAULTS.fill.start_color):
SubElement(node, 'fgColor', {'rgb':str(st.fill.start_color.index)})
if hash(st.fill.end_color) != hash(style.DEFAULTS.fill.end_color):
SubElement(node, 'bgColor', {'rgb':str(st.fill.start_color.index)})
index += 1
fills.attrib["count"] = str(index)
return table
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)