Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1# Copyright (c) 2010-2024 openpyxl
3"""Workbook is the top-level container for all document information."""
4from copy import copy
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
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
17from openpyxl.writer.excel import save_workbook
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
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
42from openpyxl.xml.constants import (
43 XLSM,
44 XLSX,
45 XLTM,
46 XLTX
47)
49INTEGER_TYPES = (int,)
51class Workbook:
52 """Workbook is the container for all other parts of the document."""
54 _read_only = False
55 _data_only = False
56 template = False
57 path = "/xl/workbook.xml"
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()
74 self._setup_styles()
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
84 if not self.write_only:
85 self._sheets.append(Worksheet(self))
87 self.rels = RelationshipList()
88 self.calculation = CalcProperties()
89 self.views = [BookView()]
92 def _setup_styles(self):
93 """Bootstrap styles"""
95 self._fonts = IndexedList()
96 self._fonts.add(DEFAULT_FONT)
98 self._alignments = IndexedList([Alignment()])
100 self._borders = IndexedList()
101 self._borders.add(DEFAULT_BORDER)
103 self._fills = IndexedList()
104 self._fills.add(DEFAULT_EMPTY_FILL)
105 self._fills.add(DEFAULT_GRAY_FILL)
107 self._number_formats = IndexedList()
108 self._date_formats = {}
109 self._timedelta_formats = {}
111 self._protections = IndexedList([Protection()])
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()
121 @property
122 def epoch(self):
123 if self._epoch == WINDOWS_EPOCH:
124 return WINDOWS_EPOCH
125 return MAC_EPOCH
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
135 @property
136 def read_only(self):
137 return self._read_only
139 @property
140 def data_only(self):
141 return self._data_only
143 @property
144 def write_only(self):
145 return self.__write_only
148 @property
149 def excel_base_date(self):
150 return self.epoch
152 @property
153 def active(self):
154 """Get the currently active sheet or None
156 :type: :class:`openpyxl.worksheet.worksheet.Worksheet`
157 """
158 try:
159 return self._sheets[self._active_sheet_index]
160 except IndexError:
161 pass
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")
180 idx = self._sheets.index(value)
181 self._active_sheet_index = idx
184 def create_sheet(self, title=None, index=None):
185 """Create a worksheet (at an optional index).
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
192 """
193 if self.read_only:
194 raise ReadOnlyWorkbookException('Cannot create new sheet in a read-only workbook')
196 if self.write_only :
197 new_ws = WriteOnlyWorksheet(parent=self, title=title)
198 else:
199 new_ws = Worksheet(parent=self, title=title)
201 self._add_sheet(sheet=new_ws, index=index)
202 return new_ws
205 def _add_sheet(self, sheet, index=None):
206 """Add an worksheet (at an optional index)."""
208 if not isinstance(sheet, (Worksheet, WriteOnlyWorksheet, Chartsheet)):
209 raise TypeError("Cannot be added to a workbook")
211 if sheet.parent != self:
212 raise ValueError("You cannot add worksheets from another workbook.")
214 if index is None:
215 self._sheets.append(sheet)
216 else:
217 self._sheets.insert(index, sheet)
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)
232 def remove(self, worksheet):
233 """Remove `worksheet` from this workbook."""
234 idx = self._sheets.index(worksheet)
235 self._sheets.remove(worksheet)
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)
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)
249 self._add_sheet(cs, index)
250 return cs
253 @deprecated("Use wb[sheetname]")
254 def get_sheet_by_name(self, name):
255 """Returns a worksheet by its name.
257 :param name: the name of the worksheet to look for
258 :type name: string
260 """
261 return self[name]
263 def __contains__(self, key):
264 return key in self.sheetnames
267 def index(self, worksheet):
268 """Return the index of a worksheet."""
269 return self.worksheets.index(worksheet)
272 @deprecated("Use wb.index(worksheet)")
273 def get_index(self, worksheet):
274 """Return the index of the worksheet."""
275 return self.index(worksheet)
277 def __getitem__(self, key):
278 """Returns a worksheet by its name.
280 :param name: the name of the worksheet to look for
281 :type name: string
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))
289 def __delitem__(self, key):
290 sheet = self[key]
291 self.remove(sheet)
293 def __iter__(self):
294 return iter(self.worksheets)
297 @deprecated("Use wb.sheetnames")
298 def get_sheet_names(self):
299 return self.sheetnames
301 @property
302 def worksheets(self):
303 """A list of sheets in this workbook
305 :type: list of :class:`openpyxl.worksheet.worksheet.Worksheet`
306 """
307 return [s for s in self._sheets if isinstance(s, (Worksheet, ReadOnlyWorksheet, WriteOnlyWorksheet))]
309 @property
310 def chartsheets(self):
311 """A list of Chartsheets in this workbook
313 :type: list of :class:`openpyxl.chartsheet.chartsheet.Chartsheet`
314 """
315 return [s for s in self._sheets if isinstance(s, Chartsheet)]
317 @property
318 def sheetnames(self):
319 """Returns the list of the names of worksheets in this workbook.
321 Names are returned in the worksheets order.
323 :type: list of strings
325 """
326 return [s.title for s in self._sheets]
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
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
340 self.defined_names[name] = defn
343 def add_named_style(self, style):
344 """
345 Add a named style
346 """
347 self._named_styles.append(style)
348 style.bind(self)
351 @property
352 def named_styles(self):
353 """
354 List available named styles
355 """
356 return self._named_styles.names
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.
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
373 def save(self, filename):
374 """Save the current workbook under the given `filename`.
375 Use this function instead of using an `ExcelWriter`.
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)
389 @property
390 def style_names(self):
391 """
392 List of named styles
393 """
394 return [s.name for s in self._named_styles]
397 def copy_worksheet(self, from_worksheet):
398 """Copy an existing worksheet in the current workbook
400 .. warning::
401 This function cannot copy worksheets between workbooks.
402 worksheets can only be copied within the workbook that they belong
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")
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
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()
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
436 if name in self.defined_names:
437 return True