test_reader.py 32.2 KB
Newer Older
CharlieC's avatar
CharlieC committed
1
# Copyright (c) 2010-2021 openpyxl
2
3
4
5
6
7
8
9
10
11
12
13

import pytest

import datetime
from io import BytesIO

from lxml.etree import iterparse, fromstring

from openpyxl.xml.constants import SHEET_MAIN_NS
from openpyxl.utils.indexed_list import IndexedList
from openpyxl.packaging.relationship import Relationship, RelationshipList
from openpyxl.utils.datetime  import CALENDAR_WINDOWS_1900, CALENDAR_MAC_1904
14
from openpyxl.styles.styleable import StyleArray
15
from openpyxl.styles.borders import DEFAULT_BORDER
16
from openpyxl.styles.differential import DifferentialStyle
17
from openpyxl.formula.translate import Translator
18
from ..worksheet import Worksheet
19
from ..pagebreak import Break, RowBreak, ColBreak
20
from ..scenario import ScenarioList, Scenario, InputCells
21

CharlieC's avatar
CharlieC committed
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
@pytest.mark.parametrize("value, expected",
                         [
                             ('4.2', 4.2),
                             ('-42.000', -42),
                             ('0', 0),
                             ('0.9999', 0.9999),
                             ('99E-02', 0.99),
                             ('4', 4),
                             ('-1E3', -1000),
                             ('1E-3', 0.001),
                             ('2e+2', 200.0),
                         ]
                         )
def test_number_convesion(value, expected):
    from .._reader import _cast_number
    assert _cast_number(value) == expected
38
39
40
41


@pytest.fixture
def Workbook():
42

43
44
45
46
47
48
49
50
    class DummyWorkbook:

        data_only = False
        _colors = []
        encoding = "utf8"
        epoch = CALENDAR_WINDOWS_1900

        def __init__(self):
51
52
            self._differential_styles = [DifferentialStyle()] * 5
            self.shared_strings = ['a'] * 30
53
54
55
            self._fonts = IndexedList()
            self._fills = IndexedList()
            self._number_formats = IndexedList()
56
            self._borders = IndexedList([DEFAULT_BORDER] * 30)
57
58
59
60
61
62
63
64
            self._alignments = IndexedList()
            self._protections = IndexedList()
            self._cell_styles = IndexedList()
            self.vba_archive = None
            for i in range(29):
                self._cell_styles.add((StyleArray([i]*9)))
            self._cell_styles.add(StyleArray([0,4,6,0,0,1,0,0,0])) #fillId=4, borderId=6, alignmentId=1))
            self.sheetnames = []
65
            self._date_formats = set()
66
            self._timedelta_formats = set()
67
68
69
70
71
72
73
74

        def create_sheet(self, title):
            return Worksheet(self)

    return DummyWorkbook()


@pytest.fixture
CharlieC's avatar
CharlieC committed
75
def WorkSheetParser():
76
    """Setup a parser instance with an empty source"""
CharlieC's avatar
CharlieC committed
77
    from .._reader import WorkSheetParser
78
79
80
81

    styles = IndexedList()
    for i in range(29):
        styles.add((StyleArray([i]*9)))
82
    styles.add(StyleArray([0,4,6,14,0,1,0,0,0])) #fillId=4, borderId=6, number_format=14 alignmentId=1))
83
    date_formats = set([1, 29])
84
85
    timedelta_formats = set([30])
    return WorkSheetParser(None, {0:'a'}, date_formats=date_formats, timedelta_formats=timedelta_formats)
86

CharlieC's avatar
CharlieC committed
87
88
from warnings import simplefilter
simplefilter("always")
89

CharlieC's avatar
CharlieC committed
90
class TestWorksheetParser:
91

CharlieC's avatar
CharlieC committed
92
93
94
95
96
97
98
99
100
101
102
    @pytest.mark.parametrize("filename, expected",
                             [
                                 ("dimension.xml", (4, 1, 27, 30)),
                                 ("no_dimension.xml", None),
                                 ("invalid_dimension.xml", (None, 1, None, 113)),
                              ]
                             )
    def test_read_dimension(self, WorkSheetParser, datadir, filename, expected):
        datadir.chdir()
        parser = WorkSheetParser
        parser.source = filename
103

CharlieC's avatar
CharlieC committed
104
105
        dimension = parser.parse_dimensions()
        assert dimension == expected
CharlieC's avatar
CharlieC committed
106

107

CharlieC's avatar
CharlieC committed
108
109
110
111
112
113
114
115
116
117
    def test_col_width(self, datadir, WorkSheetParser):
        datadir.chdir()
        parser = WorkSheetParser
        with open("complex-styles-worksheet.xml", "rb") as src:
            cols = iterparse(src, tag='{%s}col' % SHEET_MAIN_NS)
            for _, col in cols:
                parser.parse_column_dimensions(col)
        assert set(parser.column_dimensions) == set(['A', 'C', 'E', 'I', 'G'])
        assert parser.column_dimensions['A'] == {'max': '1', 'min': '1',
                                                 'index':'A', 'customWidth': '1', 'width': '31.1640625'}
118
119


CharlieC's avatar
CharlieC committed
120
121
    def test_hidden_col(self, WorkSheetParser):
        parser = WorkSheetParser
122

CharlieC's avatar
CharlieC committed
123
124
        src = """<col min="4" max="4" width="0" hidden="1" customWidth="1"/>"""
        element = fromstring(src)
125

CharlieC's avatar
CharlieC committed
126
127
128
129
        parser.parse_column_dimensions(element)
        assert 'D' in parser.column_dimensions
        assert parser.column_dimensions['D'] == {'customWidth': '1', 'width':'0',
                                                 'hidden': '1', 'max': '4', 'min': '4', 'index':'D'}
130
131


CharlieC's avatar
CharlieC committed
132
133
134
    def test_styled_col(self, datadir, WorkSheetParser):
        datadir.chdir()
        parser = WorkSheetParser
135

CharlieC's avatar
CharlieC committed
136
137
138
139
140
141
142
        with open("complex-styles-worksheet.xml", "rb") as src:
            cols = iterparse(src, tag='{%s}col' % SHEET_MAIN_NS)
            for _, col in cols:
                parser.parse_column_dimensions(col)
        assert 'I' in parser.column_dimensions
        cd = parser.column_dimensions['I']
        assert cd ==  {'customWidth': '1', 'max': '9', 'min': '9', 'width': '25', 'index':'I', 'style':'28'}
143
144


CharlieC's avatar
CharlieC committed
145
146
147
    def test_row_dimensions(self, WorkSheetParser):
        src = """<row r="2" spans="1:6" />"""
        element = fromstring(src)
148

CharlieC's avatar
CharlieC committed
149
150
        parser = WorkSheetParser
        parser.parse_row(element)
151

CharlieC's avatar
CharlieC committed
152
        assert 2 not in parser.row_dimensions
153
154


CharlieC's avatar
CharlieC committed
155
156
    def test_hidden_row(self, WorkSheetParser):
        parser = WorkSheetParser
157

CharlieC's avatar
CharlieC committed
158
159
160
161
        src = """
        <row r="2" spans="1:4" hidden="1" />
        """
        element = fromstring(src)
162

CharlieC's avatar
CharlieC committed
163
164
        parser.parse_row(element)
        assert parser.row_dimensions['2'] == {'r': '2', 'spans': '1:4', 'hidden':'1'}
165
166


CharlieC's avatar
CharlieC committed
167
168
169
170
    def test_styled_row(self, datadir, WorkSheetParser):
        parser = WorkSheetParser
        src = """<row r="23" s="28" spans="1:8" />"""
        element = fromstring(src)
171

CharlieC's avatar
CharlieC committed
172
173
174
        parser.parse_row(element)
        rd = parser.row_dimensions['23']
        assert rd == {'r': '23', 's': '28', 'spans': '1:8'}
175
176


177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
    def test_read_row_with_exponent(self, WorkSheetParser):
        parser = WorkSheetParser
        src = """<row r="1.048573e6" spans="1:8" /> """
        element = fromstring(src)
        parser.parse_row(element)
        assert parser.row_counter == 1048573


    def test_invalid_row_number(self, WorkSheetParser):
        parser = WorkSheetParser
        src = """<row r="1.5" spans="1:8" /> """
        element = fromstring(src)
        with pytest.raises(ValueError):
            parser.parse_row(element)


CharlieC's avatar
CharlieC committed
193
194
    def test_sheet_protection(self, datadir, WorkSheetParser):
        parser = WorkSheetParser
195

CharlieC's avatar
CharlieC committed
196
197
198
199
200
        src = """
        <sheetProtection xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" password="DAA7" sheet="1" formatCells="0" formatColumns="0" formatRows="0" insertColumns="0" insertRows="0" insertHyperlinks="0" deleteColumns="0" deleteRows="0" sort="0" autoFilter="0" pivotTables="0"/>
        """
        element = fromstring(src)
        parser.parse_sheet_protection(element)
201

