Python chart.BarChart类代码示例

    def test_write_no_ascii(self):

        ws = self.make_worksheet()
        ws.append(["D\xc3\xbcsseldorf"] * 10)
        serie = Serie(values=Reference(ws, (0, 0), (0, 9)), legend=Reference(ws, (1, 0), (1, 9)))
        c = BarChart()
        c.add_serie(serie)
        cw = ChartWriter(c)

def negative(wb):
    ws = wb.create_sheet(1, "Negative")
    for i in range(-5, 5):
        ws.append([i])
    chart = BarChart()
    values = Reference(ws, (0, 0), (9, 0))
    series = Series(values)
    chart.append(series)
    ws.add_chart(chart)

def numbers(wb):
    ws = wb.create_sheet(1, "Numbers")
    for i in range(10):
        ws.append([i])
    chart = BarChart()
    values = Reference(ws, (0, 0), (9, 0))
    series = Series(values)
    chart.append(series)
    ws.add_chart(chart)

def dates(wb):
    ws = wb.create_sheet(3, "Dates")
    for i in range(1, 10):
        ws.append([date(2013, i, 1), i])
    chart = BarChart()
    values = Reference(ws, (0, 1), (8, 1))
    labels = Reference(ws, (0, 0), (8, 0))
    labels.number_format = 'd-mmm'
    series = Series(values, labels=labels)
    chart.append(series)
    ws.add_chart(chart)

def letters(wb):
    ws = wb.create_sheet(2, "Letters")
    for idx, l in enumerate("ABCDEFGHIJ"):
        ws.append([l, idx, idx])
    chart = BarChart()
    labels = Reference(ws, (0, 0), (9, 0))
    values = Reference(ws, (0, 1), (9, 1))
    series = Series(values, labels=labels)
    chart.append(series)
    #  add second series
    values = Reference(ws, (0, 2), (9, 2))
    series = Series(values, labels=labels)
    chart.append(series)
    ws.add_chart(chart)

    def draw_chart(self, wsheet, series, left=0, title=""):
        chart = BarChart()
        chart.title = title
        chart.drawing.left = 500 + left
        chart.drawing.top = 250 + left
        chart.drawing.height = 200
        chart.drawing.width = 500

        i = 0
        for serie in series:
            if len(serie) == 4:

                x1 = int(serie[0])
                y1 = int(serie[1])
                x2 = int(serie[2])
                y2 = int(serie[3])

                if x2 > 3:

                    if i == 0:
                        legend = Reference(wsheet, (0, 0))
                        labels = Reference(wsheet, (x1, 0), (x2, 0))
                    else:
                        if y1 < 13:
                            legend = Reference(wsheet, (0, 4))
                        if y1 >= 13:
                            legend = Reference(wsheet, (0, 13))

                    #value = wsheet.cell(row=4,column=0).value
                    title = wsheet.title
                    if title.find("Rate") < 0:
                        isLabel = True
                    else:
                        if len(title) > 12:
                            isLabel = True
                        else:
                            isLabel = False

                    if i == 0 and isLabel:  # type(value).__name__ <> 'int':
                        seri = Serie(
                            Reference(wsheet, (x1, y1), (x2, y2)), labels=labels, legend=legend)
                    else:
                        seri = Serie(
                            Reference(wsheet, (x1, y1), (x2, y2)), legend=legend)

                    chart.add_serie(seri)

            i += 1

        wsheet.add_chart(chart)

class TestChartWriter(object):

    def setUp(self):

        wb = Workbook()
        ws = wb.get_active_sheet()
        ws.title = u'data'
        for i in range(10):
            ws.cell(row = i, column = 0).value = i
        self.chart = BarChart()
        self.chart.title = 'TITLE'
        self.chart.add_serie(Serie(Reference(ws, (0, 0), (10, 0))))
        self.chart._series[-1].color = Color.GREEN
        self.cw = ChartWriter(self.chart)
        self.root = Element('test')

    def test_write_title(self):
        self.cw._write_title(self.root)
        eq_(get_xml(self.root), '<?xml version=\'1.0\' encoding=\'UTF-8\'?><test><c:title><c:tx><c:rich><a:bodyPr /><a:lstStyle /><a:p><a:pPr><a:defRPr /></a:pPr><a:r><a:rPr lang="fr-FR" /><a:t>TITLE</a:t></a:r></a:p></c:rich></c:tx><c:layout /></c:title></test>')

    def test_write_xaxis(self):

        self.cw._write_axis(self.root, self.chart.x_axis, 'c:catAx')
        eq_(get_xml(self.root), '<?xml version=\'1.0\' encoding=\'UTF-8\'?><test><c:catAx><c:axId val="60871424" /><c:scaling><c:orientation val="minMax" /></c:scaling><c:axPos val="b" /><c:tickLblPos val="nextTo" /><c:crossAx val="60873344" /><c:crosses val="autoZero" /><c:auto val="1" /><c:lblAlgn val="ctr" /><c:lblOffset val="100" /></c:catAx></test>')

    def test_write_yaxis(self):

        self.cw._write_axis(self.root, self.chart.y_axis, 'c:valAx')
        eq_(get_xml(self.root), '<?xml version=\'1.0\' encoding=\'UTF-8\'?><test><c:valAx><c:axId val="60873344" /><c:scaling><c:orientation val="minMax" /><c:max val="10.0" /><c:min val="0" /></c:scaling><c:axPos val="l" /><c:majorGridlines /><c:numFmt formatCode="General" sourceLinked="1" /><c:tickLblPos val="nextTo" /><c:crossAx val="60871424" /><c:crosses val="autoZero" /><c:crossBetween val="between" /><c:majorUnit val="2.0" /></c:valAx></test>')

    def test_write_series(self):

        self.cw._write_series(self.root)
        eq_(get_xml(self.root), '<?xml version=\'1.0\' encoding=\'UTF-8\'?><test><c:ser><c:idx val="0" /><c:order val="0" /><c:spPr><a:solidFill><a:srgbClr val="00FF00" /></a:solidFill><a:ln><a:solidFill><a:srgbClr val="00FF00" /></a:solidFill></a:ln></c:spPr><c:marker><c:symbol val="none" /></c:marker><c:val><c:numRef><c:f>data!$A$1:$A$11</c:f><c:numCache><c:formatCode>General</c:formatCode><c:ptCount val="11" /><c:pt idx="0"><c:v>0</c:v></c:pt><c:pt idx="1"><c:v>1</c:v></c:pt><c:pt idx="2"><c:v>2</c:v></c:pt><c:pt idx="3"><c:v>3</c:v></c:pt><c:pt idx="4"><c:v>4</c:v></c:pt><c:pt idx="5"><c:v>5</c:v></c:pt><c:pt idx="6"><c:v>6</c:v></c:pt><c:pt idx="7"><c:v>7</c:v></c:pt><c:pt idx="8"><c:v>8</c:v></c:pt><c:pt idx="9"><c:v>9</c:v></c:pt><c:pt idx="10"><c:v>None</c:v></c:pt></c:numCache></c:numRef></c:val></c:ser></test>')

    def test_write_legend(self):

        self.cw._write_legend(self.root)
        eq_(get_xml(self.root), '<?xml version=\'1.0\' encoding=\'UTF-8\'?><test><c:legend><c:legendPos val="r" /><c:layout /></c:legend></test>')

    def test_write_print_settings(self):

        self.cw._write_print_settings(self.root)
        eq_(get_xml(self.root), '<?xml version=\'1.0\' encoding=\'UTF-8\'?><test><c:printSettings><c:headerFooter /><c:pageMargins b="0.75" footer="0.3" header="0.3" l="0.7" r="0.7" t="0.75" /><c:pageSetup /></c:printSettings></test>')

    def test_write_chart(self):

        self.cw._write_chart(self.root)
        eq_(get_xml(self.root), '<?xml version=\'1.0\' encoding=\'UTF-8\'?><test><c:chart><c:title><c:tx><c:rich><a:bodyPr /><a:lstStyle /><a:p><a:pPr><a:defRPr /></a:pPr><a:r><a:rPr lang="fr-FR" /><a:t>TITLE</a:t></a:r></a:p></c:rich></c:tx><c:layout /></c:title><c:plotArea><c:layout><c:manualLayout><c:layoutTarget val="inner" /><c:xMode val="edge" /><c:yMode val="edge" /><c:x val="1.28571428571" /><c:y val="0.2125" /><c:w val="0.6" /><c:h val="0.6" /></c:manualLayout></c:layout><c:barChart><c:barDir val="col" /><c:grouping val="clustered" /><c:ser><c:idx val="0" /><c:order val="0" /><c:spPr><a:solidFill><a:srgbClr val="00FF00" /></a:solidFill><a:ln><a:solidFill><a:srgbClr val="00FF00" /></a:solidFill></a:ln></c:spPr><c:marker><c:symbol val="none" /></c:marker><c:val><c:numRef><c:f>data!$A$1:$A$11</c:f><c:numCache><c:formatCode>General</c:formatCode><c:ptCount val="11" /><c:pt idx="0"><c:v>0</c:v></c:pt><c:pt idx="1"><c:v>1</c:v></c:pt><c:pt idx="2"><c:v>2</c:v></c:pt><c:pt idx="3"><c:v>3</c:v></c:pt><c:pt idx="4"><c:v>4</c:v></c:pt><c:pt idx="5"><c:v>5</c:v></c:pt><c:pt idx="6"><c:v>6</c:v></c:pt><c:pt idx="7"><c:v>7</c:v></c:pt><c:pt idx="8"><c:v>8</c:v></c:pt><c:pt idx="9"><c:v>9</c:v></c:pt><c:pt idx="10"><c:v>None</c:v></c:pt></c:numCache></c:numRef></c:val></c:ser><c:marker val="1" /><c:axId val="60871424" /><c:axId val="60873344" /></c:barChart><c:catAx><c:axId val="60871424" /><c:scaling><c:orientation val="minMax" /></c:scaling><c:axPos val="b" /><c:tickLblPos val="nextTo" /><c:crossAx val="60873344" /><c:crosses val="autoZero" /><c:auto val="1" /><c:lblAlgn val="ctr" /><c:lblOffset val="100" /></c:catAx><c:valAx><c:axId val="60873344" /><c:scaling><c:orientation val="minMax" /><c:max val="10.0" /><c:min val="0" /></c:scaling><c:axPos val="l" /><c:majorGridlines /><c:numFmt formatCode="General" sourceLinked="1" /><c:tickLblPos val="nextTo" /><c:crossAx val="60871424" /><c:crosses val="autoZero" /><c:crossBetween val="between" /><c:majorUnit val="2.0" /></c:valAx></c:plotArea><c:legend><c:legendPos val="r" /><c:layout /></c:legend><c:plotVisOnly val="1" /></c:chart></test>')

    def build_graph_in_excel(self, ws, table, start_row_in_excel, start_column_in_excel):
        columns_list_from_db = self.get_columns_list_without_id(table)[1:-1].split(', ')
        count_rows_in_table_db = self.get_count_rows(table)+1
        start_column_number = self.get_number_for_letter(start_column_in_excel) + 1

        chart = BarChart()
        chart.title = '{}'.format(table)
        chart.style = 10
        #chart.x_axis.title = 'TEst'
        chart.y_axis.title = 'Percentage'

        cats = Reference(ws,
                         min_col=start_column_number,
                         min_row=start_row_in_excel + 1,
                         max_row='{}'.format(count_rows_in_table_db)
                         )
        data1 = Reference(ws,
                          min_col=10,
                          min_row=1,
                          max_row='{}'.format(count_rows_in_table_db)
                          )
        data2 = Reference(ws, min_col=14, min_row=1, max_row='{}'.format(count_rows_in_table_db))

        chart.add_data(data1, titles_from_data=True)
        chart.add_data(data2, titles_from_data=True)
        chart.set_categories(cats)

        ws.add_chart(chart, 'A15')

