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 . 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
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'] <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.
Okay , so thank you, everyone, for taking your precious time in reading my first blog..I’ll write again soon.