def test_simple_styles(datadir):
wb = Workbook(guess_types=True)
ws = wb.active
now = datetime.datetime.now()
for idx, v in enumerate(
['12.34%', now, 'This is a test', '31.31415', None], 1):
ws.append([v])
_ = ws.cell(column=1, row=idx).style_id
# set explicit formats
ws['D9'].number_format = numbers.FORMAT_NUMBER_00
ws['D9'].protection = Protection(locked=True)
ws['D9'].style_id
ws['E1'].protection = Protection(hidden=True)
ws['E1'].style_id
assert len(wb._cell_styles) == 5
writer = StyleWriter(wb)
datadir.chdir()
with open('simple-styles.xml') as reference_file:
expected = reference_file.read()
xml = writer.write_table()
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_complex_styles(datadir):
"""Hold on to your hats"""
from openpyxl import load_workbook
datadir.join("..", "..", "..", "reader", "tests", "data").chdir()
wb = load_workbook("complex-styles.xlsx")
datadir.chdir()
with open("complex-styles.xml") as reference:
writer = StyleWriter(wb)
xml = writer.write_table()
expected = reference.read()
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_complex_styles(datadir):
"""Hold on to your hats"""
from openpyxl import load_workbook
datadir.join("..", "..", "..", "reader", "tests", "data").chdir()
wb = load_workbook("complex-styles.xlsx")
datadir.chdir()
with open("complex-styles.xml") as reference:
writer = StyleWriter(wb)
xml = writer.write_table()
expected = reference.read()
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_empty_workbook():
wb = Workbook()
writer = StyleWriter(wb)
expected = """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<numFmts count="0"/>
<fonts count="1">
<font>
<name val="Calibri"/>
<family val="2"/>
<color theme="1"/>
<sz val="11"/>
<scheme val="minor"/>
</font>
</fonts>
<fills count="2">
<fill>
<patternFill />
</fill>
<fill>
<patternFill patternType="gray125"/>
</fill>
</fills>
<borders count="1">
<border>
<left/>
<right/>
<top/>
<bottom/>
<diagonal/>
</border>
</borders>
<cellStyleXfs count="1">
<xf borderId="0" fillId="0" fontId="0" numFmtId="0"/>
</cellStyleXfs>
<cellXfs count="1">
<xf borderId="0" fillId="0" fontId="0" numFmtId="0" xfId="0"/>
</cellXfs>
<cellStyles count="1">
<cellStyle builtinId="0" name="Normal" xfId="0"/>
</cellStyles>
<dxfs count="0"/>
<tableStyles count="0" defaultPivotStyle="PivotStyleLight16" defaultTableStyle="TableStyleMedium9"/>
</styleSheet>
"""
xml = writer.write_table()
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_empty_workbook():
wb = Workbook()
writer = StyleWriter(wb)
expected = """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<numFmts count="0"/>
<fonts count="1">
<font>
<name val="Calibri"/>
<family val="2"/>
<color theme="1"/>
<sz val="11"/>
<scheme val="minor"/>
</font>
</fonts>
<fills count="2">
<fill>
<patternFill />
</fill>
<fill>
<patternFill patternType="gray125"/>
</fill>
</fills>
<borders count="1">
<border>
<left/>
<right/>
<top/>
<bottom/>
<diagonal/>
</border>
</borders>
<cellStyleXfs count="1">
<xf borderId="0" fillId="0" fontId="0" numFmtId="0"/>
</cellStyleXfs>
<cellXfs count="1">
<xf borderId="0" fillId="0" fontId="0" numFmtId="0" xfId="0"/>
</cellXfs>
<cellStyles count="1">
<cellStyle builtinId="0" name="Normal" xfId="0" hidden="0"/>
</cellStyles>
<dxfs count="0"/>
<tableStyles count="0" defaultPivotStyle="PivotStyleLight16" defaultTableStyle="TableStyleMedium9"/>
</styleSheet>
"""
xml = writer.write_table()
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_simple_styles(datadir):
wb = Workbook(guess_types=True)
ws = wb.active
now = datetime.datetime.now()
for idx, v in enumerate(['12.34%', now, 'This is a test', '31.31415', None], 1):
ws.append([v])
_ = ws.cell(column=1, row=idx).style_id
# set explicit formats
ws['D9'].number_format = numbers.FORMAT_NUMBER_00
ws['D9'].protection = Protection(locked=True)
ws['D9'].style_id
ws['E1'].protection = Protection(hidden=True)
ws['E1'].style_id
assert len(wb._cell_styles) == 5
writer = StyleWriter(wb)
datadir.chdir()
with open('simple-styles.xml') as reference_file:
expected = reference_file.read()
xml = writer.write_table()
diff = compare_xml(xml, expected)
assert diff is None, diff
class ExcelWriter(object):
"""Write a workbook object to an Excel file."""
def __init__(self, workbook):
self.workbook = workbook
self.style_writer = StyleWriter(workbook)
def write_data(self, archive, as_template=False):
"""Write the various xml files into the zip archive."""
# cleanup all worksheets
archive.writestr(
ARC_CONTENT_TYPES,
write_content_types(self.workbook, as_template=as_template))
archive.writestr(ARC_ROOT_RELS, write_root_rels(self.workbook))
archive.writestr(ARC_WORKBOOK_RELS, write_workbook_rels(self.workbook))
archive.writestr(ARC_APP, write_properties_app(self.workbook))
archive.writestr(ARC_CORE, write_properties(self.workbook.properties))
if self.workbook.loaded_theme:
archive.writestr(ARC_THEME, self.workbook.loaded_theme)
else:
archive.writestr(ARC_THEME, write_theme())
archive.writestr(ARC_WORKBOOK, write_workbook(self.workbook))
if self.workbook.vba_archive:
vba_archive = self.workbook.vba_archive
for name in vba_archive.namelist():
for s in ARC_VBA:
if match(s, name):
archive.writestr(name, vba_archive.read(name))
break
for sheet in self.workbook.worksheets:
sheet.conditional_formatting._save_styles(self.workbook)
self._write_worksheets(archive)
self._write_string_table(archive)
self._write_external_links(archive)
archive.writestr(ARC_STYLE, self.style_writer.write_table())
def _write_string_table(self, archive):
archive.writestr(ARC_SHARED_STRINGS,
write_string_table(self.workbook.shared_strings))
def _write_images(self, images, archive, image_id):
for img in images:
buf = BytesIO()
img.image.save(buf, format='PNG')
archive.writestr(PACKAGE_IMAGES + '/image%d.png' % image_id,
buf.getvalue())
image_id += 1
return image_id
def _write_worksheets(self, archive):
drawing_id = 1
chart_id = 1
image_id = 1
shape_id = 1
comments_id = 1
vba_controls_id = 1
for i, sheet in enumerate(self.workbook.worksheets):
archive.writestr(
PACKAGE_WORKSHEETS + '/sheet%d.xml' % (i + 1),
write_worksheet(
sheet,
self.workbook.shared_strings,
))
if (sheet._charts or sheet._images or sheet.relationships
or sheet._comment_count > 0
or sheet.vba_controls is not None):
rels = write_rels(sheet, drawing_id, comments_id,
vba_controls_id)
archive.writestr(
PACKAGE_WORKSHEETS + '/_rels/sheet%d.xml.rels' % (i + 1),
tostring(rels))
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
if sheet.vba_controls is not None:
vba_controls_id += 1
def _write_external_links(self, archive):
"""Write links to external workbooks"""
wb = self.workbook
for idx, book in enumerate(wb._external_links, 1):
el = write_external_link(book.links)
rel = write_external_book_rel(book)
archive.writestr(
"{0}/externalLinks/externalLink{1}.xml".format(
PACKAGE_XL, idx), tostring(el))
archive.writestr(
"{0}/externalLinks/_rels/externalLink{1}.xml.rels".format(
PACKAGE_XL, idx), tostring(rel))
def save(self, filename, as_template=False):
"""Write data into the archive."""
archive = ZipFile(filename, 'w', ZIP_DEFLATED, allowZip64=True)
self.write_data(archive, as_template=as_template)
archive.close()
class ExcelWriter(object):
"""Write a workbook object to an Excel file."""
comment_writer = CommentWriter
def __init__(self, workbook):
self.workbook = workbook
self.workbook._drawings = []
self.style_writer = StyleWriter(workbook)
def write_data(self, archive, as_template=False):
"""Write the various xml files into the zip archive."""
# cleanup all worksheets
archive.writestr(ARC_CONTENT_TYPES, write_content_types(self.workbook,
as_template=as_template))
archive.writestr(ARC_ROOT_RELS, write_root_rels(self.workbook))
archive.writestr(ARC_WORKBOOK_RELS, write_workbook_rels(self.workbook))
archive.writestr(ARC_APP, write_properties_app(self.workbook))
archive.writestr(ARC_CORE, write_properties(self.workbook.properties))
if self.workbook.loaded_theme:
archive.writestr(ARC_THEME, self.workbook.loaded_theme)
else:
archive.writestr(ARC_THEME, write_theme())
archive.writestr(ARC_WORKBOOK, write_workbook(self.workbook))
if self.workbook.vba_archive:
vba_archive = self.workbook.vba_archive
for name in vba_archive.namelist():
for s in ARC_VBA:
if match(s, name):
archive.writestr(name, vba_archive.read(name))
break
self._write_charts(archive)
self._write_images(archive)
self._write_worksheets(archive)
self._write_string_table(archive)
self._write_external_links(archive)
archive.writestr(ARC_STYLE, self.style_writer.write_table())
def _write_string_table(self, archive):
archive.writestr(ARC_SHARED_STRINGS,
write_string_table(self.workbook.shared_strings))
def _write_images(self, archive):
for idx, ref in enumerate(self.workbook._images, 1):
img = ref()
if img is None:
continue
buf = BytesIO()
img.image.save(buf, format='PNG')
img._id = idx
archive.writestr(img._path, buf.getvalue())
def _write_charts(self, archive):
for idx, ref in enumerate(self.workbook._charts, 1):
chart = ref()
if not chart:
continue
chart._id = idx
archive.writestr(chart._path, tostring(chart._write()))
def _write_worksheets(self, archive):
comments_id = 0
vba_controls_id = 0
for i, sheet in enumerate(self.workbook.worksheets, 1):
xml = sheet._write(self.workbook.shared_strings)
archive.writestr(PACKAGE_WORKSHEETS + '/sheet%d.xml' % i , xml)
if sheet._charts or sheet._images:
drawing = SpreadsheetDrawing()
drawing.charts = sheet._charts
drawing.images = sheet._images
self.workbook._drawings.append(drawing)
drawing_id = len(self.workbook._drawings)
drawingpath = "{0}/drawing{1}.xml".format(PACKAGE_DRAWINGS, drawing_id)
archive.writestr(drawingpath, tostring(drawing._write()))
archive.writestr("{0}/_rels/drawing{1}.xml.rels".format(PACKAGE_DRAWINGS,
drawing_id), tostring(drawing._write_rels()))
for r in sheet._rels:
if "drawing" in r.type:
r.target = "/" + drawingpath
if sheet._comment_count > 0:
comments_id += 1
cw = self.comment_writer(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())
if sheet.vba_controls is not None:
vba_controls_id += 1
if (sheet._rels
or sheet._comment_count > 0
or sheet.vba_controls is not None):
rels = write_rels(sheet, comments_id=comments_id, vba_controls_id=vba_controls_id)
archive.writestr( PACKAGE_WORKSHEETS +
'/_rels/sheet%d.xml.rels' % i, tostring(rels))
def _write_external_links(self, archive):
"""Write links to external workbooks"""
wb = self.workbook
for idx, book in enumerate(wb._external_links, 1):
el = write_external_link(book.links)
rel = write_external_book_rel(book)
archive.writestr(
"{0}/externalLinks/externalLink{1}.xml".format(PACKAGE_XL, idx),
tostring(el)
)
archive.writestr(
"{0}/externalLinks/_rels/externalLink{1}.xml.rels".format(PACKAGE_XL, idx),
tostring(rel)
)
def save(self, filename, as_template=False):
"""Write data into the archive."""
archive = ZipFile(filename, 'w', ZIP_DEFLATED, allowZip64=True)
self.write_data(archive, as_template=as_template)
archive.close()
class ExcelWriter(object):
"""Write a workbook object to an Excel file."""
def __init__(self, workbook):
self.workbook = workbook
self.style_writer = StyleWriter(workbook)
def write_data(self, archive, as_template=False):
"""Write the various xml files into the zip archive."""
# cleanup all worksheets
archive.writestr(ARC_CONTENT_TYPES, write_content_types(self.workbook,
as_template=as_template))
archive.writestr(ARC_ROOT_RELS, write_root_rels(self.workbook))
archive.writestr(ARC_WORKBOOK_RELS, write_workbook_rels(self.workbook))
archive.writestr(ARC_APP, write_properties_app(self.workbook))
archive.writestr(ARC_CORE, write_properties(self.workbook.properties))
if self.workbook.loaded_theme:
archive.writestr(ARC_THEME, self.workbook.loaded_theme)
else:
archive.writestr(ARC_THEME, write_theme())
archive.writestr(ARC_WORKBOOK, write_workbook(self.workbook))
if self.workbook.vba_archive:
vba_archive = self.workbook.vba_archive
for name in vba_archive.namelist():
for s in ARC_VBA:
if name.startswith(s):
archive.writestr(name, vba_archive.read(name))
break
for sheet in self.workbook.worksheets:
sheet.conditional_formatting._save_styles(self.workbook)
self._write_worksheets(archive)
self._write_string_table(archive)
self._write_external_links(archive)
archive.writestr(ARC_STYLE, self.style_writer.write_table())
def _write_string_table(self, archive):
archive.writestr(ARC_SHARED_STRINGS,
write_string_table(self.workbook.shared_strings))
def _write_images(self, images, archive, image_id):
for img in images:
buf = BytesIO()
img.image.save(buf, format= 'PNG')
archive.writestr(PACKAGE_IMAGES + '/image%d.png' % image_id, buf.getvalue())
image_id += 1
return image_id
def _write_worksheets(self, archive):
drawing_id = 1
chart_id = 1
image_id = 1
shape_id = 1
comments_id = 1
vba_controls_id = 1
for i, sheet in enumerate(self.workbook.worksheets):
archive.writestr(PACKAGE_WORKSHEETS + '/sheet%d.xml' % (i + 1),
write_worksheet(sheet, self.workbook.shared_strings,
))
if (sheet._charts or sheet._images
or sheet.relationships
or sheet._comment_count > 0
or sheet.vba_controls is not None):
rels = write_rels(sheet, drawing_id, comments_id, vba_controls_id)
archive.writestr(
PACKAGE_WORKSHEETS + '/_rels/sheet%d.xml.rels' % (i + 1),
tostring(rels)
)
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
if sheet.vba_controls is not None:
vba_controls_id += 1
def _write_external_links(self, archive):
"""Write links to external workbooks"""
wb = self.workbook
for idx, book in enumerate(wb._external_links, 1):
el = write_external_link(book.links)
rel = write_external_book_rel(book)
archive.writestr(
"{0}/externalLinks/externalLink{1}.xml".format(PACKAGE_XL, idx),
tostring(el)
)
archive.writestr(
"{0}/externalLinks/_rels/externalLink{1}.xml.rels".format(PACKAGE_XL, idx),
tostring(rel)
)
def save(self, filename, as_template=False):
"""Write data into the archive."""
archive = ZipFile(filename, 'w', ZIP_DEFLATED)
self.write_data(archive, as_template=as_template)
archive.close()
class ExcelWriter(object):
"""Write a workbook object to an Excel file."""
def __init__(self, workbook):
self.workbook = workbook
self.style_writer = StyleWriter(self.workbook)
def write_data(self, archive):
"""Write the various xml files into the zip archive."""
# cleanup all worksheets
archive.writestr(ARC_CONTENT_TYPES, write_content_types(self.workbook))
archive.writestr(ARC_ROOT_RELS, write_root_rels(self.workbook))
archive.writestr(ARC_WORKBOOK_RELS, write_workbook_rels(self.workbook))
archive.writestr(ARC_APP, write_properties_app(self.workbook))
archive.writestr(ARC_CORE,
write_properties_core(self.workbook.properties))
if self.workbook.loaded_theme:
archive.writestr(ARC_THEME, self.workbook.loaded_theme)
else:
archive.writestr(ARC_THEME, write_theme())
archive.writestr(ARC_STYLE, self.style_writer.write_table())
archive.writestr(ARC_WORKBOOK, write_workbook(self.workbook))
if self.workbook.vba_archive:
vba_archive = self.workbook.vba_archive
for name in vba_archive.namelist():
for s in ARC_VBA:
if name.startswith(s):
archive.writestr(name, vba_archive.read(name))
break
self._write_string_table(archive)
self._write_worksheets(archive, self.style_writer)
def _write_string_table(self, archive):
for ws in self.workbook.worksheets:
ws.garbage_collect()
self.shared_strings = create_string_table(self.workbook)
archive.writestr(ARC_SHARED_STRINGS,
write_string_table(self.shared_strings))
def _write_images(self, images, archive, image_id):
for img in images:
buf = BytesIO()
img.image.save(buf, format='PNG')
archive.writestr(PACKAGE_IMAGES + '/image%d.png' % image_id,
buf.getvalue())
image_id += 1
return image_id
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 save(self, filename):
"""Write data into the archive."""
archive = ZipFile(filename, 'w', ZIP_DEFLATED)
self.write_data(archive)
archive.close()
class ExcelWriter(object):
"""Write a workbook object to an Excel file."""
comment_writer = CommentWriter
def __init__(self, workbook):
self.workbook = workbook
self.workbook._drawings = []
self.style_writer = StyleWriter(workbook)
self.vba_modified = set()
def write_data(self, archive, as_template=False):
"""Write the various xml files into the zip archive."""
# cleanup all worksheets
archive.writestr(ARC_ROOT_RELS, write_root_rels(self.workbook))
archive.writestr(ARC_WORKBOOK_RELS, write_workbook_rels(self.workbook))
archive.writestr(ARC_APP, write_properties_app(self.workbook))
archive.writestr(ARC_CORE, write_properties(self.workbook.properties))
if self.workbook.loaded_theme:
archive.writestr(ARC_THEME, self.workbook.loaded_theme)
else:
archive.writestr(ARC_THEME, write_theme())
archive.writestr(ARC_WORKBOOK, write_workbook(self.workbook))
self._write_charts(archive)
self._write_images(archive)
self._write_worksheets(archive)
self._write_chartsheets(archive)
self._write_string_table(archive)
self._write_external_links(archive)
archive.writestr(ARC_STYLE, self.style_writer.write_table())
if self.workbook.vba_archive:
vba_archive = self.workbook.vba_archive
for name in set(vba_archive.namelist()) - self.vba_modified:
for s in ARC_VBA:
if match(s, name):
archive.writestr(name, vba_archive.read(name))
break
exts = []
for n in archive.namelist():
if "media" in n:
exts.append(n)
manifest = write_content_types(self.workbook, as_template=as_template, exts=exts)
archive.writestr(ARC_CONTENT_TYPES, tostring(manifest.to_tree()))
def _write_string_table(self, archive):
archive.writestr(ARC_SHARED_STRINGS,
write_string_table(self.workbook.shared_strings))
def _write_images(self, archive):
for idx, ref in enumerate(self.workbook._images, 1):
img = ref()
if img is None:
continue
buf = BytesIO()
img.image.save(buf, format='PNG')
img._id = idx
archive.writestr(img._path, buf.getvalue())
def _write_charts(self, archive):
for idx, ref in enumerate(self.workbook._charts, 1):
chart = ref()
if not chart:
continue
chart._id = idx
archive.writestr(chart._path, tostring(chart._write()))
def _write_chartsheets(self, archive):
from openpyxl.packaging.relationship import Relationship, RelationshipList
from openpyxl.worksheet.drawing import Drawing
for idx, sheet in enumerate(self.workbook.chartsheets, 1):
if sheet._charts:
drawing = SpreadsheetDrawing()
drawing.charts = sheet._charts
self.workbook._drawings.append(drawing)
drawing_id = len(self.workbook._drawings)
drawingpath = "{0}/drawing{1}.xml".format(PACKAGE_DRAWINGS, drawing_id)
archive.writestr(drawingpath, tostring(drawing._write()))
archive.writestr(
"{0}/_rels/drawing{1}.xml.rels".format(
PACKAGE_DRAWINGS, drawing_id),
tostring(drawing._write_rels())
)
rel = Relationship(type="drawing", target="/" + drawingpath)
rels = RelationshipList()
rels.append(rel)
tree = rels.to_tree()
sheet.drawing.id = "rId{0}".format(len(rels))
archive.writestr(PACKAGE_CHARTSHEETS +
'/_rels/sheet%d.xml.rels' % idx, tostring(tree)
)
xml = tostring(sheet.to_tree())
archive.writestr(PACKAGE_CHARTSHEETS + '/sheet%d.xml' % idx, xml)
def _write_worksheets(self, archive):
comments_id = 0
for i, sheet in enumerate(self.workbook.worksheets, 1):
xml = sheet._write(self.workbook.shared_strings)
archive.writestr(PACKAGE_WORKSHEETS + '/sheet%d.xml' % i , xml)
if sheet._charts or sheet._images:
drawing = SpreadsheetDrawing()
drawing.charts = sheet._charts
drawing.images = sheet._images
self.workbook._drawings.append(drawing)
drawing_id = len(self.workbook._drawings)
drawingpath = "{0}/drawing{1}.xml".format(PACKAGE_DRAWINGS, drawing_id)
archive.writestr(drawingpath, tostring(drawing._write()))
archive.writestr("{0}/_rels/drawing{1}.xml.rels".format(PACKAGE_DRAWINGS,
drawing_id), tostring(drawing._write_rels()))
for r in sheet._rels:
if "drawing" in r.type:
r.target = "/" + drawingpath
if sheet._comment_count > 0:
comments_id += 1
cw = self.comment_writer(sheet)
archive.writestr(PACKAGE_XL + '/comments%d.xml' % comments_id,
cw.write_comments())
if sheet.legacy_drawing is not None:
vmlroot = fromstring(self.workbook.vba_archive.read(sheet.legacy_drawing))
archive.writestr(sheet.legacy_drawing, cw.write_comments_vml(vmlroot))
# Record this file so we don't write it again when we dump out vba_archive
self.vba_modified.add(sheet.legacy_drawing)
else:
vmlroot = Element("xml")
archive.writestr(PACKAGE_XL + '/drawings/commentsDrawing%d.vml' % comments_id,
cw.write_comments_vml(vmlroot))
if (sheet._rels
or sheet._comment_count > 0
or sheet.legacy_drawing is not None):
rels = write_rels(sheet, comments_id=comments_id)
archive.writestr(PACKAGE_WORKSHEETS +
'/_rels/sheet%d.xml.rels' % i, tostring(rels))
def _write_external_links(self, archive):
"""Write links to external workbooks"""
wb = self.workbook
for idx, book in enumerate(wb._external_links, 1):
el = write_external_link(book.links)
rel = write_external_book_rel(book)
archive.writestr(
"{0}/externalLinks/externalLink{1}.xml".format(PACKAGE_XL, idx),
tostring(el)
)
archive.writestr(
"{0}/externalLinks/_rels/externalLink{1}.xml.rels".format(PACKAGE_XL, idx),
tostring(rel)
)
def save(self, filename, as_template=False):
"""Write data into the archive."""
archive = ZipFile(filename, 'w', ZIP_DEFLATED, allowZip64=True)
self.write_data(archive, as_template=as_template)
archive.close()
class ExcelWriter(object):
"""Write a workbook object to an Excel file."""
def __init__(self, workbook):
self.workbook = workbook
self.style_writer = StyleWriter(self.workbook)
def write_data(self, archive):
"""Write the various xml files into the zip archive."""
# cleanup all worksheets
shared_string_table = self._write_string_table(archive)
archive.writestr(ARC_CONTENT_TYPES, write_content_types(self.workbook))
archive.writestr(ARC_ROOT_RELS, write_root_rels(self.workbook))
archive.writestr(ARC_WORKBOOK_RELS, write_workbook_rels(self.workbook))
archive.writestr(ARC_APP, write_properties_app(self.workbook))
archive.writestr(ARC_CORE,
write_properties_core(self.workbook.properties))
archive.writestr(ARC_THEME, write_theme())
archive.writestr(ARC_STYLE, self.style_writer.write_table())
archive.writestr(ARC_WORKBOOK, write_workbook(self.workbook))
self._write_worksheets(archive, shared_string_table, self.style_writer)
def _write_string_table(self, archive):
for ws in self.workbook.worksheets:
ws.garbage_collect()
shared_string_table = create_string_table(self.workbook)
archive.writestr(ARC_SHARED_STRINGS,
write_string_table(shared_string_table))
return shared_string_table
def _write_worksheets(self, archive, shared_string_table, style_writer):
drawing_id = 1
chart_id = 1
shape_id = 1
for i, sheet in enumerate(self.workbook.worksheets):
archive.writestr(PACKAGE_WORKSHEETS + '/sheet%d.xml' % (i + 1),
write_worksheet(sheet, shared_string_table,
style_writer.get_style_by_hash()))
if sheet._charts or sheet.relationships:
archive.writestr(PACKAGE_WORKSHEETS +
'/_rels/sheet%d.xml.rels' % (i + 1),
write_worksheet_rels(sheet, drawing_id))
if sheet._charts:
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))
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))
sw = ShapeWriter(chart._shapes)
archive.writestr(PACKAGE_DRAWINGS + '/drawing%d.xml' % drawing_id,
sw.write(shape_id))
shape_id += len(chart._shapes)
drawing_id += 1
chart_id += 1
def save(self, filename):
"""Write data into the archive."""
archive = ZipFile(filename, 'w', ZIP_DEFLATED)
self.write_data(archive)
archive.close()
class ExcelWriter(object):
"""Write a workbook object to an Excel file."""
comment_writer = CommentWriter
def __init__(self, workbook):
self.workbook = workbook
self.workbook._drawings = []
self.style_writer = StyleWriter(workbook)
self.vba_modified = set()
def write_data(self, archive, as_template=False):
"""Write the various xml files into the zip archive."""
# cleanup all worksheets
archive.writestr(ARC_ROOT_RELS, write_root_rels(self.workbook))
archive.writestr(ARC_WORKBOOK_RELS, write_workbook_rels(self.workbook))
archive.writestr(ARC_APP, write_properties_app(self.workbook))
archive.writestr(ARC_CORE, write_properties(self.workbook.properties))
if self.workbook.loaded_theme:
archive.writestr(ARC_THEME, self.workbook.loaded_theme)
else:
archive.writestr(ARC_THEME, write_theme())
archive.writestr(ARC_WORKBOOK, write_workbook(self.workbook))
self._write_charts(archive)
self._write_images(archive)
self._write_worksheets(archive)
self._write_chartsheets(archive)
self._write_string_table(archive)
self._write_external_links(archive)
archive.writestr(ARC_STYLE, self.style_writer.write_table())
if self.workbook.vba_archive:
vba_archive = self.workbook.vba_archive
for name in set(vba_archive.namelist()) - self.vba_modified:
for s in ARC_VBA:
if match(s, name):
archive.writestr(name, vba_archive.read(name))
break
exts = []
for n in archive.namelist():
if "media" in n:
exts.append(n)
manifest = write_content_types(self.workbook,
as_template=as_template,
exts=exts)
archive.writestr(ARC_CONTENT_TYPES, tostring(manifest.to_tree()))
def _write_string_table(self, archive):
archive.writestr(ARC_SHARED_STRINGS,
write_string_table(self.workbook.shared_strings))
def _write_images(self, archive):
for idx, ref in enumerate(self.workbook._images, 1):
img = ref()
if img is None:
continue
buf = BytesIO()
img.image.save(buf, format='PNG')
img._id = idx
archive.writestr(img._path, buf.getvalue())
def _write_charts(self, archive):
for idx, ref in enumerate(self.workbook._charts, 1):
chart = ref()
if not chart:
continue
chart._id = idx
archive.writestr(chart._path, tostring(chart._write()))
def _write_chartsheets(self, archive):
from openpyxl.packaging.relationship import Relationship, RelationshipList
from openpyxl.worksheet.drawing import Drawing
for idx, sheet in enumerate(self.workbook.chartsheets, 1):
if sheet._charts:
drawing = SpreadsheetDrawing()
drawing.charts = sheet._charts
self.workbook._drawings.append(drawing)
drawing_id = len(self.workbook._drawings)
drawingpath = "{0}/drawing{1}.xml".format(
PACKAGE_DRAWINGS, drawing_id)
archive.writestr(drawingpath, tostring(drawing._write()))
archive.writestr(
"{0}/_rels/drawing{1}.xml.rels".format(
PACKAGE_DRAWINGS, drawing_id),
tostring(drawing._write_rels()))
rel = Relationship(type="drawing", target="/" + drawingpath)
rels = RelationshipList()
rels.append(rel)
tree = rels.to_tree()
sheet.drawing.id = "rId{0}".format(len(rels))
archive.writestr(
PACKAGE_CHARTSHEETS + '/_rels/sheet%d.xml.rels' % idx,
tostring(tree))
xml = tostring(sheet.to_tree())
archive.writestr(PACKAGE_CHARTSHEETS + '/sheet%d.xml' % idx, xml)
def _write_worksheets(self, archive):
comments_id = 0
for i, sheet in enumerate(self.workbook.worksheets, 1):
xml = sheet._write(self.workbook.shared_strings)
archive.writestr(PACKAGE_WORKSHEETS + '/sheet%d.xml' % i, xml)
if sheet._charts or sheet._images:
drawing = SpreadsheetDrawing()
drawing.charts = sheet._charts
drawing.images = sheet._images
self.workbook._drawings.append(drawing)
drawing_id = len(self.workbook._drawings)
drawingpath = "{0}/drawing{1}.xml".format(
PACKAGE_DRAWINGS, drawing_id)
archive.writestr(drawingpath, tostring(drawing._write()))
archive.writestr(
"{0}/_rels/drawing{1}.xml.rels".format(
PACKAGE_DRAWINGS, drawing_id),
tostring(drawing._write_rels()))
for r in sheet._rels:
if "drawing" in r.type:
r.target = "/" + drawingpath
if sheet._comment_count > 0:
comments_id += 1
cw = self.comment_writer(sheet)
archive.writestr(PACKAGE_XL + '/comments%d.xml' % comments_id,
cw.write_comments())
if sheet.legacy_drawing is not None:
vmlroot = fromstring(
self.workbook.vba_archive.read(sheet.legacy_drawing))
archive.writestr(sheet.legacy_drawing,
cw.write_comments_vml(vmlroot))
# Record this file so we don't write it again when we dump out vba_archive
self.vba_modified.add(sheet.legacy_drawing)
else:
vmlroot = Element("xml")
archive.writestr(
PACKAGE_XL +
'/drawings/commentsDrawing%d.vml' % comments_id,
cw.write_comments_vml(vmlroot))
if (sheet._rels or sheet._comment_count > 0
or sheet.legacy_drawing is not None):
rels = write_rels(sheet, comments_id=comments_id)
archive.writestr(
PACKAGE_WORKSHEETS + '/_rels/sheet%d.xml.rels' % i,
tostring(rels))
def _write_external_links(self, archive):
"""Write links to external workbooks"""
wb = self.workbook
for idx, book in enumerate(wb._external_links, 1):
el = write_external_link(book.links)
rel = write_external_book_rel(book)
archive.writestr(
"{0}/externalLinks/externalLink{1}.xml".format(
PACKAGE_XL, idx), tostring(el))
archive.writestr(
"{0}/externalLinks/_rels/externalLink{1}.xml.rels".format(
PACKAGE_XL, idx), tostring(rel))
def save(self, filename, as_template=False):
"""Write data into the archive."""
archive = ZipFile(filename, 'w', ZIP_DEFLATED, allowZip64=True)
self.write_data(archive, as_template=as_template)
archive.close()
class ExcelWriter(object):
"""Write a workbook object to an Excel file."""
def __init__(self, workbook):
self.workbook = workbook
self.style_writer = StyleWriter(self.workbook)
def write_data(self, archive):
"""Write the various xml files into the zip archive."""
# cleanup all worksheets
shared_string_table = self._write_string_table(archive)
archive.writestr(ARC_CONTENT_TYPES, write_content_types(self.workbook))
if not self.workbook.vba_archive:
archive.writestr(ARC_ROOT_RELS, write_root_rels(self.workbook))
archive.writestr(ARC_WORKBOOK_RELS, write_workbook_rels(self.workbook))
archive.writestr(ARC_APP, write_properties_app(self.workbook))
archive.writestr(ARC_CORE, write_properties_core(self.workbook.properties))
if self.workbook.loaded_theme:
archive.writestr(ARC_THEME, self.workbook.loaded_theme)
else:
archive.writestr(ARC_THEME, write_theme())
archive.writestr(ARC_STYLE, self.style_writer.write_table())
archive.writestr(ARC_WORKBOOK, write_workbook(self.workbook))
if self.workbook.vba_archive:
vba_archive = self.workbook.vba_archive
for name in vba_archive.namelist():
for s in ARC_VBA:
if name.startswith(s):
archive.writestr(name, vba_archive.read(name))
break
self._write_worksheets(archive, shared_string_table, self.style_writer)
def _write_string_table(self, archive):
for ws in self.workbook.worksheets:
ws.garbage_collect()
shared_string_table = create_string_table(self.workbook)
archive.writestr(ARC_SHARED_STRINGS, write_string_table(shared_string_table))
return shared_string_table
def _write_worksheets(self, archive, shared_string_table, style_writer):
drawing_id = 1
chart_id = 1
image_id = 1
shape_id = 1
for i, sheet in enumerate(self.workbook.worksheets):
archive.writestr(
PACKAGE_WORKSHEETS + "/sheet%d.xml" % (i + 1),
write_worksheet(sheet, shared_string_table, style_writer.get_style_by_hash()),
)
if sheet._charts or sheet._images or sheet.relationships:
archive.writestr(
PACKAGE_WORKSHEETS + "/_rels/sheet%d.xml.rels" % (i + 1), write_worksheet_rels(sheet, drawing_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)
)
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)
)
sw = ShapeWriter(chart._shapes)
archive.writestr(PACKAGE_DRAWINGS + "/drawing%d.xml" % drawing_id, sw.write(shape_id))
shape_id += len(chart._shapes)
drawing_id += 1
chart_id += 1
for img in sheet._images:
buf = StringIO()
img.image.save(buf, format="PNG")
archive.writestr(PACKAGE_IMAGES + "/image%d.png" % image_id, buf.getvalue())
image_id += 1
def save(self, filename):
"""Write data into the archive."""
archive = ZipFile(filename, "w", ZIP_DEFLATED)
self.write_data(archive)
archive.close()
class ExcelWriter(object):
"""Write a workbook object to an Excel file."""
def __init__(self, workbook):
self.workbook = workbook
self.style_writer = StyleWriter(self.workbook)
def write_data(self, archive):
"""Write the various xml files into the zip archive."""
# cleanup all worksheets
shared_string_table = self._write_string_table(archive)
archive.writestr(ARC_CONTENT_TYPES, write_content_types(self.workbook))
archive.writestr(ARC_ROOT_RELS, write_root_rels(self.workbook))
archive.writestr(ARC_WORKBOOK_RELS, write_workbook_rels(self.workbook))
archive.writestr(ARC_APP, write_properties_app(self.workbook))
archive.writestr(ARC_CORE,
write_properties_core(self.workbook.properties))
if self.workbook.loaded_theme:
archive.writestr(ARC_THEME, self.workbook.loaded_theme)
else:
archive.writestr(ARC_THEME, write_theme())
archive.writestr(ARC_STYLE, self.style_writer.write_table())
archive.writestr(ARC_WORKBOOK, write_workbook(self.workbook))
self._write_worksheets(archive, shared_string_table, self.style_writer)
def _write_string_table(self, archive):
for ws in self.workbook.worksheets:
ws.garbage_collect()
shared_string_table = create_string_table(self.workbook)
archive.writestr(ARC_SHARED_STRINGS,
write_string_table(shared_string_table))
return shared_string_table
def _write_worksheets(self, archive, shared_string_table, style_writer):
drawing_id = 1
chart_id = 1
image_id = 1
shape_id = 1
for i, sheet in enumerate(self.workbook.worksheets):
archive.writestr(
PACKAGE_WORKSHEETS + '/sheet%d.xml' % (i + 1),
write_worksheet(sheet, shared_string_table,
style_writer.get_style_by_hash()))
if sheet._charts or sheet._images or sheet.relationships:
archive.writestr(
PACKAGE_WORKSHEETS + '/_rels/sheet%d.xml.rels' % (i + 1),
write_worksheet_rels(sheet, drawing_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))
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))
sw = ShapeWriter(chart._shapes)
archive.writestr(
PACKAGE_DRAWINGS + '/drawing%d.xml' % drawing_id,
sw.write(shape_id))
shape_id += len(chart._shapes)
drawing_id += 1
chart_id += 1
for img in sheet._images:
buf = StringIO()
img.image.save(buf, format='PNG')
archive.writestr(
PACKAGE_IMAGES + '/image%d.png' % image_id,
buf.getvalue())
image_id += 1
def save(self, filename):
"""Write data into the archive."""
archive = ZipFile(filename, 'w', ZIP_DEFLATED)
self.write_data(archive)
archive.close()
