【Python×Excel】How to create Bar Chart with openpyxl 【Design Recipe】

Japanese | English

This article describes the “openpyxl” library, which manipulates Excel in Python.

Since Excel is equipped with many functions, it is not possible to provide a comprehensive explanation of all of them in a single article. The articles are divided by major themes(functions) and summarized as a series of articles [Python x Excel].

In the previous article (part7 of the series), we introduced the “overview of the procedure for creating graphs” and “classes and objects of the elements that make up a graph” using openpyxl.

【Python×Excel】How to Create Graph in openpyxl【Basic】

This section provides illustrated instructions for inserting graphs into Excel with the "openpyxl library". The definition of the reference range of data and the elements (objects) that make up a graph, such as "title," "legend," "line," and "marker," are covered.

www.shibutan-bloomers.com

2022.04.03

From this time onward, we will explain more concretely the procedure for creating graphs with sample codes as a [Practical Edition]. You will find that openpyxl makes it surprisingly easy to draw practical graphs.

Please stay with us until the end of this article, as you will be able to “do and understand” the following.

Note that this is not an explanation of the graph itself, but rather the key points and special attributes (properties) when creating graphs in Python programs. Please refer to various specialized Excel books for explanations of graphs themselves.

1. “Bar Chart” Creation Procedure

This section describes the procedure (flow) for creating graphs in oenpyxl.

For line, bar, and other types of graphs that share the same item (X-axis value) for each series, all series data can be referenced together as the Reference object. The entire flow is systematized in Fig2.

This section describes the blocks (➀~➆) in Fig2.

➀.【Get Chart Object】

The Chart object is the framework of the graph.

A dedicated class is provided for each type of graph. In the case of this “Line Chart”. The LineChart object is obtained from the LineChart class.

➁.【Add Graph Element to Chart Object】

The elements that make up a graph include the title, legend, and axis titles etc… .

These are added and set by the attributes (properties) under the Chart object.

➂.【Define Reference Data as Reference Object】

Define the cell range referenced by the graph as the Reference object. There are two cell ranges to be referenced: “data area including series name” and “item name“. (Fig3)

For Reference class, see <Related Article>.

➃.【Set data in the Chart Object】

Add the Reference object that points to the data area defined in ➂ to the Chart object with the add_data() method.

➄.【Set Item name (Category) to Chart Object】

Add the Reference object defined in ➂ to the Chart object using the set_categories() method.

➅.【Apply Efects to each Series data】

Individual series data in a graph are managed as Series objects. Decorative effects in the plot area, such as markers, lines, and fills, are set in the attributes under the Series object.

A Series object can be obtained with the series property of a Chart object.

➆.【Insert Graph into Worksheet】

Finally, in the argument of the add_chart() method of the Worksheet object, specify the Chart object and the insertion position (cell address) of the graph you have defined so far, and insert it into the worksheet.

These are the steps for creating a graph.

From the next section, we will provide detailed explanations with specific sample code. This time, we will introduce an example of a “Bar Chart“.

2. Overview of “Bar Chart” with openpyxl

A “Bar Chart” is a form of graph in which the absolute amounts of data paired on the X-axis (item name) and Y-axis (value) are expressed as bars, allowing comparison between categoryor series to be confirmed at a glance. Data reference information is managed by grouping all series data together as the Reference object.

A similar graph is a “Scatter plot,” but the difference is that the values (categories) on the X-axis are shared among the different series.

There are two types of classes that provide the framwork of a bar chart (Chart object): the BarChart class for planar (2D) charts and the BarChart3D class for three-dimensional (3D) charts, depending on the dimension of the chart.

The grouping property of the BarChart(2D/3D) object allows the user to select one of three types of graph form: “standard,” “stacked,” or “percentStacked” (100% stacked).

In addition, the type property can be used to specify the orientation of the bar as “col” (vertical) or “bar” (horizontal).

The order of the bars and the orientation of the form and bars, and the characteristics of each are shown in Fig4.

In addition to bars, plot area components can be filled with arbitrary patterns, spacing, and labels. These are managed as Series objects for each series.

The above is an overview of bar charts (BarChart objects(2D/3D)). From the next section, we will explain the step-by-step procedure for creating a graph while showing concrete sample code.

3. Implement “Bar Chart (2D)”

