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