how to use openpyxl to create chart in excel

关翰
2023-12-01

I collected all the supported chart in openpyxl in one script, please check it.
But the chart which openpyxl created seems not beautiful as manual created in excel.

from openpyxl import Workbook, load_workbook
from openpyxl.comments import Comment
from openpyxl.utils import units

from openpyxl.chart.series import DataPoint

from openpyxl.chart.axis import DateAxis, ChartLines
from openpyxl.chart.updown_bars import UpDownBars

import openpyxl
import random
from datetime import date


from openpyxl.chart import (
    BarChart,
    AreaChart,
    LineChart,
    BubbleChart,
    PieChart,
    ProjectedPieChart,
    ScatterChart,
    DoughnutChart,
    RadarChart,
    Reference,
    StockChart,
    Series,
)


def create_new_file(file_name):
    wb = Workbook()
    wb.save(file_name)

def create_chart(file_name):
    wb = Workbook()

    create_bar_chart(wb)
    create_bar2_chart(wb)
    create_area_chart(wb)
    create_line_chart(wb)
    create_line2_chart(wb)
    create_bubble_chart(wb)
    create_pie_chart(wb)
    create_scatter_chart(wb)
    create_doughnut_chart(wb)
    create_radar_chart(wb)
    create_stock_chart(wb)

    wb.save(file_name)


def create_stock_chart(wb):
    ws = wb.create_sheet("stock")
    rows = [
        ['Date', 'Volume', 'Open', 'High', 'Low', 'Close'],
        ['2015-01-01', 20000, 26.2, 27.20, 23.49, 25.45, ],
        ['2015-01-02', 10000, 25.45, 25.03, 19.55, 23.05, ],
        ['2015-01-03', 15000, 23.05, 24.46, 20.03, 22.42, ],
        ['2015-01-04', 2000, 22.42, 23.97, 20.07, 21.90, ],
        ['2015-01-05', 12000, 21.9, 23.65, 19.50, 21.51, ],
    ]

    for row in rows:
        ws.append(row)

    # High-low-close
    c1 = StockChart()
    labels = Reference(ws, min_col=1, min_row=2, max_row=6)
    data = Reference(ws, min_col=4, max_col=6, min_row=1, max_row=6)
    c1.add_data(data, titles_from_data=True)
    c1.set_categories(labels)
    for s in c1.series:
        s.graphicalProperties.line.noFill = True
    # marker for close
    s.marker.symbol = "dot"
    s.marker.size = 5
    c1.title = "High-low-close"
    c1.hiLowLines = ChartLines()

    # 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)
    c1.series[-1].val.numRef.numCache = cache

    ws.add_chart(c1, "A10")

    # Open-high-low-close
    c2 = StockChart()
    data = Reference(ws, min_col=3, max_col=6, min_row=1, max_row=6)
    c2.add_data(data, titles_from_data=True)
    c2.set_categories(labels)
    for s in c2.series:
        s.graphicalProperties.line.noFill = True
    c2.hiLowLines = ChartLines()
    c2.upDownBars = UpDownBars()
    c2.title = "Open-high-low-close"

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

    ws.add_chart(c2, "K10")

    # Create bar chart for volume

    bar = BarChart()
    data = Reference(ws, min_col=2, min_row=1, max_row=6)
    bar.add_data(data, titles_from_data=True)
    bar.set_categories(labels)

    from copy import deepcopy

    # Volume-high-low-close
    b1 = deepcopy(bar)
    c3 = deepcopy(c1)
    c3.y_axis.majorGridlines = None
    c3.y_axis.title = "Price"
    b1.y_axis.axId = 20
    b1.z_axis = c3.y_axis
    b1.y_axis.crosses = "max"
    b1 += c3

    c3.title = "High low close volume"

    ws.add_chart(b1, "A27")

    ## Volume-open-high-low-close
    b2 = deepcopy(bar)
    c4 = deepcopy(c2)
    c4.y_axis.majorGridlines = None
    c4.y_axis.title = "Price"
    b2.y_axis.axId = 20
    b2.z_axis = c4.y_axis
    b2.y_axis.crosses = "max"
    b2 += c4

    ws.add_chart(b2, "K27")