We will now describe a step-by-step example implementation of a bar chart with the following specifications.

3.1 Define Outline of “Bar Chart”【Step.1】

First, import the necessary classes. Define the BarChart object that will be the framework of the graph, and the elements that make up the graph area, such as the title and legend, in the following .

The file (.xlsx) used in this sample can be downloaded from.

# Import of modules and classes-------------------------------------------

from openpyxl import load_workbook

# Classes required for bar chart creation (definition of graph framework and data reference information)
from openpyxl.chart import BarChart, Reference, Series

# Classes required for pattern (pattern) fill
from openpyxl.drawing.fill import PatternFillProperties, ColorChoice

# Classes required to define individual data (category) information
from openpyxl.chart.marker import DataPoint

# Classes required to define data label information
from openpyxl.chart.label import DataLabel, DataLabelList

# File (sheet) read --------------------------------------------------------

wb = load_workbook('Graph_DataSource.xlsx')  # Reading Excel files
ws = wb.worksheets[0]                        # Obtaining a Worksheet object


# [A] Preparation of graph framework and components ------------------------

# Obtain a Chart object (the framework of a bar chart)
c1 = BarChart()


# Adjust graph size
c1.width = 18     # default(15cm)
c1.height = 10    # default(7cm)


# Set titles for graphs (main, axis)
c1.title = "Bar Chart"               # main title
c1.x_axis.title = 'Month'            # X-axis title
c1.y_axis.title = 'Precipitation'    # Y-axis title


# Graph legend
c1.legend.position = 'b'     # Location of legend

#---------------------------------------------
# Continue to <List2>

Now, let me explain the key points.

Up to the 15th line, the BarChart class, which provides the main function of the bar chart and the classes required for bar decoration (*) are imported.

In line 26, the BarChart object is obtained (stored in variable c1). In the subsequent processing, the methods and attributes (properties) under the BarChart object are used to construct the graph.

After executing the code up to this point, nothing is displayed except the frame of the graph area, as shown in Figure 5. The axes and legend will be reflected after the plot area is defined in in the next section.

3.2 Define Reference Data for “Bar Chart”【Step.2】

Please add <List2> following <List1>. <List2>adds data reference information to the “BarChart object”.

# [B] Obtain data reference information --------------------------------

# Obtain a reference object for the cell range that will be the data (including series names)
# See columns 3(C)-5(E), rows 4-8
data = Reference(ws, min_col=3, max_col=5, min_row=4, max_row=8)

# Obtains the reference object for the column that is the category name
# See column 2(B), rows 5-8
category = Reference(ws, min_col=2, max_col=2, min_row=5, max_row=8)

# Set data and category in a Chart object
c1.add_data(data, titles_from_data=True)  # Specify True for the second argument to make the first element the series name.
c1.set_categories(category)               # Set category

#---------------------------------------------
# Continue to <List3>

First, line 5 defines the reference information of the data as a Reference object. (within the red frame in Figure6) Next, line 9 defines a Reference object for the category of reference information in the same way.

Then, the add_data() method in line 12 and the set_categories() method in line 13 set the data information and categories to the BarChart object, respectively. Also, by setting the arg:title_from_data to “True” in the add_data() method, the element (*) in the first line of the data area is recognized as the series name.

3.3 Form and Orientation of “Bar Chart”【Step.3】

Add <List3> following <List3>. <List3> gives the bar chart a rough form and appearance.

# [C] Bar Chart (BarChart) Form Types and Visual Effects Settings ------------------

# Setting the theme color for bar graphs
c1.style = 7         # Set theme color by integer

# Set bar orientation (Vertical, Horizontal)
# "col": Vertical, "bar":Horizontal(default"col")
c1.type = "col"

# Set the form of the graph
# "standard", "stacked","percentStacked"
c1.grouping = "standard"

# Adjust the spacing of the category set
c1.gapWidth = 200

#---------------------------------------------
# Continue to <List4>

3.4 Set Series Format(Fill)【Step.4-1】

Add <List4> following <List3>. In <List4>, apply the fill effect to each series.

Specifically, “Single color Fill” and “Pattern Fill” are set for the two series data (Sapporo and Sendai).

For more information on the fill effect, please also refer to this <Fill Object>.

# [D] Set bar decorations for each series

# <Series1> bar decoration ------------------------------------------------------

# Obtaining a Series object
ser1=c1.series[0]

# Fill with a single color

# ➀ Color specification by RGB Hex notation
# ser1.graphicalProperties.solidFill = "FF0000"

# ➁ Specified by ColorChoice object
ser1.graphicalProperties.solidFill = ColorChoice(prstClr="cornflowerBlue")

# <Series2> bar decoration ------------------------------------------------------

# Obtaining a Series object
ser2=c1.series[1]

# Definition of Pattern
# (Define PatternFillProperties object)

# 【Part➀: Designation by Attribute】
# ➀-1 Fill-in vertical stripe pattern
fill =  PatternFillProperties(prst="dkVert")
# ➀-2 Foreground color is specified by foreground attribute (Red)
fill.foreground = ColorChoice(prstClr="red")
# ➀-3 Background color is specified by background attribute (Blue)
fill.background = ColorChoice(prstClr="blue")

# 【Part➁: Specification by Argument】
# fill = PatternFillProperties(prst="dkVert", fgClr=ColorChoice(prstClr="red"), bgClr=ColorChoice(prstClr="blue"))


# Set the pattern definition (PatternFillProperties object) in the pattFill attribute
ser2.graphicalProperties.pattFill = fill

#---------------------------------------------
# Continue to <List5>

Line 6 obtains the Series object for <Series1>, and up to line 14, processing is performed on this <Series1>.

The format of the ColorChoice class is as follows The following types can be specified: “built-in (prstColor),” “RGB format (srgbColor),” “system (sysColor),” “theme (schemeColor),” and so on.

In the following line 19, the Series object of <Series2> is obtained. From then until line 30, processing is performed on the bars of this <Series2>.

The pattern fill is finally applied on line 37 by applying the graphicalproperties attribute followed by the pattFill attribute. Before that, we need to define the PatternFillproperties object, which is the definition information of the pattern to be shaded.

3.5 Set Series Format(DataLabel)【Step.4-2】

Please add <List5> following <List4>. While <List4> was for the entire series, <List5> is an example of applying the format to a specific category in the same series.

# [E] Decorate only the specified category of series3

# <Series3> bar decoration -----------------------------------------------------------

# Obtaining a Series object
ser3=c1.series[2]

# Specific category information is managed in DataPoint objects
# The arg idx specifies the index of the category
pt = DataPoint(idx=3)

# Format the DataPoint object (in this case, pattern shading)
pt.graphicalProperties.pattFill = PatternFillProperties(prst="ltHorz")

# Apply a DataPoint object to a Series object
# There are three ways to apply it, any of which are acceptable

#ser3.dPt.append(pt)           # Add with the append method (1)
#ser3.data_points.append(pt)   # Add with the append method (2)
ser3.data_points = [pt]        # Set by List


# [F] Apply "data labels" to the bars of Series3-----------------------------------------

# ➀ To display labels for all categories
# ➀-1 Acquisition of DataLabelList object
#lbl = DataLabelList(showVal=True)

# ➁ To display a label with a specific category
# ➁-1 Obtain a DataLabel object (arg idx specifies the index of the item)
lb = DataLabel(idx=1, showVal=True)
# ➁-2 Add to DataLabelList object
lbl = DataLabelList(dLbl=[lb])

# Set labels (DataLabelList objects) on Series3

#ser3.dLbls=lbl   # ➂-1
ser3.labels=lbl   # ➂-2


# Add Chart object to sheet and save -----------------------------------------

ws.add_chart(c1, "B13") # Paste the graph in cell(B13) in the upper left corner
wb.save('BarChart_example1_standard.xlsx')

At line 6, the Series object for <series3> is obtained. The following 38 lines are used to process the bar of <series3>.

Also, as a supplement, if you want to set labels for all category in the series, enable the arg:showval for the “DataLabelList collection” as shown in line 27. (➀-1)

This is the end of the explanation of the sample code. The result of concatenating all <List1> ~ <List5> and executing it is as shown in Figure9. You will see a title or bar fill, then a pattern or data label for that particular category.

The results of <List1> can also be downloaded below.

4. Bar Chart Type <Appendix>

As shown in <Fig.4> in Section2, BarChart is drawn by combining “bar orientation” and “chart type. This section introduces the attributes for setting these and examples of their application.

