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