小趙同學
0%

阿里云-查询代开发票信息，写入Excel中

发表于 更新于

https://next.api.aliyun.com/home

背景

阿里云费用中心，没有一键导出代开发票的功能，所以根据阿里SDK查询后写入excel中，交付给领导。

ali_sdk_demo01

确定是否提供相关SDK

https://next.api.aliyun.com/home

https://next.api.aliyun.com/api/BssOpenApi/2017-12-14/QueryEvaluateList

准备环境

1
2
3
python3 -m venv .venv

source .venv/bin/activate

安装依赖

1
2
3
4
pip install --upgrade pip
pip install xlwt
pip install aliyun-python-sdk-bssopenapi
pip install aliyun-python-sdk-core

代码

将以下代码存入code.py文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
#!/usr/bin/env python
#coding=utf-8

#pip install xlwt
#pip install aliyun-python-sdk-bssopenapi
#pip install aliyun-python-sdk-core

from aliyunsdkcore.client import AcsClient
from aliyunsdkcore.acs_exception.exceptions import ClientException
from aliyunsdkcore.acs_exception.exceptions import ServerException
from aliyunsdkcore.auth.credentials import AccessKeyCredential
from aliyunsdkcore.auth.credentials import StsTokenCredential
from aliyunsdkbssopenapi.request.v20171214.QueryEvaluateListRequest import QueryEvaluateListRequest
import json
import xlwt


ak_id = '' # 需填入ak信息
ak_secret = ''

credentials = AccessKeyCredential(ak_id, ak_secret)
client = AcsClient(region_id='cn-hangzhou', credential=credentials)

request = QueryEvaluateListRequest()
request.set_accept_format('json')
request.set_BizTypeLists(["ALIYUN"])
response = client.do_action_with_exception(request)

response = str(response, encoding='utf-8')
data = json.loads(response)

# 创建excel工作表
workbook = xlwt.Workbook(encoding='utf-8')
worksheet = workbook.add_sheet('sheet1')

title_list = [i for i in data['Data']['EvaluateList']['Evaluate'][0].keys()]
count = 0
for title in title_list:
	worksheet.write(0, count, label=title)
	count += 1
row = 1

PageCount = int(data['Data']['TotalCount'] // 300) + 1

print("一共 %s 条数据，正在写入到表格中" % data['Data']['TotalCount'])

for PageNumber in range(1, PageCount + 1):
	request.set_PageNum(1)
	request.set_PageSize(300)
	response = client.do_action_with_exception(request)
	response = str(response, encoding='utf-8')
	data = json.loads(response)

	for list_item in data['Data']['EvaluateList']['Evaluate']:
		for title in title_list:
			column = title_list.index(title)
			value = list_item.get(title)
			worksheet.write(row, column, value)
		row += 1

workbook.save('result.xls')
print('完成')

执行

1
python code.py

效果