def _create_chart(book): c1 = LineChart() c1.title = "Mean Wind Speed" c1.style = 13 c1.y_axis.title = 'Speed' c1.x_axis.title = 'Time' mean_sheet = book.get_sheet_by_name(MEAN_WIND_SPEED_SHEET) max_sheet = book.get_sheet_by_name(MAX_WIND_SPEED_SHEET) max_row = first_empty_row(mean_sheet, 1) - 1 mean_data = Reference(mean_sheet, min_col=2, min_row=1, max_col=2, max_row=max_row) max_data = Reference(max_sheet, min_col=2, min_row=1, max_col=2, max_row=max_row) dates = Reference(mean_sheet, min_col=1, min_row=2, max_row=max_row) c1.add_data(mean_data, titles_from_data=True) c1.add_data(max_data, titles_from_data=True) c1.y_axis.scaling.max = 100 c1.set_categories(dates) s1 = c1.series[0] s2 = c1.series[1] s1.graphicalProperties.line.solidFill = "FF0000" s2.graphicalProperties.line.solidFill = "0000FF" ws = book.create_sheet() ws.title = "Charts" ws.add_chart(c1, "A1") return book
def add_chart(self, sheetname, x_col, y_col): ws = self.wb[sheetname] xtitle = ws.cell(1, x_col).value ytitle = ws.cell(1, y_col).value chart = LineChart() chart.title = "%s - %s" % (ytitle, xtitle) chart.style = 13 chart.x_axis.title = xtitle chart.y_axis.title = ytitle chart.marker = True chart.smooth = True y_data = Reference(ws, min_col=y_col, min_row=1, max_col=y_col, max_row=self.current_row) chart.add_data(y_data, titles_from_data=True) x_data = Reference(ws, min_col=x_col, min_row=2, max_col=x_col, max_row=self.current_row) chart.set_categories(x_data) s0 = chart.series[0] s0.marker.symbol = "circle" s0.smooth = True if 0 == self.chart_row: self.chart_row = self.current_row ws.add_chart(chart, "A%d" % self.chart_row) # row height is 7.5/16 cm by default self.chart_row = self.chart_row + chart.height / 7.5 * 16 + 2
def DBLOAD_CHART(worksheet, dbname, chartday, chartrow): loadchart = LineChart() loadchart.title = dbname + ' ' + chartday + ' DB time' loadchart.style = 10 loadchart.y_axis.title = 'Value' loadchart.x_axis.title = 'Housr' data = Reference(worksheet, min_col=4, min_row=8, max_col=6, max_row=8 + chartrow) loadchart.add_data(data, titles_from_data=True) # Style the lines loadseries1 = loadchart.series[0] loadseries1.marker.symbol = 'triangle' loadseries1.marker.graphicalProperties.solidFill = 'FF0000' # Marker filling loadseries1.marker.graphicalProperties.line.solidFill = 'FF0000' # Marker outline loadseries1.smooth = True # Style the lines loadseries2 = loadchart.series[1] loadseries2.marker.symbol = 'circle' loadseries2.marker.graphicalProperties.solidFill = '00FF00' # Marker filling loadseries2.marker.graphicalProperties.line.solidFill = '00FF00' # Marker outline loadseries2.smooth = True # Style the lines loadseries3 = loadchart.series[2] loadseries3.marker.symbol = 'plus' loadseries3.marker.graphicalProperties.solidFill = '0000FF' # Marker filling loadseries3.marker.graphicalProperties.line.solidFill = '0000FF' # Marker outline loadseries3.smooth = True return loadchart
def add_chart(sheet, chart_name, x_axis_name, y_axis_name, start_row, end_row, start_col, end_col, chart_position): chart = LineChart() chart.title = chart_name chart.style = 10 chart.height = 10 chart.width = 20 chart.y_axis.title = y_axis_name chart.x_axis.title = x_axis_name data = Reference(sheet, min_col = start_col, min_row=start_row, max_col = end_col, max_row=end_row) chart.add_data(data, titles_from_data=True) s1 = chart.series[0] s1.graphicalProperties.line.width = 30050 s2 = chart.series[1] s2.graphicalProperties.line.width = 30050 s3 = chart.series[2] s3.graphicalProperties.line.width = 30050 s4 = chart.series[3] s4.graphicalProperties.line.width = 30050 s5 = chart.series[4] s5.graphicalProperties.line.width = 30050 sheet.add_chart(chart, chart_position)
def insertarGraficoDevoluciones(ws): c1 = LineChart() c1.style = 13 c1.legend.position = 'b' c1.width = 25 maxCol = col2num(getMaxCol(ws, 'B', 4)) data = Reference(ws, min_col=1, min_row=5, max_col=maxCol, max_row=7) c1.add_data(data, titles_from_data=True, from_rows=True) categories = Reference(ws, min_col=2, min_row=4, max_col=maxCol) c1.set_categories(categories) setSerieStyle(c1.series[0], "circle" , "C00000") setSerieStyle(c1.series[1], "diamond" , "0000FF") setSerieStyle(c1.series[2], "triangle", "00B050") ws.add_chart(c1, "A19")
def insertarGraficoMercadoTC(ws): maxCol = col2num(getMaxCol(ws, 'B', 46)) c1 = BarChart() v1 = Reference(ws, min_col=1, min_row=47, max_col=maxCol) c1.add_data(v1, titles_from_data=True, from_rows=True) c1.y_axis.scaling.min = 0 c1.y_axis.majorGridlines = None c2 = LineChart() v2 = Reference(ws, min_col=1, min_row=48, max_col=maxCol) c2.add_data(v2, titles_from_data=True, from_rows=True) c2.y_axis.axId = 200 c1.z_axis = c2.y_axis categories = Reference(ws, min_col=2, min_row=46, max_col=maxCol) c1.set_categories(categories) # Display y-axis of the second chart on the right by setting it to cross the x-axis at its maximum c1.y_axis.crosses = "max" c1 += c2 ws.add_chart(c1, "A54")
def generate_excel(report, name, reporting_start_datetime_local, reporting_end_datetime_local, period_type): wb = Workbook() ws = wb.active ws.title = "SpaceStatistics" # Row height ws.row_dimensions[1].height = 102 for i in range(2, 2000 + 1): ws.row_dimensions[i].height = 42 # Col width ws.column_dimensions['A'].width = 1.5 ws.column_dimensions['B'].width = 25.0 for i in range(ord('C'), ord('L')): ws.column_dimensions[chr(i)].width = 15.0 # Font name_font = Font(name='Arial', size=15, bold=True) title_font = Font(name='Arial', size=15, bold=True) table_fill = PatternFill(fill_type='solid', fgColor='1F497D') f_border = Border(left=Side(border_style='medium', color='00000000'), right=Side(border_style='medium', color='00000000'), bottom=Side(border_style='medium', color='00000000'), top=Side(border_style='medium', color='00000000')) b_border = Border(bottom=Side(border_style='medium', color='00000000'), ) b_c_alignment = Alignment(vertical='bottom', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) c_c_alignment = Alignment(vertical='center', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) b_r_alignment = Alignment(vertical='bottom', horizontal='right', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) # Img img = Image("excelexporters/myems.png") ws.add_image(img, 'A1') # Title ws['B3'].alignment = b_r_alignment ws['B3'] = 'Name:' ws['C3'].border = b_border ws['C3'].alignment = b_c_alignment ws['C3'] = name ws['D3'].alignment = b_r_alignment ws['D3'] = 'Period:' ws['E3'].border = b_border ws['E3'].alignment = b_c_alignment ws['E3'] = period_type ws['B4'].alignment = b_r_alignment ws['B4'] = 'Reporting Start Datetime:' ws['C4'].border = b_border ws['C4'].alignment = b_c_alignment ws['C4'] = reporting_start_datetime_local ws['D4'].alignment = b_r_alignment ws['D4'] = 'Reporting End Datetime:' ws['E4'].border = b_border ws['E4'].alignment = b_c_alignment ws['E4'] = reporting_end_datetime_local if "reporting_period" not in report.keys() or \ "names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0: filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename #################################################################################################################### # First: Statistics # 6: title # 7: table title # 8~ca_len table_data #################################################################################################################### reporting_period_data = report['reporting_period'] if "names" not in reporting_period_data.keys() or \ reporting_period_data['names'] is None or \ len(reporting_period_data['names']) == 0: filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename ws['B6'].font = title_font ws['B6'] = name + ' ' + 'Statistics' 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'] = 'Reporting Period' ws['B7'].border = f_border ws['C7'].font = title_font ws['C7'].alignment = c_c_alignment ws['C7'] = 'Arithmetic Mean' ws['C7'].border = f_border ws['D7'].font = title_font ws['D7'].alignment = c_c_alignment ws['D7'] = 'Median (Middle Value)' ws['D7'].border = f_border ws['E7'].font = title_font ws['E7'].alignment = c_c_alignment ws['E7'] = 'Minimum Value' ws['E7'].border = f_border ws['F7'].font = title_font ws['F7'].alignment = c_c_alignment ws['F7'] = 'Maximum Value' ws['F7'].border = f_border ws['G7'].font = title_font ws['G7'].alignment = c_c_alignment ws['G7'] = 'Sample Standard Deviation' ws['G7'].border = f_border ws['H7'].font = title_font ws['H7'].alignment = c_c_alignment ws['H7'] = 'Sample Variance' 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)] = 'Increment Rate' ws['B' + str(row + 1)].border = f_border ws['C' + str(row)].font = name_font ws['C' + str(row)].alignment = c_c_alignment ws['C' + str(row)] = round(reporting_period_data['means'][i], 2) \ if reporting_period_data['means'][i] is not None else '' ws['C' + str(row)].border = f_border ws['C' + str(row)].number_format = '0.00' ws['C' + str(row + 1)].font = name_font ws['C' + str(row + 1)].alignment = c_c_alignment ws['C' + str(row + 1)] = str(round(reporting_period_data['means_increment_rate'][i] * 100, 2)) + "%" \ if reporting_period_data['means_increment_rate'][i] is not None else '0.00%' ws['C' + str(row + 1)].border = f_border ws['D' + str(row)].font = name_font ws['D' + str(row)].alignment = c_c_alignment ws['D' + str(row)] = round(reporting_period_data['medians'][i], 2) \ if reporting_period_data['medians'][i] is not None else '' ws['D' + str(row)].border = f_border ws['D' + str(row)].number_format = '0.00' ws['D' + str(row + 1)].font = name_font ws['D' + str(row + 1)].alignment = c_c_alignment ws['D' + str(row + 1)] = str(round(reporting_period_data['medians_increment_rate'][i] * 100, 2)) + "%" \ if reporting_period_data['medians_increment_rate'][i] is not None else '0.00%' ws['D' + str(row + 1)].border = f_border ws['E' + str(row)].font = name_font ws['E' + str(row)].alignment = c_c_alignment ws['E' + str(row)] = round(reporting_period_data['minimums'][i], 2) \ if reporting_period_data['minimums'][i] is not None else '' ws['E' + str(row)].border = f_border ws['E' + str(row)].number_format = '0.00' ws['E' + str(row + 1)].font = name_font ws['E' + str(row + 1)].alignment = c_c_alignment ws['E' + str(row + 1)] = str(round(reporting_period_data['minimums_increment_rate'][i] * 100, 2)) + "%" \ if reporting_period_data['minimums_increment_rate'][i] is not None else '0.00%' ws['E' + str(row + 1)].border = f_border ws['F' + str(row)].font = name_font ws['F' + str(row)].alignment = c_c_alignment ws['F' + str(row)] = round(reporting_period_data['maximums'][i], 2) \ if reporting_period_data['maximums'][i] is not None else '' ws['F' + str(row)].border = f_border ws['F' + str(row)].number_format = '0.00' ws['F' + str(row + 1)].font = name_font ws['F' + str(row + 1)].alignment = c_c_alignment ws['F' + str(row + 1)] = str(round(reporting_period_data['maximums_increment_rate'][i] * 100, 2)) + "%" \ if reporting_period_data['maximums_increment_rate'][i] is not None else '0.00%' ws['F' + str(row + 1)].border = f_border ws['G' + str(row)].font = name_font ws['G' + str(row)].alignment = c_c_alignment ws['G' + str(row)] = round(reporting_period_data['stdevs'][i], 2) \ if reporting_period_data['stdevs'][i] is not None else '' ws['G' + str(row)].border = f_border ws['G' + str(row)].number_format = '0.00' ws['G' + str(row + 1)].font = name_font ws['G' + str(row + 1)].alignment = c_c_alignment ws['G' + str(row + 1)] = str(round(reporting_period_data['stdevs_increment_rate'][i] * 100, 2)) + "%" \ if reporting_period_data['stdevs_increment_rate'][i] is not None else '0.00%' ws['G' + str(row + 1)].border = f_border ws['H' + str(row)].font = name_font ws['H' + str(row)].alignment = c_c_alignment ws['H' + str(row)] = round(reporting_period_data['variances'][i], 2) \ if reporting_period_data['variances'][i] is not None else '' ws['H' + str(row)].border = f_border ws['H' + str(row)].number_format = '0.00' ws['H' + str(row + 1)].font = name_font ws['H' + str(row + 1)].alignment = c_c_alignment ws['H' + str(row + 1)] = str(round(reporting_period_data['variances_increment_rate'][i] * 100, 2)) + "%" \ if reporting_period_data['variances_increment_rate'][i] is not None else '0.00%' ws['H' + str(row + 1)].border = f_border #################################################################################################################### # Second: Reporting Period Consumption # 9 + ca_len * 2: title # 10 + ca_len * 2: table title # per_unit_area_start_row_number + 2 ~ per_unit_area_start_row_number + 2 + ca_len : table_data #################################################################################################################### names = reporting_period_data['names'] ca_len = len(names) per_unit_area_start_row_number = 9 + ca_len * 2 ws['B' + str(per_unit_area_start_row_number)].font = title_font ws['B' + str(per_unit_area_start_row_number)] = name + ' ' + 'Per Unit Area' ws['D' + str(per_unit_area_start_row_number)].font = title_font ws['D' + str(per_unit_area_start_row_number)] = str( report['space']['area']) + 'M²' category = reporting_period_data['names'] # table_title ws['B' + str(per_unit_area_start_row_number + 1)].fill = table_fill ws['B' + str(per_unit_area_start_row_number + 1)].font = title_font ws['B' + str(per_unit_area_start_row_number + 1)].alignment = c_c_alignment ws['B' + str(per_unit_area_start_row_number + 1)] = 'Reporting Period' ws['B' + str(per_unit_area_start_row_number + 1)].border = f_border ws['C' + str(per_unit_area_start_row_number + 1)].font = title_font ws['C' + str(per_unit_area_start_row_number + 1)].alignment = c_c_alignment ws['C' + str(per_unit_area_start_row_number + 1)] = 'Arithmetic Mean' ws['C' + str(per_unit_area_start_row_number + 1)].border = f_border ws['D' + str(per_unit_area_start_row_number + 1)].font = title_font ws['D' + str(per_unit_area_start_row_number + 1)].alignment = c_c_alignment ws['D' + str(per_unit_area_start_row_number + 1)] = 'Median (Middle Value)' ws['D' + str(per_unit_area_start_row_number + 1)].border = f_border ws['E' + str(per_unit_area_start_row_number + 1)].font = title_font ws['E' + str(per_unit_area_start_row_number + 1)].alignment = c_c_alignment ws['E' + str(per_unit_area_start_row_number + 1)] = 'Minimum Value' ws['E' + str(per_unit_area_start_row_number + 1)].border = f_border ws['F' + str(per_unit_area_start_row_number + 1)].font = title_font ws['F' + str(per_unit_area_start_row_number + 1)].alignment = c_c_alignment ws['F' + str(per_unit_area_start_row_number + 1)] = 'Maximum Value' ws['F' + str(per_unit_area_start_row_number + 1)].border = f_border ws['G' + str(per_unit_area_start_row_number + 1)].font = title_font ws['G' + str(per_unit_area_start_row_number + 1)].alignment = c_c_alignment ws['G' + str(per_unit_area_start_row_number + 1)] = 'Sample Standard Deviation' ws['G' + str(per_unit_area_start_row_number + 1)].border = f_border ws['H' + str(per_unit_area_start_row_number + 1)].font = title_font ws['H' + str(per_unit_area_start_row_number + 1)].alignment = c_c_alignment ws['H' + str(per_unit_area_start_row_number + 1)] = 'Sample Variance' ws['H' + str(per_unit_area_start_row_number + 1)].border = f_border # table_data for i, value in enumerate(category): row_data = per_unit_area_start_row_number + 2 + i ws['B' + str(row_data)].font = name_font ws['B' + str(row_data)].alignment = c_c_alignment ws['B' + str(row_data)] = reporting_period_data['names'][ i] + " (" + reporting_period_data['units'][i] + "/M²)" ws['B' + str(row_data)].border = f_border ws['C' + str(row_data)].font = name_font ws['C' + str(row_data)].alignment = c_c_alignment if reporting_period_data['means_per_unit_area'][i] \ or reporting_period_data['means_per_unit_area'][i] == 0: ws['C' + str(row_data)] = round( reporting_period_data['means_per_unit_area'][i], 2) ws['C' + str(row_data)].border = f_border ws['C' + str(row_data)].number_format = '0.00' ws['D' + str(row_data)].font = name_font ws['D' + str(row_data)].alignment = c_c_alignment if reporting_period_data['medians_per_unit_area'][i] \ or reporting_period_data['medians_per_unit_area'][i] == 0: ws['D' + str(row_data)] = round( reporting_period_data['medians_per_unit_area'][i], 2) ws['D' + str(row_data)].border = f_border ws['D' + str(row_data)].number_format = '0.00' ws['E' + str(row_data)].font = name_font ws['E' + str(row_data)].alignment = c_c_alignment if reporting_period_data['minimums_per_unit_area'][i] \ or reporting_period_data['minimums_per_unit_area'][i] == 0: ws['E' + str(row_data)] = round( reporting_period_data['minimums_per_unit_area'][i], 2) ws['E' + str(row_data)].border = f_border ws['E' + str(row_data)].number_format = '0.00' ws['F' + str(row_data)].font = name_font ws['F' + str(row_data)].alignment = c_c_alignment if reporting_period_data['maximums_per_unit_area'][i] \ or reporting_period_data['maximums_per_unit_area'][i] == 0: ws['F' + str(row_data)] = round( reporting_period_data['maximums_per_unit_area'][i], 2) ws['F' + str(row_data)].border = f_border ws['F' + str(row_data)].number_format = '0.00' ws['G' + str(row_data)].font = name_font ws['G' + str(row_data)].alignment = c_c_alignment if (reporting_period_data['stdevs_per_unit_area'][i]) \ or reporting_period_data['stdevs_per_unit_area'][i] == 0: ws['G' + str(row_data)] = round( reporting_period_data['stdevs_per_unit_area'][i], 2) ws['G' + str(row_data)].border = f_border ws['G' + str(row_data)].number_format = '0.00' ws['H' + str(row_data)].font = name_font ws['H' + str(row_data)].alignment = c_c_alignment if reporting_period_data['variances_per_unit_area'][i] \ or reporting_period_data['variances_per_unit_area'][i] == 0: ws['H' + str(row_data)] = round( reporting_period_data['variances_per_unit_area'][i], 2) ws['H' + str(row_data)].border = f_border ws['H' + str(row_data)].number_format = '0.00' #################################################################################################################### # Third: Detailed Data # analysis_end_row_number~ analysis_end_row_number+time_len: line # analysis_end_row_number+1+line_charts_row_number: table title # i + analysis_end_row_number + 2 + 10 * ca_len~: table_data #################################################################################################################### has_timestamps_flag = True if "timestamps" not in reporting_period_data.keys() or \ reporting_period_data['timestamps'] is None or \ len(reporting_period_data['timestamps']) == 0: has_timestamps_flag = False if has_timestamps_flag: timestamps = reporting_period_data['timestamps'][0] values = reporting_period_data['values'] names = reporting_period_data['names'] ca_len = len(names) time_len = len(timestamps) real_timestamps_len = timestamps_data_not_equal_0( report['parameters']['timestamps']) # title line_charts_row_number = 6 * ca_len + real_timestamps_len * 7 analysis_end_row_number = 12 + 3 * ca_len detailed_start_row_number = analysis_end_row_number + line_charts_row_number + 1 ws['B' + str(detailed_start_row_number)].font = title_font ws['B' + str(detailed_start_row_number)] = name + ' ' + 'Detailed Data' # table_title ws['B' + str(detailed_start_row_number + 1)].fill = table_fill ws['B' + str(detailed_start_row_number + 1)].font = name_font ws['B' + str(detailed_start_row_number + 1)].alignment = c_c_alignment ws['B' + str(detailed_start_row_number + 1)] = 'Datetime' ws['B' + str(detailed_start_row_number + 1)].border = f_border for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + str(detailed_start_row_number + 1)].font = name_font ws[col + str(detailed_start_row_number + 1)].alignment = c_c_alignment ws[col + str(detailed_start_row_number + 1)] = names[ i] + " - (" + reporting_period_data['units'][i] + ")" ws[col + str(detailed_start_row_number + 1)].border = f_border # table_date for i in range(0, time_len): rows = i + detailed_start_row_number + 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)] = round(values[index][i], 2) ws[col + str(rows)].number_format = '0.00' ws[col + str(rows)].border = f_border # Subtotal row_subtotals = detailed_start_row_number + 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)] = 'Subtotal' ws['B' + str(row_subtotals)].border = f_border for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + str(row_subtotals)].font = name_font ws[col + str(row_subtotals)].alignment = c_c_alignment ws[col + str(row_subtotals)] = round( reporting_period_data['subtotals'][i], 2) ws[col + str(row_subtotals)].border = f_border ws[col + str(row_subtotals)].number_format = '0.00' #################################################################################################################### # third: LineChart # LineChart requires data from the detailed data table in the Excel file # so print the detailed data table first and then print LineChart #################################################################################################################### for i in range(0, ca_len): line = LineChart() line.title = "Reporting Period Consumption" + " - " + names[i] + \ "(" + reporting_period_data['units'][i] + ")" line.style = 10 line.x_axis.majorTickMark = 'in' line.y_axis.majorTickMark = 'in' line.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 times = Reference(ws, min_col=2, min_row=detailed_start_row_number + 2, max_row=detailed_start_row_number + 2 + time_len) line_data = Reference(ws, min_col=3 + i, min_row=detailed_start_row_number + 1, max_row=detailed_start_row_number + 1 + time_len) line.add_data(line_data, titles_from_data=True) line.set_categories(times) ser = line.series[0] ser.marker.symbol = "diamond" ser.marker.size = 5 ws.add_chart(line, 'B' + str(analysis_end_row_number + 6 * i)) #################################################################################################################### current_sheet_parameters_row_number = analysis_end_row_number + ca_len * 6 + 1 has_parameters_names_and_timestamps_and_values_data = True if 'parameters' not in report.keys() or \ report['parameters'] is None or \ 'names' not in report['parameters'].keys() or \ report['parameters']['names'] is None or \ len(report['parameters']['names']) == 0 or \ 'timestamps' not in report['parameters'].keys() or \ report['parameters']['timestamps'] is None or \ len(report['parameters']['timestamps']) == 0 or \ 'values' not in report['parameters'].keys() or \ report['parameters']['values'] is None or \ len(report['parameters']['values']) == 0 or \ timestamps_data_all_equal_0(report['parameters']['timestamps']): has_parameters_names_and_timestamps_and_values_data = False if has_parameters_names_and_timestamps_and_values_data: ################################################################################################################ # new worksheet ################################################################################################################ parameters_data = report['parameters'] parameters_names_len = len(parameters_data['names']) file_name = ws.title parameters_ws = wb.create_sheet(file_name + 'Parameters') parameters_timestamps_data_max_len = \ get_parameters_timestamps_lists_max_len(list(parameters_data['timestamps'])) # Row height parameters_ws.row_dimensions[1].height = 102 for i in range(2, 7 + 1): parameters_ws.row_dimensions[i].height = 42 for i in range(8, parameters_timestamps_data_max_len + 10): parameters_ws.row_dimensions[i].height = 60 # Col width parameters_ws.column_dimensions['A'].width = 1.5 parameters_ws.column_dimensions['B'].width = 25.0 for i in range(3, 12 + parameters_names_len * 3): parameters_ws.column_dimensions[format_cell.get_column_letter( i)].width = 15.0 # Img img = Image("excelexporters/myems.png") parameters_ws.add_image(img, 'A1') # Title parameters_ws['B3'].alignment = b_r_alignment parameters_ws['B3'] = 'Name:' parameters_ws['C3'].border = b_border parameters_ws['C3'].alignment = b_c_alignment parameters_ws['C3'] = name parameters_ws['D3'].alignment = b_r_alignment parameters_ws['D3'] = 'Period:' parameters_ws['E3'].border = b_border parameters_ws['E3'].alignment = b_c_alignment parameters_ws['E3'] = period_type parameters_ws['B4'].alignment = b_r_alignment parameters_ws['B4'] = 'Reporting Start Datetime:' parameters_ws['C4'].border = b_border parameters_ws['C4'].alignment = b_c_alignment parameters_ws['C4'] = reporting_start_datetime_local parameters_ws['D4'].alignment = b_r_alignment parameters_ws['D4'] = 'Reporting End Datetime:' parameters_ws['E4'].border = b_border parameters_ws['E4'].alignment = b_c_alignment parameters_ws['E4'] = reporting_end_datetime_local parameters_ws_current_row_number = 6 parameters_ws['B' + str(parameters_ws_current_row_number)].font = title_font parameters_ws[ 'B' + str(parameters_ws_current_row_number)] = name + ' ' + 'Parameters' parameters_ws_current_row_number += 1 parameters_table_start_row_number = parameters_ws_current_row_number parameters_ws.row_dimensions[ parameters_ws_current_row_number].height = 80 parameters_ws_current_row_number += 1 table_current_col_number = 2 for i in range(0, parameters_names_len): if len(parameters_data['timestamps'][i]) == 0: continue col = format_cell.get_column_letter(table_current_col_number) parameters_ws[col + str(parameters_ws_current_row_number - 1)].fill = table_fill parameters_ws[col + str(parameters_ws_current_row_number - 1)].border = f_border col = format_cell.get_column_letter(table_current_col_number + 1) parameters_ws[col + str(parameters_ws_current_row_number - 1)].fill = table_fill parameters_ws[col + str(parameters_ws_current_row_number - 1)].border = f_border parameters_ws[col + str(parameters_ws_current_row_number - 1)].font = name_font parameters_ws[col + str(parameters_ws_current_row_number - 1)].alignment = c_c_alignment parameters_ws[col + str(parameters_ws_current_row_number - 1)] = parameters_data['names'][i] table_current_row_number = parameters_ws_current_row_number for j, value in enumerate(list(parameters_data['timestamps'][i])): col = format_cell.get_column_letter(table_current_col_number) parameters_ws[col + str(table_current_row_number)].border = f_border parameters_ws[col + str(table_current_row_number)].font = title_font parameters_ws[ col + str(table_current_row_number)].alignment = c_c_alignment parameters_ws[col + str(table_current_row_number)] = value col = format_cell.get_column_letter(table_current_col_number + 1) parameters_ws[col + str(table_current_row_number)].border = f_border parameters_ws[col + str(table_current_row_number)].font = title_font parameters_ws[ col + str(table_current_row_number)].alignment = c_c_alignment parameters_ws[col + str(table_current_row_number)] = round( parameters_data['values'][i][j], 2) table_current_row_number += 1 table_current_col_number = table_current_col_number + 3 ################################################################################################################ # parameters chart and parameters table ################################################################################################################ ws['B' + str(current_sheet_parameters_row_number)].font = title_font ws['B' + str( current_sheet_parameters_row_number)] = name + ' ' + 'Parameters' current_sheet_parameters_row_number += 1 chart_start_row_number = current_sheet_parameters_row_number col_index = 0 for i in range(0, parameters_names_len): if len(parameters_data['timestamps'][i]) == 0: continue line = LineChart() data_col = 3 + col_index * 3 labels_col = 2 + col_index * 3 col_index += 1 line.title = 'Parameters - ' + \ parameters_ws.cell(row=parameters_table_start_row_number, column=data_col).value labels = Reference(parameters_ws, min_col=labels_col, min_row=parameters_table_start_row_number + 1, max_row=(len(parameters_data['timestamps'][i]) + parameters_table_start_row_number)) line_data = Reference( parameters_ws, min_col=data_col, min_row=parameters_table_start_row_number, max_row=(len(parameters_data['timestamps'][i]) + parameters_table_start_row_number)) line.add_data(line_data, titles_from_data=True) line.set_categories(labels) line_data = line.series[0] line_data.marker.symbol = "circle" line_data.smooth = True line.x_axis.crosses = 'min' line.height = 8.25 line.width = 24 line.dLbls = DataLabelList() line.dLbls.dLblPos = 't' line.dLbls.showVal = False line.dLbls.showPercent = False chart_col = 'B' chart_cell = chart_col + str(chart_start_row_number) chart_start_row_number += 6 ws.add_chart(line, chart_cell) current_sheet_parameters_row_number = chart_start_row_number current_sheet_parameters_row_number += 1 #################################################################################################################### filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename
['Humans', 10, 40, 50, 20, 10, 50], ] for row in rows: ws.append(row) c1 = BarChart() v1 = Reference(ws, min_col=1, min_row=1, max_col=7) c1.add_data(v1, titles_from_data=True, from_rows=True) c1.x_axis.title = 'Days' c1.y_axis.title = 'Aliens' c1.y_axis.majorGridlines = None c1.title = 'Survey results' # Create a second chart c2 = LineChart() v2 = Reference(ws, min_col=1, min_row=2, max_col=7) c2.add_data(v2, titles_from_data=True, from_rows=True) c2.y_axis.axId = 200 c2.y_axis.title = "Humans" # Display y-axis of the second chart on the right by setting it to cross the x-axis at its maximum c1.y_axis.crosses = "max" c1 += c2 ws.add_chart(c1, "D4") wb.save("secondary.xlsx")
# 插入平均Frame值 resultsheet['J1'] = "平均值ms" resultsheet['J2'] = "=AVERAGEA(H3:H122)" # 画图准备 chart = LineChart() chart.title = titlename + str(j) # chart.style = 5 #style都很丑,还不如默认的 chart.y_axis.title = 'ms' chart.x_axis.title = 'Frame' chart.width = 30 chart.height = 15 # data选取范围 data = Reference(resultsheet, min_col=8, min_row=2, max_col=9, max_row=122) chart.add_data(data, titles_from_data=True) # 创建图表,在B3位置插入 resultsheet.add_chart(chart,"B3") #记录时间戳作为文件名 # filename = time.strftime('%Y%m%d_%H%M%S',time.localtime(time.time())) + ".xlsx" # wb.save(filename) #以执行名称 titlename作为文件名 filename2 = titlename + str(j) + ".xlsx" wb.save(filename2) # 数据 print ("缓存处理完毕,保存数据到本地" + str(filename2)) time.sleep(3)
[date(2015,9, 15), 550, 125, 325], [date(2015,9, 22), 700, 150, 550], [date(2015,9, 29), 600, 100, 500], ] for row in rows: ws.append(row) c1 = LineChart() c1.title = "Line Chart" c1.style = 13 c1.y_axis.title = 'Number of People Counted' c1.x_axis.title = 'Service week' data = Reference(ws, min_col=2, min_row=1, max_col=4, max_row=7) c1.add_data(data, titles_from_data=True) # Style the lines s1 = c1.series[0] s1.marker.symbol = "triangle" s1.marker.graphicalProperties.solidFill = "FF0000" # Marker filling s1.marker.graphicalProperties.line.solidFill = "FF0000" # Marker outline s1.graphicalProperties.line.noFill = True s2 = c1.series[1] s2.graphicalProperties.line.solidFill = "00AAAA" s2.graphicalProperties.line.dashStyle = "sysDot" s2.graphicalProperties.line.width = 100050 # width in EMUs s2 = c1.series[2]
#画折线图 from openpyxl.chart import ( LineChart, Reference, PieChart, ) c1 = LineChart() c1.title = "2015年%s"%ws.rows[5][0].value #c1.style = 13 #c1.y_axis.title = 'Size' #c1.x_axis.title = 'Test Number' data = Reference(ws2, min_col=1, min_row=2, max_col=13, max_row=6) #print data c1.add_data(data, titles_from_data=True,from_rows=True) ws2.add_chart(c1, "A20") #画饼图,先新建一个表单 ws3 = wb2.create_sheet() _ = ws3.cell(column=1, row=1, value="项目") _ = ws3.cell(column=2, row=1, value="总计") for r in range(2,7): _ = ws3.cell(column=1, row=r, value=ws2.rows[r-1][0].value) _ = ws3.cell(column=2, row=r, value=ws2.rows[r-1][total_column-1].value) pie = PieChart()
from openpyxl import load_workbook from openpyxl.chart import BarChart, Reference, LineChart wb = load_workbook("sample.xlsx") ws = wb.active #B2:C11 까지의 데이터를 차트로 생성 # bar_value = Reference(ws, min_row=2,max_row=11,min_col=2,max_col=3) # bar_chart = BarChart() #차트의 종류 설정(Bar,Line,pie) # bar_chart.add_data(bar_value) # 차트 데이터 추가 # ws.add_chart(bar_chart,"E1") #차트 넣을 위치 #B1:C11까지의 데이터 line_value = Reference(ws, min_row=1, max_row=11, min_col=2, max_col=3) line_chart = LineChart() line_chart.add_data(line_value, titles_from_data=True) # 계열 > 영어, 수학 (제목에서 가져옴) line_chart.title = "성적표" line_chart.style = 10 # 미리 정의된 스타일을 적용, 사용자 개별 지정도 가능 line_chart.y_axis.title = "점수" # y 축의 제목 line_chart.x_axis.title = "번호" # X 축의 제목 ws.add_chart(line_chart, "E1") wb.save("sample_chart.xlsx")
def generate_excel(report, name, reporting_start_datetime_local, reporting_end_datetime_local, period_type): wb = Workbook() ws = wb.active ws.title = "CombinedEquipmentEnergyItem" # Row height ws.row_dimensions[1].height = 102 for i in range(2, 2000 + 1): ws.row_dimensions[i].height = 42 # Col width ws.column_dimensions['A'].width = 1.5 ws.column_dimensions['B'].width = 25.0 for i in range(ord('C'), ord('L')): ws.column_dimensions[chr(i)].width = 15.0 # Font name_font = Font(name='Arial', size=15, bold=True) title_font = Font(name='Arial', size=15, bold=True) table_fill = PatternFill(fill_type='solid', fgColor='1F497D') f_border = Border(left=Side(border_style='medium', color='00000000'), right=Side(border_style='medium', color='00000000'), bottom=Side(border_style='medium', color='00000000'), top=Side(border_style='medium', color='00000000')) b_border = Border(bottom=Side(border_style='medium', color='00000000'), ) b_c_alignment = Alignment(vertical='bottom', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) c_c_alignment = Alignment(vertical='center', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) b_r_alignment = Alignment(vertical='bottom', horizontal='right', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) # Img img = Image("excelexporters/myems.png") ws.add_image(img, 'A1') # Title ws['B3'].alignment = b_r_alignment ws['B3'] = 'Name:' ws['C3'].border = b_border ws['C3'].alignment = b_c_alignment ws['C3'] = name ws['D3'].alignment = b_r_alignment ws['D3'] = 'Period:' ws['E3'].border = b_border ws['E3'].alignment = b_c_alignment ws['E3'] = period_type ws['B4'].alignment = b_r_alignment ws['B4'] = 'Reporting Start Datetime:' ws['C4'].border = b_border ws['C4'].alignment = b_c_alignment ws['C4'] = reporting_start_datetime_local ws['D4'].alignment = b_r_alignment ws['D4'] = 'Reporting End Datetime:' ws['E4'].border = b_border ws['E4'].alignment = b_c_alignment ws['E4'] = reporting_end_datetime_local if "reporting_period" not in report.keys() or \ "names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0: filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename #################################################################################################################### 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 + ' ' + 'Reporting Period Consumption' current_row_number += 1 category = reporting_period_data['names'] ca_len = len(category) ws.row_dimensions[current_row_number].height = 60 ws['B' + str(current_row_number)].fill = table_fill ws['B' + str(current_row_number)].border = f_border col = 'C' for i in range(0, ca_len): ws[col + str(current_row_number)].fill = table_fill ws[col + str(current_row_number)].font = name_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = \ reporting_period_data['names'][i] + " " + reporting_period_data['energy_category_names'][i] + \ " (" + reporting_period_data['units'][i] + ")" col = chr(ord(col) + 1) current_row_number += 1 ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = '消耗' col = 'C' for i in range(0, ca_len): ws[col + str(current_row_number)].font = name_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = round( reporting_period_data['subtotals'][i], 2) col = chr(ord(col) + 1) current_row_number += 1 ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = 'Increment Rate' col = 'C' for i in range(0, ca_len): ws[col + str(current_row_number)].font = name_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = str( round(reporting_period_data['increment_rates'][i] * 100, 2)) + '%' \ if reporting_period_data['increment_rates'][i] is not None else '-' col = chr(ord(col) + 1) current_row_number += 2 category_dict = group_by_category( reporting_period_data['energy_category_names']) for category_dict_name, category_dict_values in category_dict.items(): ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = \ name + ' ' + category_dict_name + ' ' + \ '(' + reporting_period_data['units'][category_dict_values[0]] + ') by Energy Item' current_row_number += 1 table_start_row_number = current_row_number ws['B' + str(current_row_number)].fill = table_fill ws['B' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)].font = name_font ws['C' + str(current_row_number)].fill = table_fill ws['C' + str(current_row_number)].alignment = c_c_alignment ws['C' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)] = '消耗' current_row_number += 1 for i in category_dict_values: ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = \ reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")" ws['C' + str(current_row_number)].font = name_font ws['C' + str(current_row_number)].alignment = c_c_alignment ws['C' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)] = round( reporting_period_data['subtotals'][i], 3) current_row_number += 1 table_end_row_number = current_row_number - 1 pie = PieChart() pie.title = \ name + ' ' + category_dict_name + ' ' + \ '(' + reporting_period_data['units'][category_dict_values[0]] + ') by Energy Item' labels = Reference(ws, min_col=2, min_row=table_start_row_number + 1, max_row=table_end_row_number) pie_data = Reference(ws, min_col=3, min_row=table_start_row_number, max_row=table_end_row_number) pie.add_data(pie_data, titles_from_data=True) pie.set_categories(labels) pie.height = 6.6 pie.width = 9 s1 = pie.series[0] s1.dLbls = DataLabelList() s1.dLbls.showCatName = False s1.dLbls.showVal = True s1.dLbls.showPercent = True ws.add_chart(pie, 'D' + str(table_start_row_number)) if len(category_dict_values) < 4: current_row_number = current_row_number - len( category_dict_values) + 4 current_row_number += 1 #################################################################################################################### has_values_data = True has_timestamps_data = True if 'values' not in reporting_period_data.keys() or \ reporting_period_data['values'] is None or \ len(reporting_period_data['values']) == 0: has_values_data = False if 'timestamps' not in reporting_period_data.keys() or \ reporting_period_data['timestamps'] is None or \ len(reporting_period_data['timestamps']) == 0 or \ len(reporting_period_data['timestamps'][0]) == 0: has_timestamps_data = False if has_values_data and has_timestamps_data: ca_len = len(reporting_period_data['names']) time = reporting_period_data['timestamps'][0] 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 + ' ' + 'Detailed Data' 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)] = 'Datetime' col = 'C' for i in range(0, ca_len): ws[col + str(current_row_number)].fill = table_fill ws[col + str(current_row_number)].font = title_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = \ reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")" col = chr(ord(col) + 1) 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)] = 'Subtotal' col = 'C' for i in range(0, ca_len): ws[col + str(current_row_number)].font = title_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = round( reporting_period_data['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 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 Consumption - ' + \ reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")" labels = Reference(ws, min_col=2, min_row=table_start_row_number + 1, max_row=table_end_row_number) line_data = Reference(ws, min_col=3 + i, min_row=table_start_row_number, max_row=table_end_row_number) line.add_data(line_data, titles_from_data=True) line.set_categories(labels) line_data = line.series[0] line_data.marker.symbol = "circle" line_data.smooth = True line.x_axis.crosses = 'min' line.height = 8.25 line.width = format_time_width_number * len(time) if len( time) > min_len_number else min_width_number if line.width > 24: line.width = 24 line.dLbls = DataLabelList() line.dLbls.dLblPos = 't' line.dLbls.showVal = True line.dLbls.showPercent = False chart_col = 'B' chart_cell = chart_col + str(chart_start_row_number) chart_start_row_number += 6 ws.add_chart(line, chart_cell) #################################################################################################################### has_associated_equipment_flag = True if "associated_equipment" not in report.keys() or \ "energy_item_names" not in report['associated_equipment'].keys() or \ len(report['associated_equipment']["energy_item_names"]) == 0 \ or 'associated_equipment_names_array' not in report['associated_equipment'].keys() \ or report['associated_equipment']['associated_equipment_names_array'] is None \ or len(report['associated_equipment']['associated_equipment_names_array']) == 0 \ or len(report['associated_equipment']['associated_equipment_names_array'][0]) == 0: has_associated_equipment_flag = False if has_associated_equipment_flag: associated_equipment = report['associated_equipment'] ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + ' ' + 'Associated Equipment Data' current_row_number += 1 table_start_row_number = current_row_number ws.row_dimensions[current_row_number].height = 60 ws['B' + str(current_row_number)].fill = table_fill ws['B' + str(current_row_number)].font = name_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = 'Associated Equipment' ca_len = len(associated_equipment['energy_item_names']) for i in range(0, ca_len): row = chr(ord('C') + i) ws[row + str(current_row_number)].fill = table_fill ws[row + str(current_row_number)].font = name_font ws[row + str(current_row_number)].alignment = c_c_alignment ws[row + str(current_row_number)].border = f_border ws[row + str(current_row_number)] = \ reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")" associated_equipment_len = len( associated_equipment['associated_equipment_names_array'][0]) for i in range(0, associated_equipment_len): current_row_number += 1 row = str(current_row_number) ws['B' + row].font = title_font ws['B' + row].alignment = c_c_alignment ws['B' + row] = associated_equipment[ 'associated_equipment_names_array'][0][i] ws['B' + row].border = f_border for j in range(0, ca_len): col = chr(ord('C') + j) ws[col + row].font = 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']) file_name = (re.sub(r'[^A-Z]', '', ws.title)) + '_' parameters_ws = wb.create_sheet(file_name + 'Parameters') parameters_timestamps_data_max_len = \ get_parameters_timestamps_lists_max_len(list(parameters_data['timestamps'])) # Row height parameters_ws.row_dimensions[1].height = 102 for i in range(2, 7 + 1): parameters_ws.row_dimensions[i].height = 42 for i in range(8, parameters_timestamps_data_max_len + 10): parameters_ws.row_dimensions[i].height = 60 # Col width parameters_ws.column_dimensions['A'].width = 1.5 parameters_ws.column_dimensions['B'].width = 25.0 for i in range(3, 12 + parameters_names_len * 3): parameters_ws.column_dimensions[format_cell.get_column_letter( i)].width = 15.0 # Img img = Image("excelexporters/myems.png") parameters_ws.add_image(img, 'A1') # Title parameters_ws['B3'].alignment = b_r_alignment parameters_ws['B3'] = 'Name:' parameters_ws['C3'].border = b_border parameters_ws['C3'].alignment = b_c_alignment parameters_ws['C3'] = name parameters_ws['D3'].alignment = b_r_alignment parameters_ws['D3'] = 'Period:' parameters_ws['E3'].border = b_border parameters_ws['E3'].alignment = b_c_alignment parameters_ws['E3'] = period_type parameters_ws['B4'].alignment = b_r_alignment parameters_ws['B4'] = 'Reporting Start Datetime:' parameters_ws['C4'].border = b_border parameters_ws['C4'].alignment = b_c_alignment parameters_ws['C4'] = reporting_start_datetime_local parameters_ws['D4'].alignment = b_r_alignment parameters_ws['D4'] = 'Reporting End Datetime:' parameters_ws['E4'].border = b_border parameters_ws['E4'].alignment = b_c_alignment parameters_ws['E4'] = reporting_end_datetime_local parameters_ws_current_row_number = 6 parameters_ws['B' + str(parameters_ws_current_row_number)].font = title_font parameters_ws[ 'B' + str(parameters_ws_current_row_number)] = name + ' ' + 'Parameters' parameters_ws_current_row_number += 1 parameters_table_start_row_number = parameters_ws_current_row_number parameters_ws.row_dimensions[ parameters_ws_current_row_number].height = 80 parameters_ws_current_row_number += 1 table_current_col_number = 2 for i in range(0, parameters_names_len): if len(parameters_data['timestamps'][i]) == 0: continue col = format_cell.get_column_letter(table_current_col_number) parameters_ws[col + str(parameters_ws_current_row_number - 1)].fill = table_fill parameters_ws[col + str(parameters_ws_current_row_number - 1)].border = f_border col = format_cell.get_column_letter(table_current_col_number + 1) parameters_ws[col + str(parameters_ws_current_row_number - 1)].fill = table_fill parameters_ws[col + str(parameters_ws_current_row_number - 1)].border = f_border parameters_ws[col + str(parameters_ws_current_row_number - 1)].font = name_font parameters_ws[col + str(parameters_ws_current_row_number - 1)].alignment = c_c_alignment parameters_ws[col + str(parameters_ws_current_row_number - 1)] = parameters_data['names'][i] table_current_row_number = parameters_ws_current_row_number for j, value in enumerate(list(parameters_data['timestamps'][i])): col = format_cell.get_column_letter(table_current_col_number) parameters_ws[col + str(table_current_row_number)].border = f_border parameters_ws[col + str(table_current_row_number)].font = title_font parameters_ws[ col + str(table_current_row_number)].alignment = c_c_alignment parameters_ws[col + str(table_current_row_number)] = value col = format_cell.get_column_letter(table_current_col_number + 1) parameters_ws[col + str(table_current_row_number)].border = f_border parameters_ws[col + str(table_current_row_number)].font = title_font parameters_ws[ col + str(table_current_row_number)].alignment = c_c_alignment parameters_ws[col + str(table_current_row_number)] = round( parameters_data['values'][i][j], 2) table_current_row_number += 1 table_current_col_number = table_current_col_number + 3 ################################################################################################################ # parameters chart and parameters table ################################################################################################################ ws['B' + str(current_sheet_parameters_row_number)].font = title_font ws['B' + str( current_sheet_parameters_row_number)] = name + ' ' + 'Parameters' current_sheet_parameters_row_number += 1 chart_start_row_number = current_sheet_parameters_row_number col_index = 0 for i in range(0, parameters_names_len): if len(parameters_data['timestamps'][i]) == 0: continue line = LineChart() data_col = 3 + col_index * 3 labels_col = 2 + col_index * 3 col_index += 1 line.title = 'Parameters - ' + \ parameters_ws.cell(row=parameters_table_start_row_number, column=data_col).value labels = Reference(parameters_ws, min_col=labels_col, min_row=parameters_table_start_row_number + 1, max_row=(len(parameters_data['timestamps'][i]) + parameters_table_start_row_number)) line_data = Reference( parameters_ws, min_col=data_col, min_row=parameters_table_start_row_number, max_row=(len(parameters_data['timestamps'][i]) + parameters_table_start_row_number)) line.add_data(line_data, titles_from_data=True) line.set_categories(labels) line_data = line.series[0] line_data.marker.symbol = "circle" line_data.smooth = True line.x_axis.crosses = 'min' line.height = 8.25 line.width = 24 line.dLbls = DataLabelList() line.dLbls.dLblPos = 't' line.dLbls.showVal = False line.dLbls.showPercent = False chart_col = 'B' chart_cell = chart_col + str(chart_start_row_number) chart_start_row_number += 6 ws.add_chart(line, chart_cell) current_sheet_parameters_row_number = chart_start_row_number current_sheet_parameters_row_number += 1 #################################################################################################################### filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename
def place_in_excel(proj_list, data_key_list, total_data, q_masters_dict_list, q_masters_list, period): wb = Workbook() ws = wb.active ws.cell(row=1, column=1).value = 'Project' for i, proj_name in enumerate(proj_list): '''lists project names in row one''' ws.cell(row=1, column=i + 2).value = proj_name '''iterates through financial dictionary - placing financial data in ws''' for x, key in enumerate(data_key_list): try: if period == 'baseline': ws.cell(row=x + 2, column=i + 2).value = q_masters_dict_list[ q_masters_list[proj_name][2]][proj_name][key] if period == 'last': ws.cell(row=x + 2, column=i + 2).value = \ q_masters_dict_list[q_masters_list[proj_name][1]][proj_name][key] if period == 'latest': ws.cell(row=x + 2, column=i + 2).value = \ q_masters_dict_list[q_masters_list[proj_name][0]][proj_name][key] except KeyError: ws.cell(row=x + 2, column=i + 2).value = 0 '''places totals in final column. to note because this is a list and not a dictionary as for fin_data there is possibility that data could become unaligned. Whether changing the list of cells_to_capture causes them to become unaligned needs to be tested''' ws.cell(row=1, column=len(proj_list) + 2).value = 'Total' for i, values in enumerate(total_data): ws.cell(row=i + 2, column=len(proj_list) + 2).value = values '''places keys into the chart in the first column''' for i, key in enumerate(data_key_list): ws.cell(row=i + 2, column=1).value = key '''information on which projects are not included in totals''' ws.cell( row=1, column=len(proj_list) + 4).value = 'Projects that have been removed to avoid double counting' for i, project in enumerate(dont_double_count): ws.cell(row=i + 2, column=len(proj_list) + 4).value = project # ws.cell(row=1, column=len(proj_list)+6).value = 'Projects that have been removed to enable like for like' \ # 'comparison of totals' # for i, project in enumerate(like_for_like_totals): # ws.cell(row=i + 2, column=len(proj_list)+6).value = project '''data for overall chart. As above because this data is in a list - possibility of it being unaligned needs testing. not the best way of managing data flow, but working for now''' start_row = len(total_data) + 8 for x in range(0, int(len(total_data) / 4)): ws.cell(row=start_row, column=2, value=total_data[x]) start_row += 1 start_row = len(total_data) + 8 for x in range(int(len(total_data) / 4), (int(len(total_data) / 4) * 2)): ws.cell(row=start_row, column=3, value=total_data[x]) start_row += 1 start_row = len(total_data) + 8 for x in range((int(len(total_data) / 4) * 2), (int(len(total_data) / 4) * 3)): ws.cell(row=start_row, column=4, value=total_data[x]) start_row += 1 start_row = len(total_data) + 8 for x in range((int(len(total_data) / 4) * 3), int(len(total_data))): ws.cell(row=start_row, column=5, value=total_data[x]) start_row += 1 '''code was essentially a hack''' start_row = len(total_data) + 8 list_of_numbers = [0, len(capture_rdel), len(capture_rdel) * 2] total_sum = 0 for i in range(0, len(capture_rdel)): for x in list_of_numbers: total_sum = total_sum + total_data[x + i] ws.cell(row=start_row, column=6, value=total_sum) start_row += 1 total_sum = 0 a = len(total_data) + 7 ws.cell(row=a, column=2, value='RDEL') ws.cell(row=a, column=3, value='CDEL') ws.cell(row=a, column=4, value='Non-Gov') ws.cell(row=a, column=5, value='Income') ws.cell(row=a, column=6, value='Total') # ws.cell(row=a+1, column=1, value='17/18') #ws.cell(row=a + 1, column=1, value='18/19') ws.cell(row=a + 1, column=1, value='19/20') ws.cell(row=a + 2, column=1, value='20/21') ws.cell(row=a + 3, column=1, value='21/22') ws.cell(row=a + 4, column=1, value='22/23') ws.cell(row=a + 5, column=1, value='23/24') ws.cell(row=a + 6, column=1, value='24/25') ws.cell(row=a + 7, column=1, value='25/26') ws.cell(row=a + 8, column=1, value='26/27') ws.cell(row=a + 9, column=1, value='27/28') ws.cell(row=a + 10, column=1, value='28/29') ws.cell(row=a + 11, column=1, value='Unprofiled') '''this builds a very basic chart''' # TODO fix chart chart = LineChart() chart.title = 'Portfolio cost profile' chart.style = 4 chart.x_axis.title = 'Financial Year' chart.y_axis.title = 'Cost (£m)' chart.height = 15 # default is 7.5 chart.width = 26 # default is 15 '''styling chart''' # axis titles font = Font(typeface='Calibri') size = 1200 # 12 point size cp = CharacterProperties(latin=font, sz=size, b=True) # Bold pp = ParagraphProperties(defRPr=cp) rtp = RichText(p=[Paragraph(pPr=pp, endParaRPr=cp)]) chart.x_axis.title.tx.rich.p[0].pPr = pp chart.y_axis.title.tx.rich.p[0].pPr = pp # title size_2 = 1400 cp_2 = CharacterProperties(latin=font, sz=size_2, b=True) pp_2 = ParagraphProperties(defRPr=cp_2) rtp_2 = RichText(p=[Paragraph(pPr=pp_2, endParaRPr=cp_2)]) chart.title.tx.rich.p[0].pPr = pp_2 data = Reference(ws, min_col=2, min_row=51, max_col=5, max_row=61) cats = Reference(ws, min_col=1, min_row=52, max_row=61) chart.add_data(data, titles_from_data=True) chart.set_categories(cats) s3 = chart.series[0] s3.graphicalProperties.line.solidFill = "36708a" # dark blue s8 = chart.series[1] s8.graphicalProperties.line.solidFill = "68db8b" # green s9 = chart.series[2] s9.graphicalProperties.line.solidFill = "794747" # dark red s9 = chart.series[3] s9.graphicalProperties.line.solidFill = "73527f" # purple ws.add_chart(chart, "I52") return wb
def importHospitalCases(totalHospitalised, inWard, inIcu, dead, dates, hospital): select = 0 while select not in ['1', '2']: select = input('1. Save as...\n2. Back\n') if select == '1': filename = input('Save as: ') if select == '2': return wb = xl.Workbook() wb.save(f'./output/{filename}.xlsx') wb = xl.load_workbook(f'./output/{filename}.xlsx') sheet = wb.active header = ['Total hospitalised', 'In ward', 'In icu', 'Dead', 'Date'] # Write the headers for col in range(1, len(header) + 1): header_row = sheet.cell(1, col) header_row.value = header[col - 1] bottom_row = row = 2 for i in totalHospitalised: totalHospitalised_row = sheet.cell(row, 1) totalHospitalised_row.value = i row += 1 bottom_row += 1 row = 2 for i in inWard: inWard_row = sheet.cell(row, 2) inWard_row.value = i row += 1 row = 2 for i in inIcu: inIcu_row = sheet.cell(row, 3) inIcu_row.value = i row += 1 row = 2 for i in dead: dead_row = sheet.cell(row, 4) dead_row.value = i row += 1 row = 2 for i in dates: date_row = sheet.cell(row, 5) date_row.value = i row += 1 chart = LineChart() chart.title = f'Hospital data, {hospital}, {dates[0]} - {dates[-1]}' chart.style = 13 chart.y_axis.title = 'Infections' chart.x_axis.title = 'Date' values = Reference(sheet, min_col=1, min_row=1, max_col=4, max_row=bottom_row) dates = Reference(sheet, min_col=5, min_row=2, max_col=5, max_row=bottom_row) chart.add_data(values, titles_from_data=True) chart.set_categories(dates) sheet.add_chart(chart, "G2") wb.save(f'./output/{filename}.xlsx')
def generate_excel(report, name, reporting_start_datetime_local, reporting_end_datetime_local, period_type): wb = Workbook() ws = wb.active # Row height ws.row_dimensions[1].height = 102 for i in range(2, 2000 + 1): ws.row_dimensions[i].height = 42 # 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 = 25.0 for i in range(ord('C'), ord('L')): ws.column_dimensions[chr(i)].width = 15.0 # Font name_font = Font(name='Constantia', size=15, bold=True) title_font = Font(name='宋体', size=15, bold=True) data_font = Font(name='Franklin Gothic Book', size=11) table_fill = PatternFill(fill_type='solid', fgColor='1F497D') f_border = Border(left=Side(border_style='medium', color='00000000'), right=Side(border_style='medium', color='00000000'), bottom=Side(border_style='medium', color='00000000'), top=Side(border_style='medium', color='00000000')) b_border = Border(bottom=Side(border_style='medium', color='00000000'), ) b_c_alignment = Alignment(vertical='bottom', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) c_c_alignment = Alignment(vertical='center', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) b_r_alignment = Alignment(vertical='bottom', horizontal='right', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) c_r_alignment = Alignment(vertical='bottom', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) # Img img = Image("excelexporters/myems.png") img.width = img.width * 0.85 img.height = img.height * 0.85 # img = Image("myems.png") ws.add_image(img, 'B1') # Title ws.row_dimensions[3].height = 60 ws['B3'].font = name_font ws['B3'].alignment = b_r_alignment ws['B3'] = 'Name:' ws['C3'].border = b_border ws['C3'].alignment = b_c_alignment ws['C3'].font = name_font ws['C3'] = name ws['D3'].font = name_font ws['D3'].alignment = b_r_alignment ws['D3'] = 'Period:' ws['E3'].border = b_border ws['E3'].alignment = b_c_alignment ws['E3'].font = name_font ws['E3'] = period_type ws['F3'].font = name_font ws['F3'].alignment = b_r_alignment ws['F3'] = 'Date:' ws['G3'].alignment = b_c_alignment ws['G3'].font = name_font ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local ws.merge_cells("G3:H3") if "reporting_period" not in report.keys() or \ "names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0: filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename ################################################# reporting_period_data = report['reporting_period'] has_energy_data_flag = True if "names" not in reporting_period_data.keys() or \ reporting_period_data['names'] is None or \ len(reporting_period_data['names']) == 0: has_energy_data_flag = False if has_energy_data_flag: ws['B6'].font = title_font ws['B6'] = name + ' 报告期成本' category = reporting_period_data['names'] ca_len = len(category) ws.row_dimensions[7].height = 60 ws['B7'].fill = table_fill ws['B7'].border = f_border ws['B8'].font = title_font ws['B8'].alignment = c_c_alignment ws['B8'] = '成本' ws['B8'].border = f_border ws['B9'].font = title_font ws['B9'].alignment = c_c_alignment ws['B9'] = '单位面积能耗' ws['B9'].border = f_border ws['B10'].font = title_font ws['B10'].alignment = c_c_alignment ws['B10'] = '环比' ws['B10'].border = f_border col = 'B' for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + '7'].fill = table_fill ws[col + '7'].font = name_font ws[col + '7'].alignment = c_c_alignment ws[col + '7'] = reporting_period_data['names'][ i] + " (" + reporting_period_data['units'][i] + ")" ws[col + '7'].border = f_border ws[col + '8'].font = name_font ws[col + '8'].alignment = c_c_alignment ws[col + '8'] = round(reporting_period_data['subtotals'][i], 2) ws[col + '8'].border = f_border ws[col + '9'].font = name_font ws[col + '9'].alignment = c_c_alignment ws[col + '9'] = round( reporting_period_data['subtotals_per_unit_area'][i], 2) ws[col + '9'].border = f_border ws[col + '10'].font = name_font ws[col + '10'].alignment = c_c_alignment ws[col + '10'] = str(round(reporting_period_data['increment_rates'][i] * 100, 2)) + "%" \ if reporting_period_data['increment_rates'][i] is not None else "-" ws[col + '10'].border = f_border end_col = chr(ord(col) + 1) ws[end_col + '7'].fill = table_fill ws[end_col + '7'].font = name_font ws[end_col + '7'].alignment = c_c_alignment ws[end_col + '7'] = "总计 (" + reporting_period_data['total_unit'] + ")" ws[end_col + '7'].border = f_border ws[end_col + '8'].font = name_font ws[end_col + '8'].alignment = c_c_alignment ws[end_col + '8'] = round(reporting_period_data['total'], 2) ws[end_col + '8'].border = f_border ws[end_col + '9'].font = name_font ws[end_col + '9'].alignment = c_c_alignment ws[end_col + '9'] = round(reporting_period_data['total_per_unit_area'], 2) ws[end_col + '9'].border = f_border ws[end_col + '10'].font = name_font ws[end_col + '10'].alignment = c_c_alignment ws[end_col + '10'] = str(round(reporting_period_data['total_increment_rate'] * 100, 2)) + "%" \ if reporting_period_data['total_increment_rate'] is not None else "-" ws[end_col + '10'].border = f_border else: for i in range(6, 10 + 1): ws.row_dimensions[i].height = 0.1 ################################################# has_ele_peak_flag = True if "toppeaks" not in reporting_period_data.keys() or \ reporting_period_data['toppeaks'] is None or \ len(reporting_period_data['toppeaks']) == 0: has_ele_peak_flag = False if has_ele_peak_flag: ws['B12'].font = title_font ws['B12'] = name + ' 分时电耗' ws['B13'].fill = table_fill ws['B13'].font = name_font ws['B13'].alignment = c_c_alignment ws['B13'].border = f_border ws['C13'].fill = table_fill ws['C13'].font = name_font ws['C13'].alignment = c_c_alignment ws['C13'].border = f_border ws['C13'] = '分时电耗' ws['B14'].font = title_font ws['B14'].alignment = c_c_alignment ws['B14'] = '尖' ws['B14'].border = f_border ws['C14'].font = title_font ws['C14'].alignment = c_c_alignment ws['C14'].border = f_border ws['C14'] = round(reporting_period_data['toppeaks'][0], 2) ws['B15'].font = title_font ws['B15'].alignment = c_c_alignment ws['B15'] = '峰' ws['B15'].border = f_border ws['C15'].font = title_font ws['C15'].alignment = c_c_alignment ws['C15'].border = f_border ws['C15'] = round(reporting_period_data['onpeaks'][0], 2) ws['B16'].font = title_font ws['B16'].alignment = c_c_alignment ws['B16'] = '平' ws['B16'].border = f_border ws['C16'].font = title_font ws['C16'].alignment = c_c_alignment ws['C16'].border = f_border ws['C16'] = round(reporting_period_data['midpeaks'][0], 2) ws['B17'].font = title_font ws['B17'].alignment = c_c_alignment ws['B17'] = '谷' ws['B17'].border = f_border ws['C17'].font = title_font ws['C17'].alignment = c_c_alignment ws['C17'].border = f_border ws['C17'] = round(reporting_period_data['offpeaks'][0], 2) pie = PieChart() pie.title = name + ' 分时电耗' labels = Reference(ws, min_col=2, min_row=14, max_row=17) pie_data = Reference(ws, min_col=3, min_row=13, max_row=17) pie.add_data(pie_data, titles_from_data=True) pie.set_categories(labels) pie.height = 6.6 pie.width = 9 s1 = pie.series[0] s1.dLbls = DataLabelList() s1.dLbls.showCatName = False s1.dLbls.showVal = True s1.dLbls.showPercent = True ws.add_chart(pie, "D13") else: for i in range(12, 18 + 1): ws.row_dimensions[i].height = 0.1 ################################################ current_row_number = 19 has_subtotals_data_flag = True if "subtotals" not in reporting_period_data.keys() or \ reporting_period_data['subtotals'] is None or \ len(reporting_period_data['subtotals']) == 0: has_subtotals_data_flag = False if has_subtotals_data_flag: ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + ' 成本占比' current_row_number += 1 table_start_row_number = current_row_number ws['B' + str(current_row_number)].fill = table_fill ws['B' + str(current_row_number)].font = name_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)].fill = table_fill ws['C' + str(current_row_number)].font = name_font ws['C' + str(current_row_number)].alignment = c_c_alignment ws['C' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)] = '成本占比' current_row_number += 1 ca_len = len(reporting_period_data['names']) 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) 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 = 'D' + str(table_start_row_number) ws.add_chart(pie, table_cell) if ca_len < 4: current_row_number = current_row_number - ca_len + 4 else: for i in range(21, 29 + 1): current_row_number = 30 ws.row_dimensions[i].height = 0.1 ############################################### current_row_number += 1 has_detail_data_flag = True table_start_draw_flag = current_row_number + 1 if "timestamps" not in reporting_period_data.keys() or \ reporting_period_data['timestamps'] is None or \ len(reporting_period_data['timestamps']) == 0: has_detail_data_flag = False if has_detail_data_flag: reporting_period_data = report['reporting_period'] times = reporting_period_data['timestamps'] ca_len = len(report['reporting_period']['names']) 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 + ' 详细数据' table_start_row_number = (current_row_number + 1) + ca_len * 6 + real_timestamps_len * 7 current_row_number = table_start_row_number time = times[0] has_data = False if len(time) > 0: has_data = True if has_data: ws.row_dimensions[current_row_number].height = 60 ws['B' + str(current_row_number)].fill = table_fill ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)] = '日期时间' col = 'B' for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + str(current_row_number)].fill = table_fill ws[col + str(current_row_number)].font = title_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)] = reporting_period_data['names'][i] + \ " (" + reporting_period_data['units'][i] + ")" ws[col + str(current_row_number)].border = f_border end_col = chr(ord(col) + 1) ws[end_col + str(current_row_number)].fill = table_fill ws[end_col + str(current_row_number)].font = title_font ws[end_col + str(current_row_number)].alignment = c_c_alignment ws[end_col + str(current_row_number )] = "总计 (" + reporting_period_data['total_unit'] + ")" ws[end_col + str(current_row_number)].border = f_border current_row_number += 1 for i in range(0, len(time)): ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)] = time[i] ws['B' + str(current_row_number)].border = f_border col = 'B' every_day_total = 0 for j in range(0, ca_len): col = chr(ord('C') + j) ws[col + str(current_row_number)].font = title_font ws[col + str(current_row_number)].alignment = c_c_alignment value = round(reporting_period_data['values'][j][i], 2) every_day_total += value ws[col + str(current_row_number)] = value ws[col + str(current_row_number)].border = f_border end_col = chr(ord(col) + 1) ws[end_col + str(current_row_number)].font = title_font ws[end_col + str(current_row_number)].alignment = c_c_alignment ws[end_col + str(current_row_number)] = round( every_day_total, 2) ws[end_col + str(current_row_number)].border = f_border current_row_number += 1 table_end_row_number = current_row_number - 1 ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)] = '小计' ws['B' + str(current_row_number)].border = f_border col = 'B' for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + str(current_row_number)].font = title_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)] = round( reporting_period_data['subtotals'][i], 2) ws[col + str(current_row_number)].border = f_border # line line = LineChart() line.title = '报告期成本 - ' + ws.cell( column=3 + i, row=table_start_row_number).value 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 = 24 line.dLbls = DataLabelList() line.dLbls.dLblPos = 't' line.dLbls.showVal = True line.dLbls.showPercent = False chart_col = 'B' chart_cell = chart_col + str(table_start_draw_flag + 6 * i) ws.add_chart(line, chart_cell) end_col = chr(ord(col) + 1) ws[end_col + str(current_row_number)].font = title_font ws[end_col + str(current_row_number)].alignment = c_c_alignment ws[end_col + str(current_row_number)] = round( reporting_period_data['total'], 2) ws[end_col + str(current_row_number)].border = f_border current_row_number += 1 else: for i in range(30, 69 + 1): current_row_number = 70 ws.row_dimensions[i].height = 0.1 ########################################## 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 = 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 tablaExcel(nombreArchivo): wb = load_workbook( filename=nombreArchivo ) #para arbir uno existente, con los colores preseteados tabla = wb.active #Busuqeda del indice del cromosoma con mayor funcion objetivo de todas las generaciones. indiceMayorTotal = 0 mayorTotal = maximosFO[0] for i in range(1, ciclos): if (maximosFO[i] > mayorTotal): mayorTotal = maximosFO[i] indiceMayorTotal = i tabla["A1"] = "Maximo cromosoma: " tabla["C1"] = cromosomasMaxDecimal[indiceMayorTotal] tabla['A1'].font = Font(bold=True, size=12, color="A72D13") #Estilo visual tabla['C1'].font = Font(bold=True, size=12, color="A72D13") tabla.append([ "Generacion", "Minimo FO", "Maximo FO", "ValorDecimal", "Cromosoma Binario", "Promedio FO" ]) #titulos tabla['A2'].font = Font( bold=True) #Pongo los titulos en negrita. A modo visual tabla['B2'].font = Font(bold=True) tabla['C2'].font = Font(bold=True) tabla['D2'].font = Font(bold=True) tabla['E2'].font = Font(bold=True) tabla['F2'].font = Font(bold=True) for i in range(ciclos): #Datos de la corrida tabla.append([ i + 1, minimosFO[i], maximosFO[i], cromosomasMaxDecimal[i], str(cromosomasMax[i]), promediosFO[i] ]) #Generacion de grafico de lineas con minimos, maximos y promedios. grafica = LineChart() grafica.title = "Grafico de FO" grafica.style = 10 grafica.y_axis.title = "Valores FO" grafica.x_axis.title = "Ciclos" grafica.y_axis.scaling.min = 0 #Limites del eje y grafica.y_axis.scaling.max = 1 grafica.x_axis.scaling.min = 1 #Limites del eje x grafica.x_axis.scaling.max = ciclos data = Reference(tabla, min_col=2, min_row=2, max_col=3, max_row=ciclos + 2) #Rango que se grafica MaximoFO, MinimoFO data2 = Reference(tabla, min_col=6, min_row=2, max_row=ciclos + 2) #Columna de Promedio FO grafica.add_data(data, titles_from_data=True) grafica.add_data(data2, titles_from_data=True) finTabla = "B" + str( ciclos + 5 ) #Para ubicar el grafico donde termina la tabla (dependendiendo la cant de ciclos) tabla.add_chart(grafica, finTabla) #agregado y ubicacion del grafico wb.save(nombreArchivo)