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