CONTENTS

1. INTRODUCTION
2. WITHIN AN EXCEL-SHEET
3. WITHIN MULTIPLE EXCEL-SHEETS

1) INTRODUCTION
1.1. How to install openpyxl?
1.2. How to create an excel file in Python?
1.3. How to write to excel file and read from it?
1.3.1 In 1st row,1st column
1.3.2 In 1st column, print 1 to 10 like S.No
2) Play within an Excel Sheet
2.1) Project: Phone Dairy Management
Module 1: Adding a Record
Module 2: listing the values from excel
Module 3: Search a record in the excel
 Module 4: Modify a Record
Module 5: Delete a Record
Module 6: Menu
Module 7: Complete code
2.2) Project: Multiple Choice Questions Generator
3) Play with multiple Excel-Sheets and Files, Ready for freelancing

The Joy of doing Projects

When I was learning to code, I was focusing too much on learning
concepts.
Later, I started solving the problems to understand more about the concepts.
I used to feel happy, but something was lacking.
If I cannot solve problems or create something beneficial to me and others,
then what's the use of knowing too many concepts?
Knowledge is not only to solve problems but also to take up the projects
that create something beneficial.
After learning, a little bit of concepts, we directly start the projects. After
you do the projects, then learn the basics auto, or you can google it. This
book was written with the concept of REVERSE ENGINEERING.
First, learn concepts and projects, but I say while learning projects, learn the
concepts, and then go to basics.
I promise you that just practice this book, you will play with the excel like
anything. It will be at your fingertips.

You can be a freelancer for Excel sheets. Ex: someone is having 20 excel
sheets, they have to merge those into one.
Just write a program, it will be done. You can check any website about
freelancing. It gives these assignments of merging or copy-paste from a few
excels sheets to one.
I want you to realize it more than I say about the book, and I definitely say
that this book will be getting fantastic reviews and ratings.
I wrote this book with simple words so that even a layperson should get
this, that's what my intension.
Please go on reading this and practice with pleasure for fun.
All the Best
Thanks & Regards,
Your friend

INTRODUCTION

How to install openpyxl?

Install openpyxl using "pip install openpyxl" at the command prompt.
Win +R then enter cmd .. command prompt will be opened, then you have
to move to the Scripts folder in the Python.
In my system, it is like
C:\Users\dell>cd C:\Users\dell\AppData\Local\Programs\Python\Python38-
32\Scripts

C:\Users\dell\AppData\Local\Programs\Python\Python38-32\Scripts>
Now type , pip install openpyxl
C:\Users\dell\AppData\Local\Programs\Python\Python38-32\Scripts>pip
install openpyxl
That gets installed.
Now, go to python IDE
Just verify,
>>> import openpyxl
Then …
How to create an excel file in Python?
Creating an excel sheet in D: , name = first.xlsx
>>> from openpyxl import Workbook
>>> wb = Workbook()
>>> filename='D:\\first.xlsx'
>>> wb.save(filename)

Just go to the D: and check whether the first.xlsx has been created or not.

Play within one Excel sheet

How to write to excel file?
3.1 In 1st row,1st column

Now , write to the 1st row and 1st column ,

>>> from openpyxl import load_workbook

>>> filename='D:\\first.xlsx'
>>> wb = load_workbook(filename)
>>> sheet = wb['Sheet']
>>> sheet['A1'] = 'Welcome'
>>> wb.save(filename)
Or
>>> sheet.cell(row = 2, column = 2).value = ' everyone
'>>> wb.save(filename)

Just check in the D: and go to first.xlsx file and check the output will be:
In ist column, print 1 to 10 like S.No.

>>> from openpyxl import load_workbook

>>> filename='D:\\first.xlsx'
>>> wb = load_workbook(filename)
>>> sheet = wb['Sheet']

These First 4 lines of the above program are the same as the previous
program.
It is like preparing food, to prepare something ..common things we do is
switch on the gas and keep the vessel. So for almost all the programs to deal
with excel, the above 4 lines are the same.
then continue
>>> for i in range(2,12):
sheet.cell(row = i, column = 1).value = i-1
i=i+1

>>> wb.save(filename)

I took i value from 2nd row because, in 1st row, we wrote the serial
number (S.No).
We have to start writing values from row 2 to row 12.
Project: Phone Dairy Management

Our first module is writing a name and phone number to the excel sheet.

Module 1: Adding a Record

Procedure:
1. Open the excel sheet.
2. Write to row =1, column=1 as Name
3.Write to row =1, column=1 as Phone Number
4.Row =2, column = 1 , give input for Name
5.Row =2, column = 2 , give input for Phone Number
6. Save the excel-sheet; otherwise, changes won't get reflected in the file.
Again the first 4 lines are the same, switch on the gas and keep the vessel.
Open a file and save it as phone.py

from openpyxl import load_workbook

filename='D:\\first.xlsx'
wb = load_workbook(filename)
sheet = wb['Sheet']
sheet.cell(row = 1, column = 1).value = 'Name'
sheet.cell(row = 1, column = 2).value = 'Phone Number'
name=input("Enter name=")
phnm=input("Enter phone num=")
sheet = wb['Sheet']
sheet.cell(row = 2, column = 1).value = name
sheet.cell(row = 2, column = 2).value = phnm
wb.save(filename)

Note: Make sure you close the excel sheet before executing the program.
Otherwise, it won't allow you to write the file. Gives an error: permission
denied

Module 2: listing the values from excel

