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()