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'].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 \
"timestamps" not in report['reporting_period'].keys() or len(report['reporting_period']['timestamps']) == 0:
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
###############################
has_names_data_flag = True
if "names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0:
has_names_data_flag = False
current_row_number = 6
if has_names_data_flag:
reporting_period_data = report['reporting_period']
category = reporting_period_data['names']
ca_len = len(category)
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
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] + "/H)"
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)] = '平均负荷'
for i in range(0, ca_len):
col = chr(ord('C') + i)
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['averages'][i], 2) \
if reporting_period_data['averages'][i] is not None else 'N/A'
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)] = '单位面积值'
for i in range(0, ca_len):
col = chr(ord('C') + i)
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['averages_per_unit_area'][i], 2) \
if reporting_period_data['averages_per_unit_area'][i] is not None else 'N/A'
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)] = '环比'
for i in range(0, ca_len):
col = chr(ord('C') + i)
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['averages_increment_rate'][i] * 100, 2)) + "%" \
if reporting_period_data['averages_increment_rate'][i] is not None else "-"
current_row_number += 2
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
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] + "/H)"
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)] = '最大负荷'
for i in range(0, ca_len):
col = chr(ord('C') + i)
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['maximums'][i], 2) \
if reporting_period_data['maximums'][i] is not None else 'N/A'
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)] = '单位面积值'
for i in range(0, ca_len):
col = chr(ord('C') + i)
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['maximums_per_unit_area'][i], 2) \
if reporting_period_data['maximums_per_unit_area'][i] is not None else 'N/A'
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)] = '环比'
for i in range(0, ca_len):
col = chr(ord('C') + i)
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['maximums_increment_rate'][i] * 100, 2)) + "%" \
if reporting_period_data['maximums_increment_rate'][i] is not None else "-"
current_row_number += 2
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
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]
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)] = '负荷系数'
for i in range(0, ca_len):
col = chr(ord('C') + i)
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['factors'][i], 2) \
if reporting_period_data['factors'][i] is not None else '-'
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)] = '环比'
for i in range(0, ca_len):
col = chr(ord('C') + i)
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['factors_increment_rate'][i] * 100, 2)) + "%" \
if reporting_period_data['factors_increment_rate'][i] is not None else "-"
current_row_number += 2
has_sub_averages_data_flag = True
has_sub_maximums_data_flag = True
if "sub_averages" not in report['reporting_period'].keys() or len(report['reporting_period']['sub_averages']) == 0:
has_sub_averages_data_flag = False
if "sub_averages" not in report['reporting_period'].keys() or len(report['reporting_period']['sub_averages']) == 0:
has_sub_maximums_data_flag = False
if has_sub_averages_data_flag or has_sub_maximums_data_flag:
reporting_period_data = report['reporting_period']
category = reporting_period_data['names']
ca_len = len(category)
times = reporting_period_data['timestamps']
time = times[0]
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
if has_sub_averages_data_flag:
current_row_number = (current_row_number + ca_len * 6)
if has_sub_maximums_data_flag:
current_row_number = (current_row_number + ca_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)].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):
if has_sub_averages_data_flag:
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] + "/H)"
col = chr(ord(col) + 1)
if has_sub_maximums_data_flag:
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] + "/H)"
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):
if has_sub_averages_data_flag:
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['sub_averages'][j][i], 2) \
if reporting_period_data['sub_averages'][j][i] is not None else 0.00
col = chr(ord(col) + 1)
if has_sub_maximums_data_flag:
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['sub_maximums'][j][i], 2) \
if reporting_period_data['sub_maximums'][j][i] is not None else 0.00
col = chr(ord(col) + 1)
current_row_number += 1
table_end_number = current_row_number - 1
current_chart_col_number = 3
current_chart_row_number = chart_start_number
for i in range(0, ca_len):
labels = Reference(ws, min_col=2, min_row=table_start_number + 1, max_row=table_end_number)
if has_sub_averages_data_flag:
line = LineChart()
line.title = '报告期 平均负荷 - ' + ws.cell(column=current_chart_col_number, row=table_start_number).value
datas = Reference(ws, min_col=current_chart_col_number, min_row=table_start_number,
max_row=table_end_number)
line.add_data(datas, 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(current_chart_row_number))
current_chart_row_number += 6
current_chart_col_number += 1
if has_sub_maximums_data_flag:
line = LineChart()
line.title = '报告期 最大负荷 - ' + ws.cell(column=current_chart_col_number, row=table_start_number).value
datas = Reference(ws, min_col=current_chart_col_number, min_row=table_start_number,
max_row=table_end_number)
line.add_data(datas, 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(current_chart_row_number))
current_chart_row_number += 6
current_chart_col_number += 1
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
def startCheck(self):
wb = None
filename = self.fileEntry.get()
try:
wb = openpyxl.load_workbook(filename)
messagebox.showinfo('XXX様スタイルガイドツール',
os.path.basename(filename) + 'を開きます。')
except OSError:
messagebox.showinfo('XXX様スタイルガイドツール', '正しいファイルパースを選択してください。')
return
sheet = None
try:
sheet = wb['checkSheet']
except KeyError:
messagebox.showinfo('XXX様スタイルガイドツール', '英数チェックファイルを使ってください。')
return
maxRow = sheet.max_row
titleName = 'Title.xlsx.sdlxliff'
tableName = 'table'
freeName = 'Free.xlsx.sdlxliff'
imageName = '.xml'
header = sheet.cell(row=1, column=15)
header.value = 'XXX様'
header.fill = PatternFill('solid', fgColor='BFBFBF')
thin_border = Border(top=Side(border_style='thin', color='00000000'),
right=Side(border_style='thin', color='00000000'),
left=Side(border_style='thin', color='00000000'),
bottom=Side(border_style='thin',
color='00000000'))
header.border = thin_border
sheet.column_dimensions['O'].width = 30
for i in range(2, maxRow + 1):
sheet.cell(row=i, column=15).value = None
sheet.cell(row=i, column=15).alignment = Alignment(wrapText=True)
sheet.cell(row=i, column=15).border = thin_border
def errors(errVal, word, case):
if case == 'upper':
errTxt = ' は大文字ではない'
elif case == 'lower':
errTxt = ' は小文字ではない'
elif case == 'dot':
errTxt = ' のユニコードが違う'
elif case == 'singular':
errTxt = ' は複数形ではない'
elif case == 'mismatch':
errTxt = ' の訳文が合っていない'
elif case == 'delete':
errTxt = ' は翻訳対象以外'
if not errVal:
errVal = word + errTxt
else:
errVal = err.value + '\n' + word + errTxt
return errVal
def isWord(word):
nonCapWords = ["with", "per", "for", "the", "but", "from"]
if not word.isalpha():
return False
elif len(word) < 3:
return False
elif word in nonCapWords:
return False
else:
return True
def checkCount(string, src, tar):
if src.count(string) == tar.count(string):
return True
else:
return False
def tokenize(string):
punc = '!@#$%^&*()_-+={}[]:;"\'|<>,.?/~`'
for marker in punc:
text = string.replace(marker, " ")
split = text.split()
return split
for i in range(2, maxRow + 1):
t = sheet.cell(row=i, column=1)
source = sheet.cell(row=i, column=4)
target = sheet.cell(row=i, column=6)
err = sheet.cell(row=i, column=15)
src = str(source.value)
tar = str(target.value)
if src and tar:
#check 二硫化モリブデン
if '二硫化モリブデン' in src and self.lang.get() == 1:
if src.count('二硫化モリブデン') != tar.count(r'MoS<sub>2</sub>'):
err.value = errors(err.value, 'MoS<sub>2</sub>',
'mismatch')
#check dot unicode
if self.lang.get() == 1:
if '·' in str(tar):
case = 'dot'
err.value = errors(err.value, '·', case)
elif self.lang.get() == 2:
if '･' in tar:
case = 'dot'
err.value = errors(err.value, '･', case)
#check arrows
if re.compile('←\d').search(src):
if re.compile('←\d').search(src).group(0) not in tar:
err.value = errors(
err.value,
re.compile('←\d').search(src).group(0), 'mismatch')
#check 全角 space
if ' ' in src:
if self.lang.get() == 1 and ' ' not in tar:
pos = src.find(' ')
if src[pos] != 0:
starNum = re.compile(r"＊\d： ")
if not starNum.search(src):
err.value = errors(err.value, '全角スペース',
'mismatch')
if self.lang.get() == 2 and ' ' not in tar:
err.value = errors(err.value, '全角スペース', 'mismatch')
#check brackets
if '[[' in src:
if src.count('[[') != tar.count('[['):
err.value = errors(err.value, '[[', 'mismatch')
if ']]' in src:
if not checkCount(']]', src, tar):
err.value = errors(err.value, ']]', 'mismatch')
#check tab character
if r'\t' in src:
if not checkCount(r'\t', src, tar):
err.value = errors(err.value, r'\t', 'mismatch')
#check hyphens
if '----' in src:
if not checkCount('----', src, tar):
err.value = errors(err.value, '----', 'mismatch')
#check slashes
if '//' in src:
if not checkCount('//', src, tar):
err.value = errors(err.value, '//', 'mismatch')
#check bullets
if '●' in src:
if not checkCount('●', src, tar):
err.value = errors(err.value, '●', 'mismatch')
#check maru suuji
maruSuuji = '①②③④⑤⑥⑦⑧⑨⑩⑪⑫⑬⑭⑮⑯⑰⑱⑲⑳'
for num in maruSuuji:
if num in src:
if not checkCount(num, src, tar):
err.value = errors(err.value, num, 'mismatch')
#check ＊1：, etc.
if re.compile('＊\d：').search(src):
if re.compile('＊\d：').search(src).group(0) not in tar:
err.value = errors(
err.value,
re.compile('＊\d：').match(src).group(0), 'mismatch')
#check zero backslash
if 'バックラッシュ０' in src or 'バックラッシュゼロ' in src or r'バックラッシュ<rubi>0@ゼロ</rubi>' in src:
if self.lang.get() == 1:
if 'zero backslash' not in tar:
err.value = errors(err.value, 'zero backslash',
'mismatch')
elif self.lang.get() == 2:
if '零背隙' not in tar:
err.value = errors(err.value, '零背隙', 'mismatch')
#check dimensional table
if '寸法・価格表' in src or '価格表' in src:
if self.lang.get() == 1:
if 'dimensional table' not in tar:
err.value = errors(err.value, 'dimensional table',
'mismatch')
elif self.lang.get() == 2:
if '尺寸表' not in tar:
err.value = errors(err.value, '尺寸表', 'mismatch')
#check 価格
elif '価格' in src:
if '【データ上で消去】' not in tar:
err.value = '価格は翻訳対象以外'
else:
err.value = ''
#check 単価:
if '単価' in src:
if '【データ上で消去】' not in tar:
err.value = '単価は翻訳対象以外'
else:
err.value = ''
#check 円
if '円' in src:
if '【データ上で消去】' not in tar:
err.value = '円は翻訳対象以外'
else:
err.value = ''
#check 納期
if '納期' in src:
if '【データ上で消去】' not in tar:
err.value = '納期は翻訳対象以外'
#check 出荷
if '出荷' in src and '【データ上で消去】' not in tar:
err.value = errors(err.value, '出荷', 'delete')
import pandas as pd
from pandas import DataFrame
from openpyxl.drawing.image import Image
#activate a spreadsheet
gravcalc = Workbook()
sheet_one = gravcalc.active
sheet_one.title = 'Workings'
#create an extra sheet for the formulae
sheet_two = gravcalc.create_sheet('Formulae', 1)
#format the spreadsheet
bold_font = Font(bold=True, color=colors.BLUE, size=10)
font_alignment = Alignment(horizontal='center', vertical='center')
font_border = Border(bottom=Side(border_style='thin'))
sheet_one.freeze_panes = "B1"
#add titles to the spreadsheet
sheet_one["A1"] = "GRAVITY STATION"
sheet_one["A1"].font = bold_font
sheet_one["A3"] = "FIRST BASE STATION"
sheet_one["A3"].font = bold_font
sheet_one["A4"] = "LAST BASE STATION"
sheet_one["A4"].font = bold_font
sheet_one["B1"] = "TIME(hr)"
sheet_one["B1"].font = bold_font
sheet_one["B2"] = "(hr)"
sheet_one["B2"].font = bold_font
sheet_one["C1"] = "TIME(min)"
colum = [
'Объем ДД', 'Значение приоритета корректировки', 'Доля ГЭС', 'Доля ТЭС',
'Номер пакета ДД'
]
dd_1.drop(colum, axis=1, inplace=True)
dd_1.drop(dd_1[dd_1['Код ГТПП Покупателя'] != 'PINTCHIN PINTCHN1'].index,
axis=0,
inplace=True)
vc_pc['TARGET_DATE'] = vc_pc['TARGET_DATE'].astype(str)
sec_f['MONTH'] = sec_f['MONTH'].astype(str)
# Экспортируем Excel
dd_1.to_excel(path_2, sheet_name='ДД ИНТЕРРАО в НЦЗ', index=False)
vc_pc.to_excel(path_3, sheet_name='VC_PC', index=False)
sec_f.to_excel(path_4, sheet_name='Факт ИНТЕРРАО в НЦЗ', index=False)
# Создаем шаблоны стилей
border = Border(left=Side(border_style='thin', color='FF000000'),
right=Side(border_style='thin', color='FF000000'),
top=Side(border_style='thin', color='FF000000'),
bottom=Side(border_style='thin', color='FF000000'))
align_head = Alignment(horizontal='center',
vertical='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=True,
indent=0)
align_cell = Alignment(horizontal='right',
vertical='center',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
from openpyxl.styles import PatternFill
from openpyxl.styles import Font
from openpyxl.styles import Border, Side
from openpyxl.drawing.image import Image
import subprocess
wb = load_workbook("simpleWeatherSimply.xlsx")
ws = wb.active
# calculate total number of rows and
# columns in source excel file
mr = ws.max_row
mc = ws.max_column
thin_border = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
blueFill = PatternFill(start_color='FF0000FF',
end_color='FF0000FF',
fill_type='solid')
cyanFill = PatternFill(start_color='FF40E0D0',
end_color='FF40E0D0',
fill_type='solid')
greenFill = PatternFill(start_color='FF00FF00',
end_color='FF00FF00',
fill_type='solid')
yellowFill = PatternFill(start_color='FFFFFF00',
end_color='FFFFFF00',
def generate_excel(report, name, reporting_start_datetime_local,
reporting_end_datetime_local, period_type):
wb = Workbook()
ws = wb.active
ws.title = "CombinedEquipmentEfficiency"
# 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('R')):
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_efficiency" not in report.keys() or \
"names" not in report['reporting_period_efficiency'].keys() or len(
report['reporting_period_efficiency']['names']) == 0:
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
####################################################################################################################
current_row_number = 6
reporting_period_data = report['reporting_period_efficiency']
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 + ' ' + 'Reporting Period Cumulative Efficiency'
current_row_number += 1
category = reporting_period_data['names']
ca_len = len(category)
ws.row_dimensions[current_row_number].height = 80
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)
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['numerator_names'][i] + " (" + \
reporting_period_data['numerator_units'][i] + ")"
col = chr(ord(col) + 1)
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['denominator_names'][i] + " (" + \
reporting_period_data['denominator_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)] = 'Cumulative Efficiency'
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['cumulations'][i], 2)
col = chr(ord(col) + 1)
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['numerator_cumulations'][i], 2)
col = chr(ord(col) + 1)
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['denominator_cumulations'][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)] = 'Increment Rate'
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)
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_num'][i] * 100, 2)) + '%' \
if reporting_period_data['increment_rates_num'][i] is not None else '-'
col = chr(ord(col) + 1)
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_den'][i] * 100, 2)) + '%' \
if reporting_period_data['increment_rates_den'][i] is not None else '-'
col = chr(ord(col) + 1)
current_row_number += 2
####################################################################################################################
has_parameters_names_and_timestamps_and_values_data = True
current_sheet_parameters_row_number = current_row_number
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
#####################################
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'])
de_len = len(reporting_period_data['denominator_names'])
nu_len = len(reporting_period_data['numerator_names'])
time = reporting_period_data['timestamps'][0]
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number
)] = name + ' ' + 'Reporting Period Cumulative Efficiency'
current_row_number += 1
chart_start_row_number = current_row_number
current_row_number += ca_len * 6 + de_len * 6 + nu_len * 6 + 1
if has_parameters_names_and_timestamps_and_values_data:
current_sheet_parameters_row_number = current_row_number
real_timestamps_len = timestamps_data_not_equal_0(
report['parameters']['timestamps'])
current_row_number += 6 * real_timestamps_len + 2
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' ' + 'Detailed Data'
current_row_number += 1
table_start_row_number = current_row_number
ws.row_dimensions[current_row_number].height = 85
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)] = 'Datetime'
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)
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['numerator_names'][i] + " (" + \
reporting_period_data['numerator_units'][i] + ")"
col = chr(ord(col) + 1)
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['denominator_names'][i] + " (" + \
reporting_period_data['denominator_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)
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['numerator_values'][j][i], 2) \
if reporting_period_data['numerator_values'][j][i] is not None else 0.00
col = chr(ord(col) + 1)
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['denominator_values'][j][i], 2) \
if reporting_period_data['denominator_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)] = 'Subtotal'
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['cumulations'][i], 2)
col = chr(ord(col) + 1)
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['numerator_cumulations'][i], 2)
col = chr(ord(col) + 1)
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['denominator_cumulations'][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
i_num = 0
for i in range(0, ca_len):
line = LineChart()
line.title = 'Reporting Period Cumulative Efficiency - ' + \
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_num,
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)
i_num += 1
line = LineChart()
line.title = 'Reporting Period Cumulative Efficiency - ' + \
reporting_period_data['names'][i] + '-' + reporting_period_data['numerator_names'][i] + " (" + \
reporting_period_data['numerator_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_num,
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)
i_num += 1
line = LineChart()
line.title = 'Reporting Period Cumulative Efficiency - ' + \
reporting_period_data['names'][i] + '-' +reporting_period_data['denominator_names'][i] + " (" + \
reporting_period_data['denominator_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_num,
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)
i_num += 1
################################################################################################################
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 + ' ' + 'Associated Equipment Data'
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)] = 'Associated Equipment'
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
####################################################################################################################
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
class A205XLSXNode:
white_space_multiplier = 4
cell_border = Border(left=Side(border_style='thin', color='000000'),
right=Side(border_style='thin', color='000000'),
top=Side(border_style='thin', color='000000'),
bottom=Side(border_style='thin', color='000000'))
unused_style = NamedStyle(name="Unused",
fill=PatternFill(start_color='808B96',
end_color='808B96',
fill_type='solid'))
tile_style = NamedStyle(name="Title",
fill=PatternFill(start_color='00529B',
end_color='00529B',
fill_type='solid'),
font=Font(color="FFFFFF", bold=True, sz=14))
heading_style = NamedStyle(name="Heading",
fill=PatternFill(start_color='01AED8',
end_color='01AED8',
fill_type='solid'),
font=Font(bold=True),
border=cell_border)
schema_style = NamedStyle(name="Schema",
fill=PatternFill(start_color='D9D9D9',
end_color='D9D9D9',
fill_type='solid'),
font=Font(bold=True),
border=cell_border)
grid_var_style = NamedStyle(name="Grid Variables",
fill=PatternFill(start_color='D9D9D9',
end_color='D9D9D9',
fill_type='solid'),
font=Font(bold=True, color='0070C0'),
border=cell_border)
value_style = NamedStyle(name="Value",
fill=PatternFill(start_color='FFFFFF',
end_color='FFFFFF',
fill_type='solid'),
border=cell_border)
def __init__(self,
name,
parent=None,
tree=None,
value=None,
sheet_ref=None,
option=None):
self.children = [] # List of children A205XLSXNodes
self.name = name # Name of this node
self.value = value # Value (if any) of this node
self.sheet_ref = sheet_ref # Reference to another sheet (if applicable)
self.parent = parent # Parent A205XLSXNode of this node
self.grid_set = None # Ordered arrays of repeated grid variable values (used only for grid_variable nodes)
if parent:
# Inherit much information from parent
self.lineage = self.parent.lineage + [
name
] # List of parent node names (as strings)
self.options = self.parent.options + [option]
self.tree = self.parent.tree
self.sheet = self.parent.child_sheet
self.sheet_type = self.parent.child_sheet_type
self.beg = self.parent.next_child_beg
if self.sheet_type == SheetType.FLAT:
self.next_child_beg = self.beg + 1
else:
self.next_child_beg = self.beg
else:
# Root node
self.lineage = []
self.options = []
self.tree = tree
self.sheet = self.tree.schema_type
self.sheet_type = SheetType.FLAT
self.child_sheet_type = self.sheet_type
self.beg = 3
self.next_child_beg = 3
if self.sheet not in self.tree.sheets:
self.tree.sheets.append(self.sheet)
# These will be changed by any children
self.end = self.beg
self.increment_ancestors()
self.child_sheet = self.sheet
self.child_sheet_type = self.sheet_type
# Initial detection of new sheets
if self.sheet_ref:
# Indicator of pointer to another sheet
if "performance_map" in self.sheet_ref:
self.child_sheet_type = SheetType.PERFORMANCE_MAP
elif "_representation" in self.sheet_ref:
self.child_sheet_type = SheetType.FLAT
else:
self.child_sheet_type = SheetType.ARRAY
if self.child_sheet_type == SheetType.FLAT:
self.next_child_beg = 3
else:
self.next_child_beg = 1
self.child_sheet = self.sheet_ref
if parent:
self.parent.add_child(self)
def add_child(self, node):
'''
Add a child node to this node.
'''
self.children.append(node)
def get_num_ancestors(self):
'''
Count ancestors back to root.
'''
if self.parent:
return len(self.parent.lineage)
else:
return 0
def get_ancestor(self, generation):
if generation == 0:
return self
else:
return self.parent.get_ancestor(generation - 1)
def increment_ancestors(self):
'''
Increment the begining and endings appropriately for ancestors
'''
if self.parent:
if self.sheet == self.parent.sheet:
# If parent is in the same sheet increment both
self.parent.end = self.end
self.parent.increment_ancestors()
self.parent.next_child_beg = self.end + 1
def add_grid_set(self, grid_set):
'''
Add a cartesian product grid set to this node.
Only used for "grid_variables" nodes.
'''
if self.name != 'grid_variables':
raise Exception(
f"Cannot add a grid set to '{self.name}'. Grid sets can only be added to 'grid_variables' nodes."
)
self.grid_set = grid_set
def get_schema_node(self):
'''
Search for schema content for this node
'''
return self.tree.schema.get_schema_node(self.lineage, self.options)
def is_required(self):
'''
Check schema if this is a required node
'''
if self.parent:
parent_schema_node = self.parent.get_schema_node()
if 'required' in parent_schema_node:
return self.name in parent_schema_node['required']
else:
return False
else:
return True
def fill_sheet(self, worksheet):
for column in [x for x in string.ascii_uppercase
] + ["A" + x for x in string.ascii_uppercase]:
worksheet.column_dimensions[column].fill = self.unused_style.fill
worksheet[f"{column}1"].style = self.tile_style
def write_header(self, worksheet):
'''
Write the header data for a new sheet
'''
self.fill_sheet(worksheet)
if self.parent:
if self.sheet == self.parent.sheet:
# A simple array (not a nested object)
worksheet.cell(row=1, column=1).value = '.'.join(self.lineage)
return
else:
worksheet.cell(row=1,
column=1).value = '.'.join(self.parent.lineage)
else:
worksheet.cell(
row=1, column=1
).value = f"{self.tree.schema_type}: {self.tree.schema.get_rs_title(self.tree.schema_type)}"
if self.sheet_type == SheetType.FLAT:
xlsx_headers = [
'Data Group', 'Data Element', 'Value', 'Units', 'Required'
]
for column, header in enumerate(xlsx_headers, start=1):
worksheet.cell(row=2, column=column).value = header
worksheet.cell(row=2, column=column).style = self.heading_style
worksheet.column_dimensions['B'].width = 50
worksheet.column_dimensions['C'].width = 31
def write_node(self):
'''
Write tree content to XLSX
'''
wb = self.tree.workbook
sheet = self.sheet
if sheet not in wb:
wb.create_sheet(sheet)
self.write_header(wb[sheet])
schema_node = self.get_schema_node()
if self.name is not None:
if self.sheet_type == SheetType.FLAT:
if len(self.children) > 0:
level_index = 1
wb[sheet].cell(row=self.beg,
column=level_index).value = '.'.join(
self.lineage)
else:
level_index = 2
buffer = ' ' * (
self.get_num_ancestors() - 1
) * self.white_space_multiplier # TODO: get_num_ancestors_to_root for embedded RS sheets?
wb[sheet].cell(
row=self.beg,
column=level_index).value = buffer + self.name
wb[sheet].cell(row=self.beg,
column=1).style = self.schema_style
wb[sheet].cell(row=self.beg,
column=2).style = self.schema_style
wb[sheet].cell(row=self.beg,
column=4).style = self.schema_style
wb[sheet].cell(row=self.beg,
column=5).style = self.schema_style
wb[sheet].cell(row=self.beg, column=3).style = self.value_style
if schema_node:
# Add description
if 'description' in schema_node:
comment = openpyxl.comments.Comment(
schema_node['description'], "ASHRAE 205")
wb[sheet].cell(row=self.beg,
column=level_index).comment = comment
# Enum validation
if 'enum' in schema_node:
enumerants = f'"{",".join(schema_node["enum"])}"'
if len(
enumerants
) < 256: # Apparent limitation of written lists (TODO: https://stackoverflow.com/a/33532984/1344457)
dv = openpyxl.worksheet.datavalidation.DataValidation(
type='list',
formula1=enumerants,
allow_blank=True)
wb[sheet].add_data_validation(dv)
dv.add(wb[sheet].cell(row=self.beg, column=3))
# Boolean validation
if 'type' in schema_node:
if schema_node['type'] == 'boolean':
dv = openpyxl.worksheet.datavalidation.DataValidation(
type='list',
formula1='"TRUE,FALSE"',
allow_blank=True)
wb[sheet].add_data_validation(dv)
dv.add(wb[sheet].cell(row=self.beg, column=3))
# Add units
if 'units' in schema_node:
wb[sheet].cell(row=self.beg,
column=4).value = schema_node['units']
# Add required
# TODO: Make conditional formatting (e.g. red name if not entered)
if self.is_required():
wb[sheet].cell(row=self.beg,
column=5).value = u'\u2713' # Checkmark
wb[sheet].cell(
row=self.beg,
column=5).alignment = Alignment(horizontal='center')
else:
# Not found in schema
comment = openpyxl.comments.Comment(
"Not found in schema.", "ASHRAE 205")
wb[sheet].cell(row=self.beg,
column=level_index).comment = comment
wb[sheet].cell(row=self.beg,
column=level_index).font = Font(
color='FF0001', bold=True)
if self.sheet_ref:
wb[sheet].cell(row=self.beg,
column=3).value = '$' + self.sheet_ref
# Hyperlink to referenced sheets
wb[sheet].cell(
row=self.beg,
column=3).hyperlink = f"#{self.sheet_ref}!A1"
if (self.child_sheet_type == SheetType.ARRAY
and len(self.children) == 0):
# Make sheet for holding array values
array_sheet = unique_name_with_index(
self.child_sheet, self.tree.sheets)
wb.create_sheet(array_sheet)
self.write_header(wb[array_sheet])
wb[array_sheet].cell(row=2, column=1).value = self.name
wb[array_sheet].cell(
row=2, column=1).style = self.schema_style
wb[array_sheet].cell(
row=3, column=1).style = self.schema_style
if schema_node:
if 'units' in schema_node:
wb[array_sheet].cell(
row=3,
column=1).value = schema_node['units']
row = 4
if self.value:
for value in self.value:
wb[array_sheet].cell(row=row,
column=1).value = value
wb[array_sheet].cell(
row=row, column=1).style = self.value_style
row += 1
else:
array_length = 5
if schema_node:
if 'maxItems' in schema_node:
array_length = schema_node['maxItems']
for i in range(array_length):
wb[array_sheet].cell(
row=row, column=1).style = self.value_style
row += 1
if self.value is not None and self.sheet_ref is None:
wb[sheet].cell(row=self.beg, column=3).value = self.value
# TODO: Something better here...a lot of repetition...
elif self.sheet_type == SheetType.PERFORMANCE_MAP:
if len(self.children) > 0:
level_index = 2
else:
level_index = 3
wb[sheet].cell(row=level_index,
column=self.beg).value = self.name
if '_variables' in self.parent.name:
if self.parent.name == 'grid_variables':
wb[sheet].cell(
row=2, column=self.beg).style = self.grid_var_style
wb[sheet].cell(
row=3, column=self.beg).style = self.grid_var_style
wb[sheet].cell(
row=4, column=self.beg).style = self.grid_var_style
wb[sheet].cell(row=level_index,
column=self.beg).alignment = Alignment(
text_rotation=45)
if self.parent.grid_set:
row = 5
for value in self.parent.grid_set[self.name]:
wb[sheet].cell(
row=row,
column=self.beg).style = self.value_style
wb[sheet].cell(row=row,
column=self.beg).value = value
row += 1
else:
# 2^n rows for spacing
row = 5
for i in range(2**(len(self.parent.children))):
wb[sheet].cell(
row=row,
column=self.beg).style = self.value_style
row += 1
else:
wb[sheet].cell(
row=2, column=self.beg).style = self.schema_style
wb[sheet].cell(
row=3, column=self.beg).style = self.schema_style
wb[sheet].cell(
row=4, column=self.beg).style = self.schema_style
wb[sheet].cell(row=level_index,
column=self.beg).alignment = Alignment(
text_rotation=45)
if self.value is not None:
row = 5
for value in self.value:
wb[sheet].cell(
row=row,
column=self.beg).style = self.value_style
wb[sheet].cell(row=row,
column=self.beg).value = value
row += 1
else:
# 2^n rows for spacing
row = 5
for i in range(2**(len(
self.parent.parent.children[0].children))):
wb[sheet].cell(
row=row,
column=self.beg).style = self.value_style
row += 1
if schema_node:
# Add units
if 'units' in schema_node:
wb[sheet].cell(
row=4,
column=self.beg).value = schema_node['units']
# Add required
# TODO: Make conditional formatting (e.g. red name if not entered)
# Add description
if 'description' in schema_node:
comment = openpyxl.comments.Comment(
schema_node['description'], "ASHRAE 205")
wb[sheet].cell(row=level_index,
column=self.beg).comment = comment
else:
# Not found in schema
comment = openpyxl.comments.Comment(
"Not found in schema.", "ASHRAE 205")
wb[sheet].cell(row=level_index,
column=self.beg).comment = comment
wb[sheet].cell(row=level_index,
column=self.beg).font = Font(color='FF0001',
bold=True)
# TODO: Something better here...a lot of repetition...
elif self.sheet_type == SheetType.ARRAY:
if len(self.children) > 0:
raise Exception(
"Were not handling nested items in an array yet!")
wb[sheet].cell(row=2, column=self.beg).value = self.name
wb[sheet].cell(row=2,
column=self.beg).style = self.schema_style
wb[sheet].cell(row=3,
column=self.beg).style = self.schema_style
row = 4
if self.value is not None:
for value in self.value:
wb[sheet].cell(row=row, column=self.beg).value = value
wb[sheet].cell(
row=row, column=self.beg).style = self.value_style
if schema_node:
# Enum validation
if 'enum' in schema_node:
enumerants = f'"{",".join(schema_node["enum"])}"'
if len(
enumerants
) < 256: # Apparent limitation of written lists (TODO: https://stackoverflow.com/a/33532984/1344457)
dv = openpyxl.worksheet.datavalidation.DataValidation(
type='list',
formula1=enumerants,
allow_blank=True)
wb[sheet].add_data_validation(dv)
dv.add(wb[sheet].cell(row=row,
column=self.beg))
row += 1
else:
array_length = 5
parent_schema_node = self.parent.get_schema_node()
if parent_schema_node:
if 'maxItems' in parent_schema_node:
array_length = parent_schema_node['maxItems']
for i in range(array_length):
wb[sheet].cell(
row=row, column=self.beg).style = self.value_style
if schema_node:
# Enum validation
if 'enum' in schema_node:
enumerants = f'"{",".join(schema_node["enum"])}"'
if len(
enumerants
) < 256: # Apparent limitation of written lists (TODO: https://stackoverflow.com/a/33532984/1344457)
dv = openpyxl.worksheet.datavalidation.DataValidation(
type='list',
formula1=enumerants,
allow_blank=True)
wb[sheet].add_data_validation(dv)
dv.add(wb[sheet].cell(row=row,
column=self.beg))
row += 1
if schema_node:
# Add units
if 'units' in schema_node:
wb[sheet].cell(
row=3,
column=self.beg).value = schema_node['units']
# Add required
# TODO: Make conditional formatting (e.g. red name if not entered)
# Add description
if 'description' in schema_node:
comment = openpyxl.comments.Comment(
schema_node['description'], "ASHRAE 205")
wb[sheet].cell(row=2,
column=self.beg).comment = comment
else:
# Not found in schema
comment = openpyxl.comments.Comment(
"Not found in schema.", "ASHRAE 205")
wb[sheet].cell(row=2, column=self.beg).comment = comment
wb[sheet].cell(row=2,
column=self.beg).font = Font(color='FF0001',
bold=True)
for child in self.children:
child.write_node()
def read_node(self):
'''
Translate XLSX content into nodes of a tree.
'''
ws = self.tree.workbook[self.child_sheet]
end_node = False
while not end_node:
if self.child_sheet_type == SheetType.PERFORMANCE_MAP:
# Everything from the perspective of parent node
data_group = ws.cell(row=2, column=self.next_child_beg).value
data_element = ws.cell(row=3, column=self.next_child_beg).value
if data_group and data_group != self.name:
if data_group == 'grid_variables':
new_node = A205XLSXNode(data_group, parent=self)
new_node.add_grid_set({})
new_node.read_node()
elif data_group == 'lookup_variables':
if self.name == 'grid_variables':
# We hit the end of grid variables and need to conclude that node
# process grid set
grid_set = {}
for child in self.children:
grid_set[child.name] = child.value
self.add_grid_set(grid_set)
# check grid set?
grid_vars = process_grid_set(grid_set)
# reset grid variable values
for child in self.children:
child.value = grid_vars[child.name]
end_node = True
else:
new_node = A205XLSXNode(data_group, parent=self)
new_node.read_node()
elif data_element:
if self.name not in ['grid_variables', 'lookup_variables']:
raise Exception(
f"Invalid data group: '{self.name}'. Data groups in {self.parent.name} should be 'grid_variables' or 'lookup_variables'"
)
row = 5
end_of_column = False
value = []
while not end_of_column:
item = ws.cell(row=row,
column=self.next_child_beg).value
if item is not None:
value.append(item)
row += 1
else:
end_of_column = True
new_node = A205XLSXNode(data_element,
parent=self,
value=value)
else:
# End of sheet
end_node = True
elif self.child_sheet_type == SheetType.ARRAY:
# Everything from the perspective of parent node
data_element = ws.cell(row=2, column=self.next_child_beg).value
if data_element:
row = 4
end_of_column = False
value = []
while not end_of_column:
item = ws.cell(row=row,
column=self.next_child_beg).value
if item is not None:
value.append(item)
row += 1
else:
end_of_column = True
new_node = A205XLSXNode(data_element,
parent=self,
value=value)
else:
# End of sheet
end_node = True
else: # Flat Sheets
data_group = ws.cell(row=self.next_child_beg, column=1).value
data_element = ws.cell(row=self.next_child_beg, column=2).value
cell_value = ws.cell(row=self.next_child_beg, column=3).value
value = cell_value
sheet_ref = None
if type(cell_value) == str:
if cell_value[0] == '$':
value = None
sheet_ref = cell_value[1:]
if data_group:
lineage = data_group.split(".")
if len(lineage) <= self.get_num_ancestors(
) + 1 and self.parent is not None:
# if lineage the same or shorter, this is not going to be a child node
end_node = True
else:
new_node = A205XLSXNode(lineage[-1],
parent=self,
value=value,
sheet_ref=sheet_ref)
new_node.read_node()
elif data_element:
if sheet_ref:
# Get array values from another sheet
row = 4
end_of_column = False
value = []
while not end_of_column:
item = self.tree.workbook[sheet_ref].cell(
row=row, column=1).value
if item is not None:
value.append(item)
row += 1
else:
end_of_column = True
# Determine hierarchy level using number of spaces
level = (len(data_element) - len(data_element.lstrip(' '))
) / self.white_space_multiplier
data_element = data_element.strip(' ')
generations = self.get_num_ancestors() - level
if generations > 0:
end_node = True
A205XLSXNode(data_element,
parent=self.get_ancestor(generations),
value=value,
sheet_ref=sheet_ref)
else:
# End of sheet
end_node = True
def collect_content(self, content):
'''
Collect content from the tree and return it as a Python Dict.
Used primarily to load contents from an XLSX sheet.
'''
if len(self.children) > 0:
if self.child_sheet_type == SheetType.ARRAY:
# Add list elements
content[self.name] = []
length = len(self.children[0].value)
for i in range(length):
item = {}
for child in self.children:
if len(child.children) > 0:
raise Exception(
"Array sheets can only be one level deep!")
item[child.name] = child.value[i]
content[self.name].append(item)
else:
if self.name:
content[self.name] = {}
for child in self.children:
child.collect_content(content[self.name])
else:
for child in self.children:
child.collect_content(content)
else:
content[self.name] = self.value
def get(self, request, *args, **kwargs):
query = TiempoMuertonDet.objects.all()
wb = Workbook()
ws = wb.active
ws.tittle = 'Tiempos Muertos'
#Establer el nombre del archivo
nombre_archivo = "Reporte Tiempos Muertosw.xlsx"
ws['B1'].alignment = Alignment(horizontal='left', vertical='center')
ws['B1'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['B1'].fill = PatternFill(start_color='66FFCC',
end_color='66FFCC',
fill_type='solid')
ws['B1'].font = Font(name='calibri', size=12, bold=True)
ws['B1'] = 'Company'
ws.merge_cells('B1:F1')
ws['B2'].alignment = Alignment(horizontal='left', vertical='center')
ws['B2'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['B2'].fill = PatternFill(start_color='66FFCC',
end_color='66FFCC',
fill_type='solid')
ws['B2'].font = Font(name='calibri', size=12, bold=True)
ws['B2'] = 'Department'
ws.merge_cells('B2:F2')
ws['B3'].alignment = Alignment(horizontal='left', vertical='center')
ws['B3'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['B3'].fill = PatternFill(start_color='66FFCC',
end_color='66FFCC',
fill_type='solid')
ws['B3'].font = Font(name='calibri', size=12, bold=True)
ws['B3'] = 'Reporte de Tiempos Muertos'
ws.merge_cells('B3:F3')
ws.row_dimensions[1].height = 20
ws.row_dimensions[2].height = 20
ws.row_dimensions[3].height = 20
ws.column_dimensions['B'].width = 20
ws.column_dimensions['C'].width = 20
ws.column_dimensions['D'].width = 20
ws.column_dimensions['E'].width = 20
ws['B6'].alignment = Alignment(horizontal='center', vertical='center')
ws['B6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['B6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['B6'].font = Font(name='calibri', size=11, bold=True)
ws['B6'] = 'Fecha'
ws['C6'].alignment = Alignment(horizontal='center', vertical='center')
ws['C6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['C6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['C6'].font = Font(name='calibri', size=11, bold=True)
ws['C6'] = 'Planta'
controlador = 7
for q in query:
ws.cell(row=controlador,
column=7).alignment = Alignment(horizontal='center',
vertical='center')
ws.cell(row=controlador,
column=7).border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws.cell(row=controlador,
column=7).fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws.cell(row=controlador, column=7).font = Font(name='calibri',
size=11,
bold=True)
ws.cell(row=controlador, column=7).value = str(q.causa)
ws.cell(row=controlador,
column=7).alignment = Alignment(horizontal='center',
vertical='center')
ws.cell(row=controlador,
column=7).border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws.cell(row=controlador,
column=7).fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws.cell(row=controlador, column=7).font = Font(name='calibri',
size=11,
bold=True)
ws.cell(row=controlador, column=7).value = q.cantidad
ws.cell(row=controlador,
column=8).alignment = Alignment(horizontal='center',
vertical='center')
ws.cell(row=controlador,
column=8).border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws.cell(row=controlador,
column=8).fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws.cell(row=controlador, column=8).font = Font(name='calibri',
size=11,
bold=True)
ws.cell(row=controlador, column=8).value = q.obs
id_enc = q.tiempo_muerto_id
query2 = TiempoMuertoEnc.objects.filter(id=id_enc)
for x in query2:
ws.cell(row=controlador,
column=2).alignment = Alignment(horizontal='center',
vertical='center')
ws.cell(row=controlador, column=2).border = Border(
left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws.cell(row=controlador,
column=2).fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws.cell(row=controlador, column=2).font = Font(name='calibri',
size=11,
bold=True)
ws.cell(row=controlador, column=2).value = x.fecha_produccion
ws.cell(row=controlador,
column=3).alignment = Alignment(horizontal='center',
vertical='center')
ws.cell(row=controlador, column=3).border = Border(
left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws.cell(row=controlador,
column=3).fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws.cell(row=controlador, column=3).font = Font(name='calibri',
size=11,
bold=True)
ws.cell(row=controlador, column=3).value = str(x.planta)
ws.cell(row=controlador,
column=4).alignment = Alignment(horizontal='center',
vertical='center')
ws.cell(row=controlador, column=4).border = Border(
left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws.cell(row=controlador,
column=4).fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws.cell(row=controlador, column=4).font = Font(name='calibri',
size=11,
bold=True)
ws.cell(row=controlador, column=4).value = str(x.linea)
ws.cell(row=controlador,
column=5).alignment = Alignment(horizontal='center',
vertical='center')
ws.cell(row=controlador, column=5).border = Border(
left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws.cell(row=controlador,
column=5).fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws.cell(row=controlador, column=5).font = Font(name='calibri',
size=11,
bold=True)
ws.cell(row=controlador, column=5).value = str(x.supervisor)
ws.cell(row=controlador,
column=5).alignment = Alignment(horizontal='center',
vertical='center')
ws.cell(row=controlador, column=5).border = Border(
left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws.cell(row=controlador,
column=5).fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws.cell(row=controlador, column=5).font = Font(name='calibri',
size=11,
bold=True)
ws.cell(row=controlador, column=5).value = str(x.turno)
controlador += 1
response = HttpResponse(content_type='application/ms-excel')
contenido = "attachment; filename = {0}".format(nombre_archivo)
response["Content-Disposition"] = contenido
wb.save(response)
return response
from openpyxl import load_workbook
from openpyxl.styles import Border, Side
wb = load_workbook('26.sales_turnover.xlsx')
ws = wb.active
black_thin = Side(color='000000', border_style='thin')
border = Border(left=black_thin,
right=black_thin,
top=black_thin,
bottom=black_thin)
for row in ws.iter_rows(min_row=2, min_col=2):
for cell in row:
cell.border = border
wb.save('26.sales_turnover_changed.xlsx')
def get_styles():
font_main = Font(name='Times New Roman',
size=12,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
font_bold = Font(name='Times New Roman',
size=12,
bold=True,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
font_bold_s = Font(name='Times New Roman',
size=10,
bold=True,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
font_calibri = Font(name='Calibri',
size=11,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
font_arial = Font(name='Arial Cyr',
size=12,
bold=False,
italic=True,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
fill = PatternFill(fill_type='solid',
start_color='c1c1c1',
end_color='c2c2c2')
border = Border(left=Side(border_style='thin', color='FF000000'),
right=Side(border_style='thin', color='FF000000'),
top=Side(border_style='thin', color='FF000000'),
bottom=Side(border_style='thin', color='FF000000'),
diagonal=Side(border_style='thin', color='FF000000'),
diagonal_direction=0,
outline=Side(border_style='thin', color='FF000000'),
vertical=Side(border_style='thin', color='FF000000'),
horizontal=Side(border_style='thin', color='FF000000'))
align_center = Alignment(horizontal='center',
vertical='center',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
align_center2 = Alignment(horizontal='center',
vertical='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
align_left = Alignment(horizontal='left',
vertical='center',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
align_right = Alignment(horizontal='right',
vertical='center',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
header_font = Font(name='Times New Roman',
size=9,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
return (font_main, font_bold, font_bold_s, font_calibri, font_arial, fill,
border, align_center, align_center2, align_left, align_right,
header_font)
def exam_scores(exam_id):
# Читаем данные из базы данных
exam = Exam.objects.select_related('course').get(id=exam_id)
group = exam.course.group
exam_points = exam.exammarks_set.all().order_by('student__FIO')
semester = exam.course.discipline_detail.semester.name
if int(semester) % 2 == 1:
additional = int(semester) // 2
else:
additional = (int(semester) - 1) // 2
edu_period = EduPeriod.objects.get(begin_year__year=group.begin_year.year +
additional)
# Открываем шаблон
app_dir = os.path.dirname(os.path.abspath(__file__))
template = os.path.join(app_dir, 'exam_scores.xlsx')
workbook = load_workbook(template)
ws = workbook.active
# Семестр
ws['A5'] = ws['L5'] = 'Семестр: {}, {} – {} уч.г.'.format(
semester,
edu_period.begin_year.year,
edu_period.end_year.year,
)
# Форма контроля
ws['A6'] = ws['L6'] = 'Форма контроля: {}, курс: {}, группа: {}'.format(
exam.get_controlType_display(),
group.year,
group.Name,
)
# Дисциплина
ws['A7'] = ws[
'L7'] = 'Дисциплина: ' + exam.course.discipline_detail.discipline.Name
# ФИО преподавателя
ws['A8'] = ws[
'L8'] = 'Фамилия, имя, отчество преподавателя: ' + exam.course.lecturer.FIO
# Дата
ws['A9'] = ws[
'L9'] = 'Дата проведения зачета/экзамена: {:%d.%m.%Y}'.format(
exam.examDate)
# Таблица с баллами
summary = {
ExamMarks.MARKS[0][0]: 0,
ExamMarks.MARKS[1][0]: 0,
ExamMarks.MARKS[2][0]: 0,
ExamMarks.MARKS[3][0]: 0,
ExamMarks.MARKS[4][0]: 0,
ExamMarks.MARKS[5][0]: 0,
ExamMarks.MARKS[6][0]: 0,
ExamMarks.MARKS[7][0]: 0,
ExamMarks.MARKS[8][0]: 0,
ExamMarks.MARKS[9][0]: 0
}
k = 0
for points in exam_points:
k += 1
ws.insert_rows(11 + k)
ws.row_dimensions[11 + k].height = 30
row = str(11 + k)
ws['A' + row] = ws['L' + row] = str(k)
ws['B' + row] = ws['M' + row] = points.student.FIO
ws['C' + row] = ws['N' + row] = points.student.student_id
ws['D' + row] = ws['O' +
row] = points.inPoints + points.additional_points
ws['E' + row] = ws['P' + row] = points.examPoints
ws['F' + row] = ws['Q' + row] = points.total_points
ws['G' + row] = ws['R' + row] = points.get_mark_display()
ws['H' + row] = ws['S' + row] = points.mark_symbol
summary[points.mark] += 1
# Стиль для ячеек таблицы
solid_line = Side(style='thin', color='000000')
cell_style = NamedStyle(name='cell_style')
cell_style.alignment.horizontal = 'center'
cell_style.alignment.vertical = 'center'
cell_style.alignment.wrapText = Bool(True)
cell_style.border = Border(left=solid_line,
right=solid_line,
top=solid_line,
bottom=solid_line)
cell_style.font = Font(name='Arial', size=9)
cell_style.number_format = '#.0'
# Применяем стили к таблице
for i in range(12, k + 12):
for j in range(1, 10):
ws.cell(row=i,
column=j).style = ws.cell(row=i,
column=j + 11).style = cell_style
ws['C' + str(k + 14)] = ws['N' + str(k + 14)] = summary[6]
ws['C' + str(k + 15)] = ws['N' + str(k + 15)] = summary[7]
ws['C' + str(k + 16)] = ws['N' + str(k + 16)] = summary[8]
ws['C' + str(k + 17)] = ws['N' + str(k + 17)] = summary[5]
ws['C' + str(k + 18)] = ws['N' + str(k + 18)] = summary[4]
ws['C' + str(k + 19)] = ws['N' + str(k + 19)] = summary[3]
ws['C' + str(k + 20)] = ws['N' + str(k + 20)] = summary[2]
ws['C' + str(k + 21)] = ws['N' + str(k + 21)] = summary[0]
# Суммы баллов и буквенные эквиваленты оценки
for i in range(8):
row = str(14 + k + i)
ws.merge_cells('E' + row + ':F' + row)
ws.merge_cells('P' + row + ':Q' + row)
ws.merge_cells('G' + row + ':I' + row)
ws.merge_cells('R' + row + ':T' + row)
ws['E' + row].style = ws['F' + row].style = ws['G' + row].style = \
ws['H' + row].style = ws['I' + row].style = ws['P' + row].style = \
ws['Q' + row].style = ws['R' + row].style = ws['S' + row].style = \
ws['T' + row].style = cell_style
# Подпись директора
row = str(24 + k)
ws.merge_cells('A' + row + ':I' + row)
ws.merge_cells('L' + row + ':T' + row)
ws.print_area = 'A1:T' + str(row)
ws.page_setup = PrintPageSetup(worksheet=ws)
ws.page_setup.paperSize = '9'
ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE
ws.page_setup.fitToHeight = True
ws.page_setup.fitToWidth = True
ws.page_setup.fitToPage = True
ws.sheet_properties.pageSetUpPr.fitToPage = True
return workbook
def write_dest(xlsx_name, schema_name):
border = Border(
left=Side(border_style=borders.BORDER_THIN, color='FF000000'),
right=Side(border_style=borders.BORDER_THIN, color='FF000000'),
top=Side(border_style=borders.BORDER_THIN, color='FF000000'),
bottom=Side(border_style=borders.BORDER_THIN, color='FF000000')
)
alignment = Alignment(horizontal='justify', vertical='bottom',
text_rotation=0, wrap_text=False,
shrink_to_fit=True, indent=0)
fill = PatternFill(fill_type=None, start_color='FFFFFFFF')
# 基本的样式
basic_style = Style(font=Font(name='Microsoft YaHei')
, border=border, alignment=alignment
, fill=fill)
title_style = basic_style.copy(
font=Font(name='Microsoft YaHei', b=True, size=20, color='00215757'),
alignment=Alignment(horizontal='center', vertical='bottom',
text_rotation=0, wrap_text=False,
shrink_to_fit=True, indent=0),
fill=PatternFill(fill_type=fills.FILL_SOLID, start_color='00B2CBED'))
header_style = basic_style.copy(
font=Font(name='Microsoft YaHei', b=True, size=15, color='00215757'),
fill=PatternFill(fill_type=fills.FILL_SOLID, start_color='00BAA87F'))
common_style = basic_style.copy()
link_style = basic_style.copy(font=Font(
name='Microsoft YaHei', color=colors.BLUE, underline='single'))
table_data = load_schema(schema_name)
wb = Workbook()
wb.active.title = "首页列表"
for table in table_data:
ws = wb.create_sheet(title=table[0])
ws.merge_cells('E3:H3') # 合并单元格
ws['E3'].style = title_style
ws['F2'].style = Style(border=Border(
bottom=Side(border_style=borders.BORDER_THIN, color='FF000000')))
ws['G2'].style = Style(border=Border(
bottom=Side(border_style=borders.BORDER_THIN, color='FF000000')))
ws['H2'].style = Style(border=Border(
bottom=Side(border_style=borders.BORDER_THIN, color='FF000000')))
ws['I3'].style = Style(border=Border(
left=Side(border_style=borders.BORDER_THIN, color='FF000000')))
ws['E3'] = table[0]
ws['E4'].style = header_style
ws['E4'] = '列名'
ws['F4'].style = header_style
ws['F4'] = '类型'
ws['G4'].style = header_style
ws['G4'] = '空值约束'
ws['H4'].style = header_style
ws['H4'] = '备注'
ws.column_dimensions['E'].width = 20
ws.column_dimensions['F'].width = 20
ws.column_dimensions['G'].width = 16
ws.column_dimensions['H'].width = 45
for idx, each_column in enumerate(table[2:]):
ws['E{}'.format(idx + 5)].style = common_style
ws['E{}'.format(idx + 5)] = each_column[0]
ws['F{}'.format(idx + 5)].style = common_style
ws['F{}'.format(idx + 5)] = each_column[1]
ws['G{}'.format(idx + 5)].style = common_style
ws['G{}'.format(idx + 5)] = each_column[2]
ws['H{}'.format(idx + 5)].style = common_style
ws['H{}'.format(idx + 5)] = each_column[3].strip().split('\'')[1]
ws = wb['首页列表']
ws.merge_cells('D3:F3')
ws['D3'].style = title_style
ws['E2'].style = Style(border=Border(
bottom=Side(border_style=borders.BORDER_THIN, color='FF000000')))
ws['F2'].style = Style(border=Border(
bottom=Side(border_style=borders.BORDER_THIN, color='FF000000')))
ws['G3'].style = Style(border=Border(
left=Side(border_style=borders.BORDER_THIN, color='FF000000')))
ws['D3'] = '贷快发数据库系统表'
ws['D4'].style = header_style
ws['D4'] = '编号'
ws['E4'].style = header_style
ws['E4'] = '表名'
ws['F4'].style = header_style
ws['F4'] = '详情链接'
ws.column_dimensions['D'].width = 15
ws.column_dimensions['E'].width = 25
ws.column_dimensions['F'].width = 35
for inx, val in enumerate(table_data):
ws['D{}'.format(inx + 5)].style = common_style
ws['D{}'.format(inx + 5)] = inx + 1
ws['E{}'.format(inx + 5)].style = common_style
ws['E{}'.format(inx + 5)] = val[1]
linkcell = ws['F{}'.format(inx + 5)]
linkcell.style = link_style
linkcell.value = val[0]
linkcell.hyperlink = '#{0}!{1}'.format(val[0], 'E3')
wb.save(filename=xlsx_name)
elif code == "TruSeq":
CONC_INPUT_UDF = "Conc. Input (nM) TruSeq DNA"
VOLUME_PHIX_UDF = "Volume PhiX (uL) TruSeq DNA"
VOLUME_FINAL_UDF = "Volume final (uL) TruSeq DNA"
START_COL = 2
START_ROW = 2
warning = []
lims = Lims(config.BASEURI, config.USERNAME, config.PASSWORD)
process = Process(lims, id=sys.argv[1])
wb = Workbook()
ws = wb.active
side_style = Side(border_style="thin")
thick_side_style = Side(border_style="thick")
def sort_key(elem):
input, output = elem
container, well = output.location
row, col = well.split(":")
return (container.id, int(col), row)
formatting = [("Sample name", 16, None), ("Well", 8, None),
("Sample conc. [nM]", 10, '0.00'),
("Conc. Input [nM]", 10, '0.0'), ("Volum final [uL]", 10, '0'),
("Input [uL]", 10, '0.00'), ("RSB [uL]", 10, '0.00'),
("PhiX [uL]", 10, '0.00'), ("Well strip", 9, '0')]
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['shopfloor']['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 + 9 * 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 = 9 * 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 + 9 * 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 + 9 * 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 + 9 * 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.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
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)
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'] = round(reporting_period_data['subtotals_per_unit_area'][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 + '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'] = round(reporting_period_data['total_per_unit_area'], 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, 9 + 1):
ws.row_dimensions[i].height = 0.1
##################################
current_row_number = 11
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'])
total = Decimal(0.0)
for i in range(0, ca_len):
total = reporting_period_data['subtotals'][i] + total
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(reporting_period_data['subtotals'][i] / total) \
if total > 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 = '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
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
current_end_row_number = current_row_number
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:
current_end_row_number += ca_len * 6 + 1
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)
current_end_row_number += (len(time) + 3)
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 = Decimal(0.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
current_row_number = current_end_row_number
##################################
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 \
or 'child_space_names_array' not in report['child_space'].keys() \
or report['child_space']['energy_category_names'] is None \
or len(report['child_space']['child_space_names_array']) == 0 \
or len(report['child_space']['child_space_names_array'][0]) == 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
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(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 = 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)] = 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 = 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)] = '总计 (' + 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 = title_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 = Decimal(0.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
table_end_row_number = current_row_number
current_row_number += 1
chart_start_row_number = current_row_number
# Pie
for i in range(0, ca_len):
pie = PieChart()
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 + i, 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 = 8
pie.title = ws.cell(column=3 + i, row=table_start_row_number).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(chart_start_row_number)
else:
chart_cell = 'E' + str(chart_start_row_number)
chart_start_row_number += 5
ws.add_chart(pie, chart_cell)
current_row_number = chart_start_row_number
if ca_len % 2 == 1:
current_row_number += 5
current_row_number += 1
#############################################
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
from openpyxl.writer.excel import save_virtual_workbook
from openpyxl.styles import Alignment, Border, Side
from django.utils.translation import ugettext_lazy as _
from accounts.models import User
from distriblists.models import DistributionList
from distriblists.forms import DistributionListForm
header_alignment = Alignment(
horizontal='center',
textRotation=45,
)
role_alignment = Alignment(horizontal='center')
cell_border = Border(top=Side(style='thin'),
bottom=Side(style='thin'),
left=Side(style='thin'),
right=Side(style='thin'),
vertical=Side(style='thin'))
def import_review_members(filepath, category):
"""Import review members from an excel file."""
wb = openpyxl.load_workbook(filepath)
ws = wb.active
# Extracts the user list from the header row
emails, user_ids = _extract_users(ws)
max_col = len(user_ids) + 1 # Don't use ws.max_column, it's not reliable
def data2pandas2save(self, festival, product_dict, result_data):
year_df = pd.Series(self.result_data.values())
index_df = pd.Series(list(self.result_data.keys()))
df1 = pd.DataFrame(
index_df,
index=[i for i in range(0, len(self.product_dict.keys()))])
df2 = pd.DataFrame(
list(year_df[0]),
index=[i for i in range(0, len(self.product_dict.keys()))])
df3 = pd.merge(df1,
df2,
left_index=True,
right_index=True,
how='outer')
df3.columns.values[0] = '農產品'
for index, row in df3.iterrows():
product_name = [k for k in list(self.product_dict.keys())][index]
df3.loc[index, '農產品'] = product_name
Row_list = []
for index, rows in df3.iterrows():
data_list = []
for y in range(self.roc_before5years, self.roc_year + 1):
data_list.append(rows[str(y)])
l = [y for x in data_list for y in x]
Row_list.append(l)
columns = []
for y in range(self.roc_before5years, self.roc_year + 1):
if y == self.roc_year:
for i in range(0, 9):
temp_list = []
if i == 0:
temp = '節前四周'
elif i == 1:
temp = '節前三周'
elif i == 2:
temp = '節前二周'
elif i == 3:
temp = '節前一周'
elif i == 4:
temp = '節後一周'
elif i == 5:
temp = '節後二周'
elif i == 6:
temp = '節後三周'
elif i == 7:
temp = '節後四周'
elif i == 8:
temp = '最近一日'
temp_list.append(str(y))
temp_list.append(temp)
columns.append(tuple(temp_list))
else:
for i in range(0, 8):
temp_list = []
if i == 0:
temp = '節前四周'
elif i == 1:
temp = '節前三周'
elif i == 2:
temp = '節前二周'
elif i == 3:
temp = '節前一周'
elif i == 4:
temp = '節後一周'
elif i == 5:
temp = '節後二周'
elif i == 6:
temp = '節後三周'
elif i == 7:
temp = '節後四周'
temp_list.append(str(y))
temp_list.append(temp)
columns.append(tuple(temp_list))
df4 = pd.DataFrame(Row_list,
columns=pd.MultiIndex.from_tuples(columns))
for index, row in df4.iterrows():
product_name = [k for k in list(self.product_dict.keys())][index]
df4.loc[index, ('農產品', '產品名稱')] = product_name
for index, row in df4.iterrows():
df4.loc[index, (
'節後四周與去年同期比較', '漲跌率(%)'
)] = df4.loc[index,
(str(self.roc_year), '節後四周')] / df4.loc[index, (
str(self.roc_year - 1), '節後四周')] * 100 - 100
df4.loc[index, ('節後四周與去年同期比較', '差幅(元/公斤)')] = df4.loc[index, (
str(self.roc_year),
'節後四周')] - df4.loc[index, (str(self.roc_year - 1), '節後四周')]
df4.loc[index, ('前五年簡單平均', '節後四周平均(元/公斤)')] = self.trimmean(
df4.loc[index, [(str(self.roc_year - 1), '節後四周'),
(str(self.roc_year - 2), '節後四周'),
(str(self.roc_year - 3), '節後四周'),
(str(self.roc_year - 4), '節後四周'),
(str(self.roc_year - 5), '節後四周')]])
# df4.loc[index,('前五年簡單平均','節後四周平均(元/公斤)')] = df4.loc[index,[(str(self.roc_year-1), '節後四周'),(str(self.roc_year-2), '節後四周'),(str(self.roc_year-3), '節後四周'),(str(self.roc_year-4), '節後四周'),(str(self.roc_year-5), '節後四周')]].mean(axis=0)
df4.loc[index, (
'節後三周與去年同期比較', '漲跌率(%)'
)] = df4.loc[index,
(str(self.roc_year), '節後三周')] / df4.loc[index, (
str(self.roc_year - 1), '節後三周')] * 100 - 100
df4.loc[index, ('節後三周與去年同期比較', '差幅(元/公斤)')] = df4.loc[index, (
str(self.roc_year),
'節後三周')] - df4.loc[index, (str(self.roc_year - 1), '節後三周')]
df4.loc[index, ('前五年簡單平均', '節後三周平均(元/公斤)')] = self.trimmean(
df4.loc[index, [(str(self.roc_year - 1), '節後三周'),
(str(self.roc_year - 2), '節後三周'),
(str(self.roc_year - 3), '節後三周'),
(str(self.roc_year - 4), '節後三周'),
(str(self.roc_year - 5), '節後三周')]])
df4.loc[index, (
'節後二周與去年同期比較', '漲跌率(%)'
)] = df4.loc[index,
(str(self.roc_year), '節後二周')] / df4.loc[index, (
str(self.roc_year - 1), '節後二周')] * 100 - 100
df4.loc[index, ('節後二周與去年同期比較', '差幅(元/公斤)')] = df4.loc[index, (
str(self.roc_year),
'節後二周')] - df4.loc[index, (str(self.roc_year - 1), '節後二周')]
df4.loc[index, ('前五年簡單平均', '節後二周平均(元/公斤)')] = self.trimmean(
df4.loc[index, [(str(self.roc_year - 1), '節後二周'),
(str(self.roc_year - 2), '節後二周'),
(str(self.roc_year - 3), '節後二周'),
(str(self.roc_year - 4), '節後二周'),
(str(self.roc_year - 5), '節後二周')]])
df4.loc[index, (
'節後一周與去年同期比較', '漲跌率(%)'
)] = df4.loc[index,
(str(self.roc_year), '節後一周')] / df4.loc[index, (
str(self.roc_year - 1), '節後一周')] * 100 - 100
df4.loc[index, ('節後一周與去年同期比較', '差幅(元/公斤)')] = df4.loc[index, (
str(self.roc_year),
'節後一周')] - df4.loc[index, (str(self.roc_year - 1), '節後一周')]
df4.loc[index, ('前五年簡單平均', '節後一周平均(元/公斤)')] = self.trimmean(
df4.loc[index, [(str(self.roc_year - 1), '節後一周'),
(str(self.roc_year - 2), '節後一周'),
(str(self.roc_year - 3), '節後一周'),
(str(self.roc_year - 4), '節後一周'),
(str(self.roc_year - 5), '節後一周')]])
df4.loc[index, (
'節前一周與去年同期比較', '漲跌率(%)'
)] = df4.loc[index,
(str(self.roc_year), '節前一周')] / df4.loc[index, (
str(self.roc_year - 1), '節前一周')] * 100 - 100
df4.loc[index, ('節前一周與去年同期比較', '差幅(元/公斤)')] = df4.loc[index, (
str(self.roc_year),
'節前一周')] - df4.loc[index, (str(self.roc_year - 1), '節前一周')]
df4.loc[index, ('前五年簡單平均', '節前一周平均(元/公斤)')] = self.trimmean(
df4.loc[index, [(str(self.roc_year - 1), '節前一周'),
(str(self.roc_year - 2), '節前一周'),
(str(self.roc_year - 3), '節前一周'),
(str(self.roc_year - 4), '節前一周'),
(str(self.roc_year - 5), '節前一周')]])
df4.loc[index, (
'節前二周與去年同期比較', '漲跌率(%)'
)] = df4.loc[index,
(str(self.roc_year), '節前二周')] / df4.loc[index, (
str(self.roc_year - 1), '節前二周')] * 100 - 100
df4.loc[index, ('節前二周與去年同期比較', '差幅(元/公斤)')] = df4.loc[index, (
str(self.roc_year),
'節前二周')] - df4.loc[index, (str(self.roc_year - 1), '節前二周')]
df4.loc[index, ('前五年簡單平均', '節前二周平均(元/公斤)')] = self.trimmean(
df4.loc[index, [(str(self.roc_year - 1), '節前二周'),
(str(self.roc_year - 2), '節前二周'),
(str(self.roc_year - 3), '節前二周'),
(str(self.roc_year - 4), '節前二周'),
(str(self.roc_year - 5), '節前二周')]])
df4.loc[index, (
'節前三周與去年同期比較', '漲跌率(%)'
)] = df4.loc[index,
(str(self.roc_year), '節前三周')] / df4.loc[index, (
str(self.roc_year - 1), '節前三周')] * 100 - 100
df4.loc[index, ('節前三周與去年同期比較', '差幅(元/公斤)')] = df4.loc[index, (
str(self.roc_year),
'節前三周')] - df4.loc[index, (str(self.roc_year - 1), '節前三周')]
df4.loc[index, ('前五年簡單平均', '節前三周平均(元/公斤)')] = self.trimmean(
df4.loc[index, [(str(self.roc_year - 1), '節前三周'),
(str(self.roc_year - 2), '節前三周'),
(str(self.roc_year - 3), '節前三周'),
(str(self.roc_year - 4), '節前三周'),
(str(self.roc_year - 5), '節前三周')]])
df4.loc[index, (
'節前四周與去年同期比較', '漲跌率(%)'
)] = df4.loc[index,
(str(self.roc_year), '節前四周')] / df4.loc[index, (
str(self.roc_year - 1), '節前四周')] * 100 - 100
df4.loc[index, ('節前四周與去年同期比較', '差幅(元/公斤)')] = df4.loc[index, (
str(self.roc_year),
'節前四周')] - df4.loc[index, (str(self.roc_year - 1), '節前四周')]
df4.loc[index, ('前五年簡單平均', '節前四周平均(元/公斤)')] = self.trimmean(
df4.loc[index, [(str(self.roc_year - 1), '節前四周'),
(str(self.roc_year - 2), '節前四周'),
(str(self.roc_year - 3), '節前四周'),
(str(self.roc_year - 4), '節前四周'),
(str(self.roc_year - 5), '節前四周')]])
df4.index = df4.index + 1
columns_list = []
columns_list.append(('農產品', '產品名稱'))
columns_list.append(('{}'.format(self.roc_year), '最近一日'))
for y in range(self.roc_year, self.roc_year - 5 - 1, -1):
columns_list.append(('{}'.format(y), '節後四周'))
columns_list.append(('節後四周與去年同期比較', '漲跌率(%)'))
columns_list.append(('節後四周與去年同期比較', '差幅(元/公斤)'))
columns_list.append(('前五年簡單平均', '節後四周平均(元/公斤)'))
for y in range(self.roc_year, self.roc_year - 5 - 1, -1):
columns_list.append(('{}'.format(y), '節後三周'))
columns_list.append(('節後三周與去年同期比較', '漲跌率(%)'))
columns_list.append(('節後三周與去年同期比較', '差幅(元/公斤)'))
columns_list.append(('前五年簡單平均', '節後三周平均(元/公斤)'))
for y in range(self.roc_year, self.roc_year - 5 - 1, -1):
columns_list.append(('{}'.format(y), '節後二周'))
columns_list.append(('節後二周與去年同期比較', '漲跌率(%)'))
columns_list.append(('節後二周與去年同期比較', '差幅(元/公斤)'))
columns_list.append(('前五年簡單平均', '節後二周平均(元/公斤)'))
for y in range(self.roc_year, self.roc_year - 5 - 1, -1):
columns_list.append(('{}'.format(y), '節後一周'))
columns_list.append(('節後一周與去年同期比較', '漲跌率(%)'))
columns_list.append(('節後一周與去年同期比較', '差幅(元/公斤)'))
columns_list.append(('前五年簡單平均', '節後一周平均(元/公斤)'))
for y in range(self.roc_year, self.roc_year - 5 - 1, -1):
columns_list.append(('{}'.format(y), '節前一周'))
columns_list.append(('節前一周與去年同期比較', '漲跌率(%)'))
columns_list.append(('節前一周與去年同期比較', '差幅(元/公斤)'))
columns_list.append(('前五年簡單平均', '節前一周平均(元/公斤)'))
for y in range(self.roc_year, self.roc_year - 5 - 1, -1):
columns_list.append(('{}'.format(y), '節前二周'))
columns_list.append(('節前二周與去年同期比較', '漲跌率(%)'))
columns_list.append(('節前二周與去年同期比較', '差幅(元/公斤)'))
columns_list.append(('前五年簡單平均', '節前二周平均(元/公斤)'))
for y in range(self.roc_year, self.roc_year - 5 - 1, -1):
columns_list.append(('{}'.format(y), '節前三周'))
columns_list.append(('節前三周與去年同期比較', '漲跌率(%)'))
columns_list.append(('節前三周與去年同期比較', '差幅(元/公斤)'))
columns_list.append(('前五年簡單平均', '節前三周平均(元/公斤)'))
for y in range(self.roc_year, self.roc_year - 5 - 1, -1):
columns_list.append(('{}'.format(y), '節前四周'))
columns_list.append(('節前四周與去年同期比較', '漲跌率(%)'))
columns_list.append(('節前四周與去年同期比較', '差幅(元/公斤)'))
columns_list.append(('前五年簡單平均', '節前四周平均(元/公斤)'))
df4 = df4[columns_list]
df5 = df4.copy()
if festival == 1:
self.roc_date_range = self.ROC_Chinese_New_Year_dict
if festival == 2:
self.roc_date_range = self.ROC_Dragon_Boat_Festival_dict
if festival == 3:
self.roc_date_range = self.ROC_Mid_Autumn_Festival_dict
#組合新欄位名稱
yes_date = self.roc_date_range[str(self.roc_year)][-1].split('~')[0]
if len(yes_date) > 5:
yes_date = yes_date.split('/')[1] + '/' + yes_date.split('/')[2]
columns_name = []
columns_name.append('農產品')
columns_name.append('最近一日\n{}/{}\n(元/公斤)'.format(
self.today.year - 1911, yes_date))
for y in range(self.roc_year, self.roc_year - 6, -1):
date = self.roc_date_range[str(y)][7]
columns_name.append('{0}年節後四周\n{1}\n(元/公斤)'.format(y, date))
columns_name.append('{}年節後四周較{}年同期\n漲跌率\n(%)'.format(
self.roc_year, self.roc_year - 1))
columns_name.append('{}年節後四周較{}年同期\n差幅\n(元/公斤)'.format(
self.roc_year, self.roc_year - 1))
columns_name.append('近5年簡單平均\n({}-{}年)\n節後四周\n(元/公斤)'.format(
self.roc_year - 5, self.roc_year - 1))
for y in range(self.roc_year, self.roc_year - 6, -1):
date = self.roc_date_range[str(y)][6]
columns_name.append('{0}年節後三周\n{1}\n(元/公斤)'.format(y, date))
columns_name.append('{}年節後三周較{}年同期\n漲跌率\n(%)'.format(
self.roc_year, self.roc_year - 1))
columns_name.append('{}年節後三周較{}年同期\n差幅\n(元/公斤)'.format(
self.roc_year, self.roc_year - 1))
columns_name.append('近5年簡單平均\n({}-{}年)\n節後三周\n(元/公斤)'.format(
self.roc_year - 5, self.roc_year - 1))
for y in range(self.roc_year, self.roc_year - 6, -1):
date = self.roc_date_range[str(y)][5]
columns_name.append('{0}年節後二周\n{1}\n(元/公斤)'.format(y, date))
columns_name.append('{}年節後二周較{}年同期\n漲跌率\n(%)'.format(
self.roc_year, self.roc_year - 1))
columns_name.append('{}年節後二周較{}年同期\n差幅\n(元/公斤)'.format(
self.roc_year, self.roc_year - 1))
columns_name.append('近5年簡單平均\n({}-{}年)\n節後二周\n(元/公斤)'.format(
self.roc_year - 5, self.roc_year - 1))
for y in range(self.roc_year, self.roc_year - 6, -1):
date = self.roc_date_range[str(y)][4]
columns_name.append('{0}年節後一周\n{1}\n(元/公斤)'.format(y, date))
columns_name.append('{}年節後一周較{}年同期\n漲跌率\n(%)'.format(
self.roc_year, self.roc_year - 1))
columns_name.append('{}年節後一周較{}年同期\n差幅\n(元/公斤)'.format(
self.roc_year, self.roc_year - 1))
columns_name.append('近5年簡單平均\n({}-{}年)\n節後一周\n(元/公斤)'.format(
self.roc_year - 5, self.roc_year - 1))
for y in range(self.roc_year, self.roc_year - 6, -1):
date = self.roc_date_range[str(y)][3]
columns_name.append('{0}年節前一周\n{1}\n(元/公斤)'.format(y, date))
columns_name.append('{}年節前一周較{}年同期\n漲跌率\n(%)'.format(
self.roc_year, self.roc_year - 1))
columns_name.append('{}年節前一周較{}年同期\n差幅\n(元/公斤)'.format(
self.roc_year, self.roc_year - 1))
columns_name.append('近5年簡單平均\n({}-{}年)\n節前一周\n(元/公斤)'.format(
self.roc_year - 5, self.roc_year - 1))
for y in range(self.roc_year, self.roc_year - 6, -1):
date = self.roc_date_range[str(y)][2]
columns_name.append('{0}年節前二周\n{1}\n(元/公斤)'.format(y, date))
columns_name.append('{}年節前二周較{}年同期\n漲跌率\n(%)'.format(
self.roc_year, self.roc_year - 1))
columns_name.append('{}年節前二周較{}年同期\n差幅\n(元/公斤)'.format(
self.roc_year, self.roc_year - 1))
columns_name.append('近5年簡單平均\n({}-{}年)\n節前二周\n(元/公斤)'.format(
self.roc_year - 5, self.roc_year - 1))
for y in range(self.roc_year, self.roc_year - 6, -1):
date = self.roc_date_range[str(y)][1]
columns_name.append('{0}年節前三周\n{1}\n(元/公斤)'.format(y, date))
columns_name.append('{}年節前三周較{}年同期\n漲跌率\n(%)'.format(
self.roc_year, self.roc_year - 1))
columns_name.append('{}年節前三周較{}年同期\n差幅\n(元/公斤)'.format(
self.roc_year, self.roc_year - 1))
columns_name.append('近5年簡單平均\n({}-{}年)\n節前三周\n(元/公斤)'.format(
self.roc_year - 5, self.roc_year - 1))
for y in range(self.roc_year, self.roc_year - 6, -1):
date = self.roc_date_range[str(y)][0]
columns_name.append('{0}年節前四周\n{1}\n(元/公斤)'.format(y, date))
columns_name.append('{}年節前四周較{}年同期\n漲跌率\n(%)'.format(
self.roc_year, self.roc_year - 1))
columns_name.append('{}年節前四三周較{}年同期\n差幅\n(元/公斤)'.format(
self.roc_year, self.roc_year - 1))
columns_name.append('近5年簡單平均\n({}-{}年)\n節前四周\n(元/公斤)'.format(
self.roc_year - 5, self.roc_year - 1))
df5.columns = columns_name
if self.festival == 1:
festival_title = '春節'
if self.festival == 2:
festival_title = '端午節'
if self.festival == 3:
festival_title = '中秋節'
file_name = '{}_{}節前價格表.xlsx'.format(self.roc_year, festival_title)
writer = pd.ExcelWriter(file_name)
wb = openpyxl.Workbook()
df6 = df5.copy()
df6.to_excel(writer)
ws = wb.create_sheet(index=0, title="價格表")
#pandas to openpyxl
for r in dataframe_to_rows(df6, index=False, header=False):
ws.append(r)
#設定字形大小及格式
title_font = Font(size=22)
content_font = Font(size=16)
alignment = Alignment(horizontal='center',
vertical='center',
wrap_text=True)
#新增框線
border = Border(top=Side(border_style='thin', color='000000'),
bottom=Side(border_style='thin', color='000000'),
left=Side(border_style='thin', color='000000'),
right=Side(border_style='thin', color='000000'))
for _row in ws.iter_rows():
for _cell in _row:
_cell.border = border
_cell.font = content_font
_cell.number_format = '#,##0.0' #小數一位
#在第一行前插入一行
ws.insert_rows(1)
# 合併儲存格
ws.merge_cells('A1:BV1')
#儲存格內容及格式
ws['A1'] = '{}節前農產品價格變動情形表'.format(festival_title)
ws['A1'].alignment = Alignment(horizontal='center', vertical='center')
ws['A1'].font = title_font
#插入兩行製作各欄位標題
ws.insert_rows(2)
ws.insert_rows(2)
for i in range(1, len(df6.columns) + 1):
if i in [
9, 10, 18, 19, 27, 28, 36, 37, 45, 46, 54, 55, 63, 64, 72,
73
]:
ws.cell(row=2,
column=i).value = df6.columns[i - 1].split('\n')[0]
ws.cell(row=3, column=i).value = df6.columns[i - 1].split(
'\n')[1] + '\n' + df6.columns[i - 1].split('\n')[2]
else:
ws.cell(row=2, column=i).value = df6.columns[i - 1]
ws.cell(row=2, column=i).font = content_font
ws.cell(row=3, column=i).font = content_font
# 合併儲存格
for i in range(65, 91):
if i in (73, 74, 82, 83):
continue
ws.merge_cells('{0}2:{0}3'.format(chr(i)))
for i in range(65, 91):
if i in (65, 66, 74, 75, 83, 84):
continue
ws.merge_cells('A{0}2:A{0}3'.format(chr(i)))
for i in range(65, 91):
if i in (66, 67, 75, 76, 84, 85):
continue
ws.merge_cells('B{0}2:B{0}3'.format(chr(i)))
ws.merge_cells('I2:J2')
ws.merge_cells('R2:S2')
ws.merge_cells('AA2:AB2')
ws.merge_cells('AJ2:AK2')
ws.merge_cells('AS2:AT2')
ws.merge_cells('BB2:BC2')
ws.merge_cells('BK2:BL2')
ws.merge_cells('BT2:BU2')
# 設定儲存格格式
for i in range(1, len(df6.columns) + 1):
ws.cell(row=2, column=i).alignment = alignment #置中及換行
ws.cell(row=3, column=i).alignment = alignment
ws.cell(row=2, column=i).border = border #框線
ws.cell(row=3, column=i).border = border
#欄位寬度
ws.column_dimensions['A'].width = 33
for c in range(2, len(df6.columns) + 1):
ws.column_dimensions[get_column_letter(c)].width = 22
for _col in [
'I', 'J', 'R', 'S', 'AA', 'AB', 'AJ', 'AK', 'AS', 'AT', 'BB',
'BC', 'BK', 'BL', 'BT', 'BU'
]:
ws.column_dimensions[_col].width = 18.5
#設定第2列高度
ws.row_dimensions[2].height = 42
#隱藏欄位
# hidden_columns=['B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO']
# for col in hidden_columns:
# ws.column_dimensions[col].hidden= True
#凍結窗格
ws.freeze_panes = ws['B4']
# Close the Pandas Excel writer and output the Excel file.
wb.save(file_name)
return file_name
def create_excel(context_list, title_list, sheet_list):
# 1、创建excel文件
f = openpyxl.Workbook()
for i in range(int(sheet_list[0])):
f.create_sheet(title=sheet_list[i + 1], index=i)
# 2、创建sheet格式
# sheet:
sheet = f.worksheets[2]
for i in range(0, 5):
# 行合并
sheet.merge_cells(start_row=16 * i + 3,
start_column=2,
end_row=16 * i + 18,
end_column=2)
sheet.merge_cells(start_row=16 * i + 3,
start_column=3,
end_row=16 * i + 6,
end_column=3)
sheet.merge_cells(start_row=16 * i + 7,
start_column=3,
end_row=16 * i + 10,
end_column=3)
sheet.merge_cells(start_row=16 * i + 11,
start_column=3,
end_row=16 * i + 14,
end_column=3)
sheet.merge_cells(start_row=16 * i + 15,
start_column=3,
end_row=16 * i + 18,
end_column=3)
sheet.merge_cells(start_row=16 * i + 3,
start_column=4,
end_row=16 * i + 6,
end_column=4)
sheet.merge_cells(start_row=16 * i + 7,
start_column=4,
end_row=16 * i + 10,
end_column=4)
sheet.merge_cells(start_row=16 * i + 11,
start_column=4,
end_row=16 * i + 14,
end_column=4)
sheet.merge_cells(start_row=16 * i + 15,
start_column=4,
end_row=16 * i + 18,
end_column=4)
sheet.merge_cells(start_row=83, start_column=2, end_row=83, end_column=4)
sheet.merge_cells(start_row=84, start_column=2, end_row=84, end_column=4)
sheet.merge_cells(start_row=85, start_column=2, end_row=85, end_column=4)
# 设置单元格字体
# font = Font(name=u'宋体', size=14, color=BLUE, bold=True)
# sheet.cell(row=1, column=i+1).font = font
align = Alignment(horizontal='center', vertical='center')
fill = PatternFill(start_color='CC99CC',
end_color='CC99CC',
fill_type='solid')
sheet.freeze_panes = 'F3'
border = Border(left=Side(style='medium', color='FF000000'),
right=Side(style='medium', color='FF000000'),
top=Side(style='medium', color='FF000000'),
bottom=Side(style='medium', color='FF000000'),
diagonal=Side(style='medium', color='FF000000'),
diagonal_direction=0,
outline=Side(style='medium', color='FF000000'),
vertical=Side(style='medium', color='FF000000'),
horizontal=Side(style='medium', color='FF000000'))
for i in range(2, 86):
for j in range(2, 37):
sheet.cell(row=i, column=j).border = border
# 3、填写内容
for i in range(len(context_list)):
sheet.cell(row=2, column=i + 2).alignment = align
sheet.cell(row=2, column=i + 2, value=10).fill = fill
sheet.cell(row=2, column=i + 2).value = context_list[i]
B_list = ['1Q', '2Q', '3Q', '4Q', '全年']
# for i in range(len(B_list)):
# sheet.cell(row=i+3, column=2).alignment = align
# sheet.cell(row=i+3, column=2).value = B_list[i]
sheet.cell(row=3, column=2).alignment = align
sheet.cell(row=3, column=2).value = B_list[0]
sheet.cell(row=19, column=2).alignment = align
sheet.cell(row=19, column=2).value = B_list[1]
sheet.cell(row=35, column=2).alignment = align
sheet.cell(row=35, column=2).value = B_list[2]
sheet.cell(row=51, column=2).alignment = align
sheet.cell(row=51, column=2).value = B_list[3]
sheet.cell(row=67, column=2).alignment = align
sheet.cell(row=67, column=2).value = B_list[4]
C_list = ['签约1个月', '签约半年', '交付一周', '入住期']
for j in range(len(C_list)):
for l in range(0, 4):
sheet.cell(row=j + (4 * l + 3), column=3).alignment = align
sheet.cell(row=j + (4 * l + 3), column=3).value = C_list[j]
D_list = [
"2018.12-2019.2签约", "2018.7-2018.9签约", "2018.12.5-2019.3.15交付", "\\",
"2019.3-2019.5签约", "2018.10-2018.12签约", "2019.3.16-2019.6.15交付", "\\",
"2019.6-2019.8签约", "2019.1-2019.3签约", "2019.6.16-2019.9.15交付", "\\",
"2019.9-2019.11签约", "2019.4-2019.6签约", "2019.9.16-2019.12.10交付", "\\",
"2018.12-2019.11签约", "2018.7-2019.6签约", "2018.12.5-2019.12.10交付", "\\"
]
for i in range(len(D_list)):
sheet.cell(row=i + 3, column=4).alignment = align
sheet.cell(row=i + 3, column=4).value = D_list[i]
# 4、保存文件
curPath = os.getcwd()
tempPath = 'file_target'
targetPath = curPath + os.path.sep + tempPath
if not os.path.exists(targetPath):
os.makedirs(targetPath)
for i in range(len(title_list)):
f.save(targetPath + "/" + title_list[i] + ".xlsx")
def create_aww_performance_excel_file(excel_data, data_type, month, state,
district, block):
export_info = excel_data[1][1]
excel_data = [line[3:] for line in excel_data[0][1]]
thin_border = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
warp_text_alignment = Alignment(wrap_text=True)
bold_font = Font(bold=True)
blue_fill = PatternFill("solid", fgColor="B3C5E5")
grey_fill = PatternFill("solid", fgColor="BFBFBF")
workbook = Workbook()
worksheet = workbook.active
worksheet.title = "AWW Performance Report"
worksheet.sheet_view.showGridLines = False
# sheet title
worksheet.merge_cells('B2:J2')
title_cell = worksheet['B2']
title_cell.fill = PatternFill("solid", fgColor="4472C4")
title_cell.value = "AWW Performance Report for the month of {}".format(
month)
title_cell.font = Font(size=18, color="FFFFFF")
title_cell.alignment = Alignment(horizontal="center")
# sheet header
for cell in {"B3", "C3", "D3", "E3", "F3", "G3", "H3", "J3"}:
worksheet[cell].fill = blue_fill
worksheet[cell].font = bold_font
worksheet[cell].alignment = warp_text_alignment
worksheet.merge_cells('B3:C3')
worksheet['B3'].value = "State: {}".format(state)
worksheet['D3'].value = "District: {}".format(district)
worksheet.merge_cells('E3:F3')
worksheet['E3'].value = "Block: {}".format(block)
worksheet.merge_cells('H3:I3')
worksheet['H3'].value = "Date when downloaded:"
worksheet['H3'].alignment = Alignment(horizontal="right")
utc_now = datetime.now(pytz.utc)
now_in_india = utc_now.astimezone(india_timezone)
worksheet['J3'].value = custom_strftime('{S} %b %Y', now_in_india)
worksheet['J3'].alignment = Alignment(horizontal="right")
# table header
table_header_position_row = 5
table_header = {
'B': "S.No",
'C': "Supervisor",
'D': "AWC",
'E': "AWW Name",
'F': "AWW Contact Number",
'G': "Home Visits Conducted",
'H': "Number of Days AWC was Open",
'I': "Weighing Efficiency",
'J': "Eligible for Incentive",
}
for column, value in table_header.items():
cell = "{}{}".format(column, table_header_position_row)
worksheet[cell].fill = grey_fill
worksheet[cell].border = thin_border
worksheet[cell].font = bold_font
worksheet[cell].alignment = warp_text_alignment
worksheet[cell].value = value
# table contents
row_position = table_header_position_row + 1
for enum, row in enumerate(excel_data[1:], start=1):
columns = ["B", "C", "D", "E", "F", "G", "H", "I", "J"]
for column_index in range(len(columns)):
column = columns[column_index]
cell = "{}{}".format(column, row_position)
worksheet[cell].border = thin_border
if column_index == 0:
worksheet[cell].value = enum
else:
worksheet[cell].value = row[column_index - 1]
row_position += 1
# sheet dimensions
title_row = worksheet.row_dimensions[2]
title_row.height = 23
worksheet.row_dimensions[table_header_position_row].height = 46
widths = {
'A': 4,
'B': 7,
'C': max(15,
len(state) * 4 // 3),
'D': 13 + (len(district) * 4 // 3),
'E': 12,
'F': max(13,
len(block) * 4 // 3),
'G': 15,
'H': 11,
'I': 14,
'J': 14,
}
for column in ["C", "E", "G"]:
if widths[column] > 25:
worksheet.row_dimensions[3].height = max(
16 * ((widths[column] // 25) + 1),
worksheet.row_dimensions[3].height)
widths[column] = 25
columns = ["C", "D", "E", "F", "G", "H", "I", "J"]
# column widths based on table contents
for column_index in range(len(columns)):
widths[columns[column_index]] = max(
widths[columns[column_index]],
max(
len(row[column_index].decode('utf-8') if isinstance(
row[column_index], bytes) else six.
text_type(row[column_index])) for row in excel_data[1:]) *
4 // 3 if len(excel_data) >= 2 else 0)
for column, width in widths.items():
worksheet.column_dimensions[column].width = width
# export info
worksheet2 = workbook.create_sheet("Export Info")
worksheet2.column_dimensions['A'].width = 14
worksheet2['A1'].value = export_info[0][0]
worksheet2['B1'].value = export_info[0][1]
worksheet2['A2'].value = export_info[1][0]
worksheet2['B2'].value = export_info[1][1]
worksheet2['A3'].value = export_info[2][0]
worksheet2['B3'].value = export_info[2][1]
worksheet2['A4'].value = export_info[3][0]
worksheet2['B4'].value = export_info[3][1]
worksheet2['A4'].value = export_info[4][0]
worksheet2['B4'].value = export_info[4][1]
# saving file
file_hash = uuid.uuid4().hex
export_file = BytesIO()
icds_file = IcdsFile(blob_id=file_hash, data_type=data_type)
workbook.save(export_file)
export_file.seek(0)
icds_file.store_file_in_blobdb(export_file, expired=60 * 60 * 24)
icds_file.save()
return file_hash
# 번호, 영어, 수학
a1 = ws["A1"]
b1 = ws["B1"]
c1 = ws["C1"]
ws.column_dimensions["A"].width = 5 # A열의 너비를 5로 설정
ws.row_dimensions[1].height = 50 # 1열의 높이를 50으로 설정
# 스타일 적용
a1.font = Font(color="FF0000", italic=True,
bold=True) # 글자 색은 빨강, 기울임 + 두껍게 적용
b1.font = Font(color="CC33FF", name="Arial", strike=True) # 폰트 지정 + 취소선
c1.font = Font(color="0000FF", size=20, underline="single") # 글자 크기 지정 + 밑줄
thin_border = Border(left=Side(style="thin"),
right=Side(style="thin"),
top=Side(style="thin"),
bottom=Side(style="thin")) # 테두리 적용
a1.border = thin_border
b1.border = thin_border
c1.border = thin_border
# 90점 넘는 셀은 초록색
for row in ws.rows:
for cell in row:
cell.alignment = Alignment(
horizontal="center", vertical="center"
) # 각 cell에 대해 중앙 정렬 (center, left, right, top, bottom)
if cell.column == 1: # A: 번호 열은 제외
continue
warning = []
lims = Lims(config.BASEURI, config.USERNAME, config.PASSWORD)
process = Process(lims, id=sys.argv[1])
use_sample_conc = False
use_output_conc = False
if len(sys.argv) > 3:
use_sample_conc = "use_sample_conc" in sys.argv[3].split(",")
use_output_conc = "use_output_conc" in sys.argv[3].split(",")
wb = Workbook()
ws = wb.active
side_style = Side(border_style="thin")
border_style = Border(top=side_style,
left=side_style,
right=side_style,
bottom=side_style)
def sort_key(elem):
input, output = elem
container, well = output.location
row, col = well.split(":")
return (container.id, int(col), row)
def get_or_default(udfname):
try:
cols = list(set(df[colpick].values))
alphabet = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
font = Font(name='Calibri',
size=12,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
fill = PatternFill(fill_type=None,
start_color='FFFFFFFF',
end_color='FF000000')
border = Border(left=Side(border_style=None, color='FF000000'),
right=Side(border_style=None, color='FF000000'),
top=Side(border_style=None, color='FF000000'),
bottom=Side(border_style=None, color='FF000000'),
diagonal=Side(border_style=None, color='FF000000'),
diagonal_direction=0,
outline=Side(border_style=None, color='FF000000'),
vertical=Side(border_style=None, color='FF000000'),
horizontal=Side(border_style=None, color='FF000000'))
alignment = Alignment(horizontal='general',
vertical='bottom',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
def make_excel(list, sheetname, office):
import openpyxl, datetime
from openpyxl.styles import Alignment, Border, Side
now = datetime.datetime.now()
wb = openpyxl.Workbook()
sheet = wb.active
sheet.title = sheetname
sh = wb[sheetname]
sh.column_dimensions['A'].width = 60
sh.column_dimensions['B'].width = 40
sh.column_dimensions['C'].width = 30
sh.column_dimensions['I'].width = 15
sh.column_dimensions['F'].width = 15
sh.column_dimensions['K'].width = 15
columns = [
"物件url", "物件名", "住所", "家賃", "管理費", "月々支払い", "敷金", "礼金", "仲介手数料", "面積",
"１m2の値段", "初期費用", "距離"
]
sh.append(columns)
for cells in sh['1']:
cells.alignment = Alignment(horizontal='center')
cells.border = Border(outline=True,
bottom=Side(style="thick", color="FF000000"))
for i in list:
sh.append(i)
for cells in sh['A']:
currow = cells.row
if cells.row != 1:
initialcost = sh.cell(row=currow, column=6).value * 2 + sh.cell(
row=currow, column=5).value + sh.cell(
row=currow, column=6).value + sh.cell(row=currow,
column=7).value
sh.cell(row=currow, column=12).value = initialcost
#get the distance from website
import urllib.parse
from bs4 import BeautifulSoup
from selenium import webdriver
import time
driver = webdriver.PhantomJS()
for loc in sh['C']:
if loc.value != "住所":
locinfo = urllib.parse.quote_plus(loc.value)
base = f"http://r1web.realwork.jp/index_ex.html?{urllib.parse.quote_plus(f'{office}',encoding='utf-8')}&{locinfo}"
driver.get(base)
time.sleep(2)
data = driver.page_source.encode('utf-8')
soup = BeautifulSoup(data, "lxml")
div = soup.find_all("td", {'id': 'meter_r'})
div = str(div)
div = div.replace(
'[<td id="meter_r" style="width: 50mm;border-style: solid;border-color: #E0E0E0">',
'')
div = div.replace('</td>]', '')
print(div)
currow = loc.row
sh.cell(row=currow, column=13).value = div
wb.save(f"media/{sheetname}_{now}.xlsx")
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]
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
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
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
def get (self, request, *args, **kwargs):
print(request.GET.get('campo'))
fecha_sel = request.GET.get('campo')
#fecha_sel_parse =
parse(fecha_sel)
print('fecha ',fecha_sel_parse.date())
query = TiempoMuertDet.objects.all()
query2 = TiempoMuertoEnc.objects.filter(id=tiempo_muerto)
wb = Workbook()
ws = wb.active
ws.tittle='Tiempo Muerto'
#Establer el nombre del archivo
#nombre_archivo = "Reporte Tiempo Muerto" + fecha_sel_parse.date())+ ".xlsx"
#ws['B1'].alignment= Alignment(horizontal='left', vertical='center')
ws['B1'].border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
top=Side(border_style='thin'), bottom=Side(border_style='thin'))
ws['B1'].fill = PatternFill(start_color='66FFCC', end_color='66FFCC', fill_type='solid')
ws['B1'].font = Font(name='calibri', size=12, bold=True)
ws['B1']='Mar Bran S.A. de C.V.'
ws.merge_cells('B1:F1')
ws['B2'].alignment= Alignment(horizontal='left', vertical='center')
ws['B2'].border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
top=Side(border_style='thin'), bottom=Side(border_style='thin'))
ws['B2'].fill = PatternFill(start_color='66FFCC', end_color='66FFCC', fill_type='solid')
ws['B2'].font = Font(name='calibri', size=12, bold=True)
ws['B2']='Innovación, Mejora Continua y Six Sigma'
ws.merge_cells('B2:F2')
ws['B3'].alignment= Alignment(horizontal='left', vertical='center')
ws['B3'].border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
top=Side(border_style='thin'), bottom=Side(border_style='thin'))
ws['B3'].fill = PatternFill(start_color='66FFCC', end_color='66FFCC', fill_type='solid')
ws['B3'].font = Font(name='calibri', size=12, bold=True)
ws['B3']='Reporte de Tiempos Muertos'
ws.merge_cells('B3:F3')
ws.row_dimensions[1].height=20
ws.row_dimensions[2].height=20
ws.row_dimensions[3].height=20
ws.column_dimensions['B'].width=20
ws.column_dimensions['C'].width=20
ws.column_dimensions['D'].width=20
ws.column_dimensions['E'].width=20
ws['B6'].alignment= Alignment(horizontal='center', vertical='center')
ws['B6'].border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
top=Side(border_style='thin'), bottom=Side(border_style='thin'))
ws['B6'].fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid')
ws['B6'].font = Font(name='calibri', size=11, bold=True)
ws['B6']='Fecha de producciòn'
ws['C6'].alignment= Alignment(horizontal='center', vertical='center')
ws['C6'].border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
top=Side(border_style='thin'), bottom=Side(border_style='thin'))
ws['C6'].fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid')
ws['C6'].font = Font(name='calibri', size=11, bold=True)
ws['C6']='Turno'
controlador = 7
for q in query:
ws.cell(row=controlador,column=2).alignment= Alignment(horizontal='center', vertical='center')
ws.cell(row=controlador,column=2).border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
top=Side(border_style='thin'), bottom=Side(border_style='thin'))
ws.cell(row=controlador,column=2).fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid')
ws.cell(row=controlador,column=2).font = Font(name='calibri', size=11, bold=True)
ws.cell(row=controlador,column=2).value=q.fecha_produccion
ws.cell(row=controlador,column=3).alignment= Alignment(horizontal='center', vertical='center')
ws.cell(row=controlador,column=3).border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
top=Side(border_style='thin'), bottom=Side(border_style='thin'))
ws.cell(row=controlador,column=3).fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid')
ws.cell(row=controlador,column=3).font = Font(name='calibri', size=11, bold=True)
ws.cell(row=controlador,column=3).value=q.tipo_cambio
#contador+=1
controlador +=1
#Definir el tipo de resupuesta a dar
response = HttpResponse(content_type='application/ms-excel')
contenido = "attachment; filename = {0}".format(nombre_archivo)
response["Content-Disposition"] = contenido
wb.save(response)
return response
def normal_datas_to_excel(normal_data, platform_datas, start, end):
wb = Workbook()
ws = wb.active
ws.title = "平台活动报表"
ws.append(["平台活动报告"] * 4)
ws.append([''])
ws.merge_cells("A1:D2")
for row in range(1, ws.max_row + 1):
for col in range(1, ws.max_column + 1):
ws.cell(row=row,
column=col).fill = PatternFill(patternType="solid",
fgColor="56237f")
ws.cell(row=row, column=col).font = Font(name="黑体",
size=16,
color=colors.WHITE)
ws.cell(row=row,
column=col).alignment = Alignment(horizontal="center",
vertical="center")
ws.append([''] * 4)
ws.append([''] * 4)
ws.append(['日期', start + ' - ' + end])
ws.merge_cells("B5:D5")
ws.append(['活动名', normal_data['activityName']])
ws.merge_cells("B6:D6")
for row in range(5, 7):
for col in range(2, 5):
ws.cell(row=row,
column=col).alignment = Alignment(horizontal="center",
vertical="center")
ws.append(['已使用', normal_data['used'], '未使用', normal_data['remaining']])
ws.append(['参与人数', normal_data['usedPeopleNum'], '补贴金额', ''])
# ws.merge_cells("B8:D8")
# for col in range(2, 5):
# ws.cell(row=ws.max_row, column=col).alignment = Alignment(
# horizontal="center", vertical="center")
platform_datas_to_excel(ws, platform_datas)
left, right, top, bottom = [Side(style='thin', color='000000')] * 4
for row in range(5, 9):
for col in range(1, 5):
ws.cell(row=row, column=col).font = Font(name='DengXian')
ws.cell(row=row, column=col).border = Border(left=left,
right=right,
top=top,
bottom=bottom)
for row in range(15, ws.max_row + 1):
for col in range(1, ws.max_column + 1):
ws.cell(row=15, column=col).fill = PatternFill("solid",
fgColor="deebf7")
ws.cell(row=row, column=col).font = Font(name='DengXian')
ws.cell(row=row, column=col).border = Border(left=left,
right=right,
top=top,
bottom=bottom)
wb.save('/home/fanjindong/notebook/报表/{}-{}-{}_{}.xlsx'.format(
normal_data['appCode'], normal_data['activityId'], start, end))
def _insert_compare_string_results(string_results, ws, row_height):
"""
Insert string result into a worksheet
Parameters
----------
string_results: dict
result to insert
ws: Excel worksheet instance
row_height: float
Height of the row
"""
# construct thick border
thin = Side(border_style="thin", color="000000")
border = Border(top=thin, left=thin, right=thin, bottom=thin)
row_heights = {}
# loop and output result
for result in string_results:
column = result['column']
if not 'result_df' in result.keys():
ws.append([column, result['error_msg']])
for col in ['A', 'B']:
ws['%s%d' % (col, ws.max_row)].style = 'Bad'
ws.append([''])
continue
result_df = result['result_df'][0][['feature', 'value', 'graph']]
value_counts_df = result['result_df'][1]
head_row = _insert_df(result_df, ws)
# if there is value counts result
if len(value_counts_df) > 0:
value_counts_df = value_counts_df.rename(
columns={
'value': 'top 10 values',
'count_x': 'count_1',
'count_y': 'count_2'
})
databar_head = _insert_df(value_counts_df,
ws,
header=True,
head_style='60 % - Accent5')
for row_idx in range(databar_head,
databar_head + value_counts_df.shape[0] + 1):
row_heights[row_idx] = 25
# add conditional formatting: data bar
first = FormatObject(type='num', val=0)
second = FormatObject(type='num',
val=np.max([
value_counts_df['count_1'].max(),
value_counts_df['count_2'].max()
]))
data_bar1 = DataBar(cfvo=[first, second],
color=TABLE1_DARK.replace('#', ''),
showValue=True,
minLength=None,
maxLength=None)
data_bar2 = DataBar(cfvo=[first, second],
color=TABLE2_DARK.replace('#', ''),
showValue=True,
minLength=None,
maxLength=None)
# assign the data bar to a rule
rule1 = Rule(type='dataBar', dataBar=data_bar1)
ws.conditional_formatting.add(
'B%d:B%d' %
(databar_head + 1, databar_head + len(value_counts_df)), rule1)
rule2 = Rule(type='dataBar', dataBar=data_bar2)
ws.conditional_formatting.add(
'C%d:C%d' %
(databar_head + 1, databar_head + len(value_counts_df)), rule2)
# draw the thick outline border
_style_range(ws,
'A%d:C%d' %
(head_row, databar_head + len(value_counts_df)),
border=border)
else:
_style_range(ws,
'A%d:C%d' %
(head_row, head_row + result_df.shape[0] - 1),
border=border)
# add gap
ws.append([''])
_adjust_ws(ws,
row_height=row_height,
row_heights=row_heights,
adjust_type='str')
def create_left(self, path_to_sample_data, palette):
sheet = self.workbook.active
# read groups_schedules to get indices
df = pd.read_csv((cwd / path_to_sample_data).resolve(), index_col=0)
# more options can be specified also
# with pd.option_context('display.max_rows', None, 'display.max_columns', None):
# print(df)
days = [df.columns[i] for i in range(0, len(df.columns), 3)]
time_slots = df.index
# vertical offset
offset = 3
# height of a slot in rows
time_slot_height = self.time_slot_height
# number of time_slots
slots_number = len(time_slots)
# one row for name of day and rows for time_slots
day_height = 1 + slots_number * time_slot_height
week = len(days)
# for each day
for day_index in range(week):
# starting on this row
day_row = offset + day_index * day_height
# set name of day
sheet[f'A{day_row}'] = days[day_index]
# add border
side_style = Side(style='thin')
self.add_border(min_row=day_row,
min_column=1,
max_row=day_row,
max_column=1)
# paint day cell
sheet.cell(day_row, 1).fill = PatternFill("solid",
fgColor=palette['day'])
# for each time slot
for time_slot_index in range(slots_number):
time_slot_row = day_row + 1 + time_slot_index * 3
sheet.merge_cells(f'A{time_slot_row}:A{time_slot_row + 2}')
sheet.row_dimensions[time_slot_row].height = 40
sheet.row_dimensions[time_slot_row + 1].height = 40
sheet.row_dimensions[time_slot_row + 2].height = 20
sheet[f'A{time_slot_row}'] = time_slots[time_slot_index]
sheet[f'A{time_slot_row}'].alignment = Alignment(
vertical="top")
sheet[f'A{time_slot_row}'].border = Border(left=side_style,
top=side_style,
right=side_style,
bottom=side_style)
"""
get lotno
"""
data = []
# get file data
with open(file, encoding='unicode_escape') as f:
csvReader = reader((line.replace('\0', '') for line in f))
for row in csvReader:
data.append(row)
# get lotno
lotno = data[5][1]
return lotno
alignment = Alignment(horizontal='center', vertical='center')
thin = Side(border_style='thin', color=BLACK)
border = Border(top=thin, left=thin, right=thin, bottom=thin)
rowOffset = 5
project = 'Unknown'
totalLotCount = 0
nowTime = 'Unknown'
HWBIN_TO_SWBIN = {
'F28': {
3: {
'SWBin': (1, 2),
'isPassBin': True
},
1: {
'SWBin': (37, 38, 61, 62, 64, 65, 66, 90, 91, 92, 94),
import urllib.request
import time
from bs4 import BeautifulSoup
from selenium import webdriver
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Alignment, Font, Border, Side, NamedStyle, colors
import re
numregex = re.compile('\d+')
###
font = Font(color=colors.BLACK)
font2 = Font(color='578fcc')
font3 = Font(color="ff0000")
fill = PatternFill("solid", fgColor='FF839ce3')
ali = Alignment(horizontal='center', vertical='center', shrinkToFit=True)
thin = Side(border_style="thin", color="000000")
border = Border(top=thin, left=thin, right=thin, bottom=thin)
###
def style_range(ws,
cell_range,
border=Border(),
fill=None,
font=None,
alignment=None):
top = Border(top=border.top)
left = Border(left=border.left)
right = Border(right=border.right)
bottom = Border(bottom=border.bottom)
rows = ws[cell_range]
