Install Openpyxl:

Openpyxl - Introduction

Variables that are used on this page represents to

sh = worksheet, wkb = workbook, newsh = newly created worksheet., these are also defined on this page

To load a workbook into python

Openpyxl is a package which enables the python to establish the connection with excel

Import a workbook into python:

wkb = openpyxl.load_workbook("Sales.xlsx")
To load a workbook of "Sales" into python

Print Sheet Names:

print(wkb.sheetnames)

Add a sheet to a workbook:

Create a Sheet as 2nd worksheet

Sheet Index count starts from zero

wkb.create_sheet("Second Place", index = 1)

Create a Sheet as first worksheet

wkb.create_sheet("First Place", index = 0)

Print Worksheet Names:

print(wkb.sheetnames)

Assign a sheet name to worksheet Object:

sh = wkb["Second worksheet"]

Assign a sheet name to newly created worksheet Object:

Newsh = wkb.create_sheet(index = 2, title = "LowerCase Sheet")

Remove worksheet:

wkb.remove(sh)
wkb.remove_sheet(wkb.get_sheet_by_name ("First First"))

Write a data into cell:

sh = wkb['Input']
sh['A2'] = 'List of Items'
sh['A3'] = 'Apple Banana'
sh['A4'] = 'Grapes Orages'
sh['A5'] = 'Black Board'
sh['A6'] = 'Marker and Eraser'
sh['A7'] = 'Pen and pencil'
sh['A8'] = 'Tricks 12345'
sh['A9'] = 'Back Bench'

Find Max row and max column at sheet level

Max column at sheet level

Max row at sheet level

Print the data from Excel

Here data exists in a workbook from 2nd row in first column
maxrow returns last used non blank cell at sheet level

Save the workbook

wkb.save("Python_with_Excel_String_Functions.xlsx")

Copy the data from one worksheet to another

Newsh.cell(row = 1, column = 1).value = sh.cell(row = 1, column =1).value

Sh and Newsh are two different worksheet variables
Now i am copying the data from sh to Newsh

How to store the data into variable

Data = Newsh.cell(row = 1, column = 1).value

Store the cell value into the variable of Data
First row and first column value stored in the variable of Data

Write the data in a cell

Newsh.cell(row = 1, column = 2).value= Data

Convert the data into lower case

Newsh.cell(row = r, column = 2).value= Data.lower()

Increase the column width

Newsh.column_dimensions['A'].width = 22

Newsh.column_dimensions['D'].width = 22

State about Dir Function

Click on image to watch the video:

Create Attendance Sheet

Create Table

Iterate Column By Column (or) Row By Row

Click on code to watch the video: