【Python×Excel】How to make the “Bar Chart” in openpyxl and design recipe【Practice】

Python external library(openpyxl)_Creating a graph_Bar_En openpyxl

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].

Fig1. List of contents in the series [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.

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.

What you will learn in this article
  • Understand how to create line and bar charts, which share the same items (categories) for each series.
  • Examples of “Bar Charts” will be illustrated with sample code.

Graph Form:Representation (2D/3D), Orientation of bars (vertical/horizontal), stacking type, etc.

Bar Formatting:Types of Fill Effects, Labels, Spacing, and Styles

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.

The information presented on this site is only an example. Please refer to the official website for details and clarifications, if necessary.

Official Site】:https://openpyxl.readthedocs.io/en/stable/


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 Figure2.

Graph creation flow using openpyxl_Batch series data acquisition_rev0.4_En
Fig2. Procedure for creating a graph (batch reference to series data)

This section describes the blocks (~) in Figure2.

.Obtain the 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 a Graph Element to the 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 data reference 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“. (Figure3)

For Reference class, see <Related Article>.

Reference area of Reference object (data area_item name area)_rev0.3_En
Fig3 Reference range managed by Reference object

.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 decorating effects 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 a graph into a 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. 「Vertical/Horizontal Bar Chart」 by 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.

BarChart Object

Bar graph

from openpyxl.chart import BarChart

Chart Object = BarChart(gapWidth, overlap)

arg:gapWidth: Adjust the spacing between series(default 150)

arg:overlap: Adjustment of stacking overlap (default None)

Many other optional arguments

Bar(3D) graph

from openpyxl.chart import BarChart3D

Chart Object=  BarChart3D(gapWidth, gapDepth, shape)

arg:gapWidth: Adjust the spacing between series(default 150)

arg:gapDepth: Adjust the depth of the floor (default 150)

arg:shape: Specify bar shape (default None)

Many other optional arguments

The grouping attribute 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 attribute 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 Figure 4.

Type of Bar chart
Fig4. Types of bar charts

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 a “Bar chart(2D)”

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

Specifications of the “Bar graph”
  • A “Bar (vertical) graph” referring to three series and four category of data.
  • Display main title and axis (sub)titles
  • Place the legend at the bottom of the graph
  • Set a pattern for a specific series/category
  • Place data labels on the bar

3.1 Define “Bar chart” outline【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 .

# 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.

Lines 30,31:【Size specification】

The default size of the graph is (17cm x 7cm), but if you want to specify the size individually, use the width/height attributes.

Lines 35~37:【Setting the Title】

The main title is set in the title attribute, and the X/Y axis subtitle is set in the title attribute for each axis object. The axis object is obtained via the x_axis/y_axis attribute, but in this code example, the attribute chain (attribute. attribute) at a time.

Line 41:【Setting the legend

The legend object is obtained via the legend attribute. By default, the legend is automatically displayed, but it can be hidden by setting the legend attribute to “None”.

The position of the legend can also be specified with the position attribute. In this example, “b” is specified to place the legend at the bottom of the plot area. Other options are ‘r’, ‘l’, ‘t’, and ‘tr’.

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.

Fig5 Result of execution

3.2 Define reference information 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.

Reference information for bar chart cell range_List2_rev0.1_En
Fig6. Reference cell range of data in (Reference object)

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.

The series name can be specified with the arg:titles_form_data only for the element on the first line. Since this is not the first column, the original table must be arranged as shown in Figure6.

3.3 the form and orientation of the “Bar graph”【Step.2】

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>
Line 4:【theme color】

The theme color for the entire graph can be easily set by specifying a default index in the style attribute. The settable indexes are available from 1 to 48, and you can choose from shades and gradations as shown in Figure7.

The same styles are available for LineChart. For details, please refer to <here>.

Figure7 List of theme colors by style attribute (some are omitted)
Line 8:【Orientation of the bar】

The orientation of the bar is set by the type attribute. ”col” (vertical) or “bar” (horizontal).

If omitted, the default setting of “col” (vertical stacking) is applied. This will be explained in detail later.

Line 12:【Form of graph】

The form of the bar graph is set by the grouping attribute.

You can choose from “standard“, “stacked“, and “percentStacked” (100% stacked). In this case, the standard “standard” is used. Details will be explained later.

Line 15:【Interval of category set】

The spacing between category sets can be adjusted using the gapWidth attribute.

The attribute can be specified as an integer from 0 to 500, the larger the integer, the wider the interval. In this example, 200 is specified.

Explanation of gapWidth attribute_rev0.1_En
Fig8 Spacing adjustment of category set by gapWidth attribute

3.4 Formatting to series【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

# 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

# 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>.

Lines 11,14 :【single-color fills】

The solidFill attribute is used to fill the inside of the bar with a single color, following the graphicalproperties attribute.

There are two ways to specify colors: by using “RGB Hex notation (string)” as in line 11 (➀), or by using a “ColorChoice object” as in line 14 (②).

In this example, the built-in color definition “cornflowerBlue” is specified from among the many color variations provided by the ColorChoice class.

(line14) *The RGB specification in line 11 is commented out.

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.

ColorChoice Class

from openpyxl.drawing.fill import ColorChoice

ColorChoice(prstClr, srgbClor, sysClor, schemeClor)

arg:prstClor: Specify from built-in color options

(‘cornflowerBlue’, ‘darkCyan’, ‘darkSlateGrey’, ‘darkSlateBlue’, and many others)

arg:srgbClor: Specified by RGB string(‘FFFFFFFF’, etc…)

arg:sysClor: Specify by the SystemColor Object

arg:schemeClor:Specify by the SchemeColor Object

return: ColorChoice Obect

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.

Lines 26,28,30,33:【Definition of Pattern Fill】

Defines PatternFillproperties objects. The type of pattern to be shaded and the foreground and background colors are specified using class arguments and object attributes. (➀-1,2,3)

Line 33 is the case where the object is defined with argument specifications only.(➁) Both can do the same thing. (The format of the PatternFillproperties class is as follows)

PatternFillproperties Class

from openpyxl.drawing.fill import PatternFillProperties

PatternFillProperties(prst, fgClr, bgClor)

arg:prst: Specify design patterns

( Many optional specifications, such as ‘dashUpDiag’, ‘narVert’, ‘horz’, ‘smGrid’, etc…)

arg:fgClr: Specify foreground color

(Can be specified by ColorChoice class in addition to Hex notation(”FF0000”))

arg:bgClr: Specify background color

(Can be specified by ColorChoice class in addition to Hex notation(”FF0000”))

return: PatternFillProperties Object

3.5 Formatting the series【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

# 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

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

Lines 10,13,20:【Processing of specific category】

In line 10, the DataPoint object that manages information on a specific category in the series is obtained by specifying the number of the category in the arg:idx of the DataPoint class. (In this example, the fourth category data is the target.)

Line13 defines the format (pattern fill) to be applied to the “DataPoint object”.

Then, in line 20, set the “DataPoint object” defined by the data_points attribute and appdne() method under the “Series object” of <Series3>.

DataPoint Class

from openpyxl.chart.marker import DataPoint

DataPoint(idx, invertifNegative, marker, explosion)

arg: idx: Specify the target category number (Specify an index starting from 0)

arg: invertifNegative: Invert negative numbers (True: Reverses / False: Not inverted)

arg: marker: Formatting a marker (Markerオブジェクト )

arg: explosion: Setting the degree of cropping for pie charts (Specify by integer)

returnDataPoint Object

※Many other optional arguments

<Property to apply fill to a specific category>

DataPoint Object.graphicalProperties

Please refer to <here> for the attributes under GraphicalProperties.

Lines 31,33,38 :【Data label setting】

Each data label is managed by the DataLabel object. Specifies the category number (index starting with 0) to be labeled in the arg:idx and sets the arg:showVal to “True“.

In this case, the “data label” is defined for the second category in <series3>. (-1)

Next, line 33 summarizes the DataLabel object into the DataLabelList collection. (-2) Then, set the data label in the labels attribute of the “Series object” in <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)

