# -*- coding: utf-8 -*- import collections import datetime import calendar import os from collections import defaultdict import numpy as np import pandas as pd from calendar import monthrange import time from docxtpl import DocxTemplate from openpyxl import Workbook from openpyxl.styles import NamedStyle, PatternFill, Font, Alignment, Border, Side, colors word_dir = '.\\word\\' info_dir = '.\\info\\' 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 def from_filename_get_datetime(filename: str): dt = filename[-7:] date = datetime.datetime.strptime(dt, "%Y-%m") days = calendar.monthrange(date.year, date.month) later1dt = date + datetime.timedelta(days[1] + 1) later1 = later1dt.strftime("%Y-%m-%d") n_days = calendar.monthrange(later1dt.year, later1dt.month) later2dt = later1dt + datetime.timedelta(n_days[1]) later2 = later2dt.strftime("%Y-%m-%d") return dt, later1, later2 class GenerateWord(object): def __init__(self, *args, **kwargs): self.args = args self.kwargs = kwargs def my_sum(self, data_dict, loc, length=2): # 模板内使用 count = 0.0 for item in data_dict.values(): x = self.reverse_fill(item[loc]) count += x a = new_round(count, length) return a @staticmethod def reverse_fill(value_str: float, length=6): return new_round(value_str, length) @staticmethod def get_bank(**kwargs): info_map = { 'ch bj': os.path.join(info_dir, 'cn_bank_bj.txt'), 'ch hk': os.path.join(info_dir, 'cn_bank_hk.txt'), 'en bj': os.path.join(info_dir, 'en_bank_bj.txt'), 'en hk': os.path.join(info_dir, 'en_bank_hk.txt') } return info_map.get(kwargs.get('bank')) or info_map['ch bj'] @staticmethod def get_region_display(data: dict): for i in data.keys(): if i == 'cn-north-1': data['北京区资源费'] = data.pop(i) elif i == 'cn-northwest-1': data['宁夏区资源费'] = data.pop(i) return data @staticmethod def get_date_array(_date: str): _array = monthrange(int(_date[:4]), int(_date[5:7])) return [ "%s-%s-01" % (_date[:4], _date[5:7]), "%s-%s-%2d" % (_date[:4], _date[5:7], _array[1]), ] @staticmethod def fill(value, length=2): temp = "{:,.%sf}" % length if isinstance(value, str): value = value.replace(',', '') return temp.format(float(value)) elif isinstance(value, int) or isinstance(value, float): return temp.format(value) else: return temp.format(0) @staticmethod def display_time(day_str, str_fmt="%Y-%m-%d %H:%M:%S"): # 设置中文编码格式 # import locale # locale.setlocale(locale.LC_CTYPE, 'chinese') zh_fmt = '%Y年%m月%d日' # en_fmt = '%B %d, %Y' return time.strftime(zh_fmt, time.strptime(str(day_str), str_fmt)) def get_currency(self): if self.kwargs.get("currency") == 'USD': return "$" return "￥" def get_temple_number(self, number): data = [ self.fill(number * 1.06), self.fill(number), self.fill(0), self.fill(number * 0.06), ] return data @staticmethod def check_path_creat(path): if not os.path.exists(path): os.mkdir(path) 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.tag = kwargs.get('tag', '') self.tagname = kwargs.get('tagname', '') 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, **kwargs): self.border = None self.raw_wb = _wb self.wb = _wb.workbook(tag.replace(':', "_"), index=index) self.highlight = _wb.format_font self.tag = tag self.tagname = kwargs.get('tagname', None) 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 = 5 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 def set_solid_border(self, area): s_column = area[0] s_index = area[1] e_column = area[2] e_index = area[3] # 设置左框线 for cell in self.wb[s_column][s_index - 1:e_index]: cell.border = Border(left=Side(border_style='thin', color=colors.BLACK)) # 设置右框线 for cell in self.wb[e_column][s_index - 1:e_index]: cell.border = Border(right=Side(border_style='thin', color=colors.BLACK)) # # 设置上框线 # for row in tuple(self.wb[s_column + str(s_index):e_column + str(s_index)]): # for cell in row: # cell.border = Border(top=Side(border_style='thin', color=colors.BLACK)) # # 设置下框线 # for row in tuple(self.wb[s_column + str(e_index):e_column + str(e_index)]): # for cell in row: # cell.border = Border(bottom=Side(border_style='thin', color=colors.BLACK)) class IndexSheet(Sheet): def __init__(self, *args, **kwargs): kwargs['index'] = True super().__init__(*args, **kwargs) self.title_index = [ {'title': 'NO.', 'background_color': "808080", "font_color": "FFFFFF", 'sub_horizontal': 'center', 'width': 26}, {'title': self.raw_wb.tag, 'background_color': "808080", "font_color": "FFFFFF", 'width': 35}, {'title': 'Total Cost', 'background_color': "808080", "font_color": "FFFFFF", 'sub_horizontal': 'center', 'format': f'{self.raw_wb.currency}#,##0;{self.raw_wb.currency}-#,##0', 'width': 35}, {'title': 'Percentage', 'background_color': "808080", "font_color": "FFFFFF", 'sub_horizontal': 'center', 'width': 30}, ] self.margin_top = 7 self.wb.sheet_view.showGridLines = False # 隐藏网络线 self.wb.merge_cells('B8:E8') self.row_number = self.margin_top + 1 self.wb.merge_cells('A1:G1') self.wb.merge_cells('B2:E4') b2 = self.wb.cell(row=2, column=2) b2.value = 'The Monthly Bill for AWS {}'.format('世纪富轩') fontObj2 = Font(bold=False, italic=False, size=20) b2.font = fontObj2 b2.alignment = Alignment(horizontal='center', vertical='center') b5 = self.wb.cell(row=5, column=2) b5.value = 'Billing duration：' b5.style = self.highlight(background_color='FFFF00', bold=False) c5 = self.wb.cell(row=5, column=3) c5.style = self.highlight(bold=False) b6 = self.wb.cell(row=6, column=2) b6.value = 'Tag＝%s' % self.tag d5 = self.wb.cell(row=5, column=4) d5.value = self.raw_wb.time self.wb.sheet_properties.tabColor = "FF0000" def run(self): # 第二行 self.fill_index(location="B8", title_index=self.title_index) # title行 self.format_title(self.title_index) total_cost = sum([v for _, v in self.table_data.items()]) self.fill_data = [] num = 1 for k, v in self.table_data.items(): self.fill_data.append([num, k, v, "%.2f%%" % (v / total_cost * 100)]) num += 1 # 填充数据行 self.fill_table(data=self.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 AppSheet(Sheet): def __init__(self, *args, **kwargs): super().__init__(*args, **kwargs) self.title_second = [ {"title": 'NO.', 'background_color': "808080", "font_color": "FFFFFF", 'width': 5}, {"title": 'Name', 'background_color': "808080", "font_color": "FFFFFF", 'width': 30}, {"title": 'Product', 'background_color': "1F4E78", "font_color": "FFFFFF", 'width': 30}, # {"title": 'Resource Cost', 'background_color': "D9D9D9", 'width': 20, # 'format': f'{self.raw_wb.currency}#,##0.00;{self.raw_wb.currency}-#,##0.00', }, {"title": 'Total Cost', 'background_color': "D9D9D9", 'width': 30, 'format': f'{self.raw_wb.currency}#,##0.00;{self.raw_wb.currency}-#,##0.00', }, {"title": 'Tag', 'background_color': "808080", "font_color": "FFFFFF", 'width': 20}, ] self.wb.sheet_view.showGridLines = False # 隐藏网络线 # self.wb.freeze_panes = 'C4' # 冻结窗口 self.wb.merge_cells('B3:F5') b3 = self.wb.cell(row=3, column=2) b3.value = '{} program fee details'.format(self.tag) fontObj2 = Font(bold=False, italic=False, size=20) b3.font = fontObj2 b3.alignment = Alignment(horizontal='center', vertical='center') self.wb.merge_cells('B6:F6') self.row_number = self.margin_top + 1 def run(self): # 第二行 self.fill_index(location='B6', title_index=self.title_first) # title行 # self.format_title(self.title_first) # # 填充数据行 # total_1 = new_round(sum([item[-4] for item in self.table_first_data]), 6) # # 格式化 符号 # for i in range(len(self.table_first_data)): # self.table_first_data[i][3] = self.table_first_data[i][3] # self.table_first_data[i][5] = self.table_first_data[i][5] # self.table_first_data[i][6] = self.table_first_data[i][6] # self.table_first_data[i][7] = self.table_first_data[i][7] # self.fill_table(data = self.table_first_data, index_title = self.title_first) # self.set_border(2, len(self.title_first) + self.margin_left, self.row_number, self.row_number) # # 小计行 # self.fill_footer([(len(self.title_first) - 4, self.raw_wb.subtotal_str), (4, total_1)]) # 第二个表格 self.format_title(self.title_second) start = self.row_number total_2 = new_round(sum([item[3] for item in self.table_second_data]), 6) # 格式化 符号 for i in range(len(self.table_second_data)): self.table_second_data[i][3] = self.table_second_data[i][3] self.table_second_data[i][4] = self.table_second_data[i][4] self.fill_table(self.table_second_data, index_title = self.title_second) end = self.row_number - 1 # if end > start: # self.wb.merge_cells( # '%s%d:%s%d' % (chr(64 + self.margin_left + 2), start, chr(64 + self.margin_left + 2), end)) # 小计行 self.fill_footer([(3, self.raw_wb.subtotal_str), (1, total_2)]) thin_border = Border(left=Side(style='none'), right=Side(style='none'), top=Side(style='none'), bottom=Side(style='none')) self.wb.cell(row=6, column=2).border = thin_border # 总计行 # self.fill_footer([ # (len(self.title_first) - 2, self.raw_wb.total_str), # (2, new_round(float(total_1) + float(total_3), 2)) # ], color = 'FFC000') class Custom_Excel(object): def __init__(self, *args, **kwargs): self.tag = kwargs.get('tag', None) self.csv_list = kwargs.get('csv_list', []) 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={ 'InvoiceID': np.object, 'PayerAccountId': np.object, 'LinkedAccountId': np.object }, low_memory=False ) df = pd.concat([df, csv_data], axis=0) df = df[(df['RecordType'] == 'LinkedLineItem') & (df['ProductName'].notna())] df = df[(df['LinkedAccountId'] == '941124188381')] df.to_csv('941124188381-aws-cost-allocation-ACTS-{}.csv'.format(self.data_month), mode='w', encoding='UTF-8', index=False) df[self.tag] = df[self.tag].fillna('Other') df[self.tag] = df[self.tag].apply(lambda x: str(x).strip()) result = df.groupby(by=[self.tag, 'ProductName']).agg({'TotalCost': 'sum'}) result_dict = result.to_dict(orient='dict') return result_dict 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 def create_excel(self): start_dt, end_dt = self.get_period() currency = '￥' sheet = Book( tag=self.tag, time='{}_{}'.format(start_dt, end_dt), currency=currency, project='世纪富轩' # language='zh' ) self.data = self.get_data()['TotalCost'] # index_dict = defaultdict(float) # name_dict = defaultdict(list) # for k, v in self.data.items(): # k_li = k[0].split('-') # if k_li[0] == 'prophet': # index_dict['prophet'] += float(v) # elif k_li[0] == 'myyshop': # index_dict['myyshop'] += float(v) # elif k_li[0] == 'suzano': # index_dict['suzano'] += float(v) # elif k_li[0] == 'emfund': # index_dict['emfund'] += float(v) # elif k_li[0] == 'mixcldpoc': # index_dict['mixcldpoc'] += float(v) # else: # index_dict[k[0]] += v # count = len(name_dict[k[0]]) # name_dict[k[0]].append([count+1, k[0], k[1], v, self.tag]) # result = collections.OrderedDict(sorted(index_dict.items(), key=lambda t: t[1], reverse=True)) # IndexSheet(sheet, self.tag, table_data=result).run() # for i in name_dict: # app = AppSheet(sheet, i, tagname=self.tag) # app.table_second_data = name_dict[i] # app.run() # sheet.save(os.path.join('./', '{}-{}账单.xlsx'.format('世纪富轩', self.data_month))) # print('生成完成') # 只出北京区 self.create_word() print('数据处理完成') def create_word(self): prophet_dict = defaultdict(float) pro_total = 0 other_dict = defaultdict(float) other_total = 0 for k, v in self.data.items(): k_li = k[0].split('-') if k_li[0] == 'prophet': pro_total += v prophet_dict[k[1]] += v else: other_total += v other_dict[k[1]] += v prophet_dict['InvoiceID'] = '' prophet_dict['TotalCost'] = pro_total other_dict['InvoiceID'] = '' other_dict['TotalCost'] = other_total filename, _type = os.path.splitext(self.csv_list[0]) SJFXGenerateWord('prophet', prophet_dict, filename).create_word() SJFXGenerateWord('other', other_dict, filename).create_word() class SJFXGenerateWord(GenerateWord): def __init__(self, linkedid, result, filename, *args, **kwargs): super().__init__(*args, **kwargs) self.linkedid = linkedid self.result = result self.filename = filename def create_word(self): filedate, noticedate, due_data = from_filename_get_datetime( self.filename) noticenumber = self.result.pop('InvoiceID') totalcost = self.result.pop('TotalCost') product_code_dict = defaultdict() # order_dict = dict(sorted(self.result.items(), key=lambda t: t[1], reverse=True)) # for k, v in order_dict.items(): for k, v in self.result.items(): product_code_dict[k] = self.get_temple_number(v) totalcost_li = self.get_temple_number(totalcost) date = self.get_date_array(filedate) for i in range(len(date)): date[i] = self.display_time(date[i], str_fmt="%Y-%m-%d") bank_info_list = [] with open(self.get_bank(), 'r', encoding='utf8') as f: for line in f.readlines(): if "{{company_email}}" in line: line = line.replace( '{{company_email}}', self.kwargs.get( 'company_email', '')) bank_info_list.append(line.strip('\n')) xx = self.get_template() template = DocxTemplate(xx) content = { 'storage': self.linkedid, 'linkedid': '941124188381', 'noticenumber': noticenumber, 'noticedate': noticedate, 'currency': self.get_currency(), 'region_display': '北京区资源费', 'totalcost': totalcost_li, 'product_code_dict': product_code_dict, 'date': date, 'workday': due_data, 'bank_info_list': bank_info_list, } file_name = '{}_AWS账单{}.docx'.format(self.linkedid, filedate) doc_file_path = os.path.join(word_dir, self.filename) self.check_path_creat(doc_file_path) doc_file_name = os.path.join(doc_file_path, file_name) template.render(context=content) template.save(doc_file_name) print('{}_AWS账单{}.docx 生成完成'.format(self.linkedid, filedate)) return [doc_file_name] @staticmethod def get_template(): return os.path.join(info_dir, 'china-template.docx') def get_temple_number(self, number): data = [ self.fill(number), self.fill(number / 1.06), self.fill(0), self.fill(number / 1.06 * 0.06), ] return data if __name__ == '__main__': csv_list = [ '785691964566-aws-cost-allocation-ACTS-2022-04.csv', ] tag = 'user:Name' Custom_Excel(csv_list=csv_list, tag=tag).create_excel()