def add_signature_image(sheet):
xfile = openpyxl.load_workbook(filepath)
sheet = xfile.get_sheet_by_name('Time Card')
img = Image('signature_scan.gif')
img.drawing.width = 100
img.drawing.height = 50
img.anchor(sheet.cell('E35'))
sheet.add_image(img)
def find_images(archive, path):
"""
Given the path to a drawing file extract anchors with images
"""
src = archive.read(path)
tree = fromstring(src)
drawing = SpreadsheetDrawing.from_tree(tree)
rels_path = get_rels_path(path)
deps = []
if rels_path in archive.namelist():
deps = get_dependents(archive, rels_path)
images = []
for rel in drawing._blip_rels:
dep = deps[rel.embed]
if dep.Type == IMAGE_NS:
image = Image(BytesIO(archive.read(dep.target)))
image.anchor = rel.anchor
images.append(image)
return images
def generate_excel(report, name, reporting_start_datetime_local,
reporting_end_datetime_local, period_type):
wb = Workbook()
ws = wb.active
# Row height
ws.row_dimensions[1].height = 102
for i in range(2, 2000 + 1):
ws.row_dimensions[i].height = 42
# Col width
ws.column_dimensions['A'].width = 1.5
ws.column_dimensions['B'].width = 25.0
for i in range(ord('C'), ord('L')):
ws.column_dimensions[chr(i)].width = 15.0
# Font
name_font = Font(name='Constantia', size=15, bold=True)
title_font = Font(name='宋体', size=15, bold=True)
data_font = Font(name='Franklin Gothic Book', size=11)
table_fill = PatternFill(fill_type='solid', fgColor='1F497D')
f_border = Border(left=Side(border_style='medium', color='00000000'),
right=Side(border_style='medium', color='00000000'),
bottom=Side(border_style='medium', color='00000000'),
top=Side(border_style='medium', color='00000000'))
b_border = Border(bottom=Side(border_style='medium', color='00000000'), )
b_c_alignment = Alignment(vertical='bottom',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
c_c_alignment = Alignment(vertical='center',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
b_r_alignment = Alignment(vertical='bottom',
horizontal='right',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
c_r_alignment = Alignment(vertical='bottom',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
# Img
img = Image("excelexporters/myems.png")
img.width = img.width * 0.85
img.height = img.height * 0.85
# img = Image("myems.png")
ws.add_image(img, 'B1')
# Title
ws.row_dimensions[3].height = 60
ws['B3'].font = name_font
ws['B3'].alignment = b_r_alignment
ws['B3'] = 'Name:'
ws['C3'].border = b_border
ws['C3'].alignment = b_c_alignment
ws['C3'].font = name_font
ws['C3'] = name
ws['D3'].font = name_font
ws['D3'].alignment = b_r_alignment
ws['D3'] = 'Period:'
ws['E3'].border = b_border
ws['E3'].alignment = b_c_alignment
ws['E3'].font = name_font
ws['E3'] = period_type
ws['F3'].font = name_font
ws['F3'].alignment = b_r_alignment
ws['F3'] = 'Date:'
ws['G3'].alignment = b_c_alignment
ws['G3'].font = name_font
ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
ws.merge_cells("G3:H3")
if "reporting_period" not in report.keys() or \
"difference_values" not in report['reporting_period'].keys() or \
len(report['reporting_period']['difference_values']) == 0:
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
#################################################
has_difference_values_data_flag = True
if 'difference_values' not in report['reporting_period'].keys() or len(
report['reporting_period']['difference_values']) == 0:
has_difference_values_data_flag = False
current_row_number = 6
if has_difference_values_data_flag:
reporting_period_data = report['reporting_period']
category = report['meter']['energy_category_name']
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 报告期'
current_row_number += 1
ws.row_dimensions[current_row_number].height = 60
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].border = f_border
if not isinstance(category, list):
ws['C' + str(current_row_number)].fill = table_fill
ws['C' + str(current_row_number)].font = name_font
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = report['meter'][
'energy_category_name'] + " (" + report['meter'][
'unit_of_measure'] + ")"
current_row_number += 1
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '总表消耗'
ws['C' + str(current_row_number)].font = name_font
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = round(
reporting_period_data['master_meter_consumption_in_category'],
2)
current_row_number += 1
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '分表消耗'
ws['C' + str(current_row_number)].font = name_font
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = round(
reporting_period_data['submeters_consumption_in_category'], 2)
current_row_number += 1
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '差值'
ws['C' + str(current_row_number)].font = name_font
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = round(
reporting_period_data['difference_in_category'], 2)
current_row_number += 1
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '差值百分比'
ws['C' + str(current_row_number)].font = name_font
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = str(
round(reporting_period_data['percentage_difference'] * 100,
2)) + '%'
current_row_number += 2
time = reporting_period_data['timestamps']
parameters_names_len = len(report['parameters']['names'])
parameters_data = report['parameters']
parameters_parameters_datas_len = 0
for i in range(0, parameters_names_len):
if len(parameters_data['timestamps'][i]) == 0:
continue
parameters_parameters_datas_len += 1
has_time_data_flag = False
if time is not None and len(time) > 0:
has_time_data_flag = True
if has_time_data_flag:
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 详细数据'
current_row_number += 1
chart_start_number = current_row_number
current_row_number = current_row_number + 1 + len(
category) * 6 + parameters_parameters_datas_len * 6
table_start_number = current_row_number
ws.row_dimensions[current_row_number].height = 60
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)] = '日期时间'
ws['C' + str(current_row_number)].fill = table_fill
ws['C' + str(current_row_number)].font = title_font
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)] = report['meter'][
'energy_category_name'] + " (" + report['meter'][
'unit_of_measure'] + ")"
current_row_number += 1
for i in range(0, len(time)):
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)] = time[i]
ws['C' + str(current_row_number)].font = title_font
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)] = round(
reporting_period_data['difference_values'][i], 2)
current_row_number += 1
table_end_number = current_row_number - 1
line = LineChart()
line.title = '报告期差值 - ' + report['meter'][
'energy_category_name'] + " (" + report['meter'][
'unit_of_measure'] + ")"
labels = Reference(ws,
min_col=2,
min_row=table_start_number + 1,
max_row=table_end_number)
line_data = Reference(ws,
min_col=3,
min_row=table_start_number,
max_row=table_end_number)
line.add_data(line_data, titles_from_data=True)
line.set_categories(labels)
line_data = line.series[0]
line_data.marker.symbol = "circle"
line_data.smooth = True
line.x_axis.crosses = 'min'
line.height = 8.25
line.width = 24
line.dLbls = DataLabelList()
line.dLbls.dLblPos = 't'
line.dLbls.showVal = True # 数量显示
ws.add_chart(line, "B" + str(chart_start_number))
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)] = '总计'
ws['C' + str(current_row_number)].font = title_font
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)] = round(
reporting_period_data[
'master_meter_consumption_in_category'], 2)
else:
pass
##########################################
has_parameters_names_and_timestamps_and_values_data = True
# 12 is the starting line number of the last line chart in the report period
category = report['meter']['energy_category_name']
ca_len = len(category)
time_len = len(report['reporting_period']['timestamps'])
current_sheet_parameters_row_number = 14 + ca_len * 6
if 'parameters' not in report.keys() or \
report['parameters'] is None or \
'names' not in report['parameters'].keys() or \
report['parameters']['names'] is None or \
len(report['parameters']['names']) == 0 or \
'timestamps' not in report['parameters'].keys() or \
report['parameters']['timestamps'] is None or \
len(report['parameters']['timestamps']) == 0 or \
'values' not in report['parameters'].keys() or \
report['parameters']['values'] is None or \
len(report['parameters']['values']) == 0 or \
timestamps_data_all_equal_0(report['parameters']['timestamps']):
has_parameters_names_and_timestamps_and_values_data = False
if has_parameters_names_and_timestamps_and_values_data:
###############################
# new worksheet
###############################
parameters_data = report['parameters']
parameters_names_len = len(parameters_data['names'])
parameters_ws = wb.create_sheet('相关参数')
parameters_timestamps_data_max_len = \
get_parameters_timestamps_lists_max_len(list(parameters_data['timestamps']))
# Row height
parameters_ws.row_dimensions[1].height = 102
for i in range(2, 7 + 1):
parameters_ws.row_dimensions[i].height = 42
for i in range(8, parameters_timestamps_data_max_len + 10):
parameters_ws.row_dimensions[i].height = 60
# Col width
parameters_ws.column_dimensions['A'].width = 1.5
parameters_ws.column_dimensions['B'].width = 25.0
for i in range(3, 12 + parameters_names_len * 3):
parameters_ws.column_dimensions[format_cell.get_column_letter(
i)].width = 15.0
# Img
img = Image("excelexporters/myems.png")
img.width = img.width * 0.85
img.height = img.height * 0.85
# img = Image("myems.png")
parameters_ws.add_image(img, 'B1')
# Title
parameters_ws.row_dimensions[3].height = 60
parameters_ws['B3'].font = name_font
parameters_ws['B3'].alignment = b_r_alignment
parameters_ws['B3'] = 'Name:'
parameters_ws['C3'].border = b_border
parameters_ws['C3'].alignment = b_c_alignment
parameters_ws['C3'].font = name_font
parameters_ws['C3'] = name
parameters_ws['D3'].font = name_font
parameters_ws['D3'].alignment = b_r_alignment
parameters_ws['D3'] = 'Period:'
parameters_ws['E3'].border = b_border
parameters_ws['E3'].alignment = b_c_alignment
parameters_ws['E3'].font = name_font
parameters_ws['E3'] = period_type
parameters_ws['F3'].font = name_font
parameters_ws['F3'].alignment = b_r_alignment
parameters_ws['F3'] = 'Date:'
parameters_ws['G3'].border = b_border
parameters_ws['G3'].alignment = b_c_alignment
parameters_ws['G3'].font = name_font
parameters_ws[
'G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
parameters_ws.merge_cells("G3:H3")
parameters_ws_current_row_number = 6
parameters_ws['B' +
str(parameters_ws_current_row_number)].font = title_font
parameters_ws['B' +
str(parameters_ws_current_row_number)] = name + ' 相关参数'
parameters_ws_current_row_number += 1
parameters_table_start_row_number = parameters_ws_current_row_number
parameters_ws.row_dimensions[
parameters_ws_current_row_number].height = 80
parameters_ws_current_row_number += 1
table_current_col_number = 2
for i in range(0, parameters_names_len):
if len(parameters_data['timestamps'][i]) == 0:
continue
col = format_cell.get_column_letter(table_current_col_number)
parameters_ws[col + str(parameters_ws_current_row_number -
1)].fill = table_fill
parameters_ws[col + str(parameters_ws_current_row_number -
1)].border = f_border
col = format_cell.get_column_letter(table_current_col_number + 1)
parameters_ws[col + str(parameters_ws_current_row_number -
1)].fill = table_fill
parameters_ws[col + str(parameters_ws_current_row_number -
1)].border = f_border
parameters_ws[col + str(parameters_ws_current_row_number -
1)].font = name_font
parameters_ws[col + str(parameters_ws_current_row_number -
1)].alignment = c_c_alignment
parameters_ws[col + str(parameters_ws_current_row_number -
1)] = parameters_data['names'][i]
table_current_row_number = parameters_ws_current_row_number
for j, value in enumerate(list(parameters_data['timestamps'][i])):
col = format_cell.get_column_letter(table_current_col_number)
parameters_ws[col +
str(table_current_row_number)].border = f_border
parameters_ws[col +
str(table_current_row_number)].font = title_font
parameters_ws[
col +
str(table_current_row_number)].alignment = c_c_alignment
parameters_ws[col + str(table_current_row_number)] = value
col = format_cell.get_column_letter(table_current_col_number +
1)
parameters_ws[col +
str(table_current_row_number)].border = f_border
parameters_ws[col +
str(table_current_row_number)].font = title_font
parameters_ws[
col +
str(table_current_row_number)].alignment = c_c_alignment
parameters_ws[col + str(table_current_row_number)] = round(
parameters_data['values'][i][j], 2)
table_current_row_number += 1
table_current_col_number = table_current_col_number + 3
########################################################
# parameters chart and parameters table
########################################################
ws['B' + str(current_sheet_parameters_row_number)].font = title_font
ws['B' + str(current_sheet_parameters_row_number)] = name + ' 相关参数'
current_sheet_parameters_row_number += 1
chart_start_row_number = current_sheet_parameters_row_number
col_index = 0
for i in range(0, parameters_names_len):
if len(parameters_data['timestamps'][i]) == 0:
continue
line = LineChart()
data_col = 3 + col_index * 3
labels_col = 2 + col_index * 3
col_index += 1
line.title = '相关参数 - ' + \
parameters_ws.cell(row=parameters_table_start_row_number, column=data_col).value
labels = Reference(parameters_ws,
min_col=labels_col,
min_row=parameters_table_start_row_number + 1,
max_row=(len(parameters_data['timestamps'][i]) +
parameters_table_start_row_number))
line_data = Reference(
parameters_ws,
min_col=data_col,
min_row=parameters_table_start_row_number,
max_row=(len(parameters_data['timestamps'][i]) +
parameters_table_start_row_number))
line.add_data(line_data, titles_from_data=True)
line.set_categories(labels)
line_data = line.series[0]
line_data.marker.symbol = "circle"
line_data.smooth = True
line.x_axis.crosses = 'min'
line.height = 8.25
line.width = 24
line.dLbls = DataLabelList()
line.dLbls.dLblPos = 't'
line.dLbls.showVal = False
line.dLbls.showPercent = False
chart_col = 'B'
chart_cell = chart_col + str(chart_start_row_number)
chart_start_row_number += 6
ws.add_chart(line, chart_cell)
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
def export_excel(_path,
_df=None,
_wb=None,
_sheet_name='sheet1',
_letter_fmt=None,
_append=False,
_frz='B2',
_auto_flt=True,
_auto_sz=False,
_header_height=None,
_col_width_=[20, 20],
_header_fmt=None,
_header_rot=0,
_zoom=100,
_heatmap=0,
_is_index=True,
_index_name='Index',
_header_txtcol='000000',
_header_fillcol='d9f2f8',
_img=None):
import openpyxl as px
from openpyxl.utils import get_column_letter
time_start = time.perf_counter()
if _path == None and _wb == None:
print('[Error] Both of _path and _bw are None.')
return
print('[Exporting Excel file ...] Sheet : "{0}"'.format(_sheet_name))
#-------------------------------------------
# 初期設定
#-------------------------------------------
# Workbook作成
if _wb == None:
if _append: # 既存ファイルにシート追加
try:
wb = px.load_workbook(_path)
except:
_append = False # ファイルが存在しないときは新規作成
if not _append: # 新規ファイル
wb = px.Workbook()
else:
wb = _wb
_append = True
# Worksheet作成
ws = wb.create_sheet(title=_sheet_name)
#-------------------------------------------
# DataFrameをWorksheetに書き込み
#-------------------------------------------
if _df is not None:
#----- 作業用にDataFrameをコピー -----
df = _df.copy()
# Timestampを文字列に変換（そのままだとエラーになるので）
list_timestamp_col = list()
# Timestampのセルが存在する列を探して文字列に変換する
for col_name, col in df.iteritems():
for item in col:
tp = type(item)
if tp is pd._libs.tslibs.timestamps.Timestamp:
list_timestamp_col.append(col_name)
break
for col in list_timestamp_col:
df[col] = df[col].astype(str)
df[col] = df[col].replace('NaT', '')
#----- Excelファイル用フォーマットの作成 -----
base_font = '游ゴシック'
from openpyxl.styles.fonts import Font
from openpyxl.styles import PatternFill
font_header_row = Font(name=base_font,
b=True,
sz=10,
color=_header_txtcol)
font_header_col = Font(name=base_font,
b=True,
sz=10,
color=_header_txtcol)
font_cell = Font(name=base_font, sz=10)
align_header_row = px.styles.Alignment(horizontal="center",
vertical="center",
wrapText=True,
textRotation=_header_rot)
align_header_col = px.styles.Alignment(horizontal="center",
vertical="center",
wrapText=True)
fill_header_row = PatternFill(patternType='solid',
fgColor=_header_fillcol)
fill_header_col = PatternFill(patternType='solid',
fgColor=_header_fillcol)
#----- データ出力 -----
# DataFrameをWorksheetにExport
l = df.columns.tolist()
if _is_index:
l.insert(0, _index_name) # 行のindexを先頭列に追加
ws.append(l)
count = 0
for i, row in df.iterrows(): # 一行ずつwsに追加していく
l = row.values.tolist()
if _is_index:
l.insert(0, row.name) # 行のindexを先頭列に追加
ws.append(l)
count += 1
print('\r - データコピー {0}/{1}'.format(count, len(df)), end="")
print('')
#----- Worksheetの書式設定 -----
# ヘッダー行(既定値)
for cell in list(ws.rows)[0]:
cell.font = font_header_row
cell.alignment = align_header_row
cell.fill = fill_header_row
# ヘッダー行(個別)
if _header_fmt != None:
list_cell = list(ws.rows)[0]
for head, fmt in _header_fmt.items():
try:
index = list(df.columns).index(head)
if _is_index:
index += 1
cell = list_cell[index]
except:
continue
# rotation
try:
rotation = fmt['rotation']
cell.alignment = px.styles.Alignment(horizontal="center",
vertical="center",
wrapText=True,
textRotation=rotation)
except:
pass
# 文字色
try:
text_color = fmt['txtcol']
cell.font = Font(name=base_font,
b=True,
sz=10,
color=text_color)
except:
pass
# 背景色
try:
fill_color = fmt['fillcol']
cell.fill = PatternFill(patternType='solid',
fgColor=fill_color)
except:
pass
# 列ごとの書式設定用のリスト作成
list_dtxt_pat = list()
list_dfill_pat = list()
if _header_fmt != None:
for head, fmt in _header_fmt.items():
try:
index = list(df.columns).index(head)
if _is_index:
index += 1
except:
continue
# 文字色
try:
text_color = fmt['dtxtcol']
list_dtxt_pat.append(
[index,
Font(name=base_font, sz=10, color=text_color)])
except:
pass
# 背景色
try:
dfill_color = fmt['dfillcol']
list_dfill_pat.append([
index,
PatternFill(patternType='solid', fgColor=dfill_color)
])
except:
pass
# データ行書式設定
count = 0
for row in ws.iter_rows(min_row=2):
# 書式設定
for cell in row:
cell.font = font_cell
# 列ごとの書式設定で上書き
for list_pat in list_dtxt_pat: # 個別設定がある列を順に処理する
idx = list_pat[0]
row[idx].font = list_pat[1]
for list_pat in list_dfill_pat: # 個別設定がある列を順に処理する
idx = list_pat[0]
row[idx].fill = list_pat[1]
# Index列がある場合はIndex用設定
if _is_index:
row[0].font = font_header_col # 先頭列のみ太字
row[0].alignment = align_header_col # 先頭列のみセンタリング
row[0].fill = fill_header_col # 先頭列の塗りつぶし
count += 1
print('\r - 書式設定 {0}/{1}'.format(count, len(df)), end="")
print('')
#----- セルの文字書式 -----
if type(_letter_fmt) is dict: # _header_fmtがあれば不要だが互換性のために残してある
for col in ws.iter_cols():
col_name = col[0].value
if col_name in _letter_fmt:
num_format = _letter_fmt[col_name]
for cell in col:
cell.number_format = num_format
elif type(_letter_fmt) is str:
for col in ws.iter_cols():
for cell in col:
cell.number_format = _letter_fmt
# 列ごとの個別設定で上書き
if _header_fmt != None:
list_col = list(_header_fmt.keys())
for col in ws.iter_cols():
col_name = col[0].value
if col_name in list_col: # 列書式一覧の辞書にこの列が存在する
try:
fmt = _header_fmt[col_name]
num_format = fmt['dtxtformat']
for cell in col:
cell.number_format = num_format
except:
pass
# Worksheetの列幅調整
if _auto_sz: # 自動調整
for col in ws.columns:
max_length = 0
column = col[0].column
column = get_column_letter(column) # 数字をアルファベットに変換
cols = col if _header_rot != 90 else col[1:]
for cell in cols:
if len(str(cell.value)) > max_length:
max_length = len(str(cell.value))
adjusted_width = (max_length + 2) * 1.1
ws.column_dimensions[column].width = adjusted_width
else:
for col in ws.columns:
column = col[0].column # 列番号を取得
col_letter = get_column_letter(column) # 列番号を列記号に変換
width = _col_width_[0] if column == 1 else _col_width_[1] # 列幅
ws.column_dimensions[col_letter].width = width
# 列ごとの個別調整
if _header_fmt != None:
list_col = list(ws.columns)
for head, fmt in _header_fmt.items():
try:
width = fmt['width']
index = list(df.columns).index(head)
if _is_index:
index += 1
col = list_col[index]
column = col[0].column # 列番号を取得
col_letter = get_column_letter(column) # 列番号を列記号に変換
ws.column_dimensions[col_letter].width = width
except:
pass
# Worksheetの行の高さ調整
if _header_height != None:
ws.row_dimensions[1].height = _header_height
# ヒートマップ
from openpyxl.formatting.rule import ColorScale, FormatObject
from openpyxl.styles import Color
if _heatmap == 1: # 赤 → 白 → 青
first = FormatObject(type='min')
last = FormatObject(type='max')
# colors match the format objects:
colors = [Color('F8696B'), Color('5A8AC6')]
# a three color scale would extend the sequences
mid = FormatObject(type='percentile', val=50)
colors.insert(1, Color('FCFCFF'))
cs3 = ColorScale(cfvo=[first, mid, last], color=colors)
# create a rule with the color scale
from openpyxl.formatting.rule import Rule
rule = Rule(type='colorScale', colorScale=cs3)
# 対象範囲を示す文字列を作成
rg = 'A2:' + get_column_letter(ws.max_column) + str(ws.max_row)
ws.conditional_formatting.add(rg, rule)
elif _heatmap == 2: # 白 → 橙 → 赤
first = FormatObject(type='min')
last = FormatObject(type='max')
# colors match the format objects:
colors = [Color('FFFFFF'), Color('F8696B')]
# a three color scale would extend the sequences
mid = FormatObject(type='percentile', val=50)
colors.insert(1, Color('FFEB84'))
cs3 = ColorScale(cfvo=[first, mid, last], color=colors)
# create a rule with the color scale
from openpyxl.formatting.rule import Rule
rule = Rule(type='colorScale', colorScale=cs3)
# 対象範囲を示す文字列を作成
rg = 'A2:' + get_column_letter(ws.max_column) + str(ws.max_row)
ws.conditional_formatting.add(rg, rule)
elif _heatmap == 3: # 赤 → 橙 → 白
first = FormatObject(type='min')
last = FormatObject(type='max')
# colors match the format objects:
colors = [Color('F8696B'), Color('FFFFFF')]
# a three color scale would extend the sequences
mid = FormatObject(type='percentile', val=25)
colors.insert(1, Color('FFEB84'))
cs3 = ColorScale(cfvo=[first, mid, last], color=colors)
# create a rule with the color scale
from openpyxl.formatting.rule import Rule
rule = Rule(type='colorScale', colorScale=cs3)
# 対象範囲を示す文字列を作成
rg = 'A2:' + get_column_letter(ws.max_column) + str(ws.max_row)
ws.conditional_formatting.add(rg, rule)
# 枠固定
if _frz != None:
ws.freeze_panes = _frz
# オートフィルタ
if _auto_flt:
ws.auto_filter.ref = 'A1:' + get_column_letter(ws.max_column) + '1'
# 表示倍率
ws.sheet_view.zoomScale = _zoom
#-------------------------------------------
# Worksheetに画像を挿入
#-------------------------------------------
if _img != None:
from openpyxl.drawing.image import Image
for img in _img:
fpath = img[0] # 挿入する画像ファイル
anchor = img[1] # 挿入位置
px_img = Image(fpath)
px_img.anchor = anchor
ws.add_image(px_img)
#-------------------------------------------
# Excelファイルに書き込み
#-------------------------------------------
# 最後に不要なシートを削除
if 'Sheet' in wb.sheetnames:
wb.remove(wb['Sheet'])
print(' - ファイル書き込み...', end='')
wb.save(_path)
# 画像ファイル削除
if _img != None:
for img in _img:
is_delete = False # ファイルを削除するか否か
if len(img) > 2:
is_delete = img[2]
if is_delete: # ファイル削除
os.remove(fpath)
print(
'\n ---> Finished. (処理時間:{0:.3f}[sec])'.format(time.perf_counter() -
time_start))
return wb
def generate_excel(report, name, reporting_start_datetime_local,
reporting_end_datetime_local, period_type):
wb = Workbook()
# todo
ws = wb.active
# Row height
ws.row_dimensions[1].height = 102
for i in range(2, 2000 + 1):
ws.row_dimensions[i].height = 42
# Col width
ws.column_dimensions['A'].width = 1.5
ws.column_dimensions['B'].width = 25.0
for i in range(ord('C'), ord('L')):
ws.column_dimensions[chr(i)].width = 15.0
# Font
name_font = Font(name='Constantia', size=15, bold=True)
title_font = Font(name='宋体', size=15, bold=True)
data_font = Font(name='Franklin Gothic Book', size=11)
table_fill = PatternFill(fill_type='solid', fgColor='1F497D')
f_border = Border(left=Side(border_style='medium', color='00000000'),
right=Side(border_style='medium', color='00000000'),
bottom=Side(border_style='medium', color='00000000'),
top=Side(border_style='medium', color='00000000'))
b_border = Border(bottom=Side(border_style='medium', color='00000000'), )
b_c_alignment = Alignment(vertical='bottom',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
c_c_alignment = Alignment(vertical='center',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
b_r_alignment = Alignment(vertical='bottom',
horizontal='right',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
c_r_alignment = Alignment(vertical='bottom',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
# Img
img = Image("excelexporters/myems.png")
img.width = img.width * 0.85
img.height = img.height * 0.85
# img = Image("myems.png")
ws.add_image(img, 'B1')
# Title
ws.row_dimensions[3].height = 60
ws['B3'].font = name_font
ws['B3'].alignment = b_r_alignment
ws['B3'] = 'Name:'
ws['C3'].border = b_border
ws['C3'].alignment = b_c_alignment
ws['C3'].font = name_font
ws['C3'] = name
ws['D3'].font = name_font
ws['D3'].alignment = b_r_alignment
ws['D3'] = 'Period:'
ws['E3'].border = b_border
ws['E3'].alignment = b_c_alignment
ws['E3'].font = name_font
ws['E3'] = period_type
ws['F3'].font = name_font
ws['F3'].alignment = b_r_alignment
ws['F3'] = 'Date:'
ws['G3'].border = b_border
ws['G3'].alignment = b_c_alignment
ws['G3'].font = name_font
ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
ws.merge_cells("G3:H3")
if "reporting_period" not in report.keys() or \
"values" not in report['reporting_period'].keys() or len(report['reporting_period']['values']) == 0:
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
###############################
has_cost_data_flag = True
if "values" not in report['reporting_period'].keys() or len(
report['reporting_period']['values']) == 0:
has_cost_data_flag = False
if has_cost_data_flag:
ws['B6'].font = title_font
ws['B6'] = name + '报告期成本'
reporting_period_data = report['reporting_period']
category = report['offline_meter']['energy_category_name']
ca_len = len(category)
ws.row_dimensions[7].height = 60
ws['B7'].fill = table_fill
ws['B7'].border = f_border
ws['B8'].font = title_font
ws['B8'].alignment = c_c_alignment
ws['B8'] = '成本'
ws['B8'].border = f_border
ws['B9'].font = title_font
ws['B9'].alignment = c_c_alignment
ws['B9'] = '环比'
ws['B9'].border = f_border
col = 'B'
for i in range(0, ca_len):
col = chr(ord('C') + i)
ws[col + '7'].fill = table_fill
ws[col + '7'].font = name_font
ws[col + '7'].alignment = c_c_alignment
ws[col + '7'] = report['offline_meter']['energy_category_name'] + \
" (" + report['offline_meter']['unit_of_measure'] + ")"
ws[col + '7'].border = f_border
ws[col + '8'].font = name_font
ws[col + '8'].alignment = c_c_alignment
ws[col + '8'] = round(reporting_period_data['total_in_category'],
2)
ws[col + '8'].border = f_border
ws[col + '9'].font = name_font
ws[col + '9'].alignment = c_c_alignment
ws[col + '9'] = str(round(reporting_period_data['increment_rate'] * 100, 2)) + "%" \
if reporting_period_data['increment_rate'] is not None else "-"
ws[col + '9'].border = f_border
# TCE TCO2E
end_col = col
# TCE
tce_col = chr(ord(end_col) + 1)
ws[tce_col + '7'].fill = table_fill
ws[tce_col + '7'].font = name_font
ws[tce_col + '7'].alignment = c_c_alignment
ws[tce_col + '7'] = "吨标准煤 (TCE)"
ws[tce_col + '7'].border = f_border
ws[tce_col + '8'].font = name_font
ws[tce_col + '8'].alignment = c_c_alignment
ws[tce_col + '8'] = round(
reporting_period_data['total_in_kgce'] / 1000, 2)
ws[tce_col + '8'].border = f_border
ws[tce_col + '9'].font = name_font
ws[tce_col + '9'].alignment = c_c_alignment
ws[tce_col + '9'] = str(round(reporting_period_data['increment_rate'] * 100, 2)) + "%" \
if reporting_period_data['increment_rate'] is not None else "-"
ws[tce_col + '9'].border = f_border
# TCO2E
tco2e_col = chr(ord(end_col) + 2)
ws[tco2e_col + '7'].fill = table_fill
ws[tco2e_col + '7'].font = name_font
ws[tco2e_col + '7'].alignment = c_c_alignment
ws[tco2e_col + '7'] = "吨二氧化碳排放 (TCO2E)"
ws[tco2e_col + '7'].border = f_border
ws[tco2e_col + '8'].font = name_font
ws[tco2e_col + '8'].alignment = c_c_alignment
ws[tco2e_col + '8'] = round(
reporting_period_data['total_in_kgco2e'] / 1000, 2)
ws[tco2e_col + '8'].border = f_border
ws[tco2e_col + '9'].font = name_font
ws[tco2e_col + '9'].alignment = c_c_alignment
ws[tco2e_col + '9'] = str(round(reporting_period_data['increment_rate'] * 100, 2)) + "%" \
if reporting_period_data['increment_rate'] is not None else "-"
ws[tco2e_col + '9'].border = f_border
else:
for i in range(6, 9 + 1):
ws.rows_dimensions[i].height = 0.1
######################################
has_cost_datail_flag = True
reporting_period_data = report['reporting_period']
category = report['offline_meter']['energy_category_name']
ca_len = len(category)
times = reporting_period_data['timestamps']
if "values" not in reporting_period_data.keys() or len(
reporting_period_data['values']) == 0:
has_cost_datail_flag = False
if has_cost_datail_flag:
ws['B11'].font = title_font
ws['B11'] = name + '详细数据'
ws.row_dimensions[18].height = 60
ws['B18'].fill = table_fill
ws['B18'].font = title_font
ws['B18'].border = f_border
ws['B18'].alignment = c_c_alignment
ws['B18'] = '日期时间'
time = times
has_data = False
max_row = 0
if len(time) > 0:
has_data = True
max_row = 18 + len(time)
if has_data:
end_data_row_number = 19
for i in range(0, len(time)):
col = 'B'
end_data_row_number = 19 + i
row = str(end_data_row_number)
ws[col + row].font = title_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = time[i]
ws[col + row].border = f_border
ws['B' + str(end_data_row_number + 1)].font = title_font
ws['B' + str(end_data_row_number + 1)].alignment = c_c_alignment
ws['B' + str(end_data_row_number + 1)] = '总计'
ws['B' + str(end_data_row_number + 1)].border = f_border
for i in range(0, ca_len):
col = chr(ord('C') + i)
ws[col + '18'].fill = table_fill
ws[col + '18'].font = title_font
ws[col + '18'].alignment = c_c_alignment
ws[col + '18'] = report['offline_meter']['energy_category_name'] + \
" (" + report['offline_meter']['unit_of_measure'] + ")"
ws[col + '18'].border = f_border
time = times
time_len = len(time)
for j in range(0, time_len):
row = str(19 + j)
ws[col + row].font = title_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = round(reporting_period_data['values'][j],
2)
ws[col + row].border = f_border
ws[col + str(end_data_row_number + 1)].font = title_font
ws[col +
str(end_data_row_number + 1)].alignment = c_c_alignment
ws[col + str(end_data_row_number + 1)] = round(
reporting_period_data['total_in_category'], 2)
ws[col + str(end_data_row_number + 1)].border = f_border
line = LineChart()
line.title = '报告期成本 - ' + report['offline_meter']['energy_category_name'] + \
" (" + report['offline_meter']['unit_of_measure'] + ")"
line_data = Reference(ws, min_col=3, min_row=18, max_row=max_row)
line.series.append(Series(line_data, title_from_data=True))
labels = Reference(ws, min_col=2, min_row=19, max_row=max_row)
line.set_categories(labels)
line_data = line.series[0]
line_data.marker.symbol = "circle"
line_data.smooth = True
line.x_axis.crosses = 'min'
line.dLbls = DataLabelList()
line.dLbls.dLblPos = 't'
line.dLbls.showVal = True
line.height = 8.25
line.width = 24
ws.add_chart(line, "B12")
else:
for i in range(11, 43 + 1):
ws.row_dimensions[i].height = 0.0
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
def generate_excel(report, name, reporting_start_datetime_local,
reporting_end_datetime_local, period_type):
wb = Workbook()
ws = wb.active
# Row height
ws.row_dimensions[1].height = 118
for i in range(2, 37 + 1):
ws.row_dimensions[i].height = 30
for i in range(38, 69 + 1):
ws.row_dimensions[i].height = 30
# Col width
ws.column_dimensions['A'].width = 1.5
for i in range(ord('B'), ord('I')):
ws.column_dimensions[chr(i)].width = 15.0
# Font
name_font = Font(name='Constantia', size=15, bold=True)
title_font = Font(name='宋体', size=15, bold=True)
data_font = Font(name='Franklin Gothic Book', size=11)
table_fill = PatternFill(fill_type='solid', fgColor='1F497D')
f_border = Border(left=Side(border_style='medium', color='00000000'),
right=Side(border_style='medium', color='00000000'),
bottom=Side(border_style='medium', color='00000000'),
top=Side(border_style='medium', color='00000000'))
b_border = Border(bottom=Side(border_style='medium', color='00000000'), )
b_c_alignment = Alignment(vertical='bottom',
horizontal='center',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
c_c_alignment = Alignment(vertical='center',
horizontal='center',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
b_r_alignment = Alignment(vertical='bottom',
horizontal='right',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
c_r_alignment = Alignment(vertical='bottom',
horizontal='center',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
# Img
img = Image("excelexporters/myems.png")
ws.add_image(img, 'B1')
# Title
ws['B3'].font = name_font
ws['B3'].alignment = b_r_alignment
ws['B3'] = 'Name:'
ws['C3'].border = b_border
ws['C3'].alignment = b_c_alignment
ws['C3'].font = name_font
ws['C3'] = name
ws['D3'].font = name_font
ws['D3'].alignment = b_r_alignment
ws['D3'] = 'Period:'
ws['E3'].border = b_border
ws['E3'].alignment = b_c_alignment
ws['E3'].font = name_font
ws['E3'] = period_type
ws['F3'].font = name_font
ws['F3'].alignment = b_r_alignment
ws['F3'] = 'Date:'
ws['G3'].border = b_border
ws['G3'].alignment = b_c_alignment
ws['G3'].font = name_font
ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
ws.merge_cells("G3:H3")
if "reporting_period" not in report.keys() or \
"names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0:
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
#################################################
# First: 能耗分析
# 6: title
# 7: table title
# 8~10 table_data
# Total: 5 rows
# if has not energy data: set low height for rows
#################################################
reporting_period_data = report['reporting_period']
has_energy_data_flag = True
if "names" not in reporting_period_data.keys() or \
reporting_period_data['names'] is None or \
len(reporting_period_data['names']) ==0:
has_energy_data_flag = False
if has_energy_data_flag:
ws['B6'].font = title_font
ws['B6'] = name + ' 能耗分析'
category = reporting_period_data['names']
ca_len = len(category)
ws['B7'].fill = table_fill
ws['B8'].font = title_font
ws['B8'].alignment = c_c_alignment
ws['B8'] = '能耗'
ws['B8'].border = f_border
ws['B9'].font = title_font
ws['B9'].alignment = c_c_alignment
ws['B9'] = '单位面积能耗'
ws['B9'].border = f_border
ws['B10'].font = title_font
ws['B10'].alignment = c_c_alignment
ws['B10'] = '环比'
ws['B10'].border = f_border
for i in range(0, ca_len):
col = chr(ord('C') + i)
row = '7'
cell = col + row
ws[col + '7'].fill = table_fill
ws[col + '7'].font = name_font
ws[col + '7'].alignment = c_c_alignment
ws[col + '7'] = reporting_period_data['names'][
i] + " (" + reporting_period_data['units'][i] + ")"
ws[col + '7'].border = f_border
ws[col + '8'].font = name_font
ws[col + '8'].alignment = c_c_alignment
ws[col + '8'] = round(reporting_period_data['subtotals'][i], 0)
ws[col + '8'].border = f_border
ws[col + '9'].font = name_font
ws[col + '9'].alignment = c_c_alignment
ws[col + '9'] = round(
reporting_period_data['subtotals_per_unit_area'][i], 2)
ws[col + '9'].border = f_border
ws[col + '10'].font = name_font
ws[col + '10'].alignment = c_c_alignment
ws[col + '10'] = str(
round(reporting_period_data['increment_rates'][i] * 100,
2)) + "%"
ws[col + '10'].border = f_border
# TCE TCO2E
end_col = col
# TCE
tce_col = chr(ord(end_col) + 1)
ws[tce_col + '7'].fill = table_fill
ws[tce_col + '7'].font = name_font
ws[tce_col + '7'].alignment = c_c_alignment
ws[tce_col + '7'] = "TCE"
ws[tce_col + '7'].border = f_border
ws[tce_col + '8'].font = name_font
ws[tce_col + '8'].alignment = c_c_alignment
ws[tce_col + '8'] = round(reporting_period_data['total_in_kgce'], 0)
ws[tce_col + '8'].border = f_border
ws[tce_col + '9'].font = name_font
ws[tce_col + '9'].alignment = c_c_alignment
ws[tce_col + '9'] = round(
reporting_period_data['total_in_kgce_per_unit_area'], 2)
ws[tce_col + '9'].border = f_border
ws[tce_col + '10'].font = name_font
ws[tce_col + '10'].alignment = c_c_alignment
ws[tce_col + '10'] = str(
round(reporting_period_data['increment_rate_in_kgce'] * 100,
2)) + "%"
ws[tce_col + '10'].border = f_border
# TCO2E
tco2e_col = chr(ord(end_col) + 2)
ws[tco2e_col + '7'].fill = table_fill
ws[tco2e_col + '7'].font = name_font
ws[tco2e_col + '7'].alignment = c_c_alignment
ws[tco2e_col + '7'] = "TCO2E"
ws[tco2e_col + '7'].border = f_border
ws[tco2e_col + '8'].font = name_font
ws[tco2e_col + '8'].alignment = c_c_alignment
ws[tco2e_col + '8'] = round(reporting_period_data['total_in_kgco2e'],
0)
ws[tco2e_col + '8'].border = f_border
ws[tco2e_col + '9'].font = name_font
ws[tco2e_col + '9'].alignment = c_c_alignment
ws[tco2e_col + '9'] = round(
reporting_period_data['total_in_kgco2e_per_unit_area'], 2)
ws[tco2e_col + '9'].border = f_border
ws[tco2e_col + '10'].font = name_font
ws[tco2e_col + '10'].alignment = c_c_alignment
ws[tco2e_col + '10'] = str(
round(reporting_period_data['increment_rate_in_kgco2e'] * 100,
2)) + "%"
ws[tco2e_col + '10'].border = f_border
else:
for i in range(6, 10 + 1):
ws.row_dimensions[i].height = 0.1
#################################################
# Second: 分时电耗
# 12: title
# 13: table title
# 14~17 table_data
# Total: 6 rows
################################################
has_ele_peak_flag = True
if "toppeaks" not in reporting_period_data.keys() or \
reporting_period_data['toppeaks'] is None or \
len(reporting_period_data['toppeaks']) == 0:
has_ele_peak_flag = False
if has_ele_peak_flag:
ws['B12'].font = title_font
ws['B12'] = name + ' 分时电耗'
ws['B13'].fill = table_fill
ws['B13'].font = name_font
ws['B13'].alignment = c_c_alignment
ws['B13'].border = f_border
ws['C13'].fill = table_fill
ws['C13'].font = name_font
ws['C13'].alignment = c_c_alignment
ws['C13'].border = f_border
ws['C13'] = '分时电耗'
ws['B14'].font = title_font
ws['B14'].alignment = c_c_alignment
ws['B14'] = '尖'
ws['B14'].border = f_border
ws['C14'].font = title_font
ws['C14'].alignment = c_c_alignment
ws['C14'].border = f_border
ws['C14'] = round(reporting_period_data['toppeaks'][0], 0)
ws['B15'].font = title_font
ws['B15'].alignment = c_c_alignment
ws['B15'] = '峰'
ws['B15'].border = f_border
ws['C15'].font = title_font
ws['C15'].alignment = c_c_alignment
ws['C15'].border = f_border
ws['C15'] = round(reporting_period_data['onpeaks'][0], 0)
ws['B16'].font = title_font
ws['B16'].alignment = c_c_alignment
ws['B16'] = '平'
ws['B16'].border = f_border
ws['C16'].font = title_font
ws['C16'].alignment = c_c_alignment
ws['C16'].border = f_border
ws['C16'] = round(reporting_period_data['midpeaks'][0], 0)
ws['B17'].font = title_font
ws['B17'].alignment = c_c_alignment
ws['B17'] = '谷'
ws['B17'].border = f_border
ws['C17'].font = title_font
ws['C17'].alignment = c_c_alignment
ws['C17'].border = f_border
ws['C17'] = round(reporting_period_data['offpeaks'][0], 0)
pie = PieChart()
labels = Reference(ws, min_col=2, min_row=14, max_row=17)
pie_data = Reference(ws, min_col=3, min_row=14, max_row=17)
pie.add_data(pie_data, titles_from_data=True)
pie.set_categories(labels)
pie.height = 5.25 # cm 1.05*5 1.05cm = 30 pt
pie.width = 9
# pie.title = "Pies sold by category"
s1 = pie.series[0]
s1.dLbls = DataLabelList()
s1.dLbls.showCatName = True # 标签显示
s1.dLbls.showVal = True # 数量显示
s1.dLbls.showPercent = True # 百分比显示
# s1 = CharacterProperties(sz=1800) # 图表中字体大小 *100
ws.add_chart(pie, "D13")
else:
for i in range(12, 18 + 1):
ws.row_dimensions[i].height = 0.1
# end_row 10
# start_row 12
################################################
# Third: 子空间能耗
# 19: title
# 20: table title
# 21~24 table_data
# Total: 6 rows
################################################
has_child_flag = True
# Judge if the space has child space, if not, delete it.
if "child_space" not in report.keys() or "energy_category_names" not in report['child_space'].keys() or \
len(report['child_space']["energy_category_names"]) == 0:
has_child_flag = False
if has_child_flag:
child = report['child_space']
child_spaces = child['child_space_names_array'][0]
child_subtotals = child['subtotals_array'][0]
ws['B19'].font = title_font
ws['B19'] = name + ' 子空间能耗'
ws['B20'].fill = table_fill
ws['B20'].border = f_border
ws['C20'].fill = table_fill
ws['C20'].font = title_font
ws['C20'].alignment = c_c_alignment
ws['C20'].border = f_border
ws['C20'] = child['energy_category_names'][0]
ca_len = len(child['energy_category_names'])
space_len = len(child['child_space_names_array'][0])
for i in range(0, space_len):
row = str(i + 21)
ws['B' + row].font = name_font
ws['B' + row].alignment = c_c_alignment
ws['B' + row] = child['child_space_names_array'][0][i]
ws['B' + row].border = f_border
for j in range(0, ca_len):
col = chr(ord('C') + j)
ws[col + row].font = name_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = child['subtotals_array'][0][i]
ws[col + row].border = f_border
# pie
# 25~30: pie
pie = PieChart()
labels = Reference(ws, min_col=2, min_row=21, max_row=23)
pie_data = Reference(ws, min_col=3 + j, min_row=21, max_row=23)
pie.add_data(pie_data, titles_from_data=True)
pie.set_categories(labels)
pie.height = 5.25 # cm 1.05*5 1.05cm = 30 pt
pie.width = 8
# pie.title = "Pies sold by category"
s1 = pie.series[0]
s1.dLbls = DataLabelList()
s1.dLbls.showCatName = True # 标签显示
s1.dLbls.showVal = True # 数量显示
s1.dLbls.showPercent = True # 百分比显示
# s1 = CharacterProperties(sz=1800) # 图表中字体大小 *100
chart_col = chr(ord('B') + 2 * j)
chart_cell = chart_col + '26'
ws.add_chart(pie, chart_cell)
else:
for i in range(19, 36 + 1):
ws.row_dimensions[i].height = 0.1
################################################
# Fourth: 能耗详情
# 37: title
# 38: table title
# 39~69: table_data
################################################
reporting_period_data = report['reporting_period']
times = reporting_period_data['timestamps']
has_detail_data_flag = True
if "timestamps" not in reporting_period_data.keys() or \
reporting_period_data['timestamps'] is None or \
len(reporting_period_data['timestamps']) == 0:
has_detail_data_flag = False
if has_detail_data_flag:
ws['B37'].font = title_font
ws['B37'] = name + ' 能耗详情'
ws['B38'].fill = table_fill
ws['B38'].border = f_border
ws['B38'].alignment = c_c_alignment
ws['B38'] = '时间'
time = times[0]
has_data = False
max_row = 0
if len(time) > 0:
has_data = True
max_row = 38 + len(time)
print("max_row", max_row)
if has_data:
for i in range(0, len(time)):
col = 'B'
row = str(39 + i)
# col = chr(ord('B') + i)
ws[col + row].font = title_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = time[i]
ws[col + row].border = f_border
for i in range(0, ca_len):
# 38 title
col = chr(ord('C') + i)
ws[col + '38'].fill = table_fill
ws[col + '38'].font = title_font
ws[col + '38'].alignment = c_c_alignment
ws[col + '38'] = reporting_period_data['names'][
i] + " (" + reporting_period_data['units'][i] + ")"
ws[col + '38'].border = f_border
# 39 data
time = times[i]
time_len = len(time)
for j in range(0, time_len):
row = str(39 + j)
# col = chr(ord('B') + i)
ws[col + row].font = title_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = round(
reporting_period_data['values'][i][j], 0)
ws[col + row].border = f_border
# bar
# 39~: bar
bar = BarChart()
labels = Reference(ws,
min_col=2,
min_row=39,
max_row=max_row + 1)
bar_data = Reference(ws,
min_col=3 + i,
min_row=38,
max_row=max_row + 1) # openpyxl bug
bar.add_data(bar_data, titles_from_data=True)
bar.set_categories(labels)
bar.height = 5.25 # cm 1.05*5 1.05cm = 30 pt
bar.width = 18
# pie.title = "Pies sold by category"
bar.dLbls = DataLabelList()
bar.dLbls.showCatName = True # label show
bar.dLbls.showVal = True # val show
bar.dLbls.showPercent = True # percent show
# s1 = CharacterProperties(sz=1800) # font size *100
chart_col = chr(ord('B') + 2 * i)
chart_cell = chart_col + str(max_row + 2)
ws.add_chart(bar, chart_cell)
else:
for i in range(37, 69 + 1):
ws.row_dimensions[i].height = 0.1
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
def read_drawings(ws, drawings_path, archive, valid_files):
""" Given a worksheet and the XML of its drawings file, links drawings to cells
"""
drawings_codename = os.path.split(drawings_path)[-1]
rels_file = PACKAGE_DRAWINGS_RELS + '/' + drawings_codename + '.rels'
if rels_file not in valid_files:
return None
rels_source = archive.read(rels_file)
rels_root = fromstring(rels_source)
root = fromstring(archive.read(drawings_path))
for node in root:
col, row = 0, 0
posX = posY = cX = cY = None
name = u''
cell_from = node.find('{%s}from' % SHEET_DRAWING_NS)
if cell_from is not None:
col = cell_from.find('{%s}col' % SHEET_DRAWING_NS)
if col is not None:
col = int(col.text)
row = cell_from.find('{%s}row' % SHEET_DRAWING_NS)
if row is not None:
row = int(row.text)
cell = ws['%s%s' % (get_column_letter(col + 1), row + 1)]
pos = node.find('{%s}pos' % SHEET_DRAWING_NS)
if pos is not None:
posX, posY = _get_position(pos)
ext = node.find('{%s}ext' % SHEET_DRAWING_NS)
if ext is not None:
cX, cY = _get_dimension(ext)
pic = node.find('{%s}pic' % SHEET_DRAWING_NS)
if pic is not None:
sp_pr = pic.find('{%s}spPr' % SHEET_DRAWING_NS)
if sp_pr is not None:
xfrm = sp_pr.find('{%s}xfrm' % DRAWING_NS)
if xfrm is not None:
ext = xfrm.find('{%s}ext' % DRAWING_NS)
if ext is not None:
cX, cY = _get_dimension(ext)
nv_pic_pr = pic.find('{%s}nvPicPr' % SHEET_DRAWING_NS)
if nv_pic_pr is not None:
nv_pic_pr = nv_pic_pr.find('{%s}cNvPr' % SHEET_DRAWING_NS)
if nv_pic_pr is not None:
name = nv_pic_pr.attrib.get('name', '')
blip_fill = pic.find('{%s}blipFill' % SHEET_DRAWING_NS)
if blip_fill is not None:
blip = blip_fill.find('{%s}blip' % DRAWING_NS)
if blip is not None:
rid = blip.attrib.get('{%s}embed' % REL_NS)
if rid is not None:
image_file = read_image_file(rels_root, rid, valid_files)
if image_file:
img = Image(BytesIO(archive.read(image_file)))
if posY is not None and posX is not None:
img.drawing.top = posY
img.drawing.left = posX
else:
img.anchor(cell, anchortype='oneCell')
if cX is not None and cY is not None:
img.drawing.width = cX
img.drawing.height = cY
img.drawing.name = name
ws.add_image(img)
''' Need to develop flexible means of accomodating different instrument_id '''
''' e.g. AD Hub DA ATC vs AD Hub RT ATC vs AD Hub ATC - Monthly Strip '''
valuation_date = date(2017, 6, 28)
forecast_start = date(2017, 7,1)
forecast_end = date(2021, 12, 31)
df_hedges = get_hedges('Lightstone', 'HoldCo', forecast_start, forecast_end)
df_all_prices = get_all_commodity_prices(valuation_date)
excel_template = 'c:/users/agood/projects/kean_proj/templates/hedge detail.xlsx'
wb = opx.load_workbook(filename = excel_template)
ws = wb.active
image = Image('c:/users/agood/projects/kean_proj/graphics/Lightstone_Logo.jpg')
ws.add_image(image, 'A1')
ws.row_dimensions[1].height = 54
''' clear main body of worksheet '''
for row in ws.iter_rows(row_offset=4):
for cell in row:
cell.value = None
loop_rows = 1
remaining_column = 8 + (13 - forecast_start.month) + 1
data_format = NamedStyle(name='data', number_format='#,##0_);(#,##0)')
df_hedges_relevant = df_hedges[df_hedges['trade_date']<forecast_start]
df_hedges_relevant = df_hedges_relevant.reset_index(drop=True)
for i, hedge in df_hedges_relevant.iterrows():
ws.cell(row=i+5, column=1, value=hedge.loc['tid_kindle'])
def main():
for f_path in f_pathes:
print(f_path)
f_name, ext = os.path.splitext(
os.path.basename(f_path)) #パスから filename と 拡張子を抽出
save_path = os.path.join(temp_dir, f_name + '_T' + ext) # 保存先のファイルパス
save_sort_path = os.path.join(temp_dir, f_name + '_sort' + ext)
# 表を画像として保存するパスを設定
img_path = os.path.join(temp_dir, f_name + '.png')
# Excel ファイルの保存先のパス
save_excel_path = os.path.join(dist_dir, f_name + '_sort' + '.xlsx')
df = pd.read_csv(f_path, header=None,
names=index_name) # csv ファイルを読み出し
df.index = index_name # 列名を追加
df.T # 配列を転置する
df.to_csv(save_path) # 転置したデータを一度保存
df = pd.read_csv(save_path, header=None) # 保存したデータを再度読み出し
# 1列目の値をもとに行の順番をソート
df_row_sort = df.sort_values(0,
axis=0,
ascending=True,
na_position='first')
# 1行目の値をもとに列の順番をソート
df_col_sort = df_row_sort.sort_values(0,
axis=1,
ascending=True,
na_position='first')
df_col_sort.to_csv(save_sort_path, header=None,
index=False) # header と index を除いて保存
df = pd.read_csv(save_sort_path, index_col=0) # 保存したデータを再々度読み出し
# --------------------- #
# 4つの変数を計算 #
# --------------------- #
tp = _Calc_TP(df)
fp = _Calc_FP(df, tp)
fn = _Calc_FN(df, tp)
tn = _Calc_TN(df, tp=tp, fn=fn, fp=fp)
# ------------------------------------- #
# クラスごとの評価指標の計算 #
# ------------------------------------- #
tpr = _Calc_TPR(tp, fn)
fnr = _Calc_FNR(tpr)
tnr = _Calc_TNR(tn, fp)
fpr = _Calc_FPR(tnr)
tpa = _Calc_TPA(tp, fp)
tna = _Calc_TNR(tn, fp)
Evaluation_tuple = {
"TP": tp,
"TN": tn,
"FP": fp,
"FN": fn,
"真陽性率(TPR)": tpr,
"偽陰性率(FNR)": fnr,
"真陰性率(TNR)": tnr,
"偽陽性率(FPR)": fpr,
"真陽性精度(TPA)": tpa,
"真陰性精度(TNA)": tna,
}
res = pd.DataFrame(Evaluation_tuple)
# -------------------------------- #
# 評価指標の平均値の計算 #
# -------------------------------- #
acc = _Calc_Acc(df, tp)
Evaluation_tuple = {
"Acc": [acc],
}
res2 = pd.DataFrame(Evaluation_tuple)
# xslx ファイルに書き出し
with pd.ExcelWriter(save_excel_path) as writer:
df.to_excel(writer, sheet_name="Confusion_matrix")
res.to_excel(writer, sheet_name="Evaluation_index", index=False)
res2.to_excel(writer, sheet_name="Average_index", index=False)
#混同行列の表を作成
_Drowing_Table(df, save_path=img_path)
# 混同行列の表を Excel ファイルに保存
img = Image(img_path) # 画像を読み出し
try:
wb = openpyxl.load_workbook(save_excel_path)
ws = wb.create_sheet(title="Image")
ws.add_image(img, 'A1')
wb.save(save_excel_path)
except Exception as e:
print('Error: ', e)
finally:
wb.close()
def insertImage(self,image,image_size,anchor=None):
image=Image(image)
image.width,image.height=image_size
self.sheet.add_image(image,anchor)
col = 1
row = 2
#
for data in data_list: # data_list에서 문장들을 하나씩 가지고 오기
img_list = data.find(
'img') # img태그(속성과 속성값이 없거나 필요없거나 하면 생략, img로도 충분히 가려낼수 있으니)값
img_src = img_list['src']
a_list = data.find('a')
title = a_list['title']
title = re.sub('[^0-9a-zA-Zㄱ-힗]', '', title)
link = "https://comic.naver.com" + a_list['href']
strong = data.find('strong').text
#urlretrieve(img_src, './image/'+title+'.gif')
img_file = Image('./image/' + title + '.gif')
# pprint(img_file)
#cell = sheet1.cell(row=row, column=1)
img_file.anchor = 'A' + str(row)
#pprint('A' + str(col))
sheet1.add_image(img_file)
sheet1.cell(row=row, column=2).value = title
sheet1.cell(row=row, column=3).value = strong
sheet1.cell(row=row, column=4).value = link
# col = col + 1
row = row + 1
wb.save("./webtoon.xlsx")
#print(title, strong, link)
'''
data2 = data1.findAll('dd')
def run():
connection = None
cursor = None
try:
connection = psycopg2.connect(user='******',
password='******',
host='127.0.0.1',
port='5432',
database='ksgmet')
cursor = connection.cursor()
workbook = Workbook()
sheet = None
for region in REGIONS:
if sheet:
sheet = workbook.create_sheet()
else:
sheet = workbook.active
sheet.title = region.name
sheet.page_setup.fitToWidth = 1
x, y = 1, 1
sheet.merge_cells(start_row=x, start_column=y, end_row=x, end_column=y + len(PREDICTIONS))
desc_cell = sheet.cell(row=x, column=y, value=region.description)
desc_cell.alignment = Alignment(wrap_text=True, vertical='top')
row = sheet.row_dimensions[x]
row.height = IMG_ROW_HEIGHT
sheet.merge_cells(range_string='J1:L1')
if region.map_file:
img = Image('D:\\workspace\\MGR\\maps\\regions\\' + region.map_file)
img.anchor = 'J1'
img.width = IMG_WIDTH
img.height = IMG_HEIGHT
sheet.add_image(img)
x += 1
for field in FIELDS:
sheet.cell(row=x, column=y, value='Pole ' + field)
sheet.merge_cells(start_row=x, start_column=y, end_row=x, end_column=y + 5)
field_row = sheet.row_dimensions[x]
field_row.font = BOLD_FONT
field_row.fill = PatternFill('solid', fgColor='FFFF00')
x += 1
y = 1
for date_range in DATE_RANGES:
# Header
header = '{} - {}'.format(field, date_range.description)
header_cell = sheet.cell(row=x, column=y, value=header)
sheet.merge_cells(start_row=x, start_column=y, end_row=x, end_column=y + len(PREDICTIONS))
header_cell.font = BOLD_FONT
header_cell.alignment = Alignment(horizontal='center')
# Bold the column
col = sheet.column_dimensions[get_column_letter(y)]
col.width = 40
x += 1
cell = sheet.cell(row=x, column=y, value='Odległość prognozy')
cell.font = BOLD_FONT
x += 1
cell = sheet.cell(row=x, column=y, value='Ilość analizowanych prognoz')
cell.font = BOLD_FONT
x += 1
cell = sheet.cell(row=x, column=y, value='Średnia różnica')
cell.font = BOLD_FONT
x += 1
cell = sheet.cell(row=x, column=y, value='Średnia różnica wartości bezwzględnych')
cell.font = BOLD_FONT
x += 1
cell = sheet.cell(row=x, column=y, value='Odchylenie standardowe')
cell.font = BOLD_FONT
# Go to data coll
x -= 4
y += 1
for prediction in PREDICTIONS:
cell = sheet.cell(row=x, column=y, value=prediction.description())
cell.font = BOLD_FONT
x += 1
data = execute_query(cursor, region, date_range, field, prediction)
for val in data:
sheet.cell(row=x, column=y, value=val)
x += 1
# Back up to data coll start and go to the next one
y += 1
x -= 5
if region == ALL_DATA:
img_row = x + 5
img_col = y - len(PREDICTIONS) - 1
sheet.merge_cells(start_row=img_row, end_row=img_row,
start_column=img_col, end_column=img_col + 2)
row = sheet.row_dimensions[img_row]
row.height = IMG_ROW_HEIGHT
img = Image('D:\\workspace\\MGR\\maps\\first_analysis\\' + date_range.map_dir_part() +
'\\' + field + '_avg_ad.png')
img.anchor = sheet.cell(row=img_row, column=img_col).coordinate
img.width = IMG_WIDTH
img.height = IMG_HEIGHT
sheet.add_image(img)
img_col = img_col + 3
sheet.merge_cells(start_row=img_row, end_row=img_row,
start_column=img_col, end_column=img_col + 5)
img = Image('D:\\workspace\\MGR\\maps\\first_analysis\\' + date_range.map_dir_part() +
'\\' + field + '_abs_avg_ad.png')
img.anchor = sheet.cell(row=img_row, column=img_col).coordinate
img.width = IMG_WIDTH
img.height = IMG_HEIGHT
sheet.add_image(img)
img_col = img_col - 3
img_row = img_row + 1
row = sheet.row_dimensions[img_row]
row.height = IMG_ROW_HEIGHT
sheet.merge_cells(start_row=img_row, end_row=img_row,
start_column=img_col, end_column=img_col + 2)
img = Image('D:\\workspace\\MGR\\maps\\first_analysis\\' + date_range.map_dir_part() +
'\\' + field + '_std_dev_ad.png')
img.anchor = sheet.cell(row=img_row, column=img_col).coordinate
img.width = IMG_WIDTH
img.height = IMG_HEIGHT
sheet.add_image(img)
# Go back to header
x -= 1
# Space between date ranges
col = sheet.column_dimensions[get_column_letter(y)]
col.fill = PatternFill('solid', fgColor='E0E0E0')
y += 1
# New field, 8 = 5 data rows, 2 img row and one space
x += 9
y = 1
workbook.save('D:\\workspace\\MGR\\first_analysis\\data.xlsx')
finally:
if cursor:
cursor.close()
if connection:
connection.close()
def generate_excel(report,
name,
reporting_start_datetime_local,
reporting_end_datetime_local,
period_type):
wb = Workbook()
ws = wb.active
# Row height
ws.row_dimensions[1].height = 102
for i in range(2, 2000 + 1):
ws.row_dimensions[i].height = 42
# Col width
ws.column_dimensions['A'].width = 1.5
ws.column_dimensions['B'].width = 25.0
for i in range(ord('C'), ord('L')):
ws.column_dimensions[chr(i)].width = 15.0
# Font
name_font = Font(name='Constantia', size=15, bold=True)
title_font = Font(name='宋体', size=15, bold=True)
data_font = Font(name='Franklin Gothic Book', size=11)
table_fill = PatternFill(fill_type='solid', fgColor='1F497D')
f_border = Border(left=Side(border_style='medium', color='00000000'),
right=Side(border_style='medium', color='00000000'),
bottom=Side(border_style='medium', color='00000000'),
top=Side(border_style='medium', color='00000000')
)
b_border = Border(
bottom=Side(border_style='medium', color='00000000'),
)
b_c_alignment = Alignment(vertical='bottom',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
c_c_alignment = Alignment(vertical='center',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
b_r_alignment = Alignment(vertical='bottom',
horizontal='right',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
c_r_alignment = Alignment(vertical='bottom',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
# Img
img = Image("excelexporters/myems.png")
img.width = img.width * 0.85
img.height = img.height * 0.85
# img = Image("myems.png")
ws.add_image(img, 'B1')
# Title
ws.row_dimensions[3].height = 60
ws['B3'].font = name_font
ws['B3'].alignment = b_r_alignment
ws['B3'] = 'Name:'
ws['C3'].border = b_border
ws['C3'].alignment = b_c_alignment
ws['C3'].font = name_font
ws['C3'] = name
ws['D3'].font = name_font
ws['D3'].alignment = b_r_alignment
ws['D3'] = 'Period:'
ws['E3'].border = b_border
ws['E3'].alignment = b_c_alignment
ws['E3'].font = name_font
ws['E3'] = period_type
ws['F3'].font = name_font
ws['F3'].alignment = b_r_alignment
ws['F3'] = 'Date:'
ws['G3'].border = b_border
ws['G3'].alignment = b_c_alignment
ws['G3'].font = name_font
ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
ws.merge_cells("G3:H3")
if "reporting_period" not in report.keys() or \
"names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0:
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
##################################
reporting_period_data = report['reporting_period']
has_cost_data_flag = True
if "names" not in reporting_period_data.keys() or \
reporting_period_data['names'] is None or \
len(reporting_period_data['names']) == 0:
has_cost_data_flag = False
if has_cost_data_flag:
ws['B5'].font = title_font
ws['B5'] = name + ' 报告期收入'
category = reporting_period_data['names']
ca_len = len(category)
ws.row_dimensions[7].height = 60
ws['B6'].fill = table_fill
ws['B6'].border = f_border
ws['B7'].font = title_font
ws['B7'].alignment = c_c_alignment
ws['B7'] = '报告期收入'
ws['B7'].border = f_border
ws['B8'].font = title_font
ws['B8'].alignment = c_c_alignment
ws['B8'] = '环比'
ws['B8'].border = f_border
col = ''
for i in range(0, ca_len):
col = chr(ord('C') + i)
ws[col + '6'].fill = table_fill
ws[col + '6'].font = name_font
ws[col + '6'].alignment = c_c_alignment
ws[col + '6'] = reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
ws[col + '6'].border = f_border
ws[col + '7'].font = name_font
ws[col + '7'].alignment = c_c_alignment
ws[col + '7'] = round(reporting_period_data['subtotals'][i], 2)
ws[col + '7'].border = f_border
ws[col + '8'].font = name_font
ws[col + '8'].alignment = c_c_alignment
ws[col + '8'] = str(round(reporting_period_data['increment_rates'][i] * 100, 2)) + "%" \
if reporting_period_data['increment_rates'][i] is not None else "-"
ws[col + '8'].border = f_border
col = chr(ord(col) + 1)
ws[col + '6'].fill = table_fill
ws[col + '6'].font = name_font
ws[col + '6'].alignment = c_c_alignment
ws[col + '6'] = "总计 (" + reporting_period_data['total_unit'] + ")"
ws[col + '6'].border = f_border
ws[col + '7'].font = name_font
ws[col + '7'].alignment = c_c_alignment
ws[col + '7'] = round(reporting_period_data['total'], 2)
ws[col + '7'].border = f_border
ws[col + '8'].font = name_font
ws[col + '8'].alignment = c_c_alignment
ws[col + '8'] = str(round(reporting_period_data['total_increment_rate'] * 100, 2)) + "%" \
if reporting_period_data['total_increment_rate'] is not None else "-"
ws[col + '8'].border = f_border
else:
for i in range(6, 8 + 1):
ws.row_dimensions[i].height = 0.1
##################################
current_row_number = 10
has_subtotals_data_flag = True
if "subtotals" not in reporting_period_data.keys() or \
reporting_period_data['subtotals'] is None or \
len(reporting_period_data['subtotals']) == 0:
has_subtotals_data_flag = False
if has_subtotals_data_flag:
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 收入占比'
current_row_number += 1
table_start_row_number = current_row_number
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].font = name_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)].fill = table_fill
ws['C' + str(current_row_number)].font = name_font
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = '收入'
ws['D' + str(current_row_number)].fill = table_fill
ws['D' + str(current_row_number)].font = name_font
ws['D' + str(current_row_number)].alignment = c_c_alignment
ws['D' + str(current_row_number)].border = f_border
ws['D' + str(current_row_number)] = '收入占比'
current_row_number += 1
ca_len = len(reporting_period_data['names'])
wssum = Decimal(0.0)
for i in range(0, ca_len):
wssum = round(reporting_period_data['subtotals'][i], 2) + wssum
for i in range(0, ca_len):
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)] = reporting_period_data['names'][i]
ws['B' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)].font = title_font
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = round(reporting_period_data['subtotals'][i], 2)
ws['D' + str(current_row_number)].font = title_font
ws['D' + str(current_row_number)].alignment = c_c_alignment
ws['D' + str(current_row_number)].border = f_border
ws['D' + str(current_row_number)] = '{:.2%}'.format(round(
reporting_period_data['subtotals'][i], 2) / wssum) if wssum is not None and wssum != Decimal(0.0) \
else " "
current_row_number += 1
table_end_row_number = current_row_number - 1
pie = PieChart()
pie.title = name + ' 收入占比'
labels = Reference(ws, min_col=2, min_row=table_start_row_number + 1, max_row=table_end_row_number)
pie_data = Reference(ws, min_col=3, min_row=table_start_row_number, max_row=table_end_row_number)
pie.add_data(pie_data, titles_from_data=True)
pie.set_categories(labels)
pie.height = 6.6
pie.width = 9
s1 = pie.series[0]
s1.dLbls = DataLabelList()
s1.dLbls.showCatName = False
s1.dLbls.showVal = True
s1.dLbls.showPercent = True
table_cell = 'F' + str(table_start_row_number - 1)
ws.add_chart(pie, table_cell)
if ca_len < 4:
current_row_number = current_row_number - ca_len + 4
else:
for i in range(13, 22 + 1):
ws.row_dimensions[i].height = 0.1
#############################################
current_row_number = 14
reporting_period_data = report['reporting_period']
times = reporting_period_data['timestamps']
has_detail_data_flag = True
ca_len = len(report['reporting_period']['names'])
table_row = (current_row_number + 1) + ca_len * 6
if "timestamps" not in reporting_period_data.keys() or \
reporting_period_data['timestamps'] is None or \
len(reporting_period_data['timestamps']) == 0:
has_detail_data_flag = False
if has_detail_data_flag:
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 详细数据'
ws.row_dimensions[table_row].height = 60
ws['B' + str(table_row)].fill = table_fill
ws['B' + str(table_row)].font = title_font
ws['B' + str(table_row)].border = f_border
ws['B' + str(table_row)].alignment = c_c_alignment
ws['B' + str(table_row)] = '日期时间'
time = times[0]
has_data = False
max_row = 0
if len(time) > 0:
has_data = True
max_row = table_row + len(time)
if has_data:
for i in range(0, len(time)):
col = 'B'
row = str(table_row + 1 + i)
ws[col + row].font = title_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = time[i]
ws[col + row].border = f_border
for i in range(0, ca_len):
col = chr(ord('C') + i)
ws[col + str(table_row)].fill = table_fill
ws[col + str(table_row)].font = title_font
ws[col + str(table_row)].alignment = c_c_alignment
ws[col + str(table_row)] = reporting_period_data['names'][i] + " (" + reporting_period_data['units'][
i] + ")"
ws[col + str(table_row)].border = f_border
# 39 data
time = times[i]
time_len = len(time)
for j in range(0, time_len):
row = str(table_row + 1 + j)
ws[col + row].font = title_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = round(reporting_period_data['values'][i][j], 2)
ws[col + row].border = f_border
line = LineChart()
line.title = \
'报告期收入 - ' + reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
labels = Reference(ws, min_col=2, min_row=table_row + 1, max_row=max_row)
line_data = Reference(ws, min_col=3 + i, min_row=table_row, max_row=max_row)
line.add_data(line_data, titles_from_data=True)
line.set_categories(labels)
line_data = line.series[0]
line_data.marker.symbol = "circle"
line_data.smooth = True
line.x_axis.crosses = 'min'
line.height = 8.25
line.width = 24
line.dLbls = DataLabelList()
line.dLbls.dLblPos = 't'
line.dLbls.showVal = True
line.dLbls.showPercent = False
chart_col = 'B'
chart_cell = chart_col + str(current_row_number + 1 + 6 * i)
ws.add_chart(line, chart_cell)
row = str(max_row + 1)
ws['B' + row].font = title_font
ws['B' + row].alignment = c_c_alignment
ws['B' + row] = '小计'
ws['B' + row].border = f_border
col = ''
for i in range(0, ca_len):
col = chr(ord('C') + i)
row = str(max_row + 1)
ws[col + row].font = title_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = round(reporting_period_data['subtotals'][i], 2)
ws[col + row].border = f_border
col = chr(ord(col) + 1)
ws[col + str(table_row)].fill = table_fill
ws[col + str(table_row)].font = title_font
ws[col + str(table_row)].alignment = c_c_alignment
ws[col + str(table_row)] = '总计 (' + report['reporting_period']['total_unit'] + ')'
ws[col + str(table_row)].border = f_border
total_sum = 0
for j in range(0, len(time)):
row = str(table_row + 1 + j)
ws[col + row].font = title_font
ws[col + row].alignment = c_c_alignment
every_day_sum = reporting_period_values_every_day_sum(reporting_period_data, j, ca_len)
total_sum += every_day_sum
ws[col + row] = round(every_day_sum, 2)
ws[col + row].border = f_border
row = str(table_row + 1 + len(time))
ws[col + row].font = title_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = round(total_sum, 2)
ws[col + row].border = f_border
else:
for i in range(37, 69 + 1):
ws.row_dimensions[i].height = 0.1
#####################################
has_associated_equipment_flag = True
time_len = len(times[0])
current_row_number = time_len + table_row + 3
if "associated_equipment" not in report.keys() or \
"energy_category_names" not in report['associated_equipment'].keys() or \
len(report['associated_equipment']["energy_category_names"]) == 0 \
or 'associated_equipment_names_array' not in report['associated_equipment'].keys() \
or report['associated_equipment']['associated_equipment_names_array'] is None \
or len(report['associated_equipment']['associated_equipment_names_array']) == 0 \
or len(report['associated_equipment']['associated_equipment_names_array'][0]) == 0:
has_associated_equipment_flag = False
if has_associated_equipment_flag:
associated_equipment = report['associated_equipment']
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 相关设备数据'
current_row_number += 1
ws.row_dimensions[current_row_number].height = 60
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].font = name_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '相关设备'
ca_len = len(associated_equipment['energy_category_names'])
for i in range(0, ca_len):
col = chr(ord('C') + i)
ws[col + str(current_row_number)].fill = table_fill
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = \
reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
col_subtotal = chr(ord('C') + ca_len)
ws[col_subtotal + str(current_row_number)].fill = table_fill
ws[col_subtotal + str(current_row_number)].font = name_font
ws[col_subtotal + str(current_row_number)].alignment = c_c_alignment
ws[col_subtotal + str(current_row_number)].border = f_border
ws[col_subtotal + str(current_row_number)] = '总计 (' + report['reporting_period']['total_unit'] + ')'
associated_equipment_len = len(associated_equipment['associated_equipment_names_array'][0])
for i in range(0, associated_equipment_len):
current_row_number += 1
row = str(current_row_number)
ws['B' + row].font = title_font
ws['B' + row].alignment = c_c_alignment
ws['B' + row] = associated_equipment['associated_equipment_names_array'][0][i]
ws['B' + row].border = f_border
subtotal = Decimal(0.0)
for j in range(0, ca_len):
col = chr(ord('C') + j)
ws[col + row].font = title_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = round(associated_equipment['subtotals_array'][j][i], 2)
ws[col + row].border = f_border
subtotal += associated_equipment['subtotals_array'][j][i]
ws[col_subtotal + row].font = title_font
ws[col_subtotal + row].alignment = c_c_alignment
ws[col_subtotal + row] = round(subtotal, 2)
ws[col_subtotal + row].border = f_border
print(subtotal)
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
elif child.tag == 'password':
ecpassword = child.text
elif child.tag == 'username':
ecusername = child.text
wb = openpyxl.load_workbook('Finesse.xlsx')
sheet = wb.get_sheet_by_name('Cluster Setup')
sheet['B34'] = host
sheet['B35'] = backupHost
sheet['B36'] = databaseName
sheet['B37'] = port
sheet['B38'] = ecusername
sheet['B39'] = ecpassword
sheet['B40'] = uri
ws = wb.active
img = Image('DDlogo.png')
ws.add_image(img, 'A1')
wb.save('Finesse.xlsx')
# GET layout
response = GET(layout, username, password)
#xmlcontent='layout.xml'
#fileopen=open(xmlcontent,'wb')
#fileopen.write(response.content)
#fileopen.close()
#Variable initialization for Finesse 11.5 & less
uri = ''
layoutxml = ''
#tree= ET.parse('layout.xml')
picture_path3 = "D:\\2022\\基坑监测\\电子签名png格式\\黄地发电子签.png"
path = "D:\\2022\\基坑监测\\科创大街\\科创大街数据库-报表\\17监测日报2022-4-18.xlsx"
excel = win32com.client.DispatchEx('Excel.Application') # 这个是必备的，
#使用win32建新excel也需要他
excel.Visible = True # 是否可视化
wb = excel.Workbooks.Open(path, UpdateLinks=False, ReadOnly=False)
ws = wb.Worksheets['封面']
for shp in ws.Shapes:
if (shp.Type == constants.msoPicture):
shp.Delete() #z只删除图片的shape
#
####worksheet.insert_image(row=1, col=5, filename='E:\\临时图片\\' + v, options={'x_scale': 0.1, 'y_scale': 0.1})
image = Image(picture_path1)
image1 = Image(picture_path2)
image2 = Image(picture_path3)
data = pandas.read_excel(path, '封面')
book1 = load_workbook(path)
sheet1 = book1.get_sheet_by_name('封面')
image.width = 140
image.height = 120
image1.width = 140
image.height = 120
image2.width = 100
image2.height = 70
sheet1.add_image(image, 'B8')
sheet1.add_image(image1, 'B9')
sheet1.add_image(image2, 'B10')
def output_to_excel():
from openpyxl import Workbook
from openpyxl.styles import PatternFill, Font, Border, numbers, Side, Alignment
from openpyxl.drawing.image import Image
# 定义样式
font_col = Font(name='dengxian', size=20, bold=True, color='FFFFFFFF')
font_panzheng = Font(name='dengxian', size=19, bold=True, color='FFF08600')
font_prob = Font(name='dengxian', size=19, bold=False, color='FF007AC6')
fill_col = PatternFill(fill_type='solid',
start_color='FF0070C6',
end_color='FF0070C6')
fill_row = PatternFill(fill_type='solid',
start_color='FFE4F3FC',
end_color='FFE4F3FC')
fill_prob = PatternFill(fill_type='solid',
start_color='FFF7F7F7',
end_color='FFF7F7F7')
format_percent = numbers.BUILTIN_FORMATS[9]
border_corner1 = Border(left=Side(border_style='thick', color='FF999999'),
right=Side(border_style='thin', color='FFFFFFFF'),
top=Side(border_style='thick', color='FF999999'),
bottom=Side(border_style='thin', color='FFFFFFFF'))
border_corner2 = Border(left=Side(border_style='thin', color='FFFFFFFF'),
right=Side(border_style='thick', color='FF999999'),
top=Side(border_style='thick', color='FF999999'),
bottom=Side(border_style='thin', color='FFFFFFFF'))
border_corner3 = Border(left=Side(border_style='thin', color='FFFFFFFF'),
right=Side(border_style='thick', color='FF999999'),
top=Side(border_style='thin', color='FFFFFFFF'),
bottom=Side(border_style='thick',
color='FF999999'))
border_corner4 = Border(left=Side(border_style='thick', color='FF999999'),
right=Side(border_style='thin', color='FFFFFFFF'),
top=Side(border_style='thin', color='FFFFFFFF'),
bottom=Side(border_style='thick',
color='FF999999'))
border_up = Border(left=Side(border_style='thin', color='FFFFFFFF'),
right=Side(border_style='thin', color='FFFFFFFF'),
top=Side(border_style='thick', color='FF999999'),
bottom=Side(border_style='thin', color='FFFFFFFF'))
border_left = Border(left=Side(border_style='thick', color='FF999999'),
right=Side(border_style='thin', color='FFFFFFFF'),
top=Side(border_style='thin', color='FFFFFFFF'),
bottom=Side(border_style='thin', color='FFFFFFFF'))
border_right = Border(left=Side(border_style='thin', color='FFFFFFFF'),
right=Side(border_style='thick', color='FF999999'),
top=Side(border_style='thin', color='FFFFFFFF'),
bottom=Side(border_style='thin', color='FFFFFFFF'))
border_down = Border(left=Side(border_style='thin', color='FFFFFFFF'),
right=Side(border_style='thin', color='FFFFFFFF'),
top=Side(border_style='thin', color='FFFFFFFF'),
bottom=Side(border_style='thick', color='FF999999'))
border_inside = Border(left=Side(border_style='thin', color='FFFFFFFF'),
right=Side(border_style='thin', color='FFFFFFFF'),
top=Side(border_style='thin', color='FFFFFFFF'),
bottom=Side(border_style='thin', color='FFFFFFFF'))
alignment = Alignment(horizontal='center',
vertical='center',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
wb = Workbook()
ws = wb.active
prob_up_5, prob_mid_5, prob_down_5 = cal_prob(0)
prob_up_20, prob_mid_20, prob_down_20 = cal_prob(1)
ws.append(['一周', '概率', '一月', '概率'])
ws.append(['', prob_up_5, '', prob_up_20])
ws.append(['盘整', prob_mid_5, '盘整', prob_mid_20])
ws.append(['', prob_down_5, '', prob_down_20])
up, up2 = Image('up.png'), Image('up.png')
down, down2 = Image('down.png'), Image('down.png')
ws.add_image(up, 'A2')
ws.add_image(up2, 'C2')
ws.add_image(down, 'A4')
ws.add_image(down2, 'C4')
#curve=Image('.\curve.png')
#ws.add_image(curve, 'A6')
for c in ['A', 'B', 'C', 'D']:
ws.column_dimensions[c].width = 20
for r in range(1, 5):
ws.row_dimensions[r].height = 35
for row in range(1, 5):
for col in range(4):
ws[row][col].border = border_inside
ws[1][0].border, ws[1][3].border, ws[4][3].border, ws[4][
0].border = border_corner1, border_corner2, border_corner3, border_corner4
ws[1][1].border, ws[1][2].border = border_up, border_up
ws[4][1].border, ws[4][2].border = border_down, border_down
ws[2][0].border, ws[3][0].border = border_left, border_left
ws[2][3].border, ws[3][3].border = border_right, border_right
for row in range(1, 5):
for col in [0, 2]:
ws[row][col].fill = fill_row
ws[row][col].font = font_col
ws[row][col].alignment = alignment
for col in [1, 3]:
ws[row][col].fill = fill_prob
ws[row][col].font = font_prob
ws[row][col].number_format = format_percent
ws[row][col].alignment = alignment
for col in range(4):
ws[1][col].fill = fill_col
ws[1][col].font = font_col
ws[3][0].font, ws[3][2].font = font_panzheng, font_panzheng
wb.save(INDEX_SYMBOL + "_Probability.xlsx")
print("[Processing]: Output to EXCEL file succeed.")
def generate_excel(report, name, reporting_start_datetime_local,
reporting_end_datetime_local, period_type):
wb = Workbook()
ws = wb.active
# Row height
ws.row_dimensions[1].height = 102
for i in range(2, 2000 + 1):
ws.row_dimensions[i].height = 42
# Col width
ws.column_dimensions['A'].width = 1.5
ws.column_dimensions['B'].width = 25.0
for i in range(ord('C'), ord('L')):
ws.column_dimensions[chr(i)].width = 15.0
# Font
name_font = Font(name='Constantia', size=15, bold=True)
title_font = Font(name='宋体', size=15, bold=True)
data_font = Font(name='Franklin Gothic Book', size=11)
table_fill = PatternFill(fill_type='solid', fgColor='1F497D')
f_border = Border(left=Side(border_style='medium', color='00000000'),
right=Side(border_style='medium', color='00000000'),
bottom=Side(border_style='medium', color='00000000'),
top=Side(border_style='medium', color='00000000'))
b_border = Border(bottom=Side(border_style='medium', color='00000000'), )
b_c_alignment = Alignment(vertical='bottom',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
c_c_alignment = Alignment(vertical='center',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
b_r_alignment = Alignment(vertical='bottom',
horizontal='right',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
c_r_alignment = Alignment(vertical='bottom',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
# Img
img = Image("excelexporters/myems.png")
img.width = img.width * 0.85
img.height = img.height * 0.85
# img = Image("myems.png")
ws.add_image(img, 'B1')
# Title
ws.row_dimensions[3].height = 60
ws['B3'].font = name_font
ws['B3'].alignment = b_r_alignment
ws['B3'] = 'Name:'
ws['C3'].border = b_border
ws['C3'].alignment = b_c_alignment
ws['C3'].font = name_font
ws['C3'] = name
ws['D3'].font = name_font
ws['D3'].alignment = b_r_alignment
ws['D3'] = 'Period:'
ws['E3'].border = b_border
ws['E3'].alignment = b_c_alignment
ws['E3'].font = name_font
ws['E3'] = period_type
ws['F3'].font = name_font
ws['F3'].alignment = b_r_alignment
ws['F3'] = 'Date:'
ws['G3'].border = b_border
ws['G3'].alignment = b_c_alignment
ws['G3'].font = name_font
ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
ws.merge_cells("G3:H3")
if "reporting_period" not in report.keys() or \
"names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0:
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
##################################
current_row_number = 6
reporting_period_data = report['reporting_period']
has_names_data_flag = True
if "names" not in reporting_period_data.keys() or \
reporting_period_data['names'] is None or \
len(reporting_period_data['names']) == 0:
has_names_data_flag = False
if has_names_data_flag:
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 报告期消耗'
current_row_number += 1
category = reporting_period_data['names']
ca_len = len(category)
ws.row_dimensions[current_row_number].height = 60
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].border = f_border
col = 'C'
for i in range(0, ca_len):
ws[col + str(current_row_number)].fill = table_fill
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = \
reporting_period_data['names'][i] + " " + reporting_period_data['energy_category_names'][i] + \
" (" + reporting_period_data['units'][i] + ")"
col = chr(ord(col) + 1)
current_row_number += 1
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '消耗'
col = 'C'
for i in range(0, ca_len):
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = round(
reporting_period_data['subtotals'][i], 2)
col = chr(ord(col) + 1)
current_row_number += 1
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '环比'
col = 'C'
for i in range(0, ca_len):
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = str(
round(reporting_period_data['increment_rates'][i] * 100, 2)) + '%' \
if reporting_period_data['increment_rates'][i] is not None else '-'
col = chr(ord(col) + 1)
current_row_number += 2
category_dict = group_by_category(
reporting_period_data['energy_category_names'])
for category_dict_name, category_dict_values in category_dict.items():
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = \
name + ' ' + category_dict_name + ' (' + reporting_period_data['units'][category_dict_values[0]] + \
') 分项消耗占比'
current_row_number += 1
table_start_row_number = current_row_number
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)].font = name_font
ws['C' + str(current_row_number)].fill = table_fill
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = '消耗'
current_row_number += 1
for i in category_dict_values:
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = \
reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
ws['C' + str(current_row_number)].font = name_font
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = round(
reporting_period_data['subtotals'][i], 3)
current_row_number += 1
table_end_row_number = current_row_number - 1
pie = PieChart()
pie.title = \
name + ' ' + category_dict_name + ' (' + reporting_period_data['units'][category_dict_values[0]] + \
') 分项消耗占比'
labels = Reference(ws,
min_col=2,
min_row=table_start_row_number + 1,
max_row=table_end_row_number)
pie_data = Reference(ws,
min_col=3,
min_row=table_start_row_number,
max_row=table_end_row_number)
pie.add_data(pie_data, titles_from_data=True)
pie.set_categories(labels)
pie.height = 6.6
pie.width = 9
s1 = pie.series[0]
s1.dLbls = DataLabelList()
s1.dLbls.showCatName = False
s1.dLbls.showVal = True
s1.dLbls.showPercent = True
ws.add_chart(pie, 'D' + str(table_start_row_number))
if len(category_dict_values) < 4:
current_row_number = current_row_number - len(
category_dict_values) + 4
current_row_number += 1
#####################################
has_values_data = True
has_timestamps_data = True
if 'values' not in reporting_period_data.keys() or \
reporting_period_data['values'] is None or \
len(reporting_period_data['values']) == 0:
has_values_data = False
if 'timestamps' not in reporting_period_data.keys() or \
reporting_period_data['timestamps'] is None or \
len(reporting_period_data['timestamps']) == 0 or \
len(reporting_period_data['timestamps'][0]) == 0:
has_timestamps_data = False
if has_values_data and has_timestamps_data:
ca_len = len(reporting_period_data['names'])
time = reporting_period_data['timestamps'][0]
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 详细数据'
current_row_number += 1
chart_start_row_number = current_row_number
current_row_number += ca_len * 6
table_start_row_number = current_row_number
ws.row_dimensions[current_row_number].height = 60
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '日期时间'
col = 'C'
for i in range(0, ca_len):
ws[col + str(current_row_number)].fill = table_fill
ws[col + str(current_row_number)].font = title_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = \
reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
col = chr(ord(col) + 1)
current_row_number += 1
for i in range(0, len(time)):
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = time[i]
col = 'C'
for j in range(0, ca_len):
ws[col + str(current_row_number)].font = title_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = round(reporting_period_data['values'][j][i], 2) \
if reporting_period_data['values'][j][i] is not None else 0.00
col = chr(ord(col) + 1)
current_row_number += 1
table_end_row_number = current_row_number - 1
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '小计'
col = 'C'
for i in range(0, ca_len):
ws[col + str(current_row_number)].font = title_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = round(
reporting_period_data['subtotals'][i], 2)
col = chr(ord(col) + 1)
current_row_number += 2
format_time_width_number = 1.0
min_len_number = 1.0
min_width_number = 11.0 # format_time_width_number * min_len_number + 4 and min_width_number > 11.0
if period_type == 'hourly':
format_time_width_number = 4.0
min_len_number = 2
min_width_number = 12.0
elif period_type == 'daily':
format_time_width_number = 2.5
min_len_number = 4
min_width_number = 14.0
elif period_type == 'monthly':
format_time_width_number = 2.1
min_len_number = 4
min_width_number = 12.4
elif period_type == 'yearly':
format_time_width_number = 1.5
min_len_number = 5
min_width_number = 11.5
for i in range(0, ca_len):
line = LineChart()
line.title = '报告期消耗 - ' + \
reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
labels = Reference(ws,
min_col=2,
min_row=table_start_row_number + 1,
max_row=table_end_row_number)
line_data = Reference(ws,
min_col=3 + i,
min_row=table_start_row_number,
max_row=table_end_row_number)
line.add_data(line_data, titles_from_data=True)
line.set_categories(labels)
line_data = line.series[0]
line_data.marker.symbol = "circle"
line_data.smooth = True
line.x_axis.crosses = 'min'
line.height = 8.25
line.width = format_time_width_number * len(time) if len(
time) > min_len_number else min_width_number
if line.width > 24:
line.width = 24
line.dLbls = DataLabelList()
line.dLbls.dLblPos = 't'
line.dLbls.showVal = True
line.dLbls.showPercent = False
chart_col = 'B'
chart_cell = chart_col + str(chart_start_row_number)
chart_start_row_number += 6
ws.add_chart(line, chart_cell)
#####################################
has_associated_equipment_flag = True
if "associated_equipment" not in report.keys() or \
"energy_item_names" not in report['associated_equipment'].keys() or \
len(report['associated_equipment']["energy_item_names"]) == 0 \
or 'associated_equipment_names_array' not in report['associated_equipment'].keys() \
or report['associated_equipment']['associated_equipment_names_array'] is None \
or len(report['associated_equipment']['associated_equipment_names_array']) == 0 \
or len(report['associated_equipment']['associated_equipment_names_array'][0]) == 0:
has_associated_equipment_flag = False
if has_associated_equipment_flag:
associated_equipment = report['associated_equipment']
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 相关设备数据'
current_row_number += 1
table_start_row_number = current_row_number
ws.row_dimensions[current_row_number].height = 60
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].font = name_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '相关设备'
ca_len = len(associated_equipment['energy_item_names'])
for i in range(0, ca_len):
row = chr(ord('C') + i)
ws[row + str(current_row_number)].fill = table_fill
ws[row + str(current_row_number)].font = name_font
ws[row + str(current_row_number)].alignment = c_c_alignment
ws[row + str(current_row_number)].border = f_border
ws[row + str(current_row_number)] = \
reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
associated_equipment_len = len(
associated_equipment['associated_equipment_names_array'][0])
for i in range(0, associated_equipment_len):
current_row_number += 1
row = str(current_row_number)
ws['B' + row].font = title_font
ws['B' + row].alignment = c_c_alignment
ws['B' + row] = associated_equipment[
'associated_equipment_names_array'][0][i]
ws['B' + row].border = f_border
for j in range(0, ca_len):
col = chr(ord('C') + j)
ws[col + row].font = name_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = round(
associated_equipment['subtotals_array'][j][i], 2)
ws[col + row].border = f_border
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
from openpyxl import Workbook
from openpyxl.drawing.image import Image
wb = Workbook()
ws = wb.active
ws['A1'] = 'You should see three logos below'
# create an image
# img1 = Image('Tulips.jpg')
img2 = Image("1.png")
# add to worksheet and anchor next to cells
ws.add_image(img2, "A3")
ws.add_image(img2, "C5")
#注意不能将相同图片对象添加到不同单元格中，程序只会在一个位置生成多个图片。
#本例中的多个图片对象，则可以分配给多个不同单元格对象
wb.save(u'f:\\示例.xlsx')
'A': ' (1)',
'B': ' (2)',
'C': ' (3)',
'D': ' (4)',
'E': ' (5)',
'F': ' (6)',
'G': ' (7)',
'H': ' (8)',
'I': ' (9)',
'J': ' (10)'
}
for sheet_name in sheet_names1:
ws = wb[sheet_name]
img = Image(imgfile_path + sheet_name + '.JPG')
img.width = 1378 #35 cm (100dpi環境下)
img.height = 775 #19.69 cm
ws.add_image(img, 'A1')
for s_name, f_name in sheet_names2.items():
ws = wb[s_name]
img = Image(imgfile_path + f_name + '.png')
img.width = 1378 #35 cm (100dpi環境下)
img.height = 775 #19.69 cm
ws.add_image(img, 'A1')
wb.save(xlfile_path)
def generate_excel(report,
name,
reporting_start_datetime_local,
reporting_end_datetime_local,
period_type):
wb = Workbook()
ws = wb.active
# Row height
ws.row_dimensions[1].height = 102
for i in range(2, 2000 + 1):
ws.row_dimensions[i].height = 42
# Col width
ws.column_dimensions['A'].width = 1.5
ws.column_dimensions['B'].width = 25.0
for i in range(ord('C'), ord('L')):
ws.column_dimensions[chr(i)].width = 15.0
# Font
name_font = Font(name='Constantia', size=15, bold=True)
title_font = Font(name='宋体', size=15, bold=True)
data_font = Font(name='Franklin Gothic Book', size=11)
table_fill = PatternFill(fill_type='solid', fgColor='1F497D')
f_border = Border(left=Side(border_style='medium', color='00000000'),
right=Side(border_style='medium', color='00000000'),
bottom=Side(border_style='medium', color='00000000'),
top=Side(border_style='medium', color='00000000')
)
b_border = Border(
bottom=Side(border_style='medium', color='00000000'),
)
b_c_alignment = Alignment(vertical='bottom',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
c_c_alignment = Alignment(vertical='center',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
b_r_alignment = Alignment(vertical='bottom',
horizontal='right',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
c_r_alignment = Alignment(vertical='bottom',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
# Img
img = Image("excelexporters/myems.png")
img.width = img.width * 0.85
img.height = img.height * 0.85
# img = Image("myems.png")
ws.add_image(img, 'B1')
# Title
ws.row_dimensions[3].height = 60
ws['B3'].font = name_font
ws['B3'].alignment = b_r_alignment
ws['B3'] = 'Name:'
ws['C3'].border = b_border
ws['C3'].alignment = b_c_alignment
ws['C3'].font = name_font
ws['C3'] = name
ws['D3'].font = name_font
ws['D3'].alignment = b_r_alignment
ws['D3'] = 'Period:'
ws['E3'].border = b_border
ws['E3'].alignment = b_c_alignment
ws['E3'].font = name_font
ws['E3'] = period_type
ws['F3'].font = name_font
ws['F3'].alignment = b_r_alignment
ws['F3'] = 'Date:'
ws['G3'].border = b_border
ws['G3'].alignment = b_c_alignment
ws['G3'].font = name_font
ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
ws.merge_cells("G3:H3")
if "reporting_period" not in report.keys() or \
"names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0:
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
##################################
reporting_period_data = report['reporting_period']
has_cost_data_flag = True
if "names" not in reporting_period_data.keys() or \
reporting_period_data['names'] is None or \
len(reporting_period_data['names']) == 0:
has_cost_data_flag = False
if has_cost_data_flag:
ws['B5'].font = title_font
ws['B5'] = name + ' 报告期收入'
category = reporting_period_data['names']
ca_len = len(category)
ws.row_dimensions[7].height = 60
ws['B6'].fill = table_fill
ws['B6'].border = f_border
ws['B7'].font = title_font
ws['B7'].alignment = c_c_alignment
ws['B7'] = '报告期收入'
ws['B7'].border = f_border
ws['B8'].font = title_font
ws['B8'].alignment = c_c_alignment
ws['B8'] = '环比'
ws['B8'].border = f_border
col = ''
for i in range(0, ca_len):
col = chr(ord('C') + i)
ws[col + '6'].fill = table_fill
ws[col + '6'].font = name_font
ws[col + '6'].alignment = c_c_alignment
ws[col + '6'] = reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
ws[col + '6'].border = f_border
ws[col + '7'].font = name_font
ws[col + '7'].alignment = c_c_alignment
ws[col + '7'] = round(reporting_period_data['subtotals'][i], 2)
ws[col + '7'].border = f_border
ws[col + '8'].font = name_font
ws[col + '8'].alignment = c_c_alignment
ws[col + '8'] = str(round(reporting_period_data['increment_rates'][i] * 100, 2)) + "%" \
if reporting_period_data['increment_rates'][i] is not None else "-"
ws[col + '8'].border = f_border
col = chr(ord(col) + 1)
ws[col + '6'].fill = table_fill
ws[col + '6'].font = name_font
ws[col + '6'].alignment = c_c_alignment
ws[col + '6'] = "总计 (" + reporting_period_data['total_unit'] + ")"
ws[col + '6'].border = f_border
ws[col + '7'].font = name_font
ws[col + '7'].alignment = c_c_alignment
ws[col + '7'] = round(reporting_period_data['total'], 2)
ws[col + '7'].border = f_border
ws[col + '8'].font = name_font
ws[col + '8'].alignment = c_c_alignment
ws[col + '8'] = str(round(reporting_period_data['total_increment_rate'] * 100, 2)) + "%" \
if reporting_period_data['total_increment_rate'] is not None else "-"
ws[col + '8'].border = f_border
else:
for i in range(6, 8 + 1):
ws.row_dimensions[i].height = 0.1
##################################
current_row_number = 10
has_subtotals_data_flag = True
if "subtotals" not in reporting_period_data.keys() or \
reporting_period_data['subtotals'] is None or \
len(reporting_period_data['subtotals']) == 0:
has_subtotals_data_flag = False
if has_subtotals_data_flag:
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 收入占比'
current_row_number += 1
table_start_row_number = current_row_number
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].font = name_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)].fill = table_fill
ws['C' + str(current_row_number)].font = name_font
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = '收入'
ws['D' + str(current_row_number)].fill = table_fill
ws['D' + str(current_row_number)].font = name_font
ws['D' + str(current_row_number)].alignment = c_c_alignment
ws['D' + str(current_row_number)].border = f_border
ws['D' + str(current_row_number)] = '收入占比'
current_row_number += 1
ca_len = len(reporting_period_data['names'])
income_sum = Decimal(0.0)
for i in range(0, ca_len):
income_sum = round(reporting_period_data['subtotals'][i], 2) + income_sum
for i in range(0, ca_len):
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)] = reporting_period_data['names'][i]
ws['B' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)].font = title_font
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = round(reporting_period_data['subtotals'][i], 2)
ws['D' + str(current_row_number)].font = title_font
ws['D' + str(current_row_number)].alignment = c_c_alignment
ws['D' + str(current_row_number)].border = f_border
ws['D' + str(current_row_number)] = '{:.2%}'.format(round(
reporting_period_data['subtotals'][i], 2) / income_sum) \
if income_sum is not None and income_sum != Decimal(0.0) \
else ''
current_row_number += 1
table_end_row_number = current_row_number - 1
pie = PieChart()
pie.title = name + ' 收入占比'
labels = Reference(ws, min_col=2, min_row=table_start_row_number + 1, max_row=table_end_row_number)
pie_data = Reference(ws, min_col=3, min_row=table_start_row_number, max_row=table_end_row_number)
pie.add_data(pie_data, titles_from_data=True)
pie.set_categories(labels)
pie.height = 6.6
pie.width = 9
s1 = pie.series[0]
s1.dLbls = DataLabelList()
s1.dLbls.showCatName = False
s1.dLbls.showVal = True
s1.dLbls.showPercent = True
table_cell = 'F' + str(table_start_row_number - 1)
ws.add_chart(pie, table_cell)
if ca_len < 4:
current_row_number = current_row_number - ca_len + 4
else:
for i in range(13, 22 + 1):
ws.row_dimensions[i].height = 0.1
#############################################
current_row_number = 14
reporting_period_data = report['reporting_period']
times = reporting_period_data['timestamps']
has_detail_data_flag = True
ca_len = len(report['reporting_period']['names'])
real_timestamps_len = timestamps_data_not_equal_0(report['parameters']['timestamps'])
table_row = (current_row_number + 1) + ca_len * 6 + real_timestamps_len * 7
if "timestamps" not in reporting_period_data.keys() or \
reporting_period_data['timestamps'] is None or \
len(reporting_period_data['timestamps']) == 0:
has_detail_data_flag = False
if has_detail_data_flag:
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 详细数据'
ws.row_dimensions[table_row].height = 60
ws['B' + str(table_row)].fill = table_fill
ws['B' + str(table_row)].font = title_font
ws['B' + str(table_row)].border = f_border
ws['B' + str(table_row)].alignment = c_c_alignment
ws['B' + str(table_row)] = '日期时间'
time = times[0]
has_data = False
max_row = 0
if len(time) > 0:
has_data = True
max_row = table_row + len(time)
if has_data:
for i in range(0, len(time)):
col = 'B'
row = str(table_row + 1 + i)
ws[col + row].font = title_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = time[i]
ws[col + row].border = f_border
for i in range(0, ca_len):
col = chr(ord('C') + i)
ws[col + str(table_row)].fill = table_fill
ws[col + str(table_row)].font = title_font
ws[col + str(table_row)].alignment = c_c_alignment
ws[col + str(table_row)] = reporting_period_data['names'][i] + " (" + reporting_period_data['units'][
i] + ")"
ws[col + str(table_row)].border = f_border
# 39 data
time = times[i]
time_len = len(time)
for j in range(0, time_len):
row = str(table_row + 1 + j)
ws[col + row].font = title_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = round(reporting_period_data['values'][i][j], 2)
ws[col + row].border = f_border
line = LineChart()
line.title = \
'报告期收入 - ' + reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
labels = Reference(ws, min_col=2, min_row=table_row + 1, max_row=max_row)
line_data = Reference(ws, min_col=3 + i, min_row=table_row, max_row=max_row)
line.add_data(line_data, titles_from_data=True)
line.set_categories(labels)
line_data = line.series[0]
line_data.marker.symbol = "circle"
line_data.smooth = True
line.x_axis.crosses = 'min'
line.height = 8.25
line.width = 24
line.dLbls = DataLabelList()
line.dLbls.dLblPos = 't'
line.dLbls.showVal = True
line.dLbls.showPercent = False
chart_col = 'B'
chart_cell = chart_col + str(current_row_number + 1 + 6 * i)
ws.add_chart(line, chart_cell)
row = str(max_row + 1)
ws['B' + row].font = title_font
ws['B' + row].alignment = c_c_alignment
ws['B' + row] = '小计'
ws['B' + row].border = f_border
col = ''
for i in range(0, ca_len):
col = chr(ord('C') + i)
row = str(max_row + 1)
ws[col + row].font = title_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = round(reporting_period_data['subtotals'][i], 2)
ws[col + row].border = f_border
col = chr(ord(col) + 1)
ws[col + str(table_row)].fill = table_fill
ws[col + str(table_row)].font = title_font
ws[col + str(table_row)].alignment = c_c_alignment
ws[col + str(table_row)] = '总计 (' + report['reporting_period']['total_unit'] + ')'
ws[col + str(table_row)].border = f_border
total_sum = 0
for j in range(0, len(time)):
row = str(table_row + 1 + j)
ws[col + row].font = title_font
ws[col + row].alignment = c_c_alignment
every_day_sum = reporting_period_values_every_day_sum(reporting_period_data, j, ca_len)
total_sum += every_day_sum
ws[col + row] = round(every_day_sum, 2)
ws[col + row].border = f_border
row = str(table_row + 1 + len(time))
ws[col + row].font = title_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = round(total_sum, 2)
ws[col + row].border = f_border
else:
for i in range(37, 69 + 1):
ws.row_dimensions[i].height = 0.1
##########################################
current_sheet_parameters_row_number = current_row_number + ca_len * 6 + 1
has_parameters_names_and_timestamps_and_values_data = True
if 'parameters' not in report.keys() or \
report['parameters'] is None or \
'names' not in report['parameters'].keys() or \
report['parameters']['names'] is None or \
len(report['parameters']['names']) == 0 or \
'timestamps' not in report['parameters'].keys() or \
report['parameters']['timestamps'] is None or \
len(report['parameters']['timestamps']) == 0 or \
'values' not in report['parameters'].keys() or \
report['parameters']['values'] is None or \
len(report['parameters']['values']) == 0 or \
timestamps_data_all_equal_0(report['parameters']['timestamps']):
has_parameters_names_and_timestamps_and_values_data = False
if has_parameters_names_and_timestamps_and_values_data:
###############################
# new worksheet
###############################
parameters_data = report['parameters']
parameters_names_len = len(parameters_data['names'])
parameters_ws = wb.create_sheet('相关参数')
parameters_timestamps_data_max_len = \
get_parameters_timestamps_lists_max_len(list(parameters_data['timestamps']))
# Row height
parameters_ws.row_dimensions[1].height = 102
for i in range(2, 7 + 1):
parameters_ws.row_dimensions[i].height = 42
for i in range(8, parameters_timestamps_data_max_len + 10):
parameters_ws.row_dimensions[i].height = 60
# Col width
parameters_ws.column_dimensions['A'].width = 1.5
parameters_ws.column_dimensions['B'].width = 25.0
for i in range(3, 12+parameters_names_len*3):
parameters_ws.column_dimensions[format_cell.get_column_letter(i)].width = 15.0
# Img
img = Image("excelexporters/myems.png")
img.width = img.width * 0.85
img.height = img.height * 0.85
# img = Image("myems.png")
parameters_ws.add_image(img, 'B1')
# Title
parameters_ws.row_dimensions[3].height = 60
parameters_ws['B3'].font = name_font
parameters_ws['B3'].alignment = b_r_alignment
parameters_ws['B3'] = 'Name:'
parameters_ws['C3'].border = b_border
parameters_ws['C3'].alignment = b_c_alignment
parameters_ws['C3'].font = name_font
parameters_ws['C3'] = name
parameters_ws['D3'].font = name_font
parameters_ws['D3'].alignment = b_r_alignment
parameters_ws['D3'] = 'Period:'
parameters_ws['E3'].border = b_border
parameters_ws['E3'].alignment = b_c_alignment
parameters_ws['E3'].font = name_font
parameters_ws['E3'] = period_type
parameters_ws['F3'].font = name_font
parameters_ws['F3'].alignment = b_r_alignment
parameters_ws['F3'] = 'Date:'
parameters_ws['G3'].border = b_border
parameters_ws['G3'].alignment = b_c_alignment
parameters_ws['G3'].font = name_font
parameters_ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
parameters_ws.merge_cells("G3:H3")
parameters_ws_current_row_number = 6
parameters_ws['B' + str(parameters_ws_current_row_number)].font = title_font
parameters_ws['B' + str(parameters_ws_current_row_number)] = name + ' 相关参数'
parameters_ws_current_row_number += 1
parameters_table_start_row_number = parameters_ws_current_row_number
parameters_ws.row_dimensions[parameters_ws_current_row_number].height = 80
parameters_ws_current_row_number += 1
table_current_col_number = 2
for i in range(0, parameters_names_len):
if len(parameters_data['timestamps'][i]) == 0:
continue
col = format_cell.get_column_letter(table_current_col_number)
parameters_ws[col + str(parameters_ws_current_row_number-1)].fill = table_fill
parameters_ws[col + str(parameters_ws_current_row_number-1)].border = f_border
col = format_cell.get_column_letter(table_current_col_number + 1)
parameters_ws[col + str(parameters_ws_current_row_number-1)].fill = table_fill
parameters_ws[col + str(parameters_ws_current_row_number-1)].border = f_border
parameters_ws[col + str(parameters_ws_current_row_number-1)].font = name_font
parameters_ws[col + str(parameters_ws_current_row_number-1)].alignment = c_c_alignment
parameters_ws[col + str(parameters_ws_current_row_number-1)] = parameters_data['names'][i]
table_current_row_number = parameters_ws_current_row_number
for j, value in enumerate(list(parameters_data['timestamps'][i])):
col = format_cell.get_column_letter(table_current_col_number)
parameters_ws[col + str(table_current_row_number)].border = f_border
parameters_ws[col + str(table_current_row_number)].font = title_font
parameters_ws[col + str(table_current_row_number)].alignment = c_c_alignment
parameters_ws[col + str(table_current_row_number)] = value
col = format_cell.get_column_letter(table_current_col_number + 1)
parameters_ws[col + str(table_current_row_number)].border = f_border
parameters_ws[col + str(table_current_row_number)].font = title_font
parameters_ws[col + str(table_current_row_number)].alignment = c_c_alignment
parameters_ws[col + str(table_current_row_number)] = round(parameters_data['values'][i][j], 2)
table_current_row_number += 1
table_current_col_number = table_current_col_number + 3
########################################################
# parameters chart and parameters table
########################################################
ws['B' + str(current_sheet_parameters_row_number)].font = title_font
ws['B' + str(current_sheet_parameters_row_number)] = name + ' 相关参数'
current_sheet_parameters_row_number += 1
chart_start_row_number = current_sheet_parameters_row_number
col_index = 0
for i in range(0, parameters_names_len):
if len(parameters_data['timestamps'][i]) == 0:
continue
line = LineChart()
data_col = 3+col_index*3
labels_col = 2+col_index*3
col_index += 1
line.title = '相关参数 - ' + \
parameters_ws.cell(row=parameters_table_start_row_number, column=data_col).value
labels = Reference(parameters_ws, min_col=labels_col, min_row=parameters_table_start_row_number + 1,
max_row=(len(parameters_data['timestamps'][i])+parameters_table_start_row_number))
line_data = Reference(parameters_ws, min_col=data_col, min_row=parameters_table_start_row_number,
max_row=(len(parameters_data['timestamps'][i])+parameters_table_start_row_number))
line.add_data(line_data, titles_from_data=True)
line.set_categories(labels)
line_data = line.series[0]
line_data.marker.symbol = "circle"
line_data.smooth = True
line.x_axis.crosses = 'min'
line.height = 8.25
line.width = 24
line.dLbls = DataLabelList()
line.dLbls.dLblPos = 't'
line.dLbls.showVal = False
line.dLbls.showPercent = False
chart_col = 'B'
chart_cell = chart_col + str(chart_start_row_number)
chart_start_row_number += 6
ws.add_chart(line, chart_cell)
current_sheet_parameters_row_number = chart_start_row_number
current_sheet_parameters_row_number += 1
##########################################
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
def generate_excel(report, name, reporting_start_datetime_local,
reporting_end_datetime_local, period_type):
wb = Workbook()
ws = wb.active
ws.title = "ShopFloorCost"
# Row height
ws.row_dimensions[1].height = 102
for i in range(2, 2000 + 1):
ws.row_dimensions[i].height = 42
# Col width
ws.column_dimensions['A'].width = 1.5
ws.column_dimensions['B'].width = 25.0
for i in range(ord('C'), ord('L')):
ws.column_dimensions[chr(i)].width = 15.0
# Font
name_font = Font(name='Arial', size=15, bold=True)
title_font = Font(name='Arial', size=15, bold=True)
table_fill = PatternFill(fill_type='solid', fgColor='1F497D')
f_border = Border(left=Side(border_style='medium', color='00000000'),
right=Side(border_style='medium', color='00000000'),
bottom=Side(border_style='medium', color='00000000'),
top=Side(border_style='medium', color='00000000'))
b_border = Border(bottom=Side(border_style='medium', color='00000000'), )
b_c_alignment = Alignment(vertical='bottom',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
c_c_alignment = Alignment(vertical='center',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
b_r_alignment = Alignment(vertical='bottom',
horizontal='right',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
# Img
img = Image("excelexporters/myems.png")
ws.add_image(img, 'A1')
# Title
ws['B3'].alignment = b_r_alignment
ws['B3'] = 'Name:'
ws['C3'].border = b_border
ws['C3'].alignment = b_c_alignment
ws['C3'] = name
ws['D3'].alignment = b_r_alignment
ws['D3'] = 'Period:'
ws['E3'].border = b_border
ws['E3'].alignment = b_c_alignment
ws['E3'] = period_type
ws['B4'].alignment = b_r_alignment
ws['B4'] = 'Reporting Start Datetime:'
ws['C4'].border = b_border
ws['C4'].alignment = b_c_alignment
ws['C4'] = reporting_start_datetime_local
ws['D4'].alignment = b_r_alignment
ws['D4'] = 'Reporting End Datetime:'
ws['E4'].border = b_border
ws['E4'].alignment = b_c_alignment
ws['E4'] = reporting_end_datetime_local
if "reporting_period" not in report.keys() or \
"names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0:
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
####################################################################################################################
reporting_period_data = report['reporting_period']
has_energy_data_flag = True
if "names" not in reporting_period_data.keys() or \
reporting_period_data['names'] is None or \
len(reporting_period_data['names']) == 0:
has_energy_data_flag = False
if has_energy_data_flag:
ws['B6'].font = title_font
ws['B6'] = name + ' ' + 'Reporting Period Costs'
category = reporting_period_data['names']
ca_len = len(category)
ws.row_dimensions[7].height = 60
ws['B7'].fill = table_fill
ws['B7'].border = f_border
ws['B8'].font = title_font
ws['B8'].alignment = c_c_alignment
ws['B8'] = 'Cost'
ws['B8'].border = f_border
ws['B9'].font = title_font
ws['B9'].alignment = c_c_alignment
ws['B9'] = 'Per Unit Area'
ws['B9'].border = f_border
ws['B10'].font = title_font
ws['B10'].alignment = c_c_alignment
ws['B10'] = 'Increment Rate'
ws['B10'].border = f_border
col = 'B'
for i in range(0, ca_len):
col = chr(ord('C') + i)
ws[col + '7'].fill = table_fill
ws[col + '7'].font = name_font
ws[col + '7'].alignment = c_c_alignment
ws[col + '7'] = reporting_period_data['names'][
i] + " (" + reporting_period_data['units'][i] + ")"
ws[col + '7'].border = f_border
ws[col + '8'].font = name_font
ws[col + '8'].alignment = c_c_alignment
ws[col + '8'] = round(reporting_period_data['subtotals'][i], 2)
ws[col + '8'].border = f_border
ws[col + '9'].font = name_font
ws[col + '9'].alignment = c_c_alignment
ws[col + '9'] = round(
reporting_period_data['subtotals_per_unit_area'][i], 2)
ws[col + '9'].border = f_border
ws[col + '10'].font = name_font
ws[col + '10'].alignment = c_c_alignment
ws[col + '10'] = str(round(reporting_period_data['increment_rates'][i] * 100, 2)) + "%" \
if reporting_period_data['increment_rates'][i] is not None else "-"
ws[col + '10'].border = f_border
end_col = chr(ord(col) + 1)
ws[end_col + '7'].fill = table_fill
ws[end_col + '7'].font = name_font
ws[end_col + '7'].alignment = c_c_alignment
ws[end_col +
'7'] = "Total (" + reporting_period_data['total_unit'] + ")"
ws[end_col + '7'].border = f_border
ws[end_col + '8'].font = name_font
ws[end_col + '8'].alignment = c_c_alignment
ws[end_col + '8'] = round(reporting_period_data['total'], 2)
ws[end_col + '8'].border = f_border
ws[end_col + '9'].font = name_font
ws[end_col + '9'].alignment = c_c_alignment
ws[end_col + '9'] = round(reporting_period_data['total_per_unit_area'],
2)
ws[end_col + '9'].border = f_border
ws[end_col + '10'].font = name_font
ws[end_col + '10'].alignment = c_c_alignment
ws[end_col + '10'] = str(round(reporting_period_data['total_increment_rate'] * 100, 2)) + "%" \
if reporting_period_data['total_increment_rate'] is not None else "-"
ws[end_col + '10'].border = f_border
else:
for i in range(6, 10 + 1):
ws.row_dimensions[i].height = 0.1
####################################################################################################################
has_ele_peak_flag = True
if "toppeaks" not in reporting_period_data.keys() or \
reporting_period_data['toppeaks'] is None or \
len(reporting_period_data['toppeaks']) == 0:
has_ele_peak_flag = False
if has_ele_peak_flag:
ws['B12'].font = title_font
ws['B12'] = name + 'Electricity Cost by Time-Of-Use'
ws['B13'].fill = table_fill
ws['B13'].font = name_font
ws['B13'].alignment = c_c_alignment
ws['B13'].border = f_border
ws['C13'].fill = table_fill
ws['C13'].font = name_font
ws['C13'].alignment = c_c_alignment
ws['C13'].border = f_border
ws['C13'] = 'Electricity Cost by Time-Of-Use'
ws['D13'].fill = table_fill
ws['D13'].font = name_font
ws['D13'].alignment = c_c_alignment
ws['D13'].border = f_border
ws['D13'] = 'Electricity Cost Proportion by Time-Of-Use'
wssum = round(reporting_period_data['toppeaks'][0], 2) + round(
reporting_period_data['onpeaks'][0], 2) + round(
reporting_period_data['midpeaks'][0], 2) + round(
reporting_period_data['offpeaks'][0], 2)
ws['B14'].font = title_font
ws['B14'].alignment = c_c_alignment
ws['B14'] = 'TopPeak'
ws['B14'].border = f_border
ws['C14'].font = title_font
ws['C14'].alignment = c_c_alignment
ws['C14'].border = f_border
ws['C14'] = round(reporting_period_data['toppeaks'][0], 2)
ws['D14'].font = title_font
ws['D14'].alignment = c_c_alignment
ws['D14'].border = f_border
ws['D14'] = '{:.2%}'.format(round(reporting_period_data['toppeaks'][0], 2) / wssum) \
if wssum is not None and wssum != 0 else "0.00%"
ws['B15'].font = title_font
ws['B15'].alignment = c_c_alignment
ws['B15'] = 'OnPeak'
ws['B15'].border = f_border
ws['C15'].font = title_font
ws['C15'].alignment = c_c_alignment
ws['C15'].border = f_border
ws['C15'] = round(reporting_period_data['onpeaks'][0], 2)
ws['D15'].font = title_font
ws['D15'].alignment = c_c_alignment
ws['D15'].border = f_border
ws['D15'] = '{:.2%}'.format(round(reporting_period_data['onpeaks'][0], 2) / wssum) \
if wssum is not None and wssum != 0 else "0.00%"
ws['B16'].font = title_font
ws['B16'].alignment = c_c_alignment
ws['B16'] = 'MidPeak'
ws['B16'].border = f_border
ws['C16'].font = title_font
ws['C16'].alignment = c_c_alignment
ws['C16'].border = f_border
ws['C16'] = round(reporting_period_data['midpeaks'][0], 2)
ws['D16'].font = title_font
ws['D16'].alignment = c_c_alignment
ws['D16'].border = f_border
ws['D16'] = '{:.2%}'.format(round(reporting_period_data['midpeaks'][0], 2) / wssum) \
if wssum is not None and wssum != 0 else "0.00%"
ws['B17'].font = title_font
ws['B17'].alignment = c_c_alignment
ws['B17'] = 'OffPeak'
ws['B17'].border = f_border
ws['C17'].font = title_font
ws['C17'].alignment = c_c_alignment
ws['C17'].border = f_border
ws['C17'] = round(reporting_period_data['offpeaks'][0], 2)
ws['D17'].font = title_font
ws['D17'].alignment = c_c_alignment
ws['D17'].border = f_border
ws['D17'] = '{:.2%}'.format(round(reporting_period_data['offpeaks'][0], 2) / wssum) \
if wssum is not None and wssum != 0 else "0.00%"
pie = PieChart()
pie.title = name + 'Electricity Cost by Time-Of-Use'
labels = Reference(ws, min_col=2, min_row=14, max_row=17)
pie_data = Reference(ws, min_col=3, min_row=13, max_row=17)
pie.add_data(pie_data, titles_from_data=True)
pie.set_categories(labels)
pie.height = 6.6
pie.width = 9
s1 = pie.series[0]
s1.dLbls = DataLabelList()
s1.dLbls.showCatName = False
s1.dLbls.showVal = True
s1.dLbls.showPercent = True
ws.add_chart(pie, "E13")
else:
for i in range(12, 18 + 1):
ws.row_dimensions[i].height = 0.1
####################################################################################################################
current_row_number = 19
has_subtotals_data_flag = True
if "subtotals" not in reporting_period_data.keys() or \
reporting_period_data['subtotals'] is None or \
len(reporting_period_data['subtotals']) == 0:
has_subtotals_data_flag = False
if has_subtotals_data_flag:
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' ' + 'Costs Proportion'
current_row_number += 1
table_start_row_number = current_row_number
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].font = name_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)].fill = table_fill
ws['C' + str(current_row_number)].font = name_font
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = 'Cost'
ws['D' + str(current_row_number)].fill = table_fill
ws['D' + str(current_row_number)].font = name_font
ws['D' + str(current_row_number)].alignment = c_c_alignment
ws['D' + str(current_row_number)].border = f_border
ws['D' + str(current_row_number)] = 'Costs Proportion'
current_row_number += 1
ca_len = len(reporting_period_data['names'])
wssum = 0
for i in range(0, ca_len):
wssum = round(reporting_period_data['subtotals'][i], 2) + wssum
for i in range(0, ca_len):
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' +
str(current_row_number)] = reporting_period_data['names'][i]
ws['B' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)].font = title_font
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = round(
reporting_period_data['subtotals'][i], 2)
ws['D' + str(current_row_number)].font = title_font
ws['D' + str(current_row_number)].alignment = c_c_alignment
ws['D' + str(current_row_number)].border = f_border
ws['D' + str(current_row_number)] = '{:.2%}'.format(
round(reporting_period_data['subtotals'][i], 2) /
wssum) if wssum is not None and wssum != 0 else "0.00%"
current_row_number += 1
table_end_row_number = current_row_number - 1
pie = PieChart()
pie.title = name + ' ' + 'Costs Proportion'
labels = Reference(ws,
min_col=2,
min_row=table_start_row_number + 1,
max_row=table_end_row_number)
pie_data = Reference(ws,
min_col=3,
min_row=table_start_row_number,
max_row=table_end_row_number)
pie.add_data(pie_data, titles_from_data=True)
pie.set_categories(labels)
pie.height = 6.6
pie.width = 9
s1 = pie.series[0]
s1.dLbls = DataLabelList()
s1.dLbls.showCatName = False
s1.dLbls.showVal = True
s1.dLbls.showPercent = True
table_cell = 'E' + str(table_start_row_number)
ws.add_chart(pie, table_cell)
if ca_len < 4:
current_row_number = current_row_number - ca_len + 4
else:
for i in range(21, 29 + 1):
current_row_number = 30
ws.row_dimensions[i].height = 0.1
####################################################################################################################
current_row_number += 1
has_detail_data_flag = True
table_start_draw_flag = current_row_number + 1
if "timestamps" not in reporting_period_data.keys() or \
reporting_period_data['timestamps'] is None or \
len(reporting_period_data['timestamps']) == 0:
has_detail_data_flag = False
if has_detail_data_flag:
reporting_period_data = report['reporting_period']
times = reporting_period_data['timestamps']
ca_len = len(report['reporting_period']['names'])
parameters_names_len = len(report['parameters']['names'])
parameters_parameters_datas_len = 0
for i in range(0, parameters_names_len):
if len(report['parameters']['timestamps'][i]) == 0:
continue
parameters_parameters_datas_len += 1
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' ' + 'Detailed Data'
detail_data_table_start_row_number = current_row_number + (
ca_len + parameters_parameters_datas_len) * 6 + 3
current_row_number = detail_data_table_start_row_number
time = times[0]
has_data = False
if len(time) > 0:
has_data = True
if has_data:
ws.row_dimensions[current_row_number].height = 60
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)] = 'Datetime'
col = 'B'
for i in range(0, ca_len):
col = chr(ord('C') + i)
ws[col + str(current_row_number)].fill = table_fill
ws[col + str(current_row_number)].font = title_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)] = \
reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
ws[col + str(current_row_number)].border = f_border
end_col = chr(ord(col) + 1)
ws[end_col + str(current_row_number)].fill = table_fill
ws[end_col + str(current_row_number)].font = title_font
ws[end_col + str(current_row_number)].alignment = c_c_alignment
ws[end_col +
str(current_row_number
)] = "Total (" + reporting_period_data['total_unit'] + ")"
ws[end_col + str(current_row_number)].border = f_border
current_row_number += 1
for i in range(0, len(time)):
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)] = time[i]
ws['B' + str(current_row_number)].border = f_border
col = 'B'
periodic_sum = Decimal(0.0)
for j in range(0, ca_len):
col = chr(ord('C') + j)
ws[col + str(current_row_number)].font = title_font
ws[col + str(current_row_number)].alignment = c_c_alignment
value = round(reporting_period_data['values'][j][i], 2)
periodic_sum += value
ws[col + str(current_row_number)] = value
ws[col + str(current_row_number)].border = f_border
end_col = chr(ord(col) + 1)
ws[end_col + str(current_row_number)].font = title_font
ws[end_col + str(current_row_number)].alignment = c_c_alignment
ws[end_col + str(current_row_number)] = round(periodic_sum, 2)
ws[end_col + str(current_row_number)].border = f_border
current_row_number += 1
table_end_row_number = current_row_number - 1
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)] = 'Subtotal'
ws['B' + str(current_row_number)].border = f_border
col = 'B'
for i in range(0, ca_len):
col = chr(ord('C') + i)
ws[col + str(current_row_number)].font = title_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)] = round(
reporting_period_data['subtotals'][i], 2)
ws[col + str(current_row_number)].border = f_border
# line
line = LineChart()
line.title = 'Reporting Period Costs - ' + \
reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
labels = Reference(ws,
min_col=2,
min_row=detail_data_table_start_row_number +
1,
max_row=table_end_row_number)
line_data = Reference(
ws,
min_col=3 + i,
min_row=detail_data_table_start_row_number,
max_row=table_end_row_number)
line.add_data(line_data, titles_from_data=True)
line.set_categories(labels)
line_data = line.series[0]
line_data.marker.symbol = "circle"
line_data.smooth = True
line.x_axis.crosses = 'min'
line.height = 8.25
line.width = 24
line.dLbls = DataLabelList()
line.dLbls.dLblPos = 't'
line.dLbls.showVal = True
line.dLbls.showPercent = False
chart_col = 'B'
chart_cell = chart_col + str(table_start_draw_flag + 6 * i)
ws.add_chart(line, chart_cell)
end_col = chr(ord(col) + 1)
ws[end_col + str(current_row_number)].font = title_font
ws[end_col + str(current_row_number)].alignment = c_c_alignment
ws[end_col + str(current_row_number)] = round(
reporting_period_data['total'], 2)
ws[end_col + str(current_row_number)].border = f_border
current_row_number += 1
else:
for i in range(30, 69 + 1):
current_row_number = 70
ws.row_dimensions[i].height = 0.1
####################################################################################################################
has_parameters_names_and_timestamps_and_values_data = True
ca_len = len(report['reporting_period']['names'])
current_sheet_parameters_row_number = table_start_draw_flag + ca_len * 6
if 'parameters' not in report.keys() or \
report['parameters'] is None or \
'names' not in report['parameters'].keys() or \
report['parameters']['names'] is None or \
len(report['parameters']['names']) == 0 or \
'timestamps' not in report['parameters'].keys() or \
report['parameters']['timestamps'] is None or \
len(report['parameters']['timestamps']) == 0 or \
'values' not in report['parameters'].keys() or \
report['parameters']['values'] is None or \
len(report['parameters']['values']) == 0 or \
timestamps_data_all_equal_0(report['parameters']['timestamps']):
has_parameters_names_and_timestamps_and_values_data = False
if has_parameters_names_and_timestamps_and_values_data:
################################################################################################################
# new worksheet
################################################################################################################
parameters_data = report['parameters']
parameters_names_len = len(parameters_data['names'])
file_name = (re.sub(r'[^A-Z]', '', ws.title)) + '_'
parameters_ws = wb.create_sheet(file_name + 'Parameters')
parameters_timestamps_data_max_len = \
get_parameters_timestamps_lists_max_len(list(parameters_data['timestamps']))
# Row height
parameters_ws.row_dimensions[1].height = 102
for i in range(2, 7 + 1):
parameters_ws.row_dimensions[i].height = 42
for i in range(8, parameters_timestamps_data_max_len + 10):
parameters_ws.row_dimensions[i].height = 60
# Col width
parameters_ws.column_dimensions['A'].width = 1.5
parameters_ws.column_dimensions['B'].width = 25.0
for i in range(3, 12 + parameters_names_len * 3):
parameters_ws.column_dimensions[format_cell.get_column_letter(
i)].width = 15.0
# Img
img = Image("excelexporters/myems.png")
parameters_ws.add_image(img, 'A1')
# Title
parameters_ws['B3'].alignment = b_r_alignment
parameters_ws['B3'] = 'Name:'
parameters_ws['C3'].border = b_border
parameters_ws['C3'].alignment = b_c_alignment
parameters_ws['C3'] = name
parameters_ws['D3'].alignment = b_r_alignment
parameters_ws['D3'] = 'Period:'
parameters_ws['E3'].border = b_border
parameters_ws['E3'].alignment = b_c_alignment
parameters_ws['E3'] = period_type
parameters_ws['B4'].alignment = b_r_alignment
parameters_ws['B4'] = 'Reporting Start Datetime:'
parameters_ws['C4'].border = b_border
parameters_ws['C4'].alignment = b_c_alignment
parameters_ws['C4'] = reporting_start_datetime_local
parameters_ws['D4'].alignment = b_r_alignment
parameters_ws['D4'] = 'Reporting End Datetime:'
parameters_ws['E4'].border = b_border
parameters_ws['E4'].alignment = b_c_alignment
parameters_ws['E4'] = reporting_end_datetime_local
parameters_ws_current_row_number = 6
parameters_ws['B' +
str(parameters_ws_current_row_number)].font = title_font
parameters_ws[
'B' +
str(parameters_ws_current_row_number)] = name + ' ' + 'Parameters'
parameters_ws_current_row_number += 1
parameters_table_start_row_number = parameters_ws_current_row_number
parameters_ws.row_dimensions[
parameters_ws_current_row_number].height = 80
parameters_ws_current_row_number += 1
table_current_col_number = 2
for i in range(0, parameters_names_len):
if len(parameters_data['timestamps'][i]) == 0:
continue
col = format_cell.get_column_letter(table_current_col_number)
parameters_ws[col + str(parameters_ws_current_row_number -
1)].fill = table_fill
parameters_ws[col + str(parameters_ws_current_row_number -
1)].border = f_border
col = format_cell.get_column_letter(table_current_col_number + 1)
parameters_ws[col + str(parameters_ws_current_row_number -
1)].fill = table_fill
parameters_ws[col + str(parameters_ws_current_row_number -
1)].border = f_border
parameters_ws[col + str(parameters_ws_current_row_number -
1)].font = name_font
parameters_ws[col + str(parameters_ws_current_row_number -
1)].alignment = c_c_alignment
parameters_ws[col + str(parameters_ws_current_row_number -
1)] = parameters_data['names'][i]
table_current_row_number = parameters_ws_current_row_number
for j, value in enumerate(list(parameters_data['timestamps'][i])):
col = format_cell.get_column_letter(table_current_col_number)
parameters_ws[col +
str(table_current_row_number)].border = f_border
parameters_ws[col +
str(table_current_row_number)].font = title_font
parameters_ws[
col +
str(table_current_row_number)].alignment = c_c_alignment
parameters_ws[col + str(table_current_row_number)] = value
col = format_cell.get_column_letter(table_current_col_number +
1)
parameters_ws[col +
str(table_current_row_number)].border = f_border
parameters_ws[col +
str(table_current_row_number)].font = title_font
parameters_ws[
col +
str(table_current_row_number)].alignment = c_c_alignment
parameters_ws[col + str(table_current_row_number)] = round(
parameters_data['values'][i][j], 2)
table_current_row_number += 1
table_current_col_number = table_current_col_number + 3
################################################################################################################
# parameters chart and parameters table
################################################################################################################
ws['B' + str(current_sheet_parameters_row_number)].font = title_font
ws['B' + str(
current_sheet_parameters_row_number)] = name + ' ' + 'Parameters'
current_sheet_parameters_row_number += 1
chart_start_row_number = current_sheet_parameters_row_number
col_index = 0
for i in range(0, parameters_names_len):
if len(parameters_data['timestamps'][i]) == 0:
continue
line = LineChart()
data_col = 3 + col_index * 3
labels_col = 2 + col_index * 3
col_index += 1
line.title = 'Parameters - ' + \
parameters_ws.cell(row=parameters_table_start_row_number, column=data_col).value
labels = Reference(parameters_ws,
min_col=labels_col,
min_row=parameters_table_start_row_number + 1,
max_row=(len(parameters_data['timestamps'][i]) +
parameters_table_start_row_number))
line_data = Reference(
parameters_ws,
min_col=data_col,
min_row=parameters_table_start_row_number,
max_row=(len(parameters_data['timestamps'][i]) +
parameters_table_start_row_number))
line.add_data(line_data, titles_from_data=True)
line.set_categories(labels)
line_data = line.series[0]
line_data.marker.symbol = "circle"
line_data.smooth = True
line.x_axis.crosses = 'min'
line.height = 8.25
line.width = 24
line.dLbls = DataLabelList()
line.dLbls.dLblPos = 't'
line.dLbls.showVal = False
line.dLbls.showPercent = False
chart_col = 'B'
chart_cell = chart_col + str(chart_start_row_number)
chart_start_row_number += 6
ws.add_chart(line, chart_cell)
current_sheet_parameters_row_number = chart_start_row_number
current_sheet_parameters_row_number += 1
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
def generate_excel(report, name, reporting_start_datetime_local,
reporting_end_datetime_local, period_type):
wb = Workbook()
ws = wb.active
# Row height
ws.row_dimensions[1].height = 102
for i in range(2, 2000 + 1):
ws.row_dimensions[i].height = 42
# Col width
ws.column_dimensions['A'].width = 1.5
ws.column_dimensions['B'].width = 25.0
for i in range(ord('C'), ord('L')):
ws.column_dimensions[chr(i)].width = 15.0
# Font
name_font = Font(name='Constantia', size=15, bold=True)
title_font = Font(name='宋体', size=15, bold=True)
data_font = Font(name='Franklin Gothic Book', size=11)
table_fill = PatternFill(fill_type='solid', fgColor='1F497D')
f_border = Border(left=Side(border_style='medium', color='00000000'),
right=Side(border_style='medium', color='00000000'),
bottom=Side(border_style='medium', color='00000000'),
top=Side(border_style='medium', color='00000000'))
b_border = Border(bottom=Side(border_style='medium', color='00000000'), )
b_c_alignment = Alignment(vertical='bottom',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
c_c_alignment = Alignment(vertical='center',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
b_r_alignment = Alignment(vertical='bottom',
horizontal='right',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
c_r_alignment = Alignment(vertical='bottom',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
# Img
img = Image("excelexporters/myems.png")
img.width = img.width * 0.85
img.height = img.height * 0.85
# img = Image("myems.png")
ws.add_image(img, 'B1')
# Title
ws.row_dimensions[3].height = 60
ws['B3'].font = name_font
ws['B3'].alignment = b_r_alignment
ws['B3'] = 'Name:'
ws['C3'].border = b_border
ws['C3'].alignment = b_c_alignment
ws['C3'].font = name_font
ws['C3'] = name
ws['D3'].font = name_font
ws['D3'].alignment = b_r_alignment
ws['D3'] = 'Period:'
ws['E3'].border = b_border
ws['E3'].alignment = b_c_alignment
ws['E3'].font = name_font
ws['E3'] = period_type
ws['F3'].font = name_font
ws['F3'].alignment = b_r_alignment
ws['F3'] = 'Date:'
ws['G3'].border = b_border
ws['G3'].alignment = b_c_alignment
ws['G3'].font = name_font
ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
ws.merge_cells("G3:H3")
if "reporting_period" not in report.keys() or \
"names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0:
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
####################################################################################################################
current_row_number = 6
reporting_period_data = report['reporting_period']
has_names_data_flag = True
if "names" not in reporting_period_data.keys() or \
reporting_period_data['names'] is None or \
len(reporting_period_data['names']) == 0:
has_names_data_flag = False
if has_names_data_flag:
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 报告期产出'
current_row_number += 1
category = reporting_period_data['names']
ca_len = len(category)
ws.row_dimensions[current_row_number].height = 60
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].border = f_border
col = 'C'
for i in range(0, ca_len):
ws[col + str(current_row_number)].fill = table_fill
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = \
reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
col = chr(ord(col) + 1)
current_row_number += 1
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '产出'
col = 'C'
for i in range(0, ca_len):
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = round(
reporting_period_data['subtotals'][i], 2)
col = chr(ord(col) + 1)
current_row_number += 1
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '环比'
col = 'C'
for i in range(0, ca_len):
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = str(
round(reporting_period_data['increment_rates'][i] * 100, 2)) + '%' \
if reporting_period_data['increment_rates'][i] is not None else '-'
col = chr(ord(col) + 1)
current_row_number += 2
####################################################################################################################
has_values_data = True
has_timestamps_data = True
if 'values' not in reporting_period_data.keys() or \
reporting_period_data['values'] is None or \
len(reporting_period_data['values']) == 0:
has_values_data = False
if 'timestamps' not in reporting_period_data.keys() or \
reporting_period_data['timestamps'] is None or \
len(reporting_period_data['timestamps']) == 0 or \
len(reporting_period_data['timestamps'][0]) == 0:
has_timestamps_data = False
if has_values_data and has_timestamps_data:
ca_len = len(reporting_period_data['names'])
time = reporting_period_data['timestamps'][0]
real_timestamps_len = timestamps_data_not_equal_0(
report['parameters']['timestamps'])
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 详细数据'
current_row_number += 1
chart_start_row_number = current_row_number
current_row_number += ca_len * 6 + real_timestamps_len * 7 + 1
table_start_row_number = current_row_number
ws.row_dimensions[current_row_number].height = 60
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '日期时间'
col = 'C'
for i in range(0, ca_len):
ws[col + str(current_row_number)].fill = table_fill
ws[col + str(current_row_number)].font = title_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = \
reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
col = chr(ord(col) + 1)
current_row_number += 1
for i in range(0, len(time)):
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = time[i]
col = 'C'
for j in range(0, ca_len):
ws[col + str(current_row_number)].font = title_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = round(reporting_period_data['values'][j][i], 2) \
if reporting_period_data['values'][j][i] is not None else 0.00
col = chr(ord(col) + 1)
current_row_number += 1
table_end_row_number = current_row_number - 1
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '小计'
col = 'C'
for i in range(0, ca_len):
ws[col + str(current_row_number)].font = title_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = round(
reporting_period_data['subtotals'][i], 2)
col = chr(ord(col) + 1)
current_row_number += 2
format_time_width_number = 1.0
min_len_number = 1.0
min_width_number = 11.0 # format_time_width_number * min_len_number + 4 and min_width_number > 11.0
if period_type == 'hourly':
format_time_width_number = 4.0
min_len_number = 2
min_width_number = 12.0
elif period_type == 'daily':
format_time_width_number = 2.5
min_len_number = 4
min_width_number = 14.0
elif period_type == 'monthly':
format_time_width_number = 2.1
min_len_number = 4
min_width_number = 12.4
elif period_type == 'yearly':
format_time_width_number = 1.5
min_len_number = 5
min_width_number = 11.5
for i in range(0, ca_len):
line = LineChart()
line.title = '报告期产出 - ' + \
reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
labels = Reference(ws,
min_col=2,
min_row=table_start_row_number + 1,
max_row=table_end_row_number)
line_data = Reference(ws,
min_col=3 + i,
min_row=table_start_row_number,
max_row=table_end_row_number)
line.add_data(line_data, titles_from_data=True)
line.set_categories(labels)
line_data = line.series[0]
line_data.marker.symbol = "circle"
line_data.smooth = True
line.x_axis.crosses = 'min'
line.height = 8.25
line.width = format_time_width_number * len(time) if len(
time) > min_len_number else min_width_number
if line.width > 24:
line.width = 24
line.dLbls = DataLabelList()
line.dLbls.dLblPos = 't'
line.dLbls.showVal = True
line.dLbls.showPercent = False
chart_col = 'B'
chart_cell = chart_col + str(chart_start_row_number)
chart_start_row_number += 6
ws.add_chart(line, chart_cell)
#####################################
has_associated_equipment_flag = True
current_row_number += 2
if "associated_equipment" not in report.keys() or \
"energy_category_names" not in report['associated_equipment'].keys() or \
len(report['associated_equipment']["energy_category_names"]) == 0 \
or 'associated_equipment_names_array' not in report['associated_equipment'].keys() \
or report['associated_equipment']['associated_equipment_names_array'] is None \
or len(report['associated_equipment']['associated_equipment_names_array']) == 0 \
or len(report['associated_equipment']['associated_equipment_names_array'][0]) == 0:
has_associated_equipment_flag = False
if has_associated_equipment_flag:
associated_equipment = report['associated_equipment']
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 相关设备数据'
current_row_number += 1
ws.row_dimensions[current_row_number].height = 60
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].font = name_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '相关设备'
ca_len = len(associated_equipment['energy_category_names'])
for i in range(0, ca_len):
row = chr(ord('C') + i)
ws[row + str(current_row_number)].fill = table_fill
ws[row + str(current_row_number)].font = name_font
ws[row + str(current_row_number)].alignment = c_c_alignment
ws[row + str(current_row_number)].border = f_border
ws[row + str(current_row_number)] = \
reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
associated_equipment_len = len(
associated_equipment['associated_equipment_names_array'][0])
for i in range(0, associated_equipment_len):
current_row_number += 1
row = str(current_row_number)
ws['B' + row].font = title_font
ws['B' + row].alignment = c_c_alignment
ws['B' + row] = associated_equipment[
'associated_equipment_names_array'][0][i]
ws['B' + row].border = f_border
for j in range(0, ca_len):
col = chr(ord('C') + j)
ws[col + row].font = title_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = round(
associated_equipment['subtotals_array'][j][i], 2)
ws[col + row].border = f_border
####################################################################################################################
current_sheet_parameters_row_number = chart_start_row_number + 1
has_parameters_names_and_timestamps_and_values_data = True
if 'parameters' not in report.keys() or \
report['parameters'] is None or \
'names' not in report['parameters'].keys() or \
report['parameters']['names'] is None or \
len(report['parameters']['names']) == 0 or \
'timestamps' not in report['parameters'].keys() or \
report['parameters']['timestamps'] is None or \
len(report['parameters']['timestamps']) == 0 or \
'values' not in report['parameters'].keys() or \
report['parameters']['values'] is None or \
len(report['parameters']['values']) == 0 or \
timestamps_data_all_equal_0(report['parameters']['timestamps']):
has_parameters_names_and_timestamps_and_values_data = False
if has_parameters_names_and_timestamps_and_values_data:
###############################
# new worksheet
###############################
parameters_data = report['parameters']
parameters_names_len = len(parameters_data['names'])
parameters_ws = wb.create_sheet('相关参数')
parameters_timestamps_data_max_len = \
get_parameters_timestamps_lists_max_len(list(parameters_data['timestamps']))
# Row height
parameters_ws.row_dimensions[1].height = 102
for i in range(2, 7 + 1):
parameters_ws.row_dimensions[i].height = 42
for i in range(8, parameters_timestamps_data_max_len + 10):
parameters_ws.row_dimensions[i].height = 60
# Col width
parameters_ws.column_dimensions['A'].width = 1.5
parameters_ws.column_dimensions['B'].width = 25.0
for i in range(3, 12 + parameters_names_len * 3):
parameters_ws.column_dimensions[format_cell.get_column_letter(
i)].width = 15.0
# Img
img = Image("excelexporters/myems.png")
img.width = img.width * 0.85
img.height = img.height * 0.85
# img = Image("myems.png")
parameters_ws.add_image(img, 'B1')
# Title
parameters_ws.row_dimensions[3].height = 60
parameters_ws['B3'].font = name_font
parameters_ws['B3'].alignment = b_r_alignment
parameters_ws['B3'] = 'Name:'
parameters_ws['C3'].border = b_border
parameters_ws['C3'].alignment = b_c_alignment
parameters_ws['C3'].font = name_font
parameters_ws['C3'] = name
parameters_ws['D3'].font = name_font
parameters_ws['D3'].alignment = b_r_alignment
parameters_ws['D3'] = 'Period:'
parameters_ws['E3'].border = b_border
parameters_ws['E3'].alignment = b_c_alignment
parameters_ws['E3'].font = name_font
parameters_ws['E3'] = period_type
parameters_ws['F3'].font = name_font
parameters_ws['F3'].alignment = b_r_alignment
parameters_ws['F3'] = 'Date:'
parameters_ws['G3'].border = b_border
parameters_ws['G3'].alignment = b_c_alignment
parameters_ws['G3'].font = name_font
parameters_ws[
'G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
parameters_ws.merge_cells("G3:H3")
parameters_ws_current_row_number = 6
parameters_ws['B' +
str(parameters_ws_current_row_number)].font = title_font
parameters_ws['B' +
str(parameters_ws_current_row_number)] = name + ' 相关参数'
parameters_ws_current_row_number += 1
parameters_table_start_row_number = parameters_ws_current_row_number
parameters_ws.row_dimensions[
parameters_ws_current_row_number].height = 80
parameters_ws_current_row_number += 1
table_current_col_number = 'B'
for i in range(0, parameters_names_len):
if len(parameters_data['timestamps'][i]) == 0:
continue
parameters_ws[table_current_col_number +
str(parameters_ws_current_row_number -
1)].fill = table_fill
parameters_ws[table_current_col_number +
str(parameters_ws_current_row_number -
1)].border = f_border
col = decimal_to_column(
column_to_decimal(table_current_col_number) + 1)
parameters_ws[col + str(parameters_ws_current_row_number -
1)].fill = table_fill
parameters_ws[col + str(parameters_ws_current_row_number -
1)].border = f_border
parameters_ws[col + str(parameters_ws_current_row_number -
1)].font = name_font
parameters_ws[col + str(parameters_ws_current_row_number -
1)].alignment = c_c_alignment
parameters_ws[col + str(parameters_ws_current_row_number -
1)] = parameters_data['names'][i]
table_current_row_number = parameters_ws_current_row_number
for j, value in enumerate(list(parameters_data['timestamps'][i])):
col = table_current_col_number
parameters_ws[col +
str(table_current_row_number)].border = f_border
parameters_ws[col +
str(table_current_row_number)].font = title_font
parameters_ws[
col +
str(table_current_row_number)].alignment = c_c_alignment
parameters_ws[col + str(table_current_row_number)] = value
col = decimal_to_column(column_to_decimal(col) + 1)
parameters_ws[col +
str(table_current_row_number)].border = f_border
parameters_ws[col +
str(table_current_row_number)].font = title_font
parameters_ws[
col +
str(table_current_row_number)].alignment = c_c_alignment
parameters_ws[col + str(table_current_row_number)] = round(
parameters_data['values'][i][j], 2)
table_current_row_number += 1
table_current_col_number = decimal_to_column(
column_to_decimal(table_current_col_number) + 3)
########################################################
# parameters chart and parameters table
########################################################
ws['B' + str(current_sheet_parameters_row_number)].font = title_font
ws['B' + str(current_sheet_parameters_row_number)] = name + ' 相关参数'
current_sheet_parameters_row_number += 1
chart_start_row_number = current_sheet_parameters_row_number
col_index = 0
for i in range(0, parameters_names_len):
if len(parameters_data['timestamps'][i]) == 0:
continue
line = LineChart()
data_col = 3 + col_index * 3
labels_col = 2 + col_index * 3
col_index += 1
line.title = '相关参数 - ' + \
parameters_ws.cell(row=parameters_table_start_row_number, column=data_col).value
labels = Reference(parameters_ws,
min_col=labels_col,
min_row=parameters_table_start_row_number + 1,
max_row=(len(parameters_data['timestamps'][i]) +
parameters_table_start_row_number))
line_data = Reference(
parameters_ws,
min_col=data_col,
min_row=parameters_table_start_row_number,
max_row=(len(parameters_data['timestamps'][i]) +
parameters_table_start_row_number))
line.add_data(line_data, titles_from_data=True)
line.set_categories(labels)
line_data = line.series[0]
line_data.marker.symbol = "circle"
line_data.smooth = True
line.x_axis.crosses = 'min'
line.height = 8.25
line.width = 24
line.dLbls = DataLabelList()
line.dLbls.dLblPos = 't'
line.dLbls.showVal = False
line.dLbls.showPercent = False
chart_col = 'B'
chart_cell = chart_col + str(chart_start_row_number)
chart_start_row_number += 6
ws.add_chart(line, chart_cell)
current_sheet_parameters_row_number = chart_start_row_number
current_sheet_parameters_row_number += 1
##########################################
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
sheet.cell(row=8, column=k3).value = days[num]
sheet.cell(row=8, column=k3).alignment = align
sheet.cell(row=8, column=k3).font = Font(u'微软雅黑', size=11)
# 设置列宽12
c_char = get_column_letter(k3)
sheet.column_dimensions[get_column_letter(k3)].width = 12
num += 1
# 设置行高30
for k4 in range(8, 14):
sheet.row_dimensions[k4].height = 30
# 最后添加图片并设置文本属性
# 合并单元格
sheet.merge_cells('I1:P20')
# 添加图片
img = Image('PR2.jpg')
sheet.add_image(img, 'I1')
# 添加年份及月份
sheet.cell(row=3, column=1).value = '2019年'
sheet.cell(row=4, column=1).value = str(i) + '月'
# 设置年份及月份文本属性
sheet.cell(row=3, column=1).font = Font(u'微软雅黑',
size=16,
bold=True,
color='FF7887')
sheet.cell(row=4, column=1).font = Font(u'微软雅黑',
size=16,
bold=True,
color='FF7887')
def generate_excel(report, name, reporting_start_datetime_local,
reporting_end_datetime_local, period_type):
wb = Workbook()
ws = wb.active
# Row height
ws.row_dimensions[1].height = 121
for i in range(2, 37 + 1):
ws.row_dimensions[i].height = 30
for i in range(38, 90 + 1):
ws.row_dimensions[i].height = 30
# Col width
ws.column_dimensions['A'].width = 1.5
ws.column_dimensions['B'].width = 20.0
for i in range(ord('C'), ord('I')):
ws.column_dimensions[chr(i)].width = 15.0
# Font
name_font = Font(name='Constantia', size=15, bold=True)
title_font = Font(name='宋体', size=15, bold=True)
# data_font = Font(name='Franklin Gothic Book', size=11)
table_fill = PatternFill(fill_type='solid', fgColor='1F497D')
f_border = Border(left=Side(border_style='medium', color='00000000'),
right=Side(border_style='medium', color='00000000'),
bottom=Side(border_style='medium', color='00000000'),
top=Side(border_style='medium', color='00000000'))
b_border = Border(bottom=Side(border_style='medium', color='00000000'), )
b_c_alignment = Alignment(vertical='bottom',
horizontal='center',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
c_c_alignment = Alignment(vertical='center',
horizontal='center',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
b_r_alignment = Alignment(vertical='bottom',
horizontal='right',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
# c_r_alignment = Alignment(vertical='bottom',
# horizontal='center',
# text_rotation=0,
# wrap_text=False,
# shrink_to_fit=False,
# indent=0)
# Img
img = Image("excelexporters/myems.png")
# img = Image("myems.png")
ws.add_image(img, 'B1')
# Title
ws['B3'].font = name_font
ws['B3'].alignment = b_r_alignment
ws['B3'] = 'Name:'
ws['C3'].border = b_border
ws['C3'].alignment = b_c_alignment
ws['C3'].font = name_font
ws['C3'] = name
ws['D3'].font = name_font
ws['D3'].alignment = b_r_alignment
ws['D3'] = 'Period:'
ws['E3'].border = b_border
ws['E3'].alignment = b_c_alignment
ws['E3'].font = name_font
ws['E3'] = period_type
ws['F3'].font = name_font
ws['F3'].alignment = b_r_alignment
ws['F3'] = 'Date:'
ws['G3'].border = b_border
ws['G3'].alignment = b_c_alignment
ws['G3'].font = name_font
ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
ws.merge_cells("G3:H3")
if "reporting_period" not in report.keys() or \
"names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0:
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
#################################################
# First: 统计分析
# 6: title
# 7: table title
# 8~ca_len table_data
#################################################
reporting_period_data = report['reporting_period']
has_energy_data_flag = True
if "names" not in reporting_period_data.keys() or \
reporting_period_data['names'] is None or \
len(reporting_period_data['names']) == 0:
has_energy_data_flag = False
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
if has_energy_data_flag:
ws['B6'].font = title_font
ws['B6'] = name + ' 统计分析'
category = reporting_period_data['names']
# table_title
ws['B7'].fill = table_fill
ws['B7'].font = title_font
ws['B7'].alignment = c_c_alignment
ws['B7'] = '报告期'
ws['B7'].border = f_border
ws['C7'].font = title_font
ws['C7'].alignment = c_c_alignment
ws['C7'] = '算术平均数'
ws['C7'].border = f_border
ws['D7'].font = title_font
ws['D7'].alignment = c_c_alignment
ws['D7'] = '中位数'
ws['D7'].border = f_border
ws['E7'].font = title_font
ws['E7'].alignment = c_c_alignment
ws['E7'] = '最小值'
ws['E7'].border = f_border
ws['F7'].font = title_font
ws['F7'].alignment = c_c_alignment
ws['F7'] = '最大值'
ws['F7'].border = f_border
ws['G7'].font = title_font
ws['G7'].alignment = c_c_alignment
ws['G7'] = '样本标准差'
ws['G7'].border = f_border
ws['H7'].font = title_font
ws['H7'].alignment = c_c_alignment
ws['H7'] = '样本方差'
ws['H7'].border = f_border
# table_data
for i, value in enumerate(category):
row = i * 2 + 8
ws['B' + str(row)].font = name_font
ws['B' + str(row)].alignment = c_c_alignment
ws['B' + str(row)] = reporting_period_data['names'][
i] + " (" + reporting_period_data['units'][i] + " )"
ws['B' + str(row)].border = f_border
ws['B' + str(row + 1)].font = name_font
ws['B' + str(row + 1)].alignment = c_c_alignment
ws['B' + str(row + 1)] = "环比"
ws['B' + str(row + 1)].border = f_border
ws['C' + str(row)].font = name_font
ws['C' + str(row)].alignment = c_c_alignment
ws['C' + str(row)] = round(reporting_period_data['means'][i], 2) \
if reporting_period_data['means'][i] is not None else ''
ws['C' + str(row)].border = f_border
ws['C' + str(row)].number_format = '0.00'
ws['C' + str(row + 1)].font = name_font
ws['C' + str(row + 1)].alignment = c_c_alignment
ws['C' + str(row + 1)] = str(round(reporting_period_data['means_increment_rate'][i] * 100, 2)) + "%" \
if reporting_period_data['means_increment_rate'][i] is not None else '0.00%'
ws['C' + str(row + 1)].border = f_border
ws['D' + str(row)].font = name_font
ws['D' + str(row)].alignment = c_c_alignment
ws['D' + str(row)] = round(reporting_period_data['medians'][i], 2) \
if reporting_period_data['medians'][i] is not None else ''
ws['D' + str(row)].border = f_border
ws['D' + str(row)].number_format = '0.00'
ws['D' + str(row + 1)].font = name_font
ws['D' + str(row + 1)].alignment = c_c_alignment
ws['D' + str(row + 1)] = str(round(reporting_period_data['medians_increment_rate'][i] * 100, 2)) + "%" \
if reporting_period_data['medians_increment_rate'][i] is not None else '0.00%'
ws['D' + str(row + 1)].border = f_border
ws['E' + str(row)].font = name_font
ws['E' + str(row)].alignment = c_c_alignment
ws['E' + str(row)] = round(reporting_period_data['minimums'][i], 2) \
if reporting_period_data['minimums'][i] is not None else ''
ws['E' + str(row)].border = f_border
ws['E' + str(row)].number_format = '0.00'
ws['E' + str(row + 1)].font = name_font
ws['E' + str(row + 1)].alignment = c_c_alignment
ws['E' + str(row + 1)] = str(round(reporting_period_data['minimums_increment_rate'][i] * 100, 2)) + "%" \
if reporting_period_data['minimums_increment_rate'][i] is not None else '0.00%'
ws['E' + str(row + 1)].border = f_border
ws['F' + str(row)].font = name_font
ws['F' + str(row)].alignment = c_c_alignment
ws['F' + str(row)] = round(reporting_period_data['maximums'][i], 2) \
if reporting_period_data['maximums'][i] is not None else ''
ws['F' + str(row)].border = f_border
ws['F' + str(row)].number_format = '0.00'
ws['F' + str(row + 1)].font = name_font
ws['F' + str(row + 1)].alignment = c_c_alignment
ws['F' + str(row + 1)] = str(round(reporting_period_data['maximums_increment_rate'][i] * 100, 2)) + "%" \
if reporting_period_data['maximums_increment_rate'][i] is not None else '0.00%'
ws['F' + str(row + 1)].border = f_border
ws['G' + str(row)].font = name_font
ws['G' + str(row)].alignment = c_c_alignment
ws['G' + str(row)] = round(reporting_period_data['stdevs'][i], 2) \
if reporting_period_data['stdevs'][i] is not None else ''
ws['G' + str(row)].border = f_border
ws['G' + str(row)].number_format = '0.00'
ws['G' + str(row + 1)].font = name_font
ws['G' + str(row + 1)].alignment = c_c_alignment
ws['G' + str(row + 1)] = str(round(reporting_period_data['stdevs_increment_rate'][i] * 100, 2)) + "%" \
if reporting_period_data['stdevs_increment_rate'][i] is not None else '0.00%'
ws['G' + str(row + 1)].border = f_border
ws['H' + str(row)].font = name_font
ws['H' + str(row)].alignment = c_c_alignment
ws['H' + str(row)] = round(reporting_period_data['variances'][i], 2) \
if reporting_period_data['variances'][i] is not None else ''
ws['H' + str(row)].border = f_border
ws['H' + str(row)].number_format = '0.00'
ws['H' + str(row + 1)].font = name_font
ws['H' + str(row + 1)].alignment = c_c_alignment
ws['H' + str(row + 1)] = str(round(reporting_period_data['variances_increment_rate'][i] * 100, 2)) + "%" \
if reporting_period_data['variances_increment_rate'][i] is not None else '0.00%'
ws['H' + str(row + 1)].border = f_border
#################################################
# Second: 报告期消耗
# 9 + ca_len * 2: title
# 10 + ca_len * 2: table title
# row_title + 2 ~ row_title + 2 + ca_len : table_data
#################################################
if has_energy_data_flag:
names = reporting_period_data['names']
ca_len = len(names)
row_title = 9 + ca_len * 2
ws['B' + str(row_title)].font = title_font
ws['B' + str(row_title)] = name + ' 单位面积值'
ws['D' + str(row_title)].font = title_font
ws['D' + str(row_title)] = str(report['store']['area']) + 'M²'
category = reporting_period_data['names']
# table_title
ws['B' + str(row_title + 1)].fill = table_fill
ws['B' + str(row_title + 1)].font = title_font
ws['B' + str(row_title + 1)].alignment = c_c_alignment
ws['B' + str(row_title + 1)] = '报告期'
ws['B' + str(row_title + 1)].border = f_border
ws['C' + str(row_title + 1)].font = title_font
ws['C' + str(row_title + 1)].alignment = c_c_alignment
ws['C' + str(row_title + 1)] = '算术平均数'
ws['C' + str(row_title + 1)].border = f_border
ws['D' + str(row_title + 1)].font = title_font
ws['D' + str(row_title + 1)].alignment = c_c_alignment
ws['D' + str(row_title + 1)] = '中位数'
ws['D' + str(row_title + 1)].border = f_border
ws['E' + str(row_title + 1)].font = title_font
ws['E' + str(row_title + 1)].alignment = c_c_alignment
ws['E' + str(row_title + 1)] = '最小值'
ws['E' + str(row_title + 1)].border = f_border
ws['F' + str(row_title + 1)].font = title_font
ws['F' + str(row_title + 1)].alignment = c_c_alignment
ws['F' + str(row_title + 1)] = '最大值'
ws['F' + str(row_title + 1)].border = f_border
ws['G' + str(row_title + 1)].font = title_font
ws['G' + str(row_title + 1)].alignment = c_c_alignment
ws['G' + str(row_title + 1)] = '样本标准差'
ws['G' + str(row_title + 1)].border = f_border
ws['H' + str(row_title + 1)].font = title_font
ws['H' + str(row_title + 1)].alignment = c_c_alignment
ws['H' + str(row_title + 1)] = '样本方差'
ws['H' + str(row_title + 1)].border = f_border
# table_data
for i, value in enumerate(category):
row_data = row_title + 2 + i
ws['B' + str(row_data)].font = name_font
ws['B' + str(row_data)].alignment = c_c_alignment
ws['B' + str(row_data)] = reporting_period_data['names'][
i] + " (" + reporting_period_data['units'][i] + "/M²)"
ws['B' + str(row_data)].border = f_border
ws['C' + str(row_data)].font = name_font
ws['C' + str(row_data)].alignment = c_c_alignment
if reporting_period_data['means_per_unit_area'][i] \
or reporting_period_data['means_per_unit_area'][i] == 0:
ws['C' + str(row_data)] = round(
reporting_period_data['means_per_unit_area'][i], 2)
ws['C' + str(row_data)].border = f_border
ws['C' + str(row_data)].number_format = '0.00'
ws['D' + str(row_data)].font = name_font
ws['D' + str(row_data)].alignment = c_c_alignment
if reporting_period_data['medians_per_unit_area'][i] \
or reporting_period_data['medians_per_unit_area'][i] == 0:
ws['D' + str(row_data)] = round(
reporting_period_data['medians_per_unit_area'][i], 2)
ws['D' + str(row_data)].border = f_border
ws['D' + str(row_data)].number_format = '0.00'
ws['E' + str(row_data)].font = name_font
ws['E' + str(row_data)].alignment = c_c_alignment
if reporting_period_data['minimums_per_unit_area'][i] \
or reporting_period_data['minimums_per_unit_area'][i] == 0:
ws['E' + str(row_data)] = round(
reporting_period_data['minimums_per_unit_area'][i], 2)
ws['E' + str(row_data)].border = f_border
ws['E' + str(row_data)].number_format = '0.00'
ws['F' + str(row_data)].font = name_font
ws['F' + str(row_data)].alignment = c_c_alignment
if reporting_period_data['maximums_per_unit_area'][i] \
or reporting_period_data['maximums_per_unit_area'][i] == 0:
ws['F' + str(row_data)] = round(
reporting_period_data['maximums_per_unit_area'][i], 2)
ws['F' + str(row_data)].border = f_border
ws['F' + str(row_data)].number_format = '0.00'
ws['G' + str(row_data)].font = name_font
ws['G' + str(row_data)].alignment = c_c_alignment
if (reporting_period_data['stdevs_per_unit_area'][i]) \
or reporting_period_data['stdevs_per_unit_area'][i] == 0:
ws['G' + str(row_data)] = round(
reporting_period_data['stdevs_per_unit_area'][i], 2)
ws['G' + str(row_data)].border = f_border
ws['G' + str(row_data)].number_format = '0.00'
ws['H' + str(row_data)].font = name_font
ws['H' + str(row_data)].alignment = c_c_alignment
if reporting_period_data['variances_per_unit_area'][i] \
or reporting_period_data['variances_per_unit_area'][i] == 0:
ws['H' + str(row_data)] = round(
reporting_period_data['variances_per_unit_area'][i], 2)
ws['H' + str(row_data)].border = f_border
ws['H' + str(row_data)].number_format = '0.00'
########################################################
# Third: 详细数据
# row_sat+row_title~ row_sat+row_title+time_len: line
# row_sat+1+row_title: table title
# i + row_sat + 2 + 10 * ca_len~: table_data
########################################################
has_timestamps_flag = True
if "timestamps" not in reporting_period_data.keys() or \
reporting_period_data['timestamps'] is None or \
len(reporting_period_data['timestamps']) == 0:
has_timestamps_flag = False
if has_timestamps_flag:
timestamps = reporting_period_data['timestamps'][0]
values = reporting_period_data['values']
names = reporting_period_data['names']
ca_len = len(names)
time_len = len(timestamps)
# title
row_title = 10 * ca_len
# row_st == row_statistical analysis table
row_sat = 12 + 3 * ca_len
ws['B' + str(row_sat + row_title)].font = title_font
ws['B' + str(row_sat + row_title)] = name + ' 详细数据'
# table_title
ws['B' + str(row_sat + 1 + row_title)].fill = table_fill
ws['B' + str(row_sat + 1 + row_title)].font = name_font
ws['B' + str(row_sat + 1 + row_title)].alignment = c_c_alignment
ws['B' + str(row_sat + 1 + row_title)] = "时间"
ws['B' + str(row_sat + 1 + row_title)].border = f_border
for i in range(0, ca_len):
col = chr(ord('C') + i)
ws[col + str(row_sat + 1 + row_title)].font = name_font
ws[col + str(row_sat + 1 + row_title)].alignment = c_c_alignment
ws[col + str(row_sat + 1 + row_title)] = names[
i] + " - (" + reporting_period_data['units'][i] + ")"
ws[col + str(row_sat + 1 + row_title)].border = f_border
# table_date
for i in range(0, time_len):
rows = i + row_sat + 2 + 10 * ca_len
ws['B' + str(rows)].font = name_font
ws['B' + str(rows)].alignment = c_c_alignment
ws['B' + str(rows)] = timestamps[i]
ws['B' + str(rows)].border = f_border
for index in range(0, ca_len):
col = chr(ord('C') + index)
ws[col + str(rows)].font = name_font
ws[col + str(rows)].alignment = c_c_alignment
ws[col + str(rows)] = round(values[index][i], 2)
ws[col + str(rows)].number_format = '0.00'
ws[col + str(rows)].border = f_border
# 小计
row_subtotals = row_sat + 2 + time_len + 10 * ca_len
ws['B' + str(row_subtotals)].font = name_font
ws['B' + str(row_subtotals)].alignment = c_c_alignment
ws['B' + str(row_subtotals)] = "小计"
ws['B' + str(row_subtotals)].border = f_border
for i in range(0, ca_len):
col = chr(ord('C') + i)
ws[col + str(row_subtotals)].font = name_font
ws[col + str(row_subtotals)].alignment = c_c_alignment
ws[col + str(row_subtotals)] = round(
reporting_period_data['subtotals'][i], 2)
ws[col + str(row_subtotals)].border = f_border
ws[col + str(row_subtotals)].number_format = '0.00'
# LineChart
for i in range(0, ca_len):
lc = LineChart()
lc.title = "报告期消耗" + " - " + names[
i] + "(" + reporting_period_data['units'][i] + ")"
lc.style = 10
lc.height = 8.40 # cm 1.05*8 1.05cm = 30 pt
lc.width = 31
lc.x_axis.majorTickMark = 'in'
lc.y_axis.majorTickMark = 'in'
times = Reference(ws,
min_col=2,
min_row=row_sat + 2 + row_title,
max_row=row_sat + 2 + row_title + time_len)
lc_data = Reference(ws,
min_col=3 + i,
min_row=row_sat + 1 + row_title,
max_row=row_sat + 1 + row_title + time_len)
lc.add_data(lc_data, titles_from_data=True)
lc.set_categories(times)
ser = lc.series[0]
ser.marker.symbol = "diamond"
ser.marker.size = 5
ws.add_chart(lc, 'B' + str(row_sat + 10 * i))
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
def generate_excel(report, name, reporting_start_datetime_local,
reporting_end_datetime_local, period_type):
wb = Workbook()
ws = wb.active
# Row height
ws.row_dimensions[1].height = 102
for i in range(2, 2000 + 1):
ws.row_dimensions[i].height = 42
# Col width
ws.column_dimensions['A'].width = 1.5
ws.column_dimensions['B'].width = 25.0
for i in range(ord('C'), ord('L')):
ws.column_dimensions[chr(i)].width = 15.0
# Font
name_font = Font(name='Constantia', size=15, bold=True)
title_font = Font(name='宋体', size=15, bold=True)
data_font = Font(name='Franklin Gothic Book', size=11)
table_fill = PatternFill(fill_type='solid', fgColor='1F497D')
f_border = Border(left=Side(border_style='medium', color='00000000'),
right=Side(border_style='medium', color='00000000'),
bottom=Side(border_style='medium', color='00000000'),
top=Side(border_style='medium', color='00000000'))
b_border = Border(bottom=Side(border_style='medium', color='00000000'), )
b_c_alignment = Alignment(vertical='bottom',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
c_c_alignment = Alignment(vertical='center',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
b_r_alignment = Alignment(vertical='bottom',
horizontal='right',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
c_r_alignment = Alignment(vertical='bottom',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
# Img
img = Image("excelexporters/myems.png")
# img = Image("myems.png")
img.width = img.width * 1.06
img.height = img.height * 1.06
ws.add_image(img, 'B1')
# Title
ws.row_dimensions[3].height = 60
ws['B3'].font = name_font
ws['B3'].alignment = b_r_alignment
ws['B3'] = 'Name:'
ws['C3'].border = b_border
ws['C3'].alignment = b_c_alignment
ws['C3'].font = name_font
ws['C3'] = name
ws['D3'].font = name_font
ws['D3'].alignment = b_r_alignment
ws['D3'] = 'Period:'
ws['E3'].border = b_border
ws['E3'].alignment = b_c_alignment
ws['E3'].font = name_font
ws['E3'] = period_type
ws['F3'].font = name_font
ws['F3'].alignment = b_r_alignment
ws['F3'] = 'Date:'
ws['G3'].border = b_border
ws['G3'].alignment = b_c_alignment
ws['G3'].font = name_font
ws['G3'] = reporting_start_datetime_local[:
10] + "__" + reporting_end_datetime_local[:
10]
ws.merge_cells("G3:H3")
if "reporting_period" not in report.keys() or \
"names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0:
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
#################################################
# First: 报告期成本分析
# 6: title
# 7: table title
# 8~10 table_data
# Total: 5 rows
# if has not energy data: set low height for rows
#################################################
reporting_period_data = report['reporting_period']
has_energy_data_flag = True
if "names" not in reporting_period_data.keys() or \
reporting_period_data['names'] is None or \
len(reporting_period_data['names']) == 0:
has_energy_data_flag = False
if has_energy_data_flag:
ws['B6'].font = title_font
ws['B6'] = name + ' 报告期成本分析'
category = reporting_period_data['names']
ca_len = len(category)
ws.row_dimensions[7].height = 60
ws['B7'].fill = table_fill
ws['B7'].border = f_border
ws['B8'].font = title_font
ws['B8'].alignment = c_c_alignment
ws['B8'] = '成本'
ws['B8'].border = f_border
ws['B9'].font = title_font
ws['B9'].alignment = c_c_alignment
ws['B9'] = '环比'
ws['B9'].border = f_border
col = ''
for i in range(0, ca_len):
col = chr(ord('C') + i)
row = '7'
cell = col + row
ws[col + '7'].fill = table_fill
ws[col + '7'].font = name_font
ws[col + '7'].alignment = c_c_alignment
ws[col + '7'] = reporting_period_data['names'][
i] + " (" + reporting_period_data['units'][i] + ")"
ws[col + '7'].border = f_border
ws[col + '8'].font = name_font
ws[col + '8'].alignment = c_c_alignment
ws[col + '8'] = round(reporting_period_data['subtotals'][i], 2)
ws[col + '8'].border = f_border
ws[col + '9'].font = name_font
ws[col + '9'].alignment = c_c_alignment
ws[col + '9'] = str(round(reporting_period_data['increment_rates'][i] * 100, 2)) + "%" \
if reporting_period_data['increment_rates'][i] is not None else "-"
ws[col + '9'].border = f_border
col = chr(ord(col) + 1)
ws[col + '7'].fill = table_fill
ws[col + '7'].font = name_font
ws[col + '7'].alignment = c_c_alignment
ws[col + '7'] = "总计 (" + reporting_period_data['total_unit'] + ")"
ws[col + '7'].border = f_border
ws[col + '8'].font = name_font
ws[col + '8'].alignment = c_c_alignment
ws[col + '8'] = round(reporting_period_data['total'], 2)
ws[col + '8'].border = f_border
ws[col + '9'].font = name_font
ws[col + '9'].alignment = c_c_alignment
ws[col + '9'] = str(round(reporting_period_data['total_increment_rate'] * 100, 2)) + "%" \
if reporting_period_data['total_increment_rate'] is not None else "-"
ws[col + '9'].border = f_border
else:
for i in range(6, 8 + 1):
ws.row_dimensions[i].height = 0.1
#################################################
# Second: 分时用电成本
# 12: title
# 13: table title
# 14~17 table_data
# Total: 6 rows
################################################
has_ele_peak_flag = True
if "toppeaks" not in reporting_period_data.keys() or \
reporting_period_data['toppeaks'] is None or \
len(reporting_period_data['toppeaks']) == 0:
has_ele_peak_flag = False
if has_ele_peak_flag:
ws['B12'].font = title_font
ws['B12'] = name + ' 分时用电成本'
ws.row_dimensions[13].height = 60
ws['B13'].fill = table_fill
ws['B13'].font = name_font
ws['B13'].alignment = c_c_alignment
ws['B13'].border = f_border
ws['C13'].fill = table_fill
ws['C13'].font = name_font
ws['C13'].alignment = c_c_alignment
ws['C13'].border = f_border
ws['C13'] = '分时用电成本'
ws['B14'].font = title_font
ws['B14'].alignment = c_c_alignment
ws['B14'] = '尖'
ws['B14'].border = f_border
ws['C14'].font = title_font
ws['C14'].alignment = c_c_alignment
ws['C14'].border = f_border
ws['C14'] = round(reporting_period_data['toppeaks'][0], 2)
ws['B15'].font = title_font
ws['B15'].alignment = c_c_alignment
ws['B15'] = '峰'
ws['B15'].border = f_border
ws['C15'].font = title_font
ws['C15'].alignment = c_c_alignment
ws['C15'].border = f_border
ws['C15'] = round(reporting_period_data['onpeaks'][0], 2)
ws['B16'].font = title_font
ws['B16'].alignment = c_c_alignment
ws['B16'] = '平'
ws['B16'].border = f_border
ws['C16'].font = title_font
ws['C16'].alignment = c_c_alignment
ws['C16'].border = f_border
ws['C16'] = round(reporting_period_data['midpeaks'][0], 2)
ws['B17'].font = title_font
ws['B17'].alignment = c_c_alignment
ws['B17'] = '谷'
ws['B17'].border = f_border
ws['C17'].font = title_font
ws['C17'].alignment = c_c_alignment
ws['C17'].border = f_border
ws['C17'] = round(reporting_period_data['offpeaks'][0], 2)
pie = PieChart()
pie.title = name + ' 分时用电成本'
labels = Reference(ws, min_col=2, min_row=14, max_row=17)
pie_data = Reference(ws, min_col=3, min_row=13, max_row=17)
pie.add_data(pie_data, titles_from_data=True)
pie.set_categories(labels)
pie.height = 7.25 # cm 1.05*5 1.05cm = 30 pt
pie.width = 9
# pie.title = "Pies sold by category"
s1 = pie.series[0]
s1.dLbls = DataLabelList()
s1.dLbls.showCatName = False # 标签显示
s1.dLbls.showVal = True # 数量显示
s1.dLbls.showPercent = True # 百分比显示
# s1 = CharacterProperties(sz=1800) # 图表中字体大小 *100
ws.add_chart(pie, "D13")
else:
for i in range(12, 18 + 1):
ws.row_dimensions[i].height = 0.1
# end_row 10
# start_row 12
################################################
# Second: 成本占比
################################################
current_row_number = 19
has_subtotals_data_flag = True
if 'subtotals' not in reporting_period_data.keys() or \
reporting_period_data['subtotals'] is None:
has_subtotals_data_flag = False
if has_subtotals_data_flag:
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 成本占比'
current_row_number += 1
table_start_row_number = current_row_number
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].font = name_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)].fill = table_fill
ws['C' + str(current_row_number)].font = name_font
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = '成本占比'
current_row_number += 1
category = reporting_period_data['names']
ca_len = len(category)
for i in range(0, ca_len):
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)] = reporting_period_data['names'][i] + \
' (' + reporting_period_data['units'][i] + ')'
ws['B' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)].font = title_font
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)] = round(
reporting_period_data['subtotals'][i], 3)
ws['C' + str(current_row_number)].border = f_border
current_row_number += 1
table_end_row_number = current_row_number - 1
pie = PieChart()
pie.title = name + ' 成本占比'
labels = Reference(ws,
min_col=2,
min_row=table_start_row_number + 1,
max_row=table_end_row_number)
pie_data = Reference(ws,
min_col=3,
min_row=table_start_row_number,
max_row=table_end_row_number)
pie.add_data(pie_data, titles_from_data=True)
pie.set_categories(labels)
pie.height = 6.6
pie.width = 9
s1 = pie.series[0]
s1.dLbls = DataLabelList()
s1.dLbls.showCatName = False
s1.dLbls.showVal = True
s1.dLbls.showPercent = True
ws.add_chart(pie, 'D' + str(table_start_row_number))
if ca_len < 4:
current_row_number = current_row_number - ca_len + 4
current_row_number += 1
################################################
# Fourth: 成本详情
# current_row_number: title
# current_row_number+1 ~ current_row_number+1+ca_len*6-1: line
# current_row_number+1+ca_len*6: table title
# current_row_number+1+ca_len*6~: table_data
################################################
reporting_period_data = report['reporting_period']
times = reporting_period_data['timestamps']
has_detail_data_flag = True
ca_len = len(report['reporting_period']['names'])
real_timestamps_len = timestamps_data_not_equal_0(
report['parameters']['timestamps'])
table_row = current_row_number + 2 + ca_len * 6 + real_timestamps_len * 7
chart_start_row_number = current_row_number + 1
if "timestamps" not in reporting_period_data.keys() or \
reporting_period_data['timestamps'] is None or \
len(reporting_period_data['timestamps']) == 0:
has_detail_data_flag = False
if has_detail_data_flag:
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 详细数据'
ws.row_dimensions[table_row].height = 60
ws['B' + str(table_row)].fill = table_fill
ws['B' + str(table_row)].font = title_font
ws['B' + str(table_row)].border = f_border
ws['B' + str(table_row)].alignment = c_c_alignment
ws['B' + str(table_row)] = '日期时间'
time = times[0]
has_data = False
max_row = 0
if len(time) > 0:
has_data = True
max_row = table_row + len(time)
print("max_row", max_row)
if has_data:
for i in range(0, len(time)):
col = 'B'
row = str(table_row + 1 + i)
# col = chr(ord('B') + i)
ws[col + row].font = title_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = time[i]
ws[col + row].border = f_border
for i in range(0, ca_len):
# 38 title
col = chr(ord('C') + i)
ws[col + str(table_row)].fill = table_fill
ws[col + str(table_row)].font = title_font
ws[col + str(table_row)].alignment = c_c_alignment
ws[col + str(table_row)] = reporting_period_data['names'][i] + \
" (" + reporting_period_data['units'][i] + ")"
ws[col + str(table_row)].border = f_border
# 39 data
time = times[i]
time_len = len(time)
for j in range(0, time_len):
row = str(table_row + 1 + j)
# col = chr(ord('B') + i)
ws[col + row].font = title_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = round(
reporting_period_data['values'][i][j], 2)
ws[col + row].border = f_border
current_row_number = table_row + 1 + len(times[0])
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '小计'
for i in range(0, ca_len):
col = chr(ord('C') + i)
ws[col + str(current_row_number)].font = title_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = round(
reporting_period_data['subtotals'][i], 2)
# line
# 39~: line
line = LineChart()
line.title = '报告期消耗 - ' + ws.cell(column=3 + i,
row=table_row).value
labels = Reference(ws,
min_col=2,
min_row=table_row + 1,
max_row=max_row)
line_data = Reference(ws,
min_col=3 + i,
min_row=table_row,
max_row=max_row) # openpyxl bug
line.add_data(line_data, titles_from_data=True)
line.set_categories(labels)
line_data = line.series[0]
line_data.marker.symbol = "circle"
line_data.smooth = True
line.x_axis.crosses = 'min'
line.height = 8.25 # cm 1.05*5 1.05cm = 30 pt
line.width = 24
# pie.title = "Pies sold by category"
line.dLbls = DataLabelList()
line.dLbls.dLblPos = 't'
# line.dLbls.showCatName = True # label show
line.dLbls.showVal = True # val show
line.dLbls.showPercent = True # percent show
# s1 = CharacterProperties(sz=1800) # font size *100
chart_col = 'B'
chart_cell = chart_col + str(chart_start_row_number + 6 * i)
ws.add_chart(line, chart_cell)
current_sheet_parameters_row_number = chart_start_row_number + ca_len * 6
##########################################
has_parameters_names_and_timestamps_and_values_data = True
if 'parameters' not in report.keys() or \
report['parameters'] is None or \
'names' not in report['parameters'].keys() or \
report['parameters']['names'] is None or \
len(report['parameters']['names']) == 0 or \
'timestamps' not in report['parameters'].keys() or \
report['parameters']['timestamps'] is None or \
len(report['parameters']['timestamps']) == 0 or \
'values' not in report['parameters'].keys() or \
report['parameters']['values'] is None or \
len(report['parameters']['values']) == 0 or \
timestamps_data_all_equal_0(report['parameters']['timestamps']):
has_parameters_names_and_timestamps_and_values_data = False
if has_parameters_names_and_timestamps_and_values_data:
###############################
# new worksheet
###############################
parameters_data = report['parameters']
parameters_names_len = len(parameters_data['names'])
parameters_ws = wb.create_sheet('相关参数')
parameters_timestamps_data_max_len = \
get_parameters_timestamps_lists_max_len(list(parameters_data['timestamps']))
# Row height
parameters_ws.row_dimensions[1].height = 102
for i in range(2, 7 + 1):
parameters_ws.row_dimensions[i].height = 42
for i in range(8, parameters_timestamps_data_max_len + 10):
parameters_ws.row_dimensions[i].height = 60
# Col width
parameters_ws.column_dimensions['A'].width = 1.5
parameters_ws.column_dimensions['B'].width = 25.0
for i in range(3, 12 + parameters_names_len * 3):
parameters_ws.column_dimensions[format_cell.get_column_letter(
i)].width = 15.0
# Img
img = Image("excelexporters/myems.png")
img.width = img.width * 0.85
img.height = img.height * 0.85
# img = Image("myems.png")
parameters_ws.add_image(img, 'B1')
# Title
parameters_ws.row_dimensions[3].height = 60
parameters_ws['B3'].font = name_font
parameters_ws['B3'].alignment = b_r_alignment
parameters_ws['B3'] = 'Name:'
parameters_ws['C3'].border = b_border
parameters_ws['C3'].alignment = b_c_alignment
parameters_ws['C3'].font = name_font
parameters_ws['C3'] = name
parameters_ws['D3'].font = name_font
parameters_ws['D3'].alignment = b_r_alignment
parameters_ws['D3'] = 'Period:'
parameters_ws['E3'].border = b_border
parameters_ws['E3'].alignment = b_c_alignment
parameters_ws['E3'].font = name_font
parameters_ws['E3'] = period_type
parameters_ws['F3'].font = name_font
parameters_ws['F3'].alignment = b_r_alignment
parameters_ws['F3'] = 'Date:'
parameters_ws['G3'].border = b_border
parameters_ws['G3'].alignment = b_c_alignment
parameters_ws['G3'].font = name_font
parameters_ws[
'G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
parameters_ws.merge_cells("G3:H3")
parameters_ws_current_row_number = 6
parameters_ws['B' +
str(parameters_ws_current_row_number)].font = title_font
parameters_ws['B' +
str(parameters_ws_current_row_number)] = name + ' 相关参数'
parameters_ws_current_row_number += 1
parameters_table_start_row_number = parameters_ws_current_row_number
parameters_ws.row_dimensions[
parameters_ws_current_row_number].height = 80
parameters_ws_current_row_number += 1
table_current_col_number = 'B'
for i in range(0, parameters_names_len):
if len(parameters_data['timestamps'][i]) == 0:
continue
parameters_ws[table_current_col_number +
str(parameters_ws_current_row_number -
1)].fill = table_fill
parameters_ws[table_current_col_number +
str(parameters_ws_current_row_number -
1)].border = f_border
col = chr(ord(table_current_col_number) + 1)
parameters_ws[col + str(parameters_ws_current_row_number -
1)].fill = table_fill
parameters_ws[col + str(parameters_ws_current_row_number -
1)].border = f_border
parameters_ws[col + str(parameters_ws_current_row_number -
1)].font = name_font
parameters_ws[col + str(parameters_ws_current_row_number -
1)].alignment = c_c_alignment
parameters_ws[col + str(parameters_ws_current_row_number -
1)] = parameters_data['names'][i]
table_current_row_number = parameters_ws_current_row_number
for j, value in enumerate(list(parameters_data['timestamps'][i])):
col = table_current_col_number
parameters_ws[col +
str(table_current_row_number)].border = f_border
parameters_ws[col +
str(table_current_row_number)].font = title_font
parameters_ws[
col +
str(table_current_row_number)].alignment = c_c_alignment
parameters_ws[col + str(table_current_row_number)] = value
col = chr(ord(col) + 1)
parameters_ws[col +
str(table_current_row_number)].border = f_border
parameters_ws[col +
str(table_current_row_number)].font = title_font
parameters_ws[
col +
str(table_current_row_number)].alignment = c_c_alignment
parameters_ws[col + str(table_current_row_number)] = round(
parameters_data['values'][i][j], 2)
table_current_row_number += 1
table_current_col_number = chr(ord(table_current_col_number) + 3)
########################################################
# parameters chart and parameters table
########################################################
ws['B' + str(current_sheet_parameters_row_number)].font = title_font
ws['B' + str(current_sheet_parameters_row_number)] = name + ' 相关参数'
current_sheet_parameters_row_number += 1
chart_start_row_number = current_sheet_parameters_row_number
col_index = 0
for i in range(0, parameters_names_len):
if len(parameters_data['timestamps'][i]) == 0:
continue
line = LineChart()
data_col = 3 + col_index * 3
labels_col = 2 + col_index * 3
col_index += 1
line.title = '相关参数 - ' + \
parameters_ws.cell(row=parameters_table_start_row_number, column=data_col).value
labels = Reference(parameters_ws,
min_col=labels_col,
min_row=parameters_table_start_row_number + 1,
max_row=(len(parameters_data['timestamps'][i]) +
parameters_table_start_row_number))
line_data = Reference(
parameters_ws,
min_col=data_col,
min_row=parameters_table_start_row_number,
max_row=(len(parameters_data['timestamps'][i]) +
parameters_table_start_row_number))
line.add_data(line_data, titles_from_data=True)
line.set_categories(labels)
line_data = line.series[0]
line_data.marker.symbol = "circle"
line_data.smooth = True
line.x_axis.crosses = 'min'
line.height = 8.25
line.width = 24
line.dLbls = DataLabelList()
line.dLbls.dLblPos = 't'
line.dLbls.showVal = False
line.dLbls.showPercent = False
chart_col = 'B'
chart_cell = chart_col + str(chart_start_row_number)
chart_start_row_number += 6
ws.add_chart(line, chart_cell)
current_sheet_parameters_row_number = chart_start_row_number
current_sheet_parameters_row_number += 1
##########################################
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
from openpyxl.worksheet.table import Table, TableStyleInfo
from openpyxl.drawing.image import Image
wb = load_workbook(".\\Exercise Files\\Pie.xlsx")
ws = wb.active
# we will create a table object with display name of Table1
tab = Table(displayName='Table1', ref='A1:B5')
# now the table knows what its called and where to find the data and it needs to know how to format itself.
# from here we will use table style information
style = TableStyleInfo(name='TableStyleMedium9',
showFirstColumn=False,
showLastColumn=False,
showRowStripes=True,
showColumnStripes=True)
# set the tablestyleinfo property to style we just created.
tab.tableStyleInfo = style
# add the table to worksheet
ws.add_table(tab)
img = Image(".\\Exercise Files\\mountain.jpg")
img.height = img.height * .25
img.width = img.width * .25
ws.add_image(img, 'C1')
# image property allows us to access us height and width
wb.save(".\\Exercise Files\\table.xlsx")
#%%
sheet['A1'] = 'CODE'
sheet['B1'] = 'QUANTITY'
sheet['C1'] = 'RATE'
sheet['D1'] = 'PICTURE'
sheet['E1'] = 'DESCRIPTION'
descriptions = 'Generates random lorem ipsum text Lorem Ipsum Generator Lorem Ipsum Generator provides a GTK+ graphical user interface, a command-line interface'
#%%
i = sheet.max_row
for file in glob.glob("*.jpeg"):
i = i + 1
splits = file.split(' ')
sheet['A{}'.format(i)] = splits[0]
sheet['B{}'.format(i)] = splits[1]
sheet['C{}'.format(i)] = splits[2].split('.')[0]
img = Image(os.getcwd() + '\\' + file)
img.height, img.width = 150, 150
sheet.add_image(img, 'D{}'.format(i))
sheet.row_dimensions[i].height = 130
sheet.column_dimensions['B'].width = 20
sheet.column_dimensions['D'].width = 30
sheet['E{}'.format(i)] = descriptions
sheet.column_dimensions['E'].width = 50
#%%
for row_ in sheet:
for cell_ in row_:
cell_.alignment = a
#for col in sheet.columns:
# max_length = 0
# column = col[0].column # Get the column name
# for cell in col:
from openpyxl import Workbook
from openpyxl.drawing.image import Image
wb = Workbook()
ws = wb.active
img = Image("img.png")
ws.add_image(img, "C3") # C3 위치에 img.png 파일 이미지 삽임
wb.save("sample_image.xlsx")
def do_process_workbook():
print_if_debug("creating temp directory")
if not args.keep_barcodes_in_home:
tempdir = tempfile.mkdtemp()
else:
temp_dir_in_cwd = os.path.join(program_launch_cwd, 'barcode images')
os.mkdir(temp_dir_in_cwd)
tempdir = temp_dir_in_cwd
print_if_debug("temp directory created as: " + tempdir)
progress_bar.configure(mode='indeterminate', maximum=100)
progress_bar.start()
progress_numbers.configure(text="opening workbook")
wb = openpyxl.load_workbook(old_workbook_path)
ws = wb.worksheets[0]
progress_numbers.configure(text="testing workbook save")
wb.save(new_workbook_path)
count = 0
save_counter = 0
progress_bar.configure(maximum=ws.max_row, value=count)
progress_numbers.configure(text=str(count) + "/" + str(ws.max_row))
border_size = int(border_spinbox.get())
for _ in ws.iter_rows(): # iterate over all rows in current worksheet
if not process_workbook_keep_alive:
break
try:
count += 1
progress_bar.configure(maximum=ws.max_row, value=count, mode='determinate')
progress_numbers.configure(text=str(count) + "/" + str(ws.max_row))
progress_bar.configure(value=count)
# get code from column "B", on current row, add a zero to the end to make seven digits
print_if_debug("getting cell contents on line number " + str(count))
upc_barcode_number = ws["B" + str(count)].value + "0"
print_if_debug("cell contents are: " + upc_barcode_number)
# select barcode type, specify barcode, and select image writer to save as png
ean = barcode.get('ean8', upc_barcode_number, writer=ImageWriter())
# select output image size via dpi. internally, pybarcode renders as svg, then renders that as a png file.
# dpi is the conversion from svg image size in mm, to what the image writer thinks is inches.
ean.default_writer_options['dpi'] = int(dpi_spinbox.get())
# module height is the barcode bar height in mm
ean.default_writer_options['module_height'] = float(height_spinbox.get())
# text distance is the distance between the bottom of the barcode, and the top of the text in mm
ean.default_writer_options['text_distance'] = 1
# font size is the text size in pt
ean.default_writer_options['font_size'] = int(font_size_spinbox.get())
# quiet zone is the distance from the ends of the barcode to the ends of the image in mm
ean.default_writer_options['quiet_zone'] = 2
# save barcode image with generated filename
print_if_debug("generating barcode image")
with tempfile.NamedTemporaryFile(dir=tempdir, suffix='.png', delete=False) as initial_temp_file_path:
filename = ean.save(initial_temp_file_path.name[0:-4])
print_if_debug("success, barcode image path is: " + filename)
print_if_debug("opening " + str(filename) + " to add border")
barcode_image = pil_Image.open(str(filename)) # open image as pil object
print_if_debug("success")
print_if_debug("adding barcode and saving")
img_save = pil_ImageOps.expand(barcode_image, border=border_size,
fill='white') # add border around image
width, height = img_save.size # get image size of barcode with border
# resize cell to size of image
ws.column_dimensions['A'].width = int(math.ceil(float(width) * .15))
ws.row_dimensions[count].height = int(math.ceil(float(height) * .75))
# write out image to file
with tempfile.NamedTemporaryFile(dir=tempdir, suffix='.png', delete=False) as final_barcode_path:
img_save.save(final_barcode_path.name)
print_if_debug("success, final barcode path is: " + final_barcode_path.name)
# open image with as openpyxl image object
print_if_debug("opening " + final_barcode_path.name + " to insert into output spreadsheet")
img = OpenPyXlImage(final_barcode_path.name)
print_if_debug("success")
# attach image to cell
print_if_debug("adding image to cell")
img.anchor(ws.cell('A' + str(count)), anchortype='oneCell')
# add image to cell
ws.add_image(img)
save_counter += 1
print_if_debug("success")
# This save in the loop frees references to the barcode images,
# so that python's garbage collector can clear them
except Exception as barcode_error:
print_if_debug(barcode_error)
if save_counter >= file_limit - 50:
print_if_debug("saving intermediate workbook to free file handles")
progress_bar.configure(mode='indeterminate', maximum=100)
progress_bar.start()
progress_numbers.configure(text=str(count) + "/" + str(ws.max_row) + " saving")
wb.save(new_workbook_path)
print_if_debug("success")
save_counter = 1
progress_numbers.configure(text=str(count) + "/" + str(ws.max_row))
progress_bar.configure(value=0)
print_if_debug("saving workbook to file")
progress_bar.configure(mode='indeterminate', maximum=100)
progress_bar.start()
progress_numbers.configure(text="saving")
wb.save(new_workbook_path)
print_if_debug("success")
if not args.keep_barcode_files:
print_if_debug("removing temp folder " + tempdir)
shutil.rmtree(tempdir)
print_if_debug("success")
progress_bar.stop()
progress_bar.configure(maximum=100, value=0, mode='determinate')
progress_numbers.configure(text="")
def add_image(self, ws_title, img_path, anchor):
"""Add an image to worksheet, specifying the anchor cell (for the top-left corner of the image)."""
ws = self.wb[ws_title]
ws.add_image(Image(img_path), anchor)
print('\tAdded {img} to {sheet} at cell {cell}'.format(
img=os.path.basename(img_path), sheet=ws_title, cell=anchor))
def generate_excel(report, name, reporting_start_datetime_local,
reporting_end_datetime_local, period_type):
wb = Workbook()
ws = wb.active
# Row height
ws.row_dimensions[1].height = 102
for i in range(2, 2000 + 1):
ws.row_dimensions[i].height = 42
# for i in range(2, 37 + 1):
# ws.row_dimensions[i].height = 30
#
# for i in range(38, 69 + 1):
# ws.row_dimensions[i].height = 30
# Col width
ws.column_dimensions['A'].width = 1.5
ws.column_dimensions['B'].width = 25.0
for i in range(ord('C'), ord('L')):
ws.column_dimensions[chr(i)].width = 15.0
# Font
name_font = Font(name='Constantia', size=15, bold=True)
title_font = Font(name='宋体', size=15, bold=True)
data_font = Font(name='Franklin Gothic Book', size=11)
table_fill = PatternFill(fill_type='solid', fgColor='1F497D')
f_border = Border(left=Side(border_style='medium', color='00000000'),
right=Side(border_style='medium', color='00000000'),
bottom=Side(border_style='medium', color='00000000'),
top=Side(border_style='medium', color='00000000'))
b_border = Border(bottom=Side(border_style='medium', color='00000000'), )
b_c_alignment = Alignment(vertical='bottom',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
c_c_alignment = Alignment(vertical='center',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
b_r_alignment = Alignment(vertical='bottom',
horizontal='right',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
c_r_alignment = Alignment(vertical='bottom',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
# Img
img = Image("excelexporters/myems.png")
img.width = img.width * 0.85
img.height = img.height * 0.85
# img = Image("myems.png")
ws.add_image(img, 'B1')
# Title
ws.row_dimensions[3].height = 60
ws['B3'].font = name_font
ws['B3'].alignment = b_r_alignment
ws['B3'] = 'Name:'
ws['C3'].border = b_border
ws['C3'].alignment = b_c_alignment
ws['C3'].font = name_font
ws['C3'] = name
ws['D3'].font = name_font
ws['D3'].alignment = b_r_alignment
ws['D3'] = 'Period:'
ws['E3'].border = b_border
ws['E3'].alignment = b_c_alignment
ws['E3'].font = name_font
ws['E3'] = period_type
ws['F3'].font = name_font
ws['F3'].alignment = b_r_alignment
ws['F3'] = 'Date:'
ws['G3'].border = b_border
ws['G3'].alignment = b_c_alignment
ws['G3'].font = name_font
ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
ws.merge_cells("G3:H3")
if "reporting_period" not in report.keys() or \
"names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0:
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
##################################
reporting_period_data = report['reporting_period']
has_cost_data_flag = True
if "names" not in reporting_period_data.keys() or \
reporting_period_data['names'] is None or \
len(reporting_period_data['names']) == 0:
has_cost_data_flag = False
if has_cost_data_flag:
ws['B6'].font = title_font
ws['B6'] = name + ' 报告期成本'
category = reporting_period_data['names']
ca_len = len(category)
ws.row_dimensions[7].height = 60
ws['B7'].fill = table_fill
ws['B7'].border = f_border
ws['B8'].font = title_font
ws['B8'].alignment = c_c_alignment
ws['B8'] = '成本'
ws['B8'].border = f_border
ws['B9'].font = title_font
ws['B9'].alignment = c_c_alignment
ws['B9'] = '单位面积值'
ws['B9'].border = f_border
ws['B10'].font = title_font
ws['B10'].alignment = c_c_alignment
ws['B10'] = '环比'
ws['B10'].border = f_border
col = ''
for i in range(0, ca_len):
col = chr(ord('C') + i)
ws[col + '7'].fill = table_fill
ws[col + '7'].font = name_font
ws[col + '7'].alignment = c_c_alignment
ws[col + '7'] = reporting_period_data['names'][
i] + " (" + reporting_period_data['units'][i] + ")"
ws[col + '7'].border = f_border
ws[col + '8'].font = name_font
ws[col + '8'].alignment = c_c_alignment
ws[col + '8'] = round(reporting_period_data['subtotals'][i], 2)
ws[col + '8'].border = f_border
ws[col + '9'].font = name_font
ws[col + '9'].alignment = c_c_alignment
ws[col + '9'] = round(
reporting_period_data['subtotals_per_unit_area'][i], 2)
ws[col + '9'].border = f_border
ws[col + '10'].font = name_font
ws[col + '10'].alignment = c_c_alignment
ws[col + '10'] = str(round(reporting_period_data['increment_rates'][i] * 100, 2)) + "%" \
if reporting_period_data['increment_rates'][i] is not None else "-"
ws[col + '10'].border = f_border
col = chr(ord(col) + 1)
ws[col + '7'].fill = table_fill
ws[col + '7'].font = name_font
ws[col + '7'].alignment = c_c_alignment
ws[col + '7'] = "总计 (" + reporting_period_data['total_unit'] + ")"
ws[col + '7'].border = f_border
ws[col + '8'].font = name_font
ws[col + '8'].alignment = c_c_alignment
ws[col + '8'] = round(reporting_period_data['total'], 2)
ws[col + '8'].border = f_border
ws[col + '9'].font = name_font
ws[col + '9'].alignment = c_c_alignment
ws[col + '9'] = round(reporting_period_data['total_per_unit_area'], 2)
ws[col + '9'].border = f_border
ws[col + '10'].font = name_font
ws[col + '10'].alignment = c_c_alignment
ws[col + '10'] = str(round(reporting_period_data['total_increment_rate'] * 100, 2)) + "%" \
if reporting_period_data['total_increment_rate'] is not None else "-"
ws[col + '10'].border = f_border
else:
for i in range(6, 10 + 1):
ws.row_dimensions[i].height = 0.1
##############################
has_ele_peak_flag = True
if "toppeaks" not in reporting_period_data.keys() or \
reporting_period_data['toppeaks'] is None or \
len(reporting_period_data['toppeaks']) == 0:
has_ele_peak_flag = False
if has_ele_peak_flag:
ws['B12'].font = title_font
ws['B12'] = name + '分时用电成本'
ws.row_dimensions[13].height = 60
ws['B13'].fill = table_fill
ws['B13'].font = name_font
ws['B13'].alignment = c_c_alignment
ws['B13'].border = f_border
ws['C13'].fill = table_fill
ws['C13'].font = name_font
ws['C13'].alignment = c_c_alignment
ws['C13'].border = f_border
ws['C13'] = '分时用电成本'
ws['B14'].font = title_font
ws['B14'].alignment = c_c_alignment
ws['B14'] = '尖'
ws['B14'].border = f_border
ws['C14'].font = title_font
ws['C14'].alignment = c_c_alignment
ws['C14'].border = f_border
ws['C14'] = round(reporting_period_data['toppeaks'][0], 2)
ws['B15'].font = title_font
ws['B15'].alignment = c_c_alignment
ws['B15'] = '峰'
ws['B15'].border = f_border
ws['C15'].font = title_font
ws['C15'].alignment = c_c_alignment
ws['C15'].border = f_border
ws['C15'] = round(reporting_period_data['onpeaks'][0], 2)
ws['B16'].font = title_font
ws['B16'].alignment = c_c_alignment
ws['B16'] = '平'
ws['B16'].border = f_border
ws['C16'].font = title_font
ws['C16'].alignment = c_c_alignment
ws['C16'].border = f_border
ws['C16'] = round(reporting_period_data['midpeaks'][0], 2)
ws['B17'].font = title_font
ws['B17'].alignment = c_c_alignment
ws['B17'] = '谷'
ws['B17'].border = f_border
ws['C17'].font = title_font
ws['C17'].alignment = c_c_alignment
ws['C17'].border = f_border
ws['C17'] = round(reporting_period_data['offpeaks'][0], 2)
pie = PieChart()
pie.title = name + '分时用电成本'
labels = Reference(ws, min_col=2, min_row=14, max_row=17)
pie_data = Reference(ws, min_col=3, min_row=13, max_row=17)
pie.add_data(pie_data, titles_from_data=True)
pie.set_categories(labels)
pie.height = 7.25
pie.width = 9
s1 = pie.series[0]
s1.dLbls = DataLabelList()
s1.dLbls.showCatName = False
s1.dLbls.showVal = True
s1.dLbls.showPercent = True
ws.add_chart(pie, "D13")
else:
for i in range(12, 18 + 1):
ws.row_dimensions[i].height = 0.1
##################################
current_row_number = 19
has_child_flag = True
if "child_space" not in report.keys() or "energy_category_names" not in report['child_space'].keys() or \
len(report['child_space']["energy_category_names"]) == 0:
has_child_flag = False
if has_child_flag:
child = report['child_space']
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 子空间数据'
current_row_number += 1
ws.row_dimensions[current_row_number].height = 60
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].border = f_border
ca_len = len(child['energy_category_names'])
col = ''
for i in range(0, ca_len):
col = chr(ord('C') + i)
ws[col + str(current_row_number)].fill = table_fill
ws[col + str(current_row_number)].font = title_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = child['energy_category_names'][
i] + ' (' + child['units'][i] + ')'
col = chr(ord(col) + 1)
ws[col + str(current_row_number)].fill = table_fill
ws[col + str(current_row_number)].font = title_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col +
str(current_row_number
)] = '总计 (' + report['reporting_period']['total_unit'] + ')'
space_len = len(child['child_space_names_array'][0])
for i in range(0, space_len):
current_row_number += 1
row = str(current_row_number)
ws['B' + row].font = name_font
ws['B' + row].alignment = c_c_alignment
ws['B' + row] = child['child_space_names_array'][0][i]
ws['B' + row].border = f_border
col = ''
every_day_sum = 0
for j in range(0, ca_len):
col = chr(ord('C') + j)
ws[col + row].font = name_font
ws[col + row].alignment = c_c_alignment
every_day_sum += child['subtotals_array'][j][i]
ws[col + row] = round(child['subtotals_array'][j][i], 2)
ws[col + row].border = f_border
col = chr(ord(col) + 1)
ws[col + row].font = name_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = round(every_day_sum, 2)
ws[col + row].border = f_border
current_row_number += 1
# Pie
for i in range(0, ca_len):
pie = PieChart()
labels = Reference(ws,
min_col=2,
min_row=current_row_number - space_len,
max_row=current_row_number - 1)
pie_data = Reference(ws,
min_col=3 + i,
min_row=current_row_number - space_len - 1,
max_row=current_row_number - 1)
pie.add_data(pie_data, titles_from_data=True)
pie.set_categories(labels)
pie.height = 6.6
pie.width = 8
col = chr(ord('C') + i)
pie.title = ws[col + '20'].value
s1 = pie.series[0]
s1.dLbls = DataLabelList()
s1.dLbls.showCatName = False
s1.dLbls.showVal = True
s1.dLbls.showPercent = True
chart_cell = ''
if i % 2 == 0:
chart_cell = 'B' + str(current_row_number)
else:
chart_cell = 'E' + str(current_row_number)
current_row_number += 5
ws.add_chart(pie, chart_cell)
# chart_col = chr(ord('B') + 2 * i)
# chart_cell = chart_col + str(current_row_number)
# ws.add_chart(pie, chart_cell)
if ca_len % 2 == 1:
current_row_number += 5
# else:
# for i in range(19, 36 + 1):
# current_row_number = 36
# ws.row_dimensions[i].height = 0.1
current_row_number += 1
#############################################
reporting_period_data = report['reporting_period']
times = reporting_period_data['timestamps']
has_detail_data_flag = True
ca_len = len(report['reporting_period']['names'])
table_row = (current_row_number + 1) + ca_len * 6
if "timestamps" not in reporting_period_data.keys() or \
reporting_period_data['timestamps'] is None or \
len(reporting_period_data['timestamps']) == 0:
has_detail_data_flag = False
if has_detail_data_flag:
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 详细数据'
ws.row_dimensions[table_row].height = 60
ws['B' + str(table_row)].fill = table_fill
ws['B' + str(table_row)].font = title_font
ws['B' + str(table_row)].border = f_border
ws['B' + str(table_row)].alignment = c_c_alignment
ws['B' + str(table_row)] = '日期时间'
time = times[0]
has_data = False
max_row = 0
if len(time) > 0:
has_data = True
max_row = table_row + len(time)
if has_data:
for i in range(0, len(time)):
col = 'B'
row = str(table_row + 1 + i)
ws[col + row].font = title_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = time[i]
ws[col + row].border = f_border
for i in range(0, ca_len):
col = chr(ord('C') + i)
ws[col + str(table_row)].fill = table_fill
ws[col + str(table_row)].font = title_font
ws[col + str(table_row)].alignment = c_c_alignment
ws[col + str(table_row)] = reporting_period_data['names'][
i] + " (" + reporting_period_data['units'][i] + ")"
ws[col + str(table_row)].border = f_border
# 39 data
time = times[i]
time_len = len(time)
for j in range(0, time_len):
row = str(table_row + 1 + j)
ws[col + row].font = title_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = round(
reporting_period_data['values'][i][j], 2)
ws[col + row].border = f_border
line = LineChart()
line.title = \
'报告期成本 - ' + reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
labels = Reference(ws,
min_col=2,
min_row=table_row + 1,
max_row=max_row)
line_data = Reference(ws,
min_col=3 + i,
min_row=table_row,
max_row=max_row)
line.add_data(line_data, titles_from_data=True)
line.set_categories(labels)
line_data = line.series[0]
line_data.marker.symbol = "circle"
line_data.smooth = True
line.x_axis.crosses = 'min'
line.height = 8.25
line.width = 24
line.dLbls = DataLabelList()
line.dLbls.dLblPos = 't'
line.dLbls.showVal = True
line.dLbls.showPercent = False
chart_col = 'B'
chart_cell = chart_col + str(current_row_number + 1 + 6 * i)
ws.add_chart(line, chart_cell)
row = str(max_row + 1)
ws['B' + row].font = title_font
ws['B' + row].alignment = c_c_alignment
ws['B' + row] = '小计'
ws['B' + row].border = f_border
col = ''
for i in range(0, ca_len):
col = chr(ord('C') + i)
row = str(max_row + 1)
ws[col + row].font = title_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = round(reporting_period_data['subtotals'][i], 2)
ws[col + row].border = f_border
col = chr(ord(col) + 1)
ws[col + str(table_row)].fill = table_fill
ws[col + str(table_row)].font = title_font
ws[col + str(table_row)].alignment = c_c_alignment
ws[col +
str(table_row
)] = '总计 (' + report['reporting_period']['total_unit'] + ')'
ws[col + str(table_row)].border = f_border
total_sum = 0
for j in range(0, len(time)):
row = str(table_row + 1 + j)
ws[col + row].font = title_font
ws[col + row].alignment = c_c_alignment
every_day_sum = reporting_period_values_every_day_sum(
reporting_period_data, j, ca_len)
total_sum += every_day_sum
ws[col + row] = round(every_day_sum, 2)
ws[col + row].border = f_border
row = str(table_row + 1 + len(time))
ws[col + row].font = title_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = round(total_sum, 2)
ws[col + row].border = f_border
else:
for i in range(37, 69 + 1):
ws.row_dimensions[i].height = 0.1
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
#参数cols : 指定移动的列数，如：+1向右移动1列，-1向左移动1列
#参数translate=False: 表示现有单元将被覆盖。公式和参考资料将不会更新
help(Worksheet.move_range)
ws.move_range('G10:I13', rows=2, cols=2, translate=True)
#插入图片
#####
#使用：openpyxl.drawing.image.Image(self,img) 来初始化图片
#使用：Worksheet.add_image(self, img, anchor=None) 来插入图片
#参数img: 为经过Image类初始化后的图片对象
#参数anchar: 为cell单元格定位
from openpyxl.drawing.image import Image
help(Image)
help(Worksheet.add_image)
img = Image(r'openpyxl_cases\002592.png')
ws.add_image(img, 'A11')
#插入表单Table
#####
from openpyxl import Workbook
from openpyxl.worksheet.table import (
Table, #插入表单使用
TableStyleInfo #表单样式信息类
)
ws_table = wb.create_sheet(title='Table') #创建一个工作表
data = [['Apples', 10000, 5000, 8000, 6000], ['Pears', 2000, 3000, 4000, 5000],
['Bananas', 6000, 6000, 6500, 6000], ['Oranges', 500, 300, 200, 700]]
#####
def generate_excel(report, name, reporting_start_datetime_local,
reporting_end_datetime_local, period_type):
wb = Workbook()
ws = wb.active
# Row height
ws.row_dimensions[1].height = 102
for i in range(2, 2000 + 1):
ws.row_dimensions[i].height = 42
# Col width
ws.column_dimensions['A'].width = 1.5
ws.column_dimensions['B'].width = 25.0
for i in range(ord('C'), ord('L')):
ws.column_dimensions[chr(i)].width = 15.0
# Font
name_font = Font(name='Constantia', size=15, bold=True)
title_font = Font(name='宋体', size=15, bold=True)
data_font = Font(name='Franklin Gothic Book', size=11)
table_fill = PatternFill(fill_type='solid', fgColor='1F497D')
f_border = Border(left=Side(border_style='medium', color='00000000'),
right=Side(border_style='medium', color='00000000'),
bottom=Side(border_style='medium', color='00000000'),
top=Side(border_style='medium', color='00000000'))
b_border = Border(bottom=Side(border_style='medium', color='00000000'), )
b_c_alignment = Alignment(vertical='bottom',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
c_c_alignment = Alignment(vertical='center',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
b_r_alignment = Alignment(vertical='bottom',
horizontal='right',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
c_r_alignment = Alignment(vertical='bottom',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
# Img
img = Image("excelexporters/myems.png")
img.width = img.width * 0.85
img.height = img.height * 0.85
# img = Image("myems.png")
ws.add_image(img, 'B1')
# Title
ws.row_dimensions[3].height = 60
ws['B3'].font = name_font
ws['B3'].alignment = b_r_alignment
ws['B3'] = 'Name:'
ws['C3'].border = b_border
ws['C3'].alignment = b_c_alignment
ws['C3'].font = name_font
ws['C3'] = name
ws['D3'].font = name_font
ws['D3'].alignment = b_r_alignment
ws['D3'] = 'Period:'
ws['E3'].border = b_border
ws['E3'].alignment = b_c_alignment
ws['E3'].font = name_font
ws['E3'] = period_type
ws['F3'].font = name_font
ws['F3'].alignment = b_r_alignment
ws['F3'] = 'Date:'
ws['G3'].alignment = b_c_alignment
ws['G3'].font = name_font
ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
ws.merge_cells("G3:H3")
if "reporting_period" not in report.keys() or \
"difference_values" not in report['reporting_period'].keys() or \
len(report['reporting_period']['difference_values']) == 0:
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
#################################################
has_difference_values_data_flag = True
if 'difference_values' not in report['reporting_period'].keys() or len(
report['reporting_period']['difference_values']) == 0:
has_difference_values_data_flag = False
current_row_number = 6
if has_difference_values_data_flag:
reporting_period_data = report['reporting_period']
category = report['meter']['energy_category_name']
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 报告期'
current_row_number += 1
ws.row_dimensions[current_row_number].height = 60
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].border = f_border
if not isinstance(category, list):
ws['C' + str(current_row_number)].fill = table_fill
ws['C' + str(current_row_number)].font = name_font
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = report['meter'][
'energy_category_name'] + " (" + report['meter'][
'unit_of_measure'] + ")"
current_row_number += 1
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '总表消耗'
ws['C' + str(current_row_number)].font = name_font
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = round(
reporting_period_data['master_meter_consumption_in_category'],
2)
current_row_number += 1
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '分表消耗'
ws['C' + str(current_row_number)].font = name_font
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = round(
reporting_period_data['submeters_consumption_in_category'], 2)
current_row_number += 1
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '差值'
ws['C' + str(current_row_number)].font = name_font
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = round(
reporting_period_data['difference_in_category'], 2)
current_row_number += 1
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '差值百分比'
ws['C' + str(current_row_number)].font = name_font
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = str(
round(reporting_period_data['percentage_difference'] * 100,
2)) + '%'
current_row_number += 2
time = reporting_period_data['timestamps']
has_time_data_flag = False
if time is not None and len(time) > 0:
has_time_data_flag = True
if has_time_data_flag:
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 详细数据'
current_row_number += 1
chart_start_number = current_row_number
current_row_number = current_row_number + 6
table_start_number = current_row_number
ws.row_dimensions[current_row_number].height = 60
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)] = '日期时间'
ws['C' + str(current_row_number)].fill = table_fill
ws['C' + str(current_row_number)].font = title_font
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)] = report['meter'][
'energy_category_name'] + " (" + report['meter'][
'unit_of_measure'] + ")"
current_row_number += 1
for i in range(0, len(time)):
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)] = time[i]
ws['C' + str(current_row_number)].font = title_font
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)] = round(
reporting_period_data['difference_values'][i], 2)
current_row_number += 1
table_end_number = current_row_number - 1
line = LineChart()
line.title = '报告期差值 - ' + report['meter'][
'energy_category_name'] + " (" + report['meter'][
'unit_of_measure'] + ")"
labels = Reference(ws,
min_col=2,
min_row=table_start_number + 1,
max_row=table_end_number)
line_data = Reference(ws,
min_col=3,
min_row=table_start_number,
max_row=table_end_number)
line.add_data(line_data, titles_from_data=True)
line.set_categories(labels)
line_data = line.series[0]
line_data.marker.symbol = "circle"
line_data.smooth = True
line.x_axis.crosses = 'min'
line.height = 8.25
line.width = 24
line.dLbls = DataLabelList()
line.dLbls.dLblPos = 't'
line.dLbls.showVal = True # 数量显示
ws.add_chart(line, "B" + str(chart_start_number))
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)] = '总计'
ws['C' + str(current_row_number)].font = title_font
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)] = round(
reporting_period_data[
'master_meter_consumption_in_category'], 2)
else:
pass
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
def writeMeltCurves(sheet,r,c,imageFile): '''Adds a melt curve .jpeg file at the specified cell.''' image = Image(imageFile) image.anchor(sheet.cell(row=r,column=c),anchortype='oneCell') sheet.add_image(image)
from openpyxl import Workbook
from openpyxl.drawing.image import Image
Work_book = Workbook()
work_sheet = Work_book.active
img = Image("small-image.jpg")
work_sheet.add_image(img, "C11")
# Work_book.save("images.xlsx")
print(img.width, img.height)
large_img = Image("small-image.jpg")
large_img.width = 600
large_img.height = 400
# print(img.width, img.height)
work_sheet.add_image(large_img, "L11")
Work_book.save("images.xlsx")