CharlieC's avatar
CharlieC committed
202
203
204
205
206
207
208
209
        assert dict(parser.protection) == {
            'autoFilter': '0', 'deleteColumns': '0',
            'deleteRows': '0', 'formatCells': '0', 'formatColumns': '0', 'formatRows':
            '0', 'insertColumns': '0', 'insertHyperlinks': '0', 'insertRows': '0',
            'objects': '0', 'password': 'DAA7', 'pivotTables': '0', 'scenarios': '0',
            'selectLockedCells': '0', 'selectUnlockedCells': '0', 'sheet': '1', 'sort':
            '0'
        }
210
211


CharlieC's avatar
CharlieC committed
212
213
    def test_formula(self, WorkSheetParser):
        parser = WorkSheetParser
214

CharlieC's avatar
CharlieC committed
215
216
217
218
219
220
221
        src = """
        <c r="A1" t="str" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
            <f>IF(TRUE, "y", "n")</f>
            <v>y</v>
        </c>
        """
        element = fromstring(src)
222

CharlieC's avatar
CharlieC committed
223
224
225
        cell = parser.parse_cell(element)
        assert cell == {'column': 1, 'data_type': 'f', 'row': 1,
                        'style_id':0, 'value': '=IF(TRUE, "y", "n")'}
226
227


CharlieC's avatar
CharlieC committed
228
229
230
    def test_formula_data_only(self, WorkSheetParser):
        parser = WorkSheetParser
        parser.data_only = True
231

CharlieC's avatar
CharlieC committed
232
        src = """
233
        <c r="A1" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
CharlieC's avatar
CharlieC committed
234
235
236
237
238
            <f>1+2</f>
            <v>3</v>
        </c>
        """
        element = fromstring(src)
239

CharlieC's avatar
CharlieC committed
240
241
242
        cell = parser.parse_cell(element)
        assert cell == {'column': 1, 'data_type': 'n', 'row': 1,
                        'style_id':0, 'value': 3}
243
244


CharlieC's avatar
CharlieC committed
245
246
247
    def test_string_formula_data_only(self, WorkSheetParser):
        parser = WorkSheetParser
        parser.data_only = True
248

CharlieC's avatar
CharlieC committed
249
250
251
252
253
254
255
        src = """
        <c r="A1" t="str" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
            <f>IF(TRUE, "y", "n")</f>
            <v>y</v>
        </c>
        """
        element = fromstring(src)
CharlieC's avatar
CharlieC committed
256

CharlieC's avatar
CharlieC committed
257
258
259
        cell = parser.parse_cell(element)
        assert cell == {'column': 1, 'data_type': 's', 'row': 1,
                        'style_id':0, 'value': 'y'}
CharlieC's avatar
CharlieC committed
260

261

CharlieC's avatar
CharlieC committed
262
263
264
265
266
267
268
269
270
271
272
273
274
    def test_number(self, WorkSheetParser):
        parser = WorkSheetParser

        src = """
        <c r="A1" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
            <v>1</v>
        </c>
        """
        element = fromstring(src)

        cell = parser.parse_cell(element)
        assert cell == {'column': 1, 'data_type': 'n', 'row': 1,
                        'style_id':0, 'value': 1}
275
276
277



CharlieC's avatar
CharlieC committed
278
279
    def test_datetime(self, WorkSheetParser):
        parser = WorkSheetParser
280

CharlieC's avatar
CharlieC committed
281
282
283
284
285
286
287
288
289
290
291
292
        src = """
        <c r="A1" t="d" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
            <v>2011-12-25T14:23:55</v>
        </c>
        """
        element = fromstring(src)

        cell = parser.parse_cell(element)
        assert cell == {'column': 1, 'data_type': 'd', 'row': 1,
                        'style_id':0, 'value': datetime.datetime(2011, 12, 25, 14, 23, 55)}


293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
    def test_timedelta(self, WorkSheetParser):
        parser = WorkSheetParser

        src = """
        <c r="A1" t="n" s="30" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
            <v>1.25</v>
        </c>
        """
        element = fromstring(src)

        cell = parser.parse_cell(element)
        assert cell == {'column': 1, 'data_type': 'd', 'row': 1,
                        'style_id':30, 'value':datetime.timedelta(days=1, hours=6)}


CharlieC's avatar
CharlieC committed
308
309
310
311
312
313
314
315
316
317
318
319
320
321
    def test_mac_date(self, WorkSheetParser):
        parser = WorkSheetParser
        parser.epoch = CALENDAR_MAC_1904

        src = """
        <c r="A1" t="n" s="29" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
            <v>41184</v>
        </c>
        """
        element = fromstring(src)

        cell = parser.parse_cell(element)
        assert cell == {'column': 1, 'data_type': 'd', 'row': 1,
                        'style_id':29, 'value':datetime.datetime(2016, 10, 3, 0, 0)}
