Online Python Tutor & Python Trainings

PythonTutor.net

×

Oh snap! We just showed you a modal..

Because we can

Cool huh? Ok, enough teasing around..

Go to our W3.CSS Tutorial to learn more!

Openpyxl Python Tutorial


Openpyxl Tutorial

If you have to read or write data in MS Excel with Openpyxl, Load or create a worbook, create, add, modify or delete a sheet, create Python excel charts, format or style a sheet see our Openpyxl tutorial.

Please Scroll down for Openpyxl Tutorial.
Thanks.

Python Training


Python for Beginners

Openpyxl Training

Numpy & Matplotlib Training

Python Pandas Training

Starting from $25/Hour Only

Contact us

Online Training Features


Live (1 on 1) Skype

Payment after 1 Hour Session

High Quality Tutoring with Examples

50% Off! Limited Offer

$50/Hr >>> $25 / Hour only

Join Course


Online Python Programming Tutor at Skype

• We Provide Python Programming Online Tutoring for all levels, Beginners and advanced level and for data science, Machine Learning, Deep Learning.

• We help and guide you in modules like Intro to Python, Openpyxl, Numpy, Matplotlib, Pandas, Keras, Tensorflow etc.

• CS, Engineering, Social Sciences, Psychology and others aspiring to learn Python for any purpose, don't worry, You are at right place. With us you will Learn Python expertise and skills and will be second to NONE

Home Trainings Join Course Contact Us

Python Openpyxl Tutorial: Read and Write Excel files in Python

If you have to read or write data to excel files in Python, Openpyxl is the solution for creating Excel worksheets, loading workbooks, adding copying, rename or delete a sheet, read or write to rows and columns, merge unmerge cells, Python excel charts. It also provides features for formatting and styling sheets. This tutorial also shows how to read from excel sheet and convert data into numpy array and plot it with matplotlib. Openpyxl Engine is very helpful in Python Pandas. Hence for dealing with MS Excel files in Python, this is the most simple and practicle tutorial. Check it yourself.

Openpyxl working terminology

Read data from an Excel file


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 I will suggest you to see my simple and to the point Udemy Course about Python and Excel files. You will learn the skill fast with illustrations and live code examples.

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

If you want help in writing a Python code for your Excel tasks, or you want whole code written and delivered to you, please consider to contact us our expert Python Excel team is ready to help you to achieve your task within 24 hours. For just a minimal fee starting from $25 ONLY, we will write the code for you which will do your Excel tasks, in this way you automate your work and save dozens of hours and plenty of money! You pay only after the code is delivered and it works for you and you are completely satisfied. Just try us!


Here is an Ms Excel file which you are going to read with openpyxl. Create an excel file with the data on 'Sheet1' as shown in figure. Save this file in your Python Root folder and name it as "exceltest.xlsx". If you don't know where is your python root folder simply create a new folder "openpyxl" on your D: drive and save this file in that folder with name "exceltest.xlsx". In later case, full path of this file is "D:/openpyxl/exceltest.xlsx"

Excel file to read in Python

After creating this file and filling data in cells exactly as shown in figure now its time to read the file in Python.

Steps in reading file in Python Openpyxl:

1. Load workbook in memory and create a reference to that workbook.
2. Create a reference to sheet of the workbook you load in memory.
3. Access individual cells, first create a reference to cell and then read the data with the help of that reference.
Thats it. Simple isn't it?
Now we implement these steps.

Python Openpyxl Path Explanation:


Now open your python interpreter and write the following code

>>>import os

>>>os.getcwd()

This will result in this reply from interpreter, if python is installed on C: drive.



'C:\\Python34'

>>>


Or if you are using Pycharm, write the code in console (you can start Python Console on Pycharm by going from menu bar,
Tools>Python Console) and lets suppose your Pycharm is installed on D: drive, the code will result in

'E:\\Users\\TEMP\\PycharmProjects\\openpyxlworking'

Have a screen shot of both, Python Console is like that

Python Console Example

And for Pycharm Console it will look like this

Pycharm Console example

Explanation:

>>>import os

This means we are importing Operating System module to see directories and paths. In Python when we have to use any module we have to import it to make efficient memory management.

>>>os.getcwd()

This code will show what is the current working directory  ... cwd. We are asking OS to get Current working directory.       os.getcwd()
When we call a function in Python we write the name of module or class and use a dot operator and write the name of function followed by parenthesis.

From the details, either your excel file should be in one of the directories    

C:\\Python34

or openpyxlworking

and if you have saved it on D: drive in openpyxl folder, then you will have to give path to python for opening the file. Which is explained below.

Reading Excel file Steps:

1. Loading Workbook in Memory:

Step 1:


import openpyxl module which is to be used

>>>import openpyxl

Step 2:


Write path of your file. If the file is in your python root folder you don't have to give path just write file name and extension. If you saved file as exceltest.xlsx in openpyxl folder on D: drive the path is


>>> path="d:/openpyxl/exceltest.xlsx"

 Step 3:


Load file or Workbook in to memory

If same directory then

>>>wb=openpyxl.load_workbook('exceltest.xlsx')

if saved on D:/openpyxl

>>>wb=openpyxl.load_workbook(path)

