Coverage for /var/srv/projects/api.amasfac.comuna18.com/tmp/venv/lib/python3.9/site-packages/openpyxl/formula/translate.py: 23%
66 statements
« prev ^ index » next coverage.py v6.4.4, created at 2023-07-17 14:22 -0600
1"""
2This module contains code to translate formulae across cells in a worksheet.
4The idea is that if A1 has formula "=B1+C1", then translating it to cell A2
5results in formula "=B2+C2". The algorithm relies on the formula tokenizer
6to identify the parts of the formula that need to change.
8"""
10import re
11from .tokenizer import Tokenizer, Token
12from openpyxl.utils import (
13 coordinate_to_tuple,
14 column_index_from_string,
15 get_column_letter
16)
18class TranslatorError(Exception):
19 """
20 Raised when a formula can't be translated across cells.
22 This error arises when a formula's references would be translated outside
23 the worksheet's bounds on the top or left. Excel represents these
24 situations with a #REF! literal error. E.g., if the formula at B2 is
25 '=A1', attempting to translate the formula to B1 raises TranslatorError,
26 since there's no cell above A1. Similarly, translating the same formula
27 from B2 to A2 raises TranslatorError, since there's no cell to the left of
28 A1.
30 """
33class Translator(object):
35 """
36 Modifies a formula so that it can be translated from one cell to another.
38 `formula`: The str string to translate. Must include the leading '='
39 character.
40 `origin`: The cell address (in A1 notation) where this formula was
41 defined (excluding the worksheet name).
43 """
45 def __init__(self, formula, origin):
46 # Excel errors out when a workbook has formulae in R1C1 notation,
47 # regardless of the calcPr:refMode setting, so I'm assuming the
48 # formulae stored in the workbook must be in A1 notation.
49 self.row, self.col = coordinate_to_tuple(origin)
50 self.tokenizer = Tokenizer(formula)
52 def get_tokens(self):
53 "Returns a list with the tokens comprising the formula."
54 return self.tokenizer.items
56 ROW_RANGE_RE = re.compile(r"(\$?[1-9][0-9]{0,6}):(\$?[1-9][0-9]{0,6})$")
57 COL_RANGE_RE = re.compile(r"(\$?[A-Za-z]{1,3}):(\$?[A-Za-z]{1,3})$")
58 CELL_REF_RE = re.compile(r"(\$?[A-Za-z]{1,3})(\$?[1-9][0-9]{0,6})$")
60 @staticmethod
61 def translate_row(row_str, rdelta):
62 """
63 Translate a range row-snippet by the given number of rows.
64 """
65 if row_str.startswith('$'):
66 return row_str
67 else:
68 new_row = int(row_str) + rdelta
69 if new_row <= 0:
70 raise TranslatorError("Formula out of range")
71 return str(new_row)
73 @staticmethod
74 def translate_col(col_str, cdelta):
75 """
76 Translate a range col-snippet by the given number of columns
77 """
78 if col_str.startswith('$'):
79 return col_str
80 else:
81 try:
82 return get_column_letter(
83 column_index_from_string(col_str) + cdelta)
84 except ValueError:
85 raise TranslatorError("Formula out of range")
87 @staticmethod
88 def strip_ws_name(range_str):
89 "Splits out the worksheet reference, if any, from a range reference."
90 # This code assumes that named ranges cannot contain any exclamation
91 # marks. Excel refuses to create these (even using VBA), and
92 # complains of a corrupt workbook when there are names with
93 # exclamation marks. The ECMA spec only states that named ranges will
94 # be of `ST_Xstring` type, which in theory allows '!' (char code
95 # 0x21) per http://www.w3.org/TR/xml/#charsets
96 if '!' in range_str:
97 sheet, range_str = range_str.rsplit('!', 1)
98 return sheet + "!", range_str
99 return "", range_str
101 @classmethod
102 def translate_range(cls, range_str, rdelta, cdelta):
103 """
104 Translate an A1-style range reference to the destination cell.
106 `rdelta`: the row offset to add to the range
107 `cdelta`: the column offset to add to the range
108 `range_str`: an A1-style reference to a range. Potentially includes
109 the worksheet reference. Could also be a named range.
111 """
112 ws_part, range_str = cls.strip_ws_name(range_str)
113 match = cls.ROW_RANGE_RE.match(range_str) # e.g. `3:4`
114 if match is not None:
115 return (ws_part + cls.translate_row(match.group(1), rdelta) + ":"
116 + cls.translate_row(match.group(2), rdelta))
117 match = cls.COL_RANGE_RE.match(range_str) # e.g. `A:BC`
118 if match is not None:
119 return (ws_part + cls.translate_col(match.group(1), cdelta) + ':'
120 + cls.translate_col(match.group(2), cdelta))
121 if ':' in range_str: # e.g. `A1:B5`
122 # The check is necessarily general because range references can
123 # have one or both endpoints specified by named ranges. I.e.,
124 # `named_range:C2`, `C2:named_range`, and `name1:name2` are all
125 # valid references. Further, Excel allows chaining multiple
126 # colons together (with unclear meaning)
127 return ws_part + ":".join(
128 cls.translate_range(piece, rdelta, cdelta)
129 for piece in range_str.split(':'))
130 match = cls.CELL_REF_RE.match(range_str)
131 if match is None: # Must be a named range
132 return range_str
133 return (ws_part + cls.translate_col(match.group(1), cdelta)
134 + cls.translate_row(match.group(2), rdelta))
136 def translate_formula(self, dest=None, row_delta=0, col_delta=0):
137 """
138 Convert the formula into A1 notation, or as row and column coordinates
140 The formula is converted into A1 assuming it is assigned to the cell
141 whose address is `dest` (no worksheet name).
143 """
144 tokens = self.get_tokens()
145 if not tokens:
146 return ""
147 elif tokens[0].type == Token.LITERAL:
148 return tokens[0].value
149 out = ['=']
150 # per the spec:
151 # A compliant producer or consumer considers a defined name in the
152 # range A1-XFD1048576 to be an error. All other names outside this
153 # range can be defined as names and overrides a cell reference if an
154 # ambiguity exists. (I.18.2.5)
155 if dest:
156 row, col = coordinate_to_tuple(dest)
157 row_delta = row - self.row
158 col_delta = col - self.col
159 for token in tokens:
160 if (token.type == Token.OPERAND
161 and token.subtype == Token.RANGE):
162 out.append(self.translate_range(token.value, row_delta,
163 col_delta))
164 else:
165 out.append(token.value)
166 return "".join(out)