Python openpyxl

1 “Programming in Python With Openpyxl”
"PYTHON PROGRAMMING
With OPENPYXL "
Prepared by:
AMARjeetsingh thakur

1. INTRODUCTION
Openpyxl is a Python module to deal with Excel files without involving MS Excel
application software. It is used extensively in different operations from data copying to data
mining and data analysis by computer operators to data analysts and data scientists. openpyxl
is the most used module in python to handle excel files. If you have to read data from excel,
or you want to write data or draw some charts, accessing sheets, renaming sheets, adding or
deleting sheets, formatting and styling in sheets or any other task, openpyxl will do the job
for you.
If you want to Read, Write and Manipulate(Copy, cut, paste, delete or search for an item etc)
Excel files in Python with simple and practical examples we will suggest you to see this
simple and to the point Python Excel Openpyxl Course with examples about how to deal with
MS Excel files in Python. This course teaches efficiently how to manipulate excel files and
automate tasks.
Everything you do in Microsoft Excel, can be automated with Python. So why not use the
power of Python and make your life easy. You can make intelligent and thinking Excel
sheets, bringing the power of logic and thinking of Python to Excel which is usually static,
hence bringing flexibility in Excel and a number of opportunities.
Basics for Python excel openpyxl work:
 An Excel file is usually called as Spreadsheet, however in openpyxl we call it
Workbook.
 A single Workbook is usually saved in a file with extension .xlsx
 A Workbook may have as less as one sheet and as many as dozens of worksheets.
 Active sheet is the worksheet user is viewing or viewed before closing the file.
 Each sheet consists of vertical columns, known as Column starting from A.
 Each sheet consists of rows, called as Row. Numbering starts from 1.
 Row and column meet at a box called Cell. Each cell has specific address in reference
to Row and Column. The cell may contain number, formula or text.
 The grid of cells make the work area or worksheet in excel.

2. Python | Reading an excel file using openpyxl module
 Openpyxl is a Python library for reading and writing Excel (with extension
xlsx/xlsm/xltx/xltm) files. The openpyxl module allows Python program to read and
modify Excel files.
 For example, user might have to go through thousands of rows and pick out few
handful information to make small changes based on some criteria. Using Openpyxl
module, these tasks can be done very efficiently and easily.
 Use this command to install openpyxl module : pip install openpyxl
Code #1 : Program to print the particular cell value
# Python program to read an excel file
# import openpyxl module
import openpyxl
# Give the location of the file
path = "C:UsersAdminDesktopdemo.xlsx"
# To open the workbook
# workbook object is created
wb_obj = openpyxl.load_workbook(path)
# Get workbook active sheet object
# from the active attribute
sheet_obj = wb_obj.active
# Cell objects also have row, column,
# and coordinate attributes that provide
# location information for the cell.
# Note: The first row or
# column integer is 1, not 0.
# Cell object is created by using
# sheet object's cell() method.
cell_obj = sheet_obj.cell(row = 1, column = 1)
# Print value of cell object
# using the value attribute
print(cell_obj.value)
Output :
STUDENT 'S NAME
Code #2 : Determine total number of rows
import openpyxl

# to open the workbook
# print the total number of rows
print(sheet_obj.max_row)
Output :
6
Code #3 : Determine total number of columns
# importing openpyxl module
import openpyxl
# print total number of column
print(sheet_obj.max_column)
Output :
4
Code #4 : Print all columns name
import openpyxl
max_col = sheet_obj.max_column
# Loop will print all columns name
for i in range(1, max_col + 1):
cell_obj = sheet_obj.cell(row = 1, column = i)

Output :
STUDENT 'S NAME
COURSE
BRANCH
SEMESTER
Code #5 : Print first column value
import openpyxl
m_row = sheet_obj.max_row
# Loop will print all values
# of first column
for i in range(1, m_row + 1):
cell_obj = sheet_obj.cell(row = i, column = 1)
Output :
STUDENT 'S NAME
ANKIT RAI
RAHUL RAI
PRIYA RAI
AISHWARYA
HARSHITA JAISWAL
Code #6 : Print a particular row value
import openpyxl

