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()