Monthly Archives: December 2018

Python and Excel

- - Python, Tutorials

I intend to host a set of examples on using python to interact and work with excel files. This article in particular will use openpyxl module in python throughout the examples.

Installing openpyxl

I am using python3 throughout the examples, however it should work similarly with python2. As a good development practice, we should segregate dependencies between various projects and therefore is advised to use virtual environment or some sort.

python3 -m venv excelenv
source excelenv/bin/activate
pip install openpyxl

Open an existing document in python
>>> import openpyxl
>>> wb = openpyxl.load_workbook('example.xlsx')
>>> wb
<openpyxl.workbook.workbook.Workbook object at 0x7f010dce4240>
Selecting sheets from the workbook/document
>>> wb.get_sheet_names()
>>> sheet = wb.get_sheet_by_name("Sheet1")
>>> sheet
<Worksheet "Sheet1">
>>> sheet.max_column
>>> sheet.max_row
>>> sheet.min_row
>>> sheet.min_column
>>> sheet['A1'].value
>>> sheet['B1'].value
>>> sheet['A2'].value
>>> sheet['B2'].value
When the cell being accessed doesn’t have a value
>>> a = sheet['A4'].value
>>> a
>>> type(a)
<class 'NoneType'>

OpenPyXL will automatically interpret the types of the values in the cells of the sheet and return them as an object of that type. string , int, dates, etc.

>>> type(sheet['A3'].value)
<class 'str'>
>>> type(sheet['B3'].value)
<class 'int'>
Accessing cell values using row, column directive

Although we can access the values of the cell using alphabetic letters directive, we can also access them using row number and column numbers. An excel row and column starts at 1, not 0.

>>> sheet.cell(row=0, column=0)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/bhishan-1504/excelinpython/excelenv/lib/python3.6/site-packages/openpyxl/worksheet/", line 296, in cell
    raise ValueError("Row or column values must be at least 1")
ValueError: Row or column values must be at least 1

>>> sheet.cell(row=1, column=1)
<Cell 'Sheet1'.A1>
>>> sheet.cell(row=1, column=1).value
Reading values from excel sheet
>>> wb = openpyxl.load_workbook('example.xlsx')
>>> sheet = wb.get_sheet_by_name("Sheet1")
>>> for each_row in range(sheet.min_row, sheet.max_row + 1):
...     for each_col in range(sheet.min_column, sheet.max_column + 1):
...         print(sheet.cell(row=each_row, column=each_col).value)
Creating excel document
>>> import openpyxl
>>> wb = openpyxl.Workbook()
>>> wb.get_sheet_names()
>>> sheet = wb.get_sheet_by_name('Sheet')
>>> sheet
<Worksheet "Sheet">
>>> sheet.title = 'Custom Created worksheet'
>>> wb.get_sheet_names()
['Custom Created worksheet']
>>> sheet
<Worksheet "Custom Created worksheet">
>>> sheet['A1'] = ''
>>> sheet['A1'].value
>>> sheet.append(['', '102948']) # adding rows to sheet

Going serverless with Chalice and AWS lambda

- - Python, Tutorials

The intentions of this post is to host a simple example of chalice from AWS that allows serverless API creation with the use of AWS lambda. You also get auto-generation of IAM policy making it faster to deploy web applications. Chalice expects to pick the AWS credentials from ~/.aws/config

Prerequisites for chalice[AWS Credentials]

If you’ve used AWS API or boto/boto3 for python, you’ve probably already added the credentials in place. Otherwise you could do it as below:

mkdir ~/.aws
touch ~/.aws/config

Contents of the config file

aws_access_key_id = YOUR_ACCESS_KEY_HERE
aws_secret_access_key = YOUR_SECRET_ACCESS_KEY
region = YOUR_REGION

Installing Chalice

As a good development practice, we should segregate dependencies between various projects and therefore is advised to use virtual environment or some sort.

$ python3 -m venv serverlessenv
$ source serverlessenv/bin/activate

(serverlessenv)$ pip install chalice

Creating simple API with chalice

Chalice comes with command line tool. See what commands are available

(serverlessenv)$ chalice

Usage: chalice [OPTIONS] COMMAND [ARGS]...

--version Show the version and exit.
--project-dir TEXT The project directory. Defaults to CWD
--debug / --no-debug Print debug logs to stderr.
--help Show this message and exit.

generate-pipeline Generate a cloudformation template for a...
invoke Invoke the deployed lambda function NAME.

(serverlessenv)$ chalice new-project

___ _ _ _ _ ___ ___ ___
/ __|| || | /_\ | | |_ _|/ __|| __|
| (__ | __ | / _ \ | |__ | || (__ | _|
\___||_||_|/_/ \_\|____||___|\___||___|

The python serverless microframework for AWS allows
you to quickly create and deploy applications using
Amazon API Gateway and AWS Lambda.

Please enter the project name: basichelloworld

(serverlessenv)$ cd basichelloworld/

(serverlessenv)~/basichelloworld$ ls -a .chalice .gitignore requirements.txt

Chalice is very simple, somewhat similar to Flask if you come from there.

from chalice import Chalice

app = Chalice(app_name='basichelloworld')

def index():
    return {'hello': 'world'}

# The view function above will return {"hello": "world"}
# whenever you make an HTTP GET request to '/'.
# Here are a few more examples:
# @app.route('/hello/{name}')
# def hello_name(name):
#    # '/hello/james' -> {"hello": "james"}
#    return {'hello': name}
# @app.route('/users', methods=['POST'])
# def create_user():
#     # This is the JSON body the user sent in their POST request.
#     user_as_json = app.current_request.json_body
#     # We'll echo the json body back to the user in a 'user' key.
#     return {'user': user_as_json}
# See the README documentation for more examples.
Running the API locally

(serverlessenv)~/basichelloworld$ chalice local
Serving on

(serverlessenv)~/basichelloworld$ curl -X GET
{"hello": "world"}

Going serverless with chalice through AWS lambda

(serverlessenv)~/basichelloworld$ chalice deploy
Creating deployment package.
Updating policy for IAM role: basichelloworld
Creating lambda function: basichelloworld
Creating Rest API
Resources deployed:
- Lambda ARN: arn:aws:lambda:us-east-1:9582857991:function:basichelloworld
- Rest API URL:

(serverlessenv)~/basichelloworld$ curl -X GET
{"hello": "world"}