322
323


CharlieC's avatar
CharlieC committed
324
325
    def test_string(self, WorkSheetParser):
        parser = WorkSheetParser
326

CharlieC's avatar
CharlieC committed
327
328
329
330
331
332
        src = """
        <c r="A1" t="s" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
            <v>0</v>
        </c>
        """
        element = fromstring(src)
333

CharlieC's avatar
CharlieC committed
334
335
336
        cell = parser.parse_cell(element)
        assert cell == {'column': 1, 'data_type': 's', 'row': 1,
                        'style_id':0, 'value':'a'}
CharlieC's avatar
CharlieC committed
337
338


CharlieC's avatar
CharlieC committed
339
340
341
342
343
344
345
346
347
348
349
350
351
    def test_boolean(self, WorkSheetParser):
        parser = WorkSheetParser

        src = """
        <c r="A1" t="b" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
            <v>1</v>
        </c>
        """
        element = fromstring(src)

        cell = parser.parse_cell(element)
        assert cell == {'column': 1, 'data_type': 'b', 'row': 1,
                        'style_id':0, 'value':True}
CharlieC's avatar
CharlieC committed
352

353

CharlieC's avatar
CharlieC committed
354
355
356
357
358
359
360
361
362
    def test_inline_string(self, WorkSheetParser):
        parser = WorkSheetParser
        src = """
        <c xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" r="A1" s="0" t="inlineStr">
          <is>
          <t>ID</t>
          </is>
        </c>
        """
CharlieC's avatar
CharlieC committed
363

CharlieC's avatar
CharlieC committed
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
        element = fromstring(src)

        cell = parser.parse_cell(element)
        assert cell == {'column': 1, 'data_type': 's', 'row': 1,
                        'style_id':0, 'value':"ID"}


    def test_inline_richtext(self, WorkSheetParser):
        parser = WorkSheetParser
        src = """
        <c xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" r="R2" s="4" t="inlineStr">
        <is>
          <r>
            <rPr>
              <sz val="8.0" />
            </rPr>
            <t xml:space="preserve">11 de September de 2014</t>
          </r>
          </is>
        </c>
        """

        element = fromstring(src)
        cell = parser.parse_cell(element)
        assert cell == {'column': 18, 'data_type': 's', 'row': 2,
                        'style_id':4, 'value':"11 de September de 2014"}


    def test_sheet_views(self, WorkSheetParser):
        parser = WorkSheetParser

        src = b"""
        <sheetViews xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
          <sheetView tabSelected="1" zoomScale="200" zoomScaleNormal="200" zoomScalePageLayoutView="200" workbookViewId="0">
            <pane xSplit="5" ySplit="19" topLeftCell="F20" activePane="bottomRight" state="frozenSplit"/>
            <selection pane="topRight" activeCell="F1" sqref="F1"/>
            <selection pane="bottomLeft" activeCell="A20" sqref="A20"/>
            <selection pane="bottomRight" activeCell="E22" sqref="E22"/>
          </sheetView>
        </sheetViews>
        """

        parser.source = BytesIO(src)
        for _ in parser.parse():
            pass

        view = parser.views.sheetView[0]

        assert view.zoomScale == 200
        assert len(view.selection) == 3


    def test_shared_formula(self, WorkSheetParser):
        parser = WorkSheetParser
        src = """
        <c r="A9" t="str" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
          <f t="shared" si="0"/>
          <v>9</v>
        </c>
        """
        element = fromstring(src)
        parser.shared_formulae['0'] = Translator("=A4*B4", "A1")
        formula = parser.parse_formula(element)
        assert formula == "=A12*B12"


    def test_array_formula(self, WorkSheetParser, datadir):
        parser = WorkSheetParser

        src = """
        <c r="C10" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
          <f t="array" ref="C10:C14">SUM(A10:A14*B10:B14)</f>
        </c>"""
        element = fromstring(src)

439
440
        formula = parser.parse_formula(element)
        assert formula == "=SUM(A10:A14*B10:B14)"
CharlieC's avatar
CharlieC committed
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
        assert parser.array_formulae['C10']['ref'] == 'C10:C14'


    def test_extended_conditional_formatting(self, WorkSheetParser, recwarn):
        parser = WorkSheetParser

        src = """
        <extLst>
            <ext xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" uri="{78C0D931-6437-407d-A8EE-F0AAD7539E65}">
                <x14:conditionalFormattings>
                    <x14:conditionalFormatting xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main">
                        <x14:cfRule type="containsText" priority="1" operator="containsText" id="{5F3BF262-1B4E-49BC-812D-8F5B1802CE0A}">
                            <xm:f>NOT(ISERROR(SEARCH($AA$7,A1)))</xm:f>
                            <xm:f>$AA$7</xm:f>
                            <x14:dxf>
                                <fill>
                                    <patternFill>
                                        <bgColor rgb="FF6699FF"/>
                                    </patternFill>
                                </fill>
                            </x14:dxf>
                        </x14:cfRule>
                    </x14:conditionalFormatting>
                </x14:conditionalFormattings>
            </ext>
        </extLst>
        """
        element = fromstring(src)

        parser.parse_extensions(element)
        w = recwarn.pop()
        assert issubclass(w.category, UserWarning)


475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
    def test_bad_conditional_format_rule(self, WorkSheetParser, recwarn):
        parser = WorkSheetParser

        src = """
                <conditionalFormatting sqref="G124:G144">
                    <cfRule type="colorScale" priority="1">
                        <colorScale>
                            <cfvo type="num" val="&quot;&lt;&gt;100%&quot;"/>
                            <cfvo type="max"/>
                            <color rgb="FFFF7128"/>
                            <color rgb="FFFFEF9C"/>
                        </colorScale>
                    </cfRule>
                </conditionalFormatting>
                """
        element = fromstring(src)

        parser.parse_formatting(element)
        w = recwarn.pop()
        assert issubclass(w.category, UserWarning)


CharlieC's avatar
CharlieC committed
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
    def test_cell_without_coordinates(self, WorkSheetParser):
        parser = WorkSheetParser

        src = """
        <row r="1" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
          <c t="s">
            <v>2</v>
          </c>
          <c t="s">
            <v>4</v>
          </c>
          <c t="s">
            <v>3</v>
          </c>
          <c t="s">
            <v>6</v>
          </c>
          <c t="s">
            <v>9</v>
          </c>
        </row>
        """
        element = fromstring(src)

        parser.shared_strings = ["Whatever"] * 10
        parser.parse_row(element)

524
525
        assert parser.row_counter == 1
        assert parser.col_counter == 5
CharlieC's avatar
CharlieC committed
526
527


528
529
530
    def test_row_and_cell_without_coordinates(self, WorkSheetParser):
        parser = WorkSheetParser
        src = """
patrik.simons@neotide.fi's avatar
patrik.simons@neotide.fi committed
531
532
        <row xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
          <c>
533
534
            <v>2</v>
          </c>
patrik.simons@neotide.fi's avatar
patrik.simons@neotide.fi committed
535
          <c>
536
537
            <v>4</v>
          </c>
patrik.simons@neotide.fi's avatar
patrik.simons@neotide.fi committed
538
          <c>
539
540
541
542
543
            <v>3</v>
          </c>
        </row>
        """
        element = fromstring(src)
patrik.simons@neotide.fi's avatar
patrik.simons@neotide.fi committed
544
545
546
547
548
549
550
551
        max_row, cells = parser.parse_row(element)
        expected = [
            {'column': 1, 'row': 1, 'data_type': 'n', 'value': 2, 'style_id': 0},
            {'column': 2, 'row': 1, 'data_type': 'n', 'value': 4, 'style_id': 0},
            {'column': 3, 'row': 1, 'data_type': 'n', 'value': 3, 'style_id': 0},
        ]
        for expected_cell, cell in zip(expected, cells):
            assert expected_cell == cell
552
553


554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
    def test_row_and_cell_skipping_coordinates(self, WorkSheetParser):
        parser = WorkSheetParser
        src = """
        <row xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
          <c>
            <v>1</v>
          </c>
          <c r="D1">
            <v>2</v>
          </c>
          <c>
            <v>3</v>
          </c>
          <c r="G1">
            <v>4</v>
          </c>
        </row>
        """
        element = fromstring(src)
        _, cells = parser.parse_row(element)
        expected = [
            {'column': 1, 'row': 1, 'data_type': 'n', 'value': 1, 'style_id': 0},
            {'column': 4, 'row': 1, 'data_type': 'n', 'value': 2, 'style_id': 0},
            {'column': 5, 'row': 1, 'data_type': 'n', 'value': 3, 'style_id': 0},
            {'column': 7, 'row': 1, 'data_type': 'n', 'value': 4, 'style_id': 0},
        ]
        assert len(cells) == len(expected)
        for expected_cell, cell in zip(expected, cells):
            assert expected_cell == cell


585
586
587
588
589
590
591
592
593
594
595
    def test_second_row_cell_index_without_coordinates(self, WorkSheetParser):
        parser = WorkSheetParser
        src = """
        <row xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
          <c>
            <v>2</v>
          </c>
        </row>
        """
        element = fromstring(src)
        parser.parse_row(element)