Where the path is "d:/openpyxl/exceltest.xlsx"
. You may write any path, depending on where your file is saved on your computer.


Step 4:


Check type of wb

>>>type(wb)

<class 'openpyxl.workbook.workbook.Workbook'>

Complete code up to step 4.

"Load Workbook in Memory "

2. Accessing Sheets from Workbook:

Step 1:

If you see above mentioned code, that means workbook is successfully loaded and you have created a reference "wb" to your excel file. From onwards you will access this file with the help of this reference. Next step is to work with sheets. An excel spreadsheet has 3 Sheets by default and they are named as Sheet1, Sheet2 and Sheet3 respectively. 

To get information about total number of sheets in a workbook and their names we use the function get_sheet_names()

>>>wb.get_sheet_names()

['Sheet1', 'Sheet2', 'Sheet3']

Change sheet names to First, Second and Third respectively and save the file. Load the file again by running through steps 1 to 4. Call get_sheet_names function again

>>>wb.get_sheet_names()

['First', 'Second', 'Third']


Change sheet names again to Sheet1,  Sheet2 and Sheet3


Step 2:

To access sheets on a workbook, you have to create reference to that particular sheet. This is just like you created reference wb to workbook. For creating reference to worksheet one of the most common functions used is get_sheet_by_name(). This function takes sheet name as an argument and creates a reference to that particular sheet which is used to access cells on the sheet and ultimately the data stored in the cells. 

>>>sheet=wb.get_sheet_by_name('Sheet1')

>>>type(sheet)

<class 'openpyxl.worksheet.worksheet.Worksheet'>

Here a reference sheet is created to Sheet1 of exceltest.xlsx file. And when you check the type of reference by typing type(sheet) the interpreter tells that it is a reference to sheet. If you want to know the title of sheet referenced by sheet, 

>>>sheet.title()

'Sheet1'

A workbook has one active sheet. To know about active sheet

>>>wb.active

<Worksheet "Sheet1">

A summary of creating reference to Workbook and sheet, see code in Pycharm.

reference to sheet in openpyxl

3. Accessing cells and data stored in cells:

Cells in a workbook are accessed with the help of creating reference to them and value from a cell is extracted with that reference.

I will explain here three methods to read data from a cell.


The first one and very simple method is that you write the sheet reference sheet followed by square brackets with cell address  written as a string in those brackets ['B4'] followed by a dot . and keyword value, see the code. The interpreter will simply write the value stored in that particular cell, B4 contains Sara in our example. 

>>>sheet['B4'].value

Sara


Second method is that we create a reference to particular cell and then access the data with the help of that reference.


>>>a=sheet['B2']

>>>a.value

John


>>> type(a)

<class 'openpyxl.cell.cell.Cell'>

>>> a.row

 2

>>> a.column

 2


Third method which is most widely used, specially by me is accessing cells with row and column numbers. The benefit is you can iterate through whole row or column or the multidimensional grid with the help of this method very easily.

First you have to have the row and column number of cell you want to access. Then you read data with the help of creating reference to that cell.

>>>b=sheet.cell(row=3,column=4)

>>>b.value

56

>>> type(b)

<class 'openpyxl.cell.cell.Cell'>

Type shows that b is a reference to a cell. For learning purpose always check the type of reference you are creating. It will enhance your understanding how openpyxl works. 


Reading and Printing a whole Row with Openpyxl:

If you want to print a whole row, key feature is that row will remain same while you iterate through column. Our exceltest file has 16 rows and 5 columns. To read and print a row a for loop is used. You can print any row with this method.

>>> for c in range(1,6):
            d=sheet.cell(row=3,column=c)
            print(d.value)

   
2
Liza
F
56
Under Grad
>>> 

Row is printed but in the form of a Column. To print in a single line or row, use end=" " in print function.

>>> for c in range(1,6):
            d=sheet.cell(row=3,column=c)
            print(d.value + end=' ')

2  Liza  F  56  Under Grad


Reading and Printing a whole Column with Openpyxl:

In the same way, when you have to print a whole column, use a for loop, keep the column same while iterate through each row with the help of for loop. To read column 2  from excel file,

>>> for r in range(1,17):
            d=sheet.cell(row=r,column=2)
            print(d.value)

   
Name
John
Liza
Sara
David
Sean
Julie
Connor
Bush
Hilton
Maya
Collin
Phil
Demy
King
Princess

Reading Rows and Columns from a sheet:

After printing rows and columns, its time to print rows and columns simultaneously with python and openpyxl. This is reading whole sheet.

Openpyxl reading rows and columns

Using some formating for alignment and see all data i.e. rows and columns have been read from exceltest.xlsx

openpyxl reading rows and columns

Amazing isn't it? Now you have learned how to read from excel file with the help of Openpyxl and PythonTutor.net. 

More coming soon (Professor MN, Pythontutor.net)



Non Computer Science Background Students

We Specialize in teaching and tutoring Python from intro to advanced modules like Pandas and Machine learning to Non Programming and Non computer science Python learners.

If you are not from CS We will train you that there will be no difference between your Python skills and CS programmers Python skills.

Join Now

PythonTutor.net

Home    Tutorial     Online Live Trainings    Contact Us           © 2019 All rights reserved by www.PythonTutor.net

Go To Top