def create_radar_chart(wb):
    ws = wb.create_sheet("radar")
    rows = [
        ['Month', "Bulbs", "Seeds", "Flowers", "Trees & shrubs"],
        ['Jan', 0, 2500, 500, 0, ],
        ['Feb', 0, 5500, 750, 1500],
        ['Mar', 0, 9000, 1500, 2500],
        ['Apr', 0, 6500, 2000, 4000],
        ['May', 0, 3500, 5500, 3500],
        ['Jun', 0, 0, 7500, 1500],
        ['Jul', 0, 0, 8500, 800],
        ['Aug', 1500, 0, 7000, 550],
        ['Sep', 5000, 0, 3500, 2500],
        ['Oct', 8500, 0, 2500, 6000],
        ['Nov', 3500, 0, 500, 5500],
        ['Dec', 500, 0, 100, 3000],
    ]

    for row in rows:
        ws.append(row)

    chart = RadarChart()
    chart.type = "filled"
    labels = Reference(ws, min_col=1, min_row=2, max_row=13)
    data = Reference(ws, min_col=2, max_col=5, min_row=1, max_row=13)
    chart.add_data(data, titles_from_data=True)
    chart.set_categories(labels)
    chart.style = 26
    chart.title = "Garden Centre Sales"
    chart.y_axis.delete = True

    ws.add_chart(chart, "A17")


def create_doughnut_chart(wb):
    ws = wb.create_sheet("doughnut")
    data = [
        ['Pie', 2014, 2015],
        ['Plain', 40, 50],
        ['Jam', 2, 10],
        ['Lime', 20, 30],
        ['Chocolate', 30, 40],
    ]

    for row in data:
        ws.append(row)

    chart = DoughnutChart()
    labels = Reference(ws, min_col=1, min_row=2, max_row=5)
    data = Reference(ws, min_col=2, min_row=1, max_row=5)
    chart.add_data(data, titles_from_data=True)
    chart.set_categories(labels)
    chart.title = "Doughnuts sold by category"
    chart.style = 26

    # Cut the first slice out of the doughnut
    slices = [DataPoint(idx=i) for i in range(4)]
    plain, jam, lime, chocolate = slices
    chart.series[0].data_points = slices
    plain.graphicalProperties.solidFill = "FAE1D0"
    jam.graphicalProperties.solidFill = "BB2244"
    lime.graphicalProperties.solidFill = "22DD22"
    chocolate.graphicalProperties.solidFill = "61210B"
    chocolate.explosion = 10

    ws.add_chart(chart, "E1")

    from copy import deepcopy

    chart2 = deepcopy(chart)
    chart2.title = None
    data = Reference(ws, min_col=3, min_row=1, max_row=5)
    series2 = Series(data, title_from_data=True)
    series2.data_points = slices
    chart2.series.append(series2)

    ws.add_chart(chart2, "E17")


def create_scatter_chart(wb):
    ws = wb.create_sheet("scatter")
    rows = [
        ['Size', 'Batch 1', 'Batch 2', 'Batch 3', 'Batch 4', 'Batch 5', 'Batch 6', 'Batch 7'],
        [2, 70, 40, 20, 30, 10, 10, 30],
        [3, 20, 30, 10, 20, 48, 20, 25],
        [4, 30, 40, 56, 10, 58, 40, 30],
        [5, 30, 60, 32, 80, 33, 20, 25],
        [6, 35, 75, 15, 15, 20, 35, 35],
        [7, 40, 90, 20, 10, 25, 40, 40],
    ]

    for row in rows:
        ws.append(row)

    chart = ScatterChart()
    chart.title = "Scatter Chart"
    chart.style = 1
    chart.x_axis.title = 'Size'
    chart.y_axis.title = 'Percentage'

    xvalues = Reference(ws, min_col=1, min_row=2, max_row=7)
    for i in range(2, 9):
        values = Reference(ws, min_col=i, min_row=1, max_row=7)
        series = Series(values, xvalues, title_from_data=True)
        #  {'triangle', 'dash', 'x', 'auto', 'diamond', 'circle', 'star', 'picture', 'square', 'dot', 'plus'}
        series.marker = openpyxl.chart.marker.Marker('circle')
        series.graphicalProperties.line.noFill = True
        # print(dir(series.graphicalProperties))
        chart.series.append(series)

    ws.add_chart(chart, "A10")

    from copy import deepcopy
    for i in range(2):  # max is 48
        if i > 1:
            chart1 = deepcopy(chart)
            chart1.style = i
            ws.add_chart(chart1, "A" + str(i*15))


