def set_data_qitaxiangmuxinin(): a = openpyxl.load_workbook('VBA.xlsm', data_only=False) # a = openpyxl.load_workbook('data.xlsx', data_only=False) # # set_data(a) sheet = a.worksheets[9] for j in get_list_row("E"): for i in range(1, 10): data["其他项目信息"][j + str(i)] = get_value(j + str(i), sheet) with open("data_财报表修改后的_json.txt", "w", encoding="utf-8") as f: f.write(json.dumps(data))
def set_data_caibao(): a = openpyxl.load_workbook('VBA.xlsm', data_only=False) # a = openpyxl.load_workbook('data.xlsx', data_only=False) # # set_data(a) sheet = a.worksheets[5] for j in get_list_row("NC"): for i in range(1, 691): data["财报"][j + str(i)] = get_value(j + str(i), sheet) with open("data_财报表不用修改了_json.txt", "w", encoding="utf-8") as f: f.write(json.dumps(data))
def check_data(self): a = [] if self.data["财报"]["D34"] == "N": a.append(35) a.append(36) a.append(37) if self.data["财报"]["D42"] == "N": a.append(43) a.append(44) a.append(45) for j in a: for i in get_list_row("NC"): self.set_date(i + str(j), 0, "财报")
def set_fan_type_data(turbine, number=1): global data db2 = get_db2_data() df = pd.DataFrame(db2) val = df.loc[df[1] == str(turbine)] if not val.loc[::][0].tolist(): val = pd.DataFrame([[0] * 28]) list_come = get_list_row("AG") dicts_db2 = {1: "56", 2: "57", 3: "58"} # 机型高度 dicts_gaodu = {1: "58", 2: "59", 3: "60"} # 机型价格 dicts_jaige = {1: "66", 2: "67", 3: "68"} # 钢塔-重量 dicts_gangzhong = {1: "72", 2: '77', 3: '82'} # 锚栓-重量 dicts_maozhong = {1: "73", 2: '78', 3: '83'} # 混塔段-总价 dicts_hunta = {1: "73", 2: '78', 3: '83'} # 整机吊安装费(单价) dicts_diaozhuang = {1: "75", 2: '80', 3: '85'} # 垫层混凝土 dicts_dchnt = {1: "88", 2: '93', 3: '98'} # 混凝土 dicts_hnt = {1: "89", 2: '94', 3: '99'} # 钢筋 dicts_gj = {1: "90", 2: '95', 3: '100'} for i, v in enumerate(val.iloc[0]): if i == 1: continue # print(list_come[i + 1] + dicts_db2[number]) # print(v) # print(type(v)) data["DB2"][list_come[i + 1] + dicts_db2[number]] = v data["模型"]["E" + dicts_gaodu[number]] = val.iloc[0][9] # data["模型"]["E" + dicts_jaige[number]] = val.iloc[0][9] data["模型"]["E" + dicts_gangzhong[number]] = val.iloc[0][11] data["模型"]["E" + dicts_maozhong[number]] = val.iloc[0][22] data["模型"]["E" + dicts_diaozhuang[number]] = val.iloc[0][26] data["模型"]["E" + dicts_dchnt[number]] = val.iloc[0][16] data["模型"]["E" + dicts_hnt[number]] = val.iloc[0][17] data["模型"]["E" + dicts_gj[number]] = val.iloc[0][18]
def set_data(a): # 读取excel中所有单元格内容 lists = [[2, "M", 257], [3, "X", 67], [4, "U", 378], [5, "NC", 691], [6, "AC", 307], [7, "M", 451], [8, "AS", 132], [9, "E", 10], [10, "E", 38], [11, "E", 12], [12, "E", 51], [13, "E", 28], [14, "E", 18], [15, "Y", 36], [16, "AK", 223], [17, "H", 50], [18, "S", 40]] for k in lists: # 第几个工作表 sheet = a.worksheets[k[0]] # 获得sheet表名称 print("Worksheet name(s):", sheet.title) # # print(dir(sheet)) data = {} with open("data.txt", "r", encoding="utf-8") as f: data = f.read() data = eval(data) data[sheet.title] = {} lie_list = get_list_row(k[1]) for i in range(1, k[2]): for j in lie_list: data[sheet.title][j + str(i)] = get_value(j + str(i), sheet) with open("data.txt", "w", encoding="utf-8") as f: f.write(str(data))
def input_func(self, sheet=None, reference=None, input=None, old_value=None): # 返回input输入参数函数 if reference and input == reference: # 遇见OFFSET时设置标志位跳过当前位 return reference if "!" in input: # 遇见表跳转解析 ’!‘ new_sheet = input.split("!")[0] new_sheet = new_sheet.replace("'", "") input = input.split("!")[1] else: # 赋值表名不变 new_sheet = sheet if ":" in input: sum_sss = self.get_temp_data(input, new_sheet) if sum_sss == -999999: # 创建循环 start = input.split(":")[0] end = input.split(":")[1] # 解析循环的开始和结束 start_column, start_row = get_row_column(start) end_column, end_row = get_row_column(end, "") start_column_list = get_list_row(start_column) end_column_list = get_list_row(end_column) sum_sss = [] for i in range(start_row, end_row + 1): rows = [] for j in [ item for item in end_column_list if not item in start_column_list[:-1] ]: val = self.get_temp_data(j + str(i), new_sheet) if val == -999999: val = self.eng(self.get_date_value( j + str(i), new_sheet), new_sheet, old_value=j + str(i)) if isinstance(val, str) and val.startswith("="): val = self.eng(val, new_sheet, j + str(i)) val = check_value(type(val))(val) rows.append(val) # 添加循环出来的每一个值 self.set_temp_data(j + str(i), val, new_sheet) self.set_date(j + str(i), val, new_sheet) sum_sss.append(rows) if len(sum_sss) == 1: sum_sss = sum_sss[0] self.set_temp_data(input, sum_sss, new_sheet) sum_sss = np.array(sum_sss) return sum_sss else: val = self.get_temp_data(input, new_sheet) if val == -999999 or (isinstance(val, str) and val.startswith("=")): # 普通数据时直接去解析坐标的值 val = self.eng(self.get_date_value(input, new_sheet), new_sheet, input) if isinstance(val, str) and val.startswith("="): val = self.eng(val, new_sheet, input) val = check_value(type(val))(val) self.set_temp_data(input, val, new_sheet) self.set_date(input, val, new_sheet) return val