patrik.simons@neotide.fi's avatar
patrik.simons@neotide.fi committed
596
597
598
599
600
601
        max_row, cells = parser.parse_row(element)
        expected = [
            {'column': 1, 'row': 2, 'data_type': 'n', 'value': 2, 'style_id': 0},
        ]
        for expected_cell, cell in zip(expected, cells):
            assert expected_cell == cell
602
603


CharlieC's avatar
CharlieC committed
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
    def test_external_hyperlinks(self, WorkSheetParser):
        src = b"""
        <hyperlinks xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
          <hyperlink xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"
           display="http://test.com" r:id="rId1" ref="A1"/>
        </hyperlinks>
        """
        parser = WorkSheetParser
        parser.source = BytesIO(src)

        for _ in parser.parse():
            pass

        parser.parse()
        assert len(parser.hyperlinks.hyperlink) == 1


    def test_local_hyperlinks(self, WorkSheetParser):
        src = b"""
         <hyperlinks xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
            <hyperlink ref="B4:B7" location="'STP nn000TL-10, PKG 2.52'!A1" display="STP 10000TL-10"/>
        </hyperlinks>
        """
        parser = WorkSheetParser
        parser.source = BytesIO(src)

        for _ in parser.parse():
            pass

        assert len(parser.hyperlinks.hyperlink) == 1


    def test_merge_cells(self, WorkSheetParser):
        src = b"""
        <sheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
          <mergeCells>
            <mergeCell ref="C2:F2"/>
            <mergeCell ref="B19:C20"/>
            <mergeCell ref="E19:G19"/>
          </mergeCells>
        </sheet>
        """

        parser = WorkSheetParser
        parser.source = BytesIO(src)

        for _ in parser.parse():
            pass

        assert len(parser.merged_cells.mergeCell) == 3


    def test_conditonal_formatting(self, WorkSheetParser):
        src = b"""
        <sheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
          <conditionalFormatting sqref="S1:S10">
            <cfRule type="top10" dxfId="25" priority="12" percent="1" rank="10"/>
        </conditionalFormatting>
        <conditionalFormatting sqref="T1:T10">
          <cfRule type="top10" dxfId="24" priority="11" bottom="1" rank="4"/>
        </conditionalFormatting>
        </sheet>
        """
        parser = WorkSheetParser
        parser.source = BytesIO(src)

        for _ in parser.parse():
            pass
        assert len(parser.formatting) == 2


    def test_sheet_properties(self, WorkSheetParser):
        src = b"""
        <sheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
        <sheetPr codeName="Sheet3">
          <tabColor rgb="FF92D050"/>
          <outlinePr summaryBelow="1" summaryRight="1"/>
          <pageSetUpPr/>
        </sheetPr>
        </sheet>
        """
        parser = WorkSheetParser
        parser.source = BytesIO(src)

        for _ in parser.parse():
            pass

        assert parser.sheet_properties.tabColor.rgb == "FF92D050"
        assert parser.sheet_properties.codeName == "Sheet3"


    def test_sheet_format(self, WorkSheetParser):

        src = b"""
        <sheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
          <sheetFormatPr defaultRowHeight="14.25" baseColWidth="15"/>
        </sheet>
        """
        parser = WorkSheetParser
        parser.source = BytesIO(src)

        for _ in parser.parse():
            pass

        assert parser.sheet_format.defaultRowHeight == 14.25
        assert parser.sheet_format.baseColWidth == 15


    def test_tables(self, WorkSheetParser):
        src = b"""
        <tableParts count="1" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
          xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
            <tablePart r:id="rId1"/>
        </tableParts>
        """
        parser = WorkSheetParser
        parser.source = BytesIO(src)

        for _ in parser.parse():
            pass

        assert parser.tables.tablePart[0].id == "rId1"


    def test_auto_filter(self, WorkSheetParser):
        src = b"""
        <sheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
          <autoFilter ref="A1:AK3237">
            <sortState ref="A2:AM3269">
                <sortCondition ref="B1:B3269"/>
            </sortState>
          </autoFilter>
        </sheet>
        """

        parser = WorkSheetParser
        parser.source = BytesIO(src)
        for _ in parser.parse():
            pass

        assert parser.auto_filter.ref == "A1:AK3237"
        assert parser.auto_filter.sortState.ref == "A2:AM3269"


748
    def test_page_row_break(self, WorkSheetParser):
CharlieC's avatar
CharlieC committed
749
        src = b"""
750
751
752
        <rowBreaks count="1" manualBreakCount="1" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
           <brk id="15" man="1" max="16383" min="0"/>
        </rowBreaks>
CharlieC's avatar
CharlieC committed
753
        """
754
        expected_pagebreak = RowBreak()
CharlieC's avatar
CharlieC committed
755
756
757
        expected_pagebreak.append(Break(id=15))

        parser = WorkSheetParser
758
759
760
        el = fromstring(src)
        parser.parse_row_breaks(el)

761
        assert parser.row_breaks == expected_pagebreak
762
763
764
765
766
767
768
769
770
771
772
773
774
775


    def test_col_break(self, WorkSheetParser):
        src = b"""
        <colBreaks count="1" manualBreakCount="1" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
          <brk id="15" man="1" max="16383" min="0"/>
        </colBreaks>
        """
        expected_pagebreak = ColBreak()
        expected_pagebreak.append(Break(id=15))

        parser = WorkSheetParser
        el = fromstring(src)
        parser.parse_col_breaks(el)
CharlieC's avatar
CharlieC committed
776

777
        assert parser.col_breaks == expected_pagebreak
CharlieC's avatar
CharlieC committed
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795


    def test_scenarios(self, WorkSheetParser):
        src = b"""
        <sheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
        <scenarios current="0" show="0">
        <scenario name="Worst case" locked="1" user="User" comment="comment">
          <inputCells r="B2" val="50000" />
        </scenario>
        </scenarios>
        </sheet>
        """

        parser = WorkSheetParser
        parser.source = BytesIO(src)
        for _ in parser.parse():
            pass

796
797
798
799
        c = InputCells(r="B2", val="50000")
        s = Scenario(name="Worst case", inputCells=[c], locked=True, user="User", comment="comment")
        scenarios = ScenarioList(scenario=[s], current="0", show="0")

CharlieC's avatar
CharlieC committed
800
        assert parser.scenarios == scenarios
801
802


803
804
805
806
807
808
809
810
811
812
813
814
    def test_legacy(self, WorkSheetParser):
        src = """
        <legacyDrawing r:id="rId3" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
        xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"/>
        """
        parser = WorkSheetParser
        element = fromstring(src)

        parser.parse_legacy(element)
        assert parser.legacy_drawing == "rId3"


815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
    def test_custom_views_breaks(self, WorkSheetParser):
        src = b"""
        <sheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
        <customSheetViews >
            <customSheetView scale="70">
            <selection activeCell="F14" sqref="F14"/>
            <rowBreaks count="1" manualBreakCount="1">
              <brk id="1" max="16383" man="1"/>
            </rowBreaks>
            <pageMargins left="0.75" right="0.75" top="1" bottom="1" header="0.5" footer="0.5"/>
            <pageSetup paperSize="9" scale="50" firstPageNumber="99" orientation="landscape" useFirstPageNumber="1"/>
            <headerFooter alignWithMargins="0"/>
            </customSheetView>
        </customSheetViews>
        </sheet>
        """
        parser = WorkSheetParser
        parser.source = BytesIO(src)
        for _ in parser.parse():
            pass
835
        assert parser.row_breaks == RowBreak()
836
837


838
@pytest.fixture
839
def WorksheetReader():
840
    from .._reader import WorksheetReader
841
842
843
844
845
    return WorksheetReader


@pytest.fixture
def PrimedWorksheetReader(Workbook, WorksheetReader, datadir):
846
847
    wb = Workbook
    ws = wb.create_sheet("Sheet")
848
    datadir.chdir()
849
850
851
    src = "complex-styles-worksheet.xml"
    reader = WorksheetReader(ws, src, wb.shared_strings, data_only=False)
    return reader
852
853
854
855
856


class TestWorksheetReader:


857
858
859
860
    def test_cells(self, PrimedWorksheetReader):
        reader = PrimedWorksheetReader
        reader.bind_cells()
        ws = reader.ws
861

862
        assert ws['C1'].value == 'a'
863
864
        assert ws.formula_attributes == {'E2': {'ref':"E2:E11", 't':"array"}}
        assert ws['E2'].value == "=C2:C11*D2:D11"
865

866

867
868
869
870
    def test_formatting(self, PrimedWorksheetReader):
        reader = PrimedWorksheetReader
        reader.bind_cells()
        ws = reader.ws
871
872

        reader.bind_formatting()
873

874
875
876
        fmts = ws.conditional_formatting
        assert fmts['T1:T10'][-1].dxf == DifferentialStyle()
        assert len(fmts['A1']) == 2
877
878
879
880
881
882
883
884
885
886
887


    def test_merged(self, PrimedWorksheetReader):
        reader = PrimedWorksheetReader
        reader.bind_cells()
        ws = reader.ws

        reader.bind_merged_cells()

        assert ws.merged_cells == "G18:H18 G23:H24 A18:B18"

CharlieC's avatar
CharlieC committed
888