def create_pie_chart(wb):
    ws = wb.create_sheet("pie")
    data = [
        ['Pie', 'Sold'],
        ['Apple', 50],
        ['Cherry', 30],
        ['Pumpkin', 10],
        ['Chocolate', 40],
    ]

    for row in data:
        ws.append(row)

    pie = PieChart()
    labels = Reference(ws, min_col=1, min_row=2, max_col=1, 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="pie_projection")

    data = [
        ['Page', 'Views'],
        ['Search', 95],
        ['Products', 4],
        ['Offers', 0.5],
        ['Sales', 0.5],
    ]

    for row in data:
        ws.append(row)

    projected_pie = ProjectedPieChart()
    projected_pie.type = "pie"
    projected_pie.splitType = "val"  # split by value
    labels = Reference(ws, min_col=1, min_row=2, max_row=5)
    data = Reference(ws, min_col=2, min_row=1, max_row=5)
    projected_pie.add_data(data, titles_from_data=True)
    projected_pie.set_categories(labels)

    ws.add_chart(projected_pie, "A10")

    from copy import deepcopy
    projected_bar = deepcopy(projected_pie)
    projected_bar.type = "bar"
    projected_bar.splitType = 'pos'  # split by position

    ws.add_chart(projected_bar, "A27")

def create_bubble_chart(wb):
    ws = wb.create_sheet("bubble")
    rows = [
        ("Number of Products", "Sales in USD", "Market share"),
        (14, 12200, 15),
        (20, 60000, 33),
        (18, 24400, 10),
        (22, 32000, 42),
        (),
        (12, 8200, 18),
        (15, 50000, 30),
        (19, 22400, 15),
        (25, 25000, 50),
    ]

    for row in rows:
        ws.append(row)

    chart = BubbleChart()
    chart.style = 18  # use a preset style

    # add the first series of data
    xvalues = Reference(ws, min_col=1, min_row=2, max_row=5)
    yvalues = Reference(ws, min_col=2, min_row=2, max_row=5)
    size = Reference(ws, min_col=3, min_row=2, max_row=5)
    series = Series(values=yvalues, xvalues=xvalues, zvalues=size, title="2013")
    chart.series.append(series)

    # add the second
    xvalues = Reference(ws, min_col=1, min_row=7, max_row=10)
    yvalues = Reference(ws, min_col=2, min_row=7, max_row=10)
    size = Reference(ws, min_col=3, min_row=7, max_row=10)
    series = Series(values=yvalues, xvalues=xvalues, zvalues=size, title="2014")
    chart.series.append(series)

    # place the chart starting in cell E1
    ws.add_chart(chart, "E1")


def create_bar2_chart(wb):
    ws = wb.create_sheet("bar2")
    rows = [
        ('Number', 'Batch 1', 'Batch 2'),
        (2, 10, 30),
        (3, 40, 60),
        (4, 50, 70),
        (5, 20, 10),
        (6, 10, 40),
        (7, 50, 30),
    ]

    for row in rows:
        ws.append(row)

    chart1 = BarChart()
    chart1.type = "col"
    chart1.style = 10
    chart1.title = "Bar Chart"
    chart1.y_axis.title = 'Test number'
    chart1.x_axis.title = 'Sample length (mm)'

    data = Reference(ws, min_col=2, min_row=1, max_row=7, max_col=3)
    cats = Reference(ws, min_col=1, min_row=2, max_row=7)
    chart1.add_data(data, titles_from_data=True)
    chart1.set_categories(cats)
    chart1.shape = 4
    ws.add_chart(chart1, "A10")

    from copy import deepcopy

    chart2 = deepcopy(chart1)
    chart2.style = 11
    chart2.type = "bar"
    chart2.title = "Horizontal Bar Chart"

    ws.add_chart(chart2, "K10")

    chart3 = deepcopy(chart1)
    chart3.type = "col"
    chart3.style = 12
    chart3.grouping = "stacked"
    chart3.overlap = 100
    chart3.title = 'Stacked Chart'

    ws.add_chart(chart3, "A27")

    chart4 = deepcopy(chart1)
    chart4.type = "bar"
    chart4.style = 13
    chart4.grouping = "percentStacked"
    chart4.overlap = 100
    chart4.title = 'Percent Stacked Chart'

    ws.add_chart(chart4, "K27")


def create_bar_chart(wb):
    ws = wb.create_sheet("bar")
    for i in range(10):
        ws.append([i])

    values = Reference(ws, min_col=1, min_row=1, max_col=1, max_row=10)
    chart = BarChart()
    chart.add_data(values)
    ws.add_chart(chart, "E15")


def create_area_chart(wb):
    ws = wb.create_sheet("area")

    rows = [
        ['Number', 'Batch 1', 'Batch 2'],
        [2, 40, 30],
        [3, 40, 25],
        [4, 50, 30],
        [5, 30, 10],
        [6, 25, 5],
        [7, 50, 10],
    ]

    for row in rows:
        ws.append(row)

    chart = AreaChart()
    chart.title = "Area Chart"
    chart.style = 13
    chart.x_axis.title = 'Test'
    chart.y_axis.title = 'Percentage'

    cats = Reference(ws, min_col=1, min_row=2, max_row=7) # attention for the category range
    data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=7)
    chart.add_data(data, titles_from_data=True)
    chart.set_categories(cats)

    ws.add_chart(chart, "A10")


def create_line2_chart(wb):
    ws = wb.create_sheet("line2")
    rows = [
        ['Date', 'Batch 1', 'Batch 2', 'Batch 3'],
        [date(2015, 9, 1), 40, 30, 25],
        [date(2015, 9, 2), 40, 25, 30],
        [date(2015, 9, 3), 50, 30, 45],
        [date(2015, 9, 4), 30, 25, 40],
        [date(2015, 9, 5), 25, 35, 30],
        [date(2015, 9, 6), 20, 40, 35],
    ]

    for row in rows:
        ws.append(row)

    c1 = LineChart()
    c1.title = "Line Chart"
    c1.style = 13
    c1.y_axis.title = 'Size'
    c1.x_axis.title = 'Test Number'

    data = Reference(ws, min_col=2, min_row=1, max_col=4, max_row=7)
    c1.add_data(data, titles_from_data=True)

    # Style the lines
    s1 = c1.series[0]
    s1.marker.symbol = "triangle"
    s1.marker.graphicalProperties.solidFill = "FF0000"  # Marker filling
    s1.marker.graphicalProperties.line.solidFill = "FF0000"  # Marker outline

    s1.graphicalProperties.line.noFill = True

    s2 = c1.series[1]
    s2.graphicalProperties.line.solidFill = "00AAAA"
    s2.graphicalProperties.line.dashStyle = "sysDot"
    s2.graphicalProperties.line.width = 100050  # width in EMUs

    s2 = c1.series[2]
    s2.smooth = True  # Make the line smooth

    ws.add_chart(c1, "A10")

    from copy import deepcopy
    stacked = deepcopy(c1)
    stacked.grouping = "stacked"
    stacked.title = "Stacked Line Chart"
    ws.add_chart(stacked, "A27")

    percent_stacked = deepcopy(c1)
    percent_stacked.grouping = "percentStacked"
    percent_stacked.title = "Percent Stacked Line Chart"
    ws.add_chart(percent_stacked, "A44")

    # Chart with date axis
    c2 = LineChart()
    c2.title = "Date Axis"
    c2.style = 12
    c2.y_axis.title = "Size"
    c2.y_axis.crossAx = 500
    c2.x_axis = DateAxis(crossAx=100)
    c2.x_axis.number_format = 'd-mmm'
    c2.x_axis.majorTimeUnit = "days"
    c2.x_axis.title = "Date"

    c2.add_data(data, titles_from_data=True)
    dates = Reference(ws, min_col=1, min_row=2, max_row=7)
    c2.set_categories(dates)

    ws.add_chart(c2, "A61")


def create_line_chart(wb):
    ws = wb.create_sheet("line")

    # Let's create some sample sales data
    rows = [
        ["", "January", "February", "March", "April",
         "May", "June", "July", "August", "September",
         "October", "November", "December"],
        [1, ],
        [2, ],
        [3, ],
    ]

    for row in rows:
        ws.append(row)

    for row in ws.iter_rows(min_row=2, max_row=4, min_col=2, max_col=13):
        for cell in row:
            cell.value = random.randrange(5, 100)

    chart = LineChart()
    data = Reference(worksheet=ws,
                     min_row=2,
                     max_row=4,
                     min_col=1,  # because the chart now expects the first column to have the titles.
                     max_col=13)

    # from_rows=True --> This argument makes the chart plot row by row instead of column by column.

    chart.add_data(data, from_rows=True, titles_from_data=True)
    cats = Reference(worksheet=ws,
                     min_row=1,
                     max_row=1,
                     min_col=2,
                     max_col=13)
    chart.set_categories(cats)
    chart.x_axis.title = "Months"
    chart.y_axis.title = "Sales (per unit)"
    # You can play with this by choosing any number between 1 and 48
    chart.style = 24
    ws.add_chart(chart, "A10")

    chart2 = LineChart()
    data2 = Reference(worksheet=ws,
                      min_row=2,
                      max_row=4,
                      min_col=1,
                      max_col=13)
    chart2.add_data(data2)
    chart2.height = chart2.height + 8
    ws.add_chart(chart2, "A30")


def main():
    create_chart("SampleChart.xlsx")


if __name__ == '__main__':
    main()

 类似资料:

相关阅读

相关文章

相关问答