python处理接口返回数据并导出Excel格式文件

背景：项目做了重构，需要对原有功能的数据进行处理和对比，这就需要被需要的字段获取到Excel里，通过筛选条件直观的看出哪些功能正常，哪些功能不正常。

# -*- coding: UTF-8 -*-
import json
import requests
import sys

import xlrd
from openpyxl import load_workbook
from xlutils.copy import copy

reload(sys)
sys.setdefaultencoding('utf8')
url = "http://xxx/v1.0/schools/withServices?$count=true&$limit=200&$offset=0&_r=1600223532645&$filter=opened+eq+1&opened=1"
payload = "{}"
headers = {
    'Content-Type': 'application/json',
    'Authorization': 'Bearer "F4138BF83405E3B1B01A521337415C97A0DFDE23B417615AC35A650E0DAEFCED66CC11D183F04A25DE25ED31AD03BEAA055DE11107DFA4EC-00000000"',
    'sdp-app-id': '144fa1cf-b8c7-43b2-b88f-a37005e6f6c7',
    'userId': '2138822989'
}
response = requests.request("GET", url, headers=headers, data=payload)
print(response.text.encode('utf8'))
res = json.loads(response.text)
list = []
for d in res["items"]:
    # project.append(d["name"])
    # project.append(d["org_id"])
    # project.append(d["opened_num"])
    project = {}
    project["org_id"] = d["org_id"]
    project["opened_num"] = d["opened_num"]
    project["name"] = d["name"]
    list.append(project)
#导出Excel格式的文件
#方法一：
# 解决乱码问题
print json.dumps(list, encoding="UTF-8", ensure_ascii=False)
param = json.dumps(project, encoding="UTF-8", ensure_ascii=False)
wb = load_workbook(r"D:\workplace\batchGetData\test.xlsx")
sheet = wb["Sheet1"]
start_row_num = 1
index = 0
sheet.cell(1, 1).value = "opened_num"
sheet.cell(1, 2).value = "org_id"
sheet.cell(1, 3).value = "name"
# 把数值填充到excel表格中
for one in list:
    index += 1
    column_index = 1
    for key, value in one.items():
        sheet.cell(start_row_num + index, column_index).value = value
        column_index = column_index + 1
wb.save(r"D:\workplace\batchGetData\test1.xlsx")

#方法二：
# param = json.dumps(list, encoding="UTF-8", ensure_ascii=False)
# param = eval(param)
# f = open(r"D:\workplace\batchGetData\response1.json", "w+")
# json.dump(param, f, encoding="utf-8", ensure_ascii=False, indent=3)
# f.close()
# with open(r"D:\workplace\batchGetData\response1.json", "r") as fp:
#     values = json.load(fp, encoding="utf-8")
# workbook = xlrd.open_workbook(r"D:\workplace\batchGetData\book1.xlsx")
# newWorkbook = copy(workbook)
# sheet_write = newWorkbook.get_sheet(0)
# sheet_write.write(0, 0, "name")
# sheet_write.write(0, 1, "org_id")
# sheet_write.write(0, 2, "opened_num")
# for i, j in enumerate(values):
#     sheet_write.write(i + 1, 0, j.get("name"))
#     sheet_write.write(i + 1, 1, j.get("org_id"))
#     sheet_write.write(i + 1, 2, j.get("opened_num"))
#
# newWorkbook.save(r"D:\workplace\batchGetData\book2.xlsx")
print("文件创建成功")

方法一的代码比较简洁，用的是python的工具类，但是字段以字段形式保存，顺序无法自定义，所以展示的顺序是随机的。
方法二是先到处json格式的文件，再读入文件进行操作，代码相对多一些，次方法可以根据key获取value，按照定义的顺序输出字段。

最后编辑于：2020.09.18 09:13:40