def _set_swiss7_chart(self, workbook): ws = workbook['SWISS7_G'] ws_data = workbook['Data_category'] chart1 = BarChart() chart1.type = "col" # chart1.style = 12 chart1.width = 21 chart1.height = 12 chart1.grouping = "stacked" chart1.overlap = 100 chart1.y_axis.title = 'Volume du trafic en %' # chart1.x_axis.title = 'Heure' chart1.gapWidth = 2 data = Reference(ws_data, min_col=2, min_row=4, max_row=28, max_col=8) # cats = Reference(ws_data, min_col=2, min_row=4, max_row=28) chart1.add_data(data, titles_from_data=True) # chart1.set_categories(cats) chart1.shape = 4 chart1.legend = None chart1.series[0].graphicalProperties.solidFill = "00a9ff" chart1.series[1].graphicalProperties.solidFill = "bce273" chart1.series[2].graphicalProperties.solidFill = "ff708c" chart1.series[3].graphicalProperties.solidFill = "003366" chart1.series[4].graphicalProperties.solidFill = "ff00ff" chart1.series[5].graphicalProperties.solidFill = "ff3399" chart1.series[6].graphicalProperties.solidFill = "ff99cc" ws.add_chart(chart1, "A11") chart1 = BarChart() chart1.type = "col" # chart1.style = 12 chart1.width = 21 chart1.height = 12 chart1.grouping = "stacked" chart1.overlap = 100 chart1.y_axis.title = 'Volume du trafic en %' # chart1.x_axis.title = 'Heure' chart1.gapWidth = 2 data = Reference(ws_data, min_col=2, min_row=32, max_row=56, max_col=8) # cats = Reference(ws_data, min_col=2, min_row=32, max_row=56) chart1.add_data(data, titles_from_data=True) # chart1.set_categories(cats) chart1.shape = 4 chart1.legend = None chart1.series[0].graphicalProperties.solidFill = "00a9ff" chart1.series[1].graphicalProperties.solidFill = "bce273" chart1.series[2].graphicalProperties.solidFill = "ff708c" chart1.series[3].graphicalProperties.solidFill = "003366" chart1.series[4].graphicalProperties.solidFill = "ff00ff" chart1.series[5].graphicalProperties.solidFill = "ff3399" chart1.series[6].graphicalProperties.solidFill = "ff99cc" ws.add_chart(chart1, "A46")
def chart_bar(self, ws, destination_cell_coordinate, label_min_row, label_max_row, label_min_col, label_max_col, data_min_row, data_max_row, data_min_col, data_max_col, title='',from_rows=False, chart_height = default_chart_height, chart_width = default_chart_width, is_3d = False, add_chart = True, show_legend=True , **kw ): if is_3d: chart = BarChart3D() else: chart = BarChart() chart.height = chart_height # default is 7.5 chart.width = chart_width # default is 15 labels = Reference(ws, min_row=label_min_row, max_row=label_max_row, min_col=label_min_col, max_col=label_max_col,) data = Reference(ws, min_row=data_min_row, max_row=data_max_row, min_col=data_min_col, max_col=data_max_col,) chart.add_data(data, titles_from_data=False, from_rows=from_rows) chart.set_categories(labels) chart.title = title if not show_legend: chart.legend = None if add_chart: ws.add_chart(chart, destination_cell_coordinate) for seri in chart.series: seri.label = True return chart
def chartBreakdown(): '''Makes pie chart and bar chart for summary tab''' pie = PieChart() z = len(categories) data = Reference(Sum, min_col=9, min_row=2, max_row= z-1) labels = Reference(Sum, min_col=8, min_row=2, max_row= z-1) pie.add_data(data) pie.set_categories(labels) pie.title = 'Breakdown of Expenses' pie.width = 15.0 pie.height = 12.0 pie.legend.layout = Layout(manualLayout=ManualLayout(x=0.25, y=0.25, h=0.99, w=0.25)) Sum.add_chart(pie, 'A1') pie.dataLabels = DataLabelList() pie.dataLabels.showPercent = True bar = BarChart() barData1 = Reference(Sum, min_col=mNum+9, min_row=1, max_row=z-1) barData2 = Reference(Sum, min_col=mNum+12, min_row=1, max_row=z-1) bar.add_data(barData1, titles_from_data=True) bar.add_data(barData2, titles_from_data=True) bar.set_categories(labels) bar.title = 'Goal Comparison' bar.width = 2.0*z bar.height = 12.0 bar.legend.layout = Layout(manualLayout=ManualLayout(x=0.25, y=0.25, h=1.99, w=0.25)) Sum.add_chart(bar, 'A28')
def _draw_bar_chart(self, row_): """ 画垂直条形图 :param row_: 起始行 :return: None """ ws = self._wb['analysis'] bar = BarChart() bar.type = 'bar' bar.style = 11 bar.title = '失败接口概况图' bar.y_axis.title = '通过或失败用例个数' if row_ != 4: data = Reference(ws, min_col=2, min_row=3, max_row=row_ - 1, max_col=3) else: data = Reference(ws, min_col=2, min_row=3, max_row=row_, max_col=3) if row_ != 4: cats = Reference(ws, min_col=1, min_row=4, max_row=row_ - 1) else: cats = Reference(ws, min_col=1, min_row=4, max_row=row_) bar.add_data(data, titles_from_data=True) bar.set_categories(cats) bar.shape = 4 ws.add_chart(bar, "F12") bar.width = 30 bar.height = 0.5 * (row_ + 20) # 根据行数计算自适应条形图高度 self._log.info('已生成失败接口概况条形图.')
def createGraph(excel_file, df): sheet_name = "Data" sheet_name_graphs = "Graphs" writer = pd.ExcelWriter(excel_file, engine="openpyxl") book = load_workbook(excel_file) writer.book = book writer.sheets = dict((ws.title, ws) for ws in book.worksheets) ws_graph = writer.book.create_sheet(sheet_name_graphs) df.to_excel(writer, sheet_name=sheet_name) ws = writer.sheets[sheet_name] chart1 = BarChart() chart1.type = "col" chart1.style = 10 chart1.title = "ETL Results" chart1.y_axis.title = "Count" chart1.x_axis.title = "Programm" data = Reference(ws, min_col=2, min_row=1, max_row=7, max_col=3) cats = Reference(ws, min_col=1, min_row=2, max_row=7) chart1.add_data(data, titles_from_data=True) chart1.set_categories(cats) chart1.shape = 4 chart1.height = 12 chart1.width = 20 ws_graph.add_chart(chart1, "A10") writer.save()
def _do_chi_square_graph(self, ws, title, y_axis, x_axis, labels): chart = BarChart() chart.type = 'col' chart.style = 10 chart.height = 20 chart.width = 30 chart.title = title chart.y_axis.title = y_axis chart.x_axis.title = x_axis # mas uno por la fila de titulos chart_data = Reference(ws, min_col=2, min_row=1, max_row=len(labels) + 1, max_col=3) categories = Reference(ws, min_col=1, min_row=2, max_row=len(labels) + 1) chart.x_axis.delete = False chart.y_axis.delete = False chart.add_data(chart_data, titles_from_data=True) chart.set_categories(categories) ws.add_chart(chart, 'I2')
def make_bar(sheet, left_col, top_row, bot_row, title, x_title, y_title, print_cell, height, width): left_col = int(left_col) right_col = left_col + 1 top_row = int(top_row) bot_row = int(bot_row) title = str(title) print_cell = str(print_cell) height = float(height) width = float(width) bar = BarChart() bar.type = "col" bar.style = 10 bar.shape = 4 bar.title = title bar.y_axis.title = y_title bar.x_axis.title = x_title labels = Reference(sheet, min_col=left_col, max_col=left_col, min_row=top_row + 1, max_row=bot_row) data = Reference(sheet, min_col=right_col, max_col=right_col, min_row=top_row, max_row=bot_row) bar.add_data(data, titles_from_data=True) bar.set_categories(labels) bar.height = height bar.width = width sheet.add_chart(bar, print_cell)
def graph(purposes, averages, worksheet): #writes the titles in the spreadsheet worksheet.cell(row=1, column=1, value="purposes") worksheet.cell(row=1, column=2, value="avg_rate") #writes the purposes with the corresponding averages values in the spreadsheet for index, item in enumerate(purposes): worksheet.cell(row=(index + 2), column=1, value=item) worksheet.cell(row=(index + 2), column=2, value=averages[index]) #creates the Excel bar graph chart1 = BarChart() chart1.type = "col" chart1.style = 13 chart1.height = 20 chart1.width = 20 chart1.title = "Bar Chart" chart1.y_axis.title = 'Average Rates' chart1.x_axis.title = 'Purpose' data = Reference(worksheet, min_col=2, max_col=2, min_row=2, max_row=13) cats = Reference(worksheet, min_col=1, max_col=1, min_row=2, max_row=13) chart1.add_data(data) chart1.set_categories(cats) chart1.shape = 4 worksheet.add_chart(chart1, "E4") #adds the graph to the spreadsheet
def implementChart(listOfDate, Errordate, save_excel_file_today): wb_obj = openpyxl.load_workbook(save_excel_file_today) wb_obj.create_sheet('Chart view') sheet_obj = wb_obj["Chart view"] Drows = 1 Erows = 1 cell_obj = sheet_obj.cell(row=1, column=1) cell_obj.value = "listOfDate" cell_obj = sheet_obj.cell(row=1, column=2) cell_obj.value = "Error_data" for i in listOfDate: cell_obj = sheet_obj.cell(row=Drows + 1, column=1) cell_obj.value = i Drows += 1 for i in Errordate: cell_obj = sheet_obj.cell(row=Erows + 1, column=2) cell_obj.value = i Erows += 1 dates = Reference(sheet_obj, min_col=1, min_row=2, max_col=1, max_row=16) values = Reference(sheet_obj, min_col=2, min_row=2, max_col=2, max_row=16) chart = BarChart() chart.add_data(values) chart.set_categories(dates) chart.height = 15 # default is 7.5 chart.width = 30 chart.dataLabels = DataLabelList() chart.dataLabels.showVal = True chart.title = " 15 Day's Error Count " chart.x_axis.title = " Dates " chart.y_axis.title = " Number of Error " sheet_obj.add_chart(chart, "A1") wb_obj.save(save_excel_file_today)
def draw_bar_chart(writer, max_row): sheet = writer.sheets['死亡率'] date = sheet['A2'].value # 初始化我们的bar chart chart = BarChart() # 指定bar chart 的数据范围 chart_data = Reference(sheet, min_col=6, max_col=6, min_row=2, max_row=max_row-1) chart_series = Series(chart_data, title='截止{}死亡率'.format(date)) chart_series.graphicalProperties.solidFill = '5DD092' # Silver Tree # 指定x轴 x_axis_data = Reference(sheet, min_col=3, max_col=3, min_row=2, max_row=max_row-1) # chart 添加到 sheet里 chart.append(chart_series) chart.set_categories(x_axis_data) # 设置chart的样式 chart.height, chart.width = 14, 21 chart.title, chart.y_axis.title = '主要疫情地区死亡率', '死亡率' chart.y_axis.number_format = '0.0%' chart.legend.position = 't' # 画平均死亡率横线line chart line_chart = LineChart() line_data = Reference(sheet, min_col=7, max_col=7, min_row=2, max_row=max_row - 1) line_series = Series(line_data, title='除湖北外死亡率') line_chart.append(line_series) # 合并图表 chart += line_chart # 添加图表并保存 sheet.add_chart(chart, 'I1') writer.save()
def build_advanced_report_for_ticker(self, wb: openpyxl.Workbook, scenario_result, ticker: str, scenario): periods = scenario["total_periods"] result = scenario_result.get(ticker) sheet = wb.create_sheet(ticker) total_revenue_hist = result["total_revenue_hist"][::-1] total_revenue_growth = result["total_revenue_growth"][::-1] total_revenue_growth_mean = result["total_revenue_growth_mean"] hist_data_len = len(total_revenue_hist) categories = [x for x in range(-hist_data_len + 1, periods + 1)] sheet.append([" "] + categories) sheet.append(["total_revenue"] + total_revenue_hist + result["total_revenue_forward"]) tr_growth_rate_forward = [] revenue = [total_revenue_hist[-1]] + result["total_revenue_forward"] for i in range(len(revenue) - 1): tr_growth_rate_forward.append(revenue[i + 1] / revenue[i] - 1) sheet.append(["total_revenue_growth"] + total_revenue_growth + tr_growth_rate_forward) sheet.append([""]) sheet.append(["prices_t"] + result["prices_t"]) chart = LineChart() chart.y_axis.title = "Total Revenue" chart.x_axis.title = "Year" chart.height = 12 chart.width = 24 revenue_data = Reference(sheet, min_col=2, min_row=2, max_row=2, max_col=hist_data_len + periods + 1) cat_data = Reference(sheet, min_col=2, min_row=1, max_row=1, max_col=hist_data_len + periods + 1) chart.add_data(revenue_data, from_rows=True) sheet.add_chart(chart, "F8") chart.set_categories(cat_data) bar_chart = BarChart() bar_chart.y_axis.title = "Total Revenue Growth Rate" bar_chart.x_axis.title = "Year" bar_chart.height = 12 bar_chart.width = 24 growth_data = Reference(sheet, min_col=2, min_row=3, max_row=3, max_col=hist_data_len + periods + 1) bar_chart.add_data(growth_data, from_rows=True) bar_chart.set_categories(cat_data) sheet.add_chart(bar_chart, "S8") for i in range(scenario["total_periods"]): pass
def _set_cv_lv_chart(self, workbook): ws = workbook['CV_LV'] ws_data = workbook['Data_day'] chart1 = BarChart() chart1.type = "col" chart1.width = 19 chart1.height = 8 chart1.style = 10 chart1.y_axis.title = 'Vehicules à moteur en % du TJMO de la section' chart1.x_axis.title = "Selon l'heure de la journée" chart1.gapWidth = 0 #chart1.legend = None data = Reference(ws_data, min_col=11, min_row=4, max_row=28, max_col=11) cats = Reference(ws, min_col=1, min_row=5, max_row=28) chart1.add_data(data, titles_from_data=True) chart1.set_categories(cats) # chart1.shape = 4 s = chart1.series[0] s.graphicalProperties.line.solidFill = "000000" s.graphicalProperties.solidFill = "66ffb2" chart2 = LineChart() data = Reference(ws_data, min_col=11, min_row=34, max_row=58, max_col=11) chart2.add_data(data, titles_from_data=True) data = Reference(ws_data, min_col=11, min_row=65, max_row=89, max_col=11) chart2.add_data(data, titles_from_data=True) # chart2.style = 12 s = chart2.series[0] s.graphicalProperties.line.solidFill = "3333ff" s.smooth = False s = chart2.series[1] s.graphicalProperties.line.solidFill = "ff3333" s.graphicalProperties.line.dashStyle = "sysDash" s.smooth = False chart1 += chart2 ws.add_chart(chart1, "A15")
def draw_chart(input_wb, max_row): # 拿到我们需要操作的sheet top10 top_10_sheet = input_wb.worksheets[-1] top_10_sheet.sheet_view.zoomScale = 200 # 初始化chart bar_chart = BarChart() line_chart = LineChart() pie_chart = PieChart() # 生成数据 bar_chart_data = Reference(top_10_sheet, min_col=4, max_col=7, min_row=1, max_row=max_row) line_chart_data = Reference(top_10_sheet, min_col=4, max_col=4, min_row=1, max_row=max_row) pie_chart_data = Reference(top_10_sheet, min_col=4, max_col=4, min_row=1, max_row=max_row) # 指定chart的x_axis x_data = Reference(top_10_sheet, min_col=2, max_col=2, min_row=2, max_row=max_row) # 设置chart样式 bar_chart.height, bar_chart.width = 7, 15 line_chart.height, line_chart.width = 7, 15 pie_chart.height, pie_chart.width = 7, 15 bar_chart.title, bar_chart.y_axis.title, bar_chart.x_axis.title = 'top10', '人数', '国家' # bar_chart.y_axis.scaling.max = 5000000 # 把数据添加进chart bar_chart.add_data(bar_chart_data, titles_from_data=True) line_chart.add_data(line_chart_data, titles_from_data=True) bar_chart.set_categories(x_data) line_chart.set_categories(x_data) pie_chart.add_data(pie_chart_data, titles_from_data=True) pie_chart.set_categories(x_data) pie_chart.dataLabels = DataLabelList() pie_chart.dataLabels.showVal = True pie_chart.dataLabels.showLegendKey = True # 把chart添加到sheet # top_10_sheet.add_chart(bar_chart, 'I1') # top_10_sheet.add_chart(pie_chart, 'I11') bar_chart += line_chart top_10_sheet.add_chart(bar_chart, 'I1') # 保存我们的workbook input_wb.save('./excel_files/report_chart.xlsx')
def createChart(ws): chart1 = BarChart() chart1.type = "col" chart1.style = 10 chart1.title = "Violation occurrence per area" chart1.y_axis.title = 'Violation occurrence' chart1.x_axis.title = 'Violation and Location' cats = Reference(ws, min_col=1, max_col=2, min_row=2, max_row=ws.max_row) data = Reference(ws, min_col=5, max_col=5, min_row=1, max_row=ws.max_row) chart1.add_data(data, titles_from_data=True) chart1.legend = None chart1.set_categories(cats) chart1.width = 30 chart1.height = 15 ws.add_chart(chart1, "K2")
def open_close_delta(): """Create a sheet for Open/Close delta.""" ws2 = Workbook.wb.create_sheet('Open Close Delta') mysel, connection = DB_Connection.connect_db() cursor = connection.execute('SELECT * FROM {}'.format(stock)) names = list(map(lambda x: x[0], cursor.description)) idx = 1 for i in range(6): if i == 0 or i == 1 or i == 4: ws2.cell(row=1, column=idx).value = names[i] idx += 1 counter = 1 """Write relevant data to Excel.""" for i, row in enumerate(mysel): post = row[0], row[1], row[4] counter += 1 ws2.append(post) Workbook.wb.save(Workbook.wbook_name) """Use pandas to create a new field, Open/Close Delta.""" ws2['D1'] = 'Delta' df = pd.read_excel(Workbook.wbook_name, 'Open Close Delta') df['Delta'] = df['OpenPrice'] - df['ClosePrice'] for dl in range(2, counter+1): ws2['D{}'.format(dl)] = float(df['Delta'][dl-2]) """Plot delta as bar chart.""" ws2.sheet_view.zoomScale = 85 bc_three_d = BarChart() bc_three_d.type = 'col' bc_three_d .style = 10 bc_three_d .title = 'Open/Close Delta for Past 30 Trading Days' bc_three_d.y_axis.title = 'Price' bc_three_d.x_axis.title = 'Date' data = Reference(ws2, min_col=4, min_row=counter-29, max_row=counter, max_col=4) cats = Reference(ws2, min_col=1, min_row=counter-28, max_row=counter) bc_three_d.add_data(data, titles_from_data=True) bc_three_d.set_categories(cats) bc_three_d.height = 20 bc_three_d.width = 30 bc_three_d.legend = None ws2.add_chart(bc_three_d, 'F2') Workbook.wb.save(Workbook.wbook_name)
def genChart(self): ''' Generates a bar graph to visually represent the data. ''' chart = BarChart() chart.type = "col" chart.style = 1 chart.title = None chart.y_axis.title = 'Force' chart.x_axis.title = 'Operation' chartData = Reference(self.workSheet, min_col=1, \ min_row=2, max_row=len(self.forceDataFrame)+1, max_col=1) chart.add_data(chartData, titles_from_data=None) chart.legend = None chart.height = 15 chart.width = 30 self.workSheet.add_chart(chart, "G5")
def create_excel(data, filename): wb = Workbook(write_only=True) ws = wb.create_sheet('ChiCuadrado') max_row = len(data.labels) + 1 labels_xls = ('Intervalo', 'Frecuencia Observada', 'Frecuencia Esperada', 'Estadístico de prueba (C)', 'Estadístico de prueba acumulado (CA)') ws.append(labels_xls) if type(data.freq_observ[0]) is tuple: for i in range(len(data.labels)): ws.append( (str(data.labels[i][0][0]), data.freq_observ[i][1], data.freq_esperadas[i], data.chi_2[i], data.chi_2_ac[i])) else: for i in range(len(data.labels)): ws.append( (str(data.labels[i][0]) + ' - ' + str(data.labels[i][1]), data.freq_observ[i], data.freq_esperadas[i], data.chi_2[i], data.chi_2_ac[i])) chart = BarChart() chart.type = 'col' chart.style = 10 chart.height = 20 chart.width = 30 chart.title = 'Prueba Chi Cuadrado' chart.y_axis.title = 'Frecuencia' chart.x_axis.title = 'Intervalo' chart_data = Reference(ws, min_col=2, min_row=1, max_row=max_row, max_col=3) categories = Reference(ws, min_col=1, min_row=2, max_row=max_row) chart.x_axis.delete = False chart.y_axis.delete = False chart.add_data(chart_data, titles_from_data=True) chart.set_categories(categories) ws.add_chart(chart, 'I2') wb.save(f'exports/{filename}.xlsx')
def create_bar_chart(x_cells, y_cells, title, range=None, height=10, width=20): """ @fn create_bar_chart() @brief @param x_cells カテゴリ参照範囲(Reference) @param y_cells データ参照範囲(Reference) @param title タイトル @param range 値域 @param height グラフの高さ @param width グラフの幅 @retval chart グラフ """ chart = BarChart() chart.title = title chart.style = 2 chart.height = height chart.width = width chart.legend = None chart.add_data(y_cells) chart.set_categories(x_cells) return chart
def A(): sheetA = wb.create_sheet('전체 출원동향', 0) A그래프data = Data.전체출원동향() for r in dataframe_to_rows(A그래프data, index=False, header=True): sheetA.append(r) sheetA.insert_cols(2) for row, cellobj in enumerate(list(sheetA.columns)[1]): n = '=right(A%d,2)' % (row + 1) cellobj.value = n chartA1 = BarChart() dataA1 = Reference(sheetA, min_col=3, min_row=1, max_row=21) catsA1 = Reference(sheetA, min_col=2, min_row=2, max_row=21) chartA1.add_data(dataA1, titles_from_data=True) chartA1.set_categories(catsA1) chartA1.y_axis.majorGridlines = None chartA2 = LineChart() dataA2 = Reference(sheetA, min_col=4, min_row=1, max_row=21) chartA2.add_data(dataA2, titles_from_data=True) chartA2.y_axis.majorGridlines = None chartA2.y_axis.axId = 2000 # y축 위치 변경 chartA2.y_axis.crosses = 'max' # 그래프 합치기 chartA1 += chartA2 chartA1.width = 15 chartA1.height = 10 chartA1.legend.position = 't' chartA1.graphical_properties = GraphicalProperties( ln=LineProperties(noFill=True)) # 테두리 제거 sheetA.add_chart(chartA1, 'F2') global savepath savepath = Data.Save()
def generate_excel(report, name, reporting_start_datetime_local, reporting_end_datetime_local, period_type): wb = Workbook() ws = wb.active # Row height ws.row_dimensions[1].height = 118 for i in range(2, 6 + 1): ws.row_dimensions[i].height = 30 ws.row_dimensions[7].height = 60 ws.row_dimensions[3].height = 50 # Col width ws.column_dimensions['A'].width = 1.5 ws.column_dimensions['B'].width = 20.0 for i in range(ord('C'), ord('C') + 16): 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") 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~ table_data ################################################ has_data_flag = True 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: has_data_flag = False if "timestamps" not in reporting_period_data.keys() or \ reporting_period_data['timestamps'] is None or \ len(reporting_period_data['timestamps']) == 0: has_data_flag = False if "values" not in reporting_period_data.keys() or \ reporting_period_data['values'] is None or \ len(reporting_period_data['values']) == 0: has_data_flag = False ca = reporting_period_data['names'] ca_len = len(ca) temp_max_row = 0 times = reporting_period_data['timestamps'] if has_data_flag: ws['B6'].font = title_font ws['B6'] = name + ' 趋势' ws['B7'].fill = table_fill ws['B7'].border = f_border ws['B7'].alignment = c_c_alignment ws['B7'] = '时间' time = times[0] has_data = False max_row = 0 if len(time) > 0: has_data = True max_row = 8 + len(time) print("max_row", max_row) temp_max_row = max_row if has_data: for i in range(0, len(time)): col = 'B' row = str(8 + i) # col = chr(ord('B') + i) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment ws[col + row] = time[i] ws[col + row].border = f_border for i in range(0, ca_len): # 38 title col = chr(ord('C') + i) ws[col + '7'].fill = table_fill ws[col + '7'].font = title_font ws[col + '7'].alignment = c_c_alignment ws[col + '7'] = reporting_period_data['names'][i] ws[col + '7'].border = f_border # 39 data time = times[i] time_len = len(time) for j in range(0, time_len): row = str(8 + j) # col = chr(ord('B') + i) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment ws[col + row] = round( reporting_period_data['values'][i][j], 0) ws[col + row].border = f_border # bar # 39~: bar bar = BarChart() labels = Reference(ws, min_col=2, min_row=8, max_row=max_row + 1) bar_data = Reference(ws, min_col=3 + i, min_row=7, max_row=max_row + 1) # openpyxl bug bar.add_data(bar_data, titles_from_data=True) bar.set_categories(labels) bar.height = 5.25 # cm 1.05*5 1.05cm = 30 pt bar.width = 36 # pie.title = "Pies sold by category" bar.dLbls = DataLabelList() # bar.dLbls.showCatName = True # label show bar.dLbls.showVal = True # val show bar.dLbls.showPercent = True # percent show # s1 = CharacterProperties(sz=1800) # font size *100 chart_col = chr(ord('B')) chart_cell = chart_col + str(max_row + 2 + 10 * i) print("chart_cell", chart_cell) ws.add_chart(bar, chart_cell) else: pass for i in range(8, temp_max_row + 1 + 1): ws.row_dimensions[i].height = 20 filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename
def lesfimi_excel_entire_country_stats(): ref_values = { 1: (20, 55, 75), 2: (40, 85, 100), 3: (55, 100, 120), 4: (80, 120, 145), 5: (90, 140, 160), 6: (105, 155, 175), 7: (120, 165, 190), 8: (130, 180, 210), 9: (140, 180, 210), 10: (145, 180, 210), } response = HttpResponse( content_type= 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') response[ 'Content-Disposition'] = 'attachment; filename=Lesfimi - Allt Landið.xlsx' wb = openpyxl.Workbook() ws = wb.get_active_sheet() wb.remove_sheet(ws) tests = ( ('b{}_LF_mai17', 'Maí 2017'), ('b{}_LF_jan17', 'Janúar 2017'), ('{}b_LF_sept', 'September 2016'), ) for test in tests: identifier = test[0] title = test[1] ws = wb.create_sheet(title=title) ws['A1'] = 'Árgangur' ws['B1'] = 'Fjöldi nemenda' ws['C1'] = 'Fjöldi sem þreytti próf' ws['D1'] = 'Hlutfall sem þreytti próf' ws['E1'] = 'Hlutfall sem nær 90% viðmiðum' ws['F1'] = 'Hlutfall sem nær 50% viðmiðum' ws['G1'] = 'Hlutfall sem nær 25% viðmiðum' index = 2 errors = [] for year in range(1, 11): ws['A' + str(index)] = year survey = Survey.objects.filter( identifier=identifier.format(year)).first() studentgroups = StudentGroup.objects.filter( student_year=year).all() this_year_result = { 'students': 0, 'students_who_took_test': 0, 'students_over_25pct': 0, 'students_over_50pct': 0, 'students_over_90pct': 0, } for studentgroup in studentgroups: this_year_result['students'] += studentgroup.students.all( ).count() groupsurveys = GroupSurvey.objects.filter( studentgroup=studentgroup, survey=survey) if groupsurveys.all().count() > 1: errors.append( 'sama próf skráð {} sinnum fyrir {} í {}'.format( groupsurveys.all().count(), studentgroup.name, studentgroup.school.name)) for groupsurvey in groupsurveys.all(): for student in studentgroup.students.all(): surveyresults = SurveyResult.objects.filter( survey=groupsurvey, student=student) if surveyresults.all().count() > 1: errors.append( '{} niðurstöður í sama prófi skráðar fyrir nemanda {} í bekk {} í {}' .format( surveyresults.all().count(), student.ssn, studentgroup.name, studentgroup.school.name, )) for surveyresult in surveyresults.all(): try: survey_student_result = surveyresult.calculated_results( ) if not survey_student_result[0] == '': this_year_result[ 'students_who_took_test'] += 1 if int(survey_student_result[0] ) >= ref_values[year][2]: this_year_result[ 'students_over_25pct'] += 1 if int(survey_student_result[0] ) >= ref_values[year][1]: this_year_result[ 'students_over_50pct'] += 1 if int(survey_student_result[0] ) >= ref_values[year][0]: this_year_result[ 'students_over_90pct'] += 1 except: pass ws['B' + str(index)] = this_year_result['students'] ws['C' + str(index)] = this_year_result['students_who_took_test'] if this_year_result['students'] > 0 and this_year_result[ 'students_who_took_test'] > 0: ws['D' + str(index)] = (this_year_result['students_who_took_test'] / this_year_result['students']) * 100 pct_over_90pct = ( this_year_result['students_over_90pct'] / this_year_result['students_who_took_test']) * 100 ws['E' + str(index)] = pct_over_90pct pct_over_50pct = ( this_year_result['students_over_50pct'] / this_year_result['students_who_took_test']) * 100 ws['F' + str(index)] = pct_over_50pct pct_over_25pct = ( this_year_result['students_over_25pct'] / this_year_result['students_who_took_test']) * 100 ws['G' + str(index)] = pct_over_25pct else: ws['D' + str(index)] = 0 ws['E' + str(index)] = 0 ws['F' + str(index)] = 0 ws['G' + str(index)] = 0 index += 1 dims = {} for row in ws.rows: for cell in row: if cell.value: dims[cell.column] = max( (dims.get(cell.column, 0), len(str(cell.value)))) for col, value in dims.items(): ws.column_dimensions[col].width = int(value) + 2 chart = AreaChart() chart.title = "Lesfimi í {} - Allt landið".format(title) chart.style = 10 chart.width = 40 chart.height = 20 chart.layout = Layout(ManualLayout( xMode="edge", yMode="edge", )) chart.x_axis.title = 'Árgangur' chart.y_axis.title = 'Prósent' chart.y_axis.scaling.min = 0 chart.y_axis.scaling.max = 100 cats = Reference(ws, min_col=1, min_row=2, max_row=index - 1) data = Reference(ws, min_col=5, min_row=1, max_col=7, max_row=index) chart.add_data(data, titles_from_data=True) chart.set_categories(cats) bchart = BarChart() bchart.title = "Hlutfall nemenda sem þreyttu próf" bchart.style = 10 bchart.width = 20 bchart.height = 10 bchart.x_axis.title = 'Árgangur' bchart.y_axis.title = 'Prósent' bchart.y_axis.scaling.min = 0 bchart.y_axis.scaling.max = 100 bdata = Reference(ws, min_col=4, max_col=4, min_row=2, max_row=index) bchart.add_data(bdata) bchart.legend = None bchart.set_categories(cats) ws.add_chart(bchart, "I1") if index > 20: ws.add_chart(chart, "A" + str(index + 2)) else: ws.add_chart(chart, "A22") if errors: ws = wb.create_sheet(title='Villur') index = 1 for error in errors: ws['A' + str(index)] = "ATH: " + error ws['A' + str(index)].fill = PatternFill(start_color='ff0000', end_color='ff0000', fill_type='solid') ws.merge_cells('A' + str(index) + ':F' + str(index)) index += 1 wb.save(filename='/tmp/test.xlsx') # wb.save(response) return response
data_clean.append(i) try: jumlah_penduduk = data_clean[3] luas_wilayah_per100 = data_clean[2] / 100 kepadatan = jumlah_penduduk / luas_wilayah_per100 data_clean.append(float(kepadatan)) except: pass ws.append(data_clean) index += 1 len_row = len(data_clean) #beri judul untuk E1 ws['E1'] = "Kepadatan Penduduk" chart1 = BarChart() chart1.type = "col" chart1.style = 3 chart1.title = "Bar Chart" chart1.y_axis.title = "Kepadatan per 100m2" chart1.x_axis.title = "Kecamatan" data = Reference(ws, min_col=5, min_row=1, max_row=index, max_col=5) cats = Reference(ws, min_col=2, min_row=2, max_row=index) chart1.height = 10 chart1.width = 30 chart1.add_data(data, titles_from_data=True) chart1.set_categories(cats) ws.add_chart(chart1, "G2") wb.save("barPenduduk.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 = 118 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('I')): ws.column_dimensions[chr(i)].width = 25.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 = 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.merge_cells("G3:J3") for i in range(ord('G'), ord('K')): ws[chr(i) + '3'].border = b_border ws['G3'].alignment = b_c_alignment ws['G3'].font = name_font ws['G3'] = 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['units'][i] + ")" col = chr(ord(col) + 1) 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)] = '吨标准煤 (基线-实际) (TCE)' col = chr(ord(col) + 1) 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)] = '吨二氧化碳排放 (基线-实际) (TCO2E)' 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_saving'][i], 2) col = chr(ord(col) + 1) ws[col + str(current_row_number)].font = name_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = round(reporting_period_data['total_in_kgce_saving'], 2) col = chr(ord(col) + 1) ws[col + str(current_row_number)].font = name_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = round(reporting_period_data['total_in_kgco2e_saving'], 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_saving'][i], 2) col = chr(ord(col) + 1) ws[col + str(current_row_number)].font = name_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = round(reporting_period_data['total_in_kgco2e_per_unit_area_saving'], 2) col = chr(ord(col) + 1) ws[col + str(current_row_number)].font = name_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = round(reporting_period_data['total_in_kgce_per_unit_area_saving'], 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_saving'][i] * 100, 2)) + '%' \ if reporting_period_data['increment_rates_saving'][i] is not None else '-' col = chr(ord(col) + 1) ws[col + str(current_row_number)].font = name_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = str( round(reporting_period_data['increment_rate_in_kgce_saving'] * 100, 2)) + '%' \ if reporting_period_data['increment_rate_in_kgce_saving'] is not None else '-' col = chr(ord(col) + 1) ws[col + str(current_row_number)].font = name_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = str( round(reporting_period_data['increment_rate_in_kgco2e_saving'] * 100, 2)) + '%' \ if reporting_period_data['increment_rate_in_kgco2e_saving'] is not None else '-' col = chr(ord(col) + 1) current_row_number += 2 ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + ' 吨标准煤(TCE)占比' current_row_number += 1 table_start_row_number = current_row_number chart_start_row_number = current_row_number ws['B' + str(current_row_number)].fill = table_fill ws['C' + str(current_row_number)].fill = table_fill ws['C' + str(current_row_number)].font = name_small_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)] = '吨标准煤(TCE)占比' current_row_number += 1 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)].border = f_border ws['B' + str(current_row_number)] = reporting_period_data['names'][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_in_kgce_saving'][i], 2) current_row_number += 1 table_end_row_number = current_row_number - 1 if ca_len < 4: current_row_number = current_row_number - ca_len + 4 current_row_number += 1 pie = PieChart() pie.title = '吨标准煤(TCE)占比' 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(chart_start_row_number)) ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + ' 吨二氧化碳排放(TCO2E)占比' current_row_number += 1 table_start_row_number = current_row_number chart_start_row_number = current_row_number ws['B' + str(current_row_number)].fill = table_fill ws['C' + str(current_row_number)].fill = table_fill ws['C' + str(current_row_number)].font = name_small_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)] = '吨二氧化碳排放(TCO2E)占比' current_row_number += 1 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)].border = f_border ws['B' + str(current_row_number)] = reporting_period_data['names'][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_in_kgco2e_saving'][i], 2) current_row_number += 1 table_end_row_number = current_row_number - 1 if ca_len < 4: current_row_number = current_row_number - ca_len + 4 current_row_number += 1 pie = PieChart() pie.title = '吨二氧化碳排放(TCO2E)占比' 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(chart_start_row_number)) ############################################# has_values_saving_data = True has_timestamps_data = True if 'values_saving' not in reporting_period_data.keys() or \ reporting_period_data['values_saving'] is None or \ len(reporting_period_data['values_saving']) == 0: has_values_saving_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_saving_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 * 5 table_start_row_number = current_row_number 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_saving'][j][i], 2) \ if reporting_period_data['values_saving'][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_saving'][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.25 bar.width = format_time_width_number * len(time) if len(time) > min_len_number else min_width_number bar.dLbls = DataLabelList() bar.dLbls.showVal = True bar.dLbls.showPercent = True chart_col = 'B' chart_cell = chart_col + str(chart_start_row_number) chart_start_row_number += 5 ws.add_chart(bar, chart_cell) filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename
def generate_excel(report, name, reporting_start_datetime_local, reporting_end_datetime_local, period_type): wb = Workbook() ws = wb.active # Row height ws.row_dimensions[1].height = 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~11 table_data # Total: 6 rows # if has not energy data: set low height for rows ################################################# reporting_period_data = report['reporting_period'] has_energy_data_flag = True if "names" not in reporting_period_data.keys() or \ reporting_period_data['names'] is None or \ len(reporting_period_data['names']) == 0: has_energy_data_flag = False filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename if has_energy_data_flag: ws['B6'].font = title_font ws['B6'] = name + ' 统计分析' # ws['D6'].font = title_font # ws['D6'] = '面积' +report['space']['area'] category = reporting_period_data['names'] # table_title ws['B7'].fill = table_fill ws['B7'].font = title_font ws['B7'].alignment = c_c_alignment ws['B7'] = '报告期' ws['B7'].border = f_border ws['C7'].font = title_font ws['C7'].alignment = c_c_alignment ws['C7'] = '算术平均数' ws['C7'].border = f_border ws['D7'].font = title_font ws['D7'].alignment = c_c_alignment ws['D7'] = '中位数' ws['D7'].border = f_border ws['E7'].font = title_font ws['E7'].alignment = c_c_alignment ws['E7'] = '最小值' ws['E7'].border = f_border ws['F7'].font = title_font ws['F7'].alignment = c_c_alignment ws['F7'] = '最大值' ws['F7'].border = f_border ws['G7'].font = title_font ws['G7'].alignment = c_c_alignment ws['G7'] = '样本标准差' ws['G7'].border = f_border ws['H7'].font = title_font ws['H7'].alignment = c_c_alignment ws['H7'] = '样本方差' ws['H7'].border = f_border # table_data for i, value in enumerate(category): row = i*2 + 8 ws['B' + str(row)].font = name_font ws['B' + str(row)].alignment = c_c_alignment ws['B' + str(row)] = reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + " )" ws['B' + str(row)].border = f_border ws['B' + str(row + 1)].font = name_font ws['B' + str(row + 1)].alignment = c_c_alignment ws['B' + str(row + 1)] = "环比" ws['B' + str(row + 1)].border = f_border ws['C' + str(row)].font = name_font ws['C' + str(row)].alignment = c_c_alignment ws['C' + str(row)] = round(reporting_period_data['means'][i], 2) \ if reporting_period_data['means'][i] is not None else '' ws['C' + str(row)].border = f_border ws['C' + str(row + 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 + 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 + 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 + 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 + 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 + 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 ################################################# # First: 统计分析 # 13: title # 14: table title # 15~18 table_data # Total: 6 rows # if has not energy data: set low height for rows ################################################# if has_energy_data_flag: ws['B13'].font = title_font ws['B13'] = name + ' 单位面积值' ws['D13'].font = title_font ws['D13'] = str(report['space']['area']) + 'M²' category = reporting_period_data['names'] # table_title ws['B14'].fill = table_fill 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'] = '算术平均数' ws['C14'].border = f_border ws['D14'].font = title_font ws['D14'].alignment = c_c_alignment ws['D14'] = '中位数' ws['D14'].border = f_border ws['E14'].font = title_font ws['E14'].alignment = c_c_alignment ws['E14'] = '最小值' ws['E14'].border = f_border ws['F14'].font = title_font ws['F14'].alignment = c_c_alignment ws['F14'] = '最大值' ws['F14'].border = f_border ws['G14'].font = title_font ws['G14'].alignment = c_c_alignment ws['G14'] = '样本标准差' ws['G14'].border = f_border ws['H14'].font = title_font ws['H14'].alignment = c_c_alignment ws['H14'] = '样本方差' ws['H14'].border = f_border # table_data for i, value in enumerate(category): row = i + 15 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] + "/M²)" ws['B' + str(row)].border = f_border ws['C' + str(row)].font = name_font ws['C' + str(row)].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)] = round(reporting_period_data['means_per_unit_area'][i], 2) ws['C' + str(row)].border = f_border ws['D' + str(row)].font = name_font ws['D' + str(row)].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)] = round(reporting_period_data['medians_per_unit_area'][i], 2) ws['D' + str(row)].border = f_border ws['E' + str(row)].font = name_font ws['E' + str(row)].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)] = round(reporting_period_data['minimums_per_unit_area'][i], 2) ws['E' + str(row)].border = f_border ws['F' + str(row)].font = name_font ws['F' + str(row)].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)] = round(reporting_period_data['maximums_per_unit_area'][i], 2) ws['F' + str(row)].border = f_border ws['G' + str(row)].font = name_font ws['G' + str(row)].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)] = round(reporting_period_data['stdevs_per_unit_area'][i], 2) ws['G' + str(row)].border = f_border ws['H' + str(row)].font = name_font ws['H' + str(row)].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)] = round(reporting_period_data['variances_per_unit_area'][i], 2) ws['H' + str(row)].border = f_border ################################################ # Fourth: # 20: title # 21~ 26+ca_len*5-1: line # 26+ca_len*5: table title # 26+ca_len*5~: table_data ################################################ reporting_period_data = report['reporting_period'] times = reporting_period_data['timestamps'] has_detail_data_flag = True ca_len = len(report['reporting_period']['names']) table_row = 27 if "timestamps" not in reporting_period_data.keys() or \ reporting_period_data['timestamps'] is None or \ len(reporting_period_data['timestamps']) == 0: has_detail_data_flag = False if has_detail_data_flag: ws['B20'].font = title_font ws['B20'] = name + ' 报告期消耗' ws['B26'].font = title_font ws['B26'] = name + ' 详细数据' # table ws['B' + str(table_row)].fill = table_fill ws['B' + str(table_row)].border = f_border ws['B' + str(table_row)].alignment = c_c_alignment ws['B' + str(table_row)] = '日期时间' time = times[0] has_data = False max_row = 0 if len(time) > 0: has_data = True max_row = table_row + len(time) # print("max_row", max_row) if has_data: # time time_len = len(time) for index in range(0, len(time)): col = 'B' row = str(table_row + 1 + index) # col = chr(ord('B') + i) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment ws[col + row] = time[index] ws[col + row].border = f_border if index == time_len - 1: row = str(table_row + 2 + index) ws['B' + row].font = title_font ws['B' + row].alignment = c_c_alignment ws['B' + row] = "小计" ws['B' + row].border = f_border # data for index in range(0, ca_len): # table_title col = chr(ord('C') + index) 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'][index] + "(" + reporting_period_data['units'][index] + ")") ws[col + str(table_row)].border = f_border # data time = times[index] time_len = len(time) for j in range(0, time_len): row = str(table_row + 1 + j) # col = chr(ord('B') + i) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment ws[col + row] = round(reporting_period_data['values'][index][j], 0) ws[col + row].border = f_border # subtotals if j == time_len - 1: row = str(table_row + 2 + j) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment ws[col + row] = round(reporting_period_data['subtotals'][index], 0) ws[col + row].border = f_border # bar bar = BarChart() labels = Reference(ws, min_col=2, min_row=table_row + 1, max_row=max_row) bar_data = Reference(ws, min_col=3 + index, min_row=table_row, max_row=max_row) # openpyxl bug bar.add_data(bar_data, titles_from_data=True) bar.set_categories(labels) bar.height = 5.25 # cm 1.05*5 1.05cm = 30 pt bar.width = 18 # pie.title = "Pies sold by category" bar.dLbls = DataLabelList() # bar.dLbls.showCatName = True # label show bar.dLbls.showVal = True # val show bar.dLbls.showPercent = True # percent show # s1 = CharacterProperties(sz=1800) # font size *100 chart_col = 'B' chart_cell = chart_col + str(21 + 5 * index) ws.add_chart(bar, chart_cell) # ################################################ # # Fourth: 相关参数 # # table_row+2: title # # 21~ 26+ca_len*5-1: LineChart # # 26+ca_len*5: table title # # 26+ca_len*5~: table_data # ################################################ # # reporting_period_data = report['parameters'] # times = reporting_period_data['timestamps'] # has_detail_data_flag = True # ca_len = len(reporting_period_data['names']) filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename
def sheet_partecipazione(filename, start_time, participants, workbook): worksheet = workbook.create_sheet('Partecipazione') titles = ['Partecipante'] max_interruptions = 0 for pp in participants: if len(pp['periods']) > max_interruptions: max_interruptions = len(pp['periods']) for i in range(max_interruptions): titles.append(f'Inizio {i+1}') titles.append(f'Fine {i+1}') worksheet.append(titles) for cell in worksheet["1:1"]: cell.font = Font(bold=True) i = 1 for pp in participants: i = i + 1 cur_start = start_time row = [pp['name']] for j in range(len(pp['periods'])): p = pp['periods'][j] row.append(p[0] - cur_start) row.append(p[1] - p[0]) cur_start = p[1] worksheet.append(row) for ii in range(1, len(row) + 1): cell = worksheet.cell(i, ii) cell.number_format = 'hh "ore e" mm "minuti"' column_widths = [30] for _i in range(max_interruptions * 2): column_widths.append(20) for ii, column_width in enumerate(column_widths): worksheet.column_dimensions[get_column_letter(ii + 1)].width = column_width max_col = get_column_letter((max_interruptions * 2) + 1) mediumStyle = TableStyleInfo(name='TableStyleMedium2', showRowStripes=True) worksheet.add_table( Table(ref=f'A1:{max_col}{i}', displayName='RegistroPartecipazione', tableStyleInfo=mediumStyle)) worksheet.sheet_view.showGridLines = False report_id = os.path.basename(filename).replace('.json', '') worksheet.append([f'']) worksheet.append([f'Report generato per il meeting con ID: {report_id}']) data = Reference(worksheet, min_col=2, min_row=1, max_row=i, max_col=(max_interruptions * 2) + 1) cats = Reference(worksheet, min_col=1, min_row=2, max_row=i) chart = BarChart() chart.add_data(data, titles_from_data=True) chart.set_categories(cats) chart.type = "bar" chart.grouping = "stacked" chart.overlap = 100 chart.legend = None chart.height = 20 chart.width = 40 chart.x_axis.scaling.orientation = "maxMin" for ii, s in enumerate(chart.series): s.graphicalProperties.line.nofill = True s.graphicalProperties.line.shadow = None if ii % 2 == 0: s.graphicalProperties.noFill = True else: s.graphicalProperties.solidFill = "008F00" worksheet.add_chart(chart, "A43")
def pop_excl (sv_dict, ClusName): wb = openpyxl.Workbook () sh = wb.active count1 = 0 count2 = 2 alph = ['a', 'b', 'c', 'd'] f = sh['a1'] f.font = Font (bold=True) f = sh['b1'] f.font = Font (bold=True) sh.title = 'HighLevel' sh['a1'] = 'StorageView' sh['b1'] = 'Size(G)' for i in sv_dict: sh[alph[count1] + str (count2)] = i count1 += 1 sh[alph[count1] + str (count2)] = float (sv_dict[i][-1][-1]) count2 += 1 count1 = 0 count2 = 2 for i in sv_dict: sh = wb.create_sheet (i) sh = wb.get_sheet_by_name (i) f = sh['a1'] f.font = Font (bold=True) f = sh['b1'] f.font = Font (bold=True) f = sh['c1'] f.font = Font (bold=True) f = sh['d1'] f.font = Font (bold=True) sh['a1'] = 'LunID' sh['b1'] = 'Name' sh['c1'] = 'VPD' sh['d1'] = 'Size(G/T)' for j in range (len (sv_dict[i])): for k in range (4): sh[alph[count1] + str (count2)] = sv_dict[i][j][k] count1 += 1 count2 += 1 count1 = 0 count2 = 2 logging.debug('Start of chart') l = len(sv_dict) sh = wb.get_sheet_by_name ('HighLevel') logging.debug('sheets: %s' % (wb.get_sheet_names ())) logging.debug('sh: %s' % (sh.title)) chart1 = BarChart() chart1.type = "col" chart1.style = 11 chart1.title = "VPlex Capacity Report" chart1.y_axis.title = 'Size' chart1.x_axis.title = 'View Name' logging.debug('len of sv_dict: %d' % (l)) data = Reference(sh, min_col=2, min_row=2, max_row=l + 1, max_col=2) cats = Reference(sh, min_col=1, min_row=2, max_row=l + 1) chart1.add_data(data, titles_from_data=False) chart1.set_categories(cats) chart1.top = 100 chart1.left = 30 chart1.width = 27 chart1.height = 10 chart1.shape = sh.add_chart(chart1, "D2") wb.save (ClusName) return 0
def Excel(a, b, c, d): #Create Data For Excel PCount = b.count("Pass") #counts passes FCount = b.count("Fail") #counts failures FPerc = ((FCount / len(b)) * 100 ) #creates a percentage of failures to total operations FAvg = sum(a) / len(a) #calculates average forced required for press fit #Start Workbook wb = Workbook() #Creating Sheet 1 ws1 = wb.active ws1.title = d #Entering Headers ws1['A1'] = 'Force' #Turning Data Into An Array of Vectors (Columns in Excel) data_out1 = column_stack([a]) #Writing to Excel for row in data_out1.tolist(): ws1.append(row) ws1['C2'] = 'Total Pass' ws1['C7'] = 'Total Fail' ws1['C12'] = 'Percent Fail' ws1['C17'] = 'Average Force' ws1['E2'] = PCount ws1['E7'] = FCount ws1['E12'] = FPerc ws1['E17'] = '%.4f' % FAvg #Merging Cells ws1.merge_cells('C2:D3') ws1.merge_cells('E2:E3') ws1.merge_cells('C7:D8') ws1.merge_cells('E7:E8') ws1.merge_cells('C12:D13') ws1.merge_cells('E12:E13') ws1.merge_cells('C17:D18') ws1.merge_cells('E17:E18') #Creating Alignment Properties of Cells CELLS = ['A1', 'C2', 'E2', 'C7', 'E7', 'C12', 'E12', 'C17', 'E17'] #List of cells subject to alignment #For Loop to Align the Cells in CELLS for i in CELLS: ws1[i].alignment = Alignment(horizontal='center', vertical='center') #Defining Border Properties and Applying them with For Loops thick_border = Border(left=Side(border_style=BORDER_THICK, color='00000000'), ) for j in range(17): ws1.cell(row=j + 2, column=3).border = thick_border ws1.cell(row=j + 2, column=3).border = thick_border j += 1 for j in range(17): ws1.cell(row=j + 2, column=6).border = thick_border ws1.cell(row=j + 2, column=6).border = thick_border j += 1 #Creating Bar Graph chart1 = BarChart() chart1.type = "col" chart1.style = 1 chart1.title = None chart1.y_axis.title = 'Force' chart1.x_axis.title = '' ChartData = Reference(ws1, min_col=1, min_row=2, max_row=len(a) + 1, max_col=1) chart1.add_data(ChartData, titles_from_data=None) chart1.legend = None chart1.height = 15 chart1.width = 30 ws1.add_chart(chart1, "G5") #Saving/Closing wb.save(c)
def ronava_bar_chart(writingSheet, dataSheet, params): # TODO add dictionary in parameters to avoid overlapping if params["use"] == "bars": data = Reference( dataSheet, min_col=params["data_min_col"], min_row=params["data_min_row"], max_row=params["data_max_row"], max_col=params["data_max_col"], ) cats = Reference( dataSheet, min_col=params["cats_min_col"], min_row=params["cats_min_row"], max_row=params["cats_max_row"], max_col=params["cats_max_col"], ) chart = BarChart() chart.type = params["type"] chart.style = 12 # chart.grouping = "stacked" chart.title = params["title"] chart.y_axis.title = params["y_axis"] chart.x_axis.title = params["x_axis"] chart.add_data(data, titles_from_data=True) chart.set_categories(cats) chart.height = params["heigth"] chart.width = params["width"] writingSheet.add_chart(chart, "D2") elif params["use"] == "single": c1 = BarChart() v1 = Reference( dataSheet, min_col=params["data_min_col"], min_row=params["data_min_row"], max_col=params["data_max_col"] ) cats = Reference( dataSheet, min_col=params["cats_min_col"], min_row=params["cats_min_row"], max_col=params["cats_max_col"] ) c1.series = [Series(v1, title_from_data=True)] c1.style = 12 c1.set_categories(cats) c1.x_axis.title = params["x_axis"] c1.y_axis.title = params["y_axis"] c1.height = params["heigth"] c1.width = params["width"] c1.title = params["title"] writingSheet.add_chart(c1, "D4") else: c1 = BarChart() v1 = Reference( dataSheet, min_col=params["data_min_col"], min_row=params["data_min_row"], max_col=params["data_max_col"] ) cats = Reference( dataSheet, min_col=params["cats_min_col"], min_row=params["cats_min_row"], max_col=params["cats_max_col"] ) c1.series = [Series(v1, title_from_data=True)] c1.y_axis.majorGridlines = None c1.set_categories(cats) c1.x_axis.title = params["x_axis"] c1.y_axis.title = params["y_axis"] c1.height = params["heigth"] c1.width = params["width"] c1.title = params["title"] c1.style = 12 # Create a second chart c2 = LineChart() v2 = Reference( dataSheet, min_col=params["data_min_col"], min_row=params["data_min_row"] + 1, max_col=params["data_max_col"], ) c2.series = [Series(v2, title_from_data=True)] c2.y_axis.axId = 20 c2.y_axis.title = "Porcentaje Produccion" # Assign the y-axis of the second chart to the third axis of the first chart c1.z_axis = c2.y_axis c1.y_axis.crosses = "max" c1 += c2 writingSheet.add_chart(c1, "D4")
def transcribe_client_data_to_workbooks(client): report_path = r'C:\Program Files\Notepad++\reports' + '\\' + string.replace( client, '/', '-') + '_report.csv' wb = openpyxl.load_workbook( 'C:\Program Files\Notepad++\Bucket-Asset Allocation Model.xlsm', read_only=False, keep_vba=True) wb.active = 5 ws = wb.active with open(report_path, 'rU') as f: reader = csv.reader(f) for row_index, row in enumerate(reader): for column_index, cell in enumerate(row): column_letter = get_column_letter((column_index + 1)) if column_letter == 'A': if row_index > 0: ws[column_letter + str(row_index + 1)] = long( string.replace(string.replace(cell, 'Z', '9'), 'X', '0')) else: ws[column_letter + str(row_index + 1)] = cell #Proposed allocation pie chart creation #Close the file f.close() #Proposed Allocation pie chart creation wb.active = 1 ws = wb.active pie1 = PieChart() labels1 = Reference(ws, min_col=14, min_row=19, max_row=21) data1 = Reference(ws, min_col=15, min_row=18, max_row=21) pie1.add_data(data1, titles_from_data=True) pie1.set_categories(labels1) pie1.title = 'Proposed Allocation' pie1.height = 14 pie1.width = 18 ws.add_chart(pie1, "F20") pie1.dataLabels = DataLabelList() pie1.dataLabels.showPercent = True #Current allocation pie chart creation pie2 = PieChart() labels2 = Reference(ws, min_col=14, min_row=29, max_row=31) data2 = Reference(ws, min_col=15, min_row=28, max_row=31) pie2.add_data(data2, titles_from_data=True) pie2.set_categories(labels2) pie2.title = 'Current Allocation' pie2.height = 14 pie2.width = 18 ws.add_chart(pie2, 'B20') pie2.dataLabels = DataLabelList() pie2.dataLabels.showPercent = True #Bar chart creation #Change to allocations worksheet wb.active = 3 ws = wb.active #Short term bucket bar graph creation chart1 = BarChart() chart1.type = "col" chart1.style = 12 chart1.title = "Bucket I \n Short - Term" data = Reference(ws, min_col=14, min_row=61, max_row=62, max_col=15) cats = Reference(ws, min_col=13, min_row=62) chart1.add_data(data, titles_from_data=True) chart1.set_categories(cats) chart1.shape = 4 ws.add_chart(chart1, "B25") chart1.dataLabels = DataLabelList() chart1.dataLabels.showVal = True #Intermediate term bucket bar graph creation chart2 = BarChart() chart2.type = "col" chart2.style = 10 chart2.title = "Bucket II \n Intermediate - Term" data2 = Reference(ws, min_col=14, min_row=63, max_row=64, max_col=15) cats2 = Reference(ws, min_col=13, min_row=64) chart2.add_data(data2, titles_from_data=True) chart2.set_categories(cats2) chart2.shape = 4 ws.add_chart(chart2, "I25") chart2.dataLabels = DataLabelList() chart2.dataLabels.showVal = True #Long term bucket bar graph creation chart3 = BarChart() chart3.type = "col" chart3.style = 13 chart3.title = "Bucket III \n Long - Term" data3 = Reference(ws, min_col=14, min_row=65, max_row=66, max_col=15) cats3 = Reference(ws, min_col=13, min_row=66) chart3.add_data(data3, titles_from_data=True) chart3.set_categories(cats3) chart3.shape = 4 ws.add_chart(chart3, "P25") chart3.dataLabels = DataLabelList() chart3.dataLabels.showVal = True #0 portfolio bar graph creation chart4 = BarChart() chart4.type = "col" chart4.style = 10 chart4.height = 10 chart4.width = 20 chart4.title = "0 Portfolio" data4 = Reference(ws, min_col=14, min_row=57, max_row=58, max_col=15) cats4 = Reference(ws, min_col=14, min_row=57, max_col=15) chart4.add_data(data4, titles_from_data=True) chart4.set_categories(cats4) chart4.shape = 4 ws.add_chart(chart4, "B3") chart4.dataLabels = DataLabelList() chart4.dataLabels.showVal = True #Allocation Comparison Bar graph creation chart5 = BarChart() chart5.type = "col" chart5.style = 10 chart5.title = "Allocation Comparison" chart5.height = 10 chart5.width = 20 data5 = Reference(ws, min_col=10, min_row=62, max_row=64, max_col=12) cats5 = Reference(ws, min_col=9, min_row=63, max_row=64) chart5.add_data(data5, titles_from_data=True) chart5.set_categories(cats5) chart5.shape = 4 ws.add_chart(chart5, "M3") chart5.dataLabels = DataLabelList() chart5.dataLabels.showVal = True print(len(set(cdict.values()))) #Attempt to save, move on if the file is open for reading. try: wb.save('C:\\Program Files\\Notepad++\\workbooks\\' + string.replace(client, '/', '-') + '_workbook.xlsm') print(string.replace(client, '/', '-') + ' workbook completed') except IOError: print("Workbook already open, cannot overwrite, moving on.")
def prepare_utilization_chart_worksheet(): """ Objective: This function prepares a Bar chart in utilization chart worksheet Input: None Output : None Use it: prepare_utilization_chart_worksheet() """ # inbound chart inbound_chart = BarChart() # Bar chart inbound_chart.height = 15 # default is 7.5 inbound_chart.width = 30 # default is 15 #inbound_chart.grouping = "stacked" #inbound_chart.overlap = 100 #inbound_chart.type = "col" #inbound_chart.style = 13 inbound_chart.title = "Bandwidth Utilization Chart - Inbound - Avg/Max (util %)" inbound_chart.y_axis.title = 'Avg/Max Utilization(%)' inbound_chart.x_axis.title = 'Device' inbound_data = Reference(worksheet=utilization_chart_worksheet, min_row=1, min_col=3, max_row=chart_rows - 1, max_col=4) inbound_chart.add_data(inbound_data, titles_from_data=True) categories = Reference(worksheet=utilization_chart_worksheet, min_row=2, min_col=1, max_row=chart_rows - 1, max_col=1) inbound_chart.set_categories(categories) utilization_chart_worksheet.add_chart(inbound_chart, "H2") #outbound chart outbound_chart = BarChart() # Bar chart outbound_chart.height = 15 # default is 7.5 outbound_chart.width = 30 # default is 15 #outbound_chart.grouping = "stacked" #outbound_chart.overlap = 100 #outbound_chart.type = "col" #outbound_chart.style = 13 outbound_chart.title = "Bandwidth Utilization Chart - Outbound - Avg/Max (util %)" outbound_chart.y_axis.title = 'Avg/Max Utilization(%)' outbound_chart.x_axis.title = 'Device' outbound_data = Reference(worksheet=utilization_chart_worksheet, min_row=1, min_col=5, max_row=chart_rows - 1, max_col=6) outbound_chart.add_data(outbound_data, titles_from_data=True) categories = Reference(worksheet=utilization_chart_worksheet, min_row=2, min_col=1, max_row=chart_rows - 1, max_col=1) outbound_chart.set_categories(categories) utilization_chart_worksheet.add_chart(outbound_chart, "H32") report_workbook.save(output_file)
def generate_excel(report, name, reporting_start_datetime_local, reporting_end_datetime_local, period_type): wb = Workbook() # todo ws = wb.active # Row height ws.row_dimensions[1].height = 118 for i in range(2, 2000 + 1): ws.row_dimensions[i].height = 30 # Col width ws.column_dimensions['A'].width = 1.5 for i in range(ord('B'), ord('I')): ws.column_dimensions[chr(i)].width = 18 # Font name_font = Font(name='Constantia', size=15, bold=True) title_font = Font(name='宋体', size=15, bold=True) table_title_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") 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.merge_cells("G3:J3") for i in range(ord('G'), ord('K')): ws[chr(i) + '3'].border = b_border ws['G3'].alignment = b_c_alignment ws['G3'].font = name_font ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local if "reporting_period" not in report.keys() or \ "timestamps" not in report['reporting_period'].keys() or len(report['reporting_period']['timestamps']) == 0: filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename ############################### has_names_data_flag = True if "names" not in report['reporting_period'].keys() or len( report['reporting_period']['names']) == 0: has_names_data_flag = False current_row_number = 6 if has_names_data_flag: reporting_period_data = report['reporting_period'] category = reporting_period_data['names'] ca_len = len(category) ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + '报告期平均负荷' current_row_number += 1 ws['B' + str(current_row_number)].fill = table_fill for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + str(current_row_number)].fill = table_fill ws[col + str(current_row_number)].font = name_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = reporting_period_data['names'][i] + \ " (" + reporting_period_data['units'][i] + "/H)" current_row_number += 1 ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = '平均负荷' for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + str(current_row_number)].font = name_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = round( reporting_period_data['averages'][i], 2) current_row_number += 1 ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = '单位面积值' for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + str(current_row_number)].font = name_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = round( reporting_period_data['averages_per_unit_area'][i], 2) current_row_number += 1 ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = '环比' for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + str(current_row_number)].font = name_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = str( round(reporting_period_data['averages_increment_rate'][i] * 100, 2)) + "%" \ if reporting_period_data['averages_increment_rate'][i] is not None else "-" current_row_number += 2 ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + '报告期最大负荷' current_row_number += 1 ws['B' + str(current_row_number)].fill = table_fill for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + str(current_row_number)].fill = table_fill ws[col + str(current_row_number)].font = name_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = reporting_period_data['names'][i] + \ " (" + reporting_period_data['units'][i] + "/H)" current_row_number += 1 ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = '最大负荷' for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + str(current_row_number)].font = name_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = round( reporting_period_data['maximums'][i], 2) current_row_number += 1 ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = '单位面积值' for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + str(current_row_number)].font = name_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = round( reporting_period_data['maximums_per_unit_area'][i], 2) current_row_number += 1 ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = '环比' for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + str(current_row_number)].font = name_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = str( round(reporting_period_data['maximums_increment_rate'][i] * 100, 2)) + "%" \ if reporting_period_data['maximums_increment_rate'][i] is not None else "-" current_row_number += 2 ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + '报告期负荷系数' current_row_number += 1 ws['B' + str(current_row_number)].fill = table_fill for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + str(current_row_number)].fill = table_fill ws[col + str(current_row_number)].font = name_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = reporting_period_data['names'][i] current_row_number += 1 ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = '负荷系数' for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + str(current_row_number)].font = name_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = round(reporting_period_data['factors'][i], 2) \ if reporting_period_data['factors'][i] is not None else '-' current_row_number += 1 ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = '环比' for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + str(current_row_number)].font = name_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = str( round(reporting_period_data['factors_increment_rate'][i] * 100, 2)) + "%" \ if reporting_period_data['factors_increment_rate'][i] is not None else "-" current_row_number += 2 has_sub_averages_data_flag = True has_sub_maximums_data_flag = True if "sub_averages" not in report['reporting_period'].keys() or len( report['reporting_period']['sub_averages']) == 0: has_sub_averages_data_flag = False if "sub_averages" not in report['reporting_period'].keys() or len( report['reporting_period']['sub_averages']) == 0: has_sub_maximums_data_flag = False if has_sub_averages_data_flag or has_sub_maximums_data_flag: reporting_period_data = report['reporting_period'] category = reporting_period_data['names'] ca_len = len(category) times = reporting_period_data['timestamps'] time = times[0] ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + '详细数据' current_row_number += 1 chart_start_number = current_row_number if has_sub_averages_data_flag: current_row_number = (current_row_number + ca_len * 5) if has_sub_maximums_data_flag: current_row_number = (current_row_number + ca_len * 5) table_start_number = current_row_number ws['B' + str(current_row_number)].fill = table_fill ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = '日期时间' col = 'C' for i in range(0, ca_len): if has_sub_averages_data_flag: ws[col + str(current_row_number)].fill = table_fill ws[col + str(current_row_number)].font = table_title_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = reporting_period_data['names'][i] + \ " 平均负荷(" + reporting_period_data['units'][i] + "/H)" col = chr(ord(col) + 1) if has_sub_maximums_data_flag: ws[col + str(current_row_number)].fill = table_fill ws[col + str(current_row_number)].font = table_title_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = reporting_period_data['names'][i] + \ " 最大负荷(" + reporting_period_data['units'][i] + "/H)" col = chr(ord(col) + 1) current_row_number += 1 for i in range(0, len(time)): ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = time[i] col = 'C' for j in range(0, ca_len): if has_sub_averages_data_flag: ws[col + str(current_row_number)].font = title_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = round(reporting_period_data['sub_averages'][j][i], 2) \ if reporting_period_data['sub_averages'][j][i] is not None else 0.00 col = chr(ord(col) + 1) if has_sub_maximums_data_flag: ws[col + str(current_row_number)].font = title_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = round(reporting_period_data['sub_maximums'][j][i], 2) \ if reporting_period_data['sub_maximums'][j][i] is not None else 0.00 col = chr(ord(col) + 1) current_row_number += 1 table_end_number = current_row_number - 1 current_chart_col_number = 3 current_chart_row_number = chart_start_number for i in range(0, ca_len): labels = Reference(ws, min_col=2, min_row=table_start_number + 1, max_row=table_end_number) if has_sub_averages_data_flag: bar = BarChart() datas = Reference(ws, min_col=current_chart_col_number, min_row=table_start_number, max_row=table_end_number) bar.add_data(datas, titles_from_data=True) bar.set_categories(labels) bar.height = 5.25 bar.width = len(time) bar.dLbls = DataLabelList() bar.dLbls.showVal = True ws.add_chart(bar, "B" + str(current_chart_row_number)) current_chart_row_number += 5 current_chart_col_number += 1 if has_sub_maximums_data_flag: bar = BarChart() datas = Reference(ws, min_col=current_chart_col_number, min_row=table_start_number, max_row=table_end_number) bar.add_data(datas, titles_from_data=True) bar.set_categories(labels) bar.height = 5.25 bar.width = len(time) bar.dLbls = DataLabelList() bar.dLbls.showVal = True ws.add_chart(bar, "B" + str(current_chart_row_number)) current_chart_row_number += 5 current_chart_col_number += 1 filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename
def generate_excel(report, name, reporting_start_datetime_local, reporting_end_datetime_local, period_type): wb = Workbook() # todo ws = wb.active # Row height ws.row_dimensions[1].height = 118 for i in range(2, 11 + 1): ws.row_dimensions[i].height = 30 for i in range(12, 43 + 1): ws.row_dimensions[i].height = 30 # Col width ws.column_dimensions['A'].width = 1.5 for i in range(ord('B'), ord('I')): ws.column_dimensions[chr(i)].width = 15.0 # Font name_font = Font(name='Constantia', size=15, bold=True) title_font = Font(name='宋体', size=15, bold=True) data_font = Font(name='Franklin Gothic Book', size=11) table_fill = PatternFill(fill_type='solid', fgColor='1F497D') f_border = Border(left=Side(border_style='medium', color='00000000'), right=Side(border_style='medium', color='00000000'), bottom=Side(border_style='medium', color='00000000'), top=Side(border_style='medium', color='00000000')) b_border = Border(bottom=Side(border_style='medium', color='00000000'), ) b_c_alignment = Alignment(vertical='bottom', horizontal='center', text_rotation=0, wrap_text=False, shrink_to_fit=False, indent=0) c_c_alignment = Alignment(vertical='center', horizontal='center', text_rotation=0, wrap_text=False, shrink_to_fit=False, indent=0) b_r_alignment = Alignment(vertical='bottom', horizontal='right', text_rotation=0, wrap_text=False, shrink_to_fit=False, indent=0) c_r_alignment = Alignment(vertical='bottom', horizontal='center', text_rotation=0, wrap_text=False, shrink_to_fit=False, indent=0) # Img img = Image("excelexporters/myems.png") ws.add_image(img, 'B1') # Title ws['B3'].font = name_font ws['B3'].alignment = b_r_alignment ws['B3'] = 'Name:' ws['C3'].border = b_border ws['C3'].alignment = b_c_alignment ws['C3'].font = name_font ws['C3'] = name ws['D3'].font = name_font ws['D3'].alignment = b_r_alignment ws['D3'] = 'Period:' ws['E3'].border = b_border ws['E3'].alignment = b_c_alignment ws['E3'].font = name_font ws['E3'] = period_type ws['F3'].font = name_font ws['F3'].alignment = b_r_alignment ws['F3'] = 'Date:' ws.merge_cells("G3:H3") 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 if "reporting_period" not in report.keys() or \ "values" not in report['reporting_period'].keys() or len(report['reporting_period']['values']) == 0: filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename ############################### has_cost_data_flag = True if "values" not in report['reporting_period'].keys() or len( report['reporting_period']['values']) == 0: has_cost_data_flag = False if has_cost_data_flag: ws['B6'].font = title_font ws['B6'] = name + '报告期成本' reporting_period_data = report['reporting_period'] category = report['offline_meter']['energy_category_name'] ca_len = len(category) ws['B7'].fill = table_fill ws['B8'].font = title_font ws['B8'].alignment = c_c_alignment ws['B8'] = '成本' ws['B8'].border = f_border ws['B9'].font = title_font ws['B9'].alignment = c_c_alignment ws['B9'] = '环比' ws['B9'].border = f_border col = 'B' for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + '7'].fill = table_fill ws[col + '7'].font = name_font ws[col + '7'].alignment = c_c_alignment ws[col + '7'] = report['offline_meter']['energy_category_name'] + \ " (" + report['offline_meter']['unit_of_measure'] + ")" ws[col + '7'].border = f_border ws[col + '8'].font = name_font ws[col + '8'].alignment = c_c_alignment ws[col + '8'] = round(reporting_period_data['total_in_category'], 0) ws[col + '8'].border = f_border ws[col + '9'].font = name_font ws[col + '9'].alignment = c_c_alignment ws[col + '9'] = str(round(reporting_period_data['increment_rate'] * 100, 2)) + "%" \ if reporting_period_data['increment_rate'] is not None else "-" ws[col + '9'].border = f_border # TCE TCO2E end_col = col # TCE tce_col = chr(ord(end_col) + 1) ws[tce_col + '7'].fill = table_fill ws[tce_col + '7'].font = name_font ws[tce_col + '7'].alignment = c_c_alignment ws[tce_col + '7'] = "TCE" ws[tce_col + '7'].border = f_border ws[tce_col + '8'].font = name_font ws[tce_col + '8'].alignment = c_c_alignment ws[tce_col + '8'] = round(reporting_period_data['total_in_kgce'], 0) ws[tce_col + '8'].border = f_border ws[tce_col + '9'].font = name_font ws[tce_col + '9'].alignment = c_c_alignment ws[tce_col + '9'] = str(round(reporting_period_data['increment_rate'] * 100, 2)) + "%" \ if reporting_period_data['increment_rate'] is not None else "-" ws[tce_col + '9'].border = f_border # TCO2E tco2e_col = chr(ord(end_col) + 2) ws[tco2e_col + '7'].fill = table_fill ws[tco2e_col + '7'].font = name_font ws[tco2e_col + '7'].alignment = c_c_alignment ws[tco2e_col + '7'] = "TCO2E" ws[tco2e_col + '7'].border = f_border ws[tco2e_col + '8'].font = name_font ws[tco2e_col + '8'].alignment = c_c_alignment ws[tco2e_col + '8'] = round(reporting_period_data['total_in_kgco2e'], 0) ws[tco2e_col + '8'].border = f_border ws[tco2e_col + '9'].font = name_font ws[tco2e_col + '9'].alignment = c_c_alignment ws[tco2e_col + '9'] = str(round(reporting_period_data['increment_rate'] * 100, 2)) + "%" \ if reporting_period_data['increment_rate'] is not None else "-" ws[tco2e_col + '9'].border = f_border else: for i in range(6, 9 + 1): ws.rows_dimensions[i].height = 0.1 ###################################### has_cost_detail_flag = True reporting_period_data = report['reporting_period'] category = report['offline_meter']['energy_category_name'] ca_len = len(category) times = reporting_period_data['timestamps'] if "values" not in reporting_period_data.keys() or len( reporting_period_data['values']) == 0: has_cost_detail_flag = False if has_cost_detail_flag: ws['B11'].font = title_font ws['B11'] = name + '详细数据' ws['B18'].fill = table_fill ws['B18'].font = title_font ws['B18'].border = f_border ws['B18'].alignment = c_c_alignment ws['B18'] = '日期时间' time = times has_data = False max_row = 0 if len(time) > 0: has_data = True max_row = 18 + len(time) if has_data: end_data_row_number = 19 for i in range(0, len(time)): col = 'B' end_data_row_number = 19 + i row = str(end_data_row_number) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment ws[col + row] = time[i] ws[col + row].border = f_border ws['B' + str(end_data_row_number + 1)].font = title_font ws['B' + str(end_data_row_number + 1)].alignment = c_c_alignment ws['B' + str(end_data_row_number + 1)] = '总计' ws['B' + str(end_data_row_number + 1)].border = f_border bar = BarChart() for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + '18'].fill = table_fill ws[col + '18'].font = title_font ws[col + '18'].alignment = c_c_alignment ws[col + '18'] = report['offline_meter']['energy_category_name'] + \ " (" + report['offline_meter']['unit_of_measure'] + ")" ws[col + '18'].border = f_border time = times time_len = len(time) for j in range(0, time_len): row = str(19 + j) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment ws[col + row] = round(reporting_period_data['values'][j], 0) ws[col + row].border = f_border ws[col + str(end_data_row_number + 1)].font = title_font ws[col + str(end_data_row_number + 1)].alignment = c_c_alignment ws[col + str(end_data_row_number + 1)] = round( reporting_period_data['total_in_category'], 0) ws[col + str(end_data_row_number + 1)].border = f_border bar_data = Reference(ws, min_col=3 + i, min_row=18, max_row=max_row) bar.series.append(Series(bar_data, title_from_data=True)) labels = Reference(ws, min_col=2, min_row=19, max_row=max_row) bar.set_categories(labels) bar.dLbls = DataLabelList() bar.dLbls.showVal = True bar.height = 5.25 bar.width = len(time) ws.add_chart(bar, "B12") else: for i in range(11, 43 + 1): ws.row_dimensions[i].height = 0.0 filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename