Imagine having to create accounts for 10,000 employees on a website. Sounds overwhelming, right? Doing this manually would be a nightmare – it’s tedious, time-consuming, and honestly not a great idea.

Now, think about data entry workers. They extract information from tables, maybe in Excel or Google Sheets, and then input it somewhere else. They read through various sources, gather data, and enter it into a database. On top of that, they have to do calculations for the entries.

Their pay often depends on how much data they handle – the more they process, the more they earn. But let’s be honest, doing the same repetitive task over and over can get pretty boring.

So, the question is, how can we make this process faster? How can we automate our work?

Well, here’s an idea: spend some time coding. Instead of doing these tasks manually, you can write a few lines of Python code to automate the whole thing. It’s like a shortcut to make your life easier and save you from the monotony of repetitive tasks.

Openpyxl

The Openpyxl Python module is employed for interacting with Excel files, allowing users to both read from and write to them. Data scientists leverage Openpyxl for various tasks such as data analysis, copying data, mining information, creating charts, styling sheets, incorporating formulas, and more.

Workbook: In Openpyxl, a spreadsheet is referred to as a workbook. There may be one or many sheets in a workbook.

Sheet: A sheet is a single page made up of cells used to arrange data.

Cell: A cell is a point where a row and a column converge. The usual representations are A1, A3, B8, etc.

Row: A row is a horizontal line that is represented by a number (1,2, etc.).

Column: A capital letter indicates a vertical line as a column (A, B, etc.).

The Preferred Installer Program (PIP) command can be used to install Openpyxl, and installing it in a virtual environment is advised.

How to Create New Workbook in Python

import openpyxl

import xlsxwriter

wb = xlsxwriter.Workbook('C:/Users/Swarna Khushbu/Desktop/new.xlsx')



# Closing the workbook using the 'close' method

wb.close()

use your path in the above code

How to Import a Workbook in Python

We load the file using the function load_Workbook(), which accepts the filename as an input. It is necessary to store the file in the same working directory.

import openpyxl

from openpyxl import Workbook, load_workbook

wb = load_workbook('C:/Users/Swarna Khushbu/Desktop/new.xlsx')

ws = wb.active

# after setting workbook active you can print sheet name of workbook

print(ws)

Output: <Worksheet ”Sheet1″>

How to Read Cell value form Worksheet /Excel sheet using Python

import openpyxl

wb = openpyxl.load_workbook('C:/Users/Swarna Khushbu/Desktop/new.xlsx')

ws = wb.active

a = ws['A2'].value # to retrive A2 cell values

print(a)

First value this is John Cell ‘A2’ being extracted in the above code

output:

How to change cell values:

import openpyxl



# Load the existing workbook

wb = openpyxl.load_workbook('C:/Users/Swarna Khushbu/Desktop/new.xlsx')



# Select the active sheet

ws = wb.active



# Access the cell 'A3' and update its value

ws['A3'] = 'Robert'



# Save the workbook with the updated value

wb.save('C:/Users/Swarna Khushbu/Desktop/new.xlsx')