def init_new_col(sh, cur_col):
next_col = openpyxl.utils.get_column_letter(
openpyxl.utils.column_index_from_string(cur_col) + 1)
prev_col = openpyxl.utils.get_column_letter(
openpyxl.utils.column_index_from_string(cur_col) - 1)
for i, row in enumerate(
openpyxl.utils.rows_from_range('{0}2:{0}238'.format(cur_col))):
cell = sh[row[0]]
new_cell = sh[next_col + str(i + 2)]
new_cell._style = copy(cell._style)
if type(cell.value
) == str and '=' in cell.value: #cell contains a formula
formula = '=' + ''.join(
[item.value for item in Tokenizer(cell.value).items])
if prev_col in formula and 'SUM' not in formula:
formula = formula.replace(cur_col, next_col)
formula = formula.replace(prev_col, cur_col)
elif 'SUM' in formula:
sumpos = formula.find('SUM')
formula = formula[:sumpos].replace(
cur_col, next_col) + "SUM" + formula[sumpos + 3:].replace(
cur_col, next_col)
else:
formula = formula.replace(cur_col, next_col)
new_cell.value = formula
elif cell.value == 0:
new_cell.value = 0
#and finally, set column width
sh.column_dimensions[next_col].width = 14.5
def update_users_formula(self):
formula_column = column_index_from_string(Files.TOKENS_CURRENT_COL)
tokens_first_column = get_column_letter(self.nicknames_column + 1)
last_column = get_column_letter(self.sheet.max_column)
for row in self.sheet.iter_rows(min_row=2,
min_col=formula_column,
max_col=formula_column):
current_cell = row[0]
formula = current_cell.value
if formula:
tok = Tokenizer(formula)
tok.items[1].value = '{}{}:{}{}'.format(
tokens_first_column, current_cell.row, last_column,
current_cell.row)
current_cell.value = tok.render()
def destinations(self):
if self.type == "RANGE":
tok = Tokenizer("=" + self.value)
for part in tok.items:
if part.subtype == "RANGE":
m = SHEETRANGE_RE.match(part.value)
yield m.group('notquoted'), m.group('cells')
def formula_eval(self, excel_info, order_of_sheet, extra_info, formula_info):
for sheet, sheet_formulas in formula_info.items():
for rc, formula_str in sheet_formulas.items():
print 'formula', formula_str
tok = Tokenizer(formula_str)
tok_list = [[t.value, t.type, t.subtype] for t in tok.items]
ff = self.formula_expension(tok_list, excel_info, sheet)
print [tok, ff]
def TestInput(data):
fdp = atheris.FuzzedDataProvider(data)
#Initial tokenizer for random string and process it
Tokenizer(fdp.ConsumeString(200))
#Translate random string formulae
Translator(fdp.ConsumeString(200), origin="A1").translate_formula("B2")
def generate_tokens(text):
"""
token generator. Uses openpyxl.formula.Tokenizer to get tokens
"""
tok = Tokenizer(text)
for item in tok.items:
token = Token(item.type, item.subtype, item.value)
if token.type != "WHITE-SPACE":
yield token
def get_formula_sub(formula):
"""Takes in a formula, splits it and replaces the row number with a sub string {0} for later use."""
from openpyxl.formula import Tokenizer
formula_sub = "="
for t in Tokenizer(formula).items: # Tokenizer is part of openpyxl
if t.subtype is 'RANGE': # if token is a cell reference
t.value = t.value[
0] + "{0}" # replace row number with {0} e.g F3 -> F{0}
formula_sub += t.value # add it to formula_sub
else:
formula_sub += t.value # else do nothing and add it to formula_sub
return formula_sub
def expression_decoder_latex_equation(data, variables):
"""
Преобразование формулы из формата EXCEL в LaTex(mathjax) выражение.
:param data: Строка, которую надо преобразовать.
:param variables: Набор переменных, из которых будет составляться выражение.
:return: строка в синтаксисе LaTex(mathjax)
"""
done = ""
to_replace = {"\\times": "\\times ", "$": ""}
tok = Tokenizer(data)
for t in tok.items:
if t.value in "()":
done += t.value
elif t.type == "OPERATOR-INFIX":
done += t.value.replace("^", "**").replace(",", ".")
elif t.type == "FUNC":
value = t.value
if t.subtype == "OPEN":
to_repl = {"LN": "LOG", "TREND": "trend"}
for key, val in to_repl.items():
value = value.replace(key, val)
done += value
else:
done += value
elif t.type == "OPERAND" and t.subtype == "RANGE":
t.value = t.value.replace("$", "")
n = int("".join(filter(str.isdigit, t.value)))
if ":" in t.value:
v = t.value.split(":")
n = int("".join(filter(str.isdigit, v[0])))
done += "{}".format(n)
to_replace[n] = variables[n]["latex_symbol"]
elif t.value in ["ИСТИНА", "TRUE", "FALSE", "ЛОЖЬ"]:
continue
else:
done += "{}".format(n)
to_replace[n] = variables[n]["latex_symbol"]
elif t.type == "OPERAND" and t.subtype == "NUMBER":
done += str(t.value)
if not done:
return ""
done = py2tex(done, print_latex=False, print_formula=False)
for k, v in to_replace.items():
done = done.replace(str(k), str(v))
return done
def get_destinations(name_def):
"""Workaround for the bug in DefinedName.destinations"""
from openpyxl.formula import Tokenizer
from openpyxl.utils.cell import SHEETRANGE_RE
if name_def.type == "RANGE":
tok = Tokenizer("=" + name_def.value)
for part in tok.items:
if part.subtype == "RANGE":
m = SHEETRANGE_RE.match(part.value)
if m.group("quoted"):
sheet_name = m.group("quoted")
else:
sheet_name = m.group("notquoted")
yield sheet_name, m.group("cells")
def build_order(xls):
wb = load_workbook(filename=xls)
ws = wb['All items']
items = {}
for row in range(2, ws.max_row):
count = ws.cell(row, 1).value
if not count:
continue
try:
link = ws.cell(row, 2).hyperlink.target
name = ws.cell(row, 2).value
except:
value = ws.cell(row, 2).value
tok = Tokenizer(value)
if tok and len(tok.items) >= 4 and tok.items[0].value == "HYPERLINK(":
link = tok.items[1].value.strip('"\'')
name = tok.items[3].value.strip('"\'')
else:
logging.error("Could not parse link from: {}".format(value))
logging.error("\n".join("%12s%11s%9s" % (t.value, t.type, t.subtype) for t in tok.items))
continue
items[link] = {'name': name, 'count': count, 'link': link}
return items
def type(self):
tok = Tokenizer("=" + self.value)
parsed = tok.items[0]
if parsed.type == "OPERAND":
return parsed.subtype
return parsed.type
for row in sheet:
for cell in row:
if (is_header(cell)):
if (cell.coordinate in store_header.keys()):
store_header[cell.coordinate].append("1")
else:
store_header[cell.coordinate] = ["1"]
store_header = headerspread(cell, sheet, store_header)
for item in sheet._merged_cells:
if (item.find(cell.coordinate) != -1):
for r in sheet[item]:
for c in r:
store_header[c.coordinate] = ["1"]
store_header = headerspread(c, sheet, store_header)
elif (cell.data_type == 'f'):
tok = Tokenizer(cell._value)
tok.parse()
for token in tok.items:
if (re.match(r'[A-Z][0-9]+$', cell.value[1:])):
store_header = update1(sheet[cell.value[1:]], cell, store_header)
if (token.value == "AVERAGE(" or token.value == "SUM("):
refrange = re.search(r'[A-Z][0-9]+\:[A-Z][0-9]+', cell._value)
store_header = update2(cell, refrange.group(0), store_header)
# if (token.value == "*"):
# value = cell.value[1:]
# sub_cell1 = sheet[value.split("*")[0]]
# sub_cell2 = sheet[value.split("*")[1]]
# store_header = update2(sub_cell1, cell, store_header)
# store_header = update2(sub_cell2, cell, store_header)
# if (token.value == ""): # refer to other cell
def parse(formula):
tokenizer = Tokenizer(formula)
tokens = [t for t in tokenizer.items if t.type != "WHITE-SPACE"]
return expr.parse(tokens)
def print_to_doc(cell, field, document, last_paragraph):
data = None
if cell.value:
data = str(cell.value)
else:
return last_paragraph
if field == 'Name (First Name)':
last_paragraph.add_run('Nominee Information').bold = True
paragraph = document.add_paragraph('Full Name: ')
paragraph.add_run(data)
return paragraph
elif field == 'Organization Contact Name (First Name)':
paragraph = document.add_paragraph()
paragraph.add_run('Organization Contact Name: ')
paragraph.add_run(data)
return paragraph
elif field == 'Name (Last Name)' or field == 'Organization Contact Name (Last Name)':
last_paragraph.add_run(' ')
last_paragraph.add_run(data)
return
elif field == 'Email' or field == 'Phone Number':
paragraph = document.add_paragraph()
paragraph.add_run(field)
paragraph.add_run(': ')
paragraph.add_run(data)
return
elif field == 'Name of Company':
paragraph = document.add_paragraph()
paragraph.add_run(field).bold = True
paragraph.add_run(': ').bold = True
paragraph.add_run(data)
return
elif field == 'LinkedIn:' or field == 'Social Media Profile:' or field == 'Other Public Profiles (e.g. website):':
global social_media
if social_media == 0:
document.add_paragraph()
last_paragraph = document.add_paragraph().add_run('Public Profile (e.g. LinkedIn, website)').bold = True
social_media = 1
last_paragraph = document.add_paragraph()
last_paragraph.add_run(field)
last_paragraph.add_run(' ')
if "=" in data:
tok = Tokenizer(data)
for t in tok.items:
if t.type == "OPERAND" and t.value != '""':
last_paragraph.add_run(t.value[1:len(t.value) - 1])
return last_paragraph
last_paragraph.add_run(data)
return last_paragraph
elif "upload" in field.lower():
tok = Tokenizer(data)
for t in tok.items:
if t.type == "OPERAND" and t.value != '""':
document.add_paragraph()
paragraph = document.add_paragraph().add_run(field).bold = True
paragraph = document.add_paragraph(t.value[1:len(t.value) - 1])
return paragraph
return last_paragraph
else:
document.add_paragraph()
document.add_paragraph().add_run(field).bold = True
paragraph = document.add_paragraph(data)
return paragraph
def findequations(name):
'''Takes all formula's from a excel work book and translates each to the equivalent expression.
Multicell ranges are expanded to a comma separated list. \n
The ordinary operators and the SUM function can be handled. If you need more functions. You have to impelent them in the modelclass.
In the model each cell reference is prefixed by <sheet name>_
Openpyxl is the fastest library and it has a tokenizer
but it can not read all values.
Therefor xlwings is used to read repeated formula's which Openpyxl will show as '='
input:
:name: Location of a excel sheeet
Returns:
:modeldic: A dictionary with formulars keyed by cell reference
'''
outdic = {}
wb = load_workbook(
name, read_only=True, data_only=False
) # to read the spresdsheet first save as xml then write it again
wb2 = xw.Book(name) # the same worksheet in xlwings
allsheets = wb.get_sheet_names()
for wsname in allsheets:
ws = wb[wsname]
ws2 = wb2.sheets(wsname) # the same sheet but in xlwings
formulacell = [
c for row in ws.rows for c in row
if c.value != None and c.data_type == 'f'
]
for cell in formulacell:
cellref = get_column_letter(cell.column) + str(cell.row)
if DEBUG: print('This cell:', cellref, cell.data_type, cell.value)
frml = cell.value if cell.value != '=' else ws2.range(
cellref).formula # To avoid emty repeating formula'rs
tok = Tokenizer(frml)
if DEBUG and False:
print("\n".join("%19s%15s%9s" % (t.value, t.type, t.subtype)
for t in tok.items))
# left hand term is <worksheet>!<column><row>=
lhs = wstrans(wsname) + get_column_letter(cell.column) + str(
cell.row)
out = [lhs + '=']
for t in tok.items:
if t.subtype == "RANGE":
#Find or create the sheetname
sheet0 = t.value.split(
'!')[0] if '!' in t.value else wsname
sheet = wstrans(sheet0)
# print(t.value,'---->')
# Get all the cells in the range columwize
# the nested list comprehension makes the list works for square ranges.
# the split construct drops the sheet name from the range name if any
thisrange = [
sheet + i for subtupler in cols_from_range((
t.value.split('!')[-1])) for i in subtupler
]
# put a ',' between each element i the list
thistext = ','.join(thisrange)
#print(thisrange)
out = out + [thistext]
else:
out.append(t.value)
#create the equation and get rid of the !
equation = ''.join(out).replace('!', '_')
outdic[lhs] = equation
#print(equation)
return outdic
def _parse_formula(sheet, cell_f, bu, sm):
"""
_parse_formula(sheet, cell_f, bu) -> bool
--``sheet`` is an instance of openpyxl.WorkSheet with Values
--``cell_f`` is an instance of openpyxl.Cell with Formulas as str
--``bu`` is the instance of EngineModel.BusinessUnit
--``sm`` is an instance of SheetMap
Function takes a formula string from excel and creates a driver that will
provide the same value in the Blackbird Engine. Function returns False if
it was not able to parse the formula and True if the driver was added.
"""
f_id = FC.by_name["custom formula from tokens."]
formula = FC.issue(f_id)
row = cell_f.row
col = cell_f.col_idx
cell = sheet.cell(row=row, column=col) # data only cell
if cell.value is None:
return False
parse_formula_bool_cell = sheet.cell(row=row, column=sm.cols[ps.PARSE_FORMULA])
if not _check_truthy(parse_formula_bool_cell.value):
return False
line_name = sheet.cell(row=row, column=sm.cols[ps.LINE_NAME]).value
parent_name = sheet.cell(row=row, column=sm.cols[ps.PARENT_NAME]).value
stmt_name = sheet.cell(row=row, column=sm.cols[ps.STATEMENT]).value
stmt_str = stmt_name.casefold().strip()
statement = bu.financials.get_statement(stmt_str)
ancestors = [line_name]
if parent_name:
ancestors.insert(0, parent_name) # Insert in 1st position
line = statement.find_first(*ancestors)
if not line:
print("No line found!", ancestors)
return False
formula_str = cell_f.value
if not isinstance(formula_str, str):
return False
elif "!" in formula_str:
return False # Don't include other tabs "=Sheet2!A1"
else:
tok = Tokenizer(formula_str)
for t in tok.items:
if t.type == "FUNC" and t.subtype in ("OPEN", "CLOSE"):
# # print(t.value)
if t.value in ("IF(", "MAX(", "MIN(", "ROUND(", ")"):
# Allow simple functions like "=MAX(A1,B1)"
pass
else:
# Don't include all other functions like "=SUM(A1)"
return False
if ":" in t.value and t.subtype == "RANGE":
return False # Don't include ranged sources "A1:A8"
# if col == sm.cols["FIRST_PERIOD"]: # Only insert drivers in first column
if not line.get_driver():
data = dict()
i = 0
for t in tok.items:
i += 1
t_name = "token%02d" % i # token01, token02, ... token99
t_type = t_name + "_type"
t_fixed_dt = t_name + "_fixed_date"
t_rel_pd = t_name + "_relative_pd"
# # print("Token Attr:", t_name, t.value, t.type, t.subtype)
if t.type == "OPERAND" and t.subtype == "NUMBER": # "100"
data[t_name] = t.value
data[t_type] = "constant"
elif t.type == "OPERAND" and t.subtype == "LOGICAL": # "TRUE"
if t.value == "TRUE":
data[t_name] = True
elif t.value == "FALSE":
data[t_name] = False
# data[t_name] = t.value.title() # "FALSE" -> "False"
data[t_type] = "constant"
elif t.type == "OPERAND" and t.subtype == "TEXT": # "EBITDA < 0"
# t.value might be '"EBITDA<="', we just want "EBITDA<="
if t.value[1:-1] in ps.ALLOWABLE_XL_TEXT:
data[t_name] = t.value
else:
data[t_name] = ""
data[t_type] = "constant"
elif t.type == "OPERAND" and t.subtype == "RANGE": # "A1"
source_addr = t.value
source_cell = sheet.cell(source_addr)
source_row = source_cell.row
source_col = source_cell.col_idx
source_line_name = sheet.cell(row=source_row,
column=sm.cols[ps.LINE_NAME]).value
source_statement = sheet.cell(row=source_row,
column=sm.cols[ps.STATEMENT]).value
source_statement = source_statement.casefold().strip()
data[t_name] = source_line_name
data[t_type] = "source"
data[t_name + "_statement"] = source_statement
if source_addr[0] == "$":
# Fixed Dates (source is always same column)
data[t_fixed_dt] = sheet.cell(row=sm.rows["DATES"],
column=source_col).value
elif source_col != cell_f.col_idx:
# Relative Periods (n periods past or future)
data[t_rel_pd] = source_col - cell_f.col_idx
elif t.type == "OPERATOR-INFIX": # +, -, *, /, =
if t.value == "=":
# Equality comparisons are "=" in Excel and "==" in Python
data[t_name] = t.value = "=="
else:
data[t_name] = t.value
data[t_type] = "operator"
elif t.type == "OPERATOR-PREFIX": # 1st character +, -, *, /, =
data[t_name] = t.value
data[t_type] = "operator"
elif t.type == "PAREN": # "(" or ")"
data[t_name] = t.value
data[t_type] = "operator"
elif t.type == "FUNC":
if t.value in ("MAX(", "MIN(", "ROUND("):
# Excel functions same as lower case Python functions
data[t_name] = t.value.casefold()
else:
# Excel functions require a custom Python function ie "IF()"
data[t_name] = t.value
data[t_type] = "operator"
elif t.type == "SEP" and t.subtype == "ARG":
data[t_name] = t.value.casefold()
data[t_type] = "operator"
model = bu.relationships.model
dr_name = stmt_name + '>' + (parent_name or "") + ">" + line_name
driver = model.drivers.get_or_create(
dr_name,
data,
formula
)
line.assign_driver(driver.id.bbid)
return True
def write_to_excel(self,
mv,
DataFrame,
path='',
file_name='document.xlsx',
list_columns=None,
sheet=''):
if sheet == '':
list_sheet = dict(map(lambda ws: (ws.title, ws), mv.worksheets))
else:
list_sheet = [sheet]
for sheet in list_sheet:
get_descript_sheet = mv[sheet]
tmp_startrow = 0
for mark, (startcol,
startrow) in self.create_marks(get_descript_sheet,
list_columns):
self.log.message_debug("Finde mark: {}".format(mark))
if startrow != tmp_startrow and len(DataFrame.index) > 1:
tmp_startrow = startrow
get_descript_sheet.insert_rows(startrow + 1,
len(DataFrame))
for index, row in enumerate(DataFrame.loc[:,
[mark]].iterrows()):
if type(
get_descript_sheet.cell(
row=startrow + index,
column=startcol)).__name__ != 'MergedCell':
cell = get_descript_sheet.cell(row=startrow,
column=startcol)
new_cell = get_descript_sheet.cell(
row=startrow, column=startcol).offset(row=index,
column=0)
from openpyxl.formula.translate import Translator
from openpyxl.formula import Tokenizer
tok = Tokenizer(str(row[1][mark]))
if tok.formula.startswith('='):
new_cell.value = Translator(
row[1][mark],
origin=cell.coordinate).translate_formula(
row_delta=startrow - 2)
else:
new_cell.value = row[1][mark]
if cell.has_style:
new_cell.font = copy(cell.font)
new_cell.border = copy(cell.border)
new_cell.fill = copy(cell.fill)
new_cell.number_format = copy(cell.number_format)
new_cell.protection = copy(cell.protection)
new_cell.alignment = copy(cell.alignment)
for key, col, style in self.sp.pars_style_from_dataframe(
DataFrame.iloc[index]['Format']):
if col == mark:
if key == 'style':
get_descript_sheet.cell(
row=startrow + index,
column=startcol).style = style
if key == 'merge_cell':
get_descript_sheet.merge_cells(
start_row=startrow + index,
start_column=startcol,
end_row=startrow + index + style[1],
end_column=startcol + style[0])
if key == 'hide':
get_descript_sheet.row_dimensions[
startrow + index].hidden = True
new_cell.value = style
mv.save(os.path.join(path, file_name))
# mv.close()
self.log.message_debug("Write file: {}".format(path))
def expression_decoder(data):
"""
Преобразование формулы из формата EXCEL в выполняемою eval строку
:param data: Выражение, которое нужно преобразовать.
:return: строка пригодная для выполнения в eval
"""
done = ""
tok = Tokenizer(data)
# param_temp = ""
# is_trigger = "тригер" in data.lower()
# try:
# if is_trigger: # Парсинг условия тригера регулярками
#
# args = []
# for m in re.finditer(trigger_regex, data, re.MULTILINE):
#
# if m["name"]:
# if m["name"] in trigger_syn:
# done += trigger_syn[m["name"]]
# else:
# log.error("Ошибка парсинга триггера {}. Название не найдено")
#
# if m["args"]:
# done += "("
#
# for arg in re.finditer(args_regex,m["args"],re.MULTILINE):
# if arg["range"]:
# args.append(
# 'dbVar["{}",v]'.format('":"'.join(arg["range"].split(":"))).replace(' ','')
# )
#
# if arg["point_y"] and arg["point_x"]:
# args.append(
# '{{"x":dbVar["{x}",v],"y":dbVar["{y}",v]}}'.format(
# y=arg["point_y"], x=arg["point_x"]
# )
# )
#
# if arg["limits"]:
# for limit_s in arg["limits"].split(","):
#
# limit_temp = {"p": 1, "c": ''}
# for limit_p in re.finditer(
# r"(?P<percent>[-+]*[0-9]\d{0,2}(\.\d{1,2})?)%|(?P<digit>[\d,.]+)|(?P<ar>[-+]+)",
# limit_s,
# re.MULTILINE,
# ):
#
# if limit_p["percent"]:
# limit_temp["p"] = int(limit_p["percent"]) / 100
#
# if limit_p["ar"]:
# limit_temp["c"] += limit_p["ar"]
#
# if limit_p["digit"]:
# limit_temp["c"] += limit_p["digit"].replace(',','.')
# try:
# limit_temp['c']=eval(limit_temp['c'])
# except:
# log.error('Ошибка парсинга аргументов {}'.format(limit_temp['c']))
# args.append(str(limit_temp))
#
# done += ",".join(args) + ")"
# return done
#
# except Exception as e:
# print(format_exc())
# if "аппрокс" in data:
# ranges = []
# d = ""
# done = "linest("
# for m in re.finditer(linest_regex, data, re.MULTILINE):
# if m["range"]:
# ranges.append('dbVar["{}",v]'.format('":"'.join(m["range"].split(":"))))
# if m["d"]:
# d = m["d"]
# for i in ranges:
# done += i + ","
# return done + d + ")"
for t in tok.items:
if t.value in "()":
done += t.value
elif t.type == "OPERATOR-INFIX":
done += t.value.replace("^", "**")
elif t.type == "OPERAND" and t.subtype == "RANGE":
t.value = t.value.replace("$", "")
if ":" in t.value:
v = t.value.split(":")
if len(v) > 2:
done += t.value.split(":")[-1]
continue
a = '":"'.join(v)
done += 'dbVar["{}",v]'.format(a)
elif t.value in ["ИСТИНА", "TRUE", "FALSE", "ЛОЖЬ"]:
continue
else:
done += 'dbVar["{}",v]'.format(t.value)
elif t.type == "FUNC":
value = t.value
if t.subtype == "OPEN":
to_replace = {
"LN": "LOG",
"TREND": "trend",
'АППРОКС': 'approx',
'аппрокс': 'approx'
}
for key, val in to_replace.items():
value = value.replace(key, val)
done += value
else:
done += value
elif t.type == "OPERAND" and t.subtype == "NUMBER":
done += str(t.value)
elif t.type == "OPERAND":
if ":" in t.value:
done += t.value.split(":")[-1]
elif t.type == "SEP":
if t.value == ";":
done += ","
else:
done += t.value
elif t.type == "ARRAY":
pass
else:
done += t.value
return done
def isReferenced(change, formula):
changeSheet = change["sheet"]
changeCellCordinate = change["cell"]
formulaSheet = formula["sheet"]
formulaexpression = formula["value"]
logging.info("changeSheet {}".format(changeSheet))
logging.info("changeCellCordinate {}".format(changeCellCordinate))
logging.info("formulaSheet {}".format(formulaSheet))
logging.info("formulaexpression {}".format(formulaexpression))
# Tokenize formula and extract RANGE
tok = Tokenizer(formulaexpression)
#print("\n".join("%12s%11s%9s" % (t.value, t.type, t.subtype) for t in tok.items))
for token in tok.items:
if token.subtype == "RANGE":
logging.info("RANGE Found {}".format(token.value))
range = token.value
targetSheetName = formulaSheet
if "!" in range:
targetSheetName = range[:range.find('!')]
targetCordinate = range[range.find('!') +1:]
else:
targetCordinate = range
if targetSheetName != changeSheet:
continue
logging.info("targetSheetName {}".format(targetSheetName))
logging.info("targetCordinate {}".format(targetCordinate))
logging.info("changeCellCordinate {}".format(changeCellCordinate))
# Check changed cell cordinate is in the range or not
targetCordinate = targetCordinate.replace("$","")
changeCellCordinate = changeCellCordinate.replace("$","")
if ":" not in targetCordinate:
targetCordinate = "{}:{}".format(targetCordinate,targetCordinate)
if ":" not in changeCellCordinate:
changeCellCordinate = "{}:{}".format(changeCellCordinate,changeCellCordinate)
m = re.search("(.+):(.+)",targetCordinate)
targetStartCordinate = m.group(1)
targetEndCordinate = m.group(2)
logging.info('targetStartCordinate {}'.format(targetStartCordinate))
logging.info('targetEndCordinate {}'.format(targetEndCordinate))
m = re.search("([A-Z]+)([0-9]+)",targetStartCordinate)
targetStartCol = int(column_index_from_string(m.group(1)))
targetStartRow = int(m.group(2))
logging.info('targetStartCol {}'.format(targetStartCol))
logging.info('targetStartRow {}'.format(targetStartRow))
m = re.search("([A-Z]+)([0-9]+)",targetEndCordinate)
targetEndCol = int(column_index_from_string(m.group(1)))
targetEndRow = int(m.group(2))
logging.info('targetEndCol {}'.format(targetEndCol))
logging.info('targetStartRow {}'.format(targetEndRow))
m = re.search("(.+):(.+)",changeCellCordinate)
changeStartCordinate = m.group(1)
changeEndCordinate = m.group(2)
logging.info('changeStartCordinate {}'.format(changeStartCordinate))
logging.info('changeEndCordinate {}'.format(changeEndCordinate))
m = re.search("([A-Z]+)([0-9]+)",changeStartCordinate)
changeStartCol = int(column_index_from_string(m.group(1)))
changeStartRow = int(m.group(2))
logging.info('changeStartCol {}'.format(changeStartCol))
logging.info('changeStartRow {}'.format(changeStartRow))
m = re.search("([A-Z]+)([0-9]+)",changeEndCordinate)
changeEndCol = int(column_index_from_string(m.group(1)))
changeEndRow = int(m.group(2))
logging.info('changeEndCol {}'.format(changeEndCol))
logging.info('changeEndRow {}'.format(changeEndRow))
# Check change start cell is in the target range
if changeStartRow >= targetStartRow and changeStartRow <= targetEndRow and changeStartCol >= targetStartCol and changeStartCol <= targetEndCol:
logging.info('isReferenced: {}'.format("True"))
return True
# Check change end cell is in the target range
if changeEndRow >= targetStartRow and changeEndRow <= targetEndRow and changeEndCol >= targetStartCol and changeEndCol <= targetEndCol:
logging.info('isReferenced: {}'.format("True"))
return True
logging.info('isReferenced: {}'.format("False"))
return False