Chart

Add chart

def add_chart(sheet: str, cell: str, chart: Chart, **combo: Chart) -> None

Add a chart in a worksheet by given chart format set (such as offset, scale, aspect ratio setting, and print settings) and properties set.

The following shows the type of chart supported by excelize:

In the Office Excel chart data range, series specifies the set of information for which data to draw, the legend item (series), and the horizontal (category) axis label.

The series options that can be set are:

Set properties of the chart legend. The options that can be set are:

Set the position of the chart legend. The default legend position is right. This parameter only takes effect when none is False. The available positions are:

The show_legend_key parameter set the legend keys shall be shown in data labels. The default value is False.

The chart title is set by selecting the name parameter of the title object, and the title will be displayed above the chart. The parameter name supports the use of formula representations, such as Sheet1!$A$1, if you do not specify an icon title, the default value is null.

The parameter show_blanks_as provides the "Hide and empty cells" setting. The default value is: gap. In the Excel application "empty cell is displayed as": "space". The following are optional values for this parameter:

Set chart legend for all data series by legend property. The legend property is optional.

Set the bubble size in all data series for the bubble chart or 3D bubble chart by bubble_sizes property. The bubble_sizes property is optional. The default width is 100, and the value should be great than 0 and less or equal than 300.

Set the doughnut hole size in all data series for the doughnut chart by hole_size property. The hole_size property is optional. The default width is 75, and the value should be great than 0 and less or equal than 90.

Specifies that each data marker in the series has a different color by vary_colors. The default value is True.

The parameter format provides settings for parameters such as chart offset, scale, aspect ratio settings, and print properties, as well as those used in the add_picture function.

Set the position of the chart plot area by plot area. The properties that can be set are:

Set the primary horizontal and vertical axis options by x_axis and y_axis.

The properties of x_axis that can be set are:

The properties of YAxis that can be set are:

The value of text_rotation that can be set from -90 to 90.

The value of vertical that can be set are: horz, vert, vert270, wordArtVert, eaVert, mongolianVert and wordArtVertRtl.

Set the chart size by dimension property. The dimension property is optional. The properties that can be set are:

The parameter combo specifies the create a chart that combines two or more chart types in a single chart. For example, create a clustered column - line chart with data Sheet1!$E$1:$L$15:

import excelize

f = excelize.new_file()
try:
    for idx, row in enumerate(
        [
            [None, "Apple", "Orange", "Pear"],
            ["Small", 2, 3, 3],
            ["Normal", 5, 2, 4],
            ["Large", 6, 7, 8],
        ]
    ):
        cell = excelize.coordinates_to_cell_name(1, idx + 1)
        f.set_sheet_row("Sheet1", cell, row)

    f.add_chart(
        "Sheet1",
        "E1",
        excelize.Chart(
            type=excelize.ChartType.Col,
            series=[
                excelize.ChartSeries(
                    name="Sheet1!$A$2",
                    categories="Sheet1!$B$1:$D$1",
                    values="Sheet1!$B$2:$D$2",
                )
            ],
            format=excelize.GraphicOptions(
                scale_x=1,
                scale_y=1,
                offset_x=15,
                offset_y=10,
                print_object=True,
                lock_aspect_ratio=False,
                locked=False,
            ),
            title=[
                excelize.RichTextRun(
                    text="Clustered Column - Line Chart",
                )
            ],
            legend=excelize.ChartLegend(position="left"),
            plot_area=excelize.ChartPlotArea(
                show_cat_name=False,
                show_leader_lines=False,
                show_percent=True,
                show_ser_name=True,
                show_val=True,
            ),
        ),
        combo=excelize.Chart(
            type=excelize.ChartType.Line,
            series=[
                excelize.ChartSeries(
                    name="Sheet1!$A$4",
                    categories="Sheet1!$B$1:$D$1",
                    values="Sheet1!$B$4:$D$4",
                    marker=excelize.ChartMarker(
                        symbol="none",
                        size=10,
                    ),
                )
            ],
            format=excelize.GraphicOptions(
                scale_x=1,
                scale_y=1,
                offset_x=15,
                offset_y=10,
                print_object=True,
                lock_aspect_ratio=False,
                locked=False,
            ),
            legend=excelize.ChartLegend(position="right"),
            plot_area=excelize.ChartPlotArea(
                show_cat_name=False,
                show_leader_lines=False,
                show_percent=True,
                show_ser_name=True,
                show_val=True,
            ),
        ),
    )
    # Save the spreadsheet by the given path.
    f.save_as("Book1.xlsx")
except (RuntimeError, TypeError) as err:
    print(err)
finally:
    err = f.close()
    if err:
        print(err)

Add chart sheet

def add_chart_sheet(sheet: str, chart: Chart, **combo: Chart) -> None

Create a chartsheet by given chart format set (such as offset, scale, aspect ratio setting and print settings) and properties set. In Excel a chartsheet is a worksheet that only contains a chart.

Delete chart

def delete_chart(sheet: str, cell: str) -> None

Delete chart in spreadsheet by given worksheet name and cell reference.