コード例 #1
0
    def bar(self):
        from openpyxl.chart import BarChart3D, Series, Reference
        wb = load_workbook(path)
        ws = wb['Sheet0']

        chart1 = BarChart3D()
        #chart1.type = "col"
        #chart1.style = 12
        chart1.title = "EXCEL DATA"
        chart1.y_axis.title = 'Marks'
        chart1.x_axis.title = 'Student'
        #print(col)
        bar_r = ws.max_row
        bar_c = ws.max_column
        print("row: ", bar_r)
        print("col: ", bar_c)
        if bar_r <= 2:
            bar_r = 3
        self.data = Reference(ws,
                              min_col=4,
                              min_row=abs(bar_r - 2),
                              max_row=bar_r,
                              max_col=bar_c)

        chart1.add_data(self.data, titles_from_data=True)
        #chart1.set_categories(cats)

        #chart1.shape = 4
        ws.add_chart(chart1, "J15")
        wb.save(path)
コード例 #2
0
def price_updater(filename):
    wb = xl.load_workbook(filename)
    sheet = wb["Sheet1"]

    # cell = sheet["a1"]
    cell = sheet.cell(1, 1)
    print(cell.value)
    # print(sheet.max_row)

    for row in range(2, sheet.max_row + 1):
        print(row)
        cell = sheet.cell(row, 3)
        print(cell.value)
        corrected_price = (cell.value) * (0.9)
        corrected_price_cell = sheet.cell(row, 4)
        corrected_price_cell.value = corrected_price

    cell_updated = sheet.cell(1, 4)
    cell_updated.value = "Updated"

    values = Reference(sheet,
                       min_row=2,
                       max_row=sheet.max_row,
                       min_col=4,
                       max_col=4)

    chart = BarChart3D()
    chart.add_data(values)
    sheet.add_chart(chart, "e2")

    wb.save(filename)
コード例 #3
0
ファイル: jekxlsx.py プロジェクト: jackysupit/testctp
    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
コード例 #4
0
def process_workbook(filename):
    wb = xl.load_workbook(filename)
    sheet = wb['Sheet1']
    # cell = sheet['a1']
    # cell = sheet.cell(1, 1)
    # print(cell.value)
    # print(sheet.max_row)

    for row in range(2, sheet.max_row + 1):
        # print(row)
        cell = sheet.cell(row, 3)
        # print(cell.value)
        corrected_price = cell.value * 0.9
        corrected_price_cell = sheet.cell(row, 4)
        corrected_price_cell.value = corrected_price

    values = Reference(sheet,
                       min_row=2,
                       max_row=sheet.max_row,
                       min_col=4,
                       max_col=4)

    chart = BarChart3D()
    chart.add_data(values)
    sheet.add_chart(chart, "e2")

    wb.save(filename)
コード例 #5
0
def set_home_page(sheet, num_stocks):
    titles = Reference(sheet, min_col=1, min_row=2, max_row=num_stocks+1, max_col=1)
    r2_val = Reference(sheet, min_col=2, min_row=1, max_col=2, max_row=num_stocks+1)
    score_val = Reference(sheet, min_col=3, min_row=1, max_col=3, max_row=num_stocks+1)

    chart_1 = create_chart(BarChart3D(), 'Stock names', 'R2_Averages', titles, r2_val, "Model", "b")
    chart_2 = create_chart(BarChart3D(), 'Stock names', 'Scores_Averages', titles, score_val, "Model", "r")
    chart_1.width = 40  # default is 18.5
    chart_2.width = 40  # default is 18.5
    sheet.add_chart(chart_1, "F2")
    sheet.add_chart(chart_2, "F25")

    tab = Table(displayName="Table1", ref=f"A1:C{num_stocks+1}")
    # Add a default style with striped rows and banded columns
    style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False, showLastColumn=False, showRowStripes=True,
                           showColumnStripes=True)
    tab.tableStyleInfo = style
    sheet.add_table(tab)
コード例 #6
0
def process_workbook(stock_names, predictions, prices, r2_scores, scores, score_avgs, r2_avgs):
    # Creating the xl file with the analyzed data
    wb = Workbook()
    ws = wb.create_sheet('Home-Page')
    ws.append(['Stock', 'R2_avgs', 'Scores_avgs'])
    for i, stock in enumerate(stock_names):
        ws1 = wb.create_sheet(stock)
        ws1.append(['Days', 'Values', 'R2_Scores', 'My_Scores'])
        ws1.append(['Price', prices[i], 1, 0])
        for j, raw in enumerate(preds):
            ws1.append([raw, predictions[j][i], r2_scores[j][i], scores[j][i]])

        # Creating and adding the charts of the analyzed data
        titles = Reference(ws1, min_col=1, min_row=2, max_row=12, max_col=1)
        data_val = Reference(ws1, min_col=2, min_row=1, max_col=2, max_row=12)
        data_r2_score = Reference(ws1, min_col=3, min_row=1, max_col=3, max_row=12)
        data_my_score = Reference(ws1, min_col=4, min_row=1, max_col=4, max_row=12)
        chart_1 = create_chart(AreaChart3D(), 'Predictions', 'Values', titles, data_val, stock, "y")
        chart_2 = create_chart(BarChart3D(), 'Predictions', 'R2_Scores', titles, data_r2_score, stock, "b")
        chart_3 = create_chart(BarChart3D(), 'Predictions', 'My_Scores', titles, data_my_score, stock, "r")
        ws1.add_chart(chart_1, "F2")
        ws1.add_chart(chart_2, "F25")
        ws1.add_chart(chart_3, "P2")

        # Add a table
        tab = Table(displayName="Table1", ref="A1:D12")
        style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=True)
        tab.tableStyleInfo = style
        ws1.add_table(tab)

        # Add hyperlinks with font between the current sheet to the home page sheet
        ft = Font(color=colors.RED)
        ws1.cell(row=len(preds) + 4, column=2).value = "Home-Page"
        ws1.cell(row=len(preds) + 4, column=2).font = ft
        ws1.cell(row=len(preds) + 4, column=2).hyperlink = '#\'Home-Page\'!A1'
        ws.append(['  '+stock_names[i], r2_avgs[i], score_avgs[i]])
        ws.cell(row=i+2, column=1).hyperlink = f'#{stock_names[i]}!A1'
        ws.cell(row=i + 2, column=1).font = ft

    std = wb.get_sheet_by_name('Sheet')
    wb.remove_sheet(std)
    set_home_page(ws, len(stock_names))
    wb.save(f"xl_Static_analyzes/Data_Anlaz_{today}.xlsx")
コード例 #7
0
ファイル: GraphCreation.py プロジェクト: shreyjp5788/Python
    def create_graph(self):
        chart = BarChart3D()

        data = Reference(self._ws, min_row=1, min_col=4, max_row=22, max_col=5)
        categories = Reference(self._ws, min_row=2, min_col=2, max_row=22)
        chart.add_data(data, titles_from_data=True)
        chart.set_categories(categories)
        chart.style = 10
        chart.title = "SiGFP Summary Report"
        return chart
コード例 #8
0
ファイル: smells_analyzer.py プロジェクト: samerd/wvu-thesis
def _add_chart(work_sheet, cols, rows, title, pos):
    chart = BarChart3D()
    chart.title = title
    for col in cols:
        data = Reference(work_sheet, min_col=col, min_row=1, max_row=rows)
        chart.add_data(data, titles_from_data=True)
    categories = Reference(work_sheet, min_col=1, min_row=2, max_row=rows)
    chart.set_categories(categories)
    chart.shape = 'box'
    work_sheet.add_chart(chart, "B%s" % pos)
コード例 #9
0
def in_excel(data_list, result_file, sheet_name, col_num, col1, col2, title):
    #打开文件
    if os.path.exists(result_file):
        wb = load_workbook(result_file)
    else:
        wb = Workbook()
    ws = wb.active
    #读sheet页
    sheet_list = wb.sheetnames  #表里面有哪些sheet页
    print sheet_list
    # sheet = wb.get_sheet_by_name("Sheet3")
    # print(sheet["C"])    # (<Cell Sheet3.C1>, <Cell Sheet3.C2>, <Cell Sheet3.C3>, <Cell Sheet3.C4>, <Cell Sheet3.C5>, <Cell Sheet3.C6>, <Cell Sheet3.C7>, <Cell Sheet3.C8>, <Cell Sheet3.C9>, <Cell Sheet3.C10>)      <-第C列
    # print(sheet["4"])    # (<Cell Sheet3.A4>, <Cell Sheet3.B4>, <Cell Sheet3.C4>, <Cell Sheet3.D4>, <Cell Sheet3.E4>)     <-第4行
    # print(sheet["C4"].value)    # c4     <-第C4格的值
    # print(sheet.max_row)    # 10     <-最大行数
    # print(sheet.max_column)    # 5     <-最大列数
    #写入sheet页
    if sheet_name not in sheet_list:
        sheet_request = wb.create_sheet(sheet_name)
    else:
        sheet_request = wb[sheet_name]
    # rows = data_list
    # for row in rows:
    #     sheet_request.append(row)
    for i in range(len(data_list)):
        # print i[1],data_list[i][1]
        # print start_row,start_row + data_list[i][1]-1
        # sheet_request['A' + str(start_row)]=data_list[i][0]
        sheet_request[col1 + str(i + 1)] = data_list[i][0]
        sheet_request[col2 + str(i + 1)] = data_list[i][1]
    # 删除sheet
    # wb.remove('Sheet')
    # wb.remove_sheet('Sheet')
    #插入图表
    data = Reference(sheet_request,
                     min_col=col_num,
                     min_row=1,
                     max_col=col_num,
                     max_row=15)
    # titles = Reference(sheet_request, min_col=1, min_row=2, max_row=10)
    titles = Reference(sheet_request, min_col=title, min_row=2, max_row=15)
    chart = BarChart3D()
    chart.title = "用户行为分析"
    chart.add_data(data=data, titles_from_data=True)
    chart.set_categories(titles)
    sheet_request.add_chart(chart, "E5")
    #保存
    wb.save(result_file)
コード例 #10
0
 def makechart(self, title, pos, width, height, col1, row1, col2, row2,
               col3, row3, row4):
     data = Reference(self.ws,
                      min_col=col1,
                      min_row=row1,
                      max_col=col2,
                      max_row=row2)
     cat = Reference(self.ws, min_col=col3, min_row=row3, max_row=row4)
     chart = BarChart3D()
     chart.title = title
     chart.width = width
     chart.height = height
     chart.add_data(data=data, titles_from_data=True)
     chart.set_categories(cat)
     self.ws.add_chart(chart, pos)
     self.wb.save(self.filename)
コード例 #11
0
ファイル: excel.py プロジェクト: suda-morris/TV-AutoTester
    def make_bar_chart(self):
        # self.__wb = load_workbook(self.__filename)
        sheetnames = self.__wb.get_sheet_names()
        # ws = self.__wb.get_sheet_by_name(sheetnames[0])
        ws_new = self.__wb.create_sheet("bar chart")
        max_r = self.__ws.max_row - 3

        data = Reference(self.__ws, min_col=3, min_row=1, max_col=4, max_row=max_r)
        titles = Reference(self.__ws, min_col=1, min_row=2, max_row=max_r)
        chart = BarChart3D()
        chart.title = "Test Result Bar Chart"
        chart.add_data(data=data, titles_from_data=True)
        chart.set_categories(titles)
        chart.x_axis.title = ' '

        ws_new.add_chart(chart, "A1")
        self.__wb.save(self.__filename)
コード例 #12
0
def process_workbook(filename):
    wb = xl.load_workbook(filename)
    sheet = wb["Sheet1"]

    for row in range(1, sheet.max_row + 1):
        cell = sheet.cell(row, 1)
        enter_value = cell.value * 10
        enter_value_cell = sheet.cell(row, 2)
        enter_value_cell.value = enter_value
        values = Reference(sheet,
                           min_row=1,
                           max_row=sheet.max_row,
                           min_col=2,
                           max_col=2)
        chart = BarChart3D()
        chart.add_data(values)
        sheet.add_chart(chart, "a15")
        wb.save(filename)
コード例 #13
0
def plot_bar_chart3_d():
    wb = xl.load_workbook('C:\\Resources\\Program_Xl_Sheets\\Groceriesptfinal.xlsx')  # load xl workbook
    sheet = wb['dATA']  # get the worksheet
    for i in range(2, 10):
        cell = sheet['f'+str(i)]
        sheet['a'+str(i)] = cell.value
    values = Reference(
        sheet,
        min_row=2,
        max_row=10,
        min_col=1,
        max_col=1
    )
    series_object = xl.chart.Series(values, title="Yearly Revenue")
    chart = BarChart3D()
    chart.append(series_object)
    sheet.add_chart(chart, 'j2')
    wb.save('C:\\Resources\\Program_Xl_Sheets\\Groceriesptfinal.xlsx')
コード例 #14
0
ファイル: project.py プロジェクト: 99003755/Python_Project
    def bar(self):
        from openpyxl.chart import BarChart3D, Reference
        b_wb = load_workbook(self.path)
        ws = b_wb['Sheet0']

        chart1 = BarChart3D()
        # adding title, x-axis and y-axis to the bar chart
        chart1.title = "EXCEL DATA"
        chart1.y_axis.title = 'Marks'
        chart1.x_axis.title = 'Student'
        bar_r = ws.max_row
        bar_c = ws.max_column
        if bar_r <= 2:
            bar_r = 3
        e_data = Reference(ws, min_col=4, min_row=abs(bar_r-2), max_row=bar_r, max_col=bar_c)

        chart1.add_data(e_data, titles_from_data=True)
        ws.add_chart(chart1, "J15")
        b_wb.save(self.path)
コード例 #15
0
def bar_3d():
    wb = Workbook()
    ws = wb.active

    rows = [(None, 2013, 2014), ("Apples", 5, 4), ("Oranges", 6, 2),
            ("Pears", 8, 3)]

    for row in rows:
        ws.append(row)

    data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=4)
    titles = Reference(ws, min_col=1, min_row=2, max_row=4)
    chart = BarChart3D()
    chart.title = "3D Bar Chart"
    chart.add_data(data=data, titles_from_data=True)
    chart.set_categories(titles)

    ws.add_chart(chart, "E5")
    wb.save("out/bar3d.xlsx")
コード例 #16
0
ファイル: Excel Reader.py プロジェクト: ariannaghibi/Python
def sales_func(filename):
    # Load the file and the sheet tab
    wb = xl.load_workbook(filename)
    sheet = wb['Sheet1']
    # Create a new price column based on the original price
    for row in range(2, sheet.max_row + 1):
        cell = sheet.cell(row, 3)
        corrected_price = cell.value * 0.9
        corrected_price_cell = sheet.cell(row, 4)
        corrected_price_cell.value = corrected_price

    # Bar chart
    values = Reference(sheet,
                       min_row=1,
                       max_row=sheet.max_row,
                       min_col=3,
                       max_col=4)
    transaction_id = Reference(sheet,
                               min_row=2,
                               max_row=sheet.max_row,
                               min_col=1,
                               max_col=1)
    chart = BarChart3D()
    chart.title = '3D Bar Chart'
    chart.x_axis.title = "Transaction ID"
    chart.y_axis.title = "Adjusted Price ($)"
    chart.add_data(values, titles_from_data=True)
    chart.set_categories(transaction_id)
    sheet.add_chart(chart, 'E2')

    # Pie chart
    quantities = Reference(sheet, min_row=1, max_row=5, min_col=13, max_col=13)
    labels = Reference(sheet, min_row=2, max_row=5, min_col=12, max_col=12)
    chart2 = PieChart()
    chart2.title = 'Pie Chart'
    chart2.add_data(quantities, titles_from_data=True)
    chart2.set_categories(labels)
    sheet.add_chart(chart2, 'N2')

    wb.save(filename)
コード例 #17
0
def excelsheet_read_processor(excelfilename):
    path = pathlib.Path()
    address = path.glob(excelfilename)
    for file in address:
        print(file.exists())
    wb = xl.load_workbook(file)
    sheet = wb['Sheet1']
    for cell_value in range(2, sheet.max_row + 1):
        print(sheet.cell(cell_value, 2).value)
        annual_salary = sheet.cell(cell_value, 2).value * 12
        sheet.cell(cell_value, 3).value = annual_salary
    values = Reference(
        sheet,
        min_row=2,
        max_row=sheet.max_row,
        min_col=3,
        max_col=3,
    )
    chart = BarChart3D()
    chart.add_data(values)
    sheet.add_chart(chart, 'D2')
    wb.save(excelfilename)
コード例 #18
0
def create_bar_chart_2(file_path):
    """
    插入3D柱形图
    :param file_path: Excel 文件路径
    :return: None
    """
    wb = load_workbook(file_path)
    st = wb.active

    data1 = Reference(st, min_col=2, min_row=1, max_row=7, max_col=3)
    cats1 = Reference(st, min_col=1, min_row=2, max_row=7)

    chart1 = BarChart3D()
    chart1.type = "bar"
    chart1.style = 10
    chart1.title = "日均值对比"
    chart1.x_axis.title = None
    chart1.shape = 'cylinder'

    chart1.add_data(data1, titles_from_data=True)
    chart1.set_categories(cats1)
    st.add_chart(chart1, 'A26')
    wb.save(file_path)
コード例 #19
0
ファイル: excel_app.py プロジェクト: abijr/master-python101
def file_updater(folder):
    print("Fetching files ... \nPlease wait . . .")
    directory = os.path.join("/mnt/c/Users/7K/", folder)
    filename = Path()
    os.chdir(directory)
    print(f"Currently working at: '{directory}' directory.")
    n = 0
    for i, filename in enumerate(filename.glob("*.xlsx")):
        wb = xl.load_workbook(filename)
        sheet = wb["Sheet1"]
        cell_ttl = sheet.cell(1, sheet.max_column + 1, value="Updated Price")
        # cell_ttl.value = "Updated Price"
        for row in range(2, sheet.max_row + 1):
            old_cell = sheet.cell(row, sheet.max_column - 1)
            upd_val = (old_cell.value) * (1.25)
            new_cell = sheet.cell(row, sheet.max_column)
            new_cell.value = upd_val

        values = Reference(worksheet=sheet,
                           min_col=4,
                           max_col=4,
                           min_row=2,
                           max_row=sheet.max_row)

        chart1, chart2 = BarChart3D(), PieChart3D()
        chart1.add_data(values)
        chart2.add_data(values)

        place1, place2 = "f2", "f20"
        sheet.add_chart(chart1, place1)
        sheet.add_chart(chart2, place2)

        wb.save(f"New_{i+1}-{filename}")
        n += 1
    print(f"({n} Files Found and Updated)")
    return print("\nFiles Succesfully Updated.")
コード例 #20
0
from openpyxl.chart import (AreaChart, Reference, Series, BarChart3D)

wb = load_workbook('G:/Local Disk/Python/ANZ_Day4/LinkData.xlsx',
                   read_only=False,
                   data_only=True)
sheet = wb.get_sheet_by_name("Sheet1")
outerList = []
for cell in range(1, sheet.max_row):
    innerList = []
    for col in range(3, 5):
        #        print(cell,sheet.cell(row=cell,column=col).value)
        innerList.append(sheet.cell(row=cell, column=col).value)
    outerList.append(innerList)

print(outerList)

chart = BarChart3D()
chart.title = "Link Utilization"
chart.style = 13
chart.x_axis.title = 'Time'
chart.y_axis.title = 'bandwidth'

cats = Reference(sheet, min_col=3, min_row=1, max_row=7)
data = Reference(sheet, min_col=4, min_row=1, max_col=4, max_row=7)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)

sheet.add_chart(chart, "G10")
print(os.getcwd())
wb.save('G:/Local Disk/Python/ANZ_Day4/LinkData.xlsx')
コード例 #21
0
    def printPollStat(self, quizStats):

        self.addIntoExecutionLog("Poll Statistics  is being generated...")

        if not os.path.exists('Results'):
            os.makedirs('Results')

        if not os.path.exists('./Results/poll_statistics'):
            os.makedirs('./Results/poll_statistics')

        for i, keyQuizName in enumerate(quizStats):

            writer = pd.ExcelWriter("./Results/poll_statistics/" +
                                    keyQuizName + "_stats.xlsx",
                                    engine='xlsxwriter')

            quizStat = quizStats[keyQuizName]

            for j, quizPart in enumerate(quizStat.questionStatDict):
                questionStat = quizStat.questionStatDict[quizPart]
                answerNumDict = questionStat.getAnswerStat().answerNumDict

                dfAnswers = pd.DataFrame(answerNumDict,
                                         columns=answerNumDict.keys(),
                                         index=[0])
                dfAnswers = dfAnswers.transpose().rename(columns={
                    0: "count",
                    1: "%"
                })

                nums = [n for n in answerNumDict.values()]

                mysum = sum(nums)

                for key in answerNumDict:
                    dfAnswers.loc[key, '%'] = float("{:.2f}".format(
                        100 * (answerNumDict[key] / mysum)))

                dfAnswers.index.name = quizPart.getQuestion().getQuestionText()
                dfAnswers.to_excel(writer, sheet_name="q" + str(j + 1))

            writer.save()

            wb = load_workbook("./Results/poll_statistics/" + keyQuizName +
                               "_stats.xlsx")

            my_blue = openpyxl.styles.colors.Color(rgb='CCE5FF')
            my_fill_blue = openpyxl.styles.fills.PatternFill(
                patternType='solid', fgColor=my_blue)
            my_red = openpyxl.styles.colors.Color(rgb='FFCCCC')
            my_fill_red = openpyxl.styles.fills.PatternFill(
                patternType='solid', fgColor=my_red)
            my_green = openpyxl.styles.colors.Color(rgb='CCFFCC')
            my_fill_green = openpyxl.styles.fills.PatternFill(
                patternType='solid', fgColor=my_green)
            my_orange = openpyxl.styles.colors.Color(rgb='FFE5CC')
            my_fill_orange = openpyxl.styles.fills.PatternFill(
                patternType='solid', fgColor=my_orange)

            for k, ws in enumerate(wb.worksheets):

                correctAnswer = ""
                for j, quizPart in enumerate(quizStat.questionStatDict):
                    str2 = str(ws['A1'].value)
                    qmatch = StringComparator(
                        quizPart.getQuestion().getQuestionText(),
                        str2).cmp_ig_CaseSpacePunc
                    if qmatch == 0:
                        correctAnswer = quizPart.getQuestion().getAnswer()
                        break

                for row in ws.iter_rows():
                    for cell in row:
                        cell.alignment = Alignment(horizontal='center',
                                                   vertical='center',
                                                   text_rotation=0,
                                                   wrap_text=True,
                                                   shrink_to_fit=False,
                                                   indent=1)

                for cell in ws['A']:
                    cell.alignment = Alignment(horizontal='left',
                                               vertical='center',
                                               text_rotation=0,
                                               wrap_text=True,
                                               shrink_to_fit=False,
                                               indent=1)

                for column in ws.iter_cols():
                    column[0].fill = my_fill_blue

                for row in ws.iter_rows():
                    row[0].fill = my_fill_orange

                trueCellIndex = 0
                for cell in ws['A']:

                    s = cell.internal_value
                    if s is not None:

                        for answer in correctAnswer:

                            match = StringComparator(str(s),
                                                     answer).cmp_ig_C_S_P_N
                            if match == 0:
                                cell.fill = my_fill_green
                                trueCellIndex = cell.row
                                break

                        if match != 0:
                            cell.fill = my_fill_red

                ws['A1'].fill = my_fill_orange
                ws['A1'].font = Font(bold=True)
                ws.column_dimensions['A'].width = 40
                ws.column_dimensions['B'].width = 15
                ws.column_dimensions['A'].height = 20
                p = BarChart3D()
                data = Reference(worksheet=ws,
                                 min_col=2,
                                 max_col=ws.max_column,
                                 min_row=2,
                                 max_row=ws.max_row)

                # p.add_data(data)
                p.title = "Answer Distribution"

                pt = DataPoint(idx=trueCellIndex - 2)
                pt.graphicalProperties.solidFill = "b2ff59"

                answers = Reference(worksheet=ws,
                                    min_col=1,
                                    max_col=1,
                                    min_row=2,
                                    max_row=ws.max_row)

                l = list(ws.iter_rows())

                g = 2
                for row in ws.iter_rows():
                    value = Reference(worksheet=ws,
                                      min_col=3,
                                      max_col=3,
                                      min_row=g,
                                      max_row=g)

                    ans = str(ws['A' + str(g)].internal_value)

                    serie = Series(value, title=ans)

                    if ans != None:

                        for answer in correctAnswer:

                            match = StringComparator(ans,
                                                     answer).cmp_ig_C_S_P_N
                            if match == 0:
                                serie.graphicalProperties.solidFill = "b2ff59"
                            break

                    p.append(serie)
                    g += 1

                p.height = 13
                p.width = 20
                p.style = 2
                p.legend.legendPos.upper()
                p.y_axis.scaling.min = 0
                p.y_axis.scaling.max = 100
                p.y_axis.title = "%"
                ws.add_chart(p, "D1")
                k += 1
            wb.save("./Results/poll_statistics/" + keyQuizName + "_stats.xlsx")
            self.addIntoExecutionLog(
                keyQuizName +
                "_stats.xlsx file has been generated in 'poll_statistics' directory"
            )
コード例 #22
0
#       create sheets     #

for x in range(len(new_years.months_two)):
    create_month_v4(x, counter)
    create_day_v4(x)
    counter = counter + 1

counter = 8
for y in range(len(new_years.months_two)):
    new_years.expenses_overview(y, counter, overview_sheet)
    counter = counter + 1

#            create charts          #
chart_sheet = workbook.create_sheet("Charts", 0)
expenses_in_total = BarChart3D()
expenses_in_total.style = 48
ref_obj = openpyxl.chart.Reference(overview_sheet,
                                   min_col=11,
                                   min_row=8,
                                   max_col=11,
                                   max_row=19)
ref_names = Reference(overview_sheet,
                      min_col=9,
                      min_row=8,
                      max_col=9,
                      max_row=19)
real_obj = openpyxl.chart.Series(ref_obj, title="Expenses 2020 per month")
expenses_in_total.title = "Expenses in 2020 TOTAL"
expenses_in_total.add_data(data=ref_obj, titles_from_data=False)
expenses_in_total.set_categories(ref_names)
コード例 #23
0
    else:
        # ws = wb.get_sheet_by_name('Sheet0')
        ws = wb['Sheet0']
        s = ws.max_row
        for i in range(1, len(head)+1):
            ws.cell(row=s + 1, column=i).value = data[i - 1]
        wb.save(path)
if found == 0:
    print("DATA NOT FOUND")


from openpyxl.chart import BarChart3D, Series, Reference
wb = load_workbook(path)
ws = wb['Sheet0']

chart1 = BarChart3D()


#chart1.type = "col"


#chart1.style = 12
chart1.title = "EXCEL DATA"
chart1.y_axis.title = 'Marks'
chart1.x_axis.title = 'Student'
print(col)
bar_r = ws.max_row
bar_c = ws.max_column
print("row: ",bar_r)
print("col: ",bar_c)
data = Reference(ws, min_col=4, min_row=bar_r-2, max_row=bar_r, max_col=bar_c)
コード例 #24
0
def writeOutput2(outfile,records):


####################################

	wb = Workbook()
	summaryColumn = 3


	for record in records:
		sheetname = str(label[record])
		wb.create_sheet(sheetname)
		ws = wb[str(sheetname)]
		wsummary = wb.worksheets[0]




		# write labels

		ws.cell(row=1, column=1).value = "code"
		ws.cell(row=1, column=2).value = "name"		

		columnVar = 1
		rowVar = 2

		for x in sorted(major):
			ws.cell(row=rowVar, column=columnVar).value = x
			ws.cell(row=rowVar, column=columnVar+1).value = label[x]
			
			rowVar += 1

		
		columnVar = 3
		rowVar = 2


		for r in records[record]:

			# r is column
			ws.cell(row=1, column=columnVar).value = label[r]

			for x in xrange(0,len(major)):
				

				code = str(ws.cell(row=rowVar+x, column=1).value)
				if code in records[record][r]: 

					ws.cell(row=rowVar+x, column=columnVar).value = records[record][r][code] 
					
					if isinstance(wsummary.cell(row=rowVar+x, column=summaryColumn).value,float):
						
						wsummary.cell(row=rowVar+x, column=summaryColumn).value += float(records[record][r][code])

					
					else:
						
						wsummary.cell(row=rowVar+x, column=summaryColumn).value = float(records[record][r][code]) 
					

			
			columnVar += 1

		for col in ws.columns:
		     max_length = 0
		     column = col[0].column # Get the column name
		     for cell in col:
		         try: # Necessary to avoid error on empty cells
		             if len(str(cell.value)) > max_length:
		                 max_length = len(cell.value)
		         except:
		             pass
		     adjusted_width = (max_length) * 1
		     ws.column_dimensions[column].width = adjusted_width


		summaryColumn+=1			






		data = Reference(ws, min_col=3, min_row=1, max_col=columnVar-1, max_row=6)
		titles = Reference(ws, min_col=2, min_row=2, max_row=6)
		chart = BarChart()
		chart.title = label[record]
		chart.add_data(data=data, titles_from_data=True)
		chart.set_categories(titles)
		chart.x_axis.delete = False
		chart.y_axis.delete = False
		ws.add_chart(chart, "A10")





