Python Automation

Intro

Automation is a core skill of most developers, we used it to automate repetitive tasks and to have a consistent way to complete tasks such as deployments. Python is the core programming language used for Automation, with its simple and intuitive syntax it is easy for anyone to pick up. A lot of this automation is focused on Excel and automating Excel data and with such an easy entry barrier more and more people are picking it up.

Example Excel file

Example Excel file in csv format we will work with, copy and put in a file called transactions.xlsx

transaction_id,product_id,price
1001,1,$5.95
1002,2,$6.95
1003,3,$7.95

Example Python Program

Install required dependencies

In the terminal run the following, in either the IDE or the OS terminal

$ pip install openpyxl

Create program file

• Using the IDE we will create a program file
• Create a file called app.py

The Application

• In this application we are looking to update all prices for a sale
• The sale will be 10% off
• We would also like to see a bar chart of the sale prices

import openpyxl as xl                          # Here we alias the openpyxl lib for easier use
from openpyxl.chart import BarChart, Reference

def process_sale_price(filename):
  wb = xl.load_workbook(filename)              # Here we load an Excel file by its filename
  sheet = wb['Sheet1']                         # This is how we access specific sheets
  
  cell = sheet['a1]                            # This is the first method for accessing cells
  print(cell)
  cell2 = sheet(1, 1)                          # This is another way to access cells within a sheet but is less idiomatic
  print(cell2.value)
  
  print(sheet.max_row)                         # Using max_row we can get the total number of rows in the sheet, useful for iteration
  
  for row in range(2, sheet.max_row + 1)       # We will start from row 2 as we do not want the headings
    print(row)
    cell_price = sheet.cell(row, 3) 
    sale_price = cell_price.value * 0.9
    cell_price_sale = sheet.cell(row, 4)
    cell_price_sale.value = cell_price_sale
  
  values = Reference(sheet,                     # Here we can gather a group of cells from within a document
                     min_row=2,
                     max_row=sheet.max_row, 
                     min_col=4, 
                     max_col=4
  )
  
  chart = BarChart()                            # We then create an instance of a Barchart
  chart.add_data(values)                        # Add the values to this instance
  sheet.add_chart(chart, 'e2')                  # Then place the barchart on the sheet
  
  wb.save(filename)                             # We can then save all the changes to the sheet we were working on

process_sale_price('transactions.xlsx')