_reader.py 15.5 KB
Newer Older
CharlieC's avatar
CharlieC committed
1
# Copyright (c) 2010-2021 openpyxl
2
3

"""Reader for a single worksheet."""
4
from copy import copy
5
6
7
8
9
10
from warnings import warn

# compatibility imports
from openpyxl.xml.functions import iterparse

# package imports
11
from openpyxl.cell import Cell, MergedCell
12
13
14
15
16
17
from openpyxl.cell.text import Text
from openpyxl.worksheet.dimensions import (
    ColumnDimension,
    RowDimension,
    SheetFormatProperties,
)
CharlieC's avatar
CharlieC committed
18

19
20
21
22
23
24
25
26
27
28
from openpyxl.xml.constants import (
    SHEET_MAIN_NS,
    EXT_TYPES,
)
from openpyxl.formatting.formatting import ConditionalFormatting
from openpyxl.formula.translate import Translator
from openpyxl.utils import (
    get_column_letter,
    coordinate_to_tuple,
    )
29
from openpyxl.utils.datetime import from_excel, from_ISO8601, WINDOWS_EPOCH
30
from openpyxl.descriptors.excel import ExtensionList
31

32
from .filters import AutoFilter
CharlieC's avatar
CharlieC committed
33
34
35
36
from .header_footer import HeaderFooter
from .hyperlink import HyperlinkList
from .merge import MergeCells
from .page import PageMargins, PrintOptions, PrintPageSetup
37
from .pagebreak import RowBreak, ColBreak
CharlieC's avatar
CharlieC committed
38
39
40
41
from .protection import SheetProtection
from .scenario import ScenarioList
from .views import SheetViewList
from .datavalidation import DataValidationList
CharlieC's avatar
CharlieC committed
42
43
44
from .table import TablePartList
from .properties import WorksheetProperties
from .dimensions import SheetDimension
CharlieC's avatar
CharlieC committed
45
from .related import Related
CharlieC's avatar
CharlieC committed
46
47
48
49
50


CELL_TAG = '{%s}c' % SHEET_MAIN_NS
VALUE_TAG = '{%s}v' % SHEET_MAIN_NS
FORMULA_TAG = '{%s}f' % SHEET_MAIN_NS
51
MERGE_TAG = '{%s}mergeCells' % SHEET_MAIN_NS
CharlieC's avatar
CharlieC committed
52
53
54
55
56
57
58
INLINE_STRING = "{%s}is" % SHEET_MAIN_NS
COL_TAG = '{%s}col' % SHEET_MAIN_NS
ROW_TAG = '{%s}row' % SHEET_MAIN_NS
CF_TAG = '{%s}conditionalFormatting' % SHEET_MAIN_NS
LEGACY_TAG = '{%s}legacyDrawing' % SHEET_MAIN_NS
PROT_TAG = '{%s}sheetProtection' % SHEET_MAIN_NS
EXT_TAG = "{%s}extLst" % SHEET_MAIN_NS
59
HYPERLINK_TAG = "{%s}hyperlinks" % SHEET_MAIN_NS
CharlieC's avatar
CharlieC committed
60
61
62
63
64
65
66
67
68
69
70
TABLE_TAG = "{%s}tableParts" % SHEET_MAIN_NS
PRINT_TAG = '{%s}printOptions' % SHEET_MAIN_NS
MARGINS_TAG = '{%s}pageMargins' % SHEET_MAIN_NS
PAGE_TAG = '{%s}pageSetup' % SHEET_MAIN_NS
HEADER_TAG = '{%s}headerFooter' % SHEET_MAIN_NS
FILTER_TAG = '{%s}autoFilter' % SHEET_MAIN_NS
VALIDATION_TAG = '{%s}dataValidations' % SHEET_MAIN_NS
PROPERTIES_TAG = '{%s}sheetPr' % SHEET_MAIN_NS
VIEWS_TAG = '{%s}sheetViews' % SHEET_MAIN_NS
FORMAT_TAG = '{%s}sheetFormatPr' % SHEET_MAIN_NS
ROW_BREAK_TAG = '{%s}rowBreaks' % SHEET_MAIN_NS
71
COL_BREAK_TAG = '{%s}colBreaks' % SHEET_MAIN_NS
CharlieC's avatar
CharlieC committed
72
73
74
SCENARIOS_TAG = '{%s}scenarios' % SHEET_MAIN_NS
DATA_TAG = '{%s}sheetData' % SHEET_MAIN_NS
DIMENSION_TAG = '{%s}dimension' % SHEET_MAIN_NS
75
CUSTOM_VIEWS_TAG = '{%s}customSheetViews' % SHEET_MAIN_NS
76
77


CharlieC's avatar
CharlieC committed
78
79
80
def _cast_number(value):
    "Convert numbers as string to an int or float"
    if "." in value or "E" in value or "e" in value:
CharlieC's avatar
CharlieC committed
81
        return float(value)
CharlieC's avatar
CharlieC committed
82
    return int(value)
CharlieC's avatar
CharlieC committed
83
84


CharlieC's avatar
CharlieC committed
85
class WorkSheetParser(object):
86

87
    def __init__(self, src, shared_strings, data_only=False,
88
89
                 epoch=WINDOWS_EPOCH, date_formats=set(),
                 timedelta_formats=set()):
90
        self.min_row = self.min_col = None
91
        self.epoch = epoch
92
        self.source = src
93
        self.shared_strings = shared_strings
94
        self.data_only = data_only
95
96
        self.shared_formulae = {}
        self.array_formulae = {}
97
        self.row_counter = self.col_counter = 0
98
        self.tables = TablePartList()
99
        self.date_formats = date_formats
100
        self.timedelta_formats = timedelta_formats
101
        self.row_dimensions = {}
102
        self.column_dimensions = {}
103
        self.number_formats = []
CharlieC's avatar
CharlieC committed
104
        self.keep_vba = False
105
106
        self.hyperlinks = HyperlinkList()
        self.formatting = []
107
        self.legacy_drawing = None
108
        self.merged_cells = None
109
110
        self.row_breaks = RowBreak()
        self.col_breaks = ColBreak()
111

112

113
114
    def parse(self):
        dispatcher = {
CharlieC's avatar
CharlieC committed
115
116
117
            COL_TAG: self.parse_column_dimensions,
            PROT_TAG: self.parse_sheet_protection,
            EXT_TAG: self.parse_extensions,
118
            CF_TAG: self.parse_formatting,
119
            LEGACY_TAG: self.parse_legacy,
120
121
            ROW_BREAK_TAG: self.parse_row_breaks,
            COL_BREAK_TAG: self.parse_col_breaks,
122
            CUSTOM_VIEWS_TAG: self.parse_custom_views,
123
124
125
                      }

        properties = {
CharlieC's avatar
CharlieC committed
126
127
128
129
130
131
132
133
134
135
            PRINT_TAG: ('print_options', PrintOptions),
            MARGINS_TAG: ('page_margins', PageMargins),
            PAGE_TAG: ('page_setup', PrintPageSetup),
            HEADER_TAG: ('HeaderFooter', HeaderFooter),
            FILTER_TAG: ('auto_filter', AutoFilter),
            VALIDATION_TAG: ('data_validations', DataValidationList),
            PROPERTIES_TAG: ('sheet_properties', WorksheetProperties),
            VIEWS_TAG: ('views', SheetViewList),
            FORMAT_TAG: ('sheet_format', SheetFormatProperties),
            SCENARIOS_TAG: ('scenarios', ScenarioList),
136
            TABLE_TAG: ('tables', TablePartList),
137
            HYPERLINK_TAG: ('hyperlinks', HyperlinkList),
138
            MERGE_TAG: ('merged_cells', MergeCells),
139

140
141
        }

142
        it = iterparse(self.source) # add a finaliser to close the source when this becomes possible
143
144
145
146
147
148
149
150
151

        for _, element in it:
            tag_name = element.tag
            if tag_name in dispatcher:
                dispatcher[tag_name](element)
                element.clear()
            elif tag_name in properties:
                prop = properties[tag_name]
                obj = prop[1].from_tree(element)
152
                setattr(self, prop[0], obj)
153
                element.clear()
154
            elif tag_name == ROW_TAG:
155
156
157
                row = self.parse_row(element)
                element.clear()
                yield row
158
159


CharlieC's avatar
CharlieC committed
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
    def parse_dimensions(self):
        """
        Get worksheet dimensions if they are provided.
        """
        it = iterparse(self.source)

        for _event, element in it:
            if element.tag == DIMENSION_TAG:
                dim = SheetDimension.from_tree(element)
                return dim.boundaries

            elif element.tag == DATA_TAG:
                # Dimensions missing
                break
            element.clear()


177
178
179
    def parse_cell(self, element):
        data_type = element.get('t', 'n')
        coordinate = element.get('r')
180
        style_id = element.get('s', 0)
181
        if style_id:
182
183
            style_id = int(style_id)

CharlieC's avatar
CharlieC committed
184
185
186
        if data_type == "inlineStr":
            value = None
        else:
187
            value = element.findtext(VALUE_TAG, None) or None
CharlieC's avatar
CharlieC committed
188

189
190
        if coordinate:
            row, column = coordinate_to_tuple(coordinate)
CharlieC's avatar
Merge    
CharlieC committed
191
            self.col_counter = column
192
        else:
CharlieC's avatar
Merge    
CharlieC committed
193
            self.col_counter += 1
194
            row, column = self.row_counter, self.col_counter
195

196
        if not self.data_only and element.find(FORMULA_TAG) is not None:
197
198
199
            data_type = 'f'
            value = self.parse_formula(element)

CharlieC's avatar
CharlieC committed
200
        elif value is not None:
201
202
            if data_type == 'n':
                value = _cast_number(value)
203
204
205
206
                if style_id in self.timedelta_formats:
                    data_type = 'd'
                    value = from_excel(value, self.epoch, timedelta=True)
                elif style_id in self.date_formats:
207
                    data_type = 'd'
208
209
210
                    try:
                        value = from_excel(value, self.epoch)
                    except ValueError:
CharlieC's avatar
CharlieC committed
211
                        msg = """Cell {0} is marked as a date but the serial value {1} is outside the limits for dates. The cell will be treated as an error.""".format(coordinate, value)
212
213
214
                        warn(msg)
                        data_type = "e"
                        value = "#VALUE!"
215
216
            elif data_type == 's':
                value = self.shared_strings[int(value)]
217
218
            elif data_type == 'b':
                value = bool(int(value))
219
            elif data_type == "str":
220
                data_type = "s"
221
222
223
            elif data_type == 'd':
                value = from_ISO8601(value)

224
        elif data_type == 'inlineStr':
225
                child = element.find(INLINE_STRING)
226
227
228
229
                if child is not None:
                    data_type = 's'
                    richtext = Text.from_tree(child)
                    value = richtext.content
CharlieC's avatar
CharlieC committed
230

231
        return {'row':row, 'column':column, 'value':value, 'data_type':data_type, 'style_id':style_id}
232
233


234
235
236
237
    def parse_formula(self, element):
        """
        possible formulae types: shared, array, datatable
        """
238
        formula = element.find(FORMULA_TAG)
239
        formula_type = formula.get('t')
CharlieC's avatar
CharlieC committed
240
        coordinate = element.get('r')
241
        value = "="
CharlieC's avatar
CharlieC committed
242
        if formula.text is not None:
243
244
245
246
247
248
249
250
251
252
253
254
            value += formula.text

        if formula_type == "array":
            self.array_formulae[coordinate] = dict(formula.attrib)

        elif formula_type == "shared":
            idx = formula.get('si')
            if idx in self.shared_formulae:
                trans = self.shared_formulae[idx]
                value = trans.translate_formula(coordinate)
            elif value != "=":
                self.shared_formulae[idx] = Translator(value, coordinate)
CharlieC's avatar
CharlieC committed
255

256
257
258
        return value


259
260
261
262
    def parse_column_dimensions(self, col):
        attrs = dict(col.attrib)
        column = get_column_letter(int(attrs['min']))
        attrs['index'] = column
CharlieC's avatar
CharlieC committed
263
        self.column_dimensions[column] = attrs
264
265
266
267
268
269


    def parse_row(self, row):
        attrs = dict(row.attrib)

        if "r" in attrs:
270
271
272
273
274
275
276
277
            try:
                self.row_counter = int(attrs['r'])
            except ValueError:
                val = float(attrs['r'])
                if val.is_integer():
                    self.row_counter = int(val)
                else:
                    raise ValueError(f"{attrs['r']} is not a valid row number")
278
        else:
279
280
            self.row_counter += 1
        self.col_counter = 0
281

CharlieC's avatar
CharlieC committed
282
        keys = {k for k in attrs if not k.startswith('{')}
283
        if keys - {'r', 'spans'}:
284
            # don't create dimension objects unless they have relevant information
285
            self.row_dimensions[str(self.row_counter)] = attrs
286

287
        cells = [self.parse_cell(el) for el in row]
288
        return self.row_counter, cells
289
290


291
    def parse_formatting(self, element):
292
293
294
        try:
            cf = ConditionalFormatting.from_tree(element)
            self.formatting.append(cf)
295
296
        except TypeError as e:
            msg = f"Failed to load a conditional formatting rule. It will be discarded. Cause: {e}"
297
            warn(msg)
298
299


300
    def parse_sheet_protection(self, element):
CharlieC's avatar
CharlieC committed
301
        protection = SheetProtection.from_tree(element)
302
        password = element.get("password")
CharlieC's avatar
CharlieC committed
303
304
305
        if password is not None:
            protection.set_password(password, True)
        self.protection = protection
306
307
308
309
310
311
312
313
314
315


    def parse_extensions(self, element):
        extLst = ExtensionList.from_tree(element)
        for e in extLst.ext:
            ext_type = EXT_TYPES.get(e.uri.upper(), "Unknown")
            msg = "{0} extension is not supported and will be removed".format(ext_type)
            warn(msg)


316
317
318
319
320
    def parse_legacy(self, element):
        obj = Related.from_tree(element)
        self.legacy_drawing = obj.id


321
322
    def parse_row_breaks(self, element):
        brk = RowBreak.from_tree(element)
323
        self.row_breaks = brk
324
325
326
327


    def parse_col_breaks(self, element):
        brk = ColBreak.from_tree(element)
328
        self.col_breaks = brk
329
330


331
    def parse_custom_views(self, element):
332
333
334
335
        # clear page_breaks to avoid duplication which Excel doesn't like
        # basically they're ignored in custom views
        self.row_breaks = RowBreak()
        self.col_breaks = ColBreak()
336
337


338
class WorksheetReader(object):
339
340
341
342
    """
    Create a parser and apply it to a workbook
    """

343
    def __init__(self, ws, xml_source, shared_strings, data_only):
344
        self.ws = ws
345
346
347
        self.parser = WorkSheetParser(xml_source, shared_strings,
                data_only, ws.parent.epoch, ws.parent._date_formats,
                ws.parent._timedelta_formats)
CharlieC's avatar
CharlieC committed
348
        self.tables = []
349
350


CharlieC's avatar
CharlieC committed
351
    def bind_cells(self):
CharlieC's avatar
CharlieC committed
352
        for idx, row in self.parser.parse():
CharlieC's avatar
CharlieC committed
353
354
355
356
            for cell in row:
                style = self.ws.parent._cell_styles[cell['style_id']]
                c = Cell(self.ws, row=cell['row'], column=cell['column'], style_array=style)
                c._value = cell['value']
357
                c.data_type = cell['data_type']
CharlieC's avatar
CharlieC committed
358
                self.ws._cells[(cell['row'], cell['column'])] = c
359
        self.ws.formula_attributes = self.parser.array_formulae
360
361
        if self.ws._cells:
            self.ws._current_row = self.ws.max_row # use cells not row dimensions
CharlieC's avatar
CharlieC committed
362
363


364
    def bind_formatting(self):
365
366
367
368
        for cf in self.parser.formatting:
            for rule in cf.rules:
                if rule.dxfId is not None:
                    rule.dxf = self.ws.parent._differential_styles[rule.dxfId]
369
                self.ws.conditional_formatting[cf] = rule
370
371
372


    def bind_tables(self):
CharlieC's avatar
CharlieC committed
373
        for t in self.parser.tables.tablePart:
374
            rel = self.ws._rels[t.id]
CharlieC's avatar
CharlieC committed
375
            self.tables.append(rel.Target)
376
377
378


    def bind_merged_cells(self):
379
380
        from openpyxl.worksheet.cell_range import MultiCellRange
        from openpyxl.worksheet.merge import MergedCellRange
381
382
        if not self.parser.merged_cells:
            return
383

384
        ranges = []
385
        for cr in self.parser.merged_cells.mergeCell:
386
387
388
389
            mcr = MergedCellRange(self.ws, cr.ref)
            self.ws._clean_merge_range(mcr)
            ranges.append(mcr)
        self.ws.merged_cells = MultiCellRange(ranges)
390
391
392


    def bind_hyperlinks(self):
CharlieC's avatar
CharlieC committed
393
        for link in self.parser.hyperlinks.hyperlink:
394
395
396
397
398
399
400
            if link.id:
                rel = self.ws._rels[link.id]
                link.target = rel.Target
            if ":" in link.ref:
                # range of cells
                for row in self.ws[link.ref]:
                    for cell in row:
401
                        try:
402
                            cell.hyperlink = copy(link)
403
404
                        except AttributeError:
                            pass
405
            else:
406
                cell = self.ws[link.ref]
407
408
                if isinstance(cell, MergedCell):
                    cell = self.normalize_merged_cell_link(cell.coordinate)
409
410
                cell.hyperlink = link

411
412
413
414
415
416
417
418
    def normalize_merged_cell_link(self, coord):
        """
        Returns the appropriate cell to which a hyperlink, which references a merged cell at the specified coordinates,
        should be bound.
        """
        for rng in self.ws.merged_cells:
            if coord in rng:
                return self.ws.cell(*rng.top[0])
CharlieC's avatar
CharlieC committed
419

420
    def bind_col_dimensions(self):
CharlieC's avatar
CharlieC committed
421
422
423
424
425
        for col, cd in self.parser.column_dimensions.items():
            if 'style' in cd:
                key = int(cd['style'])
                cd['style'] = self.ws.parent._cell_styles[key]
            self.ws.column_dimensions[col] = ColumnDimension(self.ws, **cd)
426
427
428


    def bind_row_dimensions(self):
CharlieC's avatar
CharlieC committed
429
430
431
432
433
        for row, rd in self.parser.row_dimensions.items():
            if 's' in rd:
                key = int(rd['s'])
                rd['s'] = self.ws.parent._cell_styles[key]
            self.ws.row_dimensions[int(row)] = RowDimension(self.ws, **rd)
434
435


436
437
438
439
    def bind_properties(self):
        for k in ('print_options', 'page_margins', 'page_setup',
                  'HeaderFooter', 'auto_filter', 'data_validations',
                  'sheet_properties', 'views', 'sheet_format',
440
441
442
                  'row_breaks', 'col_breaks', 'scenarios', 'legacy_drawing',
                  'protection',
                  ):
443
444
445
446
447
            v = getattr(self.parser, k, None)
            if v is not None:
                setattr(self.ws, k, v)


448
449
450
451
452
453
454
455
    def bind_all(self):
        self.bind_cells()
        self.bind_merged_cells()
        self.bind_hyperlinks()
        self.bind_formatting()
        self.bind_col_dimensions()
        self.bind_row_dimensions()
        self.bind_tables()
456
        self.bind_properties()