max_col = sheet_obj.max_column
# Will print a particular row value
for i in range(1, max_col + 1):
cell_obj = sheet_obj.cell(row = 2, column = i)
print(cell_obj.value, end = " ")
Output :
ANKIT RAI B.TECH CSE 4

3. Python | Writing to an excel file using openpyxl module
Code #1 : Program to print an active sheet title name
import openpyxl
# Call a Workbook() function of openpyxl
# to create a new blank Workbook object
wb = openpyxl.Workbook()
# Get workbook active sheet
# from the active attribute.
sheet = wb.active
# Once have the Worksheet object,
# one can get its name from the
# title attribute.
sheet_title = sheet.title
print("active sheet title: " + sheet_title)
Output :
active sheet title: Sheet
Code #2 : Program to change the Title name
import openpyxl
sheet = wb.active
# One can change the name of the title
sheet.title = "sheet1"
print("sheet name is renamed as: " + sheet.title)
Output :
sheet name is renamed as: sheet1

Code #3 : Program to write to an Excel sheet
import openpyxl
sheet = wb.active
# Cell objects also have row, column
# and coordinate attributes that provide
# location information for the cell.
# Note: The first row or column integer
# is 1, not 0. Cell object is created by
# using sheet object's cell() method.
c1 = sheet.cell(row = 1, column = 1)
# writing values to cells
c1.value = "ANKIT"
c2 = sheet.cell(row= 1 , column = 2)
c2.value = "RAI"
# Once have a Worksheet object, one can
# access a cell object by its name also.
# A2 means column = 1 & row = 2.
c3 = sheet['A2']
c3.value = "RAHUL"
# B2 means column = 2 & row = 2.
c4 = sheet['B2']
c4.value = "RAI"
# Anytime you modify the Workbook object
# or its sheets and cells, the spreadsheet
# file will not be saved until you call
# the save() workbook method.
wb.save("C:UsersuserDesktopdemo.xlsx")
Output :

Code #4 :Program to add Sheets in the Workbook
import openpyxl
sheet = wb.active
# Sheets can be added to workbook with the
# workbook object's create_sheet() method.
wb.create_sheet(index = 1 , title = "demo sheet2")
wb.save("C:UsersuserDesktopdemo.xlsx")
Output :

4. Python | Adjusting rows and columns of an excel file
using openpyxl module
Worksheet objects have row_dimensions and column_dimensions attributes that control row
heights and column widths. A sheet’s row_dimensions and column_dimensions are
dictionary-like values; row_dimensions contains RowDimension objects and
column_dimensions contains ColumnDimension objects. In row_dimensions, one can access
one of the objects using the number of the row (in this case, 1 or 2). In column_dimensions,
one can access one of the objects using the letter of the column (in this case, A or B).
Code #1 : Program to set the dimensions of the cells.
import openpyxl
# from the active attribute.
sheet = wb.active
# writing to the specified cell
sheet.cell(row = 1, column = 1).value = ' hello '
sheet.cell(row = 2, column = 2).value = ' everyone '
# set the height of the row
sheet.row_dimensions[1].height = 70
# set the width of the column
sheet.column_dimensions['B'].width = 20
# save the file
wb.save('dimension.xlsx')
Output:
Merging the cells:

