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"""Write the workbook global settings to the archive."""
5from openpyxl.utils import quote_sheetname
6from openpyxl.xml.constants import (
7 ARC_APP,
8 ARC_CORE,
9 ARC_CUSTOM,
10 ARC_WORKBOOK,
11 PKG_REL_NS,
12 CUSTOMUI_NS,
13 ARC_ROOT_RELS,
14)
15from openpyxl.xml.functions import tostring, fromstring
17from openpyxl.packaging.relationship import Relationship, RelationshipList
18from openpyxl.workbook.defined_name import (
19 DefinedName,
20 DefinedNameList,
21)
22from openpyxl.workbook.external_reference import ExternalReference
23from openpyxl.packaging.workbook import ChildSheet, WorkbookPackage, PivotCache
24from openpyxl.workbook.properties import WorkbookProperties
25from openpyxl.utils.datetime import CALENDAR_MAC_1904
28def get_active_sheet(wb):
29 """
30 Return the index of the active sheet.
31 If the sheet set to active is hidden return the next visible sheet or None
32 """
33 visible_sheets = [idx for idx, sheet in enumerate(wb._sheets) if sheet.sheet_state == "visible"]
34 if not visible_sheets:
35 raise IndexError("At least one sheet must be visible")
37 idx = wb._active_sheet_index
38 sheet = wb.active
39 if sheet and sheet.sheet_state == "visible":
40 return idx
42 for idx in visible_sheets[idx:]:
43 wb.active = idx
44 return idx
46 return None
49class WorkbookWriter:
51 def __init__(self, wb):
52 self.wb = wb
53 self.rels = RelationshipList()
54 self.package = WorkbookPackage()
55 self.package.workbookProtection = wb.security
56 self.package.calcPr = wb.calculation
59 def write_properties(self):
61 props = WorkbookProperties() # needs a mapping to the workbook for preservation
62 if self.wb.code_name is not None:
63 props.codeName = self.wb.code_name
64 if self.wb.excel_base_date == CALENDAR_MAC_1904:
65 props.date1904 = True
66 self.package.workbookPr = props
69 def write_worksheets(self):
70 for idx, sheet in enumerate(self.wb._sheets, 1):
71 sheet_node = ChildSheet(name=sheet.title, sheetId=idx, id="rId{0}".format(idx))
72 rel = Relationship(type=sheet._rel_type, Target=sheet.path)
73 self.rels.append(rel)
75 if not sheet.sheet_state == 'visible':
76 if len(self.wb._sheets) == 1:
77 raise ValueError("The only worksheet of a workbook cannot be hidden")
78 sheet_node.state = sheet.sheet_state
79 self.package.sheets.append(sheet_node)
82 def write_refs(self):
83 for link in self.wb._external_links:
84 # need to match a counter with a workbook's relations
85 rId = len(self.wb.rels) + 1
86 rel = Relationship(type=link._rel_type, Target=link.path)
87 self.rels.append(rel)
88 ext = ExternalReference(id=rel.id)
89 self.package.externalReferences.append(ext)
92 def write_names(self):
93 defined_names = list(self.wb.defined_names.values())
95 for idx, sheet in enumerate(self.wb.worksheets):
96 quoted = quote_sheetname(sheet.title)
98 # local names
99 if sheet.defined_names:
100 names = sheet.defined_names.values()
101 for n in names:
102 n.localSheetId = idx
103 defined_names.extend(names)
105 if sheet.auto_filter:
106 name = DefinedName(name='_FilterDatabase', localSheetId=idx, hidden=True)
107 name.value = f"{quoted}!{sheet.auto_filter}"
108 defined_names.append(name)
110 if sheet.print_titles:
111 name = DefinedName(name="Print_Titles", localSheetId=idx)
112 name.value = sheet.print_titles
113 defined_names.append(name)
115 if sheet.print_area:
116 name = DefinedName(name="Print_Area", localSheetId=idx)
117 name.value = sheet.print_area
118 defined_names.append(name)
120 self.package.definedNames = DefinedNameList(definedName=defined_names)
123 def write_pivots(self):
124 pivot_caches = set()
125 for pivot in self.wb._pivots:
126 if pivot.cache not in pivot_caches:
127 pivot_caches.add(pivot.cache)
128 c = PivotCache(cacheId=pivot.cacheId)
129 self.package.pivotCaches.append(c)
130 rel = Relationship(Type=pivot.cache.rel_type, Target=pivot.cache.path)
131 self.rels.append(rel)
132 c.id = rel.id
133 #self.wb._pivots = [] # reset
136 def write_views(self):
137 active = get_active_sheet(self.wb)
138 if self.wb.views:
139 self.wb.views[0].activeTab = active
140 self.package.bookViews = self.wb.views
143 def write(self):
144 """Write the core workbook xml."""
146 self.write_properties()
147 self.write_worksheets()
148 self.write_names()
149 self.write_pivots()
150 self.write_views()
151 self.write_refs()
153 return tostring(self.package.to_tree())
156 def write_rels(self):
157 """Write the workbook relationships xml."""
159 styles = Relationship(type='styles', Target='styles.xml')
160 self.rels.append(styles)
162 theme = Relationship(type='theme', Target='theme/theme1.xml')
163 self.rels.append(theme)
165 if self.wb.vba_archive:
166 vba = Relationship(type='', Target='vbaProject.bin')
167 vba.Type ='http://schemas.microsoft.com/office/2006/relationships/vbaProject'
168 self.rels.append(vba)
170 return tostring(self.rels.to_tree())
173 def write_root_rels(self):
174 """Write the package relationships"""
176 rels = RelationshipList()
178 rel = Relationship(type="officeDocument", Target=ARC_WORKBOOK)
179 rels.append(rel)
180 rel = Relationship(Type=f"{PKG_REL_NS}/metadata/core-properties", Target=ARC_CORE)
181 rels.append(rel)
183 rel = Relationship(type="extended-properties", Target=ARC_APP)
184 rels.append(rel)
186 if len(self.wb.custom_doc_props) >= 1:
187 rel = Relationship(type="custom-properties", Target=ARC_CUSTOM)
188 rels.append(rel)
190 if self.wb.vba_archive is not None:
191 # See if there was a customUI relation and reuse it
192 xml = fromstring(self.wb.vba_archive.read(ARC_ROOT_RELS))
193 root_rels = RelationshipList.from_tree(xml)
194 for rel in root_rels.find(CUSTOMUI_NS):
195 rels.append(rel)
197 return tostring(rels.to_tree())