1# Copyright (c) 2010-2024 openpyxl
2
3from collections import defaultdict
4import re
5
6from openpyxl.descriptors.serialisable import Serialisable
7from openpyxl.descriptors import (
8 Alias,
9 String,
10 Integer,
11 Bool,
12 Sequence,
13 Descriptor,
14)
15from openpyxl.compat import safe_string
16from openpyxl.formula import Tokenizer
17from openpyxl.utils.cell import SHEETRANGE_RE
18
19RESERVED = frozenset(["Print_Area", "Print_Titles", "Criteria",
20 "_FilterDatabase", "Extract", "Consolidate_Area",
21 "Sheet_Title"])
22
23_names = "|".join(RESERVED)
24RESERVED_REGEX = re.compile(r"^_xlnm\.(?P<name>{0})".format(_names))
25
26
27class DefinedName(Serialisable):
28
29 tagname = "definedName"
30
31 name = String() # unique per workbook/worksheet
32 comment = String(allow_none=True)
33 customMenu = String(allow_none=True)
34 description = String(allow_none=True)
35 help = String(allow_none=True)
36 statusBar = String(allow_none=True)
37 localSheetId = Integer(allow_none=True)
38 hidden = Bool(allow_none=True)
39 function = Bool(allow_none=True)
40 vbProcedure = Bool(allow_none=True)
41 xlm = Bool(allow_none=True)
42 functionGroupId = Integer(allow_none=True)
43 shortcutKey = String(allow_none=True)
44 publishToServer = Bool(allow_none=True)
45 workbookParameter = Bool(allow_none=True)
46 attr_text = Descriptor()
47 value = Alias("attr_text")
48
49
50 def __init__(self,
51 name=None,
52 comment=None,
53 customMenu=None,
54 description=None,
55 help=None,
56 statusBar=None,
57 localSheetId=None,
58 hidden=None,
59 function=None,
60 vbProcedure=None,
61 xlm=None,
62 functionGroupId=None,
63 shortcutKey=None,
64 publishToServer=None,
65 workbookParameter=None,
66 attr_text=None
67 ):
68 self.name = name
69 self.comment = comment
70 self.customMenu = customMenu
71 self.description = description
72 self.help = help
73 self.statusBar = statusBar
74 self.localSheetId = localSheetId
75 self.hidden = hidden
76 self.function = function
77 self.vbProcedure = vbProcedure
78 self.xlm = xlm
79 self.functionGroupId = functionGroupId
80 self.shortcutKey = shortcutKey
81 self.publishToServer = publishToServer
82 self.workbookParameter = workbookParameter
83 self.attr_text = attr_text
84
85
86 @property
87 def type(self):
88 tok = Tokenizer("=" + self.value)
89 parsed = tok.items[0]
90 if parsed.type == "OPERAND":
91 return parsed.subtype
92 return parsed.type
93
94
95 @property
96 def destinations(self):
97 if self.type == "RANGE":
98 tok = Tokenizer("=" + self.value)
99 for part in tok.items:
100 if part.subtype == "RANGE":
101 m = SHEETRANGE_RE.match(part.value)
102 sheetname = m.group('notquoted') or m.group('quoted')
103 yield sheetname, m.group('cells')
104
105
106 @property
107 def is_reserved(self):
108 m = RESERVED_REGEX.match(self.name)
109 if m:
110 return m.group("name")
111
112
113 @property
114 def is_external(self):
115 return re.compile(r"^\[\d+\].*").match(self.value) is not None
116
117
118 def __iter__(self):
119 for key in self.__attrs__:
120 if key == "attr_text":
121 continue
122 v = getattr(self, key)
123 if v is not None:
124 if v in RESERVED:
125 v = "_xlnm." + v
126 yield key, safe_string(v)
127
128
129class DefinedNameDict(dict):
130
131 """
132 Utility class for storing defined names.
133 Allows access by name and separation of global and scoped names
134 """
135
136 def __setitem__(self, key, value):
137 if not isinstance(value, DefinedName):
138 raise TypeError("Value must be a an instance of DefinedName")
139 elif value.name != key:
140 raise ValueError("Key must be the same as the name")
141 super().__setitem__(key, value)
142
143
144 def add(self, value):
145 """
146 Add names without worrying about key and name matching.
147 """
148 self[value.name] = value
149
150
151class DefinedNameList(Serialisable):
152
153 tagname = "definedNames"
154
155 definedName = Sequence(expected_type=DefinedName)
156
157
158 def __init__(self, definedName=()):
159 self.definedName = definedName
160
161
162 def by_sheet(self):
163 """
164 Break names down into sheet locals and globals
165 """
166 names = defaultdict(DefinedNameDict)
167 for defn in self.definedName:
168 if defn.localSheetId is None:
169 if defn.name in ("_xlnm.Print_Titles", "_xlnm.Print_Area", "_xlnm._FilterDatabase"):
170 continue
171 names["global"][defn.name] = defn
172 else:
173 sheet = int(defn.localSheetId)
174 names[sheet][defn.name] = defn
175 return names
176
177
178 def _duplicate(self, defn):
179 """
180 Check for whether DefinedName with the same name and scope already
181 exists
182 """
183 for d in self.definedName:
184 if d.name == defn.name and d.localSheetId == defn.localSheetId:
185 return True
186
187
188 def __len__(self):
189 return len(self.definedName)