Learn A-Z Excel Automation with Python

Excel Automation

Table of contents

1. Introduction

2. Write data to excel spreadsheets using Openpyxl

3. Write data to excel spreadsheets using Pandas

4. Read data from excel spreadsheets using Openpyxl

5. Read data from excel spreadsheets using Pandas

6. Creating multiple spreadsheets in excel

7. Combining multiple excel spreadsheets into a single master excel-spreadsheet

8. Plotting a bar graph

9. Plotting a pie graph

10. Sample Project 1

11. Sample Project 2

12. Reference

Introduction

The article aims to showcase task-automation in Microsoft Excel.
One might want to automate tasks in excel due to a plethora of reasons, of which the following are only two examples:

• The task at hand requires repetitive actions on excel spreadsheets.
• The task is time-intensive if done manually.

Requirements

python   
pandas
openpyxl

Write data to excel spreadsheets using Openpyxl

fig: 1

Write data to excel spreadsheets using Pandas

fig: 2

Read data from excel spreadsheets using Openpyxl

fig: 3

Read data from excel spreadsheets using Pandas

fig: 4

Creating Multiple spreadsheets in excel

fig: 5

Combining multiple excel spreadsheets into a single excel spreadsheet

fig: 6

Plotting a bar graph

fig: 7

Plotting a pie graph

fig: 8

Sample Project 1

Task 1: we are going to add two more columns in excel spreadsheet Result and Grade.

In the Result column, we will put pass and Failed based on the Percentage He/She got.

• Pass: If the percentage is greater than and equal to 40.
• Failed: If the percentage is less than 40

In the Grade column, we will put 1st class, 2nd class, 3rd class and Failed based on Percentage He/She got.

• 1st class: If percentage greater than equal to 60.
• 2nd class: If percentage greater than equal to 50 but less than 60.
• 3rd class: If percentage greater than equal to 40 but less than 50.
• Failed: If percentage less than 40.

Task 2: After doing the first task we will create multiple sheets in the same excel file based on the Grade He/She got.

fig: 9

• Performing Task 1 (Adding Result column)

fig: 10

• Performing Task 1 (Adding Grade column )

fig: 11

• Separating into multiple sheets in the same excel file based on the grade obtained.

fig: 12

Sample Project 2

• In this project, we are separating the records into different excel spreadsheets for different Grade.

fig: 13

Github

https://github.com/rajansahu713/Excel-Automation-With-Python

Reference

1. https://openpyxl.readthedocs.io/en/stable/
2. https://zetcode.com/python/openpyxl/
3. https://pandas.pydata.org/
4. https://rajansahu713.medium.com/learn-a-z-excel-automation-with-python-e07fd42d37ad

Author 

Rajan sahu