Source code

Download source archive ↓

src/openpyxl/workbook/defined_name.py

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)