{"id":2981,"date":"2022-06-01T20:41:30","date_gmt":"2022-06-01T15:11:30","guid":{"rendered":"https:\/\/sheetstips.com\/?p=2981"},"modified":"2022-07-15T17:52:21","modified_gmt":"2022-07-15T12:22:21","slug":"python-plotting-charts-in-excel-sheet-using-openpyxl-module-set-2","status":"publish","type":"post","link":"https:\/\/sheetstips.com\/python-plotting-charts-in-excel-sheet-using-openpyxl-module-set-2\/","title":{"rendered":"Python Program Plotting Charts in Excel Sheet using Openpyxl module | Set 2"},"content":{"rendered":"<p>Users can automate a range of Excel functions, including reading, writing, performing computations, making graphs, and more, using one of the most important Python programs, Openpyxl. In this article, we will discuss how to create charts in an Excel sheet using Openpyxl without actually opening the Excel sheet. In order to construct a chart on an Excel sheet, we must first create a chart object in Python that belongs to a particular class. Before inserting the chart object into the sheet object, we must fill the chart object with data. The tutorial explains how to implement this in Python step-by-step. Keep reading to find out more.<\/p>\n<p>In this article, we are going to plot the charts like<\/p>\n<ul>\n<li><a href=\"#Plotting_the_Bubble_Chart\">Plotting the Bubble Chart<\/a><\/li>\n<li><a href=\"#Plotting_the_Scatter_Chart\">Plotting the Scatter Chart<\/a><\/li>\n<\/ul>\n<p>using openpyxl module in Python.<\/p>\n<p><strong>Openpyxl Module:<\/strong><\/p>\n<p>OpenPyXL is a Python package that allows you to communicate with Excel (.xlxs) files. But you can&#8217;t do it with Pandas. Yes, however Pandas does not support charts, formulas, or images. OpenPyXL allows data scientists and analysts to conduct a wide range of operations on Excel files, including:<\/p>\n<ul>\n<li>Reading and writing cells.<\/li>\n<li>Creating and renaming sheets.<\/li>\n<li>Drawing\/Plotting charts.<\/li>\n<li>Add formulas.<\/li>\n<li>Creating pivot tables.<\/li>\n<li>Inserting images, and even doing formatting and styling.<\/li>\n<\/ul>\n<p>Another noteworthy feature of OpenPyXL is that it does not require the installation of Microsoft Excel.<\/p>\n<h2>Program Plotting Charts in Excel Sheet using Openpyxl module in Python | Set 2<\/h2>\n<h3 id=\"Plotting_the_Bubble_Chart\">Chart #1: Plotting the Bubble Chart<\/h3>\n<p><strong>Approach:<\/strong><\/p>\n<ul>\n<li>Import the openpyxl module using the import keyword<\/li>\n<li>Import the BubbleChart, Reference, and Series class from openpyxl.chart submodule using the import, from keywords<\/li>\n<li>Create a workbook using the Workbook() function of the openpyxl module and store it in a variable<\/li>\n<li>Set the above workbook to active by applying the active attribute to the workbook and store this sheet in a variable<\/li>\n<li>Give the Data Values of the rows as a list and store them in a variable<\/li>\n<li>Fill out the first, second, and third columns of the active sheet with the content of each row.<\/li>\n<li>Create the BubbleChart() object using the BubbleChart() function and store this in a variable<\/li>\n<li>Creating Data to Plot<\/li>\n<li>Creating the 1st series of data using the series() function<\/li>\n<li>Append the series of data to the chart object using the append() function<\/li>\n<li>Set the title of the Chart Object using the title attribute.<\/li>\n<li>Set the x axis title of the above chart using the x_axis, title attributes.<\/li>\n<li>Set the y axis title of the above chart using the x_axis, title attributes. Add the above chartObject to the sheet using the add_chart() function by passing the chartObject and anchored cell values as arguments to it.<\/li>\n<li>Save the workbook by passing the fileName of the plot as an argument to the save() function.<\/li>\n<li>The Exit of the Program.<\/li>\n<\/ul>\n<p><strong>Below is the Implementation:<\/strong><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"python\"># import the openpyxl module using the import keyword\r\nimport openpyxl\r\n\r\n# import the BubbleChart,Reference,Series class from openpyxl.chart sub module using the import,from keywords\r\nfrom openpyxl.chart import BubbleChart, Reference, Series\r\n\r\n# Create a workbook using the Workbook() function of the openpyxl module and store it in a variable\r\nworkbook = openpyxl.Workbook()\r\n\r\n# Set the above workbook to active by applying the active attribute to the workbook and store this sheet in a variable\r\nworksheet = workbook.active\r\n\r\n#G ive the Data Values of the rows as a list and store it in a variable  \r\nrowsData = [\r\n    (\"Number of Mobiles\", \"Sales in Rs\", \"share Values\"),\r\n    (17, 12200, 18),\r\n    (30, 60000,33),\r\n    (28, 24400, 20),\r\n    (32, 32000, 52)\r\n]\r\n  \r\n# Fill out the first, second, and third columns of the active sheet with the content of each row.\r\nfor rowVal in rowsData:\r\n    worksheet.append(rowVal)\r\n  \r\n# Create the BubbleChart() object using the BubbleChart() function and store this in a variable\r\nchartObj = BubbleChart()\r\n  \r\n# Creating Data to Plot\r\nx_values = Reference(worksheet, min_col = 1,\r\n                    min_row = 2, max_row = 5)\r\n                      \r\ny_values = Reference(worksheet, min_col = 2,\r\n                    min_row = 2, max_row = 5)\r\n                      \r\nsize = Reference(worksheet, min_col = 3,\r\n                min_row = 2, max_row = 5)\r\n  \r\n# creating the 1st series of data using the series() function\r\nseries = Series(values = y_values, xvalues = x_values,\r\n                      zvalues = size, title =\"2022\")\r\n  \r\n# Append the series of data to the chart object using the append() function\r\nchartObj.series.append(series)\r\n  \r\n# Set the title of the Chart Object using the title attribute\r\nchartObj.title = \" PlOTTING BUBBLE CHART \"\r\n  \r\n# Set the x axis title of the above chart using the x_axis,title attributes\r\nchartObj.x_axis.title = \" X-AXIS \"\r\n  \r\n# Set the y axis title of the above chart using the x_axis,title attributes\r\nchartObj.y_axis.title = \" Y-AXIS \"\r\n  \r\n# Add the above chartObject to the sheet using the add_chart() function \r\n# by passing the chartObject and anchored cell values as arguments to it.\r\nworksheet.add_chart(chartObj, \"E2\")\r\n  \r\n# Save the workbook by passing the fileName of the plot as an argument to the save() function.\r\nworkbook.save(\"chartBubble.xlsx\")<\/pre>\n<p><strong>Output:<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-3084\" src=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2022\/05\/bubble-chart.jpg\" alt=\"bubble chart\" width=\"1214\" height=\"532\" srcset=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2022\/05\/bubble-chart.jpg 1214w, https:\/\/sheetstips.com\/wp-content\/uploads\/2022\/05\/bubble-chart-300x131.jpg 300w, https:\/\/sheetstips.com\/wp-content\/uploads\/2022\/05\/bubble-chart-1024x449.jpg 1024w, https:\/\/sheetstips.com\/wp-content\/uploads\/2022\/05\/bubble-chart-768x337.jpg 768w\" sizes=\"(max-width: 1214px) 100vw, 1214px\" \/><\/p>\n<h3 id=\"Plotting_the_Scatter_Chart\">Chart #2: Plotting the Scatter Chart<\/h3>\n<p><strong>Approach:<\/strong><\/p>\n<ul>\n<li>Import the openpyxl module using the import keyword<\/li>\n<li>Import the ScatterChart, Reference, and Series class from openpyxl.chart submodule using the import, from keywords<\/li>\n<li>Create a workbook using the Workbook() function of the openpyxl module and store it in a variable<\/li>\n<li>Set the above workbook to active by applying the active attribute to the workbook and store this sheet in a variable<\/li>\n<li>Give the Data Values of the rows as a list and store them in a variable<\/li>\n<li>Fill out the first, second, and third columns of the active sheet with the content of each row.<\/li>\n<li>Create the ScatterChart() object using the ScatterChart() function and store this in a variable<\/li>\n<li>Creating Data to Plot<\/li>\n<li>Creating the 1st series of data using the series() function<\/li>\n<li>Append the series of data to the chart object using the append() function<\/li>\n<li>Set the title of the Chart Object using the title attribute.<\/li>\n<li>Set the x axis title of the above chart using the x_axis, title attributes.<\/li>\n<li>Set the y axis title of the above chart using the x_axis, title attributes. Add the above chartObject to the sheet using the add_chart() function by passing the chartObject and anchored cell values as arguments to it.<\/li>\n<li>Save the workbook by passing the fileName of the plot as an argument to the save() function.<\/li>\n<li>The Exit of the Program.<\/li>\n<\/ul>\n<p><strong>Below is the Implementation:<\/strong><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"python\"># import the openpyxl module using the import keyword\r\nimport openpyxl\r\n\r\n# import the BubbleChart,Reference,Series class from openpyxl.chart sub module using the import,from keywords\r\nfrom openpyxl.chart import ScatterChart, Reference, Series\r\n\r\n# Create a workbook using the Workbook() function of the openpyxl module and store it in a variable\r\nworkbook = openpyxl.Workbook()\r\n\r\n# Set the above workbook to active by applying the active attribute to the workbook and store this sheet in a variable\r\nworksheet = workbook.active\r\n\r\n#G ive the Data Values of the rows as a list and store it in a variable  \r\nrowsData = [\r\n    (\"Number of Mobiles\", \"Sales in Rs\", \"share Values\"),\r\n    (17, 12200, 18),\r\n    (30, 60000,33),\r\n    (28, 24400, 20),\r\n    (32, 32000, 52)\r\n]\r\n  \r\n# Fill out the first, second, and third columns of the active sheet with the content of each row.\r\nfor rowVal in rowsData:\r\n    worksheet.append(rowVal)\r\n  \r\n# Create the ScatterChart() object using the ScatterChart() function and store this in a variable\r\nchartObj = ScatterChart()\r\n  \r\n# Creating Data to Plot\r\nx_values = Reference(worksheet, min_col = 1,\r\n                    min_row = 2, max_row = 5)\r\n                      \r\ny_values = Reference(worksheet, min_col = 2,\r\n                    min_row = 2, max_row = 5)\r\n                      \r\nsize = Reference(worksheet, min_col = 3,\r\n                min_row = 2, max_row = 5)\r\n  \r\n# creating the 1st series of data using the series() function\r\nseries = Series(values = y_values, xvalues = x_values,\r\n                      zvalues = size, title =\"2022\")\r\n  \r\n# Append the series of data to the chart object using the append() function\r\nchartObj.series.append(series)\r\n  \r\n# Set the title of the Chart Object using the title attribute\r\nchartObj.title = \" PlOTTING SCATTER CHART \"\r\n  \r\n# Set the x axis title of the above chart using the x_axis,title attributes\r\nchartObj.x_axis.title = \" X-AXIS \"\r\n  \r\n# Set the y axis title of the above chart using the x_axis,title attributes\r\nchartObj.y_axis.title = \" Y-AXIS \"\r\n  \r\n# Add the above chartObject to the sheet using the add_chart() function \r\n# by passing the chartObject and anchored cell values as arguments to it.\r\nworksheet.add_chart(chartObj, \"E2\")\r\n  \r\n# Save the workbook by passing the fileName of the plot as an argument to the save() function.\r\nworkbook.save(\"chartScatter.xlsx\")<\/pre>\n<p><strong>Output:<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-3085\" src=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2022\/05\/scatter-Chart.jpg\" alt=\"scatter Chart\" width=\"1217\" height=\"527\" srcset=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2022\/05\/scatter-Chart.jpg 1217w, https:\/\/sheetstips.com\/wp-content\/uploads\/2022\/05\/scatter-Chart-300x130.jpg 300w, https:\/\/sheetstips.com\/wp-content\/uploads\/2022\/05\/scatter-Chart-1024x443.jpg 1024w, https:\/\/sheetstips.com\/wp-content\/uploads\/2022\/05\/scatter-Chart-768x333.jpg 768w\" sizes=\"(max-width: 1217px) 100vw, 1217px\" \/><\/p>\n<p>You now have the knowledge necessary to create bubble charts and scatter charts in Excel using Python. Try it out for yourself and let us know if the tips we provided here were helpful.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Users can automate a range of Excel functions, including reading, writing, performing computations, making graphs, and more, using one of the most important Python programs, Openpyxl. In this article, we will discuss how to create charts in an Excel sheet using Openpyxl without actually opening the Excel sheet. In order to construct a chart on &#8230; <a title=\"Python Program Plotting Charts in Excel Sheet using Openpyxl module | Set 2\" class=\"read-more\" href=\"https:\/\/sheetstips.com\/python-plotting-charts-in-excel-sheet-using-openpyxl-module-set-2\/\" aria-label=\"More on Python Program Plotting Charts in Excel Sheet using Openpyxl module | Set 2\">Read more<\/a><\/p>\n","protected":false},"author":3,"featured_media":3106,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[182],"tags":[],"jetpack_featured_media_url":"https:\/\/sheetstips.com\/wp-content\/uploads\/2022\/05\/Python-Program-Plotting-Charts-in-Excel-Sheet-using-openpyxl-module-Set-2.png","jetpack-related-posts":[{"id":2974,"url":"https:\/\/sheetstips.com\/python-plotting-charts-in-excel-sheet-using-openpyxl-module-set-3\/","url_meta":{"origin":2981,"position":0},"title":"Python Program Plotting Charts in Excel Sheet using openpyxl module | Set 3","author":"David","date":"June 1, 2022","format":false,"excerpt":"One of the most significant Python packages, Openpyxl, enables users to automate a variety of Excel tasks, including reading, writing, mathematical calculations, creating graphs, and more. Let's talk about how to use Openpyxl to make charts on an Excel sheet without launching the Excel sheet in this article. Before plotting\u2026","rel":"","context":"In &quot;MS Excel&quot;","block_context":{"text":"MS Excel","link":"https:\/\/sheetstips.com\/category\/ms-excel\/"},"img":{"alt_text":"Python Program Plotting Charts in Excel Sheet using openpyxl module Set 3","src":"https:\/\/i0.wp.com\/sheetstips.com\/wp-content\/uploads\/2022\/05\/Python-Program-Plotting-Charts-in-Excel-Sheet-using-openpyxl-module-Set-3.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":2982,"url":"https:\/\/sheetstips.com\/python-plotting-charts-in-excel-sheet-using-openpyxl-module-set-1\/","url_meta":{"origin":2981,"position":1},"title":"Python Program Plotting Charts in Excel Sheet using Openpyxl module | Set 1","author":"David","date":"June 1, 2022","format":false,"excerpt":"Openpyxl is one of the most important Python libraries which helps users to automate excel operations such as reading, writing, mathematical operations, plotting graphs, and much more. In this article, let us discuss how to create charts in an Excel sheet using Openpyxl without launching the Excel sheet. To create\u2026","rel":"","context":"In &quot;MS Excel&quot;","block_context":{"text":"MS Excel","link":"https:\/\/sheetstips.com\/category\/ms-excel\/"},"img":{"alt_text":"Python Program Plotting Charts in Excel Sheet using Openpyxl module Set 1","src":"https:\/\/i0.wp.com\/sheetstips.com\/wp-content\/uploads\/2022\/05\/Python-Program-Plotting-Charts-in-Excel-Sheet-using-Openpyxl-module-Set-1.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":2832,"url":"https:\/\/sheetstips.com\/how-to-automate-an-excel-sheet-in-python\/","url_meta":{"origin":2981,"position":2},"title":"How to Automate an Excel Sheet in Python? &#8211; Excel Automation Using Python Pandas","author":"David","date":"June 1, 2022","format":false,"excerpt":"Automate Excel With Python: When you are working on the same dataset, entering the data manually would be extremely time-consuming and boring. To overcome this and save us time, we can simply automate our Excel file with Python. And to automate the XLSX file to Python, one will have to\u2026","rel":"","context":"In &quot;MS Excel&quot;","block_context":{"text":"MS Excel","link":"https:\/\/sheetstips.com\/category\/ms-excel\/"},"img":{"alt_text":"How to Automate an Excel Sheet in Python","src":"https:\/\/i0.wp.com\/sheetstips.com\/wp-content\/uploads\/2022\/05\/How-to-Automate-an-Excel-Sheet-in-Python.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":3128,"url":"https:\/\/sheetstips.com\/python-writing-excel-file-using-openpyxl-module\/","url_meta":{"origin":2981,"position":3},"title":"Python Program for Writing to an Excel File using openpyxl Module","author":"Anthony","date":"June 1, 2022","format":false,"excerpt":"Openpyxl is a Python library that reads and writes Excel files (with the extensions xlsx\/xlsm\/xltx\/xltm). Python openpyxl module allows the program to read and modify Excel files. For example, a user will have to go through hundreds of rows of data and select a few handful of information to make\u2026","rel":"","context":"In &quot;MS Excel&quot;","block_context":{"text":"MS Excel","link":"https:\/\/sheetstips.com\/category\/ms-excel\/"},"img":{"alt_text":"Python Program for Writing to an Excel File using openpyxl Module","src":"https:\/\/i0.wp.com\/sheetstips.com\/wp-content\/uploads\/2022\/06\/Python-Program-for-Writing-to-an-Excel-File-using-openpyxl-Module.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":3130,"url":"https:\/\/sheetstips.com\/python-reading-excel-file-using-openpyxl-module\/","url_meta":{"origin":2981,"position":4},"title":"Python Program for Reading an Excel File using openpyxl Module","author":"Anthony","date":"June 1, 2022","format":false,"excerpt":"Openpyxl is a Python library that reads and writes Excel files (with the extensions xlsx\/xlsm\/xltx\/xltm). Python openpyxl module allows the program to read and modify Excel files. For example, a user will have to go through hundreds of rows of data and select a few handful of information to make\u2026","rel":"","context":"In &quot;MS Excel&quot;","block_context":{"text":"MS Excel","link":"https:\/\/sheetstips.com\/category\/ms-excel\/"},"img":{"alt_text":"Python Program for Reading an Excel File using openpyxl Module","src":"https:\/\/i0.wp.com\/sheetstips.com\/wp-content\/uploads\/2022\/06\/Python-Program-for-Reading-an-Excel-File-using-openpyxl-Module.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":3132,"url":"https:\/\/sheetstips.com\/python-arithmetic-operations-in-excel-file-using-openpyxl\/","url_meta":{"origin":2981,"position":5},"title":"Python Program for Arithmetic Operations in Excel File using openpyxl","author":"Anthony","date":"January 22, 2024","format":false,"excerpt":"Using openpyxl to read and write to an excel sheet openpyxl Module: openpyxl is a Python library that allows you to do various actions on Excel files such as reading, writing, mathematical operations, and graph plotting. Now we look at how to use openpyxl to execute various Arithmetic computations. =SUM(cell1:cell2):\u2026","rel":"","context":"In &quot;MS Excel&quot;","block_context":{"text":"MS Excel","link":"https:\/\/sheetstips.com\/category\/ms-excel\/"},"img":{"alt_text":"Python Program for Arithmetic Operations in Excel File using openpyxl","src":"https:\/\/i0.wp.com\/sheetstips.com\/wp-content\/uploads\/2022\/06\/Python-Program-for-Arithmetic-Operations-in-Excel-File-using-openpyxl.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]}],"_links":{"self":[{"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/posts\/2981"}],"collection":[{"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/comments?post=2981"}],"version-history":[{"count":7,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/posts\/2981\/revisions"}],"predecessor-version":[{"id":4158,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/posts\/2981\/revisions\/4158"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/media\/3106"}],"wp:attachment":[{"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/media?parent=2981"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/categories?post=2981"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/tags?post=2981"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}