Import Excel Sheet into Python

3 min read
Import Excel Sheet into Python

I remember when I had joined the FOSS Club in the 2nd Semester in my university for the first time , the first week we were told to do the course on python at The very next week , we were given a task to be completed . I believe that this task helped me in improving my skills in finding things.

The task was to read two numbers stored in two different cells in an Excel sheet , read them using python and add them to print their value (also using Python) .

We were not allowed to do anything else with the original excel sheet. So today I’ll be sharing the details on how I tackled the problem

I’ve got a few things out for today:

  • Import a worksheet in Python
  • Read data from it
  • Manipulate data

A Few Excel Definitions :

  • Workbook : An Excel Document is called a workbook . It has a .xlsx extension (or .xls if you’re using MS Office 2003 or .odp if you’re using LibreOffice or OpenOffice) . In this article , we’ll be mainly dealing with .xlsx workbooks
  • Worksheet : Each worksheet Contains multiple sheets called Worksheets. The sheet that a viewer is viewing is called an active worksheet.
  • Each sheet has many Columns and Rows .
  • A box at a particular row and column is called a cell. A cell can contain any value. A grid of cells makes up a sheet.

Import OpenPyXl Module in Python

By default , Python does not have the OpenPyXL module pre-installed . So you’ll need to install this module in order to work with spreadsheets in Python . You can download this module by reading its full documentation from here .

On a fresh install of Ubuntu or Debian or any other Debian based Linux OS , you’ll have to install the pip package manager first . To install pip , use the following command

sudo apt install python-pip

Then , to install the OpenPyXL module in Python , you need to use the following command :

pip install openpyxl

To check whether OpenPyXL was installed or not , use the following command after entering python in Terminal or any other Command Prompt:

>>> import openpyxl

Working with a Spreadsheet

This is how your spreadsheet will look like . I’m right now active on “Sheet3” of my workbook.

Now type a number , (eg. 30) into the cell A1 . Then type another number , (eg. 50) into the cell B1.

So your workbook in Sheet3 must look like :

Opening Excel Documents with OpenPyXl

Now navigate to a Terminal , enter python , which will open python inside the terminal. Now I assume you saved the file as example.xlsx in your user folder. Now enter these commands One by one :

>>> import openpyxl
>>> wb = openpyxl.load_workbook("example.xlsx")

Now , to check whether our worksheet example.xlsx (Or any other spreadsheet) was loaded , use this command

>>> type(wb)

And it should show this output :


Getting Spreadsheets from your workbook

>>> import openpyxl
>>> wb = openpyxl.load_workbook('example.xlsx')
>>> sheet = wb.get_sheet_by_name('Sheet3')

>>> sheet['A1']

>>> sheet['A1'].value

>>> a = sheet['A1']
>>> b = sheet['B1']
>>> c = a.value + b.value
>>> print c

So this is how I tackled the problem.

Okay , so thank you, everyone, for taking your precious time in reading my first blog..I’ll write again soon.


🎉 You've successfully subscribed to Sparker0i's Blog!