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

3
"""Workbook is the top-level container for all document information."""
4
from copy import copy
5

CharlieC's avatar
CharlieC committed
6
from openpyxl.compat import deprecated
7
from openpyxl.worksheet.worksheet import Worksheet
8
9
from openpyxl.worksheet._read_only import ReadOnlyWorksheet
from openpyxl.worksheet._write_only import WriteOnlyWorksheet
CharlieC's avatar
CharlieC committed
10
from openpyxl.worksheet.copier import WorksheetCopy
11

12
from openpyxl.utils import quote_sheetname
13
from openpyxl.utils.indexed_list import IndexedList
14
from openpyxl.utils.datetime  import WINDOWS_EPOCH, MAC_EPOCH
15
16
from openpyxl.utils.exceptions import ReadOnlyWorkbookException

17
from openpyxl.writer.excel import save_workbook
18

19
from openpyxl.styles.cell_style import StyleArray
20
from openpyxl.styles.named_styles import NamedStyle
21
from openpyxl.styles.differential import DifferentialStyleList
CharlieC's avatar
CharlieC committed
22
23
24
25
26
27
28
from openpyxl.styles.alignment import Alignment
from openpyxl.styles.borders import DEFAULT_BORDER
from openpyxl.styles.fills import DEFAULT_EMPTY_FILL, DEFAULT_GRAY_FILL
from openpyxl.styles.fonts import DEFAULT_FONT
from openpyxl.styles.protection import Protection
from openpyxl.styles.colors import COLOR_INDEX
from openpyxl.styles.named_styles import NamedStyleList
CharlieC's avatar
CharlieC committed
29
from openpyxl.styles.table import TableStyleList
30
31

from openpyxl.chartsheet import Chartsheet
CharlieC's avatar
CharlieC committed
32
from .defined_name import DefinedName, DefinedNameList
CharlieC's avatar
CharlieC committed
33
from openpyxl.packaging.core import DocumentProperties
34
from openpyxl.packaging.relationship import RelationshipList
35
from .child import _WorkbookChild
CharlieC's avatar
Merge    
CharlieC committed
36
from .protection import DocumentSecurity
37
from .properties import CalcProperties
CharlieC's avatar
CharlieC committed
38
from .views import BookView
39

40

41
42
43
44
45
46
47
from openpyxl.xml.constants import (
    XLSM,
    XLSX,
    XLTM,
    XLTX
)

CharlieC's avatar
CharlieC committed
48
INTEGER_TYPES = (int,)
49

50
class Workbook(object):
51
    """Workbook is the container for all other parts of the document."""
52

53
54
    _read_only = False
    _data_only = False
55
56
    template = False
    path = "/xl/workbook.xml"
57

58
    def __init__(self,
CharlieC's avatar
CharlieC committed
59
                 write_only=False,
60
                 iso_dates=False,
CharlieC's avatar
CharlieC committed
61
                 ):
62
        self._sheets = []
CharlieC's avatar
CharlieC committed
63
        self._pivots = []
64
        self._active_sheet_index = 0
65
        self.defined_names = DefinedNameList()
66
        self._external_links = []
67
68
        self.properties = DocumentProperties()
        self.security = DocumentSecurity()
CharlieC's avatar
CharlieC committed
69
        self.__write_only = write_only
CharlieC's avatar
CharlieC committed
70
        self.shared_strings = IndexedList()
saykharng's avatar
saykharng committed
71

72
        self._setup_styles()
saykharng's avatar
saykharng committed
73

74
        self.loaded_theme = None
75
        self.vba_archive = None
76
        self.is_template = False
CharlieC's avatar
CharlieC committed
77
        self.code_name = None
78
        self.epoch = WINDOWS_EPOCH
CharlieC's avatar
CharlieC committed
79
        self.encoding = "utf-8"
80
        self.iso_dates = iso_dates
81

82
        if not self.write_only:
83
            self._sheets.append(Worksheet(self))
84

85
        self.rels = RelationshipList()
86
        self.calculation = CalcProperties()
CharlieC's avatar
CharlieC committed
87
        self.views = [BookView()]
88

saykharng's avatar
saykharng committed
89

90
91
    def _setup_styles(self):
        """Bootstrap styles"""
CharlieC's avatar
CharlieC committed
92

93
        self._fonts = IndexedList()
CharlieC's avatar
CharlieC committed
94
95
        self._fonts.add(DEFAULT_FONT)

96
        self._alignments = IndexedList([Alignment()])
97

98
        self._borders = IndexedList()
CharlieC's avatar
CharlieC committed
99
100
        self._borders.add(DEFAULT_BORDER)

101
        self._fills = IndexedList()
CharlieC's avatar
CharlieC committed
102
103
104
        self._fills.add(DEFAULT_EMPTY_FILL)
        self._fills.add(DEFAULT_GRAY_FILL)

CharlieC's avatar
CharlieC committed
105
        self._number_formats = IndexedList()
106
        self._date_formats = {}
107
        self._timedelta_formats = {}
108

109
        self._protections = IndexedList([Protection()])
110

CharlieC's avatar
CharlieC committed
111
        self._colors = COLOR_INDEX
CharlieC's avatar
CharlieC committed
112
        self._cell_styles = IndexedList([StyleArray()])
113
        self._named_styles = NamedStyleList()
114
        self.add_named_style(NamedStyle(font=copy(DEFAULT_FONT), border=copy(DEFAULT_BORDER), builtinId=0))
CharlieC's avatar
CharlieC committed
115
        self._table_styles = TableStyleList()
116
        self._differential_styles = DifferentialStyleList()
117
118


119
120
    @property
    def epoch(self):
121
122
123
        if self._epoch == WINDOWS_EPOCH:
            return WINDOWS_EPOCH
        return MAC_EPOCH
124
125
126
127
128
129
130
131
132


    @epoch.setter
    def epoch(self, value):
        if value not in (WINDOWS_EPOCH, MAC_EPOCH):
            raise ValueError("The epoch must be either 1900 or 1904")
        self._epoch = value


133
134
    @property
    def read_only(self):
135
136
137
138
139
        return self._read_only

    @property
    def data_only(self):
        return self._data_only
140
141
142
143

    @property
    def write_only(self):
        return self.__write_only
144

145

CharlieC's avatar
CharlieC committed
146
147
148
149
    @property
    def excel_base_date(self):
        return self.epoch

150
151
    @property
    def active(self):
Stephen Schaub's avatar
Stephen Schaub committed
152
        """Get the currently active sheet or None
CharlieC's avatar
CharlieC committed
153

Stephen Schaub's avatar
Stephen Schaub committed
154
155
        :type: :class:`openpyxl.worksheet.worksheet.Worksheet`
        """
156
157
158
159
        try:
            return self._sheets[self._active_sheet_index]
        except IndexError:
            pass
160

161
162
163
    @active.setter
    def active(self, value):
        """Set the active sheet"""
164
        if not isinstance(value, (_WorkbookChild, INTEGER_TYPES)):
165
            raise TypeError("Value must be either a worksheet, chartsheet or numerical index")
166
        if isinstance(value, INTEGER_TYPES):
167
168
169
170
171
172
            self._active_sheet_index = value
            return
            #if self._sheets and 0 <= value < len(self._sheets):
                #value = self._sheets[value]
            #else:
                #raise ValueError("Sheet index is outside the range of possible values", value)
173
174
175
176
177
178
179
180
        if value not in self._sheets:
            raise ValueError("Worksheet is not in the workbook")
        if value.sheet_state != "visible":
            raise ValueError("Only visible sheets can be made active")

        idx = self._sheets.index(value)
        self._active_sheet_index = idx

181

182
    def create_sheet(self, title=None, index=None):
183
        """Create a worksheet (at an optional index).
184

185
        :param title: optional title of the sheet
CharlieC's avatar
CharlieC committed
186
        :type title: str
187
188
189
        :param index: optional position at which the sheet will be inserted
        :type index: int

190
        """
191
        if self.read_only:
192
193
            raise ReadOnlyWorkbookException('Cannot create new sheet in a read-only workbook')

194
        if self.write_only :
195
            new_ws = WriteOnlyWorksheet(parent=self, title=title)
196
        else:
197
            new_ws = Worksheet(parent=self, title=title)
198

199
        self._add_sheet(sheet=new_ws, index=index)
200
201
        return new_ws

202
203
204
205

    def _add_sheet(self, sheet, index=None):
        """Add an worksheet (at an optional index)."""

206
        if not isinstance(sheet, (Worksheet, WriteOnlyWorksheet, Chartsheet)):
207
208
209
            raise TypeError("Cannot be added to a workbook")

        if sheet.parent != self:
210
            raise ValueError("You cannot add worksheets from another workbook.")
211

212
        if index is None:
213
            self._sheets.append(sheet)
214
        else:
215
216
            self._sheets.insert(index, sheet)

217

CharlieC's avatar
CharlieC committed
218
219
220
221
222
223
224
225
226
227
228
229
    def move_sheet(self, sheet, offset=0):
        """
        Move a sheet or sheetname
        """
        if not isinstance(sheet, Worksheet):
            sheet = self[sheet]
        idx = self._sheets.index(sheet)
        del self._sheets[idx]
        new_pos = idx + offset
        self._sheets.insert(new_pos, sheet)


230
    def remove(self, worksheet):
Stephen Schaub's avatar
Stephen Schaub committed
231
        """Remove `worksheet` from this workbook."""
232
233
234
235
        idx = self._sheets.index(worksheet)
        localnames = self.defined_names.localnames(scope=idx)
        for name in localnames:
            self.defined_names.delete(name, scope=idx)
236
237
238
        self._sheets.remove(worksheet)


239
240
    @deprecated("Use wb.remove(worksheet) or del wb[sheetname]")
    def remove_sheet(self, worksheet):
Stephen Schaub's avatar
Stephen Schaub committed
241
        """Remove `worksheet` from this workbook."""
242
243
244
        self.remove(worksheet)


245
246
247
248
249
    def create_chartsheet(self, title=None, index=None):
        if self.read_only:
            raise ReadOnlyWorkbookException("Cannot create new sheet in a read-only workbook")
        cs = Chartsheet(parent=self, title=title)

CharlieC's avatar
CharlieC committed
250
        self._add_sheet(cs, index)
251
252
        return cs

253

CharlieC's avatar
CharlieC committed
254
    @deprecated("Use wb[sheetname]")
255
    def get_sheet_by_name(self, name):
256
257
        """Returns a worksheet by its name.

258
259
260
        :param name: the name of the worksheet to look for
        :type name: string

261
        """
CharlieC's avatar
CharlieC committed
262
        return self[name]
263

264
    def __contains__(self, key):
CharlieC's avatar
CharlieC committed
265
        return key in self.sheetnames
266

267
268
269
270
271
272
273

    def index(self, worksheet):
        """Return the index of a worksheet."""
        return self.worksheets.index(worksheet)


    @deprecated("Use wb.index(worksheet)")
274
    def get_index(self, worksheet):
275
        """Return the index of the worksheet."""
276
        return self.index(worksheet)
277

278
    def __getitem__(self, key):
279
280
281
282
283
284
        """Returns a worksheet by its name.

        :param name: the name of the worksheet to look for
        :type name: string

        """
285
        for sheet in self.worksheets + self.chartsheets:
CharlieC's avatar
CharlieC committed
286
287
288
            if sheet.title == key:
                return sheet
        raise KeyError("Worksheet {0} does not exist.".format(key))
289
290
291

    def __delitem__(self, key):
        sheet = self[key]
292
        self.remove(sheet)
293
294
295
296

    def __iter__(self):
        return iter(self.worksheets)

CharlieC's avatar
CharlieC committed
297
298

    @deprecated("Use wb.sheetnames")
299
    def get_sheet_names(self):
300
301
        return self.sheetnames

302
303
    @property
    def worksheets(self):
Stephen Schaub's avatar
Stephen Schaub committed
304
        """A list of sheets in this workbook
CharlieC's avatar
CharlieC committed
305

Stephen Schaub's avatar
Stephen Schaub committed
306
307
        :type: list of :class:`openpyxl.worksheet.worksheet.Worksheet`
        """
308
        return [s for s in self._sheets if isinstance(s, (Worksheet, ReadOnlyWorksheet, WriteOnlyWorksheet))]
309
310
311

    @property
    def chartsheets(self):
Stephen Schaub's avatar
Stephen Schaub committed
312
313
314
315
        """A list of Chartsheets in this workbook

        :type: list of :class:`openpyxl.chartsheet.chartsheet.Chartsheet`
        """
316
317
        return [s for s in self._sheets if isinstance(s, Chartsheet)]

318
319
    @property
    def sheetnames(self):
Stephen Schaub's avatar
Stephen Schaub committed
320
        """Returns the list of the names of worksheets in this workbook.
321
322
323

        Names are returned in the worksheets order.

Stephen Schaub's avatar
Stephen Schaub committed
324
        :type: list of strings
325

326
        """
327
        return [s.title for s in self._sheets]
328

329
    def create_named_range(self, name, worksheet=None, value=None, scope=None):
330
        """Create a new named_range on a worksheet"""
331
        defn = DefinedName(name=name, localSheetId=scope)
CharlieC's avatar
CharlieC committed
332
        if worksheet is not None:
333
            defn.value = "{0}!{1}".format(quote_sheetname(worksheet.title), value)
