def generate_excel(report, name, reporting_start_datetime_local,
reporting_end_datetime_local, period_type):
wb = Workbook()
ws = wb.active
# Row height
ws.row_dimensions[1].height = 102
for i in range(2, 2000 + 1):
ws.row_dimensions[i].height = 42
# Col width
ws.column_dimensions['A'].width = 1.5
ws.column_dimensions['B'].width = 25.0
for i in range(ord('C'), ord('L')):
ws.column_dimensions[chr(i)].width = 15.0
# Font
name_font = Font(name='Constantia', size=15, bold=True)
title_font = Font(name='宋体', size=15, bold=True)
data_font = Font(name='Franklin Gothic Book', size=11)
table_fill = PatternFill(fill_type='solid', fgColor='1F497D')
f_border = Border(left=Side(border_style='medium', color='00000000'),
right=Side(border_style='medium', color='00000000'),
bottom=Side(border_style='medium', color='00000000'),
top=Side(border_style='medium', color='00000000'))
b_border = Border(bottom=Side(border_style='medium', color='00000000'), )
b_c_alignment = Alignment(vertical='bottom',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
c_c_alignment = Alignment(vertical='center',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
b_r_alignment = Alignment(vertical='bottom',
horizontal='right',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
c_r_alignment = Alignment(vertical='bottom',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
# Img
img = Image("excelexporters/myems.png")
img.width = img.width * 0.85
img.height = img.height * 0.85
# img = Image("myems.png")
ws.add_image(img, 'B1')
# Title
ws.row_dimensions[3].height = 60
ws['B3'].font = name_font
ws['B3'].alignment = b_r_alignment
ws['B3'] = 'Name:'
ws['C3'].border = b_border
ws['C3'].alignment = b_c_alignment
ws['C3'].font = name_font
ws['C3'] = name
ws['D3'].font = name_font
ws['D3'].alignment = b_r_alignment
ws['D3'] = 'Period:'
ws['E3'].border = b_border
ws['E3'].alignment = b_c_alignment
ws['E3'].font = name_font
ws['E3'] = period_type
ws['F3'].font = name_font
ws['F3'].alignment = b_r_alignment
ws['F3'] = 'Date:'
ws['G3'].border = b_border
ws['G3'].alignment = b_c_alignment
ws['G3'].font = name_font
ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
ws.merge_cells("G3:H3")
if "reporting_period" not in report.keys() or \
"names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0:
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
####################################################################################################################
current_row_number = 6
reporting_period_data = report['reporting_period']
has_names_data_flag = True
if "names" not in reporting_period_data.keys() or \
reporting_period_data['names'] is None or \
len(reporting_period_data['names']) == 0:
has_names_data_flag = False
if has_names_data_flag:
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 报告期产出'
current_row_number += 1
category = reporting_period_data['names']
ca_len = len(category)
ws.row_dimensions[current_row_number].height = 60
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].border = f_border
col = 'C'
for i in range(0, ca_len):
ws[col + str(current_row_number)].fill = table_fill
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = \
reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
col = chr(ord(col) + 1)
current_row_number += 1
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '产出'
col = 'C'
for i in range(0, ca_len):
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = round(
reporting_period_data['subtotals'][i], 2)
col = chr(ord(col) + 1)
current_row_number += 1
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '环比'
col = 'C'
for i in range(0, ca_len):
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = str(
round(reporting_period_data['increment_rates'][i] * 100, 2)) + '%' \
if reporting_period_data['increment_rates'][i] is not None else '-'
col = chr(ord(col) + 1)
current_row_number += 2
####################################################################################################################
has_values_data = True
has_timestamps_data = True
if 'values' not in reporting_period_data.keys() or \
reporting_period_data['values'] is None or \
len(reporting_period_data['values']) == 0:
has_values_data = False
if 'timestamps' not in reporting_period_data.keys() or \
reporting_period_data['timestamps'] is None or \
len(reporting_period_data['timestamps']) == 0 or \
len(reporting_period_data['timestamps'][0]) == 0:
has_timestamps_data = False
if has_values_data and has_timestamps_data:
ca_len = len(reporting_period_data['names'])
time = reporting_period_data['timestamps'][0]
real_timestamps_len = timestamps_data_not_equal_0(
report['parameters']['timestamps'])
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 详细数据'
current_row_number += 1
chart_start_row_number = current_row_number
current_row_number += ca_len * 6 + real_timestamps_len * 7 + 1
table_start_row_number = current_row_number
ws.row_dimensions[current_row_number].height = 60
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '日期时间'
col = 'C'
for i in range(0, ca_len):
ws[col + str(current_row_number)].fill = table_fill
ws[col + str(current_row_number)].font = title_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = \
reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
col = chr(ord(col) + 1)
current_row_number += 1
for i in range(0, len(time)):
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = time[i]
col = 'C'
for j in range(0, ca_len):
ws[col + str(current_row_number)].font = title_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = round(reporting_period_data['values'][j][i], 2) \
if reporting_period_data['values'][j][i] is not None else 0.00
col = chr(ord(col) + 1)
current_row_number += 1
table_end_row_number = current_row_number - 1
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '小计'
col = 'C'
for i in range(0, ca_len):
ws[col + str(current_row_number)].font = title_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = round(
reporting_period_data['subtotals'][i], 2)
col = chr(ord(col) + 1)
current_row_number += 2
format_time_width_number = 1.0
min_len_number = 1.0
min_width_number = 11.0 # format_time_width_number * min_len_number + 4 and min_width_number > 11.0
if period_type == 'hourly':
format_time_width_number = 4.0
min_len_number = 2
min_width_number = 12.0
elif period_type == 'daily':
format_time_width_number = 2.5
min_len_number = 4
min_width_number = 14.0
elif period_type == 'monthly':
format_time_width_number = 2.1
min_len_number = 4
min_width_number = 12.4
elif period_type == 'yearly':
format_time_width_number = 1.5
min_len_number = 5
min_width_number = 11.5
for i in range(0, ca_len):
line = LineChart()
line.title = '报告期产出 - ' + \
reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
labels = Reference(ws,
min_col=2,
min_row=table_start_row_number + 1,
max_row=table_end_row_number)
line_data = Reference(ws,
min_col=3 + i,
min_row=table_start_row_number,
max_row=table_end_row_number)
line.add_data(line_data, titles_from_data=True)
line.set_categories(labels)
line_data = line.series[0]
line_data.marker.symbol = "circle"
line_data.smooth = True
line.x_axis.crosses = 'min'
line.height = 8.25
line.width = format_time_width_number * len(time) if len(
time) > min_len_number else min_width_number
if line.width > 24:
line.width = 24
line.dLbls = DataLabelList()
line.dLbls.dLblPos = 't'
line.dLbls.showVal = True
line.dLbls.showPercent = False
chart_col = 'B'
chart_cell = chart_col + str(chart_start_row_number)
chart_start_row_number += 6
ws.add_chart(line, chart_cell)
#####################################
has_associated_equipment_flag = True
current_row_number += 2
if "associated_equipment" not in report.keys() or \
"energy_category_names" not in report['associated_equipment'].keys() or \
len(report['associated_equipment']["energy_category_names"]) == 0 \
or 'associated_equipment_names_array' not in report['associated_equipment'].keys() \
or report['associated_equipment']['associated_equipment_names_array'] is None \
or len(report['associated_equipment']['associated_equipment_names_array']) == 0 \
or len(report['associated_equipment']['associated_equipment_names_array'][0]) == 0:
has_associated_equipment_flag = False
if has_associated_equipment_flag:
associated_equipment = report['associated_equipment']
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 相关设备数据'
current_row_number += 1
ws.row_dimensions[current_row_number].height = 60
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].font = name_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '相关设备'
ca_len = len(associated_equipment['energy_category_names'])
for i in range(0, ca_len):
row = chr(ord('C') + i)
ws[row + str(current_row_number)].fill = table_fill
ws[row + str(current_row_number)].font = name_font
ws[row + str(current_row_number)].alignment = c_c_alignment
ws[row + str(current_row_number)].border = f_border
ws[row + str(current_row_number)] = \
reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
associated_equipment_len = len(
associated_equipment['associated_equipment_names_array'][0])
for i in range(0, associated_equipment_len):
current_row_number += 1
row = str(current_row_number)
ws['B' + row].font = title_font
ws['B' + row].alignment = c_c_alignment
ws['B' + row] = associated_equipment[
'associated_equipment_names_array'][0][i]
ws['B' + row].border = f_border
for j in range(0, ca_len):
col = chr(ord('C') + j)
ws[col + row].font = title_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = round(
associated_equipment['subtotals_array'][j][i], 2)
ws[col + row].border = f_border
####################################################################################################################
current_sheet_parameters_row_number = chart_start_row_number + 1
has_parameters_names_and_timestamps_and_values_data = True
if 'parameters' not in report.keys() or \
report['parameters'] is None or \
'names' not in report['parameters'].keys() or \
report['parameters']['names'] is None or \
len(report['parameters']['names']) == 0 or \
'timestamps' not in report['parameters'].keys() or \
report['parameters']['timestamps'] is None or \
len(report['parameters']['timestamps']) == 0 or \
'values' not in report['parameters'].keys() or \
report['parameters']['values'] is None or \
len(report['parameters']['values']) == 0 or \
timestamps_data_all_equal_0(report['parameters']['timestamps']):
has_parameters_names_and_timestamps_and_values_data = False
if has_parameters_names_and_timestamps_and_values_data:
###############################
# new worksheet
###############################
parameters_data = report['parameters']
parameters_names_len = len(parameters_data['names'])
parameters_ws = wb.create_sheet('相关参数')
parameters_timestamps_data_max_len = \
get_parameters_timestamps_lists_max_len(list(parameters_data['timestamps']))
# Row height
parameters_ws.row_dimensions[1].height = 102
for i in range(2, 7 + 1):
parameters_ws.row_dimensions[i].height = 42
for i in range(8, parameters_timestamps_data_max_len + 10):
parameters_ws.row_dimensions[i].height = 60
# Col width
parameters_ws.column_dimensions['A'].width = 1.5
parameters_ws.column_dimensions['B'].width = 25.0
for i in range(3, 12 + parameters_names_len * 3):
parameters_ws.column_dimensions[format_cell.get_column_letter(
i)].width = 15.0
# Img
img = Image("excelexporters/myems.png")
img.width = img.width * 0.85
img.height = img.height * 0.85
# img = Image("myems.png")
parameters_ws.add_image(img, 'B1')
# Title
parameters_ws.row_dimensions[3].height = 60
parameters_ws['B3'].font = name_font
parameters_ws['B3'].alignment = b_r_alignment
parameters_ws['B3'] = 'Name:'
parameters_ws['C3'].border = b_border
parameters_ws['C3'].alignment = b_c_alignment
parameters_ws['C3'].font = name_font
parameters_ws['C3'] = name
parameters_ws['D3'].font = name_font
parameters_ws['D3'].alignment = b_r_alignment
parameters_ws['D3'] = 'Period:'
parameters_ws['E3'].border = b_border
parameters_ws['E3'].alignment = b_c_alignment
parameters_ws['E3'].font = name_font
parameters_ws['E3'] = period_type
parameters_ws['F3'].font = name_font
parameters_ws['F3'].alignment = b_r_alignment
parameters_ws['F3'] = 'Date:'
parameters_ws['G3'].border = b_border
parameters_ws['G3'].alignment = b_c_alignment
parameters_ws['G3'].font = name_font
parameters_ws[
'G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
parameters_ws.merge_cells("G3:H3")
parameters_ws_current_row_number = 6
parameters_ws['B' +
str(parameters_ws_current_row_number)].font = title_font
parameters_ws['B' +
str(parameters_ws_current_row_number)] = name + ' 相关参数'
parameters_ws_current_row_number += 1
parameters_table_start_row_number = parameters_ws_current_row_number
parameters_ws.row_dimensions[
parameters_ws_current_row_number].height = 80
parameters_ws_current_row_number += 1
table_current_col_number = 2
for i in range(0, parameters_names_len):
if len(parameters_data['timestamps'][i]) == 0:
continue
col = format_cell.get_column_letter(table_current_col_number)
parameters_ws[col + str(parameters_ws_current_row_number -
1)].fill = table_fill
parameters_ws[col + str(parameters_ws_current_row_number -
1)].border = f_border
col = format_cell.get_column_letter(table_current_col_number + 1)
parameters_ws[col + str(parameters_ws_current_row_number -
1)].fill = table_fill
parameters_ws[col + str(parameters_ws_current_row_number -
1)].border = f_border
parameters_ws[col + str(parameters_ws_current_row_number -
1)].font = name_font
parameters_ws[col + str(parameters_ws_current_row_number -
1)].alignment = c_c_alignment
parameters_ws[col + str(parameters_ws_current_row_number -
1)] = parameters_data['names'][i]
table_current_row_number = parameters_ws_current_row_number
for j, value in enumerate(list(parameters_data['timestamps'][i])):
col = format_cell.get_column_letter(table_current_col_number)
parameters_ws[col +
str(table_current_row_number)].border = f_border
parameters_ws[col +
str(table_current_row_number)].font = title_font
parameters_ws[
col +
str(table_current_row_number)].alignment = c_c_alignment
parameters_ws[col + str(table_current_row_number)] = value
col = format_cell.get_column_letter(table_current_col_number +
1)
parameters_ws[col +
str(table_current_row_number)].border = f_border
parameters_ws[col +
str(table_current_row_number)].font = title_font
parameters_ws[
col +
str(table_current_row_number)].alignment = c_c_alignment
parameters_ws[col + str(table_current_row_number)] = round(
parameters_data['values'][i][j], 2)
table_current_row_number += 1
table_current_col_number = table_current_col_number + 3
########################################################
# parameters chart and parameters table
########################################################
ws['B' + str(current_sheet_parameters_row_number)].font = title_font
ws['B' + str(current_sheet_parameters_row_number)] = name + ' 相关参数'
current_sheet_parameters_row_number += 1
chart_start_row_number = current_sheet_parameters_row_number
col_index = 0
for i in range(0, parameters_names_len):
if len(parameters_data['timestamps'][i]) == 0:
continue
line = LineChart()
data_col = 3 + col_index * 3
labels_col = 2 + col_index * 3
col_index += 1
line.title = '相关参数 - ' + \
parameters_ws.cell(row=parameters_table_start_row_number, column=data_col).value
labels = Reference(parameters_ws,
min_col=labels_col,
min_row=parameters_table_start_row_number + 1,
max_row=(len(parameters_data['timestamps'][i]) +
parameters_table_start_row_number))
line_data = Reference(
parameters_ws,
min_col=data_col,
min_row=parameters_table_start_row_number,
max_row=(len(parameters_data['timestamps'][i]) +
parameters_table_start_row_number))
line.add_data(line_data, titles_from_data=True)
line.set_categories(labels)
line_data = line.series[0]
line_data.marker.symbol = "circle"
line_data.smooth = True
line.x_axis.crosses = 'min'
line.height = 8.25
line.width = 24
line.dLbls = DataLabelList()
line.dLbls.dLblPos = 't'
line.dLbls.showVal = False
line.dLbls.showPercent = False
chart_col = 'B'
chart_cell = chart_col + str(chart_start_row_number)
chart_start_row_number += 6
ws.add_chart(line, chart_cell)
current_sheet_parameters_row_number = chart_start_row_number
current_sheet_parameters_row_number += 1
##########################################
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
def stats_export(request):
activity_area = ActivityArea.objects.filter(
pk=request.GET.get('activity_area', None)).first()
start_date = date_from_get(request, 'start_date', start_of_business_year())
end_date = date_from_get(request, 'end_date', end_of_business_year())
filename = '{}_{}_{}stats.xlsx'.format(
start_date.strftime('%Y-%m-%d'), end_date.strftime('%Y-%m-%d'),
str(activity_area.pk) + '_' if activity_area else '')
response = HttpResponse(
content_type=
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
response['Content-Disposition'] = 'attachment; filename=' + filename
wb = Workbook()
# Sheet 1: assignments by subscription
ws1 = wb.active
ws1.title = "assignments by subscription"
# header
ws1.cell(1, 1, u"{}".format(Config.vocabulary('member_pl')))
ws1.column_dimensions['A'].width = 40
ws1.cell(1, 2, u"{}".format(_('Arbeitseinsätze')))
ws1.column_dimensions['B'].width = 17
ws1.cell(
1, 3,
u"{}".format(_('{}-Grösse').format(Config.vocabulary('subscription'))))
ws1.column_dimensions['C'].width = 17
# data
for row, subscription in enumerate(
assignments_by_subscription(start_date, end_date, activity_area),
2):
ws1.cell(
row, 1, ", ".join([
member.get_name()
for member in subscription['subscription'].recipients
]))
ws1.cell(row, 2, subscription['assignments'])
ws1.cell(row, 3, subscription['subscription'].totalsize)
# Sheet 2: assignments per day
ws2 = wb.create_sheet(title="assignments per day")
# header
ws2.cell(1, 1, u"{}".format(_('Datum')))
ws2.column_dimensions['A'].width = 20
ws2.cell(1, 2, u"{}".format(_('Arbeitseinsätze geleistet')))
ws2.column_dimensions['B'].width = 17
# data
for row, assignment in enumerate(
assignments_by('day', start_date, end_date, activity_area), 2):
ws2.cell(row, 1, make_naive(assignment['day']))
ws2.cell(row, 2, assignment['count'])
# Sheet 3: slots by day
ws3 = wb.create_sheet(title="slots per day")
# header
ws3.cell(1, 1, u"{}".format(_('Datum')))
ws3.column_dimensions['A'].width = 20
ws3.cell(1, 2, u"{}".format(_('Arbeitseinsätze ausgeschrieben')))
ws3.column_dimensions['B'].width = 17
# data
for row, assignment in enumerate(
slots_by('day', start_date, end_date, activity_area), 2):
ws3.cell(row, 1, make_naive(assignment['day']))
ws3.cell(row, 2, assignment['available'])
# Sheet 4: assignments per member
ws4 = wb.create_sheet(title="assignments per member")
# header
ws4.cell(1, 1, u"{}".format(Config.vocabulary('member')))
ws4.column_dimensions['A'].width = 40
ws4.cell(1, 2, u"{}".format(_('Arbeitseinsätze')))
ws4.column_dimensions['B'].width = 17
# data
members = members_with_assignments(start_date, end_date, activity_area)
for row, member in enumerate(members, 2):
ws4.cell(row, 1, u"{}".format(member))
ws4.cell(row, 2, member.assignments)
wb.save(response)
return response
def create_xlsx(config, data_out, grades_keys, grades_values, bw_out):
"""
Creates and saves the xlsx to disk, containing multiple spreadsheets with the data and analysis
:param config: config file dictionary
:param data_out: 2D array of data to save
:param grades_keys: Keys of the grades
:param grades_values: 2D array of all the grades
:param bw_out: 2D array containing the low, mid, high to create the box plot
:return: Does not return anything
"""
wb = Workbook(write_only=True)
# Probs Output
ws1 = wb.create_sheet('Analysis Output')
# ws1.title = 'Analysis Output'
for row in data_out:
ws1.append(row)
rc_rules_q = config['output']['condFormat']['quartiles']
rule_color_grad_perc = ColorScaleRule(start_type=rc_rules_q['start_type'],
start_value=rc_rules_q['start_value'],
start_color=rc_rules_q['start_color'],
mid_type=rc_rules_q['mid_type'],
mid_value=rc_rules_q['mid_value'],
mid_color=rc_rules_q['mid_color'],
end_type=rc_rules_q['end_type'],
end_value=rc_rules_q['end_value'],
end_color=rc_rules_q['end_color'])
rc_rules_c = config['output']['condFormat']['CDFs']
rule_color_grad_cdfs = ColorScaleRule(start_type=rc_rules_c['start_type'],
start_value=rc_rules_c['start_value'],
start_color=rc_rules_c['start_color'],
mid_type=rc_rules_c['mid_type'],
mid_value=rc_rules_c['mid_value'],
mid_color=rc_rules_c['mid_color'],
end_type=rc_rules_c['end_type'],
end_value=rc_rules_c['end_value'],
end_color=rc_rules_c['end_color'])
# Color the Q1, Q2, Q3 and the 3 CDFs, 1 based index
rows_percentile = ['3', '4', '5']
rows_cdfs = ['15', '16', '17']
last_col = get_column_letter(len(data_out[0]))
for r in rows_percentile:
cond_range = 'B' + r + ':' + last_col + r
ws1.conditional_formatting.add(cond_range, rule_color_grad_perc)
for r in rows_cdfs:
cond_range = 'B' + r + ':' + last_col + r
ws1.conditional_formatting.add(cond_range, rule_color_grad_cdfs)
# Print Raw Grades by Grader data for inspection
ws2 = wb.create_sheet(title='Raw Data By Grader')
ws2.append(grades_keys)
for row in [*itertools.zip_longest(*grades_values)]:
ws2.append(row)
rc_rules_g = config['output']['condFormat']['rawGrades']
rule_color_grad_grades = ColorScaleRule(start_type=rc_rules_g['start_type'],
start_value=rc_rules_g['start_value'],
start_color=rc_rules_g['start_color'],
mid_type=rc_rules_g['mid_type'],
mid_value=rc_rules_g['mid_value'],
mid_color=rc_rules_g['mid_color'],
end_type=rc_rules_g['end_type'],
end_value=rc_rules_g['end_value'],
end_color=rc_rules_g['end_color'])
cond_range = 'A2:' + get_column_letter(len(grades_keys)) + str(max([len(i) for i in grades_values]) + 1)
ws2.conditional_formatting.add(cond_range, rule_color_grad_grades)
# Older compatible Box Whisker Plot
ws3 = wb.create_sheet(title='Box And Whisker')
low = bw_out[0]
mid = np.subtract(bw_out[1], low).tolist()
high = np.subtract(bw_out[2], bw_out[1]).tolist()
bw_rows = [
["Labels"] + grades_keys,
["low"] + low,
["mid"] + mid,
["high"] + high
]
for row in bw_rows:
ws3.append(row)
# Create Chart
chart1 = BarChart(barDir='col', gapWidth='50', grouping='stacked', overlap='100')
chart1.style = 12
chart1.title = 'Box without Whiskers Chart'
chart1.y_axis.title = 'Grade'
chart1.x_axis.title = 'Grader'
chart1.shape = 4
chart1_data = Reference(ws3, min_col=1, min_row=2, max_row=4, max_col=len(bw_rows[0]))
chart1_titles = Reference(ws3, min_col=2, min_row=1, max_row=1, max_col=len(bw_rows[0]))
chart1.add_data(chart1_data, from_rows=True, titles_from_data=True)
chart1.set_categories(chart1_titles)
# TODO REMOVE SHADOW
chart1.ser[0].graphicalProperties.noFill = True
chart1.ser[0].graphicalProperties.line.noFill = True
chart1_config = config['output']['xlsxChart']
chart1.y_axis.scaling.min = chart1_config['y_axis']['min']
chart1.y_axis.scaling.max = chart1_config['y_axis']['max']
chart1.y_axis.majorUnit = chart1_config['y_axis']['unit']
chart1.width = chart1_config['width']
chart1.height = chart1_config['height']
ws3.add_chart(chart1, 'A7')
filename = config['output']['filename'] + '.xlsx'
try:
wb.save(filename=filename)
except IOError as e:
print(e, file=sys.stderr)
print("ERROR: FAILED TO SAVE XLSX! "
"Please make sure the file is not already open", file=sys.stderr)
else:
print("Successfully created %s" % filename)
def create_workbook(env, object_classes, domains, out_file, delim, timestamp,
prepend_timestamp, obfuscate_password):
if prepend_timestamp:
filename = os.path.split(out_file)[-1]
filename = "{}-{}".format(t.timestamp, filename)
path = list(os.path.split(out_file)[:-1])
path.append(filename)
out_file = os.path.join(*path)
wb = Workbook()
logger.info("Querying for configuration")
skip = []
for object_class in object_classes:
header_row = ["appliance", "domain", "Object Class", "Object Name"]
rows = []
ws = wb.create_sheet(title=object_class)
for dp in env.appliances:
if dp in skip:
continue
print "Retrieving {}".format(object_class)
logger.info("Retrieving {} configuration".format(object_class))
print "\t{}".format(dp.hostname)
logger.info("Querying {}".format(dp.hostname))
_domains = domains
if "all-domains" in domains:
try:
_domains = dp.domains
except:
print " ".join(
("ERROR: See log for details,",
"skipping appliance {}".format(dp.hostname)))
logger.exception(" ".join(
("An unhandled exception was raised",
"while retrieving list of domains.",
"Skipping appliance {}.".format(dp.hostname))))
skip.append(dp)
continue
for domain in _domains:
print "\t\t{}".format(domain)
logger.info("Looking in domain {}".format(domain))
xpath = CONFIG_XPATH + object_class
try:
logger.info("Querying {} for {} in domain {}".format(
dp.hostname, object_class, domain))
config = dp.get_config(_class=object_class, domain=domain)
except datapower.AuthenticationFailure:
logger.warn("Recieved AuthenticationFailure."
"Retrying in 5 seconds...")
print " ".join(("Recieved AuthenticationFailure.",
"Retrying in 5 seconds..."))
sleep(5)
try:
config = dp.get_config(_class=object_class,
domain=domain)
except datapower.AuthenticationFailure:
print "Received AuthenticationFailure again. Skipping."
logger.error(
"Received AuthenticationFailure again. Skipping.")
skip.append(dp)
continue
except:
print " ".join(
("ERROR: See log for details,",
"skipping appliance {}".format(dp.hostname)))
logger.exception(" ".join(
("An unhandled exception was raised.",
"Skipping appliance {}.".format(dp.hostname))))
skip.append(dp)
break
nodes = config.xml.findall(xpath)
for index, node in enumerate(nodes):
name = node.get("name")
logger.info("Found node {} - {}".format(node.tag, name))
row = [dp.hostname, domain, object_class, name]
row.extend([None] * 1500)
for child in list(node):
logger.info(
"Found child node {}. recursing...".format(child))
_recurse_config(child.tag, child, row, header_row,
delim, obfuscate_password)
rows.append(row)
rows.insert(0, header_row)
for row in rows:
ws.append(row)
wb.remove_sheet(wb.worksheets[0])
logger.info("writing workbook {}".format(out_file))
wb.save(out_file)
def demo(displayHeartbeat):
response = connect_to_stream()
if response.status_code != 200:
print(response.text)
return
states_collection = list()
item = 1
current_date = datetime.now().strftime('%d-%m-%Y')
workbook = Workbook()
dest_filename = str(current_date) + '.xlsx'
worksheet1 = workbook.active
worksheet1.title = 'Data log'
worksheet1.cell(row=1, column=1).value = 'date_time'
worksheet1.cell(row=1, column=2).value = 'status'
worksheet1.cell(row=1, column=3).value = 'ask_closeout'
worksheet1.cell(row=1, column=4).value = 'bid_closeout'
worksheet1.cell(row=1, column=5).value = 'ask'
worksheet1.cell(row=1, column=6).value = 'bid'
worksheet1.cell(row=1, column=7).value = 'spread'
for line in response.iter_lines(1):
if line:
try:
line = line.decode('utf-8')
msg = json.loads(line)
except Exception as e:
print("Caught exception when converting message into json\n" +
str(e))
return
if "instrument" in msg or "tick" in msg or displayHeartbeat:
print("item =", item)
cur_state = dict()
if msg['type'] == 'PRICE':
cur_state['date_time'] = datetime.strftime(
datetime.strptime(msg['time'].split('.')[0],
'%Y-%m-%dT%H:%M:%S'),
'%d.%m.%Y %H:%M:%S')
cur_state['status'] = msg['status']
cur_state['instruments'] = msg['instrument']
cur_state['ask_closeout'] = float(msg['closeoutAsk'])
cur_state['bid_closeout'] = float(msg['closeoutBid'])
cur_state['ask'] = float(msg['asks'][0]['price'])
cur_state['bid'] = float(msg['bids'][0]['price'])
cur_state['spread'] = float(
'%.5f' % (float(msg['asks'][0]['price']) -
float(msg['bids'][0]['price'])))
else:
replacement = len(states_collection) - 1
cur_state['date_time'] = datetime.strftime(
datetime.strptime(msg['time'].split('.')[0],
'%Y-%m-%dT%H:%M:%S'),
'%d.%m.%Y %H:%M:%S')
cur_state['status'] = 'HEARTBEAT'
cur_state['ask_closeout'] = states_collection[replacement][
'ask_closeout']
cur_state['bid_closeout'] = states_collection[replacement][
'bid_closeout']
cur_state['ask'] = states_collection[replacement]['ask']
cur_state['bid'] = states_collection[replacement]['bid']
cur_state['spread'] = states_collection[replacement][
'spread']
print(cur_state)
states_collection.append(cur_state)
if len(states_collection) < 15:
pass
else:
print('TO DELETE:', states_collection[-15]['date_time'])
del (states_collection[-15])
print('len(states_collection)', len(states_collection))
# save_state = item % 12
# ocup_lines = 1
# if save_state == 0:
# row = ocup_lines + 1
# print('row', type(row), ' ', row)
# iter_row = len(states_collection) - 12
# print('iter_row', type(iter_row), ' ', iter_row)
for data_row in states_collection:
worksheet1.cell(
row=item + 1, column=1).value = states_collection[
len(states_collection) - 1]['date_time']
worksheet1.cell(row=item + 1,
column=2).value = states_collection[
len(states_collection) - 1]['status']
worksheet1.cell(
row=item + 1, column=3).value = states_collection[
len(states_collection) - 1]['ask_closeout']
worksheet1.cell(
row=item + 1, column=4).value = states_collection[
len(states_collection) - 1]['bid_closeout']
worksheet1.cell(row=item + 1,
column=5).value = states_collection[
len(states_collection) - 1]['ask']
worksheet1.cell(row=item + 1,
column=6).value = states_collection[
len(states_collection) - 1]['bid']
worksheet1.cell(row=item + 1,
column=7).value = states_collection[
len(states_collection) - 1]['spread']
workbook.save(filename=dest_filename)
print('SAVED')
# ocup_lines += 12
# else:
# pass
item += 1
def startWorkbook():
global wb
wb = Workbook()
global ws
ws = wb.active
def make_xlsx_file(renderd_data):
data = json.loads(renderd_data)
header = [
'Sr.No', 'RM Name', 'UOM',
'Total Production Qty Till {0}'.format(data.get("from_date")),
'Pending PO Qty Till {0}'.format(data.get("from_date")),
'Current Stock'
]
header_2 = [
'Required', 'Expected PO', 'Short/Excess with PO',
'Short/Excess without PO'
]
book = Workbook()
sheet = book.active
row = 1
col = 1
cell = sheet.cell(row=row, column=col)
cell.value = 'Planning Master'
cell.font = cell.font.copy(bold=True)
cell.alignment = cell.alignment.copy(horizontal="center",
vertical="center")
cell.fill = PatternFill(start_color='ffff00',
end_color='ffff00',
fill_type='solid')
cell = sheet.cell(row=row, column=col + 1)
cell.value = data.get("planning_master")
cell.font = cell.font.copy(bold=True)
cell.alignment = cell.alignment.copy(horizontal="center",
vertical="center")
cell.fill = PatternFill(start_color='ffff00',
end_color='ffff00',
fill_type='solid')
cell = sheet.cell(row=row, column=col + 3)
cell.value = 'From Date'
cell.font = cell.font.copy(bold=True)
cell.alignment = cell.alignment.copy(horizontal="center",
vertical="center")
cell.fill = PatternFill(start_color='ffff00',
end_color='ffff00',
fill_type='solid')
cell = sheet.cell(row=row, column=col + 4)
cell.value = data.get('from_date')
cell.font = cell.font.copy(bold=True)
cell.alignment = cell.alignment.copy(horizontal="center",
vertical="center")
cell.fill = PatternFill(start_color='ffff00',
end_color='ffff00',
fill_type='solid')
cell = sheet.cell(row=row, column=col + 6)
cell.value = 'To Date'
cell.font = cell.font.copy(bold=True)
cell.alignment = cell.alignment.copy(horizontal="center",
vertical="center")
cell.fill = PatternFill(start_color='ffff00',
end_color='ffff00',
fill_type='solid')
cell = sheet.cell(row=row, column=col + 7)
cell.value = data.get('to_date')
cell.font = cell.font.copy(bold=True)
cell.alignment = cell.alignment.copy(horizontal="center",
vertical="center")
cell.fill = PatternFill(start_color='ffff00',
end_color='ffff00',
fill_type='solid')
cell = sheet.cell(row=row, column=col + 9)
cell.value = 'Description'
cell.font = cell.font.copy(bold=True)
cell.alignment = cell.alignment.copy(horizontal="center",
vertical="center")
cell.fill = PatternFill(start_color='ffff00',
end_color='ffff00',
fill_type='solid')
cell = sheet.cell(row=row, column=col + 10)
cell.value = data.get('description')
cell.font = cell.font.copy(bold=True)
cell.alignment = cell.alignment.copy(horizontal="center",
vertical="center")
cell.fill = PatternFill(start_color='ffff00',
end_color='ffff00',
fill_type='solid')
row = 2
col = 1
for item in header:
cell = sheet.cell(row=row, column=col)
cell.value = item
cell.font = cell.font.copy(bold=True)
cell.alignment = cell.alignment.copy(horizontal="center",
vertical="center")
cell.fill = PatternFill(start_color='1E90FF',
end_color='1E90FF',
fill_type='solid')
sheet.merge_cells(start_row=2,
start_column=col,
end_row=3,
end_column=col)
col += 1
col = 7
end_col = 10
col_1 = 7
for date in data.get("date_list"):
cell = sheet.cell(row=2, column=col)
cell.value = date
cell.font = cell.font.copy(bold=True)
cell.alignment = cell.alignment.copy(horizontal="center",
vertical="center")
cell.fill = PatternFill(start_color='1E90FF',
end_color='1E90FF',
fill_type='solid')
sheet.merge_cells(start_row=2,
start_column=col,
end_row=2,
end_column=end_col)
for raw in header_2:
cell = sheet.cell(row=3, column=col_1)
cell.value = raw
cell.font = cell.font.copy(bold=True)
cell.alignment = cell.alignment.copy(horizontal="center",
vertical="center")
cell.fill = PatternFill(start_color='1E90FF',
end_color='1E90FF',
fill_type='solid')
sheet.merge_cells(start_row=3,
start_column=col_1,
end_row=3,
end_column=col_1)
col_1 += 1
end_col += 4
col += 4
row = 4
col = 1
count = 0
for item in data.get("table_data"):
cell = sheet.cell(row=row, column=col)
cell.value = count + 1
cell.alignment = cell.alignment.copy(horizontal="center",
vertical="center")
sheet.merge_cells(start_row=row,
start_column=col,
end_row=row,
end_column=col)
cell = sheet.cell(row=row, column=col + 1)
cell.value = item.get("item_code") + '-' + item.get("item_name")
cell.alignment = cell.alignment.copy(horizontal="center",
vertical="center")
sheet.merge_cells(start_row=row,
start_column=col,
end_row=row,
end_column=col)
cell = sheet.cell(row=row, column=col + 2)
cell.value = item.get("stock_uom")
cell.alignment = cell.alignment.copy(horizontal="center",
vertical="center")
sheet.merge_cells(start_row=row,
start_column=col,
end_row=row,
end_column=col)
cell = sheet.cell(row=row, column=col + 3)
cell.value = item.get("planned_qty")
if item.get("planned_qty") < 0:
cell.fill = PatternFill(start_color='ff0000',
end_color='ff0000',
fill_type='solid')
cell.alignment = cell.alignment.copy(horizontal="center",
vertical="center")
sheet.merge_cells(start_row=row,
start_column=col,
end_row=row,
end_column=col)
cell = sheet.cell(row=row, column=col + 4)
cell.value = item.get("pending_qty")
if item.get("pending_qty") < 0:
cell.fill = PatternFill(start_color='ff0000',
end_color='ff0000',
fill_type='solid')
cell.alignment = cell.alignment.copy(horizontal="center",
vertical="center")
sheet.merge_cells(start_row=row,
start_column=col,
end_row=row,
end_column=col)
cell = sheet.cell(row=row, column=col + 5)
cell.value = item.get("ohs_qty")
if item.get("ohs_qty") < 0:
cell.fill = PatternFill(start_color='ff0000',
end_color='ff0000',
fill_type='solid')
cell.alignment = cell.alignment.copy(horizontal="center",
vertical="center")
sheet.merge_cells(start_row=row,
start_column=col,
end_row=row,
end_column=col)
date_col = 7
for date in data.get("date_list"):
cell = sheet.cell(row=row, column=date_col)
cell.value = item.get(date).get("required_qty")
if flt(item.get(date).get("required_qty")) < 0:
cell.fill = PatternFill(start_color='ff0000',
end_color='ff0000',
fill_type='solid')
cell.alignment = cell.alignment.copy(horizontal="center",
vertical="center")
sheet.merge_cells(start_row=row,
start_column=date_col,
end_row=row,
end_column=date_col)
date_col += 1
cell = sheet.cell(row=row, column=date_col)
cell.value = item.get(date).get("expected_po")
if flt(item.get(date).get("expected_po")) < 0:
cell.fill = PatternFill(start_color='ff0000',
end_color='ff0000',
fill_type='solid')
elif flt(item.get(date).get("expected_po")) > 0:
cell.fill = PatternFill(start_color='ffff00',
end_color='ff0000',
fill_type='solid')
cell.alignment = cell.alignment.copy(horizontal="center",
vertical="center")
sheet.merge_cells(start_row=row,
start_column=date_col,
end_row=row,
end_column=date_col)
date_col += 1
cell = sheet.cell(row=row, column=date_col)
cell.value = item.get(date).get("with_po")
if flt(item.get(date).get("with_po")) < 0:
cell.fill = PatternFill(start_color='ff0000',
end_color='ff0000',
fill_type='solid')
cell.alignment = cell.alignment.copy(horizontal="center",
vertical="center")
sheet.merge_cells(start_row=row,
start_column=date_col,
end_row=row,
end_column=date_col)
date_col += 1
cell = sheet.cell(row=row, column=date_col)
cell.value = item.get(date).get("with_out_po")
if flt(item.get(date).get("with_out_po")) < 0:
cell.fill = PatternFill(start_color='ff0000',
end_color='ff0000',
fill_type='solid')
cell.alignment = cell.alignment.copy(horizontal="center",
vertical="center")
sheet.merge_cells(start_row=row,
start_column=date_col,
end_row=row,
end_column=date_col)
date_col += 1
count += 1
row += 1
file_path = frappe.utils.get_site_path("public")
now = datetime.now()
fname = "MRP_RM_WISE_REPORT" + nowdate() + ".xlsx"
book.save(file_path + fname)
def main():
apb = ArgumentParserBuilder()
apb.add_options(
opt('--num_models',
type=int,
default=1,
help='number of models to train and evaluate'),
opt('--hop_size',
type=float,
default=0.05,
help='hop size for threshold'),
opt('--dataset_path',
type=str,
default='/data/speaker-id-split-medium'),
opt('--exp_type',
type=str,
choices=['hey_firefox', 'hey_snips'],
default='hey_firefox'), opt('--seed', type=int, default=0),
opt('--noiseset_path', type=str, default='/data/MS-SNSD'))
args = apb.parser.parse_args()
random.seed(args.seed)
# Preapring xlsx objects
clean_wb = Workbook()
noisy_wb = Workbook()
now = datetime.now()
clean_sheets = {}
noisy_sheets = {}
col_mapping = {
'Dev positive': 'B',
'Dev noisy positive': 'B',
'Dev negative': 'H',
'Dev noisy negative': 'H',
'Test positive': 'N',
'Test noisy positive': 'N',
'Test negative': 'T',
'Test noisy negative': 'T'
}
# total sample counts, these number can be found when the datasets are loaded for training
if args.exp_type == "hey_firefox":
total_counts = {
'Dev positive': '76',
'Dev negative': '2531',
'Test positive': '54',
'Test negative': '2504'
}
elif args.exp_type == "hey_snips":
total_counts = {
'Dev positive': '2484',
'Dev negative': '13598',
'Test positive': '2529',
'Test negative': '13943'
}
metrics = [
'threshold',
'tp',
'tn',
'fp',
'fn',
]
clean_col_names = [
'Dev positive', 'Dev negative', 'Test positive', 'Test negative'
]
def round_and_convert_to_str(n):
return str(round(n, 2))
raw_thresholds = np.arange(0, 1.000001, args.hop_size)
thresholds = list(map(round_and_convert_to_str, raw_thresholds))
# compute metrics for the overall experiments
clean_cols_aggregated = {}
noisy_cols_aggregated = {}
def compute_aggregated_metrics(sheet, col_idx, results):
sheet[col_idx + '3'] = str(results.mean())
sheet[col_idx + '4'] = str(results.std())
sheet[col_idx + '5'] = str(np.percentile(results, 90))
sheet[col_idx + '6'] = str(np.percentile(results, 95))
sheet[col_idx + '7'] = str(np.percentile(results, 99))
sheet[col_idx + '8'] = str(results.sum())
def get_cell_idx(char, ind):
return chr(ord(char) + ind)
def prepare_report(sheet):
sheet['A3'] = 'mean'
sheet['A4'] = 'std'
sheet['A5'] = 'p90'
sheet['A6'] = 'p95'
sheet['A7'] = 'p99'
sheet['A8'] = 'sum'
for col in clean_col_names:
cell_idx = col_mapping[col] + '1'
sheet[cell_idx] = col
cell_idx = get_cell_idx(col_mapping[col], 1) + '1'
sheet[cell_idx] = total_counts[col]
for i, metric in enumerate(metrics):
cell_idx = get_cell_idx(col_mapping[col], i) + '2'
sheet[cell_idx] = metric
# reports are generated for each threshold and each gets a separate sheet
for idx, threshold in enumerate(thresholds):
clean_sheets[threshold] = clean_wb.create_sheet(threshold, idx)
prepare_report(clean_sheets[threshold])
noisy_sheets[threshold] = noisy_wb.create_sheet(threshold, idx)
prepare_report(noisy_sheets[threshold])
for col_name in clean_col_names:
for metric_idx, metric in enumerate(metrics):
target_metric = threshold + '_' + get_cell_idx(
col_mapping[col_name], metric_idx)
clean_cols_aggregated[target_metric] = []
noisy_cols_aggregated[target_metric] = []
# reports are generated at exp_results
os.system('mkdir -p exp_results')
dt_string = now.strftime('%b-%d-%H-%M')
clean_file_name = 'exp_results/' + args.exp_type + '_clean_' + dt_string + '.xlsx'
clean_wb.save(clean_file_name)
print('\treport for clean setting is generated at ', clean_file_name)
noisy_file_name = 'exp_results/' + args.exp_type + '_noisy_' + dt_string + '.xlsx'
noisy_wb.save(noisy_file_name)
print('\treport for noisy setting is generated at ', noisy_file_name)
# Training settings
os.environ['DATASET_PATH'] = args.dataset_path
os.environ['WEIGHT_DECAY'] = '0.00001'
os.environ['LEARNING_RATE'] = '0.01'
os.environ['LR_DECAY'] = '0.98'
os.environ['BATCH_SIZE'] = '16'
os.environ['MAX_WINDOW_SIZE_SECONDS'] = '0.5'
os.environ['USE_NOISE_DATASET'] = 'True'
os.environ['NUM_MELS'] = '40'
os.environ['NOISE_DATASET_PATH'] = args.noiseset_path
if args.exp_type == "hey_firefox":
os.environ['NUM_EPOCHS'] = '300'
os.environ['VOCAB'] = '[" hey","fire","fox"]'
os.environ['INFERENCE_SEQUENCE'] = '[0,1,2]'
elif args.exp_type == "hey_snips":
os.environ['NUM_EPOCHS'] = '100'
os.environ['VOCAB'] = '[" hey","snips"]'
os.environ['INFERENCE_SEQUENCE'] = '[0,1]'
seeds = []
print('-- training ', args.num_models, ' models --')
training_commands = []
training_envs = []
def get_workspace_path(exp_type, seed):
return os.getcwd() + '/workspaces/exp_' + exp_type + '_res8/' + str(
seed)
# generate commands to run along with the environments
for i in range(args.num_models):
seed = str(random.randint(1, 1000000))
seeds.append(seed)
env = {}
env['SEED'] = seed
workspace_path = get_workspace_path(args.exp_type, seed)
os.system('mkdir -p ' + workspace_path)
command = 'python -m training.run.train --model res8 --workspace ' + workspace_path + ' -i ' + args.dataset_path
training_commands.append(command)
training_envs.append(env)
print('seeds for each model: ', seeds)
run_batch_commands(training_commands, training_envs)
print('-- evaluating each models --')
eval_commands = []
eval_envs = []
for seed in seeds:
for threshold_idx, threshold in enumerate(thresholds):
env = {}
env['SEED'] = seed
env['INFERENCE_THRESHOLD'] = threshold
workspace_path = get_workspace_path(args.exp_type, seed)
command = 'python -m training.run.train --eval --model res8 --workspace ' + workspace_path + ' -i ' + args.dataset_path
result_path = workspace_path + '/' + threshold + '_results.csv'
# if evaluation is done previously, we skip it
if not path.exists(result_path):
eval_commands.append(command)
eval_envs.append(env)
run_batch_commands(eval_commands, eval_envs)
print('-- generating reports --')
for seed_idx, seed in enumerate(seeds):
for threshold_idx, threshold in enumerate(thresholds):
clean_sheet = clean_sheets[threshold]
noisy_sheet = noisy_sheets[threshold]
row = str(seed_idx + 10)
clean_sheet['A' + row] = seed
noisy_sheet['A' + row] = seed
workspace_path = get_workspace_path(args.exp_type, seed)
result_path = workspace_path + '/' + threshold + '_results.csv'
raw_result = subprocess.check_output(
['tail', '-n', '8', result_path]).decode('utf-8')
results = raw_result.split('\n')
# parse and update the report
for result in results:
if len(result) == 0:
break
vals = result.split(',')
key = vals[0]
start_col = col_mapping[key]
if 'noisy' in key:
for metric_idx, metric in enumerate(vals[1:]):
col_idx = get_cell_idx(start_col, metric_idx)
cell_ind = col_idx + str(row)
noisy_sheet[cell_ind] = metric
target_metric = threshold + '_' + col_idx
noisy_cols_aggregated[target_metric].append(
float(metric))
compute_aggregated_metrics(
noisy_sheet, col_idx,
np.array(noisy_cols_aggregated[target_metric]))
else:
for metric_idx, metric in enumerate(vals[1:]):
col_idx = get_cell_idx(start_col, metric_idx)
cell_ind = col_idx + str(row)
clean_sheet[cell_ind] = metric
target_metric = threshold + '_' + col_idx
clean_cols_aggregated[target_metric].append(
float(metric))
compute_aggregated_metrics(
clean_sheet, col_idx,
np.array(clean_cols_aggregated[target_metric]))
clean_wb.save(clean_file_name)
noisy_wb.save(noisy_file_name)
print('-- report generation has been completed --')
print('\treport for clean setting is generated at ', clean_file_name)
print('\treport for noisy setting is generated at ', noisy_file_name)
def post(self, request, *args, **kwargs):
if (request.POST.get('save')):
studid = request.POST.getlist('studid')
points = []
points.append(request.POST.getlist('points1'))
points.append(request.POST.getlist('points2'))
points.append(request.POST.getlist('points3'))
points.append(request.POST.getlist('points4'))
points.append(request.POST.getlist('points6'))
semester = request.POST.getlist('semester')
arr_size = len(studid)
checkpoint = CheckPoint.objects.all()
discipline = Discipline.objects.get(id=self.kwargs['pk'])
exam = Exam.objects.get(discipline__id=self.kwargs['pk'],
semester__id=semester[0])
for i in range(0, arr_size):
st = Student.objects.get(id=studid[i])
k = 0
exammarks = ExamMarks.objects.filter(
exam__discipline__id=discipline.id,
exam__semester__id=semester[i]).get(student=st)
exammarks.examPoints = float(points[4][i].replace(',', '.'))
exammarks.inPoints = float(points[3][i].replace(',', '.'))
totalPoints = exammarks.examPoints + exammarks.inPoints
for ch in checkpoint:
brspoints = BRSpoints.objects.filter(
brs__discipline__id=discipline.id,
brs__semester__id=semester[i]).filter(
CheckPoint=ch).get(student=st)
if (k != 4):
brspoints.points = float(points[k][i].replace(
',', '.'))
k = k + 1
else:
brspoints.points = totalPoints
k = 0
brspoints.save()
tempMarkSymbol = get_markSymbol(exam.controlType.name,
totalPoints)
tempMark = get_mark(exam.controlType.name, totalPoints)
# if (tempMarkSymbol is None):
# newMarkSymbol = None
# else:
# newMarkSymbol = MarkSymbol.objects.filter(name=tempMarkSymbol).first()
# if (newMarkSymbol is None):
# newMarkSymbol = MarkSymbol.objects.create(name=tempMarkSymbol)
newMark = 2
# exammarks.markSymbol = newMarkSymbol
exammarks.mark_symbol = tempMarkSymbol if tempMarkSymbol else ''
exammarks.mark = newMark
exammarks.save()
return redirect('brs_studentlist', pk=self.kwargs['pk'])
elif request.POST.get('vedomost'):
# определяем стили
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)
number_format = 'General'
protection = Protection(locked=True, hidden=False)
# объект
wb = Workbook()
# активный лист
ws = wb.active
# название страницы
# ws = wb.create_sheet('первая страница', 0)
ws.title = 'первая страница'
# текущее время
p = datetime.now()
# данные для строк
group_name = str(request.POST.get('selected_group'))
disc_id = self.kwargs['pk']
studid = request.POST.getlist('studid')
group = Group.objects.get(name=group_name)
inpoints = request.POST.getlist('points4')
exampoints = request.POST.getlist('points6')
semester = request.POST.getlist('semester')
exam = Exam.objects.get(discipline__id=disc_id,
semester_id=semester[0])
arr_size = len(studid)
_row = 12
_column = 4
k = 1
z = 0
zachteno = 0
ne_zachteno = 0
ne_attest = 0
otl = 0
horosho = 0
udovl = 0
neudovl = 0
ne_yavka = 0
for i in range(0, arr_size):
gl = Student.objects.get(id=studid[i])
ws.cell(row=_row, column=1).value = str(k)
k += 1
ws.cell(row=_row, column=2).value = gl.FIO
ws.cell(row=_row, column=3).value = gl.StudentID
ws.cell(row=_row, column=_column).value = str(
float(inpoints[i].replace(',', '.'))).replace('.', ',')
ws.cell(row=_row, column=_column + 1).value = str(
float(exampoints[i].replace(',', '.'))).replace('.', ',')
totalpoints = float(inpoints[i].replace(',', '.')) + float(
exampoints[i].replace(',', '.'))
ws.cell(row=_row,
column=_column + 2).value = str(totalpoints).replace(
'.', ',')
ws.cell(row=_row,
column=_column + 3).value = get_mark_vedomost(
exam.controlType.name,
float(inpoints[i].replace(',', '.')),
float(exampoints[i].replace(',', '.')))
ws.cell(row=_row, column=_column + 4).value = get_markSymbol(
exam.controlType.name, totalpoints)
_row += 1
z += 1
zk = z + 11
zp = z + 14
zp2 = zp + 7
if group.year is None:
year = 'отсутствует'
else:
year = group.year
if exam.discipline.lecturer is None:
fio = 'отсутствует'
else:
fio = exam.discipline.lecturer.FIO
ws.cell(
row=1, column=1
).value = 'ФГАОУ ВО «Северо-Восточный федеральный университет им.М.К.Аммосова'
ws.cell(row=2,
column=1).value = 'Институт математики и информатики'
ws.cell(row=3, column=1
).value = 'Ведомость текущей и промежуточной аттестации'
ws.cell(row=5, column=1).value = 'Семестр: ' + str(
exam.semester.name
) + ', ' + exam.eduperiod.beginyear + '-' + exam.eduperiod.endyear + ' уч.г.'
ws.cell(row=6, column=1).value = 'Форма контроля:'
ws.cell(row=6, column=3).value = exam.controlType.name
ws.cell(row=6, column=5).value = 'курс ' + str(year)
ws.cell(row=6, column=6).value = 'группа:'
ws.cell(row=6, column=7).value = group_name
ws.cell(row=7, column=1).value = 'Дисциплина:'
ws.cell(row=7, column=3).value = exam.discipline.name
ws.cell(row=8,
column=1).value = 'Фамилия, имя, отчество преподавателя:'
ws.cell(row=8, column=4).value = fio
ws.cell(row=9, column=1).value = 'Дата проведения зачета/экзамена:'
ws.cell(row=9, column=3).value = exam.examDate
ws.cell(row=11, column=1).value = '№'
ws.cell(row=11, column=2).value = 'Фамилия, имя, отчество'
ws.cell(row=11, column=3).value = '№ зачетной книжки'
ws.cell(
row=11,
column=4).value = 'Сумма баллов за текущую работу-рубеж.срез'
ws.cell(
row=11, column=5
).value = 'Баллы ' + exam.controlType.name + ' (бонусные баллы)'
ws.cell(row=11, column=6).value = 'Всего баллов'
ws.cell(row=11, column=7).value = 'Оценка прописью'
ws.cell(row=11, column=8).value = 'Буквенный эквивалент'
ws.cell(row=11, column=9).value = 'Подпись преподавателя'
ws.cell(row=zp, column=2).value = 'зачтено'
ws.cell(row=zp + 1, column=2).value = 'не зачтено'
ws.cell(row=zp + 2, column=2).value = 'не аттест'
ws.cell(row=zp + 3, column=2).value = '5(отлично)'
ws.cell(row=zp + 4, column=2).value = '4(хорошо)'
ws.cell(row=zp + 5, column=2).value = '3(удовл)'
ws.cell(row=zp + 6, column=2).value = '2(неудовл)'
ws.cell(row=zp2, column=2).value = 'не явка'
ws.cell(row=zp, column=5).value = 'Сумма баллов'
ws.cell(row=zp + 1, column=5).value = '95-100'
ws.cell(row=zp + 2, column=5).value = '85-94,9'
ws.cell(row=zp + 3, column=5).value = '75-84,9'
ws.cell(row=zp + 4, column=5).value = '65-74,9'
ws.cell(row=zp + 5, column=5).value = '55-64,9'
ws.cell(row=zp + 6, column=5).value = '25-54,9'
ws.cell(row=zp2, column=5).value = '0-24,9'
ws.cell(row=zp, column=7).value = 'Буквенный эквивалент оценки'
ws.cell(row=zp + 1, column=7).value = 'A'
ws.cell(row=zp + 2, column=7).value = 'B'
ws.cell(row=zp + 3, column=7).value = 'C'
ws.cell(row=zp + 4, column=7).value = 'D'
ws.cell(row=zp + 5, column=7).value = 'E'
ws.cell(row=zp + 6, column=7).value = 'FX'
ws.cell(row=zp2, column=7).value = 'F'
ws.cell(row=zp + 10,
column=2).value = 'Директор ИМИ СВФУ____________________'
ws.cell(row=zp + 10, column=4).value = 'В.И.Афанасьева'
# объединение ячеек
ws.merge_cells('A1:I1')
ws.merge_cells('A2:I2')
ws.merge_cells('A3:I3')
ws.merge_cells('A5:B5')
ws.merge_cells('A6:B6')
ws.merge_cells('C6:D6')
ws.merge_cells('A7:B7')
ws.merge_cells('C7:G7')
ws.merge_cells('A8:C8')
ws.merge_cells('D8:G8')
ws.merge_cells('A9:B9')
ws.merge_cells('C9:D9')
ws.merge_cells('E' + str(zp) + ':F' + str(zp))
ws.merge_cells('E' + str(zp + 1) + ':F' + str(zp + 1))
ws.merge_cells('E' + str(zp + 2) + ':F' + str(zp + 2))
ws.merge_cells('E' + str(zp + 3) + ':F' + str(zp + 3))
ws.merge_cells('E' + str(zp + 4) + ':F' + str(zp + 4))
ws.merge_cells('E' + str(zp + 5) + ':F' + str(zp + 5))
ws.merge_cells('E' + str(zp + 6) + ':F' + str(zp + 6))
ws.merge_cells('E' + str(zp + 7) + ':F' + str(zp + 7))
ws.merge_cells('G' + str(zp) + ':H' + str(zp))
ws.merge_cells('G' + str(zp + 1) + ':H' + str(zp + 1))
ws.merge_cells('G' + str(zp + 2) + ':H' + str(zp + 2))
ws.merge_cells('G' + str(zp + 3) + ':H' + str(zp + 3))
ws.merge_cells('G' + str(zp + 4) + ':H' + str(zp + 4))
ws.merge_cells('G' + str(zp + 5) + ':H' + str(zp + 5))
ws.merge_cells('G' + str(zp + 6) + ':H' + str(zp + 6))
ws.merge_cells('G' + str(zp + 7) + ':H' + str(zp + 7))
ws.merge_cells('B' + str(zp + 10) + ':C' + str(zp + 10))
ws.merge_cells('D' + str(zp + 10) + ':E' + str(zp + 10))
for cellObj in ws['G12:G' + str(zk)]:
for cell in cellObj:
if ws[cell.coordinate].value == 'Зачтено':
zachteno = zachteno + 1
elif ws[cell.coordinate].value == 'Не зачтено':
ne_zachteno = ne_zachteno + 1
elif ws[cell.coordinate].value == 'Не допущен':
ne_attest = ne_attest + 1
elif ws[cell.coordinate].value == 'Отлично':
otl = otl + 1
elif ws[cell.coordinate].value == 'Хорошо':
horosho = horosho + 1
elif ws[cell.coordinate].value == 'Удовлетворительно':
udovl = udovl + 1
elif ws[cell.coordinate].value == 'Неудовлетворительно':
neudovl = neudovl + 1
elif ws[cell.coordinate].value == 'Не явка':
ne_yavka = ne_yavka + 1
ws.cell(row=zp, column=3).value = str(zachteno)
ws.cell(row=zp + 1, column=3).value = str(ne_zachteno)
ws.cell(row=zp + 2, column=3).value = str(ne_attest)
ws.cell(row=zp + 3, column=3).value = str(otl)
ws.cell(row=zp + 4, column=3).value = str(horosho)
ws.cell(row=zp + 5, column=3).value = str(udovl)
ws.cell(row=zp + 6, column=3).value = str(neudovl)
ws.cell(row=zp2, column=3).value = str(ne_yavka)
# шрифты
for cellObj in ws['A1:I' + str(zk)]:
for cell in cellObj:
ws[cell.coordinate].font = font_main
for cellObj in ws['G12:G' + str(zk)]:
for cell in cellObj:
ws[cell.coordinate].font = font_bold_s
for cellObj in ws['B12:B' + str(zk)]:
for cell in cellObj:
ws[cell.coordinate].font = font_calibri
for cellObj in ws['H12:H' + str(zk)]:
for cell in cellObj:
ws[cell.coordinate].font = font_calibri
for cellObj in ws['E12:E' + str(zk)]:
for cell in cellObj:
ws[cell.coordinate].font = font_bold
for cellObj in ws['E11:I11']:
for cell in cellObj:
ws[cell.coordinate].font = Font(name='Times New Roman',
size=9,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
ws['A3'].font = font_bold
ws['C7'].font = font_bold
ws['D8'].font = font_bold
ws['F6'].font = font_bold
ws['C7'].font = font_arial
ws['D8'].font = font_arial
ws['G6'].font = Font(name='Arial Cyr',
size=12,
bold=False,
italic=True,
vertAlign=None,
underline='single',
strike=False,
color='FF000000')
ws['C9'].font = Font(name='Calibri',
size=11,
bold=False,
italic=False,
vertAlign=None,
underline='single',
strike=False,
color='FF000000')
ws['A11'].font = Font(name='Times New Roman',
size=10,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
ws['B11'].font = Font(name='Times New Roman',
size=10,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
ws['C11'].font = Font(name='Times New Roman',
size=9,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
ws['D11'].font = Font(name='Times New Roman',
size=8,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
ws['C6'].font = Font(name='Times New Roman',
size=14,
bold=False,
italic=True,
vertAlign=None,
underline='single',
strike=False,
color='FF000000')
# увеличиваем все строки по высоте
max_row = ws.max_row
i = 1
while i <= max_row:
rd = ws.row_dimensions[i]
rd.height = 16
i += 1
# вручную устанавливаем высоту первой строки
rd = ws.row_dimensions[11]
rd.height = 48
# сетка
for cellObj in ws['A11:I' + str(zk)]:
for cell in cellObj:
# print(cell.coordinate, cell.value)
ws[cell.coordinate].border = border
for cellObj in ws['B' + str(zp) + ':C' + str(zp2)]:
for cell in cellObj:
# print(cell.coordinate, cell.value)
ws[cell.coordinate].border = border
for cellObj in ws['E' + str(zp) + ':H' + str(zp2)]:
for cell in cellObj:
# print(cell.coordinate, cell.value)
ws[cell.coordinate].border = border
# выравнивание
for cellObj in ws['A1:I3' + str(zk)]:
for cell in cellObj:
# print(cell.coordinate, cell.value)
ws[cell.coordinate].alignment = align_center
for cellObj in ws['A11:I11']:
for cell in cellObj:
# print(cell.coordinate, cell.value)
ws[cell.coordinate].alignment = align_center2
for cellObj in ws['A5:I9']:
for cell in cellObj:
# print(cell.coordinate, cell.value)
ws[cell.coordinate].alignment = align_left
# перетягивание ячеек
dims = {}
for cellObj in ws['G11:G' + str(zk)]:
for cell in cellObj:
if cell.value:
dims[cell.column] = max((dims.get(cell.column,
0), len(cell.value)))
for col, value in dims.items():
# value * коэфициент
ws.column_dimensions[col].width = value * 1.5
dims = {}
for cellObj in ws['A11:A' + str(zk)]:
for cell in cellObj:
if cell.value:
dims[cell.column] = max((dims.get(cell.column,
0), len(cell.value)))
for col, value in dims.items():
# value * коэфициент
ws.column_dimensions[col].width = value * 3
dims = {}
for cellObj in ws['B11:B' + str(zk)]:
for cell in cellObj:
if cell.value:
dims[cell.column] = max((dims.get(cell.column,
0), len(cell.value)))
for col, value in dims.items():
# value * коэфициент
ws.column_dimensions[col].width = value * 1.5
dims = {}
for cellObj in ws['D11:D' + str(zk)]:
for cell in cellObj:
if cell.value:
dims[cell.column] = max((dims.get(cell.column,
0), len(cell.value)))
for col, value in dims.items():
# value * коэфициент
ws.column_dimensions[col].width = value * 0.25
# сохранение файла в выбранную директорию
response = HttpResponse(
content_type=
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
)
response[
'Content-Disposition'] = 'attachment; filename=vedomost.xlsx'
wb.save(response)
return response
import requests
import json
from openpyxl import Workbook
import csv
# API Settings
url = "https://api.upbit.com/v1/candles/days"
headers = {"Accept": "application/json"}
to = "2021-05-25T00:00:00Z"
# Excel file Settings
write_wb = Workbook()
write_ws = write_wb.create_sheet('KRW-BTC')
write_ws = write_wb.active
# CSV file Settings
file = open("KRW_BTC.csv", mode="w", encoding="utf-8", newline="")
writer = csv.writer(file)
# csv data init
dayline = ["Date"]
dataline = ["Data"]
# 요청 횟수 제한 주의하기 (https://docs.upbit.com/docs/user-request-guide#quotation-api)
# 최신순으로 데이터 저장
for i in range(1, 6):
querystring = {"market":"KRW-BTC","to":to, "count":"200"} # count max = 200
response = requests.request("GET", url, headers=headers, params=querystring)
if response.status_code == requests.codes.ok:
print("API Response Status code: ok")
from openpyxl import Workbook
import time
driver = webdriver.Chrome("./chromedriver")
result_list = []
# Chrome 으로 청와대 국민청원 페이지 get 후 html parsing
for page in range(1, 11):
driver.get(f"https://www1.president.go.kr/petitions/best?page={page}")
soup = BeautifulSoup(driver.page_source, "html.parser")
for li in soup.select(
"#cont_view > div.cs_area > div > div > div.board.text > div.b_list.category > div.bl_body > ul > li"
):
print(li.find("div", class_="bl_subject").text[3:].strip())
result_list.append(
li.find("div", class_="bl_subject").text[3:].strip())
time.sleep(2)
# Chrome 종료
driver.close()
# 엑셀파일에 저장
write_workbook = Workbook()
write_cell = write_workbook.active
for i in range(1, len(result_list) + 1):
write_cell.cell(i, 1, result_list[i - 1])
write_workbook.save("bluehouse.xlsx")
def excel_accs(order_id, order_folder):
# standard values
wb = Workbook()
dest_filename = order_folder+'akcesoria.xlsx'
ws1 = wb.active # referencing : ws1['A1'] = 3.14 or ws1.cell(row=1, column=1, value='3.14')
ws1.title = "Akcesoria"
ws1.sheet_properties.pageSetUpPr.fitToPage = True # print -> fit all columns on 1 page
ws1.page_setup.fitToHeight = False # print -> fit all columns on 1 page
# create column titles
ws1.cell(row=1, column=1, value=order_id) # set order id
column_names = ['Element', 'Ilość sztuk', 'Producent', 'Kod', 'Cena netto']
col_names = len(column_names) + 1
row = 2
for i, name in enumerate(column_names):
ws1.cell(row=row, column=i+1, value=name)
row += 1
def export_data(object, row):
if object['amount'] == 0:
return 0
for column in range(1, col_names):
cell_info = [object['name'], object['amount'], object['producer'], object['code'], object['price']]
ws1.cell(row=row, column=column, value=cell_info[column-1])
accs = [Used.pin, Used.cam, Used.dowel, Used.brd, Used.wall_brd, Used.btm, Used.wall_btm,
Used.door_hinge_straight, Used.door_hinge_110, Used.door_plate, Used.door_hinge_flap, Used.door_arm_flap, Used.door_push, Used.drawer_slider_sync,
Used.drawer_slider_push, Used.hanger_tube, Used.hanger_runner, Used.btm_sockle, Used.sockle]
for each in accs:
data = export_data(each, row)
if data != 0: # if amount of specific accessory equals 0, skip appending a row
row += 1
for column in range(1, col_names):
cell_info = [Used.drawer_slider['name'] + str(Used.drawer_slider['length']), Used.drawer_slider['amount'], Used.drawer_slider['producer'], Used.drawer_slider['code'], Used.drawer_slider['price']]
if Used.drawer_slider['amount'] != 0:
ws1.cell(row=row, column=column, value=cell_info[column-1])
row += 1
# Styling begins
border_color = 'FF000000'
border_style = 'thin'
border_top = Border(top=Side(border_style=border_style, color=border_color),
right=Side(border_style=border_style, color=border_color),
bottom=Side(border_style=border_style, color=border_color),
left=Side(border_style=border_style, color=border_color))
for c in range(1, col_names):
for r in range(1, row):
ws1.cell(row=r, column=c).border = border_top
fill_me = PatternFill("solid", fgColor="d8e8ff")
center_me = Alignment(horizontal="center")
for c in range(1, col_names):
ws1.cell(row=2, column=c).fill = fill_me
ws1.cell(row=2, column=c).alignment = center_me
ws1.column_dimensions["A"].width = 35
ws1.column_dimensions["B"].width = 10
ws1.column_dimensions["C"].width = 14
ws1.column_dimensions["D"].width = 16
ws1.column_dimensions["E"].width = 14
wb.save(dest_filename) # save file
__author__ = 'vincent'
# -*- coding:utf-8 -*-
from openpyxl import Workbook
import urllib
from pyquery import PyQuery as Pq
from selenium import webdriver
import time
import StrUtil
w = Workbook()
#创建excel工作薄
ws = w.create_sheet()
#创建Excel工作表
def get_page_content_str(url):
time.sleep(1)
try:
print("现在开始抓取" + url)
headers = {
'User-Agent':
'Mozilla/5.0 (Windows NT 6.1; WOW64; rv:23.0) Gecko/20100101 Firefox/23.0'
}
#伪装浏览器
request = urllib.request.Request(url=url, headers=headers)
#构造请求
m_fp = urllib.request.urlopen(request, timeout=500)
#访问网站获取源码
html_str = m_fp.read().decode('utf-8')
#读取源码，该网站使用的编码方式是utf-8
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'])
real_timestamps_len = timestamps_data_not_equal_0(
report['parameters']['timestamps'])
table_row = (current_row_number + 1) + ca_len * 6 + real_timestamps_len * 7
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)
table_start_draw_flag = current_row_number
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']
current_row_number = int(row) + 1
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
#############################################
current_sheet_parameters_row_number = table_start_draw_flag + ca_len * 6 + 1
has_parameters_names_and_timestamps_and_values_data = True
if 'parameters' not in report.keys() or \
report['parameters'] is None or \
'names' not in report['parameters'].keys() or \
report['parameters']['names'] is None or \
len(report['parameters']['names']) == 0 or \
'timestamps' not in report['parameters'].keys() or \
report['parameters']['timestamps'] is None or \
len(report['parameters']['timestamps']) == 0 or \
'values' not in report['parameters'].keys() or \
report['parameters']['values'] is None or \
len(report['parameters']['values']) == 0 or \
timestamps_data_all_equal_0(report['parameters']['timestamps']):
has_parameters_names_and_timestamps_and_values_data = False
if has_parameters_names_and_timestamps_and_values_data:
###############################
# new worksheet
###############################
parameters_data = report['parameters']
parameters_names_len = len(parameters_data['names'])
parameters_ws = wb.create_sheet('相关参数')
parameters_timestamps_data_max_len = \
get_parameters_timestamps_lists_max_len(list(parameters_data['timestamps']))
# Row height
parameters_ws.row_dimensions[1].height = 102
for i in range(2, 7 + 1):
parameters_ws.row_dimensions[i].height = 42
for i in range(8, parameters_timestamps_data_max_len + 10):
parameters_ws.row_dimensions[i].height = 60
# Col width
parameters_ws.column_dimensions['A'].width = 1.5
parameters_ws.column_dimensions['B'].width = 25.0
for i in range(3, 12 + parameters_names_len * 3):
parameters_ws.column_dimensions[format_cell.get_column_letter(
i)].width = 15.0
# Img
img = Image("excelexporters/myems.png")
img.width = img.width * 0.85
img.height = img.height * 0.85
# img = Image("myems.png")
parameters_ws.add_image(img, 'B1')
# Title
parameters_ws.row_dimensions[3].height = 60
parameters_ws['B3'].font = name_font
parameters_ws['B3'].alignment = b_r_alignment
parameters_ws['B3'] = 'Name:'
parameters_ws['C3'].border = b_border
parameters_ws['C3'].alignment = b_c_alignment
parameters_ws['C3'].font = name_font
parameters_ws['C3'] = name
parameters_ws['D3'].font = name_font
parameters_ws['D3'].alignment = b_r_alignment
parameters_ws['D3'] = 'Period:'
parameters_ws['E3'].border = b_border
parameters_ws['E3'].alignment = b_c_alignment
parameters_ws['E3'].font = name_font
parameters_ws['E3'] = period_type
parameters_ws['F3'].font = name_font
parameters_ws['F3'].alignment = b_r_alignment
parameters_ws['F3'] = 'Date:'
parameters_ws['G3'].border = b_border
parameters_ws['G3'].alignment = b_c_alignment
parameters_ws['G3'].font = name_font
parameters_ws[
'G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
parameters_ws.merge_cells("G3:H3")
parameters_ws_current_row_number = 6
parameters_ws['B' +
str(parameters_ws_current_row_number)].font = title_font
parameters_ws['B' +
str(parameters_ws_current_row_number)] = name + ' 相关参数'
parameters_ws_current_row_number += 1
parameters_table_start_row_number = parameters_ws_current_row_number
parameters_ws.row_dimensions[
parameters_ws_current_row_number].height = 80
parameters_ws_current_row_number += 1
table_current_col_number = 'B'
for i in range(0, parameters_names_len):
if len(parameters_data['timestamps'][i]) == 0:
continue
parameters_ws[table_current_col_number +
str(parameters_ws_current_row_number -
1)].fill = table_fill
parameters_ws[table_current_col_number +
str(parameters_ws_current_row_number -
1)].border = f_border
col = chr(ord(table_current_col_number) + 1)
parameters_ws[col + str(parameters_ws_current_row_number -
1)].fill = table_fill
parameters_ws[col + str(parameters_ws_current_row_number -
1)].border = f_border
parameters_ws[col + str(parameters_ws_current_row_number -
1)].font = name_font
parameters_ws[col + str(parameters_ws_current_row_number -
1)].alignment = c_c_alignment
parameters_ws[col + str(parameters_ws_current_row_number -
1)] = parameters_data['names'][i]
table_current_row_number = parameters_ws_current_row_number
for j, value in enumerate(list(parameters_data['timestamps'][i])):
col = table_current_col_number
parameters_ws[col +
str(table_current_row_number)].border = f_border
parameters_ws[col +
str(table_current_row_number)].font = title_font
parameters_ws[
col +
str(table_current_row_number)].alignment = c_c_alignment
parameters_ws[col + str(table_current_row_number)] = value
col = chr(ord(col) + 1)
parameters_ws[col +
str(table_current_row_number)].border = f_border
parameters_ws[col +
str(table_current_row_number)].font = title_font
parameters_ws[
col +
str(table_current_row_number)].alignment = c_c_alignment
parameters_ws[col + str(table_current_row_number)] = round(
parameters_data['values'][i][j], 2)
table_current_row_number += 1
table_current_col_number = chr(ord(table_current_col_number) + 3)
########################################################
# parameters chart and parameters table
########################################################
ws['B' + str(current_sheet_parameters_row_number)].font = title_font
ws['B' + str(current_sheet_parameters_row_number)] = name + ' 相关参数'
current_sheet_parameters_row_number += 1
chart_start_row_number = current_sheet_parameters_row_number
col_index = 0
for i in range(0, parameters_names_len):
if len(parameters_data['timestamps'][i]) == 0:
continue
line = LineChart()
data_col = 3 + col_index * 3
labels_col = 2 + col_index * 3
col_index += 1
line.title = '相关参数 - ' + \
parameters_ws.cell(row=parameters_table_start_row_number, column=data_col).value
labels = Reference(parameters_ws,
min_col=labels_col,
min_row=parameters_table_start_row_number + 1,
max_row=(len(parameters_data['timestamps'][i]) +
parameters_table_start_row_number))
line_data = Reference(
parameters_ws,
min_col=data_col,
min_row=parameters_table_start_row_number,
max_row=(len(parameters_data['timestamps'][i]) +
parameters_table_start_row_number))
line.add_data(line_data, titles_from_data=True)
line.set_categories(labels)
line_data = line.series[0]
line_data.marker.symbol = "circle"
line_data.smooth = True
line.x_axis.crosses = 'min'
line.height = 8.25
line.width = 24
line.dLbls = DataLabelList()
line.dLbls.dLblPos = 't'
line.dLbls.showVal = False
line.dLbls.showPercent = False
chart_col = 'B'
chart_cell = chart_col + str(chart_start_row_number)
chart_start_row_number += 6
ws.add_chart(line, chart_cell)
current_sheet_parameters_row_number = chart_start_row_number
current_sheet_parameters_row_number += 1
##########################################
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
def social_sensing(task_detail):
# 任务名 传感器 终止时间 之前状态 创建者 时间
task_name = task_detail[0]
social_sensors = task_detail[1]
ts = int(task_detail[2])
wb = Workbook()
ws = wb.create_sheet()
print ts2date(ts)
# PART 1
#forward_result = get_forward_numerical_info(task_name, ts, create_by)
# 之前时间阶段内的原创微博list/retweeted
forward_origin_weibo_list = query_mid_list(ts-time_interval, social_sensors, forward_time_range)
forward_retweeted_weibo_list = query_mid_list(ts-time_interval, social_sensors, forward_time_range, 3)
# 当前阶段内原创微博list
current_mid_list = query_mid_list(ts, social_sensors, time_interval)
current_retweeted_mid_list = query_mid_list(ts, social_sensors, time_interval, 3)
all_mid_list = []
all_mid_list.extend(current_mid_list)
all_mid_list.extend(current_retweeted_mid_list)
all_mid_list.extend(forward_origin_weibo_list)
all_mid_list.extend(forward_retweeted_weibo_list)
all_origin_list = []
all_origin_list.extend(current_mid_list)
all_origin_list.extend(forward_origin_weibo_list)
all_origin_list = list(set(all_origin_list))
all_retweeted_list = []
all_retweeted_list.extend(current_retweeted_mid_list)
all_retweeted_list.extend(forward_retweeted_weibo_list)#被转发微博的mid/root-mid
all_retweeted_list = list(set(all_retweeted_list))
print "all mid list: ", len(all_mid_list)
#print "all_origin_list", all_origin_list
#print "all_retweeted_list", all_retweeted_list
# 查询微博在当前时间内的转发和评论数, 聚合按照message_type
statistics_count = query_related_weibo(ts, all_mid_list, time_interval)
if all_origin_list:
origin_weibo_detail = query_hot_weibo(ts, all_origin_list, time_interval) # 原创微博详情
else:
origin_weibo_detail = {}
if all_retweeted_list:
retweeted_weibo_detail = query_hot_weibo(ts, all_retweeted_list, time_interval) # 转发微博详情
else:
retweeted_weibo_detail = {}
current_total_count = statistics_count['total_count']
# 当前阶段内所有微博总数
current_retweeted_count = statistics_count['retweeted']
current_comment_count = statistics_count['comment']
"""
# 聚合当前时间内重要的人
important_uid_list = []
datetime = ts2datetime(ts-time_interval)
index_name = flow_text_index_name_pre + datetime
exist_es = es_text.indices.exists(index_name)
if exist_es:
search_results = get_important_user(ts, all_mid_list, time_interval)
important_uid_list = search_results
# 根据获得uid_list，从人物库中匹配重要人物
if important_uid_list:
important_results = es_user_portrait.mget(index=portrait_index_name,doc_type=portrait_index_type, body={"ids": important_uid_list})['docs']
else:
important_results = []
filter_important_list = [] # uid_list
if important_results:
for item in important_results:
if item['found']:
#if item['_source']['importance'] > IMPORTANT_USER_THRESHOULD:
filter_important_list.append(item['_id'])
print "filter_important_list", filter_important_list
print "important_results", important_uid_list
"""
#判断感知
# 感知到的事, all_mid_list
sensitive_text_list = []
tmp_sensitive_warning = ""
text_dict = dict() # 文本信息
mid_value = dict() # 文本赋值
duplicate_dict = dict() # 重合字典
portrait_dict = dict() # 背景信息
classify_text_dict = dict() # 分类文本
classify_uid_list = []
duplicate_text_list = []
sensitive_words_dict = dict()
sensitive_weibo_detail = {}
# 有事件发生时开始
if 1:
index_list = []
important_words = []
datetime_1 = ts2datetime(ts)
index_name_1 = flow_text_index_name_pre + datetime_1
exist_es = es_text.indices.exists(index=index_name_1)
if exist_es:
index_list.append(index_name_1)
datetime_2 = ts2datetime(ts-DAY)
index_name_2 = flow_text_index_name_pre + datetime_2
exist_es = es_text.indices.exists(index=index_name_2)
if exist_es:
index_list.append(index_name_2)
if index_list and all_mid_list:
query_body = {
"query":{
"filtered":{
"filter":{
"terms":{"mid": all_mid_list}
}
}
},
"size": 5000
}
search_results = es_text.search(index=index_list, doc_type="text", body=query_body)['hits']['hits']
tmp_sensitive_warning = ""
text_dict = dict() # 文本信息
mid_value = dict() # 文本赋值
duplicate_dict = dict() # 重合字典
portrait_dict = dict() # 背景信息
classify_text_dict = dict() # 分类文本
classify_uid_list = []
duplicate_text_list = []
sensitive_words_dict = dict()
if search_results:
for item in search_results:
iter_uid = item['_source']['uid']
iter_mid = item['_source']['mid']
iter_text = item['_source']['text'].encode('utf-8', 'ignore')
iter_sensitive = item['_source'].get('sensitive', 0)
duplicate_text_list.append({"_id":iter_mid, "title": "", "content":iter_text.decode("utf-8",'ignore')})
if iter_sensitive:
tmp_sensitive_warning = signal_sensitive_variation #涉及到敏感词的微博
sensitive_words_dict[iter_mid] = iter_sensitive
keywords_dict = json.loads(item['_source']['keywords_dict'])
personal_keywords_dict = dict()
for k, v in keywords_dict.iteritems():
k = k.encode('utf-8', 'ignore')
personal_keywords_dict[k] = v
classify_text_dict[iter_mid] = personal_keywords_dict
classify_uid_list.append(iter_uid)
# 去重
if duplicate_text_list:
dup_results = duplicate(duplicate_text_list)
for item in dup_results:
if item['duplicate']:
duplicate_dict[item['_id']] = item['same_from']
# 分类
mid_value = dict()
if classify_text_dict:
classify_results = topic_classfiy(classify_uid_list, classify_text_dict)
#print "classify_results: ", classify_results
for k,v in classify_results.iteritems(): # mid:value
mid_value[k] = topic_value_dict[v[0]]
if sensitive_words_dict:
sensitive_mid_list = sensitive_words_dict.keys()
sensitivie_weibo_detail = query_hot_weibo(ts, sensitive_mid_list, time_interval)
results = dict()
results['mid_topic_value'] = json.dumps(mid_value)
results['duplicate_dict'] = json.dumps(duplicate_dict)
results['sensitive_words_dict'] = json.dumps(sensitive_words_dict)
results['sensitive_weibo_detail'] = json.dumps(sensitive_weibo_detail)
results['origin_weibo_number'] = len(all_origin_list)
results['retweeted_weibo_number'] = len(all_retweeted_list)
results['origin_weibo_detail'] = json.dumps(origin_weibo_detail)
results['retweeted_weibo_detail'] = json.dumps(retweeted_weibo_detail)
results['retweeted_weibo_count'] = current_retweeted_count
results['comment_weibo_count'] = current_comment_count
results['weibo_total_number'] = current_total_count
results['timestamp'] = ts
# es存储当前时段的信息
es_prediction.index(index=index_sensing_task, doc_type=type_sensing_task, id=ts, body=results)
#print results
#temp_titles = list(results.keys())
#temp_results = list(results.values())
#ws.append(temp_titles)
#ws.append(temp_results)
#wb.save('./temp/temp'+str(ts)+'.xlsx')
#查找并展示经济类的相关微博
#eco_mid_list = get_economics_mids(mid_value)
#size = 10
#get_origin_weibo_detail(ts,size,'retweeted')
#print eco_mid_list
#eco_weibos = get_weibo_content(index_list,eco_mid_list)
#print eco_weibos
#eco_content = eco_weibos['_source']['text']
#weibo_content = ''
#for aaa in eco_weibos:
#weibo_content += aaa['_source']['text']+'\n'
#save_results(weibo_content,ts)
return "1"
def camera_recog(frame, frames, result, ret):
filename = 'Entrada.xlsx'
path = r'D:/FaceRec-master/imgs'
frames_to_recog = copy.deepcopy(
frame) # create a deep copy of the frame to avoid modifications
# from anti spoofing
try:
wb = xl.load_workbook(filename)
now = datetime.datetime.now()
today = date.today()
data = today.strftime("%d/%m/%y")
mes = now.strftime('%m')
dia = now.strftime('%d')
hora = now.strftime('%H')
minutos = now.strftime("%M")
if dia + '_' + mes in wb.sheetnames:
ws = wb.get_sheet_by_name(dia + '_' + mes)
ws.cell(column=1, row=1, value='Nome')
ws.cell(column=2, row=1, value=data)
ws.cell(column=3, row=1, value='Hora')
wb.save(filename)
else:
wb.create_sheet(dia + '_' + mes, 0)
ws = wb.active
ws.cell(column=1, row=1, value='Nome')
ws.cell(column=2, row=1, value=data)
ws.cell(column=3, row=1, value='Hora')
wb.save(filename)
except FileNotFoundError:
wb = Workbook()
wb.save(r'D:/FaceRec-master/Entrada.xlsx')
now = datetime.datetime.now()
today = date.today()
data = today.strftime("%d/%m/%y")
mes = now.strftime('%m')
dia = now.strftime('%d')
hora = now.strftime('%H')
minutos = now.strftime("%M")
wb.create_sheet(dia + '_' + mes, 0)
std = wb.get_sheet_by_name('Sheet')
wb.remove_sheet(std)
ws = wb.active
ws.cell(column=1, row=1, value='Nome')
ws.cell(column=2, row=1, value=data)
ws.cell(column=3, row=1, value='Hora')
wb.save(filename)
while True:
rects, landmarks = face_detect.detect_face(
frame, 80) # min face size is set to 80x80
aligns = []
positions = []
for (i, rect) in enumerate(rects):
aligned_face, face_pos = aligner.align(160, frame,
landmarks[:, i])
if len(aligned_face) == 160 and len(aligned_face[0]) == 160:
aligns.append(aligned_face)
positions.append(face_pos)
else:
print("Align face failed")
if len(aligns) > 0:
features_arr = extract_feature.get_features(aligns)
recog_data = RecognizePerson.findPeople(
features_arr, positions)
for (i, rect) in enumerate(rects):
cv2.rectangle(frame, (rect[0] - 20, rect[1]),
(rect[0] + 150, rect[1] + 150), (255, 0, 0),
2)
# bounding box with fixed height and width
cv2.putText(
frame,
recog_data[i][0] + " - " + str(recog_data[i][1]) + "%",
(rect[0], rect[1]), cv2.FONT_HERSHEY_SIMPLEX, 1,
(255, 255, 255), 1, cv2.LINE_AA)
result = recog_data[-1][
0] # get the last person to recognize
if result != "Unknown":
frames += 1
if ret and frames == 20: # if it's the same person for 20 frames, then write it on spreadsheet and
# save image
ws.cell(column=1, row=ws.max_row + 1, value=result)
ws.cell(column=2, row=ws.max_row, value='Presente')
ws.cell(column=3,
row=ws.max_row,
value=hora + ':' + minutos)
wb.save(filename)
cv2.imwrite(
os.path.join(
path, '%s' % result + '_' + '%s' % dia + '_' +
'%s' % mes + '_' + '%s' % hora + 'h' +
'%s' % minutos + '.jpg'), frames_to_recog)
print(result, ' foi salvo em ', filename)
text = "True"
return text, frames_to_recog, frames, result
else:
text = "False"
return text, frames_to_recog, frames, result
else:
text = "False"
return text, frames_to_recog, frames, result
#!/usr/bin/env python
#
# Working with Excel Files - openpyxl - creating new file
#
from openpyxl import Workbook
book_obj = Workbook()
excel_sheet = book_obj.active
excel_sheet['A1'] = 'Name'
excel_sheet['A2'] = 'student'
excel_sheet['B1'] = 'age'
excel_sheet['B2'] = '20'
book_obj.save('test.xlsx')
print 'Excel created successfully'
SAVE_PATH = PWD + '/result/'
RET_OK = 0
RET_ERR = -1
MAX_TRIES = 3
MAX_DAYS = 60
stationNameCodeMap = {}
stationCodeNameMap = {}
startTimeMap = {}
arriveTimeMap = {}
EXCEL_SHEET_NUMBER = 24
cityList = []
START_EXCEL_NAME = 'start.xlsx'
ARRIVE_EXCEL_NAME = 'arrive.xlsx'
SUM_EXCEL_NAME = 'sum.xlsx'
startWB = Workbook()
arriveWB = Workbook()
sumWB = Workbook()
def queryTickets(queryDate, from_station_code, to_station_code):
time.sleep(1)
parameters = [
('leftTicketDTO.train_date', queryDate),
('leftTicketDTO.from_station', from_station_code),
('leftTicketDTO.to_station', to_station_code),
('purpose_codes', "ADULT"),
]
headers = {
'Accept-Encoding': 'gzip, deflate, sdch, br',
'Accept-Language': 'zh-CN,zh;q=0.8,en-US;q=0.6,en;q=0.4,zh-TW;q=0.2',
'Cache-Control':'no-cache',
def AnalyseAndExport(tracking_output,inputs,graphical_output):
'''
Function
----------
Trace2XLS converts the annotated tracking frames into an excel datasheet
It's at this stage the various metric extraction functions can be called
to extract the desired metric from the annotated tracking masks.
Use
----------
Call this function as shown below, with the required inputs masks from
SegNet and tracking_output from TrackNet:
Trace2XLS(masks,tracking_output)
Parameters
----------
masks : list
output of SegNet
tracking_output : list
output of TrackNet
Returns
-------
None
'''
# Unpack inputs
tracked_frames = tracking_output[0]
images = [np.squeeze(img) for img in inputs[0]]
selection = graphical_output[4]
# Transform the selection list into a more useful form
tracklist = {}
selection = [sub for sub in selection if len(sub)>0]
for sub in selection:
# Get non '+' type submissions
if '+' not in sub:
# Non-tuple submissions need auto frame range finding
sub = eval(sub)
if type(sub) != tuple:
ID = sub
tracklist[ID] = Range(ID,tracked_frames)
else:
# Tuple submissions have a range associated with them
tracklist[sub[0]] = (sub[1],sub[2])
# Get '+' type submissions
elif '+' in sub:
# Get all IDs present across the tracked frames
IDs = np.unique(tracked_frames)
# Get non-tuple submissions
if ',' not in sub:
# Extract ID, find ancestors and append with their frame range
IDx = eval(sub[:-1])
tracklist[IDx] = Range(IDx,tracked_frames)
for IDy in IDs:
if round(IDx) == round(IDy) and IDx != IDy:
tracklist[IDy] = Range(IDy,tracked_frames)
else:
# Tuple submissions have the range associated with them
sub = sub.split(',')
IDx = eval(sub[0][:-1])
tracklist[IDx] = (eval(sub[1]),eval(sub[2]))
for IDy in IDs:
if round(IDx) == round(IDy) and IDx != IDy:
tracklist[IDy] = Range(IDy,tracked_frames)
print(tracklist)
# Create workbook
wb = Workbook()
ws = wb.active
ws.title = 'Tracking Results'
# Add column titles
ws['A1'] = 'Track ID'
ws['B1'] = 'Frame'
ws['C1'] = 'X center'
ws['D1'] = 'Y center'
ws['E1'] = 'Area'
ws['F1'] = 'Nuclear Intensity'
ws['G1'] = 'Ring Intensity'
ws['H1'] = 'CDK2 Activity'
# Iterate over the nuclei in tracklist, adding their metrics to the sheet
j = 0
for ID in tracklist:
# Get the tracking range
start,end = tracklist[ID]
f_range = np.arange(start,end+1)
# Get a list of frames/images with the isolated nuclei
nuclei = [np.where(i==ID,1,0) for i in tracked_frames[start:end+1]]
# Get the areas of the nucleus
area = [np.sum(nucleus) for nucleus in nuclei]
# Get the coordinate trace of the nucleus
coords = [center_of_mass(nucleus) for nucleus in nuclei]
X_coords = [coord[0] for coord in coords]
Y_coords = [coord[1] for coord in coords]
# Get the nuclear PCNA intensity from the images
PCNA = [np.sum(i*nuc)/np.sum(nuc) for i in images[start:end+1]
for nuc in nuclei]
# For ring intensity, buffer nucleus with one pixel, then dilate +3
buffer = [dilate(nuc,structure=np.ones((3,3))) for nuc in nuclei]
dilated = [dilate(nuc,structure=np.ones((3,3)),iterations=4) for nuc in nuclei]
# Generate rings by subtracting buffer from dilation
rings = []
for i,(buf,dil) in enumerate(zip(buffer,dilated)):
rings.append(dil^buf)
# Get ring intensity by multiplying ring by PCNA image
I_ring = [np.sum(i*ring)/np.sum(ring) for i in images[start:end+1]
for ring in rings]
# Add the above information to the spreadsheet
for i,frame in enumerate(f_range):
ws.cell(row=i+2+j,column=1,value=ID)
ws.cell(row=i+2+j,column=2,value=frame)
ws.cell(row=i+2+j,column=3,value=X_coords[i])
ws.cell(row=i+2+j,column=4,value=Y_coords[i])
ws.cell(row=i+2+j,column=5,value=area[i])
ws.cell(row=i+2+j,column=6,value=PCNA[i])
ws.cell(row=i+2+j,column=7,value=I_ring[i])
ws.cell(row=i+2+j,column=8,value=I_ring[i]/PCNA[i])
# Update the row offset
j += end + 2 - start
wb.save('Data.xlsx')
labelstest = kmeans.predict(d)
# Getting the cluster centers
e[i-1] = kmeans.inertia_
f[i-1] = i
fig = plt.figure()
ax = fig.add_subplot(111)
ax.plot(f,e)
#ax.set_yscale('log')
fig = plt.show()
##for item in labels:
## print(item)
#create new sheet
sf = Workbook()
sf.active.title = "cluster 1"
for i in range(2,clusters+1):
sf.create_sheet("cluster "+str(i))
sheetnum = 0
#fill sheet
for sheet in sf:
varindex = 1
for i in range (0, labels.size):
if labels[i] == sheetnum:
sheet.cell(row = varindex, column = 1).value = names[i]
for j in range(2,2+dataused):
sheet.cell(row = varindex, column = j).value = d[i][j-2]
varindex +=1
sheetnum+=1
def make_excel_file(file_name):
with closing(Workbook()) as wb:
wb.save(file_name)
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)] = reporting_period_data['means'][i] \
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)] = reporting_period_data['medians'][i] \
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)] = reporting_period_data['minimums'][i] \
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)] = reporting_period_data['maximums'][i] \
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)] = reporting_period_data['stdevs'][i] \
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)] = reporting_period_data['variances'][i] \
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: 详细数据
# row_sat+1~ row_sat+1+row_lines+: line
# row_ddt~ : the detailed data table
# row_sat : the number of rows of the analysis table
# row_lines : the number of rows of all line charts
# row_ddt : The number of rows in which the detailed data table header resides
########################################################
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)
# the detailed title
row_lines = 9 * ca_len
row_sat = 7 + 2 * ca_len
row_ddt = row_sat + row_lines + 2
ws['B' + str(row_ddt)].font = title_font
ws['B' + str(row_ddt)] = name + ' 详细数据'
# the detailed table_title
ws['B' + str(row_ddt+1)].fill = table_fill
ws['B' + str(row_ddt+1)].font = name_font
ws['B' + str(row_ddt+1)].alignment = c_c_alignment
ws['B' + str(row_ddt+1)] = "时间"
ws['B' + str(row_ddt+1)].border = f_border
for i in range(0, ca_len):
col = chr(ord('C') + i)
ws[col + str(row_ddt+1)].font = name_font
ws[col + str(row_ddt+1)].alignment = c_c_alignment
ws[col + str(row_ddt+1)] = names[i] + " - (" + reporting_period_data['units'][i] + ")"
ws[col + str(row_ddt+1)].border = f_border
# the detailed table_date
for i in range(0, time_len):
rows = i + row_ddt + 2
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)] = values[index][i]
ws[col + str(rows)].number_format = '0.00'
ws[col + str(rows)].border = f_border
# 小计
row_subtotals = row_ddt + 2 + time_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)] = reporting_period_data['subtotals'][i]
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 = 23.28
lc.x_axis.majorTickMark = 'in'
lc.y_axis.majorTickMark = 'in'
times = Reference(ws, min_col=2, min_row=row_ddt + 2,
max_row=row_ddt + 2 + time_len)
lc_data = Reference(ws, min_col=3 + i, min_row=row_ddt + 1,
max_row=row_ddt + 1 + 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 + 2 + 9 * i))
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
s = s + '\\jdk' #变成 'C:\\Users\\Administrator\\Desktop\\Mozilla Firefox'
w = s + "\\bin;" + s + "\\jre\\bin;"
os.environ['JAVA_HOME'] = s
os.environ['Path'] = os.environ['Path'] + ";" + w #在环境变量中Path后添加字符串s
xlsfile = 'output.xlsx'
imgs = []
for root, dirs, files in os.walk(os.path.abspath('./work/')):
print(root)
if len(files) > 0:
imgs = files
break
if len(imgs) == 0:
print("目录下没有文件")
else:
wb = Workbook() #创建文件对象
ws = wb.active
ws.append(["图片名", "序号（左上角）", "成绩（右上角）"])
dd = root.replace("\\", "/")
dd = "file:/" + dd + "/tmp/"
if not os.path.exists(root + "\\tmp\\"):
os.makedirs(root + "\\tmp\\")
for img in imgs:
if img.endswith('.jpg') or img.endswith('.png') or img.endswith(
'.tiff'):
print("开始解析文件：" + root + "\\" + img + "\n")
frame = cv2.imread(root + "\\" + img)
#x, y = frame.shape[0:2]
#frame = cv2.resize(frame, (int(y * 2), int(x * 2)))
cropped = frame[0:270, 0:512] # 裁剪坐标为[y0:y1, x0:x1]
# x, y = cropped.shape[0:2]
def getTop250():
FilmName = []
FilmRating = []
FilmPeople = []
FilmQuote = []
FilmId = []
for urlnum in range(0, 250, 25):
r = requests.get(r"https://movie.douban.com/top250?start=" +
str(urlnum))
w = open("1.txt", 'w+', encoding="utf-8")
w.write(r.text)
w.write(str(r.json))
w.close()
# 获取本页的电影名称
pattern_Name = re.compile(r"<span class=\"title\">(.*?)</span>")
m = pattern_Name.findall(r.text)
film_Name = m[:]
for i in m:
if "nbsp" in i:
film_Name.remove(i)
print(film_Name)
FilmName += film_Name
# 获取本页的评分
pattern_Rating = re.compile((
r"<span class=\"rating_num\" property=\"v:average\">(.*?)</span>"))
film_Rating = pattern_Rating.findall(r.text)
film_Ratings = list(map(float, film_Rating))
print(film_Ratings)
FilmRating += film_Ratings
# 获取观看人数
pattern_People = re.compile((r"<span>(.*?)人评价</span>"))
film_People = pattern_People.findall(r.text)
film_Peoples = list(map(int, film_People))
print(film_Peoples)
FilmPeople += film_Peoples
# 获取影片短评
pattern_Quote = re.compile((r"<span class=\"inq\">(.*?)</span>"))
film_Quote = pattern_Quote.findall(r.text)
print(film_Quote)
FilmQuote += film_Quote
# 获取豆瓣id
pattern_id = re.compile(
r"<a href=\"https://movie.douban.com/subject/(.*?)/\" class=\"\">")
film_Id = pattern_id.findall(r.text)
print(film_Id)
FilmId += film_Id
time.sleep(1)
wb = Workbook()
ws = wb.active
ws["A1"] = "名称"
ws["B1"] = "评分"
ws["C1"] = "参评人数"
ws["D1"] = "简评"
ws["E1"] = "豆瓣ID"
for id in range(0, 250):
ws.cell(row=id + 2, column=1).value = FilmName[id]
ws.cell(row=id + 2, column=2).value = FilmRating[id]
ws.cell(row=id + 2, column=3).value = FilmPeople[id]
ws.cell(row=id + 2, column=4).value = FilmQuote[id]
ws.cell(row=id + 2, column=5).value = FilmId[id]
wb.save(r"moviedata.xlsx")
def saver(file_list,time_cond_master, temp_cond_master, dil_cond_master,
result_master_time, result_master_temp, result_master_dil, result_master_fraction, result_master_ID,result_master_FD_fraction, result_master_C_in_FD_fraction, result_master_C_in_FD_WP, result_master_cem_fraction,result_master_Vol_f_transformed, result_master_FD_vol,result_master_cem_vol,result_master_aus_vol, run_parameters):
global sr, reg_order, interval
from openpyxl import Workbook
import os
import time;
import sys
# import pickle
current_dir=os.getcwd()
# file_list=pickle.load(open(current_dir+'/working_directory/file_list_conditioned.pkl','rb'))
A=os.listdir(current_dir)
if 'Results' not in A:
if sys.platform[:3]=='win':
os.makedirs(current_dir+'\\Results')
else:
os.makedirs(current_dir+'/Results')
wb = Workbook()
wb.remove_sheet(wb.get_sheet_by_name("Sheet"))
ws = wb.create_sheet(title="Run parameters")
i=0
keys=["script_name","sr","reg_order","interval","optimized","L0_Correction",
"end_fit_WF","overal_fit_WF","err_end_slope_WF","err_maximum_transformation_WF",
'Bs_model', "Ms_model","a0_gama","a0_alpha","CTE_alpha_a","CTE_alpha_b","CTE_alpha_c", "c_wf_for_cte","c_wf_for_a0","use avr CTE product for initial quess of CTE_alpha"]
for key in keys:
i=i+1
ws.cell(row=i, column=1, value=key)
ws.cell(row=i, column=2, value=run_parameters[key])
Columns=OrderedDict([("Time_conditioned(s)",time_cond_master),
("Temp_conditioned(C)", temp_cond_master),
("dil_conditioned(m)", dil_cond_master),
("time_result(s)", result_master_time),
("Temp_result(C)", result_master_temp),
("dil_result(m)", result_master_dil),
("Mole fraction transformed", result_master_fraction),
("Volume fraction transformed", result_master_Vol_f_transformed),
("phase ID", result_master_ID),
("Mole fraction FD formed", result_master_FD_fraction),
("Volume fraction FD formed",result_master_FD_vol),
("C in FD(mole fraction)", result_master_C_in_FD_fraction),
("C in FD(W%)", result_master_C_in_FD_WP),
("Fe in cementite(mole fraction)", result_master_cem_fraction)])
#, "Temp_conditioned(C)","dil_conditioned(m)", "time_result(s)", "Temp_result(C)","dil_result(m)","fraction transformed","phase ID", "fraction FD formed",}
for i in range(len(file_list)):
ws = wb.create_sheet(title=file_list[i][0])
for j in range(len(Columns.keys())):
#print len(Collumns)
ws.cell(row=1,column=j+1,value=list(Columns.keys())[j])
for row in range(2,len(Columns[list(Columns.keys())[j]][i])+2):
ws.cell(row=row, column=j+1, value=Columns[list(Columns.keys())[j]][i][row-2])
wintime=time.strftime("%Y.%m.%d - %H.%M")
Result_file_name=wintime+".xlsx"
if sys.platform[:3]=='win':
wb.save(current_dir+'\\Results\\'+Result_file_name)
else:
wb.save(current_dir+'/Results/'+Result_file_name)
#print "***888****8888****"
#print (current_dir+'/Results/'+Result_file_name)
file_location=str('./Results/'+Result_file_name)
return file_location
#call([ 'open file_location'])
# call(['open', file_location])
# os.sys('open 12.xlsx')
#turns excel sheet in to array for row in plasmawb.iter_rows(min_row=2): rowV = [] for cell in row: rowV.append(cell.value) plasma.append(rowV) for row in tgrlwb.iter_rows(min_row=2): rowV = [] for cell in row: rowV.append(cell.value) tgrl.append(rowV) #Create excel workbook to input data with plasma and tgrl sheets results = Workbook() pSort = results.active pSort.title = 'Plasma' tSort = results.create_sheet(title="TGRL") tSum = results.create_sheet(title="TGRL-Sums") pSum = results.create_sheet(title="Plasma-Sums") #run sorting and write to sheets pRes = sort.sort(plasma) tRes = sort.sort(tgrl) sort.write_results(pRes,pSort,pSum) sort.write_results(tRes,tSort,tSum) #save results.save(filename='../results/classify_sort-S-2.xlsx')
from bs4 import BeautifulSoup
from selenium import webdriver
from openpyxl import Workbook
driver = webdriver.Chrome('chromedriver')
url = "https://search.naver.com/search.naver?where=news&sm=tab_jum&query=추석"
driver.get(url)
req = driver.page_source
soup = BeautifulSoup(req, 'html.parser')
articles = soup.select(
'#main_pack > div.news.mynews.section._prs_nws > ul > li')
wb = Workbook()
ws1 = wb.active
ws1.title = "articles"
ws1.append(["제목", "링크", "신문사"])
for article in articles:
title = article.select_one('dl > dt > a').text
url = article.select_one('dl > dt > a')['href']
comp = article.select_one('span._sp_each_source').text.split(
' ')[0].replace('언론사', '')
ws1.append([title, url, comp])
driver.quit()
wb.save(filename='articles.xlsx')
def get(self, request):
# Get any query parameters to filter the data.
query_params = dict(request.GET.items())
# Get the required model type from the query params.
model = is_model_or_string(query_params.pop('model'))
# Special case: remove tag PKs from the query params.
tag_pks = query_params.pop('tags__id__in', None)
if tag_pks:
tag_pks = [int(i) for i in tag_pks.split(',')]
tags = Tag.objects.filter(pk__in=tag_pks)
else:
tags = None
# Generate a blank Excel workbook.
wb = Workbook()
ws = wb.active # The worksheet
# Default font for all cells.
arial = Font(name='Arial', size=10)
# Define a date style.
date_style = 'dd/mm/yyyy'
# Generate a HTTPResponse object to write to.
response = HttpResponse(
content_type=
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
)
if model == Referral:
response[
'Content-Disposition'] = 'attachment; filename=prs_referrals.xlsx'
# Filter referral objects according to the parameters.
referrals = Referral.objects.current().filter(**query_params)
if tags: # Optional: filter by tags.
referrals = referrals.filter(tags__in=tags).distinct()
# Write the column headers to the new worksheet.
headers = [
'Referral ID', 'Region(s)', 'Referrer', 'Type', 'Reference',
'Received', 'Description', 'Address', 'Triggers', 'Tags',
'File no.', 'LGA'
]
for col, value in enumerate(headers, 1):
cell = ws.cell(row=1, column=col)
cell.value = value
cell.font = arial
# Write the referral values to the worksheet.
for row, r in enumerate(referrals, 2): # Start at row 2
cell = ws.cell(row=row, column=1)
cell.value = r.pk
cell.font = arial
cell = ws.cell(row=row, column=2)
cell.value = r.regions_str
cell.font = arial
cell = ws.cell(row=row, column=3)
cell.value = r.referring_org.name
cell.font = arial
cell = ws.cell(row=row, column=4)
cell.value = r.type.name
cell.font = arial
cell = ws.cell(row=row, column=5)
cell.value = r.reference
cell.font = arial
cell = ws.cell(row=row, column=6)
cell.value = r.referral_date
cell.number_format = date_style
cell.font = arial
cell = ws.cell(row=row, column=7)
cell.value = r.description
cell.font = arial
cell = ws.cell(row=row, column=8)
cell.value = r.address
cell.font = arial
cell = ws.cell(row=row, column=9)
cell.value = ', '.join([t.name for t in r.dop_triggers.all()])
cell.font = arial
cell = ws.cell(row=row, column=10)
cell.value = ', '.join([t.name for t in r.tags.all()])
cell.font = arial
cell = ws.cell(row=row, column=11)
cell.value = r.file_no
cell.font = arial
cell = ws.cell(row=row, column=12)
cell.value = r.lga.name if r.lga else ''
cell.font = arial
elif model == Clearance:
response[
'Content-Disposition'] = 'attachment; filename=prs_clearance_requests.xlsx'
# Filter clearance objects according to the parameters.
clearances = Clearance.objects.current().filter(**query_params)
# Write the column headers to the new worksheet.
headers = [
'Referral ID', 'Region(s)', 'Reference', 'Condition no.',
'Approved condition', 'Category', 'Task description',
'Deposited plan no.', 'Assigned user', 'Status', 'Start date',
'Due date', 'Complete date', 'Stop date', 'Restart date',
'Total stop days'
]
for col, value in enumerate(headers, 1):
cell = ws.cell(row=1, column=col)
cell.value = value
cell.font = arial
# Write the clearance values to the worksheet.
for row, c in enumerate(clearances, 2): # Start at row 2
cell = ws.cell(row=row, column=1)
cell.value = c.condition.referral.pk
cell.font = arial
cell = ws.cell(row=row, column=2)
cell.value = c.condition.referral.regions_str
cell.font = arial
cell = ws.cell(row=row, column=3)
cell.value = c.condition.referral.reference
cell.font = arial
cell = ws.cell(row=row, column=4)
cell.value = c.condition.identifier
cell.font = arial
cell = ws.cell(row=row, column=5)
cell.value = c.condition.condition
cell.font = arial
cell = ws.cell(row=row, column=6)
cell.font = arial
if c.condition.category:
cell.value = c.condition.category.name
cell = ws.cell(row=row, column=7)
cell.value = c.task.description
cell.font = arial
cell = ws.cell(row=row, column=8)
cell.value = c.deposited_plan
cell.font = arial
cell = ws.cell(row=row, column=9)
cell.value = c.task.assigned_user.get_full_name()
cell.font = arial
cell = ws.cell(row=row, column=10)
cell.value = c.task.state.name
cell.font = arial
cell = ws.cell(row=row, column=11)
cell.value = c.task.start_date
cell.number_format = date_style
cell.font = arial
cell = ws.cell(row=row, column=12)
cell.value = c.task.due_date
cell.number_format = date_style
cell.font = arial
cell = ws.cell(row=row, column=13)
cell.value = c.task.complete_date
cell.number_format = date_style
cell.font = arial
cell = ws.cell(row=row, column=14)
cell.value = c.task.stop_date
cell.number_format = date_style
cell.font = arial
cell = ws.cell(row=row, column=15)
cell.value = c.task.restart_date
cell.number_format = date_style
cell.font = arial
cell = ws.cell(row=row, column=16)
cell.value = c.task.stop_time
cell.font = arial
elif model == Task:
response[
'Content-Disposition'] = 'attachment; filename=prs_tasks.xlsx'
# Filter task objects according to the parameters.
tasks = Task.objects.current().filter(**query_params)
# Business rule: filter out 'Condition clearance' task types.
cr = TaskType.objects.get(name='Conditions clearance request')
tasks = tasks.exclude(type=cr)
# Write the column headers to the new worksheet.
headers = [
'Task ID', 'Region(s)', 'Referral ID', 'Referred by',
'Referral type', 'Reference', 'Referral received', 'Task type',
'Task status', 'Assigned user', 'Task start', 'Task due',
'Task complete', 'Stop date', 'Restart date',
'Total stop days', 'File no.', 'DoP triggers',
'Referral description', 'Referral address', 'LGA'
]
for col, value in enumerate(headers, 1):
cell = ws.cell(row=1, column=col)
cell.value = value
cell.font = arial
# Write the task values to the worksheet.
for row, t in enumerate(tasks, 2): # Start at row 2
cell = ws.cell(row=row, column=1)
cell.value = t.pk
cell.font = arial
cell = ws.cell(row=row, column=2)
cell.value = t.referral.regions_str
cell.font = arial
cell = ws.cell(row=row, column=3)
cell.value = t.referral.pk
cell.font = arial
cell = ws.cell(row=row, column=4)
cell.value = t.referral.referring_org.name
cell.font = arial
cell = ws.cell(row=row, column=5)
cell.value = t.referral.type.name
cell.font = arial
cell = ws.cell(row=row, column=6)
cell.value = t.referral.reference
cell.font = arial
cell = ws.cell(row=row, column=7)
cell.value = t.referral.referral_date
cell.number_format = date_style
cell.font = arial
cell = ws.cell(row=row, column=8)
cell.value = t.type.name
cell.font = arial
cell = ws.cell(row=row, column=9)
cell.value = t.state.name
cell.font = arial
cell = ws.cell(row=row, column=10)
cell.value = t.assigned_user.get_full_name()
cell.font = arial
cell = ws.cell(row=row, column=11)
cell.value = t.start_date
cell.number_format = date_style
cell.font = arial
cell = ws.cell(row=row, column=12)
cell.value = t.due_date
cell.number_format = date_style
cell.font = arial
cell = ws.cell(row=row, column=13)
cell.value = t.complete_date
cell.number_format = date_style
cell.font = arial
cell = ws.cell(row=row, column=14)
cell.value = t.stop_date
cell.number_format = date_style
cell.font = arial
cell = ws.cell(row=row, column=15)
cell.value = t.restart_date
cell.number_format = date_style
cell.font = arial
cell = ws.cell(row=row, column=16)
cell.value = t.stop_time
cell.font = arial
cell = ws.cell(row=row, column=17)
cell.value = t.referral.file_no
cell.font = arial
cell = ws.cell(row=row, column=18)
cell.value = ', '.join(
[i.name for i in t.referral.dop_triggers.all()])
cell.font = arial
cell = ws.cell(row=row, column=19)
cell.value = t.referral.description
cell.font = arial
cell = ws.cell(row=row, column=20)
cell.value = t.referral.address
cell.font = arial
cell = ws.cell(row=row, column=21)
cell.value = t.referral.lga.name if t.referral.lga else ''
cell.font = arial
wb.save(response) # Save the workbook contents to the response.
return response
import openpyxl
from openpyxl import Workbook
import datetime
from time import sleep
wb_name = '-Aggregated.xlsx'
cwd_dir = os.getcwd()
data_dir = os.getcwd() + "\\data\\"
death = 0
infected = 0
line = 1
line_total = 1
workbook = Workbook()
ws = workbook.active
sheet_wy = workbook.create_sheet('WY', 0)
sheet_wi = workbook.create_sheet('WI', 0)
sheet_wv = workbook.create_sheet('WV', 0)
sheet_wa = workbook.create_sheet('WA', 0)
sheet_va = workbook.create_sheet('VA', 0)
sheet_vt = workbook.create_sheet('VT', 0)
sheet_ut = workbook.create_sheet('UT', 0)
sheet_tx = workbook.create_sheet('TX', 0)
sheet_tn = workbook.create_sheet('TN', 0)
sheet_sd = workbook.create_sheet('SD', 0)
sheet_sc = workbook.create_sheet('SC', 0)
sheet_ri = workbook.create_sheet('RI', 0)
sheet_pr = workbook.create_sheet('PR', 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 = 102
for i in range(2, 2000 + 1):
ws.row_dimensions[i].height = 30
# Col width
ws.column_dimensions['A'].width = 1.5
ws.column_dimensions['B'].width = 25.0
for i in range(ord('C'), ord('L')):
ws.column_dimensions[chr(i)].width = 15.0
# Font
name_font = Font(name='Constantia', size=15, bold=True)
name_small_font = Font(name='Constantia', size=10, bold=True)
title_font = Font(name='宋体', size=15, bold=True)
title_small_font = Font(name='宋体', size=10, 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.width = img.width * 0.85
img.height = img.height * 0.85
# img = Image("myems.png")
ws.add_image(img, 'B1')
# Title
ws.merge_cells('B3:I3')
ws['B3'].font = name_font
ws['B3'].alignment = b_c_alignment
ws['B3'] = 'Name: ' + name + ' Period: ' + period_type + \
' Date: ' + reporting_start_datetime_local + "__" + reporting_end_datetime_local
if "reporting_period" not in report.keys() or \
"names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0:
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
##################################
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['B' + str(current_row_number)].fill = table_fill
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_small_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = \
reporting_period_data['names'][i] + " " + reporting_period_data['energy_category_names'][i] + \
" (" + reporting_period_data['units'][i] + ")"
col = chr(ord(col) + 1)
current_row_number += 1
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '消耗'
col = 'C'
for i in range(0, ca_len):
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = round(
reporting_period_data['subtotals'][i], 2)
col = chr(ord(col) + 1)
current_row_number += 1
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '单位面积值'
col = 'C'
for i in range(0, ca_len):
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = round(
reporting_period_data['subtotals_per_unit_area'][i], 2)
col = chr(ord(col) + 1)
current_row_number += 1
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '环比'
col = 'C'
for i in range(0, ca_len):
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = str(
round(reporting_period_data['increment_rates'][i] * 100, 2)) + '%' \
if reporting_period_data['increment_rates'][i] is not None else '-'
col = chr(ord(col) + 1)
current_row_number += 2
category_dict = group_by_category(
reporting_period_data['energy_category_names'])
for category_dict_name, category_dict_values in category_dict.items():
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = \
category_dict_name + ' (' + reporting_period_data['units'][category_dict_values[0]] + \
') 分项消耗占比'
current_row_number += 1
table_start_row_number = current_row_number
ws['B' + str(current_row_number)].fill = table_fill
ws['C' + str(current_row_number)].font = name_small_font
ws['C' + str(current_row_number)].fill = table_fill
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = '消耗'
current_row_number += 1
for i in category_dict_values:
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = \
reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
ws['C' + str(current_row_number)].font = name_font
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = round(
reporting_period_data['subtotals'][i], 2)
current_row_number += 1
table_end_row_number = current_row_number - 1
pie = PieChart()
pie.title = \
category_dict_name + ' (' + reporting_period_data['units'][category_dict_values[0]] + \
') 分项消耗占比'
labels = Reference(ws,
min_col=2,
min_row=table_start_row_number + 1,
max_row=table_end_row_number)
pie_data = Reference(ws,
min_col=3,
min_row=table_start_row_number,
max_row=table_end_row_number)
pie.add_data(pie_data, titles_from_data=True)
pie.set_categories(labels)
pie.height = 5.25
pie.width = 9
s1 = pie.series[0]
s1.dLbls = DataLabelList()
s1.dLbls.showCatName = False
s1.dLbls.showVal = True
s1.dLbls.showPercent = True
ws.add_chart(pie, 'D' + str(table_start_row_number))
if len(category_dict_values) < 4:
current_row_number = current_row_number - len(
category_dict_values) + 4
current_row_number += 1
#####################################
has_child_flag = True
if "child_space" not in report.keys() or "energy_item_names" not in report['child_space'].keys() or \
len(report['child_space']["energy_item_names"]) == 0:
has_child_flag = False
if has_child_flag:
child = report['child_space']
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 子空间数据'
current_row_number += 1
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].border = f_border
ca_len = len(child['energy_item_names'])
for i in range(0, ca_len):
row = chr(ord('C') + i)
ws[row + str(current_row_number)].fill = table_fill
ws[row + str(current_row_number)].font = name_small_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] + ")"
space_len = len(child['child_space_names_array'][0])
for i in range(0, space_len):
current_row_number += 1
row = str(current_row_number)
ws['B' + row].font = name_font
ws['B' + row].alignment = c_c_alignment
ws['B' + row] = child['child_space_names_array'][0][i]
ws['B' + row].border = f_border
for j in range(0, ca_len):
col = chr(ord('C') + j)
ws[col + row].font = name_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = child['subtotals_array'][j][i]
ws[col + row].border = f_border
current_row_number += 1
# Pie
for i in range(0, ca_len):
pie = PieChart()
labels = Reference(ws,
min_col=2,
min_row=current_row_number - space_len,
max_row=current_row_number - 1)
pie_data = Reference(ws,
min_col=3 + i,
min_row=current_row_number - space_len -
1,
max_row=current_row_number - 1)
pie.add_data(pie_data, titles_from_data=True)
pie.set_categories(labels)
pie.height = 5.85
pie.width = 8
col = chr(ord('C') + i)
pie.title = ws[col +
str(current_row_number - space_len - 1)].value
s1 = pie.series[0]
s1.dLbls = DataLabelList()
s1.dLbls.showCatName = False
s1.dLbls.showVal = True
s1.dLbls.showPercent = True
chart_cell = ''
if i % 2 == 0:
chart_cell = 'B' + str(current_row_number)
else:
chart_cell = 'E' + str(current_row_number)
current_row_number += 6
ws.add_chart(pie, chart_cell)
current_row_number += 7
#######################
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['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].font = title_small_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_small_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):
bar = BarChart()
bar.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)
bar_data = Reference(ws,
min_col=3 + i,
min_row=table_start_row_number,
max_row=table_end_row_number)
bar.add_data(bar_data, titles_from_data=True)
bar.set_categories(labels)
bar.height = 5.85
bar.width = format_time_width_number * len(time) if len(
time) > min_len_number else min_width_number
if bar.width > 24:
bar.width = 24
bar.dLbls = DataLabelList()
bar.dLbls.showVal = False
bar.dLbls.showPercent = True
chart_col = 'B'
chart_cell = chart_col + str(chart_start_row_number)
chart_start_row_number += 6
ws.add_chart(bar, chart_cell)
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
