Plotting Charts
Charts are composed of at least one series of one or more data points. Series themselves are comprised of references to cell ranges. For plotting the charts on an excel sheet, firstly, create chart objects of specific chart class( i.e BarChart, LineChart, etc.). After creating chart objects, insert data in it, and lastly, add that chart object in the sheet object.
Example 1: Creating and Customizing Bar Chart in Excel with openpyxl
In this example, the openpyxl module is used to create a new Excel workbook. Numeric values from 0 to 9 are written to the first column of the active sheet. A BarChart object is then created, and data for plotting is specified using the Reference class. The chart is customized with a title, x-axis title, and y-axis title. Finally, the chart is added to the sheet, anchored to cell E2.
Python3
# import openpyxl module import openpyxl # import BarChart class from openpyxl.chart sub_module from openpyxl.chart import BarChart, Reference wb = openpyxl.Workbook() sheet = wb.active # write o to 9 in 1st column of the active sheet for i in range(10): sheet.append([i]) # create data for plotting values = Reference(sheet, min_col=1, min_row=1, max_col=1, max_row=10) # Create object of BarChart class chart = BarChart() # adding data to the Bar chart object chart.add_data(values) # set the title of the chart chart.title = " BAR-CHART " # set the title of the x-axis chart.x_axis.title = " X_AXIS " # set the title of the y-axis chart.y_axis.title = " Y_AXIS " sheet.add_chart(chart, "E2") # save the file wb.save("sample.xlsx") |
Output:
Example 2: Creating and Customizing Line Chart in Excel with openpyxl
In this example, the openpyxl module is used to create a new Excel workbook. Numeric values from 0 to 9 are written to the first column of the active sheet. A LineChart object is then created, and data for plotting is specified using the Reference class. The chart is customized with a title, x-axis title, and y-axis title. Finally, the chart is added to the sheet, anchored to cell E2.
Python3
# import openpyxl module import openpyxl # import LineChart class from openpyxl.chart sub_module from openpyxl.chart import LineChart, Reference wb = openpyxl.Workbook() sheet = wb.active # write o to 9 in 1st column of the active sheet for i in range(10): sheet.append([i]) values = Reference(sheet, min_col=1, min_row=1, max_col=1, max_row=10) # Create object of LineChart class chart = LineChart() chart.add_data(values) # set the title of the chart chart.title = " LINE-CHART " # set the title of the x-axis chart.x_axis.title = " X-AXIS " # set the title of the y-axis chart.y_axis.title = " Y-AXIS "sheet.add_chart(chart, "E2") # save the file wb.save("sample.xlsx") |
Output:
Refer to the below articles to get detailed information about plotting in excel using Python.
Working with Excel Spreadsheets in Python
You all must have worked with Excel at some time in your life and must have felt the need to automate some repetitive or tedious task. Don’t worry in this tutorial we are going to learn about how to work with Excel using Python, or automating Excel using Python. We will be covering this with the help of the Openpyxl module and will also see how to get Python in Excel.