脚本说明：
这是一个调用火山引擎成本账单按天汇总open api接口（接口地址：https://www.volcengine.com/docs/6269/1215981）， 并将查询的成本账单数据汇总生成一个excel的脚本。需求方可按需调用该脚本，将excel投递到tos或直接将数据通过dataleap/dataworks定期导入到数据仓库，进行财务账单分析。该脚本用golang及python实现，可按需取用。
- golang实现
/**
* @author Markma
* @Wechat mark_xddll
* @date 6/17/24 11:54
* @version 1.0
*/
package service
import (
"fmt"
"github.com/tealeg/xlsx"
"github.com/volcengine/volcengine-go-sdk/service/billing"
"github.com/volcengine/volcengine-go-sdk/volcengine"
"github.com/volcengine/volcengine-go-sdk/volcengine/credentials"
"github.com/volcengine/volcengine-go-sdk/volcengine/session"
)
var CostBillDailyService costBillDailyService
type costBillDailyService struct{}
func (costBillDailySVC *costBillDailyService) ListAmortizedCostBillDaily() {
ak, sk, region := "",
"",
"cn-beijing"
config := volcengine.NewConfig().WithRegion(region).
WithCredentials(credentials.NewStaticCredentials(ak, sk, ""))
sess, err := session.NewSession(config)
if err != nil {
panic(err)
}
svc := billing.New(sess) //initial billing sdk client
query_month := "2024-05"
//calling open api once, order to get total records
amortizedCostBillDailyInput := &billing.ListAmortizedCostBillDailyInput{
AmortizedMonth: volcengine.String(query_month),
Offset: volcengine.Int32(0),
Limit: volcengine.Int32(1),
NeedRecordNum: volcengine.Int32(1),
}
resp, err := svc.ListAmortizedCostBillDaily(amortizedCostBillDailyInput)
if err != nil {
panic(err)
}
//create an excel file to write datas
file := xlsx.NewFile()
sheet, err := file.AddSheet("detail")
if err != nil {
fmt.Println("can not creat sheet:", err)
return
}
header := sheet.AddRow()
header.AddCell().Value = "产品中文名称"
header.AddCell().Value = "分摊日"
header.AddCell().Value = "本日已分摊-抹零金额"
header.AddCell().Value = "每日分摊-抹零金额"
header.AddCell().Value = "每日分摊-折后价"
header.AddCell().Value = "每日分摊-现金支付"
header.AddCell().Value = "本日已分摊-原价"
header.AddCell().Value = "计费方式"
header.AddCell().Value = "本日已分摊-现金支付"
header.AddCell().Value = "现金支付"
header.AddCell().Value = "本日已分摊-优惠金额"
header.AddCell().Value = "消费时间"
header.AddCell().Value = "每日分摊-优惠金额"
header.AddCell().Value = "本日已分摊-折后价"
header.AddCell().Value = "账务账期"
header.AddCell().Value = "业务账期"
header.AddCell().Value = "支付账号 ID"
header.AddCell().Value = "支付账号名"
header.AddCell().Value = "实例 ID"
header.AddCell().Value = "实例名称"
header.AddCell().Value = "计费单元"
header.AddCell().Value = "地域"
header.AddCell().Value = "可用区"
header.AddCell().Value = "单价"
header.AddCell().Value = "单价单位"
header.AddCell().Value = "使用时长"
header.AddCell().Value = "使用时长单位"
header.AddCell().Value = "计费模式"
header.AddCell().Value = "账单类型"
header.AddCell().Value = "分摊类型"
header.AddCell().Value = "分摊开始时间"
header.AddCell().Value = "分摊结束时间"
header.AddCell().Value = "原价"
header.AddCell().Value = "优惠金额"
header.AddCell().Value = "抹零金额"
header.AddCell().Value = "折后价"
header.AddCell().Value = "应付金额"
header.AddCell().Value = "代金券抵扣"
header.AddCell().Value = "项目"
pageSize := int32(100)
offSet := int32(0)
total := *resp.Total
pages := (total + pageSize - 1) / pageSize //compute total pages by total count
for i := int32(0); i < pages; i++ {
amortizedCostBillDailyInput := &billing.ListAmortizedCostBillDailyInput{
AmortizedMonth: volcengine.String(query_month),
Offset: volcengine.Int32(offSet),
Limit: volcengine.Int32(pageSize),
NeedRecordNum: volcengine.Int32(1),
}
resp, err := svc.ListAmortizedCostBillDaily(amortizedCostBillDailyInput)
if err != nil {
panic(err)
}
//write datas to excel sheet
for _, item := range resp.List {
row := sheet.AddRow()
row.AddCell().Value = *item.ProductZh //产品中文名称
row.AddCell().Value = *item.AmortizedDay //分摊日
row.AddCell().Value = *item.NowAmortizedRoundAmount //本日已分摊-抹零金额
row.AddCell().Value = *item.DailyAmortizedRoundAmount //每日分摊-抹零金额
row.AddCell().Value = *item.DailyAmortizedDiscountBillAmount //每日分摊-折后价
row.AddCell().Value = *item.DailyAmortizedPaidAmount //每日分摊-现金支付
row.AddCell().Value = *item.NowAmortizedOriginalBillAmount //本日已分摊-原价
row.AddCell().Value = *item.BillingMethodCode //计费方式
row.AddCell().Value = *item.NowAmortizedPaidAmount //本日已分摊-现金支付
row.AddCell().Value = *item.PaidAmount //现金支付
row.AddCell().Value = *item.NowAmortizedPreferentialBillAmount //本日已分摊-优惠金额
row.AddCell().Value = *item.ExpenseTime //消费时间
row.AddCell().Value = *item.DailyAmortizedPreferentialBillAmount //每日分摊-优惠金额
row.AddCell().Value = *item.NowAmortizedDiscountBillAmount //本日已分摊-折后价
row.AddCell().Value = *item.BillPeriod //账务账期
row.AddCell().Value = *item.BusiPeriod //业务账期
row.AddCell().Value = *item.PayerID //支付账号ID
row.AddCell().Value = *item.PayerUserName //支付账号名
row.AddCell().Value = *item.InstanceNo //实例ID
row.AddCell().Value = *item.InstanceName //实例名称
row.AddCell().Value = *item.Element //计费单元
row.AddCell().Value = *item.Region //地域
row.AddCell().Value = *item.Zone //可用区
row.AddCell().Value = *item.Price //单价
row.AddCell().Value = *item.PriceUnit //单价单位
row.AddCell().Value = *item.UseDuration //使用时长
row.AddCell().Value = *item.UseDurationUnit //使用时长
row.AddCell().Value = *item.BillingMode //计费模式
row.AddCell().Value = *item.BillCategory //账单类型
row.AddCell().Value = *item.AmortizedType //分摊类型
row.AddCell().Value = *item.AmortizedBeginTime //分摊开始时间
row.AddCell().Value = *item.AmortizedEndTime //分摊结束时间
row.AddCell().Value = *item.OriginalBillAmount //原价
row.AddCell().Value = *item.PreferentialBillAmount //优惠金额
row.AddCell().Value = *item.RoundAmount //抹零金额
row.AddCell().Value = *item.DiscountBillAmount //折后价
row.AddCell().Value = *item.PayableAmount //应付金额
row.AddCell().Value = *item.CouponAmount //代金券抵扣
row.AddCell().Value = *item.Project //项目
}
offSet = offSet + pageSize
}
//save file to tos
err = file.Save(query_month + ".xlsx")
if err != nil {
fmt.Println("save excel file exception:", err)
return
}
fmt.Println("-----------save file success-------------")
}
- python实现
# coding: utf-8
"""
Author: markma
Date: 2024-06-18
wechat: mark_xddll
"""
from __future__ import print_function
import time
import volcenginesdkbilling
import volcenginesdkcore
from volcenginesdkcore.rest import ApiException
import openpyxl
if __name__ == '__main__':
# initial sdk client, AK、SK & Region must be required
configuration = volcenginesdkcore.Configuration()
configuration.ak = ""
configuration.sk = ""
configuration.region = "cn-beijing"
configuration.client_side_validation = True
# set default configuration
volcenginesdkcore.Configuration.set_default(configuration)
# use global default configuration
api_instance = volcenginesdkbilling.BILLINGApi(volcenginesdkcore.ApiClient(configuration))
query_date = "2024-05" # the month whick you want to query
try:
# calling api by param limit = 1, order to get total count
onePage = api_instance.list_amortized_cost_bill_daily(volcenginesdkbilling.ListAmortizedCostBillDailyRequest(
amortized_month=query_date,
offset=0,
limit=1,
need_record_num=1
))
# creat a excel workbook and use the first sheet
wb = openpyxl.Workbook()
sheet = wb.active
# add row header to the sheet
header = sheet.append(["产品中文名称",
"分摊日",
"本日已分摊-抹零金额",
"每日分摊-抹零金额",
"每日分摊-折后价",
"每日分摊-现金支付",
"本日已分摊-原价",
"计费方式",
"本日已分摊-现金支付",
"现金支付",
"本日已分摊-优惠金额",
"消费时间",
"每日分摊-优惠金额",
"本日已分摊-折后价",
"账务账期",
"业务账期",
"支付账号 ID",
"支付账号名",
"实例 ID",
"实例名称",
"计费单元",
"地域",
"可用区",
"单价",
"单价单位",
"使用时长",
"使用时长单位",
"计费模式",
"账单类型",
"分摊类型",
"分摊开始时间",
"分摊结束时间",
"原价",
"优惠金额",
"抹零金额",
"折后价",
"应付金额",
"代金券抵扣",
"项目"])
# Calculate how many pages there are in total through the total number of pages.
pageSize = 100
offSet = 0
total = onePage.total
pages = (total + pageSize - 1) // pageSize
# write row datas
for i in range(pages):
resp = api_instance.list_amortized_cost_bill_daily(
volcenginesdkbilling.ListAmortizedCostBillDailyRequest(
amortized_month=query_date,
offset=offSet,
limit=pageSize,
need_record_num=1
))
# time.sleep() is very necessary, It can prevent the API call count from exceeding the limit.
time.sleep(0.5)
offSet = offSet + pageSize
for item in resp.list:
row = sheet.append([item.product_zh,
item.amortized_day,
item.now_amortized_round_amount,
item.daily_amortized_round_amount,
item.daily_amortized_discount_bill_amount,
item.daily_amortized_paid_amount,
item.now_amortized_original_bill_amount,
item.billing_method_code,
item.now_amortized_paid_amount,
item.paid_amount,
item.now_amortized_preferential_bill_amount,
item.expense_time,
item.daily_amortized_preferential_bill_amount,
item.now_amortized_discount_bill_amount,
item.bill_period,
item.busi_period,
item.payer_id,
item.payer_user_name,
item.instance_no,
item.instance_name,
item.element,
item.region,
item.zone,
item.price,
item.price_unit,
item.use_duration,
item.use_duration_unit,
item.billing_mode,
item.bill_category,
item.amortized_type,
item.amortized_begin_time,
item.amortized_end_time,
item.original_bill_amount,
item.preferential_bill_amount,
item.round_amount,
item.discount_bill_amount,
item.payable_amount,
item.coupon_amount,
item.project])
# save file
wb.save(query_date + ".xlsx")
print("-------------------------------write excel data success!--------------------------------------------")
except ApiException as e:
print("calling api raise Exception: %s\n" % e)