Build Smarter Spreadsheets with Python

Build Smarter Spreadsheets with Python

Mary Mbaoma's photo
Mary Mbaoma
·

7 min read

Hi there, today's article shows us how Python makes spreadsheet creation and data manipulation more fun!

First up, we discuss Excel, a spreadsheet package. This is a Microsoft Office tool which can be used to display data in a spreadsheet format. It is made up of cells and it can be used to display data in a manner which can be easily understood. It also enables users to perform arithmetic operations and sorting with ease.

The key terminologies which would be used in this article are highlighted below:

  • Spreadsheet: this is the document we will create and work with. It is a file made of rows and columns that help organize data efficiently.
  • Column: this is marked with upper case letters in an orderly fashion such as 'A', 'B', etc and can be seen vertically on the spreadsheet.
  • Row: this is marked with numbers in ascending order and can be seen horizontally on the leftmost part of the spreadsheet.
  • Cell: this is an intersection of a column and row. They are called by joining the letter of the column and the number of the row we are referring to. For example: 'A1', 'B2'etc

Next up, Openpyxl; this is a Python library used in reading and writing excel files. It supports the xlsx and xlsm formats of excel files.

Our tutorial will consider creating a smart payroll in form of a spreadsheet; but before delving into this task, let us quickly see a use case of Openpyxl and Excel.

Baby Steps ....

  • First, we install openpyxl. 
pip install openpyxl
pip install pandas
  • Then, we create a spreadsheet
# import the openpyxl library
from openpyxl import Workbook

#create the spreadsheet
our_workbook = Workbook()
sheet_one = our_workbook.active

#add texts to the cells of the spreadsheet
sheet_one["A1"] = "Ganbare"
sheet_one["B1"] = "Tomodachi"

#save the file with the 'xlsx' extension
our_workbook.save(filename= "sheet_one.xlsx")
  • Run the code and watch our for an excel file which will be created in the same directory with the already assigned file name.

image tadaaa!!!

Up next Smart, the Payroll Dude!

  • First we create a spreadsheet (with some style formating)
# import the openpyxl library
from openpyxl import Workbook

#add stying to the sheet by importing the following libraries
from openpyxl.styles import NamedStyle, Font, Color, Border, colors, Side
from openpyxl.styles.differential import DifferentialStyle
from openpyxl.formatting.rule import Rule

#create the spreadsheet
our_workbook = Workbook()
sheet_one = our_workbook.active

#name the sheet
sheet_one.title = "Employee details"

#effect the styling
bold_font = Font(bold=True, color=colors.BLUE, size=10)
font_border = Border(bottom=Side(border_style='thin'))
sheet_one.freeze_panes = "A1"

#add titles to the spreadsheet
sheet_one["A1"] = "NAME"
sheet_one["A1"].font = bold_font

sheet_one["B1"] = "GROSS PAY (N)"
sheet_one["B1"].font = bold_font

sheet_one["C1"] = "TOTAL WITH-HOLDINGS (N)"
sheet_one["C1"].font = bold_font

sheet_one["D1"] = "NET AMOUNT PAYABLE (N)"
sheet_one["D1"].font = bold_font

#save the file with the 'xlsx' extension
our_workbook.save(filename= "sheet_one.xlsx")
  • Then, we add data to our spreadsheet and save our changes on the sheet.

image (Spreadsheet after some adjustments to the margins)

  • We write a script to work on the data on our spreadsheet. In this use case, we write a script similar to what we have below.
#this script reads data from an excel sheet and performs some calculations

#import 'load_workbook' from 'openpyxl' library
from openpyxl import load_workbook

#import pandas and DataFrame
from pandas import DataFrame 
import pandas as pd

#load the workbook containing data
old_workbook = load_workbook(filename="sheet_one.xlsx")
new_sheet = old_workbook.active

#read the file
filename = r"sheet_one.xlsx"
df = pd.read_excel(filename)

#get the user's input values from the columns in the spreadsheet as python lists
name = list(df["NAME"][0:])
gross_pay = list(df["GROSS PAY (N)"][0:])
with_holdings = list(df["TOTAL WITH-HOLDINGS (N)"][0:])
payable_amount = list(df["NET AMOUNT PAYABLE (N)"][0:])

#applying formulae to the values gotten from the spreadsheet
payable_amount = [(gross_pay[i] - with_holdings[i]) for i in range(len(gross_pay)) and range(len(with_holdings))]

#display the results of the above lists as excel columns
df = pd.DataFrame()

#to display final results gotten after performing calculations
df["NAME"] = name[::]
df["GROSS PAY (N)"] = gross_pay[::]
df["TOTAL WITH-HOLDINGS (N)"] = with_holdings[::]
df["NET AMOUNT PAYABLE (N)"] = payable_amount[::]


#then rewrite the values to a new sheet
df.to_excel("EmployeeSalary.xlsx", index=False)

In getting the user's inputs from the sheet, we grab values by specfying their cell names

We make use of list comprehension to subtract the total with-holdings amount from the gross pay

  • Run the script, then open the newly created excel file in your current directory with the assigned filename.

image (Spreadsheet after some adjustments to the margins)

Phew! that was a lot! Hopefully you're still with me.

Check out these reading resources for more information on the wonders of Openpyxl and Excel

Code snippets can be found on Github

Thanks @AkinwandeBolu for helping me edit this piece.

Feel free to reach out to me via twitter

Sayonara!

Pythonexcelautomation