Module 13 - Programming with Python

This repository contains a demo project created as part of my DevOps studies in the TechWorld with Nana – DevOps Bootcamp.

Demo Project: Automation with Python — Working with Spreadsheets

Technologies used: Python, PyCharm, Git

Project Description:

• Write an application that reads a spreadsheet file and process and manipulate the spreadsheet

---

Prerequisites

1. Install Python

Download from python.org/downloads, then verify the installation:

python3 --version

2. Install uv

curl -LsSf https://astral.sh/uv/install.sh | sh

# Verify
uv --version

3. Install an IDE: PyCharm

4. Create the virtual environment & install dependencies

uv sync

---

1. List each company with respective product count

from typing import cast
import openpyxl

inv_file = openpyxl.load_workbook('inventory.xlsx')
product_list = inv_file['Sheet1']

products_per_supplier = {}

for product_row in range(2, product_list.max_row + 1):
    supplier_name = cast(str, product_list.cell(product_row, 4).value)

    if supplier_name in products_per_supplier:
        current_num_products = products_per_supplier[supplier_name]
        products_per_supplier[supplier_name] = current_num_products + 1
    else:
        print("adding a new supplier")
        products_per_supplier[supplier_name] = 1

print(products_per_supplier)

2. List each company with respective total inventory value

from typing import cast
import openpyxl

inv_file = openpyxl.load_workbook('inventory.xlsx')
product_list = inv_file['Sheet1']

total_value_per_supplier = {}

for product_row in range(2, product_list.max_row + 1):
    supplier_name = cast(str, product_list.cell(product_row, 4).value)
    inventory = product_list.cell(product_row, 2).value
    price = product_list.cell(product_row, 3).value

    if supplier_name in total_value_per_supplier:
        current_total_value = total_value_per_supplier[supplier_name]
        total_value_per_supplier[supplier_name] = current_total_value + inventory * price
    else:
        total_value_per_supplier[supplier_name] = inventory * price

print(total_value_per_supplier)

3. List products with inventory less than 10

from typing import cast
import openpyxl

inv_file = openpyxl.load_workbook('inventory.xlsx')
product_list = inv_file['Sheet1']

products_under_10_inv = {}

for product_row in range(2, product_list.max_row + 1):
    inventory = cast(int, product_list.cell(product_row, 2).value)
    product_num = cast(str, product_list.cell(product_row, 1).value)

    # logic products with inventory less than 10
    if inventory < 10:
        products_under_10_inv[int(product_num)] = int(inventory)

print(products_under_10_inv)

4. Write to Spreadsheet: calculate and write inventory value for each product

from typing import cast
import openpyxl

inv_file = openpyxl.load_workbook('inventory.xlsx')
product_list = inv_file['Sheet1']

for product_row in range(2, product_list.max_row + 1):
    inventory = cast(int, product_list.cell(product_row, 2).value)
    price = cast(int, product_list.cell(product_row, 3).value)
    inventory_price = product_list.cell(product_row, 5)

    # add value for total inventory price
    inventory_price.value = inventory * price

inv_file.save('inventory_with_total_values.xlsx')