####################################


	# write values



	ws = wb.worksheets[0]
	ws.title="summary"
	columnVar = 1
	rowVar = 2



# write labels

	ws.cell(row=1, column=1).value = "code"
	ws.cell(row=1, column=2).value = "name"		


	for x in sorted(major):
		ws.cell(row=rowVar, column=columnVar).value = x
		ws.cell(row=rowVar, column=columnVar+1).value = label[x]	


		rowVar +=1
	# make multiple outputs per project and by per employee
	
	columnVar = 1
	rowVar = 1


	for record in records:

		ws.cell(row=rowVar, column=columnVar+2).value = label[record]
		columnVar+=1


	for col in ws.columns:
	     max_length = 0
	     column = col[0].column # Get the column name
	     for cell in col:
	         try: # Necessary to avoid error on empty cells
	             if len(str(cell.value)) > max_length:
	                 max_length = len(cell.value)
	         except:
	             pass
	     adjusted_width = (max_length) * 1
	     ws.column_dimensions[column].width = adjusted_width

	print outfile, columnVar	 


	data = Reference(ws, min_col=3, min_row=1, max_col=columnVar+1, max_row=6)
	titles = Reference(ws, min_col=2, min_row=2, max_row=6)
	chart = BarChart3D()
	chart.title = "3D Bar Chart"
	chart.add_data(data=data, titles_from_data=True)
	chart.set_categories(titles)

	ws.add_chart(chart, "A10")

	chart3 = BarChart()
	chart3 = deepcopy(chart)
	chart3.type = "col"
	chart3.style = 10
	chart3.grouping = "stacked"
	chart3.overlap = 100
	chart3.title = 'Stacked Chart'
	chart3.set_categories(titles)

	ws.add_chart(chart3, "A30")

	chart.title.delete = False
	chart.x_axis.delete = False
	chart.y_axis.delete = False

	chart3.title.delete = False
	chart3.x_axis.delete = False
	chart3.y_axis.delete = False


	wb.save(year+"_"+month+"_"+outfile)
コード例 #25
0
    插入柱形图
    :param file_path: Excel 文件路径
    :param bar_chart 要添加的图表
    :param chart_location 图表位置
    :return: None
    """
    wb = load_workbook(file_path)
    st = wb.active

    data1 = Reference(st, min_col=4, min_row=1, max_col=7, max_row=6)
    cats1 = Reference(st, min_col=1, min_row=2, max_row=6)

    chart1 = bar_chart
    chart1.type = "col"
    chart1.style = 10
    chart1.title = 'rustfisher.com'
    chart1.x_axis.title = None

    chart1.add_data(data1, titles_from_data=True)
    chart1.set_categories(cats1)
    st.add_chart(chart1, chart_location)
    wb.save(file_path)


if __name__ == '__main__':
    excel_path = 'out/指数.xlsx'
    create_bar_chart_1(excel_path)
    create_bar_chart_2(excel_path)
    create_bar_chart_for_ratios(excel_path, BarChart())
    create_bar_chart_for_ratios(excel_path, BarChart3D(), chart_location='F26')
コード例 #26
0
def testChart(file, scores):
    #have a test
    wb = Workbook(write_only=True)
    ws_sta = wb.create_sheet()

    #header = [
    #    ' ', 'Discussion', 'Implementation started', 'Implementation Done', 'Total Innovation Point'
    #]

    headers = row_labels
    headers.insert(0, ' ')

    revertrows = list()
    for i in range(len(team_list)):
        revertrow = [x[i] for x in scores]
        revertrow.insert(0, team_list[i])
        print("revertrow:", revertrow)
        revertrows.append(revertrow)

    print("revertrows:", revertrows)

    ws_sta.append(headers)
    for row in revertrows:
        print(row)
        ws_sta.append(row)

    data = Reference(ws_sta,
                     min_col=2,
                     min_row=1,
                     max_col=4,
                     max_row=len(team_list) + 1)
    titles = Reference(ws_sta,
                       min_col=1,
                       min_row=2,
                       max_row=len(team_list) + 1)
    print("data:", data)
    print("title:", titles)
    chart = BarChart3D()
    chart.title = "Innovation Statistic - Idea status"
    chart.add_data(data=data, titles_from_data=True)
    chart.set_categories(titles)
    ws_sta.add_chart(chart, "A15")

    data = Reference(ws_sta,
                     min_col=5,
                     min_row=1,
                     max_col=5,
                     max_row=len(team_list) + 1)
    titles = Reference(ws_sta,
                       min_col=1,
                       min_row=2,
                       max_row=len(team_list) + 1)
    print("data:", data)
    print("title:", titles)
    chart = BarChart3D()
    chart.title = "Innovation Statistic - Innovation Point"
    chart.add_data(data=data, titles_from_data=True)
    chart.set_categories(titles)
    ws_sta.add_chart(chart, "I15")

    wb.save(file)
コード例 #27
0
work_book.save('AirLineBumps_bar_chart.xlsx')


from copy import deepcopy


hor_chart = deepcopy(chart)

hor_chart.type = "bar"
hor_chart.title = "Horizontal Bar Chart"
sheet.add_chart(hor_chart, "H16")
work_book.save('AirLineBumps_bar_chart.xlsx')
stacked_chart = deepcopy(chart)
stacked_chart.type =  "col"
stacked_chart.grouping = "stacked"
stacked_chart.overlap = 100
stacked_chart.title = "Stacked Chart"
sheet.add_chart(stacked_chart, "N1")
work_book.save('AirLineBumps_bar_chart.xlsx')
from openpyxl.chart import BarChart3D
chart_3d = BarChart3D()
chart_3d.type = "col"
chart_3d.title = "3D Bar Chart"
chart_3d.y_axis.title = "Number of bumps"
chart_3d.x_axis.title="Airline"
chart_3d.add_data(data, titles_from_data = True)
chart_3d.set_categories(cats)
sheet.add_chart(chart_3d, "N16")
work_book.save('AirLineBumps_bar_chart.xlsx')
コード例 #28
0
# ch19_33.py
import openpyxl
from openpyxl.chart import BarChart3D, Reference

wb = openpyxl.Workbook()  # 開啟活頁簿
ws = wb.active  # 獲得目前工作表
rows = [['', '2020年', '2021年'], ['亞洲', 100, 300], ['歐洲', 400, 600],
        ['美洲', 500, 700], ['非洲', 200, 100]]
for row in rows:
    ws.append(row)

chart = BarChart3D()  # 3D長條圖
chart.title = '深石軟件銷售表'  # 圖表標題
chart.y_axis.title = '業績金額'  # y軸標題
chart.x_axis.title = '地區'  # x軸標題

data = Reference(ws, min_col=2, max_col=3, min_row=1, max_row=5)  # 圖表資料
chart.add_data(data, titles_from_data=True)  # 建立圖表
xtitle = Reference(ws, min_col=1, min_row=2, max_row=5)  # x軸標記名稱
chart.set_categories(xtitle)  # 設定x軸標記名稱(亞洲歐洲美洲非洲)
ws.add_chart(chart, 'E1')  # 放置圖表位置
wb.save('out19_33.xlsx')
コード例 #29
0
ファイル: 添加Excel图表.py プロジェクト: wljave/subentry
# 获取文件夹下的所有文件名
file_list = os.listdir(path)
# 遍历文件名列表,取得每一个文件名
for file_name in file_list:
    # 拼接文件路径
    file_path = path + file_name
    print('正在处理:' + file_name)
    # 读取工作簿
    wb = load_workbook(file_path)
    # 读取工作簿中的活跃工作表
    ws = wb.active

    # 实例化 LineChart 类,得到 折线图 - LineChart 对象;柱状图 - BarChart3D 对象
    chart1 = LineChart()
    chart2 = BarChart3D()
    # 引用工作表的部分数据
    data = Reference(worksheet=ws, min_row=3, max_row=9, min_col=1, max_col=5)
    # 添加被引用的数据到 LineChart 对象        chart.add_data(data, from_rows=True, titles_from_data=True)
    chart1.add_data(data, from_rows=True, titles_from_data=True)
    chart2.add_data(data, from_rows=True, titles_from_data=True)
    # chart1.varyColors = True
    # chart2.varyColors = True
    # 添加 LineChart 对象到工作表中,指定折线图的位置
    ws.add_chart(chart1, "C12")
    ws.add_chart(chart2, "C29")

    # 引用工作表的表头数据
    cats = Reference(worksheet=ws, min_row=2, max_row=2, min_col=2, max_col=5)
    # 设置类别轴的标签
    chart1.set_categories(cats)