def _write_cell_style(self):
cell_styles = SubElement(self._root, 'cellStyles', {'count': '1'})
SubElement(cell_styles, 'cellStyle', {
'name': "Normal",
'xfId': "0",
'builtinId': "0"
})
def _write_series_color_node(node, serie):
"""write series color
"""
# edge color
line = SubElement(node, '{%s}ln' % DRAWING_NS)
fill = SubElement(line, '{%s}solidFill' % DRAWING_NS)
SubElement(fill, '{%s}srgbClr' % DRAWING_NS, {'val': serie.color})
def write_sheetviews(worksheet):
views = Element('sheetViews')
sheetviewAttrs = {'workbookViewId': '0'}
if not worksheet.show_gridlines:
sheetviewAttrs['showGridLines'] = '0'
view = SubElement(views, 'sheetView', sheetviewAttrs)
selectionAttrs = {}
topLeftCell = worksheet.freeze_panes
if topLeftCell:
colName, row = coordinate_from_string(topLeftCell)
column = column_index_from_string(colName)
pane = 'topRight'
paneAttrs = {}
if column > 1:
paneAttrs['xSplit'] = str(column - 1)
if row > 1:
paneAttrs['ySplit'] = str(row - 1)
pane = 'bottomLeft'
if column > 1:
pane = 'bottomRight'
paneAttrs.update(dict(topLeftCell=topLeftCell,
activePane=pane,
state='frozen'))
view.append(Element('pane', paneAttrs))
selectionAttrs['pane'] = pane
if row > 1 and column > 1:
SubElement(view, 'selection', {'pane': 'topRight'})
SubElement(view, 'selection', {'pane': 'bottomLeft'})
selectionAttrs.update({'activeCell': worksheet.active_cell,
'sqref': worksheet.selected_cell})
SubElement(view, 'selection', selectionAttrs)
return views
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'})
return fills
def write_cell(worksheet, cell, styled=None):
coordinate = cell.coordinate
attributes = {'r': coordinate}
if styled:
attributes['s'] = '%d' % cell.style_id
if cell.data_type != 'f':
attributes['t'] = cell.data_type
value = cell._value
el = Element("c", attributes)
if value is None or value == "":
return el
if cell.data_type == 'f':
shared_formula = worksheet.formula_attributes.get(coordinate, {})
if (shared_formula.get('t') == 'shared'
and 'ref' not in shared_formula):
value = None
formula = SubElement(el, 'f', shared_formula)
if value is not None:
formula.text = value[1:]
value = None
if cell.data_type == 's':
value = worksheet.parent.shared_strings.add(value)
cell_content = SubElement(el, 'v')
if value is not None:
cell_content.text = safe_string(value)
return el
def _write_border(self, node, border):
"""Write the child elements for an individual border section"""
border_node = SubElement(node, 'border', dict(border))
for tag, elem in border.children:
side = SubElement(border_node, tag, dict(elem))
if elem.color is not None:
self._write_color(side, elem.color)
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, 1026):
shape = self._write_comment_shape(comment, i)
root.append(shape)
return tostring(root)
def write_chart(self):
"""write chart"""
chart = SubElement(self.root, '{%s}chart' % CHART_NS)
self._write_title(chart)
self._write_layout(chart)
self._write_legend(chart)
SubElement(chart, '{%s}plotVisOnly' % CHART_NS, {'val': '1'})
def _write_differential_styles(self):
dxfs = SubElement(self._root,
"dxfs",
count=str(len(self.wb._differential_styles)))
for fmt in self.wb._differential_styles:
dxfs.append(fmt.to_tree())
return dxfs
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_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.styles:
if st.font != DEFAULTS.font and st.font not in table:
font_node = SubElement(fonts, 'font')
table[st.font] = index
index += 1
self._write_font(font_node, st.font)
fonts.attrib["count"] = "%d" % index
return table
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.styles:
if st.font != DEFAULTS.font and st.font not in table:
font_node = SubElement(fonts, 'font')
table[st.font] = index
index += 1
self._write_font(font_node, st.font)
fonts.attrib["count"] = "%d" % index
return table
def to_tree(self, tagname=None):
if tagname is None:
tagname = self.tagname
attrs = dict(self)
el = Element(tagname, attrs)
for n in self.__nested__:
value = getattr(self, n)
if isinstance(value, tuple):
if hasattr(el, 'extend'):
el.extend(self._serialise_nested(value))
else: # py26 nolxml
for _ in self._serialise_nested(value):
el.append(_)
elif value:
SubElement(el, n, val=safe_string(value))
for child in self.__elements__:
obj = getattr(self, child)
if isinstance(obj, tuple):
for v in obj:
if hasattr(v, 'to_tree'):
el.append(v.to_tree(tagname=child))
else:
SubElement(el, child).text = v
elif obj is not None:
el.append(obj.to_tree(tagname=child))
return el
def _write_legend(self, chart):
if self.chart.show_legend:
legend = SubElement(chart, '{%s}legend' % CHART_NS)
SubElement(legend, '{%s}legendPos' % CHART_NS,
{'val': self.chart.legend.position})
SubElement(legend, '{%s}layout' % CHART_NS)
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 idx, comment in enumerate(self.comments, 1026):
shape = _shape_factory()
col, row = coordinate_from_string(comment.ref)
row -= 1
column = column_index_from_string(col) - 1
shape.set('id', "_x0000_s%04d" % idx)
client_data = shape.find("{%s}ClientData" % excelns)
client_data.find("{%s}Row" % excelns).text = str(row)
client_data.find("{%s}Column" % excelns).text = str(column)
root.append(shape)
return tostring(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 etree_write_cell(xf, worksheet, cell, styled=None):
value, attributes = _set_attributes(cell, styled)
el = Element("c", attributes)
if value is None or value == "":
xf.write(el)
return
if cell.data_type == 'f':
shared_formula = worksheet.formula_attributes.get(cell.coordinate, {})
formula = SubElement(el, 'f', shared_formula)
if value is not None:
formula.text = value[1:]
value = None
if cell.data_type == 's':
inline_string = SubElement(el, 'is')
text = SubElement(inline_string, 't')
text.text = value
whitespace(text)
else:
cell_content = SubElement(el, 'v')
if value is not None:
cell_content.text = safe_string(value)
xf.write(el)
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_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 st.borders != DEFAULTS.borders and st.borders not in table:
table[st.borders] = 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)
else:
node = SubElement(border, side, {'style':obj.border_style})
self._unpack_color(node, obj.color.index)
index += 1
borders.attrib["count"] = str(index)
return table
def _write_borders(self):
"""Write the child elements for an individual border section"""
borders_node = SubElement(self._root,
'borders',
count="%d" % len(self.borders))
for border in self.borders:
borders_node.append(border.to_tree())
def write_external_link(links):
"""Serialise links to ranges in a single external worbook"""
root = Element("{%s}externalLink" % SHEET_MAIN_NS)
book = SubElement(root, "{%s}externalBook" % SHEET_MAIN_NS, {'{%s}id' % REL_NS:'rId1'})
external_ranges = SubElement(book, "{%s}definedNames" % SHEET_MAIN_NS)
for l in links:
external_ranges.append(Element("{%s}definedName" % SHEET_MAIN_NS, dict(l)))
return root
def write_external_link(links):
"""Serialise links to ranges in a single external worbook"""
root = Element("{%s}externalLink" % SHEET_MAIN_NS)
book = SubElement(root, "{%s}externalBook" % SHEET_MAIN_NS, {'{%s}id' % REL_NS:'rId1'})
external_ranges = SubElement(book, "{%s}definedNames" % SHEET_MAIN_NS)
for l in links:
external_ranges.append(Element("{%s}definedName" % SHEET_MAIN_NS, dict(l)))
return root
def _write_cell_style_xfs(self):
cell_style_xfs = SubElement(self._root, 'cellStyleXfs', {'count': '1'})
SubElement(cell_style_xfs, 'xf', {
'numFmtId': "0",
'fontId': "0",
'fillId': "0",
'borderId': "0"
})
def _write_style_names(self):
styles = self.wb._named_styles
cell_styles = SubElement(self._root, 'cellStyles', count=str(len(styles)))
for idx, style in enumerate(styles.values()):
attrs = dict(style)
attrs['xfId'] = str(idx)
SubElement(cell_styles, 'cellStyle', attrs)
def _write_number_formats(self):
node = SubElement(self._root,
'numFmts',
count="%d" % len(self.number_formats))
for idx, nf in enumerate(self.number_formats, 164):
SubElement(node, 'numFmt', {
'numFmtId': '%d' % idx,
'formatCode': '%s' % nf
})
def write_header_footer(worksheet):
header = worksheet.header_footer.getHeader()
footer = worksheet.header_footer.getFooter()
if header or footer:
tag = Element('headerFooter')
if header:
SubElement(tag, 'oddHeader').text = header
if footer:
SubElement(tag, 'oddFooter').text = footer
return tag
def _write_series(self, subchart):
for i, series 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 series.title:
tx = SubElement(ser, '{%s}tx' % CHART_NS)
SubElement(tx, '{%s}v' % CHART_NS).text = series.title
if isinstance(self, LineChart):
marker = SubElement(ser, "{%s}marker" % CHART_NS)
SubElement(marker, "{%s}symbol" % CHART_NS, val=safe_string(series.marker))
if series.color:
sppr = SubElement(ser, '{%s}spPr' % CHART_NS)
self._write_series_color(sppr, series)
if series.error_bar:
self._write_error_bar(ser, series)
if series.labels:
self._write_series_labels(ser, series)
if series.xvalues:
self._write_series_xvalues(ser, series)
val = SubElement(ser, self.series_type)
self._write_serial(val, series.reference)
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 st.fill != DEFAULTS.fill and st.fill not in table:
table[st.fill] = index
fill = SubElement(fills, 'fill')
if st.fill.fill_type != DEFAULTS.fill.fill_type:
node = SubElement(fill, 'patternFill', {'patternType':st.fill.fill_type})
if st.fill.start_color != DEFAULTS.fill.start_color:
self._unpack_color(node, st.fill.start_color.index, 'fgColor')
if st.fill.end_color != DEFAULTS.fill.end_color:
self._unpack_color(node, st.fill.end_color.index, 'bgColor')
index += 1
fills.attrib["count"] = str(index)
return table
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_colors(self):
"""
Workbook contains a different colour index.
"""
if self.wb._colors == COLOR_INDEX:
return
cols = SubElement(self._root, "colors")
rgb = SubElement(cols, "indexedColors")
for color in self.wb._colors:
SubElement(rgb, "rgbColor", rgb=color)
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_workbook(workbook):
"""Write the core workbook xml."""
root = Element('{%s}workbook' % SHEET_MAIN_NS)
if LXML:
_nsmap = {'r':REL_NS}
root = Element('{%s}workbook' % SHEET_MAIN_NS, nsmap=_nsmap)
wb_props = {}
if workbook.code_name is not None:
wb_props['codeName'] = workbook.code_name
SubElement(root, '{%s}workbookPr' % SHEET_MAIN_NS, wb_props)
# book views
book_views = SubElement(root, '{%s}bookViews' % SHEET_MAIN_NS)
SubElement(book_views, '{%s}workbookView' % SHEET_MAIN_NS,
{'activeTab': '%d' % workbook._active_sheet_index}
)
# worksheets
sheets = SubElement(root, '{%s}sheets' % SHEET_MAIN_NS)
for idx, sheet in enumerate(workbook.worksheets + workbook.chartsheets, 1):
sheet_node = SubElement(
sheets, '{%s}sheet' % SHEET_MAIN_NS,
{'name': sheet.title, 'sheetId': '%d' % idx,
'{%s}id' % REL_NS: 'rId%d' % idx})
if not sheet.sheet_state == 'visible':
if len(workbook._sheets) == 1:
raise ValueError("The only worksheet of a workbook cannot be hidden")
sheet_node.set('state', sheet.sheet_state)
# external references
if getattr(workbook, '_external_links', []):
external_references = SubElement(root, '{%s}externalReferences' % SHEET_MAIN_NS)
# need to match a counter with a workbook's relations
counter = len(workbook.worksheets) + 3 # strings, styles, theme
if workbook.vba_archive:
counter += 1
for idx, _ in enumerate(workbook._external_links, counter+1):
ext = Element("{%s}externalReference" % SHEET_MAIN_NS, {"{%s}id" % REL_NS:"rId%d" % idx})
external_references.append(ext)
# Defined names
defined_names = SubElement(root, '{%s}definedNames' % SHEET_MAIN_NS)
_write_defined_names(workbook, defined_names)
# Defined names -> autoFilter
for i, sheet in enumerate(workbook.worksheets):
auto_filter = sheet.auto_filter.ref
if not auto_filter:
continue
name = SubElement(
defined_names, '{%s}definedName' % SHEET_MAIN_NS,
dict(name='_xlnm._FilterDatabase', localSheetId=str(i), hidden='1'))
name.text = "'%s'!%s" % (sheet.title.replace("'", "''"),
absolute_coordinate(auto_filter))
SubElement(root, '{%s}calcPr' % SHEET_MAIN_NS,
{'calcId': '124519', 'fullCalcOnLoad': '1'})
return tostring(root)
def write_workbook(workbook):
"""Write the core workbook xml."""
root = Element("{%s}workbook" % SHEET_MAIN_NS)
if LXML:
_nsmap = {"r": REL_NS}
root = Element("{%s}workbook" % SHEET_MAIN_NS, nsmap=_nsmap)
wb_props = {}
if workbook.code_name is not None:
wb_props["codeName"] = workbook.code_name
SubElement(root, "{%s}workbookPr" % SHEET_MAIN_NS, wb_props)
# book views
book_views = SubElement(root, "{%s}bookViews" % SHEET_MAIN_NS)
SubElement(book_views, "{%s}workbookView" % SHEET_MAIN_NS, {"activeTab": "%d" % workbook._active_sheet_index})
# worksheets
sheets = SubElement(root, "{%s}sheets" % SHEET_MAIN_NS)
for i, sheet in enumerate(workbook.worksheets, 1):
sheet_node = SubElement(
sheets,
"{%s}sheet" % SHEET_MAIN_NS,
{"name": sheet.title, "sheetId": "%d" % i, "{%s}id" % REL_NS: "rId%d" % i},
)
if not sheet.sheet_state == Worksheet.SHEETSTATE_VISIBLE:
if len(workbook.worksheets) == 1:
raise ValueError("The only worksheet of a workbook cannot be hidden")
sheet_node.set("state", sheet.sheet_state)
# external references
if getattr(workbook, "_external_links", []):
external_references = SubElement(root, "{%s}externalReferences" % SHEET_MAIN_NS)
# need to match a counter with a workbook's relations
counter = len(workbook.worksheets) + 3 # strings, styles, theme
if workbook.vba_archive:
counter += 1
for idx, _ in enumerate(workbook._external_links, counter + 1):
ext = Element("{%s}externalReference" % SHEET_MAIN_NS, {"{%s}id" % REL_NS: "rId%d" % idx})
external_references.append(ext)
# Defined names
defined_names = SubElement(root, "{%s}definedNames" % SHEET_MAIN_NS)
_write_defined_names(workbook, defined_names)
# Defined names -> autoFilter
for i, sheet in enumerate(workbook.worksheets):
auto_filter = sheet.auto_filter.ref
if not auto_filter:
continue
name = SubElement(
defined_names,
"{%s}definedName" % SHEET_MAIN_NS,
dict(name="_xlnm._FilterDatabase", localSheetId=str(i), hidden="1"),
)
name.text = "'%s'!%s" % (sheet.title.replace("'", "''"), absolute_coordinate(auto_filter))
SubElement(root, "{%s}calcPr" % SHEET_MAIN_NS, {"calcId": "124519", "fullCalcOnLoad": "1"})
return tostring(root)
def write_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 tostring(root)
def _write_dxfs(self):
if self._style_properties and 'dxf_list' in self._style_properties:
dxfs = SubElement(
self._root, 'dxfs',
{'count': str(len(self._style_properties['dxf_list']))})
for d in self._style_properties['dxf_list']:
dxf = SubElement(dxfs, 'dxf')
if 'font' in d and d['font'] is not None:
font_node = SubElement(dxf, 'font')
if d['font'].color is not None:
self._write_color(font_node, d['font'].color)
ConditionalElement(font_node, 'b', d['font'].bold, 'val')
ConditionalElement(font_node, 'i', d['font'].italic, 'val')
ConditionalElement(font_node, 'u',
d['font'].underline != 'none',
{'val': d['font'].underline})
ConditionalElement(font_node, 'strike',
d['font'].strikethrough)
if 'fill' in d:
f = d['fill']
fill = SubElement(dxf, 'fill')
if f.fill_type:
node = SubElement(fill, 'patternFill',
{'patternType': f.fill_type})
else:
node = SubElement(fill, 'patternFill')
if f.start_color != DEFAULTS.fill.start_color:
self._write_color(node, f.start_color, 'fgColor')
if f.end_color != DEFAULTS.fill.end_color:
self._write_color(node, f.end_color, 'bgColor')
if 'border' in d:
borders = d['border']
border = SubElement(dxf, 'border')
# caution: respect this order
for side in ('left', 'right', 'top', 'bottom'):
obj = getattr(borders, side)
if obj.border_style is None or obj.border_style == 'none':
node = SubElement(border, side)
else:
node = SubElement(border, side,
{'style': obj.border_style})
self._write_color(node, obj.color)
else:
dxfs = SubElement(self._root, 'dxfs', {'count': '0'})
return dxfs
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, 1):
sheet_node = SubElement(
sheets, '{%s}sheet' % SHEET_MAIN_NS,
{'name': sheet.title, 'sheetId': '%d' % i,
'{%s}id' % REL_NS: 'rId%d' % i })
if not sheet.sheet_state == Worksheet.SHEETSTATE_VISIBLE:
if len(workbook.worksheets) == 1:
raise ValueError("The only worksheet of a workbook cannot be hidden")
sheet_node.set('state', sheet.sheet_state)
# external references
if getattr(workbook, '_external_links', []):
external_references = SubElement(root, '{%s}externalReferences' % SHEET_MAIN_NS)
# need to match a counter with a workbook's relations
counter = len(workbook.worksheets) + 3 # strings, styles, theme
if workbook.vba_archive:
counter += 1
for idx, _ in enumerate(workbook._external_links, counter+1):
ext = Element("{%s}externalReference" % SHEET_MAIN_NS, {"{%s}id" % REL_NS:"rId%d" % idx})
external_references.append(ext)
# Defined names
defined_names = SubElement(root, '{%s}definedNames' % SHEET_MAIN_NS)
_write_defined_names(workbook, defined_names)
# Defined names -> autoFilter
for i, sheet in enumerate(workbook.worksheets):
auto_filter = sheet.auto_filter.ref
if not auto_filter:
continue
name = SubElement(
defined_names, '{%s}definedName' % SHEET_MAIN_NS,
dict(name='_xlnm._FilterDatabase', localSheetId=str(i), hidden='1'))
name.text = "'%s'!%s" % (sheet.title.replace("'", "''"),
absolute_coordinate(auto_filter))
SubElement(root, '{%s}calcPr' % SHEET_MAIN_NS,
{'calcId': '124519', 'calcMode': 'auto', 'fullCalcOnLoad': '1'})
return tostring(root)
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_string_table(string_table):
"""Write the string table xml."""
out = BytesIO()
with xmlfile(out) as xf:
with xf.element("sst", xmlns=SHEET_MAIN_NS, uniqueCount="%d" % len(string_table)):
for key in string_table:
el = Element('si')
text = SubElement(el, 't')
text.text = key
if key.strip() != key:
text.set(PRESERVE_SPACE, 'preserve')
xf.write(el)
return out.getvalue()
def _write_alignment(self, node, alignment):
values = dict(alignment)
if values.get('horizontal', 'general') == 'general':
del values['horizontal']
if values.get('vertical', 'bottom') == 'bottom':
del values['vertical']
SubElement(node, 'alignment', values)
def etree_write_cell(xf, worksheet, cell, styled=None):
coordinate = cell.coordinate
attributes = {'r': coordinate}
if styled:
attributes['s'] = '%d' % cell.style_id
if cell.data_type != 'f':
attributes['t'] = cell.data_type
value = cell._value
if cell.data_type == "d":
if cell.parent.parent.iso_dates:
if isinstance(value, timedelta):
value = days_to_time(value)
value = value.isoformat()
else:
attributes['t'] = "n"
value = to_excel(value, worksheet.parent.epoch)
if cell._comment is not None:
comment = CommentRecord.from_cell(cell)
worksheet._comments.append(comment)
el = Element("c", attributes)
if value is None or value == "":
xf.write(el)
return
if cell.data_type == 'f':
shared_formula = worksheet.formula_attributes.get(coordinate, {})
formula = SubElement(el, 'f', shared_formula)
if value is not None:
formula.text = value[1:]
value = None
if cell.data_type == 's':
value = worksheet.parent.shared_strings.add(value)
cell_content = SubElement(el, 'v')
if value is not None:
cell_content.text = safe_string(value)
if cell.hyperlink:
worksheet._hyperlinks.append(cell.hyperlink)
xf.write(el)
def _write_cell_styles(self):
""" write styles combinations based on ids found in tables """
# writing the cellXfs
cell_xfs = SubElement(self._root, 'cellXfs',
count='%d' % len(self.wb._cell_styles))
for style in self.wb._cell_styles:
node = style.to_tree()
cell_xfs.append(node)
if style.applyAlignment:
al = self.wb._alignments[style.alignmentId]
el = al.to_tree()
node.append(el)
if style.applyProtection:
prot = self.wb._protections[style.protectionId]
el = prot.to_tree()
node.append(el)
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.styles:
if st.fill != DEFAULTS.fill and st.fill not in table:
table[st.fill] = index
node = SubElement(fills, 'fill')
if isinstance(st.fill, PatternFill):
self._write_pattern_fill(node, st.fill)
elif isinstance(st.fill, GradientFill):
self._write_gradient_fill(node, st.fill)
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.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_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.styles:
if st.border != DEFAULTS.border and st.border not in table:
table[st.border] = index
self._write_border(borders, st.border)
index += 1
borders.attrib["count"] = str(index)
return table
def _write_cell_styles(self):
""" write styles combinations based on ids found in tables """
# writing the cellXfs
cell_xfs = SubElement(self._root, 'cellXfs',
count='%d' % len(self.styles))
# default
def _get_default_vals():
return dict(numFmtId='0', fontId='0', fillId='0',
xfId='0', borderId='0')
for st in self.styles:
vals = _get_default_vals()
if st.font != 0:
vals['fontId'] = "%d" % (st.font)
vals['applyFont'] = '1'
if st.border != 0:
vals['borderId'] = "%d" % (st.border)
vals['applyBorder'] = '1'
if st.fill != 0:
vals['fillId'] = "%d" % (st.fill)
vals['applyFill'] = '1'
if st.number_format != 0:
vals['numFmtId'] = '%d' % st.number_format
vals['applyNumberFormat'] = '1'
node = SubElement(cell_xfs, 'xf', vals)
if st.alignment != 0:
node.set("applyProtection", '1')
al = self.alignments[st.alignment]
el = al.to_tree()
node.append(el)
if st.protection != 0:
node.set('applyProtection', '1')
prot = self.protections[st.protection]
el = prot.to_tree()
node.append(el)
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 st.font != DEFAULTS.font and st.font not in table:
table[st.font] = index
font_node = SubElement(fonts, 'font')
SubElement(font_node, 'sz', {'val':str(st.font.size)})
self._unpack_color(font_node, 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'})
ConditionalElement(font_node, "b", st.font.bold)
ConditionalElement(font_node, "i", st.font.italic)
ConditionalElement(font_node, "u", st.font.underline == 'single')
index += 1
fonts.attrib["count"] = str(index)
return table
def write_cell(worksheet, cell, styled=None):
coordinate = cell.coordinate
attributes = {'r': coordinate}
if styled:
attributes['s'] = '%d' % cell.style_id
if cell.data_type != 'f':
attributes['t'] = cell.data_type
value = cell._value
if cell._comment is not None:
comment = CommentRecord._adapted(cell.comment, cell.coordinate)
worksheet._comments.append(comment)
el = Element("c", attributes)
if value is None or value == "":
return el
if cell.data_type == 'f':
shared_formula = worksheet.formula_attributes.get(coordinate, {})
formula = SubElement(el, 'f', shared_formula)
if value is not None:
formula.text = value[1:]
value = None
if cell.data_type == 's':
value = worksheet.parent.shared_strings.add(value)
cell_content = SubElement(el, 'v')
if value is not None:
cell_content.text = safe_string(value)
if cell.hyperlink:
worksheet._hyperlinks.append(cell.hyperlink)
return el
def write_autofilter(worksheet):
auto_filter = worksheet.auto_filter
if auto_filter.ref is None:
return
el = Element('autoFilter', ref=auto_filter.ref)
if (auto_filter.filter_columns
or auto_filter.sort_conditions):
for col_id, filter_column in sorted(auto_filter.filter_columns.items()):
fc = SubElement(el, 'filterColumn', colId=str(col_id))
attrs = {}
if filter_column.blank:
attrs = {'blank': '1'}
flt = SubElement(fc, 'filters', attrs)
for val in filter_column.vals:
flt.append(Element('filter', val=val))
if auto_filter.sort_conditions:
srt = SubElement(el, 'sortState', ref=auto_filter.ref)
for sort_condition in auto_filter.sort_conditions:
sort_attr = {'ref': sort_condition.ref}
if sort_condition.descending:
sort_attr['descending'] = '1'
srt.append(Element('sortCondtion', sort_attr))
return el
def _write_borders(self):
"""Write the child elements for an individual border section"""
borders_node = SubElement(self._root, 'borders', count="%d" % len(self.borders))
for border in self.borders:
borders_node.append(border.to_tree())
def _write_fills(self):
fills_node = SubElement(self._root, 'fills', count="%d" % len(self.fills))
for fill in self.fills:
fills_node.append(fill.to_tree())
def _write_fonts(self):
fonts_node = SubElement(self._root, 'fonts', count="%d" % len(self.fonts))
for font in self.fonts:
fonts_node.append(font.to_tree())
def _write_differential_styles(self):
dxfs = SubElement(self._root, "dxfs", count=str(len(self.wb._differential_styles)))
for fmt in self.wb._differential_styles:
dxfs.append(fmt.to_tree())
return dxfs
def _write_conditional_styles(self):
dxfs = SubElement(self._root, "dxfs", count=str(len(self.wb.conditional_formats)))
for fmt in self.wb.conditional_formats:
dxfs.append(fmt.to_tree())
return dxfs
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.ref
if not auto_filter:
continue
name = SubElement(
defined_names, '{%s}definedName' % SHEET_MAIN_NS,
dict(name='_xlnm._FilterDatabase', localSheetId=str(i), hidden='1'))
name.text = "'%s'!%s" % (sheet.title.replace("'", "''"),
absolute_coordinate(auto_filter))
SubElement(root, '{%s}calcPr' % SHEET_MAIN_NS,
{'calcId': '124519', 'calcMode': 'auto', 'fullCalcOnLoad': '1'})
return get_document_content(root)
