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 sample_output_excel_chart(): # Sample from http://zetcode.com/articles/openpyxl/ book = Workbook() sheet = book.active rows = [ ("USA", 46), ("China", 38), ("UK", 29), ("Russia", 22), ("South Korea", 13), ("Germany", 11) ] for row in rows: sheet.append(row) data = Reference(sheet, min_col=2, min_row=1, max_col=2, max_row=6) categs = Reference(sheet, min_col=1, min_row=1, max_row=6) chart = BarChart() chart.add_data(data=data) chart.set_categories(categs) chart.legend = None chart.y_axis.majorGridlines = None chart.varyColors = True chart.title = "Olympic Gold medals in London" sheet.add_chart(chart, "A8") book.save("bar_chart.xlsx")
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 main(): book = Workbook() sheet = book.active rows = [("USA", 46), ("UK", 27), ("China", 26), ("Russia", 19), ("Germany", 17), ("Japan", 12)] for row in rows: sheet.append(row) data = Reference(sheet, min_row=1, min_col=2, max_row=6, max_col=2) categs = Reference(sheet, min_row=1, min_col=1, max_row=6) chart = BarChart() chart.add_data(data=data) chart.set_categories(categs) chart.legend = None chart.y_axis.majorGridlines = None chart.varyColors = True chart.title = 'Olympic Gold medals in Rio 2016' sheet.add_chart(chart, 'A8') book.save('bar_chart.xlsx')
def sheetThree(): ws = wb.create_sheet('Evaluation Summary') ruleSummaryRes = client.get_compliance_summary_by_config_rule() global column_num column_num = 3 ws.merge_cells('A1:D1') ws['A1'] = "Evaluation Summary(by config rule)" #border line comp = ruleSummaryRes['ComplianceSummary']['CompliantResourceCount'][ 'CappedCount'] noncomp = ruleSummaryRes['ComplianceSummary']['NonCompliantResourceCount'][ 'CappedCount'] #rows = [ # ('', '준수', '미준수', '전체'), # ('Rule count',comp,noncomp,comp+noncomp) #] excelData = ['AWS Config rule 개수', comp, noncomp, comp + noncomp] saveContent(excelData, ws) #for row in rows: # ws.append(row) bc = BarChart() bc.type = "col" bc.style = 10 bc.title = "Evaluation Summary" bc.y_axis.title = 'Rule Count' bc.x_axis.title = 'Compliant Status' data = Reference(ws, min_col=2, min_row=1, max_row=4, max_col=3) cats = Reference(ws, min_col=1, min_row=2, max_row=4) bc.add_data(data, titles_from_data=True) bc.set_categories(cats) bc.shape = 4 bc.width = 8 bc.legend = None ws.add_chart(bc, "A6") rownum = 0 while rownum < 3: ws.cell(row=rownum + 1, column=1).style = paintstyle ws.cell(row=rownum + 1, column=2).style = paintstyle ws.cell(row=rownum + 1, column=3).style = paintstyle ws.cell(row=rownum + 1, column=4).style = paintstyle rownum += 1 setTitleCell(ws.cell(row=1, column=1)) excelColName = ['', '준수', '미준수', '전체'] writeColName(excelColName, 2, ws) ws.row_dimensions[1].height = 25 ws.column_dimensions['A'].width = 20 ws.column_dimensions['B'].width = 15 ws.column_dimensions['C'].width = 15 print('Evaluation Summary sheet done')
def draw_bar2D( self, sheet_name, data_position=[], label_position=[], display_position="A10", title="Bar Chart", x_title="display_x", y_title="display_y", is_display_legend=False ): """ 数据必须为列。。。不知道为啥。。 """ ws = self.wb[sheet_name] # 数据所在列的坐标范围, 不包含label DATA_COL_MIN = data_position[0] DATA_COL_MAX = data_position[1] DATA_ROW_MIN = data_position[2] DATA_ROW_MAX = data_position[3] # label 所在范围 LABEL_COL_MIN = label_position[0] LABEL_COL_MAX = label_position[1] LABEL_ROW_MIN = label_position[2] LABEL_ROW_MAX = label_position[3] # 创建 chart 对象 chart1 = BarChart() # 竖直的柱状图"col" chart1.type = "col" chart1.style = 10 chart1.shape = 4 chart1.title = title chart1.y_axis.title = y_title chart1.x_axis.title = x_title data = Reference(ws, min_col=DATA_COL_MIN, max_col=DATA_COL_MAX, min_row=DATA_ROW_MIN, max_row=DATA_ROW_MAX) cats = Reference(ws, min_col=LABEL_COL_MIN, max_col=LABEL_COL_MAX, min_row=LABEL_ROW_MIN, max_row=LABEL_ROW_MAX) chart1.add_data(data, titles_from_data=True) chart1.set_categories(cats) # label , 柱状图上的数字 chart1.dLbls = DataLabelList() chart1.dLbls.showVal = True # 是否显示图例图例 if is_display_legend == False: chart1.legend = None # 显示位置 ws.add_chart(chart1, display_position)
def createEmptyChart(ws): for row in data: ws.append(row) values = Reference(ws, min_col=1, max_col=1, min_row=1, max_row=1) chart = BarChart() chart.add_data(values) chart.x_axis.allow_none = True chart.y_axis.allow_none = True chart.y_axis.majorGridlines = None chart.legend = None chart.x_axis.delete = True chart.y_axis.delete = True chart.title = 'Test' ws.add_chart(chart, 'A1')
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 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 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 create_bar_chart(sheet, start_row, size): bar_chart = BarChart() bar_chart.type = "col" bar_chart.style = 10 bar_chart.title = "Память квазициклов" bar_chart.y_axis.title = 'Память' bar_chart.x_axis.title = 'Квазициклы' data = Reference(sheet, min_col=2, min_row=start_row - 1, max_row=start_row + size) indexes = Reference(sheet, min_col=1, min_row=start_row, max_row=start_row + size) bar_chart.add_data(data, titles_from_data=True) bar_chart.set_categories(indexes) bar_chart.shape = 4 bar_chart.legend = None return bar_chart
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 avg_chart(): chart1 = BarChart() ws = wb["Past Data"] data = Reference(worksheet=ws, min_row=1, max_row=ws.max_row, min_col=2, max_col=2) cats = Reference(worksheet=ws, min_row=2, max_row=ws.max_row, min_col=1, max_col=1) # set the title of the chart chart1.title = f'Average Regen Timing' chart1.add_data(data, titles_from_data=True) chart1.legend = None chart1.set_categories(cats) count_line = LineChart() count = Reference(worksheet=ws, min_row=2, max_row=ws.max_row - 1, min_col=3) count_line.add_data(count) chart1 += count_line # set data labels and styles s1 = chart1.series[0] s1.dLbls = DataLabelList() s1.dLbls.showVal = True pt = DataPoint(idx=ws.max_row - 3) pt.graphicalProperties.solidFill = "ff9900" pt1 = DataPoint(idx=ws.max_row - 2) pt1.graphicalProperties.solidFill = "00ff00" s1.dPt.append(pt) s1.dPt.append(pt1) # print(type(ws.max_row)) wb["Regen Timing"].add_chart(chart1, "A1")
def excel_export(user_inputs, ten_table_data, bar_chart_x_axis_values, bar_chart_y_axis_values): wb = Workbook() # ---- create styles/formats to be used later ---- # for applicable numerical values comma_no_decimal_style = NamedStyle(name='comma_no_decimal_style') comma_no_decimal_style.number_format = '#,##0' comma_no_decimal_style.alignment = Alignment( horizontal='left') # left align like text is by default in Excel # for table header ten_table_header_style = NamedStyle(name='ten_table_header_style') ten_table_header_style.font = Font(bold=True) ten_table_header_style.fill = PatternFill(start_color='8DB4E2', end_color='8DB4E2', fill_type='solid') ten_table_header_style.border = Border(top=Side(border_style=BORDER_THIN, color='00000000'), bottom=Side( border_style=BORDER_THIN, color='00000000'), left=Side(border_style=BORDER_THIN, color='00000000'), right=Side(border_style=BORDER_THIN, color='00000000')) # for table title ten_table_title_style = NamedStyle(name='ten_table_title_style') ten_table_title_style.font = Font(size=18, bold=True) ten_table_title_style.alignment = Alignment(horizontal='center') # -----------------------INPUT TAB----------------------- ws_input = wb.active ws_input.title = 'input' # provide user selections for row, label in enumerate([ 'Rank Position', 'Rank By', 'State', 'City', 'Zip Code', 'Place of Service', 'Provider Type', 'Credential', 'HCPCS Code' ], start=1): ws_input.cell(column=1, row=row, value=label).font = Font(bold=True) ws_input.cell( column=2, row=1, value=', '.join(user_inputs['rank_position']) if 'rank_position' in user_inputs else '' ) # (rank position should always be in user inputs, since it's required) ws_input.cell( column=2, row=2, value=', '.join(user_inputs['rank_by']) if 'rank_by' in user_inputs else '') # (rank by should always be in user inputs, since it's required) ws_input.cell(column=2, row=3, value=', '.join(user_inputs['state']) if 'state' in user_inputs else '(all)') ws_input.cell(column=2, row=4, value=', '.join(user_inputs['city']) if 'city' in user_inputs else '(all)') ws_input.cell(column=2, row=5, value=', '.join(user_inputs['zip_code']) if 'zip_code' in user_inputs else '(all)') ws_input.cell(column=2, row=6, value=', '.join(user_inputs['place_of_service']) if 'place_of_service' in user_inputs else '(all)') ws_input.cell(column=2, row=7, value=', '.join(user_inputs['provider_type']) if 'provider_type' in user_inputs else '(all)') ws_input.cell(column=2, row=8, value=', '.join(user_inputs['credential']) if 'credential' in user_inputs else '(all)') ws_input.cell(column=2, row=9, value=', '.join(user_inputs['hcpcs_code']) if 'hcpcs_code' in user_inputs else '(all)') for col in ['A', 'B']: ws_input.column_dimensions[col].width = 20 # -----------------------DATA TAB----------------------- ws_data = wb.create_sheet(title='data') # ----provide table data---- for col, label in enumerate( ['Provider ID', 'Patients', 'Avg Charged', 'Avg Allowed', 'Avg Paid'], start=1): ws_data.cell(column=col, row=1, value=label) for row, data in enumerate(ten_table_data, start=2): ws_data.cell(column=1, row=row, value=data['provider_id']) ws_data.cell( column=2, row=row, value=int(data['patients'].replace(',', '')) ).style = comma_no_decimal_style # transforming string into number (so user can more easily perform calculations in Excel) and formatting ws_data.cell(column=3, row=row, value=int(data['avg_charged'].replace( ',', ''))).style = comma_no_decimal_style ws_data.cell(column=4, row=row, value=int(data['avg_allowed'].replace( ',', ''))).style = comma_no_decimal_style ws_data.cell(column=5, row=row, value=int(data['avg_paid'].replace( ',', ''))).style = comma_no_decimal_style # ----provide chart data---- ws_data.cell(column=8, row=1, value='Bar Chart') for row, x in enumerate(bar_chart_x_axis_values, start=2): ws_data.cell(column=8, row=row, value=x) for row, y in enumerate(bar_chart_y_axis_values, start=2): ws_data.cell(column=9, row=row, value=y).style = comma_no_decimal_style # set column width and bold headings for table & chart data for col in ['A', 'B', 'C', 'D', 'E', 'H', 'I']: ws_data.column_dimensions[col].width = 16 ws_data[f'{col}1'].font = Font(bold=True) # -----------------------RESULTS TAB----------------------- ws_results = wb.create_sheet(title='results') # ---- create and format table ---- ten_table_title = f"{user_inputs['rank_position'][0]} 10 Providers by {'Number of Patients' if user_inputs['rank_by'][0] == 'Patients' else 'Average Charged Amount'}" ws_results.merge_cells('A1:E1') ws_results.cell(column=1, row=1, value=ten_table_title).style = ten_table_title_style for col, label in enumerate( ['Provider ID', 'Patients', 'Avg Charged', 'Avg Allowed', 'Avg Paid'], start=1): ws_results.cell(column=col, row=2, value=label).style = ten_table_header_style for row in range(3, len(ten_table_data) + 3): ws_results.cell(column=1, row=row, value=f"=data!A{row-1}") ws_results.cell(column=2, row=row, value=f"=data!B{row-1}").style = comma_no_decimal_style ws_results.cell(column=3, row=row, value=f"=data!C{row-1}").style = comma_no_decimal_style ws_results.cell(column=4, row=row, value=f"=data!D{row-1}").style = comma_no_decimal_style ws_results.cell(column=5, row=row, value=f"=data!E{row-1}").style = comma_no_decimal_style ten_table = Table( displayName="TenTable", ref=f'A3:E{len(ten_table_data)+2}', headerRowCount=0 ) # didn't specify a header so that default column filters aren't created ten_table.tableStyleInfo = TableStyleInfo(name="TableStyleLight15", showRowStripes=True) ws_results.add_table(ten_table) for col in ['A', 'B', 'C', 'D', 'E']: ws_results.column_dimensions[col].width = 16 # ---- create bar chart ---- bar_chart = BarChart() bar_chart.type = 'col' bar_chart.style = 10 bar_chart.title = 'Total Patients by Average Charged Amount' bar_chart.y_axis.title = 'Patients' bar_chart.x_axis.title = 'Avg Charged' bar_chart.add_data(Reference(ws_data, min_col=9, max_col=9, min_row=2, max_row=len(bar_chart_y_axis_values) + 1), titles_from_data=False) bar_chart.set_categories( Reference(ws_data, min_col=8, max_col=8, min_row=2, max_row=len(bar_chart_x_axis_values) + 1)) bar_chart.shape = 4 bar_chart.legend = None bar_chart.height = 9 bar_chart.width = 18 ws_results.add_chart(bar_chart, 'H1') # ---- add footnotes ---- footnote1 = "Based on 2017 Medicare Provider Utilization and Payment Data accessed December 18, 2019 from data.cms.gov: https://data.cms.gov/Medicare-Physician-Supplier/Medicare-Provider-Utilization-and-Payment-Data-Phy/fs4p-t5eq." footnote2 = "Only providers listed as individuals and their services within the 50 states/DC are included. Please note that Provider ID is a generated number created by the developer in an effort to de-identify providers." footnote3 = "Additional data cleaning/transformations on the data set were performed as needed at the sole discretion of the developer." footnote4 = "The specific data shown in the above table & graph is based on user selections (see input tab) in the web application." ws_results.cell(column=1, row=20, value='Source Data:').font = Font(size=9, underline='single') for i, note in enumerate([footnote1, footnote2, footnote3], start=1): ws_results.cell(column=1, row=20 + i, value=note).font = Font(size=9) ws_results.cell(column=1, row=25, value=footnote4).font = Font( size=9 ) # writes a couple of lines down from the previous footnote in order to leave a blank line between this last footnote and the prior ones # set the results sheet (which is at index 2) as the active worksheet so that the user will be on this sheet when opening the file wb.active = 2 # the active worksheet is now the one at index 2 # save workbook to a stream (vs. saving to a file on disk) to later send to user excel_stream = io.BytesIO() wb.save(excel_stream) excel_stream.seek(0) # go to the beginning of the stream return excel_stream
[3, 25, 40], [4 ,30, 50], [5 ,10, 30], [6, 5, 25], [7 ,10, 50], ] for row in rows: ws3.append(row) chart = AreaChart3D() chart.title = "Area Chart" chart.style = 13 chart.x_axis.title = 'Test' chart.y_axis.title = 'Percentage' chart.legend = None cats = Reference(ws3, min_col=1, min_row=1, max_row=7) data = Reference(ws3, min_col=2, min_row=1, max_col=3, max_row=7) chart.add_data(data, titles_from_data=True) chart.set_categories(cats) ws3.add_chart(chart, "A10") ####################### ## STACKED BAR CHART ## ####################### from openpyxl import Workbook from openpyxl.chart import BarChart, Series, Reference
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])
def excel_create_chart(excel_file): """ 生成图表 :param excel_file: :return: """ wb = load_workbook(excel_file) print(f'{datetime.now()} | 信息 | 开始Excel图表渲染') # 进行净值走势图渲染 net_worth_sheet = wb['账户净值'] net_worth_chart_sheet = wb.create_chartsheet(title='净值走势图') chart1 = LineChart() dates1 = Reference(net_worth_sheet, min_col=1, min_row=2, max_row=len(net_worth_sheet['A'])) data1 = Reference(net_worth_sheet, min_col=3, min_row=1, max_row=len(net_worth_sheet['C'])) chart1.add_data(data1, titles_from_data=True) chart1.y_axis = NumericAxis(title='净值', majorTickMark='out') chart1.x_axis = TextAxis(majorTickMark='out', tickLblSkip=10, tickMarkSkip=10, noMultiLvlLbl=True, numFmt='yyyy-mm-dd') chart1.legend = None chart1.title = str() chart1.style = 1 chart1.set_categories(dates1) net_worth_chart_sheet.add_chart(chart1) # 进行权益走势图渲染 account_sheet = wb['账户统计'] account_chart_sheet = wb.create_chartsheet(title='权益走势图') dates2 = Reference(account_sheet, min_col=1, min_row=2, max_row=len(account_sheet['A'])) chart2 = LineChart(varyColors=True) data2 = Reference(account_sheet, min_col=8, min_row=1, max_row=len(account_sheet['H'])) chart2.add_data(data2, titles_from_data=True) chart2.y_axis = NumericAxis(title='权益', majorTickMark='out') chart2.legend = None chart2.set_categories(dates2) chart3 = BarChart() data3 = Reference(account_sheet, min_col=10, min_row=1, max_row=len(account_sheet['J'])) chart3.add_data(data3, titles_from_data=True) chart3.y_axis = NumericAxis(axId=200, title='风险度', majorGridlines=None, majorTickMark='out', crosses='max') chart3.x_axis = TextAxis(majorTickMark='out', tickLblSkip=10, tickMarkSkip=10, noMultiLvlLbl=True, numFmt='yyyy-mm-dd') chart3.legend = None chart3.set_categories(dates2) chart2 += chart3 account_chart_sheet.add_chart(chart2) # 进行交易分布图的渲染 categories_analysis_sheet = wb['交易分析(按品种)'] trading_frequency_analysis_sheet = wb.create_chartsheet(title='交易分布图') labels = Reference(categories_analysis_sheet, min_col=1, min_row=2, max_row=len(categories_analysis_sheet['A'])) chart4 = PieChart(varyColors=True) chart4.style = 34 data4 = Reference(categories_analysis_sheet, min_col=4, min_row=2, max_row=len(categories_analysis_sheet['D'])) chart4.add_data(data4) chart4.set_categories(labels) chart4.legend = None # chart4.series[0].data_points = [DataPoint(idx=i, explosion=8) # for i in range(len(categories_analysis_sheet['D']) - 1)] chart4.series[0].dLbls = DataLabelList(dLblPos='bestFit', showPercent=True, showCatName=True, showVal=True, showLeaderLines=True) chart4.layout = Layout(manualLayout=ManualLayout( x=0, y=0, h=0.75, w=0.75, xMode='factor', yMode='factor')) trading_frequency_analysis_sheet.add_chart(chart4) # 进行品种盈亏图的渲染 categories_win_and_loss_chart_sheet = wb.create_chartsheet(title='品种盈亏图') chart5 = BarChart(barDir='col') chart5.style = 18 data5 = Reference(categories_analysis_sheet, min_col=2, min_row=2, max_row=len(categories_analysis_sheet['B'])) chart5.add_data(data5) chart5.set_categories(labels) chart5.legend = None chart5.series[0].dLbls = DataLabelList(showVal=True) chart5.y_axis = NumericAxis(title='平仓盈亏', majorTickMark='out', minorTickMark='out') categories_win_and_loss_chart_sheet.add_chart(chart5) # 进行交易盈亏图的渲染 trading_win_and_loss_chart_sheet = wb.create_chartsheet(title='交易盈亏图') chart6 = RadarChart() chart6.style = 24 data6 = Reference(categories_analysis_sheet, min_col=8, max_col=9, min_row=1, max_row=categories_analysis_sheet.max_row) chart6.add_data(data6, titles_from_data=True) chart6.set_categories(labels) trading_win_and_loss_chart_sheet.add_chart(chart6) # 图表保存 wb.save(excel_file) wb.close() # 输出信息 print(f'{datetime.now()} | 信息 | 已生成Excel图表')
def exp_excel(cls, aa, file, norm='hist_raw', additional_info=None): """ Export results in an Excel file. Parameters ---------- aa: ArrayAnalyse instant norm: str evaluation strategy selection (see ArrayAnalyse.evaluate) file: file can be a path to a file (a string), a path-like object, or a file-like object additional_info: List[Dict] list with dictionaries containing 'name' and 'value' key """ if not aa.is_finalized: warnings.warn( 'Data collection needs to be finalized to generate a report.', RuntimeWarning) return None wb = Workbook() highlight = NamedStyle(name="highlight") highlight.font = Font(bold=True, size=20) wb.add_named_style(highlight) bold = NamedStyle(name="bold") bold.font = Font(bold=True) wb.add_named_style(bold) bold_right = NamedStyle(name="bold_right") bold_right.font = Font(bold=True) bold_right.alignment = Alignment(horizontal='right') wb.add_named_style(bold_right) bold_center = NamedStyle(name="bold_center") bold_center.font = Font(bold=True) bold_center.alignment = Alignment(horizontal='center') wb.add_named_style(bold_center) # Overview worksheet ws = wb.active alignment_img = opImage(config.template_folder / 'logo.png') ws.add_image(alignment_img, f"B1") ws['E4'] = f'proMAD' ws['E5'] = config.version ws['E4'].style = highlight ws.title = "Overview" data = aa.evaluate(norm=norm) row_offset = 12 column_offset = 2 if cls.norm_return[norm] == ('values', ): for entry in data: ws.cell(column=entry['position'][1] + column_offset, row=entry['position'][0] + row_offset, value=entry['value'][-1]) else: for entry in data: ws.cell(column=entry['position'][1] + column_offset, row=entry['position'][0] + row_offset, value=entry['value'][0]) for column in range(sum(aa.array_data['net_layout_x'])): ws.cell(column=column + column_offset, row=row_offset - 1, value=column + 1).style = bold_center for row in range(sum(aa.array_data['net_layout_y'])): ws.cell(column=column_offset - 1, row=row + row_offset, value=get_column_letter(row + 1)).style = bold_right alignment_img_raw = io.BytesIO() aa.figure_alignment(file=alignment_img_raw) alignment_img = opImage(alignment_img_raw) ws[f"B{sum(aa.array_data['net_layout_y']) + row_offset + 1}"] = 'Fig. 1: Overview and alignment check' ws.add_image(alignment_img, f"B{sum(aa.array_data['net_layout_y'])+row_offset+2}") # Result worksheet ws = wb.create_sheet() ws.title = "Results" new_rows = [] ref_spots = [] data = aa.evaluate(norm=norm, double_spot=True) for entry in data: pos = aa.get_position_string(entry['position']) if not isinstance(pos, str): pos = ", ".join(pos) if 'Reference' in entry['info'][0] or 'Negative Controls' in entry[ 'info'][0]: ref_spots.append([entry['info'][0], pos, entry['value']]) else: new_rows.append([entry['info'][0], pos, entry['value']]) ws['A1'] = 'Membrane Results' ws['A1'].style = highlight ws.append([]) ws.append(('Name', 'Position', 'Value')) ws['A3'].style = bold ws['B3'].style = bold ws['C3'].style = bold_right data_start = 5 + len(ref_spots) for row in sorted(ref_spots, key=lambda s: s[2], reverse=True): ws.append(row) ws.append([]) for row in sorted(new_rows, key=lambda s: s[2], reverse=True): ws.append(row) ws.column_dimensions['A'].width = 20 ws.column_dimensions['C'].width = 12 cutoff = max(min(len(new_rows), 15), int(len(new_rows) * 0.2)) values = Reference(ws, min_col=3, min_row=data_start, max_col=3, max_row=cutoff + data_start) categories = Reference(ws, min_col=1, min_row=data_start, max_col=1, max_row=cutoff + data_start) chart = BarChart() chart.add_data(values) chart.set_categories(categories) chart.y_axis.title = cls.norm_unit[norm][0].upper( ) + cls.norm_unit[norm][1:] chart.title = f'Top spots' chart.width = 30 chart.height = 15 chart.gapWidth = 25 chart.legend = None ws.add_chart(chart, "E3") # Result details worksheet ws = wb.create_sheet() ws.title = "Result details" new_rows = [] data = aa.evaluate(norm=norm) for entry in data: new_rows.append( [entry['info'][0], aa.get_position_string(entry['position'])] + [v for v in entry['value']]) ws['A1'] = 'Membrane Result Details' ws['A1'].style = highlight ws.append([]) ws.append(['Name', 'Position'] + [head.title() for head in cls.norm_return[norm]]) ws['A3'].style = bold ws['B3'].style = bold for n in range(len(cls.norm_return[norm])): ws[get_column_letter(n + 3) + '3'].style = bold_right for row in sorted(new_rows, key=lambda s: s[0].lower()): ws.append(row) ws.column_dimensions['A'].width = 20 for n in range(len(new_rows[0]) - 2): ws.column_dimensions[get_column_letter(n + 3)].width = 12 # Info worksheet ws = wb.create_sheet() ws.title = "Info" ws['A1'] = f'Technical data' ws['A1'].style = highlight detail_values, detail_names = cls.get_details(aa, norm) info_list = [ 'date', 'time', 'skip', 'program', 'version', 'url', 'skip', 'array_name', 'array_type', 'array_id', 'norm_name', 'norm', 'norm_description', 'unit' ] tech_data_list = [] for key in info_list: if key == 'skip': tech_data_list.append(('', '')) else: tech_data_list.append((detail_names[key], detail_values[key])) if additional_info: tech_data_list += [('', '')] + [(entry['name'], entry['value']) for entry in additional_info] ws.column_dimensions['A'].width = 15 ws.column_dimensions['B'].width = 15 for n, content in enumerate(tech_data_list): ws.cell(column=1, row=n + 3, value=content[0]).style = bold_right ws.cell(column=2, row=n + 3, value=content[1]) if isinstance(file, os.PathLike) or isinstance(file, str): wb.save(file) elif isinstance(file, (io.RawIOBase, io.BufferedIOBase)): with NamedTemporaryFile() as tmp: wb.save(tmp.name) tmp.seek(0) file.write(tmp.read())
book.save("./data/meltop100.xlsx") # Trythis 3-1번 book = openpyxl.load_workbook("./data/meltop100.xlsx") sheet3 = book.create_sheet() sheet3.title = "차트출력" datax = Reference(sheet1, min_col=4, min_row=2, max_col=4, max_row=11) categs = Reference(sheet1, min_col=2, min_row=2, max_row=11) chart1 = BarChart() chart1.add_data(datax) chart1.set_categories(categs) chart1.legend = None # 범례 chart1.varyColors = True chart1.title = "상위 10위 좋아요수" sheet3.add_chart(chart1, "A8") # Trythis 3-2번 chart2 = ScatterChart() chart2.style = 13 datax = Reference(sheet1, min_col=1, min_row=2, max_row=11) value = Reference(sheet1, min_col=5, min_row=1, max_row=11) series = Series(value, datax, title_from_data=True) chart2.series.append(series) categs = Reference(sheet1, min_col=2, min_row=2, max_row=11) # chart1.add_data(datax)
def regen_time_tab(): youtec_df = trouble_report.youtec() df1 = pd.merge(clean, youtec_df, on='Date', how='left').replace(np.nan, "") df1 = df1[[ 'Date', 'Line', 'Hours', 'Target', 'Program', 'Disk Type', 'Duration', 'CVD Change', 'Activities', 'Leakages' ]] start_row = 16 # # ws = wb.active ws = wb.create_sheet("Regen Timing", 0) ws.freeze_panes = 'A17' ws.sheet_view.showGridLines = False ws.title = "Regen Timing" listy = dataframe_to_rows(df1, index=False, header=True) for row, row_entries in enumerate(listy, start=16): for column, value in enumerate(row_entries, start=1): ws.cell(column=column, row=row, value=value) # for r in dataframe_to_rows(df1, index=False, header=True): # ws.append(r) for cell in ws[start_row]: cell.font = Font(bold=True) cell.alignment = Alignment(horizontal="center") cell.fill = PatternFill(start_color="feedc6", end_color="feedc6", fill_type="solid") # merge # for i in range(start_row, ws.max_row+1): # ws.merge_cells(start_row=i, start_column=9, end_row=i, # end_column=16) # border BORDER_LIST = [f'A{x}:J{x}' for x in range(start_row, ws.max_row + 1)] for pos in BORDER_LIST: set_border(ws, pos) # center characters for row_cells in ws.iter_rows(min_row=start_row, max_row=ws.max_row, min_col=1, max_col=2): for cell in row_cells: cell.alignment = Alignment(horizontal='center', vertical='center') for row_cells in ws.iter_rows(min_row=start_row, max_row=ws.max_row, min_col=3, max_col=7): for cell in row_cells: cell.alignment = Alignment(horizontal='right', vertical='center') for row_cells in ws.iter_rows(min_row=start_row, max_row=ws.max_row, min_col=8, max_col=8): for cell in row_cells: cell.alignment = Alignment(horizontal='center', vertical='center') for row_cells in ws.iter_rows(min_row=start_row, max_row=ws.max_row, min_col=9, max_col=10): for cell in row_cells: cell.alignment = Alignment(vertical='center', wrapText=True) # cell.style.alignment.wrap_text = True ws.column_dimensions['G'].width = 10 ws.column_dimensions['H'].width = 16 ws.column_dimensions['I'].width = 41 ws.column_dimensions['J'].width = 41 chart1 = BarChart() data = Reference(worksheet=ws, min_row=start_row + 1, max_row=ws.max_row, min_col=3, max_col=3) cats = Reference(ws, min_row=start_row + 1, max_row=ws.max_row, min_col=1, max_col=2) # set the title of the chart chart1.title = f'{config.month_name} Regen Timing' chart1.add_data(data, titles_from_data=False) chart1.legend = None chart1.set_categories(cats) # Style the lines s1 = chart1.series[0] s1.graphicalProperties.solidFill = "ff4400" target_line = LineChart() target = Reference(worksheet=ws, min_row=start_row + 1, max_row=ws.max_row, min_col=4) target_line.add_data(target) chart1 += target_line ws.add_chart(chart1, "I1") print('done')
def draw_chart(ws, type_chart, position, title, min_col_data, min_row_data, max_col_data, max_row_data, style=2, legend=None, x_title=None, y_title=None, min_col_cat=None, min_row_cat=None, max_col_cat=None, max_row_cat=None, width=None, height=None): if type_chart == 'bar': chart = BarChart() elif type_chart == 'line': chart = LineChart() elif type_chart == 'pie': chart = PieChart() else: print('Enter bar, line or pie!') # default chart chart = BarChart() if type_chart != 'pie': chart.x_axis.title = x_title chart.y_axis.title = y_title if legend is None: chart.legend = legend if (width and height) is not None: chart.width = width chart.height = height chart.style = style chart.title = title data = Reference(ws, min_col=min_col_data, min_row=min_row_data, max_col=max_col_data, max_row=max_row_data) chart.add_data(data) if (min_col_cat and min_row_cat and max_col_cat and max_row_cat) is not None: cats = Reference(ws, min_col=min_col_cat, min_row=min_row_cat, max_col=max_col_cat, max_row=max_row_cat) chart.set_categories(cats) ws.add_chart(chart, position)
imgFile = '../crawl/images/aaa.png' img = openpyxl.drawing.image.Image(imgFile) sheet.add_image(img, 'B5') img2 = Image.open(imgFile) new_img = img2.resize((30, 30)) new_img.save('aaa.png') img3 = openpyxl.drawing.image.Image('aaa.png') sheet.add_image(img3, 'D1') img4 = openpyxl.drawing.image.Image('aaa.png') sheet.add_image(img4, 'D2') # chart sheet1 = book.worksheets[0] data = Reference(sheet1, min_col=5, min_row=2, max_col=5, max_row=11) cates = Reference(sheet1, min_col=1, min_row=2, max_row=11) chart = BarChart() chart.add_data(data=data) chart.set_categories(cates) chart.legend = 'Legend' # chart.y_axis.majorGridlines = None chart.varyColors = True chart.title = "Top 10 Likes" sheet.add_chart(chart, "A8") book.save(file)
def create_chart(FILENAME, TABLE_FILENAME): SMPL = find_smpl_number(FILENAME) true_smpl_name, median_size = find_true_smpl_name(TABLE_FILENAME, SMPL) #os.chdir("files") wb = load_workbook('%s' % FILENAME) ws = wb.active max_size = [] end = False current_row = 5 final_row = 4 rows = [] beginning = True subsequent_zeros = 0 while (end == False): if subsequent_zeros > 4: end = True d = ws.cell(row=current_row, column=2) if (d.value != None): if (d.value == 0 and beginning == True): current_row += 1 continue else: beginning = False new_row = [] e = ws.cell(row=current_row, column=1) new_row.append(e.value) new_row.append(d.value) rows.append(new_row) if (d.value == 100): max_size.append(e.value) final_row += 1 current_row += 1 if (d.value == 0): subsequent_zeros += 1 else: end = True print(final_row) print(rows) wb2 = Workbook() ws2 = wb2.active ws2['A1'] = "size" ws2['B1'] = "frequency" for row in rows: ws2.append(row) chart1 = BarChart() # chart1.type = "col" chart1.style = 3 chart1.title = '%s' % true_smpl_name chart1.y_axis.title = 'normalized frequency / %' chart1.x_axis.title = 'size / nm' data = Reference(ws2, min_col=2, min_row=2, max_row=final_row - 4, max_col=2) cats = Reference(ws2, min_col=1, min_row=2, max_row=final_row - 4, max_col=1) chart1.add_data(data, titles_from_data=True) chart1.set_categories(cats) chart1.shape = 3 chart1.legend = None chart1.y_axis.scaling.max = 100 chart1.height = 15 chart1.width = 25 chart1.x_axis.delete = False chart1.y_axis.delete = False ws2.add_chart(chart1, "D10") print(cats) ws2['D3'] = "median size / nm" ws2['D4'] = median_size FILENAME = FILENAME.rstrip('.xlsx') wb2.save("{}_{}_CHART.xlsx".format(FILENAME, true_smpl_name)) true_smpl_name = group_smpl_by_content(true_smpl_name) size = [max_size, median_size] return true_smpl_name, size
break sheet.cell(row=row_number, column=1).value = key if data[key]: sheet.cell(row=row_number, column=2).value = data[key] else: sheet.cell(row=row_number, column=2).value = 0 row_number += 1 chartEndRow = row_number - 1 row_number += 1 # グラフ生成 chart = BarChart() chart.title = master_column chart.legend = None titles = Reference( sheet, min_col=1, min_row=chartStartRow, max_row=chartEndRow) values = Reference( sheet, min_col=2, min_row=chartStartRow, max_col=2, max_row=chartEndRow) chart.add_data(values) chart.set_categories(titles) chartPosition = 1 + i * 20 #1,21,41,... sheet.add_chart(chart, f"E{chartPosition}") book.save(excel_bookname)
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 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")
wb = Workbook() ws = wb.active rows = [['Number', 'Batch 1', 'Batch 2'], #column headings [2, 40, 30], #Row 2 [3, 40, 25]] for row in rows: ws.append(row) chart = AreaChart() #Create chart. AreaChart3D() for 3D chart.title = "Area Chart" #title of chart chart.style = 13 # chart.x_axis.title = 'Test' #title of x-axis chart.y_axis.title = 'Percentage' #title of y-axis chart.legend = None #for the legend cats = Reference(ws, min_col=1, min_row=1, max_row=7) data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=7) chart.add_data(data, titles_from_data=True) chart.set_categories(cats) ws.add_chart(chart, "A10") #Bar Charts from openpyxl.chart import BarChart, Series, Reference wb = Workbook(write_only=True) ws = wb.create_sheet() rows = [('Number', 'Batch 1', 'Batch 2'), (2, 10, 30)]
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
img = openpyxl.drawing.image.Image(imgFile) col = ((i + 9) // 10) * 2 - 1 row = (7 * i - 6) % 70 + 1 tmpCell = ws2.cell(row=row - 1, column=col) tmpCell.value = "Top{} Image".format(i) ws2.add_image(img, '{}{}'.format(chr(col + 64), row)) print("OK === >> {}".format(i)) dat = Reference(ws, min_col=4, min_row=2, max_col=4, max_row=11) cat = Reference(ws, min_col=2, min_row=2, max_col=2, max_row=11) barChart = BarChart() barChart.add_data(data=dat) barChart.set_categories(cat) barChart.legend = None # 범례 barChart.varyColors = True barChart.title = "Top10 Likes" ws3.add_chart(barChart, "D1") scatChart = ScatterChart() scatChart.style = 13 scatChart.x_axis.title = 'SongName' scatChart.y_axis.title = 'DiffLikes' xvalues = Reference(ws, min_col=1, min_row=2, max_row=11) values = Reference(ws, min_col=5, min_row=1, max_row=11) series = Series(values, xvalues, title_from_data=True) scatChart.series.append(series)