Source code

Download source archive ↓

src/openpyxl/writer/excel.py

1# Copyright (c) 2010-2024 openpyxl
2
3
4# Python stdlib imports
5import datetime
6import re
7from zipfile import ZipFile, ZIP_DEFLATED
8
9# package imports
10from openpyxl.utils.exceptions import InvalidFileException
11from openpyxl.xml.constants import (
12    ARC_ROOT_RELS,
13    ARC_WORKBOOK_RELS,
14    ARC_APP,
15    ARC_CORE,
16    ARC_CUSTOM,
17    CPROPS_TYPE,
18    ARC_THEME,
19    ARC_STYLE,
20    ARC_WORKBOOK,
21    )
22from openpyxl.drawing.spreadsheet_drawing import SpreadsheetDrawing
23from openpyxl.xml.functions import tostring, fromstring
24from openpyxl.packaging.manifest import Manifest
25from openpyxl.packaging.relationship import (
26    get_rels_path,
27    RelationshipList,
28    Relationship,
29)
30from openpyxl.comments.comment_sheet import CommentSheet
31from openpyxl.styles.stylesheet import write_stylesheet
32from openpyxl.worksheet._writer import WorksheetWriter
33from openpyxl.workbook._writer import WorkbookWriter
34from .theme import theme_xml
35
36
37class ExcelWriter:
38    """Write a workbook object to an Excel file."""
39
40    def __init__(self, workbook, archive):
41        self._archive = archive
42        self.workbook = workbook
43        self.manifest = Manifest()
44        self.vba_modified = set()
45        self._tables = []
46        self._charts = []
47        self._images = []
48        self._drawings = []
49        self._comments = []
50        self._pivots = []
51
52
53    def write_data(self):
54        from openpyxl.packaging.extended import ExtendedProperties
55        """Write the various xml files into the zip archive."""
56        # cleanup all worksheets
57        archive = self._archive
58
59        props = ExtendedProperties()
60        archive.writestr(ARC_APP, tostring(props.to_tree()))
61
62        archive.writestr(ARC_CORE, tostring(self.workbook.properties.to_tree()))
63        if self.workbook.loaded_theme:
64            archive.writestr(ARC_THEME, self.workbook.loaded_theme)
65        else:
66            archive.writestr(ARC_THEME, theme_xml)
67
68        if len(self.workbook.custom_doc_props) >= 1:
69            archive.writestr(ARC_CUSTOM, tostring(self.workbook.custom_doc_props.to_tree()))
70            class CustomOverride():
71                path = "/" + ARC_CUSTOM #PartName
72                mime_type = CPROPS_TYPE #ContentType
73
74            custom_override = CustomOverride()
75            self.manifest.append(custom_override)
76
77        self._write_worksheets()
78        self._write_chartsheets()
79        self._write_images()
80        self._write_charts()
81
82        self._write_external_links()
83
84        stylesheet = write_stylesheet(self.workbook)
85        archive.writestr(ARC_STYLE, tostring(stylesheet))
86
87        writer = WorkbookWriter(self.workbook)
88        archive.writestr(ARC_ROOT_RELS, writer.write_root_rels())
89        archive.writestr(ARC_WORKBOOK, writer.write())
90        archive.writestr(ARC_WORKBOOK_RELS, writer.write_rels())
91
92        self._merge_vba()
93
94        self.manifest._write(archive, self.workbook)
95
96    def _merge_vba(self):
97        """
98        If workbook contains macros then extract associated files from cache
99        of old file and add to archive
100        """
101        ARC_VBA = re.compile("|".join(
102            ('xl/vba', r'xl/drawings/.*vmlDrawing\d\.vml',
103             'xl/ctrlProps', 'customUI', 'xl/activeX', r'xl/media/.*\.emf')
104        )
105                             )
106
107        if self.workbook.vba_archive:
108            for name in set(self.workbook.vba_archive.namelist()) - self.vba_modified:
109                if ARC_VBA.match(name):
110                    self._archive.writestr(name, self.workbook.vba_archive.read(name))
111
112
113    def _write_images(self):
114        # delegate to object
115        for img in self._images:
116            self._archive.writestr(img.path[1:], img._data())
117
118
119    def _write_charts(self):
120        # delegate to object
121        if len(self._charts) != len(set(self._charts)):
122            raise InvalidFileException("The same chart cannot be used in more than one worksheet")
123        for chart in self._charts:
124            self._archive.writestr(chart.path[1:], tostring(chart._write()))
125            self.manifest.append(chart)
126
127
128    def _write_drawing(self, drawing):
129        """
130        Write a drawing
131        """
132        self._drawings.append(drawing)
133        drawing._id = len(self._drawings)
134        for chart in drawing.charts:
135            self._charts.append(chart)
136            chart._id = len(self._charts)
137        for img in drawing.images:
138            self._images.append(img)
139            img._id = len(self._images)
140        rels_path = get_rels_path(drawing.path)[1:]
141        self._archive.writestr(drawing.path[1:], tostring(drawing._write()))
142        self._archive.writestr(rels_path, tostring(drawing._write_rels()))
143        self.manifest.append(drawing)
144
145
146    def _write_chartsheets(self):
147        for idx, sheet in enumerate(self.workbook.chartsheets, 1):
148
149            sheet._id = idx
150            xml = tostring(sheet.to_tree())
151
152            self._archive.writestr(sheet.path[1:], xml)
153            self.manifest.append(sheet)
154
155            if sheet._drawing:
156                self._write_drawing(sheet._drawing)
157
158                rel = Relationship(type="drawing", Target=sheet._drawing.path)
159                rels = RelationshipList()
160                rels.append(rel)
161                tree = rels.to_tree()
162
163                rels_path = get_rels_path(sheet.path[1:])
164                self._archive.writestr(rels_path, tostring(tree))
165
166
167    def _write_comment(self, ws):
168
169        cs = CommentSheet.from_comments(ws._comments)
170        self._comments.append(cs)
171        cs._id = len(self._comments)
172        self._archive.writestr(cs.path[1:], tostring(cs.to_tree()))
173        self.manifest.append(cs)
174
175        if ws.legacy_drawing is None or self.workbook.vba_archive is None:
176            ws.legacy_drawing = 'xl/drawings/commentsDrawing{0}.vml'.format(cs._id)
177            vml = None
178        else:
179            vml = fromstring(self.workbook.vba_archive.read(ws.legacy_drawing))
180
181        vml = cs.write_shapes(vml)
182
183        self._archive.writestr(ws.legacy_drawing, vml)
184        self.vba_modified.add(ws.legacy_drawing)
185
186        comment_rel = Relationship(Id="comments", type=cs._rel_type, Target=cs.path)
187        ws._rels.append(comment_rel)
188
189
190    def write_worksheet(self, ws):
191        ws._drawing = SpreadsheetDrawing()
192        ws._drawing.charts = ws._charts
193        ws._drawing.images = ws._images
194        if self.workbook.write_only:
195            if not ws.closed:
196                ws.close()
197            writer = ws._writer
198        else:
199            writer = WorksheetWriter(ws)
200            writer.write()
201
202        ws._rels = writer._rels
203        self._archive.write(writer.out, ws.path[1:])
204        self.manifest.append(ws)
205        writer.cleanup()
206
207
208    def _write_worksheets(self):
209
210        pivot_caches = set()
211
212        for idx, ws in enumerate(self.workbook.worksheets, 1):
213
214            ws._id = idx
215            self.write_worksheet(ws)
216
217            if ws._drawing:
218                self._write_drawing(ws._drawing)
219
220                for r in ws._rels:
221                    if "drawing" in r.Type:
222                        r.Target = ws._drawing.path
223
224            if ws._comments:
225                self._write_comment(ws)
226
227            if ws.legacy_drawing is not None:
228                shape_rel = Relationship(type="vmlDrawing", Id="anysvml",
229                                         Target="/" + ws.legacy_drawing)
230                ws._rels.append(shape_rel)
231
232            for t in ws._tables.values():
233                self._tables.append(t)
234                t.id = len(self._tables)
235                t._write(self._archive)
236                self.manifest.append(t)
237                ws._rels.get(t._rel_id).Target = t.path
238
239            for p in ws._pivots:
240                if p.cache not in pivot_caches:
241                    pivot_caches.add(p.cache)
242                    p.cache._id = len(pivot_caches)
243
244                self._pivots.append(p)
245                p._id = len(self._pivots)
246                p._write(self._archive, self.manifest)
247                self.workbook._pivots.append(p)
248                r = Relationship(Type=p.rel_type, Target=p.path)
249                ws._rels.append(r)
250
251            if ws._rels:
252                tree = ws._rels.to_tree()
253                rels_path = get_rels_path(ws.path)[1:]
254                self._archive.writestr(rels_path, tostring(tree))
255
256
257    def _write_external_links(self):
258        # delegate to object
259        """Write links to external workbooks"""
260        wb = self.workbook
261        for idx, link in enumerate(wb._external_links, 1):
262            link._id = idx
263            rels_path = get_rels_path(link.path[1:])
264
265            xml = link.to_tree()
266            self._archive.writestr(link.path[1:], tostring(xml))
267            rels = RelationshipList()
268            rels.append(link.file_link)
269            self._archive.writestr(rels_path, tostring(rels.to_tree()))
270            self.manifest.append(link)
271
272
273    def save(self):
274        """Write data into the archive."""
275        self.write_data()
276        self._archive.close()
277
278
279def save_workbook(workbook, filename):
280    """Save the given workbook on the filesystem under the name filename.
281
282    :param workbook: the workbook to save
283    :type workbook: :class:`openpyxl.workbook.Workbook`
284
285    :param filename: the path to which save the workbook
286    :type filename: string
287
288    :rtype: bool
289
290    """
291    archive = ZipFile(filename, 'w', ZIP_DEFLATED, allowZip64=True)
292    workbook.properties.modified = datetime.datetime.now(tz=datetime.timezone.utc).replace(tzinfo=None)
293    writer = ExcelWriter(workbook, archive)
294    writer.save()
295    return True