def change_jiashe():
"""
修改假设表 date
:return:
"""
a = openpyxl.load_workbook('VBA.xlsm', data_only=False)
# a = openpyxl.load_workbook('data.xlsx', data_only=False)
# # set_data(a)
sheet = a.worksheets[4]
# # print(type(get_value("E4", sheet)))
data["假设表"]["E" + str(1)] = "J"
for i in range(2, 378):
value = get_value("E" + str(i), sheet)
if isinstance(value, str) and value.startswith("="):
print(value)
func = formulas.Parser().ast(value)[1].compile()
rows = {}
for j in func.inputs:
col, row = get_row_column(j)
if str(row) in rows:
rows[str(row)] += 1
else:
rows[str(row)] = 1
str_row = "J" + sorted(rows, key=lambda k: rows[k],
reverse=True)[0]
value = value.replace("INDIRECT($E$1&ROW())", str_row)
print(value)
data["假设表"]["E" + str(i)] = value
# data["财报"]["E" + str(i)] =
with open("data_假设表修改后的_json.txt", "w", encoding="utf-8") as f:
f.write(json.dumps(data))
def compute_sheet(self):
cursor = Transaction().connection.cursor()
table = sql.Table(self.data_table_name)
cursor.execute(*table.delete())
#fields = dict([(x.alias, x) for x in self.formulas])
direct_fields = [x.alias for x in self.formulas if not x.expression]
formula_fields = [x.alias for x in self.formulas if x.expression]
sql_fields = [
sql.Column(table, x) for x in direct_fields + formula_fields
]
parser = formulas.Parser()
formula_fields = [(x, parser.ast(x.expression)[1].compile()
if x.expression.startswith('=') else '')
for x in self.formulas if x.expression]
insert_values = []
checker = TimeoutChecker(self.timeout, self.timeout_exception)
if not formula_fields:
# If there are no formula_fields we can make the loop faster as
# we don't use OrderedDict and don't evaluate formulas
for records in self.dataset.get_data():
checker.check()
for record in records:
insert_values.append(
[getattr(record, x) for x in direct_fields])
else:
for records in self.dataset.get_data():
checker.check()
for record in records:
values = OrderedDict()
if direct_fields:
values.update(
OrderedDict([(x, getattr(record, x))
for x in direct_fields]))
if formula_fields:
for field, ast in formula_fields:
if field.expression.startswith('='):
inputs = []
for input_ in ast.inputs.keys():
# TODO: Check if input_ exists and raise
# proper user error Indeed, we should check
# de formulas when we move to active state
inputs.append(values[input_.lower()])
value = ast(inputs)[0]
else:
value = field.expression
ftype = FIELD_TYPE_PYTHON[field.type]
values[field] = ftype(value)
insert_values.append(list(values.values()))
if insert_values:
cursor.execute(*table.insert(sql_fields, insert_values))
def formula_error(self):
if not self.expression:
return
if not self.expression.startswith('='):
return
parser = formulas.Parser()
try:
builder = parser.ast(self.expression)[1]
# Find missing methods:
# https://github.com/vinci1it2000/formulas/issues/19#issuecomment-429793111
missing_methods = [
k for k, v in builder.dsp.function_nodes.items()
if v['function'] is formulas.functions.not_implemented
]
if missing_methods:
# When there are two occurrences of the same missing method,
# the function name returned looks like this:
#
# Sample formula: A(x) + A(y)
# missing_methods: ['A', 'A<0>']
#
# So in the line below we remove the '<0>' suffix
missing_methods = {x.split('<')[0] for x in missing_methods}
if len(missing_methods) == 1:
msg = 'Unknown method: '
else:
msg = 'Unknown methods: '
msg += (', '.join(missing_methods))
return ('error', msg)
ast = builder.compile()
missing = (set([x.lower()
for x in ast.inputs]) - self.previous_formulas())
if not missing:
return
return ('warning', 'Referenced alias "%s" not found. Ensure it is '
'declared before this formula.' % ', '.join(missing))
except formulas.errors.FormulaError as error:
msg = error.msg.replace('\n', ' ')
if error.args[1:]:
msg = msg % error.args[1:]
return ('error', msg)
def formula_parser(self, formulea):
return formulas.Parser().ast(formulea)[1].compile()
def _compile_condition(condition_lines: List[Text]) -> Any:
condition = "".join(condition_lines)
if not condition.startswith("="):
condition = "={}".format(condition)
return formulas.Parser().ast(condition)[1].compile() # type: ignore
def parameters_from_eq(eq):
eq.replace('**', '^')
eq = '=' + eq
compiled = formulas.Parser().ast(eq)[1].compile()
inputs = list(compiled.inputs)
return inputs
def get_ast(self):
parser = formulas.Parser()
ast = parser.ast(self.formula)[1].compile()
return ast
def get_inputs(self, name):
if not self.formula:
return
parser = formulas.Parser()
ast = parser.ast(self.formula)[1].compile()
return (' '.join([x for x in ast.inputs])).lower()
