コード例 #1
0
def add_pie_chart(writer, df):
    # 初始化excel并确立图表类型
    sheet = writer.sheets['全球最新疫情分布']
    chart = PieChart()
    # 设置插入序列
    max_row = len(df)
    labels = Reference(sheet,
                       min_col=2,
                       max_col=2,
                       min_row=2,
                       max_row=max_row + 1)
    chart_data = Reference(sheet,
                           min_col=3,
                           max_col=3,
                           min_row=1,
                           max_row=max_row + 1)
    chart.add_data(chart_data, titles_from_data=True)
    chart.set_categories(labels)
    # 设置图表格式
    chart.title = "除湖北外全球各地区确诊人数占比"
    chart.height, chart.width = 14, 21
    chart.style = 5
    chart.dataLabels = DataLabelList()
    chart.dataLabels.showCatName = True
    chart.dataLabels.showPercent = True
    # 插入图表
    sheet.add_chart(chart, 'F1')
    writer.save()
コード例 #2
0
class TestPieChartWriter(object):
    def setup(self):
        """Setup a worksheet with one column of data and a pie chart"""
        wb = Workbook()
        ws = wb.get_active_sheet()
        ws.title = "Pie"
        for i in range(1, 5):
            ws.append([i])
        self.piechart = PieChart()
        values = Reference(ws, (0, 0), (9, 0))
        series = Serie(values, labels=values)
        self.piechart.add_serie(series)
        ws.add_chart(self.piechart)
        self.cw = PieChartWriter(self.piechart)
        self.root = Element("test")

    def test_write_chart(self):
        """check if some characteristic tags of PieChart are there"""
        self.cw._write_chart(self.root)

        tagnames = ["{%s}pieChart" % CHART_NS, "{%s}varyColors" % CHART_NS]
        root = safe_iterator(self.root)
        chart_tags = [e.tag for e in root]
        for tag in tagnames:
            assert_true(tag in chart_tags, tag)

        assert_false("c:catAx" in chart_tags)

    def test_serialised(self):
        """Check the serialised file against sample"""
        xml = self.cw.write()
        expected_file = os.path.join(DATADIR, "writer", "expected", "piechart.xml")
        with open(expected_file) as expected:
            diff = compare_xml(xml, expected.read())
            assert diff is None, diff
コード例 #3
0
def excel_automation(filename):
    # for experiment, ignore it
    # wb = xl.load_workbook("transactions.xlsx")
    wb = xl.load_workbook(f"{filename}")
    sheet = wb['Sheet1']

    for row in range(2, sheet.max_row + 1):
        cell = sheet.cell(row, 3)

        # add corrected formula here
        corrected_price = cell.value * 0.7

        corrected_price_cell = sheet.cell(row, sheet.max_column + 1)
        corrected_price_cell.value = corrected_price

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

    chart = PieChart()
    chart.add_data(graphValues)

    sheet.add_chart(chart, 'f2')

    wb.save(f'{filename}')
コード例 #4
0
def chartBreakdown():
    '''Makes pie chart and bar chart for summary tab'''
    pie = PieChart()
    z = len(categories)

    data = Reference(Sum, min_col=9, min_row=2, max_row= z-1)
    labels = Reference(Sum, min_col=8, min_row=2, max_row= z-1)
    pie.add_data(data)
    pie.set_categories(labels)
    pie.title = 'Breakdown of Expenses'
    pie.width = 15.0
    pie.height = 12.0
    pie.legend.layout = Layout(manualLayout=ManualLayout(x=0.25, y=0.25, h=0.99, w=0.25))

    Sum.add_chart(pie, 'A1')
    pie.dataLabels = DataLabelList()
    pie.dataLabels.showPercent = True

    bar = BarChart()
    barData1 = Reference(Sum, min_col=mNum+9, min_row=1, max_row=z-1)
    barData2 = Reference(Sum, min_col=mNum+12, min_row=1, max_row=z-1)
    bar.add_data(barData1, titles_from_data=True)
    bar.add_data(barData2, titles_from_data=True)
    bar.set_categories(labels)
    bar.title = 'Goal Comparison'
    bar.width = 2.0*z
    bar.height = 12.0
    bar.legend.layout = Layout(manualLayout=ManualLayout(x=0.25, y=0.25, h=1.99, w=0.25))
    Sum.add_chart(bar, 'A28')
コード例 #5
0
    def __init__(self, db_name, save_atexit_only=True):
        self.db_name = db_name

        if path.exists(self.db_name):
            self.wb = openpyxl.load_workbook(self.db_name)
            self.sheet = self.wb.active
        else:
            self.wb = openpyxl.Workbook()
            self.sheet = self.wb.active

            data = Reference(self.sheet,
                             min_col=4,
                             min_row=1,
                             max_col=6,
                             max_row=2)

            chart = PieChart()
            chart.title = 'Статистика'
            chart.add_data(data, titles_from_data=True)

            self.sheet.add_chart(chart, 'D4')

            self.sheet['D1'] = 'Негативных'
            self.sheet['D2'] = '=COUNTIF(B3:B20000,"Негативный")'

            self.sheet['E1'] = 'Нейтральных'
            self.sheet['E2'] = '=COUNTIF(B3:B20000,"Нейтральный")'

            self.sheet['F1'] = 'Позитивных'
            self.sheet['F2'] = '=COUNTIF(B3:B20000,"Позитивный")'

        if save_atexit_only:
            atexit.register(self.save)
コード例 #6
0
ファイル: charts.py プロジェクト: fizikst/connect_ws
def pie(wb):
    ws = wb.create_sheet(4, "Pie")
    for i in range(1, 5):
        ws.append([i])
    chart = PieChart()
    values = Reference(ws, (0, 0), (3, 0))
    series = Series(values, labels=values)
    chart.append(series)
    ws.add_chart(chart)
コード例 #7
0
def make_pie(sheet, left_col, top_row, bot_row, title, print_cell, height,
             width):
    left_col = int(left_col)
    right_col = left_col + 1
    top_row = int(top_row)
    bot_row = int(bot_row)
    title = str(title)
    print_cell = str(print_cell)
    height = float(height)
    width = float(width)

    pie = PieChart()
    labels = Reference(sheet,
                       min_col=left_col,
                       max_col=left_col,
                       min_row=top_row + 1,
                       max_row=bot_row)
    data = Reference(sheet,
                     min_col=right_col,
                     max_col=right_col,
                     min_row=top_row,
                     max_row=bot_row)
    pie.add_data(data, titles_from_data=True)
    pie.set_categories(labels)
    pie.title = title
    pie.height = height
    pie.width = width
    sheet.add_chart(pie, print_cell)

    return None
コード例 #8
0
ファイル: excelDispose.py プロジェクト: zhangyuzhe-0719/boyun
 def chartInit(self):
     '''
     画一个饼图
     :return:
     '''
     self.pie = PieChart()
     lables = Reference(self.ws, min_col=5, min_row=2, max_row=3)
     data = Reference(self.ws, min_col=6, min_row=2, max_row=3)
     self.pie.add_data(data)
     self.pie.set_categories(lables)
     self.pie.title = '通过率对比图'
     self.ws.add_chart(self.pie, 'B8')
