文件操作--Excel（xlsx格式）

之前：

import json

import requests
from openpyxl import load_workbook
#创建文件对象
wb = load_workbook("thirdData.xlsx")
ws = wb.active
print(ws.title)


for i in range(1,8):
    url = "http://var-derived.common.jc1.jieyue.com/api/invokeRuleFlow"

    data = {
        "sysCode": "loan",
        "frontTransNo": "id0001",
        "frontTransTime": "2019-05-2900:00:00",
        "rulePackage": "资产规则集/creditScoreCard3",
        "processId": "creditCardThree",
        "facts": {"TongDunInfo": {
                "className": "com.jieyue.urule.entity.TongDunInfoDTO",
                "phoneNodeScore":ws["D"+str(i)].value,  #获取文件的值
                "idBlackRat": ws["D"+str(i+7)].value,
                "idTotalCnt": ws["D"+str(i+14)].value,
                "phoneBlackCnt":ws["D"+str(i+21)].value,
                "homePhoneNodeScore":ws["D"+str(i+28)].value,"idNodeScore": 1
            }，"ScoreCardModelVO": {
                "className": "com.jieyue.urule.entity.ScoreCardModelVO"
            }
        }
    }
    headers = {"Content-Type": "application/json"}
    req = requests.post(url=url, data=json.dumps(data), headers=headers)
    res = json.loads(req.content)
#写入文件
    ws["H"+str(i)].value = res['responseBody']['facts']['TongDunDerivedInfo']['phoneNodeScore']
    ws["H"+str(i+7)].value = res['responseBody']['facts']['TongDunDerivedInfo']['idBlackRat']
    ws["H"+str(i+14)].value = res['responseBody']['facts']['TongDunDerivedInfo']['idTotalCnt']
    ws["H"+str(i+21)].value = res['responseBody']['facts']['TongDunDerivedInfo']['phoneBlackCnt']
    ws["H"+str(i+28)].value = res['responseBody']['facts']['TongDunDerivedInfo']['homePhoneNodeScore']
    wb.save("thirdData.xlsx")

 

优化：

import json
import time

import requests
from openpyxl import load_workbook

wb = load_workbook("3.0duotou.xlsx")
ws = wb.active

# ws = wb["人行"]
print(ws.title)

start = time.time()
try:
    for i in range(1,ws.max_row+1):   #最大行号
        url = "http://rule-jc1.jieyue.com/fintech-ruleEngine/api/ruleEngine/funcPointRuleHit/v1"

        data = {
         "funcType": "intoCheckModule",
         "interfaceNo": "10001","frontTransTime": "2019-04-02 13:23:55",
         "intoId": "120154630220",
         "moduleData": {
          "thirdRefuseV1": {
           "product_code": "PTL190600158",
           "product_name": "".join(str(ws["B"+str(i)].value).split()),  #空格处理
           "grade": "".join(str(ws["A"+str(i)].value).split()),   
           "search_num": "".join(str(ws["C"+str(i)].value).split()),    
           "customer_label": "B04",
           "card_used_limit": "200001",
           "loan_balance": "8000001",
           "shzx_search_num": "0","sh_search_num": "0",
           "overdue_total": "0"
          }
         }
        }
        headers = {"Content-Type": "application/json"}
        req = requests.post(url=url, data=json.dumps(data), headers=headers)
        res = json.loads(req.content)
        if res['responseBody']['dataMap'] != {}:   #多种情况考虑
            ws["H" + str(i)].value = res['responseBody']['dataMap']['refuseCd']
        else:
            ws["H" + str(i)].value = "异常用例"
        wb.save("3.0duotou.xlsx")
except Exception as e:   #异常处理
    raise e

end =time.time()
print("耗时：",end-start)    #耗时