A rectangular area of cells can be merged into a single cell with the merge_cells() sheet
method. The argument to merge_cells() is a single string of the top-left and bottom-right cells
of the rectangular area to be merged.
Code #2 : Program to merge the cells.
import openpyxl
sheet = wb.active
# merge cell from A2 to D4 i.e.
# A2, B2, C2, D2, A3, B3, C3, D3, A4, B4, C4 and D4 .
# A2:D4' merges 12 cells into a single cell.
sheet.merge_cells('A2:D4')
sheet.cell(row = 2, column = 1).value = 'Twelve cells join together.'
# merge cell C6 and D6
sheet.merge_cells('C6:D6')
sheet.cell(row = 6, column = 6).value = 'Two merge cells.'
wb.save('merge.xlsx')
Output:
Unmerging the cells:
To unmerge cells, call the unmerge_cells() sheet method.
Code #3 : Program to unmerge the cells.
import openpyxl
wb = openpyxl.load_workbook('merge.xlsx')
sheet = wb.active
# unmerge the cells
sheet.unmerge_cells('A2:D4')
sheet.unmerge_cells('C6:D6')
wb.save('merge.xlsx')

Output:
Setting the font styles of the cells: To customize font styles in cells, important, import the
Font() function from the openpyxl.styles module.
Code #4 : Program to set the font of the text.
import openpyxl
# import Font function from openpyxl
from openpyxl.styles import Font
sheet = wb.active
sheet.cell(row = 1, column = 1).value = "Ankit Rai"
# set the size of the cell to 24
sheet.cell(row = 1, column = 1).font = Font(size = 24 )
# set the font style to italic
sheet.cell(row = 2, column = 2).font = Font(size = 24, italic = True)
# set the font style to bold
sheet.cell(row = 3, column = 3).font = Font(size = 24, bold = True)
# set the font name to 'Times New Roman'
sheet.cell(row = 4, column = 4).font = Font(size = 24, name = 'Times New
Roman')

wb.save('styles.xlsx')
Output:

5. Python | How to copy data from one excel sheet to
another
In this section, we will learn how to copy data from one excel sheet to destination excel
workbook using openpyxl module in Python.
For working with excel files, we require openpyxl, which is a Python library that is used for
reading, writing and modifying excel (with extension xlsx/xlsm/xltx/xltm) files. For copying
one excel file to another, we first open both the source and destination excel files. Then we
calculate the total number of rows and columns in the source excel file and read a single cell
value and store it in a variable and then write that value to the destination excel file at a cell
position similar to that of the cell in source file. The destination file is saved.
Procedure –
1) Import openpyxl library as xl.
2) Open the source excel file using the path in which it is located.
Note: The path should be a string and have double backslashes () instead of single backslash
(). Eg: Path should be C:UsersDesktopsource.xlsx Instead
of C:UsersAdminDesktopsource.xlsx
3) Open the required worksheet to copy using the index of it. The index of worksheet ‘n’ is
‘n-1’. For example, the index of worksheet 1 is 0.
4) Open the destination excel file and the active worksheet in it.
5) Calculate the total number of rows and columns in source excel file.
6) Use two for loops (one for iterating through rows and another for iterating through
columns of the excel file) to read the cell value in source file to a variable and then write it to
a cell in destination file from that variable.
7) Save the destination file.
Code #1 : Program to copy data from one excel sheet to another
import openpyxl as xl;
# opening the source excel file
filename ="C:UsersAdminDesktoptrading.xlsx"
wb1 = xl.load_workbook(filename)
ws1 = wb1.worksheets[0]
# opening the destination excel file
filename1 ="C:UsersAdminDesktoptest.xlsx"
wb2 = xl.load_workbook(filename1)
ws2 = wb2.active
# calculate total number of rows and
# columns in source excel file
mr = ws1.max_row
mc = ws1.max_column
# copying the cell values from source
# excel file to destination excel file

for i in range (1, mr + 1):
for j in range (1, mc + 1):
# reading cell value from source excel file
c = ws1.cell(row = i, column = j)
# writing the read value to destination excel file
ws2.cell(row = i, column = j).value = c.value
# saving the destination excel file
wb2.save(str(filename1))
Source File:

Output:

For more information contact:
amarjeetsinght@gmail.com
linkedin.com/in/amarjeetsingh-thakur-54915955

Python openpyxl

Recommended

More Related Content

What's hot (20)

Similar to Python openpyxl (20)

More from Amarjeetsingh Thakur (20)

Recently uploaded (20)

Python openpyxl