コード例 #9
0
ファイル: app.py プロジェクト: zhuby1973/map
def pieChart():
    from openpyxl import Workbook
    from openpyxl.chart import PieChart, Reference

    data = [
        ['水果', '销量'],
        ['苹果', 50],
        ['樱桃', 30],
        ['橘子', 10],
        ['香蕉', 40],
    ]

    wb = Workbook()
    ws = wb.active

    for row in data:
        ws.append(row)

    pie = PieChart()
    labels = Reference(ws, min_col=1, min_row=2, max_row=5)
    data = Reference(ws, min_col=2, min_row=1, max_row=5)
    pie.add_data(data, titles_from_data=True)
    pie.set_categories(labels)
    pie.title = "水果销量占比"

    ws.add_chart(pie, "D1")
    wb.save('piechart.xlsx')
    print('打开文件 piechart.xlsx 查看结果')
コード例 #10
0
ファイル: chart.py プロジェクト: sunxiaoou/py
def pie_chart():
    data = [
        ['Pie', 'Sold'],
        ['Apple', 50],
        ['Cherry', 30],
        ['Pumpkin', 10],
        ['Chocolate', 40],
    ]
    wb = Workbook()
    ws = wb.active
    for row in data:
        ws.append(row)
    pie = PieChart()
    labels = Reference(ws, min_col=1, min_row=2, max_row=5)
    data = Reference(ws, min_col=2, min_row=1, max_row=5)
    pie.add_data(data, titles_from_data=True)
    pie.set_categories(labels)
    pie.title = "Pies sold by category"

    # Cut the first slice out of the pie
    slice = DataPoint(idx=0, explosion=20)
    pie.series[0].data_points = [slice]

    ws.add_chart(pie, "D1")
    wb.save("chart.xlsx")
コード例 #11
0
def pie_chart(wp):
    table = wp.get_sheet_by_name(wp.get_sheet_names()[0])

    #生成饼图对象
    pie = PieChart()
    #图的标题
    pie.title = "API接口测试统计"
    '''行数和列数都是从1开始的,和遍历用例是一样都是从1开始'''
    #获取标签(取范围第一列的最小行数和最大行数)
    labels = Reference(table, min_col=4, min_row=6, max_col=4, max_row=7)
    #获取数据(取范围第二列的最小行数-1和最大行数)
    data = Reference(table, min_col=5, min_row=5, max_col=5, max_row=7)

    #添加数据和标签到饼图中
    pie.add_data(data, titles_from_data=True)
    pie.set_categories(labels)

    #放在excel表中
    table.add_chart(pie, "A10")

    #保存excel
    # wb.save("test1.xlsx")


# wb = load_workbook("D:\\PycharmProjects\\test.xlsx")
# pie_chart(wb)
コード例 #12
0
def plot_pie_chart():
    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 = PieChart()
    chart.append(series_object)
    sheet.add_chart(chart, 'j2')
    wb.save('C:\\Resources\\Program_Xl_Sheets\\Groceriesptfinal.xlsx')
コード例 #13
0
def test_duplicate_chart(ExcelWriter, archive):
    from openpyxl.chart import PieChart
    pc = PieChart()
    wb = Workbook()
    writer = ExcelWriter(wb, archive)

    writer._charts = [pc] * 2
    with pytest.raises(InvalidFileException):
        writer._write_charts()
コード例 #14
0
ファイル: save_to.py プロジェクト: sunxiaoou/py
def summarize_amount(file: str, sheet_name: str):
    wb = openpyxl.load_workbook(file)
    sheet = wb[sheet_name]
    last_row = sheet.max_row
    last_col = sheet.max_column

    for i in range(2, last_row + 1):
        for j in range(6, last_col):
            sheet.cell(row=i, column=j).number_format = '#,##,0.00'
        sheet.cell(row=i, column=last_col).number_format = '0.00%'

    summaries = [
        {'location': (last_row + 2, 1),
         'letter': 'A',
         'labels': ['招商银行', '恒生银行', '银河', '华盛*', '富途*', '蛋卷*', '同花顺'],
         'category': 'platform',
         'anchor': 'K1'},
        {'location': (last_row + 2, 4),
         'letter': 'B',
         'labels': ['rmb', 'hkd', 'usd'],
         'category': 'currency',
         'anchor': 'K16'},
        {'location': (last_row + 2, 7),
         'letter': 'E',
         'labels': [0, 1, 2, 3],
         'category': 'risk',
         'anchor': 'K31'}
    ]

    for summary in summaries:
        row, col = summary['location']
        le = [get_column_letter(j) for j in range(col, col + 3)]
        for i in range(len(summary['labels'])):
            sheet.cell(row=row+i, column=col).value = summary['labels'][i]
            c = sheet.cell(row=row+i, column=col+1)
            c.number_format = "#,##,0.00"
            c.value = '=SUMIF(${0}$2:${0}${1},{2}{3},$H$2:$H${1})'.format(summary['letter'],
                                                                          last_row, le[0], row + i)
            c = sheet.cell(row=row+i, column=col+2)
            c.number_format = "#,##,0.00"
            c.value = '=SUMIF(${0}$2:${0}${1},{2}{3},$I$2:$I${1})'.format(summary['letter'],
                                                                          last_row, le[0], row + i)
        sheet.cell(row=row+i+1, column=col).value = 'sum'
        c = sheet.cell(row=row+i+1, column=col+1)
        c.number_format = "#,##,0.00"
        c.value = '=SUM({0}{1}:{0}{2})'.format(le[1], row, row + i)
        c = sheet.cell(row=row+i+1, column=col+2)
        c.number_format = "#,##,0.00"
        c.value = '=SUM({0}{1}:{0}{2})'.format(le[2], row, row + i)

        pie = PieChart()
        labels = Reference(sheet, min_col=col, min_row=row, max_row=row+i)
        data = Reference(sheet, min_col=col+1, min_row=row-1, max_row=row+i)
        pie.add_data(data, titles_from_data=True)
        pie.set_categories(labels)
        pie.title = summary['category']
        sheet.add_chart(pie, summary['anchor'])

    wb.save(file)
コード例 #15
0
def draw_chart(input_wb, max_row):
    # 拿到我们需要操作的sheet top10
    top_10_sheet = input_wb.worksheets[-1]
    top_10_sheet.sheet_view.zoomScale = 200
    # 初始化chart
    bar_chart = BarChart()
    line_chart = LineChart()
    pie_chart = PieChart()
    # 生成数据
    bar_chart_data = Reference(top_10_sheet,
                               min_col=4,
                               max_col=7,
                               min_row=1,
                               max_row=max_row)
    line_chart_data = Reference(top_10_sheet,
                                min_col=4,
                                max_col=4,
                                min_row=1,
                                max_row=max_row)
    pie_chart_data = Reference(top_10_sheet,
                               min_col=4,
                               max_col=4,
                               min_row=1,
                               max_row=max_row)
    # 指定chart的x_axis
    x_data = Reference(top_10_sheet,
                       min_col=2,
                       max_col=2,
                       min_row=2,
                       max_row=max_row)
    # 设置chart样式
    bar_chart.height, bar_chart.width = 7, 15
    line_chart.height, line_chart.width = 7, 15
    pie_chart.height, pie_chart.width = 7, 15
    bar_chart.title, bar_chart.y_axis.title, bar_chart.x_axis.title = 'top10', '人数', '国家'
    # bar_chart.y_axis.scaling.max = 5000000
    # 把数据添加进chart
    bar_chart.add_data(bar_chart_data, titles_from_data=True)
    line_chart.add_data(line_chart_data, titles_from_data=True)
    bar_chart.set_categories(x_data)
    line_chart.set_categories(x_data)
    pie_chart.add_data(pie_chart_data, titles_from_data=True)
    pie_chart.set_categories(x_data)
    pie_chart.dataLabels = DataLabelList()
    pie_chart.dataLabels.showVal = True
    pie_chart.dataLabels.showLegendKey = True
    # 把chart添加到sheet
    # top_10_sheet.add_chart(bar_chart, 'I1')
    # top_10_sheet.add_chart(pie_chart, 'I11')
    bar_chart += line_chart
    top_10_sheet.add_chart(bar_chart, 'I1')
    # 保存我们的workbook
    input_wb.save('./excel_files/report_chart.xlsx')
コード例 #16
0
ファイル: app.py プロジェクト: hiimmuc/python_learn_part2
def process_workbook(filename):
    wb = xl.load_workbook(filename)
    sheet = wb['Sheet1']

    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
        print(type(corrected_price))

    value = Reference(sheet, min_row=2,
                      max_row=sheet.max_row,
                      min_col=4,
                      max_col=4)
    chart = PieChart()
    chart.add_data(value)
    sheet.add_chart(chart, 'a2')
    wb.save(filename)
    wb.close()
コード例 #17
0
def plot_sentiment(tickers):
    df = scored_news(tickers)

    #getrange of headlines dates and daily scores
    dates = date_range(df)

    daily = daily_sentiment(tickers)
    df = tally_scores(df).T
    blank = []

    columns = list(df)

    path = "C:\\Users\\matth\\Desktop\\stock-sentiment-analyzer\\pie.xlsx"
    sf = StyleFrame(daily.T)
    col_list = list(daily.columns)
    writer = pd.ExcelWriter(path, engine='xlsxwriter')
    daily.T.to_excel(excel_writer=writer, sheet_name='Daily Sentiment Summary')
    worksheet = writer.sheets['Daily Sentiment Summary']
    worksheet.set_column('B:B', 15)
    writer.save()

    wb = load_workbook("pie.xlsx")
    for i in range(0, len(df.columns)):

        ws = wb.create_sheet(tickers[i])
        score = 1

        for index, row in df.iterrows():
            vals = []
            if index == 'tickers':
                continue
            vals.append(df.loc[index, i])
            vals.insert(0, df.index[score])
            score = score + 1
            ws.append(vals)

        pie = PieChart()
        labels = Reference(ws, min_col=1, max_col=1, min_row=1, max_row=3)

        data = Reference(ws, min_col=2, max_col=2, min_row=1, max_row=3)
        pie.add_data(data)

        pie.set_categories(labels)
        pie.title = tickers[i] + ' Sentiment'
        pie.layout = Layout(manualLayout=ManualLayout(
            h=0.7,
            w=0.7,
        ))
        ws.add_chart(pie, "E3")

    wb.save("pie.xlsx")
コード例 #18
0
ファイル: jekxlsx.py プロジェクト: jackysupit/testctp
    def contoh_gauge_chart(self, ws):
        data = [
            ["Donut", "Pie"],
            [25, 75],
            [50, 1],
            [25, 124],
            [100],
        ]
        # based on http://www.excel-easy.com/examples/gauge-chart.html
        for row in data:
            ws.append(row)

        # First chart is a doughnut chart
        c1 = DoughnutChart(firstSliceAng=270, holeSize=50)
        c1.title = "Code coverage"
        c1.legend = None
        ref = Reference(ws, min_col=1, min_row=2, max_row=5)
        s1 = Series(ref, title_from_data=False)
        slices = [DataPoint(idx=i) for i in range(4)]
        slices[0].graphicalProperties.solidFill = "FF3300"  # red
        slices[1].graphicalProperties.solidFill = "FCF305"  # yellow
        slices[2].graphicalProperties.solidFill = "1FB714"  # green
        slices[3].graphicalProperties.noFill = True  # invisible
        s1.data_points = slices
        c1.series = [s1]


        # Second chart is a pie chart
        c2 = PieChart(firstSliceAng=270)
        c2.legend = None
        ref = Reference(ws, min_col=2, min_row=2, max_col=2, max_row=4)
        s2 = Series(ref, title_from_data=False)
        slices = [DataPoint(idx=i) for i in range(3)]
        slices[0].graphicalProperties.noFill = True  # invisible
        slices[1].graphicalProperties.solidFill = "000000"  # black needle
        slices[2].graphicalProperties.noFill = True  # invisible
        s2.data_points = slices
        c2.series = [s2]

        c1 += c2  # combine charts
        ws.add_chart(c1, "D1")

        # Second chart is a pie chart
        c2 = PieChart(firstSliceAng=270)
        # c2.legend = None
        ref = Reference(ws, min_col=2, min_row=2, max_col=2, max_row=4)
        s2 = Series(ref, title_from_data=False)
        # slices = [DataPoint(idx=i) for i in range(3)]
        # slices[0].graphicalProperties.noFill = True  # invisible
        # slices[1].graphicalProperties.solidFill = "000000"  # black needle
        # slices[2].graphicalProperties.noFill = True  # invisible
        # s2.data_points = slices
        c2.series = [s2]

        ws.add_chart(c2, "D20")
コード例 #19
0
def create_chart_file(name_file, ws_result):
    actual_path = os.path.abspath(os.path.dirname(__file__))
    result_file_path = os.path.join(actual_path,
                                    f"../files/result_files/{name_file}.xlsx")
    try:
        wb = openpyxl.load_workbook(result_file_path)
        chart_worksheet = wb.create_sheet(title="Gráfico")
        chart_worksheet['A1'] = "Total Sucess"
        chart_worksheet['A2'] = "Total Failed"
        chart_worksheet['B1'] = f'=COUNTIF(${ws_result}.E:E;"Sucess")'
        chart_worksheet['B2'] = f'=COUNTIF(${ws_result}.E:E;"Failed")'

        pie_chart = PieChart()
        values = Reference(chart_worksheet,
                           min_col=1,
                           min_row=1,
                           max_col=2,
                           max_row=2)
        labels = Reference(chart_worksheet,
                           min_col=1,
                           min_row=1,
                           max_col=2,
                           max_row=2)
        pie_chart.add_data(values, titles_from_data=True)
        pie_chart.set_categories(labels)
        chart_worksheet.add_chart(pie_chart, "A1")
        wb.save(result_file_path)
    except Exception as e:
        print(e)
