|
|
# -*- coding: utf-8 -*-
|
|
|
import calendar
|
|
|
import datetime
|
|
|
import os
|
|
|
import numpy as np
|
|
|
import pandas as pd
|
|
|
from openpyxl import Workbook
|
|
|
from openpyxl.styles import NamedStyle, PatternFill, Font, Alignment, Border, Side, colors
|
|
|
|
|
|
|
|
|
def new_round(_float, _len=2):
|
|
|
"""
|
|
|
Parameters
|
|
|
----------
|
|
|
_float:
|
|
|
_len: int, 指定四舍五入需要保留的小数点后几位数为_len
|
|
|
|
|
|
Returns
|
|
|
-------
|
|
|
type ==> float, 返回四舍五入后的值
|
|
|
"""
|
|
|
try:
|
|
|
if isinstance(_float, np.float64):
|
|
|
return new_round(float(_float), _len)
|
|
|
elif isinstance(_float, float):
|
|
|
if 'e' in str(_float).lower():
|
|
|
return round(_float, _len)
|
|
|
elif str(_float)[::-1].find('.') <= _len:
|
|
|
return _float
|
|
|
elif str(_float)[-1] == '5':
|
|
|
return round(float(str(_float)[:-1] + '6'), _len)
|
|
|
else:
|
|
|
return round(_float, _len)
|
|
|
elif isinstance(_float, str):
|
|
|
_float = _float.replace(',', '')
|
|
|
return new_round(float(_float), _len)
|
|
|
|
|
|
else:
|
|
|
return round(_float, _len)
|
|
|
except Exception as e:
|
|
|
pass
|
|
|
|
|
|
|
|
|
class Book:
|
|
|
def __init__(self, *args, **kwargs):
|
|
|
self.wb = Workbook()
|
|
|
self.wb.remove(self.wb.active)
|
|
|
self.highlight = None
|
|
|
self.project = kwargs.get('project', '')
|
|
|
self.currency = kwargs.get('currency', '￥')
|
|
|
self.time = kwargs.get('time', '')
|
|
|
self.subtotal_str = '小计:' if kwargs.get('language', None) else 'subtotal:'
|
|
|
self.total_str = '总计:' if kwargs.get('language', None) else 'total:'
|
|
|
|
|
|
def save(self, file_name='freeze.xlsx'):
|
|
|
self.wb.save(file_name)
|
|
|
|
|
|
def workbook(self, name=None, index=False):
|
|
|
name = name.replace(' ', '_')
|
|
|
if name is None or index is True:
|
|
|
wb = self.wb.create_sheet('ResourcesUsage', )
|
|
|
wb.title = 'ResourcesUsage'
|
|
|
else:
|
|
|
wb = self.wb.create_sheet(name, ) # 在最后添加
|
|
|
wb.title = name
|
|
|
return wb
|
|
|
|
|
|
def format_font(self, background_color="FFFFFF", font_color='2B2B2B', bold=True):
|
|
|
if not self.highlight:
|
|
|
self.highlight = NamedStyle(name = "highlight")
|
|
|
self.highlight.fill = PatternFill("solid", fgColor = background_color) # 背景填充
|
|
|
self.highlight.font = Font(name = '微软雅黑', size = 11, bold = bold, italic = False, strike = False,
|
|
|
color = font_color)
|
|
|
return self.highlight
|
|
|
|
|
|
class Sheet:
|
|
|
|
|
|
def __init__(self, _wb: Book, tag: str, table_first_data=None, table_second_data=None, table_data=None,
|
|
|
index=False):
|
|
|
self.border = None
|
|
|
self.raw_wb = _wb
|
|
|
self.wb = _wb.workbook(tag.replace(':', "_"), index=index)
|
|
|
self.highlight = _wb.format_font
|
|
|
self.tag = tag
|
|
|
self.title_first = []
|
|
|
self.table_first_data = table_first_data
|
|
|
self.table_data = table_data
|
|
|
self.title_second = []
|
|
|
self.table_second_data = table_second_data
|
|
|
self.margin_left = 1
|
|
|
self.margin_top = 1
|
|
|
self.row_number = None
|
|
|
|
|
|
def merge(self, left, right, top, down, color=None, value=None):
|
|
|
"""
|
|
|
:param left:
|
|
|
:param right:
|
|
|
:param top:
|
|
|
:param down:
|
|
|
:param color:
|
|
|
:param value:
|
|
|
:return:
|
|
|
"""
|
|
|
self.set_border(left, right, top, down)
|
|
|
self.wb.merge_cells('%s%d:%s%d' % (chr(64 + left), top, chr(64 + right), down))
|
|
|
subtotal = self.wb.cell(row=self.row_number, column=left)
|
|
|
subtotal.style = self.highlight(color)
|
|
|
if value:
|
|
|
subtotal.value = value
|
|
|
if not isinstance(value, str):
|
|
|
subtotal.number_format = f'{self.raw_wb.currency}#,##0.00;{self.raw_wb.currency}-#,##0.00'
|
|
|
subtotal.alignment = Alignment(horizontal='center', vertical='center')
|
|
|
self.wb.row_dimensions[self.row_number].height = 20
|
|
|
|
|
|
def fill_table(self, data: list, index_title: list, link=False, index=False):
|
|
|
"""
|
|
|
填充数据
|
|
|
:param data:
|
|
|
:param index_title:
|
|
|
:return:
|
|
|
"""
|
|
|
for row in data:
|
|
|
for k, value in enumerate(row):
|
|
|
cell = self.wb.cell(row=self.row_number, column= k + self.margin_left + 1)
|
|
|
|
|
|
cell.style = self.highlight(bold=False)
|
|
|
cell.alignment = Alignment(
|
|
|
horizontal = index_title[k].get('sub_horizontal', 'center'),
|
|
|
vertical = 'center'
|
|
|
)
|
|
|
cell.border = self.my_border('thin', 'thin', 'thin', 'thin')
|
|
|
self.wb.row_dimensions[self.row_number].height = 20
|
|
|
if index_title[k].get('format'):
|
|
|
cell.number_format = index_title[k].get('format')
|
|
|
if k == 0 and link is True:
|
|
|
cell.value = '=HYPERLINK("%s", "%s")' % (f'#{value.replace(" ", "_")}!B2', value)
|
|
|
cell.font = Font(underline = 'single', color = '0563C1')
|
|
|
else:
|
|
|
cell.value = value
|
|
|
if index and k == 1:
|
|
|
cell.hyperlink = f'#{value.replace(" ", "_")}!B2'
|
|
|
cell.font = Font(underline = 'single', color = '0563C1')
|
|
|
|
|
|
self.row_number += 1
|
|
|
|
|
|
def fill_footer(self, footer_data: list, color='A9D08E'):
|
|
|
"""
|
|
|
填充角标
|
|
|
:return:
|
|
|
"""
|
|
|
total = self.margin_left + 1
|
|
|
for k, v in footer_data:
|
|
|
# 合并每一个区域块
|
|
|
self.merge(total, total + k - 1, self.row_number, self.row_number, value = v, color = color)
|
|
|
# 标注网格线
|
|
|
self.set_border(total, total + k - 1, self.row_number, self.row_number)
|
|
|
total += k
|
|
|
# 完成下移
|
|
|
# 空行
|
|
|
self.row_number += 1
|
|
|
for i in range(2):
|
|
|
self.wb.append([])
|
|
|
self.row_number += 1
|
|
|
|
|
|
def format_title(self, index_data: list):
|
|
|
for i, title in enumerate(index_data):
|
|
|
self.wb.column_dimensions[chr(65 + i + 1)].width = title.get('width', 20)
|
|
|
cell = self.wb.cell(row = self.row_number, column = i + self.margin_left + 1)
|
|
|
title_info = index_data[i]
|
|
|
cell.value = title_info['title']
|
|
|
cell.style = self.highlight(
|
|
|
title_info['background_color'],
|
|
|
font_color = title_info.get('font_color', '2B2B2B')
|
|
|
)
|
|
|
cell.alignment = Alignment(horizontal = 'center', vertical = 'center')
|
|
|
self.set_border(self.margin_left + 1, len(index_data) + self.margin_left, self.row_number, self.row_number)
|
|
|
self.row_number += 1
|
|
|
|
|
|
def my_border(self, t_border, b_border, l_border, r_border):
|
|
|
if self.border:
|
|
|
return self.border
|
|
|
else:
|
|
|
self.border = Border(top=Side(border_style=t_border, color=colors.BLACK),
|
|
|
bottom=Side(border_style=b_border, color=colors.BLACK),
|
|
|
left=Side(border_style=l_border, color=colors.BLACK),
|
|
|
right=Side(border_style=r_border, color=colors.BLACK))
|
|
|
return self.border
|
|
|
|
|
|
def redirect_index(self):
|
|
|
cell = self.wb.cell(row = 1, column = 1)
|
|
|
cell.style = self.highlight(bold = False)
|
|
|
cell.border = self.my_border('thin', 'thin', 'thin', 'thin')
|
|
|
self.wb.row_dimensions[self.row_number].height = 20
|
|
|
cell.value = '=HYPERLINK("%s", "%s")' % (f'#ResourcesUsage!B2', "跳回首页")
|
|
|
cell.font = Font(underline = 'single', color = '0563C1')
|
|
|
|
|
|
def set_border(self, left, right, top, down):
|
|
|
for row in range(top, down + 1):
|
|
|
for line in range(left, right + 1):
|
|
|
cell = self.wb.cell(row = row, column = line)
|
|
|
cell.border = self.my_border('thin', 'thin', 'thin', 'thin')
|
|
|
cell.alignment = Alignment(horizontal = 'center', vertical = 'center')
|
|
|
|
|
|
def fill_index(self, location='B2', title_index: list = None, title=None, bkg=None):
|
|
|
if title:
|
|
|
self.wb[location] = title
|
|
|
else:
|
|
|
self.wb[location] = self.raw_wb.project + ':' + self.raw_wb.time
|
|
|
if bkg:
|
|
|
self.wb[location].style = self.highlight(background_color = bkg)
|
|
|
else:
|
|
|
self.wb[location].style = self.highlight(background_color = 'D9D9D9')
|
|
|
self.set_border(self.margin_left + 1, len(title_index) + self.margin_left, self.row_number,
|
|
|
self.row_number)
|
|
|
self.wb[location].border = self.my_border('thin', 'thin', 'thin', 'thin')
|
|
|
self.wb[location].alignment = Alignment(horizontal = 'center', vertical = 'center')
|
|
|
self.row_number += 1
|
|
|
|
|
|
class IndexSheet(Sheet):
|
|
|
|
|
|
def __init__(self, *args, **kwargs):
|
|
|
kwargs['index'] = True
|
|
|
super().__init__(*args, **kwargs)
|
|
|
self.title_index = [
|
|
|
{'title': 'Tag', 'background_color': "808080", "font_color": "FFFFFF", 'sub_horizontal': 'center',
|
|
|
'width': 20},
|
|
|
{'title': 'Product', 'background_color': "808080", "font_color": "FFFFFF", 'width': 30},
|
|
|
{'title': 'Total Cost', 'background_color': "808080", "font_color": "FFFFFF", 'sub_horizontal': 'center',
|
|
|
'format': f'{self.raw_wb.currency}#,##0.00;{self.raw_wb.currency}-#,##0.00', 'width': 20},
|
|
|
{'title': 'Percentage', 'background_color': "808080", "font_color": "FFFFFF", 'sub_horizontal': 'center',
|
|
|
'width': 20},
|
|
|
]
|
|
|
self.margin_top = 4
|
|
|
self.wb.sheet_view.showGridLines = False # 隐藏网络线
|
|
|
self.wb.merge_cells('B5:E5')
|
|
|
self.row_number = self.margin_top + 1
|
|
|
|
|
|
a2 = self.wb.cell(row = 2, column = 1)
|
|
|
a2.value = 'Billing duration：'
|
|
|
a2.style = self.highlight(background_color = 'FFFF00', bold = False)
|
|
|
b2 = self.wb.cell(row = 2, column = 2)
|
|
|
b2.style = self.highlight(background_color = 'FFFF00', bold = False)
|
|
|
a3 = self.wb.cell(row = 3, column = 1)
|
|
|
a3.value = 'tag＝%s' % self.tag
|
|
|
c2 = self.wb.cell(row = 2, column = 3)
|
|
|
c2.value = self.raw_wb.time
|
|
|
self.wb.sheet_properties.tabColor = "FF0000"
|
|
|
|
|
|
def run(self):
|
|
|
# 第二行
|
|
|
self.fill_index(location = "B5", title_index = self.title_index)
|
|
|
# title行
|
|
|
self.format_title(self.title_index)
|
|
|
total_cost = sum([v for _, v in self.table_data.items()])
|
|
|
fill_data = []
|
|
|
num = 1
|
|
|
for k, v in self.table_data.items():
|
|
|
fill_data.append([k[0], k[1], v, "%.2f%%" % (v / total_cost * 100)])
|
|
|
num += 1
|
|
|
# 填充数据行
|
|
|
self.fill_table(data=fill_data, index_title=self.title_index, index=True)
|
|
|
self.set_border(2, len(self.title_index) + self.margin_left, self.row_number, self.row_number)
|
|
|
# 总计行
|
|
|
self.fill_footer([(2, self.raw_wb.total_str), (1, total_cost)])
|
|
|
|
|
|
class Custom_Excel(object):
|
|
|
|
|
|
def __init__(self, *args, **kwargs):
|
|
|
self.project = kwargs.get('project', None)
|
|
|
self.tag = kwargs.get('tag', None)
|
|
|
self.csv_list = kwargs.get('csv_list', [])
|
|
|
self.linked_accounts = kwargs.get('linkid', [])
|
|
|
self.filter_line = [self.tag, 'ProductName']
|
|
|
|
|
|
def get_data(self):
|
|
|
df = pd.DataFrame()
|
|
|
for _csv in self.csv_list:
|
|
|
if _csv:
|
|
|
csv_data = pd.read_csv(
|
|
|
_csv,
|
|
|
sep=',',
|
|
|
encoding='utf-8',
|
|
|
skiprows=[0, ], # 默认要删除第一行
|
|
|
dtype={'LinkedAccountId': np.object},
|
|
|
)
|
|
|
df = pd.concat([df, csv_data], axis=0)
|
|
|
df.reset_index(drop=True)
|
|
|
return df
|
|
|
|
|
|
def filter_data(self):
|
|
|
data = self.get_data()
|
|
|
data = data[(data['RecordType'] == 'LinkedLineItem') & (data['ProductName'].notna())]
|
|
|
detail_data = data[data['LinkedAccountId'].isin(self.linked_accounts)]
|
|
|
self.detail_data = detail_data
|
|
|
#空值填充
|
|
|
detail_data = detail_data.reset_index(drop=True)
|
|
|
detail_data[self.tag] = detail_data[self.tag].fillna('other')
|
|
|
return detail_data
|
|
|
|
|
|
def data_handling(self):
|
|
|
df = self.filter_data()
|
|
|
result = df.groupby(by=self.filter_line).agg({'TotalCost': 'sum'})
|
|
|
result_dict = result.to_dict(orient='dict')
|
|
|
return result_dict
|
|
|
|
|
|
def create_excel(self):
|
|
|
start_dt, end_dt = self.get_period()
|
|
|
sheet = Book(
|
|
|
project=self.project,
|
|
|
time='{}_{}'.format(start_dt, end_dt),
|
|
|
language='zh'
|
|
|
)
|
|
|
data = self.data_handling()
|
|
|
table_data = data['TotalCost']
|
|
|
IndexSheet(sheet, self.tag, table_data=table_data).run()
|
|
|
sheet.save(os.path.join('./', '{}-{}账单.xlsx'.format(self.project, self.data_month)))
|
|
|
with pd.ExcelWriter('{}-{}账单.xlsx'.format(self.project, self.data_month), mode='a', engine="openpyxl") as writer:
|
|
|
self.detail_data.to_excel(writer, sheet_name='详情', index=False)
|
|
|
print('数据处理完成')
|
|
|
|
|
|
def get_period(self):
|
|
|
data = self.csv_list[0]
|
|
|
self.data_month = data[-11:-4]
|
|
|
date = datetime.datetime.strptime(self.data_month, "%Y-%m")
|
|
|
later = date.strftime("%Y-%m-%d")
|
|
|
days = calendar.monthrange(date.year, date.month)
|
|
|
later1dt = date + datetime.timedelta(days[1] - 1)
|
|
|
later1 = later1dt.strftime("%Y-%m-%d")
|
|
|
return later, later1
|
|
|
|
|
|
|
|
|
if __name__ == '__main__':
|
|
|
|
|
|
csv_list = [
|
|
|
'936739285320-aws-cost-allocation-ACTS-2022-03.csv',
|
|
|
'936739285320-aws-cost-allocation-ACTS-Ningxia-2022-03.csv'
|
|
|
]
|
|
|
linkid = ['675887039365']
|
|
|
|
|
|
Custom_Excel(project='九城', tag='user:resource', linkid=linkid, csv_list=csv_list).create_excel()
|