def chart_openpyxl(worksheet):

    global TOTAL_DAYS

    print 'generate Chart...'
    # create open-high-low-close chart 
    stock = StockChart()
    dates = Reference(worksheet, min_col=1, min_row=31, max_row=TOTAL_DAYS+1)
    data = Reference(worksheet, min_col=2, max_col=5, min_row=31, max_row=TOTAL_DAYS+1)
    stock.add_data(data, titles_from_data= True)
    stock.set_categories(dates)
    for s in stock.series:
        s.graphicalProperties.line.noFill = True 
    stock.hiLowLines = ChartLines()
    stock.upDownBars = UpDownBars()

    # Excel is broken and needs a cache of values in order to display hiLoLines :-/
    from openpyxl.chart.data_source import NumData, NumVal
    pts = [NumVal(idx=i) for i in range(len(data) - 1)]
    cache = NumData(pt=pts)

    # add dummy cache
    stock.series[-1].val.numRef.numCache = cache

    # create 5-30MA chart
    bar = BarChart()
    data = Reference(worksheet, min_col=9, min_row=31, max_row=TOTAL_DAYS+1)
    bar.add_data(data, titles_from_data=False)
    bar.set_categories(dates)
    
    # merge K-Line & 5-30MA chart
    stock_tmp = deepcopy(bar)
    bar_tmp = deepcopy(stock)
    stock_tmp.title = "TWSE Mometum Chart"
    stock_tmp.height = 15
    stock_tmp.width = 25
    stock_tmp.y_axis.axId = 20 
    stock_tmp.z_axis = bar_tmp.y_axis
    stock_tmp.y_axis.crosses = "max"
    stock_tmp.legend = None                  # hidden series label name 
    bar_tmp.y_axis.majorGridlines = None
    bar_tmp.y_axis.title = "Price"
    stock_tmp += bar_tmp

    worksheet.add_chart(stock_tmp, "A{}".format(TOTAL_DAYS+2))

def add_charts(sheet, mark, model, data, cats):
    """draw charts with median and average mileages for each year"""
    chart = BarChart()
    chart.title = mark + " " + model
    chart.type = "col"
    chart.y_axis.title = 'Mileage'
    chart.x_axis.title = 'Year of prod.'

    chart.add_data(data, titles_from_data=True)
    chart.set_categories(cats)
    sheet.add_chart(chart, "E3")

    def setUp(self):

        wb = Workbook()
        ws = wb.get_active_sheet()
        ws.title = u'data'
        for i in range(10):
            ws.cell(row = i, column = 0).value = i
        self.chart = BarChart()
        self.chart.title = 'TITLE'
        self.chart.add_serie(Serie(Reference(ws, (0, 0), (10, 0))))
        self.chart._series[-1].color = Color.GREEN
        self.cw = ChartWriter(self.chart)
        self.root = Element('test')

def excel_mtf_barchart(ws):
    chart1 = BarChart()
    chart1.type = "col"
    chart1.style = 10
    chart1.title = "MTF Chart"
    chart1.y_axis.title = 'MTF'
    chart1.x_axis.title = 'ROI'
# Select all data include title
    data = Reference(ws, min_col=2, min_row=1, max_row=19, max_col=2)
# Select data only
    cats = Reference(ws, min_col=1, min_row=2, max_row=18)
    chart1.add_data(data, titles_from_data=True)
    chart1.set_categories(cats)
    chart1.shape = 4
    chart1.x_axis.scaling.min = 0
    chart1.x_axis.scaling.max = 18
    chart1.y_axis.scaling.min = 0
    chart1.y_axis.scaling.max = 1
    ws.add_chart(chart1, "G1")

def excel_sfr_barchart(ws):
    chart1 = BarChart()
    chart1.type = "col"
    chart1.style = 12
    chart1.title = "SFR Chart"
    chart1.y_axis.title = 'SFR'
    chart1.x_axis.title = 'ROI'
# Select all data include title
    data = Reference(ws, min_col=5, min_row=1, max_row=37, max_col=5)
# Select data only
    cats = Reference(ws, min_col=4, min_row=2, max_row=37)
    chart1.add_data(data, titles_from_data=True)
    chart1.set_categories(cats)
    chart1.shape = 4
    chart1.x_axis.scaling.min = 0
    chart1.x_axis.scaling.max = 37
    chart1.y_axis.scaling.min = 0
    chart1.y_axis.scaling.max = 1
    ws.add_chart(chart1, "G21")

def save_excel_data():
    count = 0
    wb=openpyxl.Workbook()
    ws1=wb.active
    ws1=wb.create_sheet("mysheet2")
    ws1.append(['이름','국어','영어'])
    for n in myDataList:
        count += 1
        ws1.append([n['name'],n['korean'],n['english']])
 
    #Chart 1
    chart1 = BarChart()
    chart1.style=11
    chart1.title='Bar chart'
    chart1.x_axis.title='이름'
    chart1.y_axis.title='점수'
    data = Reference(ws1, min_col=1, min_row=1, max_row=count+1, max_col=3)
    cat = Reference(ws1, min_col=1, min_row=2, max_row=count+1)
    chart1.add_data(data, titles_from_data=True)
    chart1.set_categories(cat)
    ws1.add_chart(chart1, 'F1')

    wb.save('day2result.xlsx')
    print('write...done')

def insertarGraficoMercadoTC(ws):
    maxCol = col2num(getMaxCol(ws, 'B', 46))

    c1 = BarChart()
    v1 = Reference(ws, min_col=1, min_row=47, max_col=maxCol)
    c1.add_data(v1, titles_from_data=True, from_rows=True)
    c1.y_axis.scaling.min = 0
    c1.y_axis.majorGridlines = None

    c2 = LineChart()
    v2 = Reference(ws, min_col=1, min_row=48, max_col=maxCol)
    c2.add_data(v2, titles_from_data=True, from_rows=True)
    c2.y_axis.axId = 200
    c1.z_axis = c2.y_axis

    categories = Reference(ws, min_col=2, min_row=46, max_col=maxCol)
    c1.set_categories(categories)
    
    # Display y-axis of the second chart on the right by setting it to cross the x-axis at its maximum
    c1.y_axis.crosses = "max"
    c1 += c2

    ws.add_chart(c1, "A54")