def insertTotalMoneyCPChart(sheet_obj, num_of_exp, symbol, finalOptionExpDate, final_chart_col_loc, final_chart_insert_pos, getNumberColsData): if (Commonapi.debug == 1): print("insertTotalMoneyCPChart started") for i in range(0, (int)(num_of_exp)): min_cols = (int)(final_chart_col_loc[i]) max_cols = min_cols + 1 m_rows = min_cols + getNumberColsData print("m_rows min_cols is :", m_rows, min_cols) chart = BarChart() chart.type = "col" chart.grouping = "stacked" chart.overlap = 100 chart.title = symbol + '_' + finalOptionExpDate[i] chart.y_axis.title = 'TOTAL_CALL_PUT_TM x K' chart.x_axis.title = 'Dates' data = Reference(sheet_obj, min_col=min_cols, min_row=1, max_row=m_rows, max_col=max_cols) cats = Reference(sheet_obj, min_col=1, min_row=2, max_row=m_rows) chart.add_data(data, titles_from_data=True) chart.dataLabels = DataLabelList() chart.dataLabels.showVal = True chart.set_categories(cats) sheet_obj.add_chart(chart, final_chart_insert_pos[i]) title = symbol + '_' + finalOptionExpDate[i] print("Bar chart :", title) if (Commonapi.debug == 1): print("insertTotalMoneyCPChart ended")
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 insertCPIOMOIchartToCPMISheet(sheet_obj, symbol, finalOptionExpDate, num_of_exp, final_OTMOI_chart_insert_pos, iotmio_data_col_loc, getNumberColsData, iotmio_row_loc): if (Commonapi.debug == 1): print("insertCPIOMOIchartToCPMISheet started") for i in range(0, num_of_exp): min_rows = (int)(iotmio_row_loc[1]) min_cols = (int)(iotmio_data_col_loc[i]) max_cols = min_cols + 3 max_rows = min_rows + getNumberColsData chart = BarChart() chart.type = "col" chart.grouping = "stacked" chart.overlap = 100 chart.title = symbol + '_' + finalOptionExpDate[i] chart.y_axis.title = final_OTM_chart_y_axis[0] chart.x_axis.title = 'Dates' data = Reference(sheet_obj, min_col=min_cols, min_row=min_rows, max_row=max_rows, max_col=max_cols) cats = Reference(sheet_obj, min_col=1, min_row=min_rows + 1, max_row=max_rows) chart.add_data(data, titles_from_data=True) chart.dataLabels = DataLabelList() chart.dataLabels.showVal = True chart.set_categories(cats) sheet_obj.add_chart(chart, final_OTMOI_chart_insert_pos[i]) title = symbol + '_' + finalOptionExpDate[i] print("Bar chart :", title) if (Commonapi.debug == 1): print("insertCPIOMOIchartToCPMISheet ended")
datasheet_3.cell(row=_row, column=_col).border = border_s for _row in range(2, 34): datasheet_3.cell(row=_row, column=6).number_format = number_format_s # 生成表2图表1 chart2_1 = BarChart() chart2_1.style = 3 chart2_1.width = 30 chart2_1.title = "周开通量统计" chart2_1.y_axis.title = '周开通量(台)' data = Reference(datasheet_2, min_col=6, min_row=1, max_row=32) cats = Reference(datasheet_2, min_col=2, min_row=2, max_row=32) chart2_1.add_data(data, titles_from_data=True) chart2_1.set_categories(cats) datasheet_2.add_chart(chart2_1, "A35") chart2_1.dataLabels = DataLabelList() chart2_1.dataLabels.showVal = True # 生成表2图表2 chart2_2 = BarChart() chart2_2.style = 3 chart2_2.width = 30 chart2_2.title = "当年累计开通量统计" chart2_2.y_axis.title = '当年累计开通量(台)' data = Reference(datasheet_2, min_col=4, min_row=1, max_row=32) cats = Reference(datasheet_2, min_col=2, min_row=2, max_row=32) chart2_2.add_data(data, titles_from_data=True) chart2_2.set_categories(cats) datasheet_2.add_chart(chart2_2, "A52") chart2_2.dataLabels = DataLabelList() chart2_2.dataLabels.showVal = True # 生成表3图表1
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 generate_charts(filename): wb = openpyxl.load_workbook(filename) mysheet = wb.get_active_sheet() # 找到最大行 for i in range(3, 30): if mysheet['B' + str(i)].value == None: max_row = i - 1 break # ft = Font(bold=True) mysheet['B' + str(max_row + 1)].value = 'total' mysheet['B' + str(max_row + 1)].font = ft k = 54 t = 61 for x in 'CDEFGHIJKLMN': x_max = x + str(max_row) x_cell = x + str(max_row + 1) mysheet[x_cell].value = '=SUM(' + x + str(4) + ':' + x_max + ')' if x > 'D' and x < 'J': mysheet['C' + str(k)].value = mysheet[x_cell].value k += 1 if x >= 'J' and x <= 'N': mysheet['C' + str(t)].value = mysheet[x_cell].value t += 1 mysheet[x_cell].font = ft chart = BarChart() chart.style = 11 chart.type = "bar" chart.title = "问题燃尽情况" # 系列重叠 chart.overlap = 100 # 添加数据标签 chart.dataLabels = DataLabelList() chart.dataLabels.showVal = True data = Reference(mysheet, min_col=3, min_row=3, max_row=max_row, max_col=4) cats = Reference(mysheet, min_col=2, min_row=4, max_row=max_row) chart.add_data(data, titles_from_data=True) chart.set_categories(cats) chart.shape = 4 mysheet.add_chart(chart, 'B' + str(max_row + 3)) pie1 = PieChart() labels = Reference(mysheet, min_col=2, min_row=54, max_row=58) data = Reference(mysheet, min_col=3, min_row=53, max_row=58) pie1.add_data(data, titles_from_data=True) pie1.set_categories(labels) pie1.title = "问题分类占比" pie1.dataLabels = DataLabelList() pie1.dataLabels.showVal = True # Cut the first slice out of the pie slice = DataPoint(idx=0, explosion=20) pie1.series[0].data_points = [slice] mysheet.add_chart(pie1, 'I' + str(max_row + 3)) pie2 = PieChart() labels = Reference(mysheet, min_col=2, min_row=61, max_row=65) data = Reference(mysheet, min_col=3, min_row=60, max_row=65) pie2.add_data(data, titles_from_data=True) pie2.set_categories(labels) pie2.title = "严重级别占比" pie2.dataLabels = DataLabelList() pie2.dataLabels.showVal = True # Cut the first slice out of the pie slice = DataPoint(idx=0, explosion=20) pie2.series[0].data_points = [slice] mysheet.add_chart(pie2, 'B' + str(max_row + 19)) wb.save(filename)
def createTwoAxisChart(wb, readType, rows, titles, lineNumber, place="C10", max_row=-1, max_col=-1, min_row=1, min_col=1, isDesc=True, isMark=True, isLineWidth=True, isDataLable=True): ws = excel_util.creatSheet(wb, False, titles[0]) excel_util.putRowsToSheet(rows, ws) if max_row == -1: max_row = len(rows) if max_col == -1: max_col = len(rows[0]) c1 = LineChart() c2 = BarChart() if readType == "row": addChartDataBtType(ws, readType, c2, max_row, max_col, lineNumber, min_col) addChartDataBtType(ws, readType, c1, lineNumber, max_col, min_row, min_col) elif readType == "col": addChartDataBtType(ws, readType, c2, max_row, max_col, min_row, lineNumber) addChartDataBtType(ws, readType, c1, max_row, lineNumber, min_row, min_col) c1.y_axis.axId = 200 c1.y_axis.crosses = "max" c1 += c2 c2.y_axis.title = titles[3] c2.y_axis.title.tx.rich.p[0].r.rPr = cpAxisTitle c2.y_axis.txPr = RichText(p=[ Paragraph(pPr=ParagraphProperties(defRPr=cpAxisText), endParaRPr=cpAxisText) ]) c2.dataLabels = DataLabelList() c2.dataLabels.txPr = RichText(p=[ Paragraph(pPr=ParagraphProperties(defRPr=cpDataLabel), endParaRPr=cpDataLabel) ]) c2.dataLabels.showVal = True setChartTitleAndSize(c1, titles) setChartByParameter(c1, isDesc, isMark, isLineWidth, isDataLable) # 逆序 if isDesc: c1.y_axis.scaling.orientation = "maxMin" c1.x_axis.crosses = "min" ws.add_chart(c1, place)
def __init__(self, filename, sheet_name=[], chart_title=[], xaxis_title=[], yaxis_title=[], stack=[], chart_position="F10", whiten_all_area=True, font_name='Calibri', axis_title_font_size=10, output_filename=None): wb = load_workbook(filename) counts = len(sheet_name) wsDict = {} for count in range(counts): wsDict['ws' + str(count)] = wb[sheet_name[count]] chart = BarChart() chart.style = 10 chart.type = "col" if stack[count] == True: chart.grouping = "stacked" chart.overlap = 100 chart.title = chart_title[count] chart.y_axis.title = xaxis_title[count] chart.x_axis.title = yaxis_title[count] data = Reference(wsDict['ws' + str(count)], min_col=wsDict['ws' + str(count)].min_column + 1, min_row=wsDict['ws' + str(count)].min_row, max_row=wsDict['ws' + str(count)].max_row, max_col=wsDict['ws' + str(count)].max_column) cats = Reference(wsDict['ws' + str(count)], min_col=wsDict['ws' + str(count)].min_column, min_row=wsDict['ws' + str(count)].min_row + 1, max_row=wsDict['ws' + str(count)].max_row, max_col=wsDict['ws' + str(count)].min_column) chart.add_data(data, titles_from_data=True) chart.set_categories(cats) ## set axis title property?? font_test = draw_Font(typeface=font_name) cp = CharacterProperties(latin=font_test, sz=axis_title_font_size * 100) chart.x_axis.txPr = RichText(p=[ Paragraph(pPr=ParagraphProperties(defRPr=cp), endParaRPr=cp) ]) chart.y_axis.txPr = RichText(p=[ Paragraph(pPr=ParagraphProperties(defRPr=cp), endParaRPr=cp) ]) xPara = [ Paragraph(pPr=ParagraphProperties(defRPr=cp), r=RegularTextRun(t=s)) for s in xaxis_title[count].split("\n") ] yPara = [ Paragraph(pPr=ParagraphProperties(defRPr=cp), r=RegularTextRun(t=s)) for s in yaxis_title[count].split("\n") ] chart.x_axis.title.tx.rich.paragraphs = xPara chart.y_axis.title.tx.rich.paragraphs = yPara ## hide legend if not stack[count]: chart.legend = None wsDict['ws' + str(count)].add_chart(chart, chart_position) ## whiten data if whiten_all_area: table = wsDict['ws' + str(count)]["A1":"AZ200"] color = 'FFFFFFFF' font = Font(bold=False, italic=False, vertAlign=None, underline='none', strike=False, color=color) fill = PatternFill(start_color=color, end_color=color, fill_type="solid") for tr in table: for td in tr: td.font = font td.border = None td.fill = fill ### data label chart.dataLabels = DataLabelList() chart.dataLabels.showVal = True if output_filename is None: wb.save(filename=filename) else: wb.save(filename=output_filename) # # barchart_creater(filename = 'temp0.xlsx',sheet_name=["Issue Count by Due Date","Issue Count By Owner","MTM Issue Comparison"], # chart_title=["Number of Issues By Due Date","Number of outstanding Issues by Owner","Number of Issues comparison month to month by Source"], # xaxis_title = ["Number of Issues",'Issue Owner','Month'], # yaxis_title=['Number of Days till Due','Number of Issues','Number Of Issues'],stack = [False,False,True])