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, 1026):
shape = self._write_comment_shape(comment, i)
root.append(shape)
return tostring(root)
def _write_comment_shape(self, comment, idx):
# get zero-indexed coordinates of the comment
col, row = coordinate_from_string(comment._parent.coordinate)
row -= 1
column = column_index_from_string(col) - 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%04d" % idx ,
"type": "#_x0000_t202",
"style": style,
"fillcolor": "#ffffe1",
"{%s}insetmode" % officens: "auto"
}
shape = Element("{%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 = "%d" % row
SubElement(client_data, "{%s}Column" % excelns).text = "%d" % column
return shape
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, 1026):
shape = self._write_comment_shape(comment, i)
root.append(shape)
return tostring(root)
def _write_comment_shape(self, comment, idx):
# get zero-indexed coordinates of the comment
col, row = coordinate_from_string(comment._parent.coordinate)
row -= 1
column = column_index_from_string(col) - 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%04d" % idx,
"type": "#_x0000_t202",
"style": style,
"fillcolor": "#ffffe1",
"{%s}insetmode" % officens: "auto"
}
shape = Element("{%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 = "%d" % row
SubElement(client_data, "{%s}Column" % excelns).text = "%d" % column
return shape
class ExcelRenderer(mistune.Renderer):
"""
Renders a Markdown formatted text to an OOXML worksheet.
Each block item (paragraph, headline, code...) is rendered in a
separate cell, while inline elements (including newlines) are
placed into cells as rich text.
Some features of Markdown are not supported:
- Tables
- Images
- HTML tags
- Footnotes (for the moment)
- List item continuation after a nested list (is considered as a new
list item)
Links are output in text as footnotes and target is placed at the end,
after a blank cell.
When using this renderer, the markdown processor will output an
instance of :py:class:`Collector`. Use its :py:func:`~Collector.render`
function to get the result in a worksheet.
.. sourcecode:: python
from openpyxl import Workbook
renderer = ExcelRenderer()
markdown = mistune.Markdown(renderer=renderer)
collector = markdown(text)
wb = Workbook()
ws = wb.active
collector.render(ws)
wb.save(<output path>)
"""
def __init__(self, *args, **kwargs):
super().__init__(*args, **kwargs)
self.links = IndexedList()
def placeholder(self):
return Collector(self)
# ==== Block elements ====
def _get_block(self, bucket):
if bucket.inline is not None:
return BlockCollector(bucket.inline)
else:
return bucket.block
def block_code(self, code, language=None):
#print("block_code: ", code, language)
return self._get_block(code).nest("code")
def block_quote(self, text):
#print("block_quote: ", text)
return self._get_block(text).nest("quote")
def block_html(self, html):
#print("block_html: ", html)
return None
def header(self, text, level, raw=None):
#print("header: ", text, level, raw)
return self._get_block(text).nest("h" + str(level))
def hrule(self):
#print("hrule: ")
return BlockCollector(InlineCollector("")).nest("hrule")
def list(self, body, ordered=True):
#print("list: ", body, ordered)
return self._get_block(body).nest("list")
def list_item(self, text):
#print("list_item: ", text)
return self._get_block(text).nest("list_item")
def paragraph(self, text):
#print("paragraph: ", text)
return self._get_block(text).nest("paragraph")
def table(self, header, body):
#print("table: ", header, body)
return None
def table_row(self, content):
#print("table_row", content)
return None
def table_cell(self, content, **flags):
#print("table_cell: ", content, flags)
return None
# ==== Inline elements ====
def autolink(self, link, is_email=False):
#print("autolink: ", link, is_email)
return link.inline.apply_marker("l")
def codespan(self, text):
#print("codespan: ", text)
return InlineCollector(text).apply_marker("c")
def double_emphasis(self, text):
return text.inline.apply_marker("i").apply_marker("b")
def emphasis(self, text):
#print("emphasis: ", text)
return text.inline.apply_marker("i")
def image(self, src, title, alt_text):
return None
def linebreak(self):
return InlineCollector("\n")
def newline(self):
return None
def link(self, link, title, content):
#print("link: ", link, title, content)
self.links.add(link)
idx = self.links.index(link) + 1
return content.inline.apply_marker("l").append("[{:d}]".format(idx))
def strikethrough(self, text):
#print("strikethrough: ", text)
return text.inline.apply_marker("s")
def text(self, text):
#print("text: ", text)
return InlineCollector(text)
def inline_html(self, text):
return text.inline
def terminate(self):
"""
Called by the collector when the render function is called
"""
collector = Collector(self)
if len(self.links) > 0:
# Blank line
collector += BlockCollector(InlineCollector(""))
# Each link in a special cell with format
# "[<idx>] <link>"
for idx, link in enumerate(self.links, start=1):
content = InlineCollector("[{:d}] ".format(idx))
content += InlineCollector(link).apply_marker("l")
block = BlockCollector(content).nest("link")
collector += block
# Reset links
self.links = IndexedList()
return collector