コード例 #20
0
def monthChartBreakdown():
    '''Makes pie chart for each month'''
    curMonth = ['', 'January', 'February', 'March', 'April', 'May', 'June',
                'July', 'August', 'September', 'October', 'November', 'December']

    for i in range(1, mNum+1):
        pie = PieChart()
        length_m = len(categories)
        monthData = Reference(Sum, min_col = i+9, min_row = 2, max_row = length_m-1)
        labels = Reference(Sum, min_col=8, min_row=2, max_row = length_m-1)
        pie.add_data(monthData)
        pie.set_categories(labels)
        pie.title = curMonth[i]+ ' Expenses by Category'
        pie.width = 18.0
        pie.height = 12.0
        pie.legend.layout = Layout(manualLayout=ManualLayout(x=0.25, y=0.25, h=0.99, w=0.25))

        wb.get_sheet_by_name(TM[i]).add_chart(pie, 'G3')
コード例 #21
0
def make_chart_carrier(result_file="MainTestResults/MainResult.xlsx",
                       summary_file="CarrierTestResults"
                       "/CarrierTestResult.json"):
    try:
        wb = load_workbook(os.path.join(current_path, result_file))
    except:
        wb = Workbook()
    if "Carrier Summary" in wb.sheetnames:
        del wb["Carrier Summary"]

    wb.create_sheet("Carrier Summary")
    ws = wb["Carrier Summary"]
    _ = ws.cell(row=1, column=1, value='Carrier Summary')
    _.font = Font(bold=True)
    # summary_file = "CarrierTestResults/CarrierTestResult.json"
    with open(os.path.join(current_path, summary_file), 'r') as f:
        res = json.load(f)
    ips = [key for key in res.keys() if key != 'summary']
    row = 1
    pie_row_start = 4
    for ip in ips:
        data_to_add = [["made ip", ip]]
        for key in res[ip].keys():
            data_to_add.append([key, res[ip][key]])
        for line in data_to_add:
            ws.append(line)
        pie_row_end = pie_row_start + 3
        pie = PieChart()
        print("pie row start:", pie_row_start)
        print("pie row end:", pie_row_end)
        labels = Reference(ws,
                           min_col=1,
                           min_row=pie_row_start,
                           max_row=pie_row_end)
        data = Reference(ws,
                         min_col=2,
                         min_row=pie_row_start - 1,
                         max_row=pie_row_end)
        pie.add_data(data, titles_from_data=True)
        pie.set_categories(labels)
        pie.title = ip + " fail rate"
        pie.dataLabels = DataLabelList()
        pie.dataLabels.showPercent = True
        ws.add_chart(pie, "D" + str(row))
        pie_row_start += len(data_to_add)
        row += 18

    wb.save(os.path.join(current_path, result_file))
コード例 #22
0
 def setup(self):
     """Setup a worksheet with one column of data and a pie chart"""
     wb = Workbook()
     ws = wb.get_active_sheet()
     ws.title = 'Pie'
     for i in range(1, 5):
         ws.append([i])
     self.piechart = PieChart()
     values = Reference(ws, (0, 0), (9, 0))
     series = Serie(values, labels=values)
     self.piechart.add_serie(series)
     ws.add_chart(self.piechart)
     self.cw = PieChartWriter(self.piechart)
     self.root = Element('test')
コード例 #23
0
ファイル: jekxlsx.py プロジェクト: jackysupit/testctp
    def chart_pie(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
                  , **kw
                  ):

        if is_3d:
            chart = PieChart3D()
        else:
            chart = PieChart()

        # print("@@@@@@@@@@@@@@@ title = {title} @@@@@@@@@@@@@@@@@@".format(title=title))
        # print("default_height={} | width:{} ||| new: {} | {}".format(chart.height, chart.width, chart_height, chart_width))
        chart.height = chart_height  # default is 7.5
        chart.width = chart_width  # default is 15

        # print("@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@")
        # print("@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@")
        # print("@@@@@@@@@@@@@@@ title = {title} @@@@@@@@@@@@@@@@@@".format(title=title))
        # print("labels = min_col={label_min_col}, max_col={label_max_col}, min_row={label_min_row}, max_row={label_max_row}".format(label_min_col=label_min_col,label_max_col=label_max_col,label_min_row=label_min_row,label_max_row=label_max_row,))
        # print("data = min_col={data_min_col}, max_col={data_max_col}, min_row={data_min_row}, max_row={data_max_row}".format(data_min_col=data_min_col,data_max_col=data_max_col,data_min_row=data_min_row,data_max_row=data_max_row,))
        # print("from_rows: ", from_rows)
        # print("@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@")
        # print("@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@")

        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 add_chart:
            ws.add_chart(chart, destination_cell_coordinate)

        return chart
コード例 #24
0
def create_pie_chart():
    wb, ws, max_row = load_book(Main)
    chart_worksheet = wb[Category_Data]
    cs = wb.create_chartsheet("Category Chart")

    r = load_chart_data(ws, "Category", chart_worksheet)

    category_expenses = PieChart()
    labels = Reference(chart_worksheet, min_col=2, min_row=3, max_row=r - 1)
    data = Reference(chart_worksheet, min_col=3, min_row=2, max_row=r - 1)
    category_expenses.add_data(data, titles_from_data=True)
    category_expenses.set_categories(labels)
    category_expenses.title = "Expenses by Category"

    cs.add_chart(category_expenses)
    wb.save(file_path)
コード例 #25
0
def generate_picks_chart(workbook):
    print("Generating picks chart")

    chart = PieChart()
    chart.title = "Picks"

    datasheet = workbook['Summary']
    data = Reference(datasheet, min_col=4, max_col=4, min_row=13, max_row=17)
    titles = Reference(datasheet, min_col=1, min_row=14, max_row=17)
    chart.add_data(data=data, titles_from_data=True)
    chart.set_categories(titles)

    chart.dataLabels = DataLabelList()
    chart.dataLabels.showPercent = True
    chart.dataLabels.showVal = True
    chart.dataLabels.showCatName = False
    chart.dataLabels.showSerName = False
    chart.dataLabels.showLegendKey = False

    return chart
コード例 #26
0
def add_pie_chart(workbook, worksheet_level):
    pie = PieChart()
    cats = Reference(worksheet_level,
                     min_col=1,
                     min_row=2,
                     max_row=worksheet_level.max_row)
    data = Reference(worksheet_level,
                     min_col=2,
                     min_row=1,
                     max_row=worksheet_level.max_row)
    pie.add_data(data, titles_from_data=True)
    pie.set_categories(cats)
    pie.title = '问题评级'
    data_points = DataPoint(idx=0, explosion=20)
    pie.series[0].data_points = [data_points]
    worksheet_level.add_chart(pie, "C9")
    workbook.save(PATH + '/total.xlsx')
コード例 #27
0
def make_chart_main(result_file="MainTestResults/MainResult.xlsx",
                    result_json='MainTestResults/main_result.json'):
    try:
        wb = load_workbook(os.path.join(current_path, result_file))
        if "Main Summary" in wb.sheetnames:
            del wb['Main Summary']
        wb.create_sheet('Main Summary')
        ws = wb['Main Summary']
    except:
        wb = Workbook()
        ws = wb.active
        ws.title = 'Main Summary'

    with open(os.path.join(current_path, result_json), 'r') as f:
        res = json.load(f)

    for key in res.keys():
        ws.append([key, res[key]])

    pie_row_start = 2
    pie_row_end = pie_row_start + len(res) - 2
    pie = PieChart()
    print("pie row start:", pie_row_start)
    print("pie row end:", pie_row_end)
    labels = Reference(ws,
                       min_col=1,
                       min_row=pie_row_start,
                       max_row=pie_row_end)
    data = Reference(ws,
                     min_col=2,
                     min_row=pie_row_start - 1,
                     max_row=pie_row_end)
    pie.add_data(data, titles_from_data=True)
    pie.set_categories(labels)
    pie.title = "main cycle fail rate"
    pie.dataLabels = DataLabelList()
    pie.dataLabels.showPercent = True
    ws.add_chart(pie, "D2")

    wb.save(os.path.join(current_path, result_file))
コード例 #28
0
    def export_pie_chart(self, data, title):
        wb = Workbook()
        ws = wb.active
        for row in [('', '')] + data:
            ws.append(row)

        pie = PieChart()
        labels = Reference(ws, min_col=1, min_row=2, max_row=len(data) + 1)
        items = Reference(ws, min_col=2, min_row=1, max_row=len(data) + 1)
        pie.add_data(items, titles_from_data=True)
        pie.set_categories(labels)
        pie.title = title
        ws.add_chart(pie, 'A{0}'.format(len(data) + 3))

        output = StringIO.StringIO()
        wb.save(filename=output)
        output.flush()
        return output.getvalue()
コード例 #29
0
ファイル: ExcelLib.py プロジェクト: Radhika-Goel/PracticeCode
def drawFinalPieChart(sheetName):
    wb = loadExcel()
    ws = wb.get_sheet_by_name(sheetName)
    pie = PieChart()
    labels = Reference(ws, min_col=1, min_row=2, max_row=3)
    data = Reference(ws, min_col=2, min_row=1, max_row=3)
    pie.add_data(data, titles_from_data=True)
    pie.set_categories(labels)
    pie.title = "AWSB FINAL TEST REPORT"
    ws.add_chart(pie, "A6")
    wb.save(excelSheetName)

    

    
コード例 #30
0
ファイル: excel.py プロジェクト: suda-morris/TV-AutoTester
    def make_pie_chart(self):
        sheetnames = self.__wb.get_sheet_names()
        # ws = self.__wb.get_sheet_by_name(sheetnames[0])
        ws_new = self.__wb.create_sheet("pie chart")
        # 最后一行
        max_r = self.__ws.max_row

        pie = PieChart()
        labels = Reference(self.__ws, min_col=1, min_row=max_r - 2, max_row=max_r - 1)
        data = Reference(self.__ws, min_col=2, min_row=max_r - 3, max_row=max_r - 1)
        pie.add_data(data, titles_from_data=True)
        pie.set_categories(labels)
        pie.title = "Test Result Pie Chart"

        # Cut the first slice out of the pie
        slice = DataPoint(idx=0, explosion=20)
        pie.series[0].data_points = [slice]

        ws_new.add_chart(pie, "A1")
        self.__wb.save(self.__filename)
コード例 #31
0
ファイル: WordAnalysis.py プロジェクト: bjeong16/Python
def write_text_xlsx_file(used_list):
    file1 = open("WordAnalysis.txt", 'w')
    word_string = list()
    value_string = list()

    excel_file = Workbook()
    sheet = excel_file.active
    end_word = "A" + str(length)
    end_data = "B" + str(length)
    cell_range_word = sheet["A2": end_word]
    cell_range_data = sheet["B2": end_data]
    cell_range = sheet["A1": end_data]  # 엑셀 범위 지정
    sheet["A1"].value = "단어"
    sheet["B1"].value = "횟수"
    sheet['C1'].value = "많이 쓰인 단어"
    sheet['D1'].value = "횟수"
    marker = 2      # 셀번호

    for word in used_list:
        word_string.append(str(word))
        sheet["A" + str(marker)] = str(word)
        value_string.append(my_list.count(word))
        sheet["B"+str(marker)] = my_list.count(word)
        marker += 1
        file1.write(str(word) + "   :   " + str(my_list.count(word)) + "\n")   # 엑셀, 텍스트파일에 자료 이동

    row_num = 0
    for row in cell_range:
        if row[1].value > (length / 200):
            row_num += 1
            sheet.cell(row = row_num, column = 4, value = row[1].value)
            sheet.cell(row = row_num, column = 3, value = row[0].value)    # 파이 차트에서 쓸 값 정리

    pie = PieChart()
    labels = Reference(sheet, min_col = 3, min_row = 2, max_row = marker - 1)
    data = Reference(sheet, min_col = 4, min_row = 1, max_row = marker - 1)
    pie.add_data(data, titles_from_data=True)
    pie.set_categories(labels)
    pie.title = "Significant words in article"
    sheet.add_chart(pie, "M1")
    excel_file.save("Word_AnalyticsFinal.xlsx")
コード例 #32
0
def results_to_excel(position, techs, posts_seen, total_found):
    wb = Workbook()
    sheet = wb.active
    date = datetime.datetime.now()

    # initial format of the sheet
    sheet.cell(1, 1).value = 'Total posts viewed:'
    sheet.cell(1, 2).value = posts_seen
    sheet.cell(1, 4).value = 'Date:'
    sheet.cell(1, 5).value = date
    sheet.cell(3, 1).value = 'Tech/Lang'
    sheet.cell(3, 2).value = 'Number of appearances'
    sheet.cell(3, 3).value = 'Ocurrence percentage (*)'
    sheet['F19'] = "(*) respect to the total number of posts viewed"
    sheet.cell(1, 1).font = Font(name='Arial', bold=True, size=13)
    sheet.cell(1, 4).font = Font(name='Arial', bold=True, size=13)
    sheet.cell(3, 1).font = Font(name='Arial', bold=True, size=13)
    sheet.cell(3, 2).font = Font(name='Arial', bold=True, size=13)
    sheet.cell(3, 3).font = Font(name='Arial', bold=True, size=13)
    sheet['F19'].font = Font(name='Arial', size=13)

    # dump the techs dictionary the sheet
    row = 4
    for key, value in techs.items():
        sheet.cell(row, 1).value = key
        sheet.cell(row, 2).value = value
        sheet.cell(row, 3).value = f'{"{:.2f}".format(value*100/posts_seen)}%'
        sheet.cell(row, 3).alignment = Alignment(horizontal='right')
        row += 1

    # generates the chart of the results
    pie = PieChart()
    labels = Reference(sheet, min_col=1, min_row=4, max_row=row - 1)
    data = Reference(sheet, min_col=2, min_row=4, max_row=row - 1)
    pie.add_data(data)
    pie.set_categories(labels)
    pie.title = f'Number of times a technology/language was found from {total_found} techs found'

    sheet.add_chart(pie, 'F3')

    wb.save(f'{position} {date}.xlsx')
