产品提了一个倒排期需求,业务完成度和时间都有一定要求;必须完成,且有一部分数据是通过excel进行导入,且数据不算太大,但是列很多,一个月导一次
为了更好的将精力放在业务实现上,因此写了一个python来处理这种数据导入的问题,java写实在是太麻烦了,不管是POI还是easyExcel,而且最主要是这个后面完全会调三方接口来做,只是一个过渡期,大pyhton上场
import json
import os
import openpyxl
def file_save(str):
File_Name = "target.txt"
if os.path.exists(File_Name):
os.remove(File_Name)
with open(File_Name, 'a') as file_object:
file_object.write("{}".format(str))
pass
excel = openpyxl.load_workbook(filename="excel.xlsx")
## 获取表格sheet页名称
sheet_name = excel.sheetnames
print("sheet:", sheet_name)
## 获取sheet对象
sheet_obj = excel.worksheets
## 读取excel 信息
json_array = []
for sheet in sheet_obj:
## 获取标题
sheet_obj_title = sheet.title
sheet_obj_max_row = sheet.max_row
sheet_obj_max_column = sheet.max_column
print("sheet的标题:", sheet_obj_title, "行数:", sheet_obj_max_row, "列数:", sheet_obj_max_column)
## 读取每行数据
for rowIndex in range(2, sheet_obj_max_row + 1):
shop_name = sheet.cell(row=rowIndex, column=1).value
shop_id = sheet.cell(row=rowIndex, column=2).value
month = sheet.cell(row=rowIndex, column=3).value
target_A = sheet.cell(row=rowIndex, column=4).value
target_B = sheet.cell(row=rowIndex, column=5).value
target_C = sheet.cell(row=rowIndex, column=6).value
json_obj = {
"customer_shop_name": shop_name,
"customer_shop_id": shop_id,
"year_month": month,
"kpi_goal": target_A,
"gross_profit_goal": target_B,
"send_order_goal": target_C
}
json_array.append(json_obj)
print(json_array)
file_save(json.dumps(json_array, ensure_ascii=False))
[{"shop_id": 423, "year_month": 1, "kpi_goal": 1000, "gross_profit_goal": 1100, "send_order_goal": 1200}, {"shop_id": 586, "year_month": 2, "kpi_goal": 2000, "gross_profit_goal": 2100, "send_order_goal": 2200}]
写个插入接口,齐活!