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)