def _write_cell_xfs(self, number_format_node, fonts_node, fills_node, borders_node):
""" 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')
_fonts = IndexedList()
_fills = IndexedList()
_borders = IndexedList()
_custom_fmts = IndexedList()
for st in self.styles:
vals = _get_default_vals()
font = st.font
if font != DEFAULTS.font:
if font not in _fonts:
font_id = _fonts.add(font)
self._write_font(fonts_node, st.font)
else:
font_id = _fonts.index(font)
vals['fontId'] = "%d" % (font_id + 1) # There is one default font
vals['applyFont'] = '1'
border = st.border
if st.border != DEFAULTS.border:
if border not in _borders:
border_id = _borders.add(border)
self._write_border(borders_node, border)
else:
border_id = _borders.index(border)
vals['borderId'] = "%d" % (border_id + 1) # There is one default border
vals['applyBorder'] = '1'
fill = st.fill
if fill != DEFAULTS.fill:
if fill not in _fills:
fill_id = _fills.add(st.fill)
fill_node = SubElement(fills_node, 'fill')
if isinstance(fill, PatternFill):
self._write_pattern_fill(fill_node, fill)
elif isinstance(fill, GradientFill):
self._write_gradient_fill(fill_node, fill)
else:
fill_id = _fills.index(fill)
vals['fillId'] = "%d" % (fill_id + 2) # There are two default fills
vals['applyFill'] = '1'
nf = st.number_format
if nf != DEFAULTS.number_format:
fmt_id = numbers.builtin_format_id(nf)
if fmt_id is None:
if nf not in _custom_fmts:
fmt_id = _custom_fmts.add(nf) + 165
self._write_number_format(number_format_node, fmt_id, nf)
else:
fmt_id = _custom_fmts.index(nf)
vals['numFmtId'] = '%d' % fmt_id
vals['applyNumberFormat'] = '1'
if st.alignment != DEFAULTS.alignment:
vals['applyAlignment'] = '1'
if st.protection != DEFAULTS.protection:
vals['applyProtection'] = '1'
node = SubElement(cell_xfs, 'xf', vals)
if st.alignment != DEFAULTS.alignment:
self._write_alignment(node, st.alignment)
if st.protection != DEFAULTS.protection:
self._write_protection(node, st.protection)
fonts_node.attrib["count"] = "%d" % (len(_fonts) + 1)
borders_node.attrib["count"] = "%d" % (len(_borders) + 1)
fills_node.attrib["count"] = "%d" % (len(_fills) + 2)
number_format_node.attrib['count'] = '%d' % len(_custom_fmts)
def _write_cell_xfs(self, number_format_node, fonts_node, fills_node,
borders_node):
""" 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')
_fonts = IndexedList()
_fills = IndexedList()
_borders = IndexedList()
_custom_fmts = IndexedList()
for st in self.styles:
vals = _get_default_vals()
font = st.font
if font != DEFAULTS.font:
if font not in _fonts:
font_id = _fonts.add(font)
self._write_font(fonts_node, st.font)
else:
font_id = _fonts.index(font)
vals['fontId'] = "%d" % (font_id + 1
) # There is one default font
vals['applyFont'] = '1'
border = st.border
if st.border != DEFAULTS.border:
if border not in _borders:
border_id = _borders.add(border)
self._write_border(borders_node, border)
else:
border_id = _borders.index(border)
vals['borderId'] = "%d" % (border_id + 1
) # There is one default border
vals['applyBorder'] = '1'
fill = st.fill
if fill != DEFAULTS.fill:
if fill not in _fills:
fill_id = _fills.add(st.fill)
fill_node = SubElement(fills_node, 'fill')
if isinstance(fill, PatternFill):
self._write_pattern_fill(fill_node, fill)
elif isinstance(fill, GradientFill):
self._write_gradient_fill(fill_node, fill)
else:
fill_id = _fills.index(fill)
vals['fillId'] = "%d" % (fill_id + 2
) # There are two default fills
vals['applyFill'] = '1'
nf = st.number_format
if nf != DEFAULTS.number_format:
fmt_id = numbers.builtin_format_id(nf)
if fmt_id is None:
if nf not in _custom_fmts:
fmt_id = _custom_fmts.add(nf) + 165
self._write_number_format(number_format_node, fmt_id,
nf)
else:
fmt_id = _custom_fmts.index(nf)
vals['numFmtId'] = '%d' % fmt_id
vals['applyNumberFormat'] = '1'
if st.alignment != DEFAULTS.alignment:
vals['applyAlignment'] = '1'
if st.protection != DEFAULTS.protection:
vals['applyProtection'] = '1'
node = SubElement(cell_xfs, 'xf', vals)
if st.alignment != DEFAULTS.alignment:
self._write_alignment(node, st.alignment)
if st.protection != DEFAULTS.protection:
self._write_protection(node, st.protection)
fonts_node.attrib["count"] = "%d" % (len(_fonts) + 1)
borders_node.attrib["count"] = "%d" % (len(_borders) + 1)
fills_node.attrib["count"] = "%d" % (len(_fills) + 2)
number_format_node.attrib['count'] = '%d' % len(_custom_fmts)
class CommentWriter(object):
def extract_comments(self):
"""
extract list of comments and authors
"""
for _coord, cell in iteritems(self.sheet._cells):
if cell.comment is not None:
self.authors.add(cell.comment.author)
self.comments.append(cell.comment)
def __init__(self, sheet):
self.sheet = sheet
self.authors = IndexedList()
self.comments = []
self.extract_comments()
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': '%d' % self.authors.index(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 tostring(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 tostring(root)
def _write_comment_shape(self, root, comment, idx):
# get zero-indexed coordinates of the comment
row = comment._parent.row - 1
column = column_index_from_string(comment._parent.column) - 1
style = ("position:absolute; margin-left:59.25pt;"
"margin-top:1.5pt;width:%(width)s;height:%(height)s;"
"z-index:1;visibility:hidden") % {'height': comment._height,
'width': comment._width}
attrs = {
"id": "_x0000_s%s" % (idx + 1026),
"type": "#_x0000_t202",
"style": style,
"fillcolor": "#ffffe1",
"{%s}insetmode" % officens: "auto"
}
shape = SubElement(root, "{%s}shape" % vmlns, attrs)
SubElement(shape, "{%s}fill" % vmlns,
{"color2": "#ffffe1"})
SubElement(shape, "{%s}shadow" % vmlns,
{"color": "black", "obscured": "t"})
SubElement(shape, "{%s}path" % vmlns,
{"{%s}connecttype" % officens: "none"})
textbox = SubElement(shape, "{%s}textbox" % vmlns,
{"style": "mso-direction-alt:auto"})
SubElement(textbox, "div", {"style": "text-align:left"})
client_data = SubElement(shape, "{%s}ClientData" % excelns,
{"ObjectType": "Note"})
SubElement(client_data, "{%s}MoveWithCells" % excelns)
SubElement(client_data, "{%s}SizeWithCells" % excelns)
SubElement(client_data, "{%s}AutoFill" % excelns).text = "False"
SubElement(client_data, "{%s}Row" % excelns).text = str(row)
SubElement(client_data, "{%s}Column" % excelns).text = str(column)