889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
    @pytest.mark.parametrize(argnames="input_coordinate,expected_coordinate",
                             argvalues=[("H18", "G18"), ("G18", "G18"), ("I18", None), ("H23", "G23")])
    def test_normalize_merged_cell_link(self, PrimedWorksheetReader, input_coordinate, expected_coordinate):
        reader = PrimedWorksheetReader
        reader.bind_cells()
        reader.bind_merged_cells()

        normalized = reader.normalize_merged_cell_link(input_coordinate)

        if expected_coordinate is None:
            assert normalized is None
        else:
            assert normalized.coordinate == expected_coordinate


CharlieC's avatar
CharlieC committed
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
    def test_external_hyperlinks(self, PrimedWorksheetReader):
        reader = PrimedWorksheetReader
        reader.bind_cells()
        ws = reader.ws

        r = Relationship(type="hyperlink", Id="rId1", Target="../")
        rels = RelationshipList()
        rels.append(r)
        ws._rels = rels

        reader.bind_hyperlinks()

        assert ws['A1'].hyperlink.target == "../"


    def test_internal_hyperlinks(self, PrimedWorksheetReader):
        reader = PrimedWorksheetReader
        reader.bind_cells()
        ws = reader.ws

        r = Relationship(type="hyperlink", Id="rId1", Target="../")
        rels = RelationshipList()
        rels.append(r)
        ws._rels = rels

        reader.bind_hyperlinks()

        assert ws['B4'].hyperlink.location == "'STP nn000TL-10, PKG 2.52'!A1"
932
        assert ws['B4'].hyperlink.ref == "B4"
CharlieC's avatar
CharlieC committed
933
934


935
936
937
938
939
940
941
942
943
944
945
946
947
    def test_merged_hyperlinks(self, PrimedWorksheetReader):
        reader = PrimedWorksheetReader
        reader.bind_cells()
        ws = reader.ws

        r = Relationship(type="hyperlink", Id="rId1", Target="../")
        rels = RelationshipList()
        rels.append(r)
        ws._rels = rels

        reader.bind_merged_cells()
        reader.bind_hyperlinks()

948
949
950
951
        assert ws.merged_cells == "G18:H18 G23:H24 A18:B18"
        assert ws['A18'].hyperlink.display == 'http://test.com'
        assert ws['B18'].hyperlink is None

952
953
954
955
956
        # Link referencing H24 should be placed on G23 because H24 is a merged cell
        # and G23 is the top-left cell in the merged range
        assert ws["G23"].hyperlink.tooltip == "openpyxl"
        assert ws["H24"].hyperlink is None

957

CharlieC's avatar
CharlieC committed
958
959
960
961
962
963
964
965
966
967
968
969
970
    def test_tables(self, PrimedWorksheetReader):
        reader = PrimedWorksheetReader
        reader.bind_cells()
        ws = reader.ws

        r = Relationship(type="table", Id="rId1", Target="../tables/table1.xml")
        rels = RelationshipList()
        rels.append(r)
        ws._rels = rels

        reader.bind_tables()

        assert reader.tables == ["../tables/table1.xml"]
CharlieC's avatar
CharlieC committed
971
972


973
974
975
976
977
978
979
980
    def test_cols(self, PrimedWorksheetReader):
        reader = PrimedWorksheetReader
        reader.bind_cells()
        ws = reader.ws

        reader.bind_col_dimensions()

        assert len(ws.column_dimensions) == 5
981
        assert ws.column_dimensions['I'].parent == ws
982
983
984
985
986
987
988
989
990
991


    def test_rows(self, PrimedWorksheetReader):
        reader = PrimedWorksheetReader
        reader.bind_cells()
        ws = reader.ws

        reader.bind_row_dimensions()

        assert len(ws.row_dimensions) == 7
992
        assert ws.row_dimensions[4].parent == ws
993
994
995
996
997
998
999
1000
1001
1002


    def test_properties(self, PrimedWorksheetReader):
        reader = PrimedWorksheetReader
        reader.bind_cells()
        ws = reader.ws

        reader.bind_properties()

        for k in ('page_margins', 'page_setup', 'views', 'sheet_format',
1003
                  'legacy_drawing', 'protection'):
1004
            assert getattr(ws, k) == getattr(reader.parser, k)
1005
1006
1007
1008
1009
1010
1011
1012


    def test_more_rows_than_cells(self, Workbook, WorksheetReader, datadir):
        ws = Workbook.create_sheet("Sheet")
        datadir.chdir()
        reader = WorksheetReader(ws, "more_rows_than_cells.xml", None, None)
        reader.bind_cells()
        assert ws._current_row == 3