# -*- 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()