Source code

Download source archive ↓

src/openpyxl/worksheet/_writer.py

1# Copyright (c) 2010-2024 openpyxl
2
3import atexit
4from collections import defaultdict
5from io import BytesIO
6import os
7from tempfile import NamedTemporaryFile
8from warnings import warn
9
10from openpyxl.xml.functions import xmlfile
11from openpyxl.xml.constants import SHEET_MAIN_NS
12
13from openpyxl.comments.comment_sheet import CommentRecord
14from openpyxl.packaging.relationship import Relationship, RelationshipList
15from openpyxl.styles.differential import DifferentialStyle
16
17from .dimensions import SheetDimension
18from .hyperlink import HyperlinkList
19from .merge import MergeCell, MergeCells
20from .related import Related
21from .table import TablePartList
22
23from openpyxl.cell._writer import write_cell
24
25
26ALL_TEMP_FILES = []
27
28@atexit.register
29def _openpyxl_shutdown():
30    for path in ALL_TEMP_FILES:
31        if os.path.exists(path):
32            os.remove(path)
33
34
35def create_temporary_file(suffix=''):
36    fobj = NamedTemporaryFile(mode='w+', suffix=suffix,
37                              prefix='openpyxl.', delete=False)
38    filename = fobj.name
39    fobj.close()
40    ALL_TEMP_FILES.append(filename)
41    return filename
42
43
44class WorksheetWriter:
45
46
47    def __init__(self, ws, out=None):
48        self.ws = ws
49        self.ws._hyperlinks = []
50        self.ws._comments = []
51        if out is None:
52            out = create_temporary_file()
53        self.out = out
54        self._rels = RelationshipList()
55        self.xf = self.get_stream()
56        next(self.xf) # start generator
57
58
59    def write_properties(self):
60        props = self.ws.sheet_properties
61        self.xf.send(props.to_tree())
62
63
64    def write_dimensions(self):
65        """
66        Write worksheet size if known
67        """
68        ref = getattr(self.ws, 'calculate_dimension', None)
69        if ref:
70            dim = SheetDimension(ref())
71            self.xf.send(dim.to_tree())
72
73
74    def write_format(self):
75        self.ws.sheet_format.outlineLevelCol = self.ws.column_dimensions.max_outline
76        fmt = self.ws.sheet_format
77        self.xf.send(fmt.to_tree())
78
79
80    def write_views(self):
81        views = self.ws.views
82        self.xf.send(views.to_tree())
83
84
85    def write_cols(self):
86        cols = self.ws.column_dimensions
87        self.xf.send(cols.to_tree())
88
89
90    def write_top(self):
91        """
92        Write all elements up to rows:
93        properties
94        dimensions
95        views
96        format
97        cols
98        """
99        self.write_properties()
100        self.write_dimensions()
101        self.write_views()
102        self.write_format()
103        self.write_cols()
104
105
106    def rows(self):
107        """Return all rows, and any cells that they contain"""
108        # order cells by row
109        rows = defaultdict(list)
110        for (row, col), cell in sorted(self.ws._cells.items()):
111            rows[row].append(cell)
112
113        # add empty rows if styling has been applied
114        for row in self.ws.row_dimensions.keys() - rows.keys():
115            rows[row] = []
116
117        return sorted(rows.items())
118
119
120    def write_rows(self):
121        xf = self.xf.send(True)
122
123        with xf.element("sheetData"):
124            for row_idx, row in self.rows():
125                self.write_row(xf, row, row_idx)
126
127        self.xf.send(None) # return control to generator
128
129
130    def write_row(self, xf, row, row_idx):
131        attrs = {'r': f"{row_idx}"}
132        dims = self.ws.row_dimensions
133        attrs.update(dims.get(row_idx, {}))
134
135        with xf.element("row", attrs):
136
137            for cell in row:
138                if cell._comment is not None:
139                    comment = CommentRecord.from_cell(cell)
140                    self.ws._comments.append(comment)
141                if (
142                    cell._value is None
143                    and not cell.has_style
144                    and not cell._comment
145                    ):
146                    continue
147                write_cell(xf, self.ws, cell, cell.has_style)
148
149
150    def write_protection(self):
151        prot = self.ws.protection
152        if prot:
153            self.xf.send(prot.to_tree())
154
155
156    def write_scenarios(self):
157        scenarios = self.ws.scenarios
158        if scenarios:
159            self.xf.send(scenarios.to_tree())
160
161
162    def write_filter(self):
163        flt = self.ws.auto_filter
164        if flt:
165            self.xf.send(flt.to_tree())
166
167
168    def write_sort(self):
169        """
170        As per discusion with the OOXML Working Group global sort state is not required.
171        openpyxl never reads it from existing files
172        """
173        pass
174
175
176    def write_merged_cells(self):
177        merged = self.ws.merged_cells
178        if merged:
179            cells = [MergeCell(str(ref)) for ref in self.ws.merged_cells]
180            self.xf.send(MergeCells(mergeCell=cells).to_tree())
181
182
183    def write_formatting(self):
184        df = DifferentialStyle()
185        wb = self.ws.parent
186        for cf in self.ws.conditional_formatting:
187            for rule in cf.rules:
188                if rule.dxf and rule.dxf != df:
189                    rule.dxfId = wb._differential_styles.add(rule.dxf)
190            self.xf.send(cf.to_tree())
191
192
193    def write_validations(self):
194        dv = self.ws.data_validations
195        if dv:
196            self.xf.send(dv.to_tree())
197
198
199    def write_hyperlinks(self):
200
201        links = self.ws._hyperlinks
202
203        for link in links:
204            if link.target:
205                rel = Relationship(type="hyperlink", TargetMode="External", Target=link.target)
206                self._rels.append(rel)
207                link.id = rel.id
208
209        if links:
210            self.xf.send(HyperlinkList(links).to_tree())
211
212
213    def write_print(self):
214        print_options = self.ws.print_options
215        if print_options:
216            self.xf.send(print_options.to_tree())
217
218
219    def write_margins(self):
220        margins = self.ws.page_margins
221        if margins:
222            self.xf.send(margins.to_tree())
223
224
225    def write_page(self):
226        setup = self.ws.page_setup
227        if setup:
228            self.xf.send(setup.to_tree())
229
230
231    def write_header(self):
232        hf = self.ws.HeaderFooter
233        if hf:
234            self.xf.send(hf.to_tree())
235
236
237    def write_breaks(self):
238        brks = (self.ws.row_breaks, self.ws.col_breaks)
239        for brk in brks:
240            if brk:
241                self.xf.send(brk.to_tree())
242
243
244    def write_drawings(self):
245        if self.ws._charts or self.ws._images:
246            rel = Relationship(type="drawing", Target="")
247            self._rels.append(rel)
248            drawing = Related()
249            drawing.id = rel.id
250            self.xf.send(drawing.to_tree("drawing"))
251
252
253    def write_legacy(self):
254        """
255        Comments & VBA controls use VML and require an additional element
256        that is no longer in the specification.
257        """
258        if (self.ws.legacy_drawing is not None or self.ws._comments):
259            legacy = Related(id="anysvml")
260            self.xf.send(legacy.to_tree("legacyDrawing"))
261
262
263    def write_tables(self):
264        tables = TablePartList()
265
266        for table in self.ws.tables.values():
267            if not table.tableColumns:
268                table._initialise_columns()
269                if table.headerRowCount:
270                    try:
271                        row = self.ws[table.ref][0]
272                        for cell, col in zip(row, table.tableColumns):
273                            if cell.data_type != "s":
274                                warn("File may not be readable: column headings must be strings.")
275                            col.name = str(cell.value)
276                    except TypeError:
277                        warn("Column headings are missing, file may not be readable")
278            rel = Relationship(Type=table._rel_type, Target="")
279            self._rels.append(rel)
280            table._rel_id = rel.Id
281            tables.append(Related(id=rel.Id))
282
283        if tables:
284            self.xf.send(tables.to_tree())
285
286
287    def get_stream(self):
288        with xmlfile(self.out) as xf:
289            with xf.element("worksheet", xmlns=SHEET_MAIN_NS):
290                try:
291                    while True:
292                        el = (yield)
293                        if el is True:
294                            yield xf
295                        elif el is None: # et_xmlfile chokes
296                            continue
297                        else:
298                            xf.write(el)
299                except GeneratorExit:
300                    pass
301
302
303    def write_tail(self):
304        """
305        Write all elements after the rows
306        calc properties
307        protection
308        protected ranges #
309        scenarios
310        filters
311        sorts # always ignored
312        data consolidation #
313        custom views #
314        merged cells
315        phonetic properties #
316        conditional formatting
317        data validation
318        hyperlinks
319        print options
320        page margins
321        page setup
322        header
323        row breaks
324        col breaks
325        custom properties #
326        cell watches #
327        ignored errors #
328        smart tags #
329        drawing
330        drawingHF #
331        background #
332        OLE objects #
333        controls #
334        web publishing #
335        tables
336        """
337        self.write_protection()
338        self.write_scenarios()
339        self.write_filter()
340        self.write_merged_cells()
341        self.write_formatting()
342        self.write_validations()
343        self.write_hyperlinks()
344        self.write_print()
345        self.write_margins()
346        self.write_page()
347        self.write_header()
348        self.write_breaks()
349        self.write_drawings()
350        self.write_legacy()
351        self.write_tables()
352
353
354    def write(self):
355        """
356        High level
357        """
358        self.write_top()
359        self.write_rows()
360        self.write_tail()
361        self.close()
362
363
364    def close(self):
365        """
366        Close the context manager
367        """
368        if self.xf:
369            self.xf.close()
370
371
372    def read(self):
373        """
374        Close the context manager and return serialised XML
375        """
376        self.close()
377        if isinstance(self.out, BytesIO):
378            return self.out.getvalue()
379        with open(self.out, "rb") as src:
380            out = src.read()
381
382        return out
383
384
385    def cleanup(self):
386        """
387        Remove tempfile
388        """
389        os.remove(self.out)
390        ALL_TEMP_FILES.remove(self.out)