def _write_worksheets(self, archive, style_writer):
drawing_id = 1
chart_id = 1
image_id = 1
shape_id = 1
comments_id = 1
for i, sheet in enumerate(self.workbook.worksheets):
archive.writestr(
PACKAGE_WORKSHEETS + '/sheet%d.xml' % (i + 1),
write_worksheet(sheet, self.shared_strings,
style_writer.styles))
if (sheet._charts or sheet._images or sheet.relationships
or sheet._comment_count > 0):
archive.writestr(
PACKAGE_WORKSHEETS + '/_rels/sheet%d.xml.rels' % (i + 1),
write_worksheet_rels(sheet, drawing_id, comments_id))
if sheet._charts or sheet._images:
dw = DrawingWriter(sheet)
archive.writestr(
PACKAGE_DRAWINGS + '/drawing%d.xml' % drawing_id,
dw.write())
archive.writestr(PACKAGE_DRAWINGS +
'/_rels/drawing%d.xml.rels' % drawing_id,
dw.write_rels(
chart_id,
image_id)) # TODO remove this dependency
drawing_id += 1
for chart in sheet._charts:
cw = ChartWriter(chart)
archive.writestr(
PACKAGE_CHARTS + '/chart%d.xml' % chart_id, cw.write())
if chart._shapes:
archive.writestr(
PACKAGE_CHARTS +
'/_rels/chart%d.xml.rels' % chart_id,
cw.write_rels(
drawing_id)) # TODO remove this dependency
sw = ShapeWriter(chart._shapes)
archive.writestr(
PACKAGE_DRAWINGS + '/drawing%d.xml' % drawing_id,
sw.write(shape_id)) # TODO remove this dependency
shape_id += len(chart._shapes)
drawing_id += 1
chart_id += 1
image_id = self._write_images(sheet._images, archive, image_id)
if sheet._comment_count > 0:
cw = CommentWriter(sheet)
archive.writestr(PACKAGE_XL + '/comments%d.xml' % comments_id,
cw.write_comments())
archive.writestr(
PACKAGE_XL +
'/drawings/commentsDrawing%d.vml' % comments_id,
cw.write_comments_vml())
comments_id += 1
def test_write_comments_vml():
ws = _create_ws()[0]
cw = CommentWriter(ws)
reference_file = os.path.join(DATADIR, 'writer', 'expected',
'commentsDrawing1.vml')
content = cw.write_comments_vml()
with open(reference_file) as expected:
correct = fromstring(expected.read())
check = fromstring(content)
correct_ids = []
correct_coords = []
check_ids = []
check_coords = []
for i in correct.findall("{%s}shape" % vmlns):
correct_ids.append(i.attrib["id"])
row = i.find("{%s}ClientData" % excelns).find("{%s}Row" %
excelns).text
col = i.find("{%s}ClientData" % excelns).find("{%s}Column" %
excelns).text
correct_coords.append((row, col))
# blank the data we are checking separately
i.attrib["id"] = "0"
i.find("{%s}ClientData" % excelns).find("{%s}Row" %
excelns).text = "0"
i.find("{%s}ClientData" % excelns).find("{%s}Column" %
excelns).text = "0"
for i in check.findall("{%s}shape" % vmlns):
check_ids.append(i.attrib["id"])
row = i.find("{%s}ClientData" % excelns).find("{%s}Row" %
excelns).text
col = i.find("{%s}ClientData" % excelns).find("{%s}Column" %
excelns).text
check_coords.append((row, col))
# blank the data we are checking separately
i.attrib["id"] = "0"
i.find("{%s}ClientData" % excelns).find("{%s}Row" %
excelns).text = "0"
i.find("{%s}ClientData" % excelns).find("{%s}Column" %
excelns).text = "0"
assert set(correct_coords) == set(check_coords)
assert set(correct_ids) == set(check_ids)
diff = compare_xml(get_document_content(correct),
get_document_content(check))
assert diff is None, diff
def test_comment_writer_init():
ws, comment1, comment2, comment3 = _create_ws()
cw = CommentWriter(ws)
assert set(cw.authors) == set(["author", "author2", "author3"])
assert cw.author_to_id[cw.authors[0]] == "0"
assert cw.author_to_id[cw.authors[1]] == "1"
assert cw.author_to_id[cw.authors[2]] == "2"
assert set(cw.comments) == set([comment1, comment2, comment3])
def test_write_comments():
ws = _create_ws()[0]
reference_file = os.path.join(DATADIR, 'writer', 'expected',
'comments1.xml')
cw = CommentWriter(ws)
content = cw.write_comments()
with open(reference_file) as expected:
correct = fromstring(expected.read())
check = fromstring(content)
# check top-level elements have the same name
for i, j in zip(correct.getchildren(), check.getchildren()):
assert i.tag == j.tag
correct_comments = correct.find('{%s}commentList' %
SHEET_MAIN_NS).getchildren()
check_comments = check.find('{%s}commentList' %
SHEET_MAIN_NS).getchildren()
correct_authors = correct.find('{%s}authors' %
SHEET_MAIN_NS).getchildren()
check_authors = check.find('{%s}authors' % SHEET_MAIN_NS).getchildren()
# replace author ids with author names
for i in correct_comments:
i.attrib["authorId"] = correct_authors[int(
i.attrib["authorId"])].text
for i in check_comments:
i.attrib["authorId"] = check_authors[int(
i.attrib["authorId"])].text
# sort the comment list
correct_comments.sort(key=lambda tag: tag.attrib["ref"])
check_comments.sort(key=lambda tag: tag.attrib["ref"])
correct.find('{%s}commentList' % SHEET_MAIN_NS)[:] = correct_comments
check.find('{%s}commentList' % SHEET_MAIN_NS)[:] = check_comments
# sort the author list
correct_authors.sort(key=lambda tag: tag.text)
check_authors.sort(key=lambda tag: tag.text)
correct.find('{%s}authors' % SHEET_MAIN_NS)[:] = correct_authors
check.find('{%s}authors' % SHEET_MAIN_NS)[:] = check_authors
diff = compare_xml(get_document_content(correct),
get_document_content(check))
assert diff is None, diff
def test_write_comments_vml(datadir):
datadir.chdir()
ws = _create_ws()[0]
cw = CommentWriter(ws)
content = cw.write_comments_vml()
with open('commentsDrawing1.vml') as expected:
correct = fromstring(expected.read())
check = fromstring(content)
correct_ids = []
correct_coords = []
check_ids = []
check_coords = []
for i in correct.findall("{%s}shape" % vmlns):
correct_ids.append(i.attrib["id"])
row = i.find("{%s}ClientData" % excelns).find("{%s}Row" % excelns).text
col = i.find("{%s}ClientData" % excelns).find("{%s}Column" %
excelns).text
correct_coords.append((row, col))
# blank the data we are checking separately
i.attrib["id"] = "0"
i.find("{%s}ClientData" % excelns).find("{%s}Row" % excelns).text = "0"
i.find("{%s}ClientData" % excelns).find("{%s}Column" %
excelns).text = "0"
for i in check.findall("{%s}shape" % vmlns):
check_ids.append(i.attrib["id"])
row = i.find("{%s}ClientData" % excelns).find("{%s}Row" % excelns).text
col = i.find("{%s}ClientData" % excelns).find("{%s}Column" %
excelns).text
check_coords.append((row, col))
# blank the data we are checking separately
i.attrib["id"] = "0"
i.find("{%s}ClientData" % excelns).find("{%s}Row" % excelns).text = "0"
i.find("{%s}ClientData" % excelns).find("{%s}Column" %
excelns).text = "0"
assert set(correct_coords) == set(check_coords)
assert set(correct_ids) == set(check_ids)
diff = compare_xml(tostring(correct), tostring(check))
assert diff is None, diff
def test_write_only_cell_vml(datadir):
from openpyxl.xml.functions import Element, tostring
datadir.chdir()
wb = Workbook()
ws = wb.active
cell = ws['A1'] # write-only cells are always A1
cell.comment = Comment("Some text", "an author")
cell.col_idx = 2
cell.row = 2
writer = CommentWriter(ws)
root = Element("root")
xml = writer._write_comment_shape(cell.comment, 1)
xml = tostring(xml)
expected = """
<v:shape
xmlns:v="urn:schemas-microsoft-com:vml"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:o="urn:schemas-microsoft-com:office:office"
fillcolor="#ffffe1"
id="_x0000_s0001"
style="position:absolute; margin-left:59.25pt;margin-top:1.5pt;width:108pt;height:59.25pt;z-index:1;visibility:hidden"
type="#_x0000_t202"
o:insetmode="auto">
<v:fill color2="#ffffe1"/>
<v:shadow color="black" obscured="t"/>
<v:path o:connecttype="none"/>
<v:textbox style="mso-direction-alt:auto">
<div style="text-align:left"/>
</v:textbox>
<x:ClientData ObjectType="Note">
<x:MoveWithCells/>
<x:SizeWithCells/>
<x:AutoFill>False</x:AutoFill>
<x:Row>1</x:Row>
<x:Column>1</x:Column>
</x:ClientData>
</v:shape>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_write_only_cell_vml(datadir):
from openpyxl.xml.functions import Element, tostring
datadir.chdir()
wb = Workbook()
ws = wb.active
cell = ws['A1'] # write-only cells are always A1
cell.comment = Comment("Some text", "an author")
cell.coordinate = "B2" # coordinate calculcated on-demand
writer = CommentWriter(ws)
root = Element("root")
xml = writer._write_comment_shape(cell.comment, 1)
xml = tostring(xml)
expected = """
<v:shape
xmlns:v="urn:schemas-microsoft-com:vml"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:o="urn:schemas-microsoft-com:office:office"
fillcolor="#ffffe1"
id="_x0000_s0001"
style="position:absolute; margin-left:59.25pt;margin-top:1.5pt;width:108pt;height:59.25pt;z-index:1;visibility:hidden"
type="#_x0000_t202"
o:insetmode="auto">
<v:fill color2="#ffffe1"/>
<v:shadow color="black" obscured="t"/>
<v:path o:connecttype="none"/>
<v:textbox style="mso-direction-alt:auto">
<div style="text-align:left"/>
</v:textbox>
<x:ClientData ObjectType="Note">
<x:MoveWithCells/>
<x:SizeWithCells/>
<x:AutoFill>False</x:AutoFill>
<x:Row>1</x:Row>
<x:Column>1</x:Column>
</x:ClientData>
</v:shape>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_write_comments():
ws = _create_ws()[0]
reference_file = os.path.join(DATADIR, 'writer', 'expected',
'comments1.xml')
cw = CommentWriter(ws)
content = cw.write_comments()
with open(reference_file) as expected:
correct = fromstring(expected.read())
check = fromstring(content)
# check top-level elements have the same name
for i, j in zip(correct.getchildren(), check.getchildren()):
assert i.tag == j.tag
correct_comments = correct.find('{%s}commentList' % SHEET_MAIN_NS).getchildren()
check_comments = check.find('{%s}commentList' % SHEET_MAIN_NS).getchildren()
correct_authors = correct.find('{%s}authors' % SHEET_MAIN_NS).getchildren()
check_authors = check.find('{%s}authors' % SHEET_MAIN_NS).getchildren()
# replace author ids with author names
for i in correct_comments:
i.attrib["authorId"] = correct_authors[int(i.attrib["authorId"])].text
for i in check_comments:
i.attrib["authorId"] = check_authors[int(i.attrib["authorId"])].text
# sort the comment list
correct_comments.sort(key=lambda tag: tag.attrib["ref"])
check_comments.sort(key=lambda tag: tag.attrib["ref"])
correct.find('{%s}commentList' % SHEET_MAIN_NS)[:] = correct_comments
check.find('{%s}commentList' % SHEET_MAIN_NS)[:] = check_comments
# sort the author list
correct_authors.sort(key=lambda tag: tag.text)
check_authors.sort(key=lambda tag:tag.text)
correct.find('{%s}authors' % SHEET_MAIN_NS)[:] = correct_authors
check.find('{%s}authors' % SHEET_MAIN_NS)[:] = check_authors
diff = compare_xml(get_document_content(correct), get_document_content(check))
assert diff is None, diff
def test_write_comments_vml():
ws = _create_ws()[0]
cw = CommentWriter(ws)
reference_file = os.path.join(DATADIR, 'writer', 'expected',
'commentsDrawing1.vml')
content = cw.write_comments_vml()
with open(reference_file) as expected:
correct = fromstring(expected.read())
check = fromstring(content)
correct_ids = []
correct_coords = []
check_ids = []
check_coords = []
for i in correct.findall("{%s}shape" % vmlns):
correct_ids.append(i.attrib["id"])
row = i.find("{%s}ClientData" % excelns).find("{%s}Row" % excelns).text
col = i.find("{%s}ClientData" % excelns).find("{%s}Column" % excelns).text
correct_coords.append((row,col))
# blank the data we are checking separately
i.attrib["id"] = "0"
i.find("{%s}ClientData" % excelns).find("{%s}Row" % excelns).text="0"
i.find("{%s}ClientData" % excelns).find("{%s}Column" % excelns).text="0"
for i in check.findall("{%s}shape" % vmlns):
check_ids.append(i.attrib["id"])
row = i.find("{%s}ClientData" % excelns).find("{%s}Row" % excelns).text
col = i.find("{%s}ClientData" % excelns).find("{%s}Column" % excelns).text
check_coords.append((row,col))
# blank the data we are checking separately
i.attrib["id"] = "0"
i.find("{%s}ClientData" % excelns).find("{%s}Row" % excelns).text="0"
i.find("{%s}ClientData" % excelns).find("{%s}Column" % excelns).text="0"
assert set(correct_coords) == set(check_coords)
assert set(correct_ids) == set(check_ids)
diff = compare_xml(get_document_content(correct), get_document_content(check))
assert diff is None, diff
def test_comment_writer_init():
ws, comment1, comment2, comment3 = _create_ws()
cw = CommentWriter(ws)
assert set(cw.authors) == set(["author", "author2", "author3"])
assert set(cw.comments) == set([comment1, comment2, comment3])
