Automate an excel using Python

We all know that Python is ruling all over the world, and we also know that Python is beginner’s friendly and it’s easy to learn in comparison to other languages. One of the best things you can do with Python is Automation.

Why we require to automate python with excel?

Consider a scenario that you’re asked to create an account on a website for 30,000 employees. How would you feel? Surely you will be frustrated doing this task manually and repeatedly.

Now just imagine the life of employees who are into the data entry jobs. Their job is to take the data from tables such as Excel or Google Sheet and insert it somewhere else. They browse different websites and magazine, they collect the data from there, and then they insert it into the database. They also need to do the calculations for the entries.

Generally, the wages is based on the performance in this work. More entries, higher the wages.

In this blog, we will create a small project to learn automation in Python. "If you’re a beginner then you may prefer to watch some videos to learn the automation in Python and reading this blog might be a boring task for you but here we will go through step by step to explain everything in detail and to make things easier for you".

Introduction of Task:-

In the above excel sheet, we have the recorded some data for all kinds of transactions, but let’s say due to an error, the price for the product listed in the 3rd column is wrong. We need to decrease the price by 10%. You can do this task manually by using a mathematical formula in the 4th column but it will take huge time if there are thousands of data.

Now Lets start the Main Coding part:-

there are so many libraries available for python to work on in different number of task, but we will be using the library for automation of excel via python is openpyxl.

For installing the library we are using pip:-  pip install openpyxl

Now lets have a look what our code actually look like:-

import openpyxl as xl
from openpyxl.chart import BarChart, Reference

wb = xl.load_workbook('python-spreadsheet.xlsx')
sheet = wb['Sheet1']

for row in range(2, sheet.max_row + 1):
cell = sheet.cell(row, 3)
corrected_price = float(cell.value.replace('$','')) * 0.9
corrected_price_cell = sheet.cell(row, 4)
corrected_price_cell.value = corrected_price

values = Reference(sheet, min_row=2, max_row=sheet.max_row, min_col=4, max_col=4)
chart = BarChart()
chart.add_data(values)
sheet.add_chart(chart, 'e2')

wb.save('python-spreadsheet2.xlsx')

Lets understand this:-

Step 1:- We have install  or import a package named openpyxl and also we need to import BarChart,                  References. This BarChart library is used for adding the chart.

Step 2:- Now we need to load the excel sheet for that we are creating a variable wb  and now store that                   sheet with the filename in function braces.

Step 3:-  To use the column 2 to 4 we have to create a loop in it. We are saving our entries in variable                    named cell.

Step 4:-  Now we need to calculate the corrected prices. So we are multiplying the values saved in the                   cell variable with 0.9. Once the calculation is done we need to add all the corrected prices in                   a new column. To add a new column we will get a reference to the cell in the given                                  row but in the fourth column. Once the cell is created, we need to set the corrected price                       values in this cell .

Step 5:-  Now we need to save all updated entries and the chart we have created in the above code. We                will save this in a new file python-spreadsheet2.xlsx because we don’t’ want to accidentally                   overwrite the original file in case our program has a bug.

               Run your program. A newly updated file python-spreadhsheet2.xlsx  will be                                             created for you with updated prices and charts.

Step 6:-  This program is only relying on a specific file that is python-spreadsheet.xlsx.

             To make it work for several spreadsheets we will reorganize this code, and we will move the                 code inside a function. This function will take the name of the file as an input and it will                         execute the process. Below is the updated code for the same.