4.1 Orientation of the “Bar”

The direction of the bar can be specified as “col” (vertical bar) or “bar” (horizontal bar) using the type attribute. You can easily switch between the two, but keep in mind that the order of categories is reversed in both settings.

By replacing <List6> with line 8 of <List3>, the orientation of the bar was changed from vertical to horizontal. (Figure10)

# Set bar orientation (Vertical, Horizontal)
# col":vertical bars , "bar":horizontal bars (default is "col")
c1.type = "bar"

4.2 Form of the “Bar”

The graph form can be selected from among “standard“, “stacked“, and “percentStacked” (100% stacked) by using the grouping attribute.

This can be changed by simply replacing line 12 of <List3> with line 3 of <List7>. (Figure11)

# Set the form of the graph
# Choose from  "standard", "stacked","percentStacked"
c1.grouping = "stacked" # Stacked bar graph


# Adjusts the degree of sharing of adjacent edges (only stacking type can be set)
# 100 is the default, with the upper and lower bars shifting as they get smaller (can be set between -100 and 100).
c1.overlap = 0

For “stacked” type bars, the overlap attribute can be set to adjust the degree of sharing of adjacent edges. “100” is for perfect adjacency, and the smaller the value, the further apart they are. The range is from -100 to 100. Line 8 of <List7>.

The above sections 1 to 3 are a series of explanations about “BarChart.” There is another type of bar chart, the 3D type, which I will mention briefly at the end.

5. About 3D Bar Chart(BarChart3D)

As mentioned at the beginning of <Section.2>, the BarChart3D class is available to create a three-dimensional (3D) representation of a “Bar chart”.

For 3D bar graphs, a Chart object is obtained from the “BarChart3D class” as shown in , and sets the reference information and attributes in the same way as for planar graphs.

# Class that defines the framework of a 3D bar chart
from openpyxl.chart import BarChart3D, Reference, Series

# Obtain the Chart object
c1 = BarChart3D()

The basic attributes are the same as those for planar (2nd order), but since the number of orders increases for three-dimensional (3rd order), the attributes that can be set also increase accordingly. However, if you are not particular about this, you can leave the default settings for any of the attributes (there is no need to set them in particular), but there are some attributes that are unique to 3D graphs. <Table1>

The following <List9> is an excerpt from <Table1> that sets the shape property to specify the bar shape and the gapDepth property to adjust the depth of the floor.

# Specify the shape of the bar with the shape attribute
# {‘pyramid','pyramidToMax','coneToMax','cylinder',box',cone’}
c1.shape = "cylinder"

# Specify floor depth (0 to 500)
c1.gapDepth = 250

The graph reflecting <List8><List9> is shown below.

The “shape attribute” specifies “cylinder“, so the bar is represented as a cylinder. You can also confirm that the depth can be adjusted by using the “gapDepth” attribute.

These are the differences between how to create a three-dimensional (3D) bar graph and a planar graph. Again, the basic code is the same, so we will skip the explanation of the common code.

6. Summary

How was it?

Introduced the procedure for creating bar graphs using the “openpyxl” library with actual examples.

While some points may have been difficult to understand from the previous explanation of classes and objects, we hope that you felt that your understanding was deepened by reading the actual code.

In addition, there are some patterns in the code for creating graphs, so please use the sample code as a reference and try to arrange it.

There are many other types of graphs in Excel besides “Bar charts”.

The following is an explanation of the most commonly used types of “Line graphs“, “Scatter plots“, “Bubble charts“, etc. Please refer to these as well.

【Python×Excel】How to create Line Chart with openpyxl 【Design Recipe】

The procedure for inserting a "line chart" in the "openpyxl library," which manipulates Excel, will be explained with illustrations and sample code. In addition, examples of applying "lines," "markers," and styles to plot areas will be introduced.

www.shibutan-bloomers.com

2022.04.15

【Python×Excel】How to create "Scatter" "Bubble" with openpyxl【Design Recipe】

This article explains the procedure for creating "Scatter" and "Bubble Chart" with "openpyxl library" with illustrations and sample code. How to apply plot area formatting (markers/data labels/fill) is also discussed.

www.shibutan-bloomers.com

2022.04.30

Let’s summarize what we have so far.

Thank you for reading to the end.

Japanese | English