Hello Everyone ,

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 codecademy.com .
The very next week , we were given a task to be completed . 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-get 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)

This should show <class 'openpyxl.workbook.workbook.Workbook'>

Getting Spreadsheets from your Workbook

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

>>> sheet['A1']
<Cell Sheet1.A1>

>>> sheet['A1'].value
30

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

So this is how I tackled the problem .


In a nutshell

These are the commands I entered in the python shell , one after the other (won't be writing the output here) :

>>> import openpyxl 
>>> wb = openpyxl.load_workbook("example.xlsx")
>>> sheet = wb.get_sheet_by_name('Sheet3')
>>> a = sheet['A1']
>>> b = sheet['B1']
>>> c = a.value + b.value
>>> print c

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