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:
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
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 :
To check whether OpenPyXL was installed or not , use the following command after entering python in Terminal or any other Command Prompt:
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 :
Now , to check whether our worksheet example.xlsx (Or any other spreadsheet) was loaded , use this command
This should show <class 'openpyxl.workbook.workbook.Workbook'>
$ 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.