コード例 #33
0
def pie_chart(wb):
    table = wb.get_sheet_by_name(wb.get_sheet_names()[0])

    # 生成饼图对象
    pie = PieChart()
    #  饼图的标题
    pie.title = "接口测试统计"
    # 获取标签 获得的是范围
    labels = Reference(table, min_col=7, min_row=11, max_col=7, max_row=13)
    # 获取数据,需要注意的是excel中的data上面需要留个空行
    data = Reference(table, min_col=8, min_row=10, max_col=8, max_row=13)

    # 添加数据和标签到图表里
    pie.add_data(data, titles_from_data=True)
    pie.set_categories(labels)

    # 添加图表到sheet里
    table.add_chart(pie, "C21")

    #保存excel
    wb.save("test1.xlsx")
コード例 #34
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)
コード例 #35
0
ファイル: excel.py プロジェクト: czcorpus/kontext
    def export_pie_chart(self, data, title):
        wb = Workbook()
        ws = wb.active
        for row in [('', '')] + data:
            ws.append(row)

        pie = PieChart()
        labels = Reference(ws, min_col=1, min_row=2, max_row=len(data)+1)
        items = Reference(ws, min_col=2, min_row=1, max_row=len(data)+1)
        pie.add_data(items, titles_from_data=True)
        pie.set_categories(labels)
        pie.title = title
        ws.add_chart(pie, 'A{0}'.format(len(data)+3))

        output = StringIO.StringIO()
        wb.save(filename=output)
        output.flush()
        return output.getvalue()
コード例 #36
0
ファイル: gauge.py プロジェクト: BespokeInsights/openpyxl
c1.legend = None

ref = Reference(ws, min_col=1, min_row=2, max_row=5)
s1 = Series(ref, title_from_data=False)

slices = [DataPoint(idx=i) for i in range(4)]
slices[0].graphicalProperties.solidFill = "FF3300" # red
slices[1].graphicalProperties.solidFill = "FCF305" # yellow
slices[2].graphicalProperties.solidFill = "1FB714" # green
slices[3].graphicalProperties.noFill = True # invisible

s1.data_points = slices
c1.series = [s1]

# Second chart is a pie chart
c2 = PieChart(firstSliceAng=270)
c2.legend = None

ref = Reference(ws, min_col=2, min_row=2, max_col=2, max_row=4)
s2 = Series(ref, title_from_data=False)

slices = [DataPoint(idx=i) for i in range(3)]
slices[0].graphicalProperties.noFill = True # invisible
slices[1].graphicalProperties.solidFill = "000000" # black needle
slices[2].graphicalProperties.noFill = True # invisible
s2.data_points = slices
c2.series = [s2]

c1 += c2 # combine charts

ws.add_chart(c1, "D1")
コード例 #37
0
ファイル: pie.py プロジェクト: BespokeInsights/openpyxl
data = [
    ['Pie', 'Sold'],
    ['Apple', 50],
    ['Cherry', 30],
    ['Pumpkin', 10],
    ['Chocolate', 40],
]

wb = Workbook()
ws = wb.active

for row in data:
    ws.append(row)

pie = PieChart()
labels = Reference(ws, min_col=1, min_row=2, max_row=5)
data = Reference(ws, min_col=2, min_row=1, max_row=5)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = "Pies sold by category"

# Cut the first slice out of the pie
slice = DataPoint(idx=0, explosion=20)
pie.series[0].data_points = [slice]

ws.add_chart(pie, "D1")


ws = wb.create_sheet(title="Projection")
コード例 #38
0
ファイル: metricsummarysheet.py プロジェクト: lhoag64/Queries
  def drawSummary(self):
    self.ws.SetColWid(2,10)
    self.ws.SetColWid(3,60)
    self.ws.SetColWid(4,20)
    self.ws.SetColWid(5,20)
    self.ws.SetColWid(6,10)

    self.ws.DrawRegion(2,2,66,6,'thick','Blue 1')

    self.ws.ws.merge_cells('C3:E3')

    fmt1  = {'hAlign':'C','vAlign':'C','font':{'emph':'B','size':14}}
    fmt2  = {'hAlign':'L','vAlign':'C','border':{'A':'thin'},'fill':'Blue 2','font':{'emph':'B','size':11}}
    fmt2a = {'hAlign':'C','vAlign':'C','border':{'A':'thin'},'fill':'Blue 2','font':{'emph':'B','size':11}}
    fmt2b = {'hAlign':'C','vAlign':'C','border':{'A':'thin'},'fill':'Blue 2','numFmt':'0.0','font':{'emph':'B'}}
    fmt3  = {'hAlign':'L','vAlign':'C','border':{'A':'thin'},'fill':'Blue 3'}
    fmt3a = {'hAlign':'C','vAlign':'C','border':{'A':'thin'},'fill':'Blue 3'}
    fmt3b = {'hAlign':'C','vAlign':'C','border':{'A':'thin'},'fill':'Blue 3','numFmt':'0.0','font':{'emph':'B'}}
    fmt4  = {'hAlign':'L','vAlign':'C','border':{'A':'thin'},'fill':'Green 1'}
    fmt4b = {'hAlign':'C','vAlign':'C','border':{'A':'thin'},'fill':'Green 1','numFmt':'0.0','font':{'emph':'B'}}
    fmt5  = {'hAlign':'L','vAlign':'C','border':{'A':'thin'},'fill':'Orange 1'}
    fmt5b = {'hAlign':'C','vAlign':'C','border':{'A':'thin'},'fill':'Orange 1','numFmt':'0.0','font':{'emph':'B'}}
    fmt6  = {'hAlign':'L','vAlign':'C','border':{'A':'thin'},'fill':'Red 1'}
    fmt6b = {'hAlign':'C','vAlign':'C','border':{'A':'thin'},'fill':'Red 1','numFmt':'0.0','font':{'emph':'B'}}
    fmt7  = {'hAlign':'L','vAlign':'C','border':{'A':'thin'},'fill':'Yellow 1'}
    fmt7b = {'hAlign':'C','vAlign':'C','border':{'A':'thin'},'fill':'Yellow 1','numFmt':'0.0','font':{'emph':'B'}}

    self.ws.SetCell( 3, 3,'Monthly Summary Statement: Year To Date (YTD)',fmt1)

    self.ws.SetCell( 6, 3,'Hours',fmt2)
    self.ws.SetCell( 6, 4,'Hours',fmt2a)
    self.ws.SetCell( 6, 5,'As a % of Contracted',fmt2a)
    self.ws.SetCell( 7, 3,'Contracted number of hours',fmt3)
    self.ws.SetCell( 7, 4,'=SUM(GS243:HE243)',fmt3b)
    self.ws.SetCell( 7, 5,None,fmt3b)
    self.ws.SetCell( 8, 3,'Total Hours booked',fmt3)
    self.ws.SetCell( 8, 4,'=SUM(GS223:HE223)',fmt3b)
    self.ws.SetCell( 8, 5,None,fmt3b)
    self.ws.SetCell( 9, 3,'Additional hours worked over contracted',fmt3)
    self.ws.SetCell( 9, 4,'=SUM(GS242:HE242)',fmt3b)
    self.ws.SetCell( 9, 5,None,fmt3b)
    self.ws.SetCell(10, 3,'Number of Heads',fmt3)
    self.ws.ws.merge_cells('D10:E10')
    self.ws.SetCell(10, 4,16,fmt3b)
    self.ws.SetCell(10, 5,None,fmt3b)

    self.ws.ws.merge_cells('D12:E12')
    self.ws.ws.merge_cells('D13:E13')
    self.ws.ws.merge_cells('D14:E14')
    self.ws.ws.merge_cells('D15:E15')
    self.ws.ws.merge_cells('D16:E16')
    self.ws.SetCell(12, 3,'Activity (Summary) AVERAGE ACCROSS YEAR',fmt2)
    self.ws.SetCell(12, 4,'Percentage %',fmt2a)
    self.ws.SetCell(13, 3,'Utilisation (Customer Funded works)',fmt4)
    self.ws.SetCell(13, 4,'=HF224',fmt4b)
    self.ws.SetCell(13, 5,None,fmt4b)
    self.ws.SetCell(14, 3,'Utilisation (Pre Sales work)',fmt5)
    self.ws.SetCell(14, 4,'=HF229',fmt5b)
    self.ws.SetCell(14, 5,None,fmt5b)
    self.ws.SetCell(15, 3,'Utilisation (Downtime,Exc Leave and Sickness)',fmt6)
    self.ws.SetCell(15, 4,'=HF234',fmt6b)
    self.ws.SetCell(15, 5,None,fmt6b)
    self.ws.SetCell(16, 3,'Utilisation (Leave and Sickness)',fmt7)
    self.ws.SetCell(16, 4,'=HF239',fmt7b)
    self.ws.SetCell(16, 5,None,fmt7b)

    self.ws.SetCell(19, 3,'Activity (Detailed)',fmt2)
    self.ws.SetCell(19, 4,'Hours',fmt2a)
    self.ws.SetCell(19, 5,'As a % of Total',fmt2a)
    self.ws.SetCell(20, 3,'Support agreement (Software)',fmt4)
    self.ws.SetCell(20, 4,'=SUM(GS201:HE201)',fmt4b)
    self.ws.SetCell(20, 5,'=D20/SUM(D20:D27)*100',fmt4b)
    self.ws.SetCell(21, 3,'Hardware agreement (Hardware)',fmt4)
    self.ws.SetCell(21, 4,'=SUM(GS202:HE202)',fmt4b)
    self.ws.SetCell(21, 5,'=D21/SUM(D20:D27)*100',fmt4b)
    self.ws.SetCell(22, 3,'Post Sales support (SW-Customer Funded)',fmt4)
    self.ws.SetCell(22, 4,'=SUM(GS205:HE205)',fmt4b)
    self.ws.SetCell(22, 5,'=D22/SUM(D20:D27)*100',fmt4b)
    self.ws.SetCell(23, 3,'Post Sales support (HW-Customer Funded',fmt4)
    self.ws.SetCell(23, 4,'=SUM(GS206:HE206)',fmt4b)
    self.ws.SetCell(23, 5,'=D23/SUM(D20:D27)*100',fmt4b)
    self.ws.SetCell(24, 3,'NRE (Customer funded)',fmt4)
    self.ws.SetCell(24, 4,'=SUM(GS207:HE207)',fmt4b)
    self.ws.SetCell(24, 5,'=D24/SUM(D20:D27)*100',fmt4b)
    self.ws.SetCell(25, 3,'Training - Providing - Non Customer Specific',fmt4)
    self.ws.SetCell(25, 4,'=SUM(GS208:HE208)',fmt4b)
    self.ws.SetCell(25, 5,'=D25/SUM(D20:D27)*100',fmt4b)
    self.ws.SetCell(26, 3,'Training - Providing - Customer Specific',fmt4)
    self.ws.SetCell(26, 4,'=SUM(GS209:HE209)',fmt4b)
    self.ws.SetCell(26, 5,'=D26/SUM(D20:D27)*100',fmt4b)
    self.ws.SetCell(27, 3,'Post Sales Support (Warranty Period)',fmt4)
    self.ws.SetCell(27, 4,'=SUM(GS214:HE214)',fmt4b)
    self.ws.SetCell(27, 5,'=D27/SUM(D20:D27)*100',fmt4b)
    self.ws.SetCell(28, 3,'Pre Sales Support',fmt5)
    self.ws.SetCell(28, 4,'=SUM(GS203:HE203)',fmt5b)
    self.ws.SetCell(28, 5,'=D28/SUM(D28:D33)*100',fmt5b)
    self.ws.SetCell(29, 3,'Post Sales Support (Non contract)',fmt5)
    self.ws.SetCell(29, 4,'=SUM(GS204:HE204)',fmt5b)
    self.ws.SetCell(29, 5,'=D29/SUM(D28:D33)*100',fmt5b)
    self.ws.SetCell(30, 3,'Training - Receiving - Non Customer Specific',fmt5)
    self.ws.SetCell(30, 4,'=SUM(GS210:HE210)',fmt5b)
    self.ws.SetCell(30, 5,'=D30/SUM(D28:D33)*100',fmt5b)
    self.ws.SetCell(31, 3,'Training - Receiving - Customer Specific',fmt5)
    self.ws.SetCell(31, 4,'=SUM(GS211:HE211)',fmt5b)
    self.ws.SetCell(31, 5,'=D31/SUM(D28:D33)*100',fmt5b)
    self.ws.SetCell(32, 3,'Internal Business Meeting',fmt5)
    self.ws.SetCell(32, 4,'=SUM(GS212:HE212)',fmt5b)
    self.ws.SetCell(32, 5,'=D32/SUM(D28:D33)*100',fmt5b)
    self.ws.SetCell(33, 3,'Professional Services',fmt5)
    self.ws.SetCell(33, 4,'=SUM(GS213:HE213)',fmt5b)
    self.ws.SetCell(33, 5,'=D33/SUM(D28:D33)*100',fmt5b)
    self.ws.SetCell(34, 3,'Downtime - Excluding Leave & Sickness',fmt6)
    self.ws.SetCell(34, 4,'=SUM(GS232:HE232)',fmt6b)
    self.ws.SetCell(34, 5,'=D34/SUM(D34:D34)*100',fmt6b)
    self.ws.SetCell(35, 3,'Leave and Sickness',fmt7)
    self.ws.SetCell(35, 4,'=SUM(GS237:HE237)',fmt7b)
    self.ws.SetCell(35, 5,'=D35/SUM(D35:D35)*100',fmt7b)

    self.ws.SetCell(37, 3,'Customer split',fmt2)
    self.ws.SetCell(37, 4,'Hours',fmt2a)
    self.ws.SetCell(37, 5,'As a % of Total',fmt2a)
    self.ws.SetCell(38, 3,'Ericsson',fmt3)
    self.ws.SetCell(38, 4,'=SUM(GS247:HE247)',fmt3b)
    self.ws.SetCell(38, 5,'=D38/SUM(D38:D44)*100',fmt3b)
    self.ws.SetCell(39, 3,'Nokia',fmt3)
    self.ws.SetCell(39, 4,'=SUM(GS248:HE248)',fmt3b)
    self.ws.SetCell(39, 5,'=D39/SUM(D38:D44)*100',fmt3b)
    self.ws.SetCell(40, 3,'Alcatel-Lucent',fmt3)
    self.ws.SetCell(40, 4,'=SUM(GS249:HE249)',fmt3b)
    self.ws.SetCell(40, 5,'=D40/SUM(D38:D44)*100',fmt3b)
    self.ws.SetCell(41, 3,'Sum of all other customers',fmt3)
    self.ws.SetCell(41, 4,'=SUM(GS250:HE250)',fmt3b)
    self.ws.SetCell(41, 5,'=D41/SUM(D38:D44)*100',fmt3b)
    self.ws.SetCell(42, 3,'Cobham',fmt3)
    self.ws.SetCell(42, 4,'=SUM(GS251:HE251)',fmt3b)
    self.ws.SetCell(42, 5,'=D42/SUM(D38:D44)*100',fmt3b)
    self.ws.SetCell(43, 3,'Technical Training - All Types',fmt3)
    self.ws.SetCell(43, 4,'=SUM(GS252:HE251)',fmt3b)
    self.ws.SetCell(43, 5,'=D43/SUM(D38:D44)*100',fmt3b)
    self.ws.SetCell(44, 3,'Customer \'Other\'',fmt3)
    self.ws.SetCell(44, 4,'=SUM(GS253:HE251)',fmt3b)
    self.ws.SetCell(44, 5,'=D44/SUM(D38:D44)*100',fmt3b)

    self.ws.SetCell(46, 3,'Labour and Travel',fmt2)
    self.ws.SetCell(46, 4,'Hours',fmt2a)
    self.ws.SetCell(46, 5,'As a % of Total',fmt2a)
    self.ws.SetCell(47, 3,'Number of Labour Hours',fmt3)
    self.ws.SetCell(47, 4,'=SUM(GS217:HE217)',fmt3b)
    self.ws.SetCell(47, 5,'=D47/SUM(D47:D49)*100',fmt3b)
    self.ws.SetCell(48, 3,'Number of Labour Hours',fmt3)
    self.ws.SetCell(48, 4,'=SUM(GS218:HE218)',fmt3b)
    self.ws.SetCell(48, 5,'=D48/SUM(D47:D49)*100',fmt3b)
    self.ws.SetCell(49, 3,'Number of Labour Hours',fmt3)
    self.ws.SetCell(49, 4,'=SUM(GS219:HE219)',fmt3b)
    self.ws.SetCell(49, 5,'=D49/SUM(D47:D49)*100',fmt3b)

    self.ws.DrawBorder( 6, 3, 10, 5,'medium')
    self.ws.DrawBorder( 6, 3,  6, 5,'medium')
    self.ws.DrawBorder(12, 3, 16, 5,'medium')
    self.ws.DrawBorder(12, 3, 12, 5,'medium')
    self.ws.DrawBorder(19, 3, 35, 5,'medium')
    self.ws.DrawBorder(19, 3, 19, 5,'medium')
    self.ws.DrawBorder(37, 3, 44, 5,'medium')
    self.ws.DrawBorder(37, 3, 37, 5,'medium')
    self.ws.DrawBorder(46, 3, 49, 5,'medium')
    self.ws.DrawBorder(46, 3, 46, 5,'medium')


    ltsChart = PieChart()
    data   = Reference(self.ws.ws,min_col=5,min_row=47,max_row=49)
    labels = Reference(self.ws.ws,min_col=3,min_row=47,max_row=49)
    ltsChart.add_data(data)
    ltsChart.set_categories(labels)
    ltsChart.title = 'Labour Vs Travel'
    self.ws.ws.add_chart(ltsChart,'H46')
コード例 #39
0
from openpyxl import Workbook

from openpyxl.chart import PieChart, Reference, Series

wb = Workbook()
ws = wb.active
cs = wb.create_chartsheet()

rows = [
    ["Bob", 3],
    ["Harry", 2],
    ["James", 4],
]

for row in rows:
    ws.append(row)


chart = PieChart()
labels = Reference(ws, min_col=1, min_row=1, max_row=3)
data = Reference(ws, min_col=2, min_row=1, max_row=3)
chart.series = (Series(data),)
chart.title = "PieChart"

cs.add_chart(chart)

wb.save("demo.xlsx")
コード例 #40
0
ファイル: copyandwrite.py プロジェクト: Cartmanfku/forbigbaby
#c1.y_axis.title = 'Size'
#c1.x_axis.title = 'Test Number'

data = Reference(ws2, min_col=1, min_row=2, max_col=13, max_row=6)
#print data
c1.add_data(data, titles_from_data=True,from_rows=True)

ws2.add_chart(c1, "A20")

#画饼图,先新建一个表单

ws3 = wb2.create_sheet()

_ = ws3.cell(column=1, row=1, value="项目")
_ = ws3.cell(column=2, row=1, value="总计")
for r in range(2,7):
    _ = ws3.cell(column=1, row=r, value=ws2.rows[r-1][0].value)
    _ = ws3.cell(column=2, row=r, value=ws2.rows[r-1][total_column-1].value)


pie = PieChart()
labels = Reference(ws3, min_col=1, min_row=2, max_row=6)
data = Reference(ws3, min_col=2, min_row=1, max_row=6)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = "2015年%s构成图"%ws.rows[5][0].value
ws2.add_chart(pie, "H20")



wb2.save(filename = dest_filename)
コード例 #41
0
data = [
    ['Category', 'Number'],
    ['Paid', paidStaff],
    ['Volunteer', volunteerStaff],
]

wb = Workbook()
ws = wb.active
ws = wb.create_sheet(title="Ministry Paid")


for row in data:
    ws.append(row)

pie = PieChart()
labels = Reference(ws, min_col=1, min_row=2, max_row=5)
data = Reference(ws, min_col=2, min_row=1, max_row=5)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = "Volunteers vs. Paid"

# Cut the first slice out of the pie
slice = DataPoint(idx=0, explosion=20)
pie.series[0].data_points = [slice]

ws.add_chart(pie, "D1")