Source code

Download source archive ↓

src/openpyxl/workbook/workbook.py

1# Copyright (c) 2010-2024 openpyxl
2
3"""Workbook is the top-level container for all document information."""
4from copy import copy
5
6from openpyxl.compat import deprecated
7from openpyxl.worksheet.worksheet import Worksheet
8from openpyxl.worksheet._read_only import ReadOnlyWorksheet
9from openpyxl.worksheet._write_only import WriteOnlyWorksheet
10from openpyxl.worksheet.copier import WorksheetCopy
11
12from openpyxl.utils import quote_sheetname
13from openpyxl.utils.indexed_list import IndexedList
14from openpyxl.utils.datetime  import WINDOWS_EPOCH, MAC_EPOCH
15from openpyxl.utils.exceptions import ReadOnlyWorkbookException
16
17from openpyxl.writer.excel import save_workbook
18
19from openpyxl.styles.cell_style import StyleArray
20from openpyxl.styles.named_styles import NamedStyle
21from openpyxl.styles.differential import DifferentialStyleList
22from openpyxl.styles.alignment import Alignment
23from openpyxl.styles.borders import DEFAULT_BORDER
24from openpyxl.styles.fills import DEFAULT_EMPTY_FILL, DEFAULT_GRAY_FILL
25from openpyxl.styles.fonts import DEFAULT_FONT
26from openpyxl.styles.protection import Protection
27from openpyxl.styles.colors import COLOR_INDEX
28from openpyxl.styles.named_styles import NamedStyleList
29from openpyxl.styles.table import TableStyleList
30
31from openpyxl.chartsheet import Chartsheet
32from .defined_name import DefinedName, DefinedNameDict
33from openpyxl.packaging.core import DocumentProperties
34from openpyxl.packaging.custom import CustomPropertyList
35from openpyxl.packaging.relationship import RelationshipList
36from .child import _WorkbookChild
37from .protection import DocumentSecurity
38from .properties import CalcProperties
39from .views import BookView
40
41
42from openpyxl.xml.constants import (
43    XLSM,
44    XLSX,
45    XLTM,
46    XLTX
47)
48
49INTEGER_TYPES = (int,)
50
51class Workbook:
52    """Workbook is the container for all other parts of the document."""
53
54    _read_only = False
55    _data_only = False
56    template = False
57    path = "/xl/workbook.xml"
58
59    def __init__(self,
60                 write_only=False,
61                 iso_dates=False,
62                 ):
63        self._sheets = []
64        self._pivots = []
65        self._active_sheet_index = 0
66        self.defined_names = DefinedNameDict()
67        self._external_links = []
68        self.properties = DocumentProperties()
69        self.custom_doc_props = CustomPropertyList()
70        self.security = DocumentSecurity()
71        self.__write_only = write_only
72        self.shared_strings = IndexedList()
73
74        self._setup_styles()
75
76        self.loaded_theme = None
77        self.vba_archive = None
78        self.is_template = False
79        self.code_name = None
80        self.epoch = WINDOWS_EPOCH
81        self.encoding = "utf-8"
82        self.iso_dates = iso_dates
83
84        if not self.write_only:
85            self._sheets.append(Worksheet(self))
86
87        self.rels = RelationshipList()
88        self.calculation = CalcProperties()
89        self.views = [BookView()]
90
91
92    def _setup_styles(self):
93        """Bootstrap styles"""
94
95        self._fonts = IndexedList()
96        self._fonts.add(DEFAULT_FONT)
97
98        self._alignments = IndexedList([Alignment()])
99
100        self._borders = IndexedList()
101        self._borders.add(DEFAULT_BORDER)
102
103        self._fills = IndexedList()
104        self._fills.add(DEFAULT_EMPTY_FILL)
105        self._fills.add(DEFAULT_GRAY_FILL)
106
107        self._number_formats = IndexedList()
108        self._date_formats = {}
109        self._timedelta_formats = {}
110
111        self._protections = IndexedList([Protection()])
112
113        self._colors = COLOR_INDEX
114        self._cell_styles = IndexedList([StyleArray()])
115        self._named_styles = NamedStyleList()
116        self.add_named_style(NamedStyle(font=copy(DEFAULT_FONT), border=copy(DEFAULT_BORDER), builtinId=0))
117        self._table_styles = TableStyleList()
118        self._differential_styles = DifferentialStyleList()
119
120
121    @property
122    def epoch(self):
123        if self._epoch == WINDOWS_EPOCH:
124            return WINDOWS_EPOCH
125        return MAC_EPOCH
126
127
128    @epoch.setter
129    def epoch(self, value):
130        if value not in (WINDOWS_EPOCH, MAC_EPOCH):
131            raise ValueError("The epoch must be either 1900 or 1904")
132        self._epoch = value
133
134
135    @property
136    def read_only(self):
137        return self._read_only
138
139    @property
140    def data_only(self):
141        return self._data_only
142
143    @property
144    def write_only(self):
145        return self.__write_only
146
147
148    @property
149    def excel_base_date(self):
150        return self.epoch
151
152    @property
153    def active(self):
154        """Get the currently active sheet or None
155
156        :type: :class:`openpyxl.worksheet.worksheet.Worksheet`
157        """
158        try:
159            return self._sheets[self._active_sheet_index]
160        except IndexError:
161            pass
162
163    @active.setter
164    def active(self, value):
165        """Set the active sheet"""
166        if not isinstance(value, (_WorkbookChild, INTEGER_TYPES)):
167            raise TypeError("Value must be either a worksheet, chartsheet or numerical index")
168        if isinstance(value, INTEGER_TYPES):
169            self._active_sheet_index = value
170            return
171            #if self._sheets and 0 <= value < len(self._sheets):
172                #value = self._sheets[value]
173            #else:
174                #raise ValueError("Sheet index is outside the range of possible values", value)
175        if value not in self._sheets:
176            raise ValueError("Worksheet is not in the workbook")
177        if value.sheet_state != "visible":
178            raise ValueError("Only visible sheets can be made active")
179
180        idx = self._sheets.index(value)
181        self._active_sheet_index = idx
182
183
184    def create_sheet(self, title=None, index=None):
185        """Create a worksheet (at an optional index).
186
187        :param title: optional title of the sheet
188        :type title: str
189        :param index: optional position at which the sheet will be inserted
190        :type index: int
191
192        """
193        if self.read_only:
194            raise ReadOnlyWorkbookException('Cannot create new sheet in a read-only workbook')
195
196        if self.write_only :
197            new_ws = WriteOnlyWorksheet(parent=self, title=title)
198        else:
199            new_ws = Worksheet(parent=self, title=title)
200
201        self._add_sheet(sheet=new_ws, index=index)
202        return new_ws
203
204
205    def _add_sheet(self, sheet, index=None):
206        """Add an worksheet (at an optional index)."""
207
208        if not isinstance(sheet, (Worksheet, WriteOnlyWorksheet, Chartsheet)):
209            raise TypeError("Cannot be added to a workbook")
210
211        if sheet.parent != self:
212            raise ValueError("You cannot add worksheets from another workbook.")
213
214        if index is None:
215            self._sheets.append(sheet)
216        else:
217            self._sheets.insert(index, sheet)
218
219
220    def move_sheet(self, sheet, offset=0):
221        """
222        Move a sheet or sheetname
223        """
224        if not isinstance(sheet, Worksheet):
225            sheet = self[sheet]
226        idx = self._sheets.index(sheet)
227        del self._sheets[idx]
228        new_pos = idx + offset
229        self._sheets.insert(new_pos, sheet)
230
231
232    def remove(self, worksheet):
233        """Remove `worksheet` from this workbook."""
234        idx = self._sheets.index(worksheet)
235        self._sheets.remove(worksheet)
236
237
238    @deprecated("Use wb.remove(worksheet) or del wb[sheetname]")
239    def remove_sheet(self, worksheet):
240        """Remove `worksheet` from this workbook."""
241        self.remove(worksheet)
242
243
244    def create_chartsheet(self, title=None, index=None):
245        if self.read_only:
246            raise ReadOnlyWorkbookException("Cannot create new sheet in a read-only workbook")
247        cs = Chartsheet(parent=self, title=title)
248
249        self._add_sheet(cs, index)
250        return cs
251
252
253    @deprecated("Use wb[sheetname]")
254    def get_sheet_by_name(self, name):
255        """Returns a worksheet by its name.
256
257        :param name: the name of the worksheet to look for
258        :type name: string
259
260        """
261        return self[name]
262
263    def __contains__(self, key):
264        return key in self.sheetnames
265
266
267    def index(self, worksheet):
268        """Return the index of a worksheet."""
269        return self.worksheets.index(worksheet)
270
271
272    @deprecated("Use wb.index(worksheet)")
273    def get_index(self, worksheet):
274        """Return the index of the worksheet."""
275        return self.index(worksheet)
276
277    def __getitem__(self, key):
278        """Returns a worksheet by its name.
279
280        :param name: the name of the worksheet to look for
281        :type name: string
282
283        """
284        for sheet in self.worksheets + self.chartsheets:
285            if sheet.title == key:
286                return sheet
287        raise KeyError("Worksheet {0} does not exist.".format(key))
288
289    def __delitem__(self, key):
290        sheet = self[key]
291        self.remove(sheet)
292
293    def __iter__(self):
294        return iter(self.worksheets)
295
296
297    @deprecated("Use wb.sheetnames")
298    def get_sheet_names(self):
299        return self.sheetnames
300
301    @property
302    def worksheets(self):
303        """A list of sheets in this workbook
304
305        :type: list of :class:`openpyxl.worksheet.worksheet.Worksheet`
306        """
307        return [s for s in self._sheets if isinstance(s, (Worksheet, ReadOnlyWorksheet, WriteOnlyWorksheet))]
308
309    @property
310    def chartsheets(self):
311        """A list of Chartsheets in this workbook
312
313        :type: list of :class:`openpyxl.chartsheet.chartsheet.Chartsheet`
314        """
315        return [s for s in self._sheets if isinstance(s, Chartsheet)]
316
317    @property
318    def sheetnames(self):
319        """Returns the list of the names of worksheets in this workbook.
320
321        Names are returned in the worksheets order.
322
323        :type: list of strings
324
325        """
326        return [s.title for s in self._sheets]
327
328
329    @deprecated("Assign scoped named ranges directly to worksheets or global ones to the workbook. Deprecated in 3.1")
330    def create_named_range(self, name, worksheet=None, value=None, scope=None):
331        """Create a new named_range on a worksheet
332
333        """
334        defn = DefinedName(name=name)
335        if worksheet is not None:
336            defn.value = "{0}!{1}".format(quote_sheetname(worksheet.title), value)
337        else:
338            defn.value = value
339
340        self.defined_names[name] = defn
341
342
343    def add_named_style(self, style):
344        """
345        Add a named style
346        """
347        self._named_styles.append(style)
348        style.bind(self)
349
350
351    @property
352    def named_styles(self):
353        """
354        List available named styles
355        """
356        return self._named_styles.names
357
358
359    @property
360    def mime_type(self):
361        """
362        The mime type is determined by whether a workbook is a template or
363        not and whether it contains macros or not. Excel requires the file
364        extension to match but openpyxl does not enforce this.
365
366        """
367        ct = self.template and XLTX or XLSX
368        if self.vba_archive:
369            ct = self.template and XLTM or XLSM
370        return ct
371
372
373    def save(self, filename):
374        """Save the current workbook under the given `filename`.
375        Use this function instead of using an `ExcelWriter`.
376
377        .. warning::
378            When creating your workbook using `write_only` set to True,
379            you will only be able to call this function once. Subsequent attempts to
380            modify or save the file will raise an :class:`openpyxl.shared.exc.WorkbookAlreadySaved` exception.
381        """
382        if self.read_only:
383            raise TypeError("""Workbook is read-only""")
384        if self.write_only and not self.worksheets:
385            self.create_sheet()
386        save_workbook(self, filename)
387
388
389    @property
390    def style_names(self):
391        """
392        List of named styles
393        """
394        return [s.name for s in self._named_styles]
395
396
397    def copy_worksheet(self, from_worksheet):
398        """Copy an existing worksheet in the current workbook
399
400        .. warning::
401            This function cannot copy worksheets between workbooks.
402            worksheets can only be copied within the workbook that they belong
403
404        :param from_worksheet: the worksheet to be copied from
405        :return: copy of the initial worksheet
406        """
407        if self.__write_only or self._read_only:
408            raise ValueError("Cannot copy worksheets in read-only or write-only mode")
409
410        new_title = u"{0} Copy".format(from_worksheet.title)
411        to_worksheet = self.create_sheet(title=new_title)
412        cp = WorksheetCopy(source_worksheet=from_worksheet, target_worksheet=to_worksheet)
413        cp.copy_worksheet()
414        return to_worksheet
415
416
417    def close(self):
418        """
419        Close workbook file if open. Only affects read-only and write-only modes.
420        """
421        if hasattr(self, '_archive'):
422            self._archive.close()
423
424
425    def _duplicate_name(self, name):
426        """
427        Check for duplicate name in defined name list and table list of each worksheet.
428        Names are not case sensitive.
429        """
430        name = name.lower()
431        for sheet in self.worksheets:
432            for t in sheet.tables:
433                if name == t.lower():
434                    return True
435
436        if name in self.defined_names:
437            return True
438