How to automate your excel file using openpyxl?
I will write about accessing excel files using openpyxl and show its usage to create various charts.
3 January, 2023
1
1
1
Contributors
openpyxl is a python library to read/write excel xlsx/xlsm/xltx/xltm files. We install the library in the Jupyter notebook as shown below.
Then we import relevant functions we will use to carry out the operations some of which are mentioned below.
The above functions create Bar charts, Pie Charts, and their 3D versions. The function load_workbook is used to load the excel workbook. Pandas is used to convert any excel file into a data frame format so multiple data analysis operations can be carried out. I have used the code below to load the .csv file.
The first 5 rows of the data frame
Next, we will see a pivot table operation to find the maximum count of a particular crime for the years 2001 to 2012.
The result will be stored in an excel (.xlsx) file.
Pivot table representation of a crime (maximum number) between the years 2001 and 2012
Now that we have a report generated using pandas, let's start using openpyxl and see what we can get out of them.
The code above uses the created report (report_2021.xlsx) and extracts the maximum and minimum row and column numbers which are min_column, max_column, min_row, and max_row. Then a bar chart is created using the code below.
The code has data and categories which are the data and the years between 2001 and 2012. The bar chart is created from cell H12. The image is shown below.
The bar chart of the maximum number of crime between the years 2001 and 2012.
Similarly, I also created the 3D Barchart for the same excel report as above. The code is.
The result is as below.
3D Bar chart results using the same data as the bar chart
The 3D pie chart was created as below.
The code is repeated for all the cases, just that the objects like BarChart(), BarChart3D(), and PieChart3D() are changed for creating different types of charts. The 3D Pie chart looks as below.
3D Pie chart results
So, here I end the article on some of the applications of openpyxl. Of course, there are tons of applications using openpyxl which we can do. I will add a few more articles on those topics. Mostly, I referred to the original documentation of openpyxl to create the codes.