334
335
336
337
        else:
            defn.value = value

        self.defined_names.append(defn)
CharlieC's avatar
CharlieC committed
338

339

340
341
342
343
    def add_named_style(self, style):
        """
        Add a named style
        """
344
        self._named_styles.append(style)
345
346
347
        style.bind(self)


348
349
350
351
352
353
354
355
    @property
    def named_styles(self):
        """
        List available named styles
        """
        return self._named_styles.names


CharlieC's avatar
CharlieC committed
356
    @deprecated("Use workbook.defined_names.definedName")
357
    def get_named_ranges(self):
358
        """Return all named ranges"""
359
        return self.defined_names.definedName
360

361
362

    @deprecated("Use workbook.defined_names.append")
363
    def add_named_range(self, named_range):
364
        """Add an existing named_range to the list of named_ranges."""
CharlieC's avatar
CharlieC committed
365
        self.defined_names.append(named_range)
366

367

CharlieC's avatar
CharlieC committed
368
    @deprecated("Use workbook.defined_names[name]")
369
    def get_named_range(self, name):
370
        """Return the range specified by name."""
371
        return self.defined_names[name]
CharlieC's avatar
CharlieC committed
372

373

CharlieC's avatar
CharlieC committed
374
    @deprecated("Use del workbook.defined_names[name]")
375
    def remove_named_range(self, named_range):
376
        """Remove a named_range from this workbook."""
CharlieC's avatar
CharlieC committed
377
378
        del self.defined_names[named_range]

379

380
381
382
383
384
385
386
387
388
389
390
391
392
393
    @property
    def mime_type(self):
        """
        The mime type is determined by whether a workbook is a template or
        not and whether it contains macros or not. Excel requires the file
        extension to match but openpyxl does not enforce this.

        """
        ct = self.template and XLTX or XLSX
        if self.vba_archive:
            ct = self.template and XLTM or XLSM
        return ct


394
    def save(self, filename):
395
        """Save the current workbook under the given `filename`.
396
        Use this function instead of using an `ExcelWriter`.
397

398
        .. warning::
399
            When creating your workbook using `write_only` set to True,
400
401
402
            you will only be able to call this function once. Subsequents attempts to
            modify or save the file will raise an :class:`openpyxl.shared.exc.WorkbookAlreadySaved` exception.
        """
403
404
        if self.read_only:
            raise TypeError("""Workbook is read-only""")
405
406
407
        if self.write_only and not self.worksheets:
            self.create_sheet()
        save_workbook(self, filename)
408
409
410
411
412
413
414
415


    @property
    def style_names(self):
        """
        List of named styles
        """
        return [s.name for s in self._named_styles]
CharlieC's avatar
CharlieC committed
416
417
418
419


    def copy_worksheet(self, from_worksheet):
        """Copy an existing worksheet in the current workbook
Stephen Schaub's avatar
Stephen Schaub committed
420
421
422
423

        .. warning::
            This function cannot copy worksheets between workbooks.
            worksheets can only be copied within the workbook that they belong
CharlieC's avatar
CharlieC committed
424
425
426
427

        :param from_worksheet: the worksheet to be copied from
        :return: copy of the initial worksheet
        """
428
429
        if self.__write_only or self._read_only:
            raise ValueError("Cannot copy worksheets in read-only or write-only mode")
CharlieC's avatar
CharlieC committed
430

431
        new_title = u"{0} Copy".format(from_worksheet.title)
CharlieC's avatar
CharlieC committed
432
433
434
435
        to_worksheet = self.create_sheet(title=new_title)
        cp = WorksheetCopy(source_worksheet=from_worksheet, target_worksheet=to_worksheet)
        cp.copy_worksheet()
        return to_worksheet
CharlieC's avatar
CharlieC committed
436
437
438
439
440
441
442
443


    def close(self):
        """
        Close workbook file if open. Only affects read-only and write-only modes.
        """
        if hasattr(self, '_archive'):
            self._archive.close()
saykharng's avatar
saykharng committed
444

445

saykharng's avatar
saykharng committed
446
    def _duplicate_name(self, name):
saykharng's avatar
saykharng committed
447
448
        """
        Check for duplicate name in defined name list and table list of each worksheet.
449
        Names are not case sensitive.
saykharng's avatar
saykharng committed
450
        """
451
452
453
454
455
456
457
458
459
        name = name.lower()
        for sheet in self.worksheets:
            for t in sheet.tables:
                if name == t.lower():
                    return True

        if name in self.defined_names:
            return True