Source code

Download source archive ↓

src/openpyxl/workbook/_writer.py

1# Copyright (c) 2010-2024 openpyxl
2
3"""Write the workbook global settings to the archive."""
4
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
16
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
26
27
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")
36
37    idx = wb._active_sheet_index
38    sheet = wb.active
39    if sheet and sheet.sheet_state == "visible":
40        return idx
41
42    for idx in visible_sheets[idx:]:
43        wb.active = idx
44        return idx
45
46    return None
47
48
49class WorkbookWriter:
50
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
57
58
59    def write_properties(self):
60
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
67
68
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)
74
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)
80
81
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)
90
91
92    def write_names(self):
93        defined_names = list(self.wb.defined_names.values())
94
95        for idx, sheet in enumerate(self.wb.worksheets):
96            quoted = quote_sheetname(sheet.title)
97
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)
104
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)
109
110            if sheet.print_titles:
111                name = DefinedName(name="Print_Titles", localSheetId=idx)
112                name.value = sheet.print_titles
113                defined_names.append(name)
114
115            if sheet.print_area:
116                name = DefinedName(name="Print_Area", localSheetId=idx)
117                name.value = sheet.print_area
118                defined_names.append(name)
119
120        self.package.definedNames = DefinedNameList(definedName=defined_names)
121
122
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
134
135
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
141
142
143    def write(self):
144        """Write the core workbook xml."""
145
146        self.write_properties()
147        self.write_worksheets()
148        self.write_names()
149        self.write_pivots()
150        self.write_views()
151        self.write_refs()
152
153        return tostring(self.package.to_tree())
154
155
156    def write_rels(self):
157        """Write the workbook relationships xml."""
158
159        styles =  Relationship(type='styles', Target='styles.xml')
160        self.rels.append(styles)
161
162        theme =  Relationship(type='theme', Target='theme/theme1.xml')
163        self.rels.append(theme)
164
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)
169
170        return tostring(self.rels.to_tree())
171
172
173    def write_root_rels(self):
174        """Write the package relationships"""
175
176        rels = RelationshipList()
177
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)
182
183        rel = Relationship(type="extended-properties", Target=ARC_APP)
184        rels.append(rel)
185
186        if len(self.wb.custom_doc_props) >= 1:
187            rel = Relationship(type="custom-properties", Target=ARC_CUSTOM)
188            rels.append(rel)
189
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)
196
197        return tostring(rels.to_tree())