DataLabel Class

from openpyxl.chart.label import DataLabel

DataLabel(idx, showVal, showSerName, showPercent)

arg:idx: Specify the category index

arg:showVal: Display numbers on labels    True(display)/False(hidden・default)

arg:showSerName: Display the series name on the label True(display)/False(hidden・default)

arg:showPercent: Display the percentage (%) on the label True(display)/False(hidden・default)

return: DataLabel Object

from openpyxl.chart.label import DataLabelList

DataLabelList(dLbl, showVal)

arg: dLbl: Pass DataLabel objects as list elements (number required)

arg: showVal:Specify True to set the label

(However, if True is specified, all categories will be displayed at once. To specify individual categories, use False or do not set.)

return: DataLabelList Object

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.

Bar chart_List1-List5 execution results
Fig9. Execution results of sample code (<List1> ~ <List5>)

4. Types of “Bar graphs” <Appendix>

As shown in <fig4.> in Section2, a 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"
Bar_type attribute_col_bar_rev0.1_En
Fig10. Setting bar orientation by type attribute

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
Fig11. Selection of graph form by grouping attribute

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>.

Bar_overlap attribute_En
Fig12. Adjustment of adjacent edges by overlap attribute

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>

【Attributes of Chart object】【functions】【Other details】
backWall propertyBack Settings(Under investigation)
floor propertyFloor setting(Under investigation
sideWall propertySide setting(Under investigation)
view3D propertyAdjusting the viewpoint(Under investigation)
gapDepth propertyAdjust floor depthSet in the range of 0~500
shape propertySpecify bar shape{pyramid’, ‘pyramidToMax’, ‘coneToMax’, ‘cylinder’, ‘box’, ‘cone}

The following <List9> is an excerpt from <Table1> that sets the shape attribute to specify the bar shape and the gapDepth attribute 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.

3D bar chart sample code execution example
Fig13. Example of executing “3D-BarChart”

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.

Let’s summarize what we have so far.

. There are two points to keep in mind when generating a “Chart object” for a graph that shares the same categories for each series, such as a bar chart.

  • The reference information of the “Reference Object” is obtained by batch-acquisition of multiple series at once.
  • It is not possible to add objects to each series.

. To apply decorations (fill effect, data labels, etc.) to a bar, acquire a “Series object” and then set it by the underlying attributes.

  • The fill effect is set in graphicalproperties.solidFill attribute
  • To set the data label, define a DataLabel object and set it to the labels attribute.

Thank you for reading to the end.