How to see what are there in the excel file using Python?
print(sheet.max_row) this gives the maximum no of rows that are filled
print(sheet.max_column) this gives the maximum no of columns that are
filled
Procedure:
Open the excel sheet
Columns =1 and column = 2
Rows starts from 2 and ends by sheet.max_row

save this below program as list.py

from openpyxl import load_workbook
filename='D:\\first.xlsx'
wb = load_workbook(filename)
sheet = wb['Sheet']
for i in range(1,sheet.max_row):
print(sheet.cell(row = i, column = 1).value,sheet.cell(row = i, column =
2).value)
Output:

Module 3: Search a record in the excel

Search with a name, and find out their number.

Procedure:
1. Open the excel sheet.
2. Enter the name in the variable "search"
3. Search in the 1st column, and all rows start from 2nd row to
sheet.max_row
4. When the match found, read the value from the corresponding phone
number

# starting 4 lines are the same

from openpyxl import load_workbook
filename='D:\\first.xlsx'
wb = load_workbook(filename)
sheet = wb['Sheet']
search=input("Enter the name to know the phone number=")
for i in range(1,sheet.max_row):
if(search==sheet.cell(row = i, column = 1).value):
print('phone number=')
print(sheet.cell(row = i, column = 2).value)

Output:
Enter the name to know the phone number = Balu
9381407314
Module 4: Modify a Record
Enter a name to modify both the name and the phone number

Procedure:
Open the excel sheet.
Enter the name in the variable "search"
Search in the 1st column, and all rows
When the match is found, modify the row by writing new values.
Save the file. If not saved, then changes won't get reflected in the excel
sheet.

from openpyxl import load_workbook

filename='D:\\first.xlsx'
wb = load_workbook(filename)
sheet = wb['Sheet']
modify=input("Enter the name to modify the name and the phone
number=")
for i in range(1,sheet.max_row):
if(modify==sheet.cell(row = i, column = 1).value):
name_m = input(' Ente the name= ')
phnm_m = input('Enter the phone number= ')
sheet.cell(row = i, column = 1).value=name_m
sheet.cell(row = i, column = 2).value=phnm_m
wb.save(filename)
print('updated')
Home play:
Write a program , it should give us the option like
Do you want to change the name ?
Do you want to change only the phone number of the person?
Do you want to change them both?

Module 5: Delete a Record

Enter the name, then that entire row will be deleted
For that, we use sheet.delete_rows(row = , column = )
Procedure:
Open the excel sheet
Enter the name in the variable "delete"
Search in the 1st column, and all rows
When the match found , delete the row using sheet.delete_rows()
Save the file , if not saved then changes wont get reflected in the excel
sheet.
from openpyxl import load_workbook
filename='D:\\first.xlsx'
wb = load_workbook(filename)
sheet = wb['Sheet']
delete=input("Enter the name to delete ")
for i in range(1,sheet.max_row):
if(delete==sheet.cell(row = i, column = 1).value):
sheet.delete_rows(idx=i, amount=1)
wb.save(filename)
print('deleted the row ')

Output:

Project 2: Quiz / MCQ Multiple choice Questions

Generator
We will help school teachers and children with our knowledge.
A school teacher wanted a software, here requirement is:
The School teacher will be preparing different question papers with
answers. They are printing the question papers, and correcting them is
taking so much time, so he wants your advice and how you can make his
work done through software quickly and accurately.
Our project name is "MCQ Multiple Choice Questions."
The teacher gave the MCQ paper, which is having 10 questions in it.
Country will be given and 4 options, one option whichever is the correct to
be entered.
For the right answer, 1 mark will be added, and there is no negative
marking.

Countries as Questions and capitals with options, you have to find out the
right capital.
You can generate the question paper-like, capital given as a question and
countries as options.
Countries and currencies etc. etc
Just have an excel to input, and our software will do other things.
Implementation:
Code to generate a Question and get an answer
Code to generate a Question with 4 options
Finally, Generate 10 questions with options.

Code to generate one question with 4 options

import random
from openpyxl import load_workbook
filename='D:\\first.xlsx'
wb = load_workbook(filename)
sheet = wb['Sheet']
marks =0;
# generating a random question
q = random.randint(2,sheet.max_row)

question=sheet.cell(row = q, column = 1).value

print("Question = "+ question)
# getting the answer in a variable
correct_answer=sheet.cell(row = q, column = 2).value

# adding to a list "options"

options=[correct_answer]
sList = [2,3,4,5,6,7,8,9]
# these are just like serial No.s
rList = random.sample(sList,4)
for i in range(0,4):
n=rList[i]
if q!=n:
a=sheet.cell(row = n, column = 2).value
options.append(a)

if len(options)==5:
options.pop() # removes the last option
random.shuffle(options)
print("1."+options[0]+" 2."+options[1]+" 3."+options[2]+" 4."+options[3])

ea=int(input("Enter answer="))
if(options[ea-1]==correct_answer):
marks=marks+1

print("Total marks ="+ str(marks))

Output:
Play within multiple Excel sheets and Files
Mobile store Example:

for i in range(2,10):
from openpyxl import load_workbook
file1='D:\\file1.xlsx'
file2='D:\\file2.xlsx'
merge_file='D:\\merge.xlsx'

wb1 = load_workbook(file1)
sheet = wb1['Sheet1']
Value1=sheet.cell(row = i, column = 1).value

wb2 = load_workbook(file2)
sheet = wb2['Sheet1']
Value2= sheet.cell(row=i, column=1).value

wb3 = load_workbook(merge_file)
sheet = wb3['Sheet1']

sum=(Value1) + (Value2)
sheet.cell(row=i, column=1).value = sum
wb3.save(merge_file)
Conclusion:

Our Training programs: