Michellebuchiokonicha

Posted on Aug 16 • Edited on Aug 20

How to Automate Excel Files from APIs with Python and Openpyxl.

#python #softwaredevelopment #automation #excel

Automation in programming is the use of programming languages like Python, VBA, and other technology tools to create programs, scripts, or various tools that perform automatic tasks with no manual intervention.
The sole purpose of automating tasks is to avoid manual input and to ensure various systems run by themselves.

For this article, I will provide a step-by-step guide on how I automated an Excel file, and different sheets on a MacBook, without visual basic for applications, using Python in this case.

First of all, to get started, you don't need to be a Python dev as I will paste a code snippet here.

Tools Required

VScode of course
Python installed/updated
A virtual environment to run any new installation or updates for your Python code.
The virtual environment is the .venv. You will see it in your vscode.
Install openpyxyl
Install any other necessary dependency.
Get started.

The Different Aspects we will be considering:

Creating a new Excel file with python
Updating an existing Excel file with python Updating a specific Excel file sheet only with Python
Using APIs to update Excel files and Excel file sheets.
Creating a button that allows users to update on click.
Adding dynamic dates and time in your code
An alternative to the Excel button is cron or Windows shell
Instead of VBA, what else is possible?
Issues faced with writing VBA in a MacBook
Issues I faced while creating the button
Why I opted for cron
Creating this for both Windows and Mac users
Other tools that can be used for the automation of Excel
Power query from web feature
Power automate
Visual Basic in Excel

Creating a new Excel file with python

Creating an Excel sheet in Python with openpyxl is easy.
All you need to do is install openpyxl, pandas, and requests if you are getting data from an API.
Go to the openpyxl documentation to learn how to import it into your application and the packages you want to use.

import pandas
import requests
from openpyxl import Workbook, load_workbook
from openpyxl.utils import get_column_letter

Next up,
you create a new workbook
Set it as the active workbook
Add your title and header and populate the data
Save the new workbook with your preferred Excel name and tada!
you have created your first Excel file.

# create a new workbook
wb = Workbook()
ws = wb.active
ws.title = "Data"

ws.append(['Tim', 'Is', 'Great', '!'])
ws.append(['Sam', 'Is', 'Great', '!'])
ws.append(['John', 'Is', 'Great', '!'])
ws.append(['Mimi', 'Is', 'Great', '!'])
wb.save('mimi.xlsx')

Creating a new sheet in an Excel file.

Creating a specific sheet in your Excel file is a similar process. however, you need to specify the sheet to be created with a sheetname.

# create sheet
wb.create_sheet('Test')
print(wb.sheetnames)

Modifying an Excel sheet.

To modify an Excel sheet and not the full file,

Load the workbook you want to modify
They specify the particular sheet to modify using its name or index. It is safer to use the index in case the name eventually changes.
In the code snippet below, I used the Sheet label

# wb = load_workbook('mimi.xlsx')

# modify sheet
ws = wb.active
ws['A1'].value = "Test"
print(ws['A1'].value)
wb.save('mimi.xlsx')

Accessing multiple cells

To access multiple cells,
Load the workbook
Make it the active workbook
loop through its rows and columns

# Accessing multiple cells
 wb = load_workbook('mimi.xlsx')
 ws = wb.active

 for row in range(1, 11):
     for col in range(1, 5):
         char = get_column_letter(col)
         ws[char + str(row)] = char + str(row)
         print(ws[char + str(row)].value)

 wb.save('mimi.xlsx')

Merging Excel cells

To merge different cells in Excel using Python,
Load the workbook
Indicate the active workbook
indicate the cells you want to merge

# Merging excel cells
wb = load_workbook('mimi.xlsx')
ws = wb.active

ws.merge_cells("A1:D2")
wb.save("mimi.xlsx")

Unmerging cells

To unmerge different cells in Excel using python,
Load the workbook
Indicate the active workbook
indicate the cells you want to unmerge

# merging excel cells
wb = load_workbook('mimi.xlsx')
ws = wb.active

ws.unmerge_cells("A1:D1")
wb.save("mimi.xlsx")

Inserting new excel cells

To insert new cells

Load the workbook
Indicate the active workbook
use the insert_rows and insert_columns to insert new rows or new columns based on preference.

# inserting cells
wb = load_workbook('mimi.xlsx')
ws = wb. is active

ws.insert_rows(7)
ws.insert_rows(7)

ws.move_range("C1:D11", rows=2, cols=2)
wb.save("mimi.xlsx")

Updating an existing Excel file with internal Data
Add your arrays and objects and take in the information needed

from openpyxl import Workbook, load_workbook
from openpyxl.utils import get_column_letter
from openpyxl.styles import Font

data = {
    "Pam" : {
        "math":65,
        "science": 78,
        "english": 98,
        "gym": 89
    },
    "Mimi" : {
        "math":55,
        "science": 72,
        "english": 88,
        "gym": 77
    },
    "Sid" : {
        "math":100,
        "science": 66,
        "english": 93,
        "gym": 74
    },
    "Love" : {
        "math":77,
        "science": 83,
        "english": 59,
        "gym": 91
    },
}

wb = Workbook()
ws = wb.active
ws.title = "Mock"
headings = ['Name'] + list(data['Joe'].keys())
ws.append(headings)

for a person in data:
    grades = list(data[person].values())
    ws.append([person] + grades)

for col in range(2, len(data['Pam']) + 2):
    char = get_column_letter(col)
    ws[char + '7'] = f"=SUM({char + '2'}:{char + '6'})/{len(data)}"

for col in range(1, 6):
    ws[get_column_letter(col) + '1'].font = Font(bold=True, color="0099CCFF")


wb.save("NewMock.xlsx")

Updating an existing Excel file with Python and APIs

To update an Excel file using Python and APIs, you need to call the APIs into your file using a Get request.
Set the active Excel file as described above and then you run your script.
Here is an example of this:

from openpyxl import Workbook, load_workbook
import requests
from datetime import datetime, timedelta

import schedule
import time

api_url = "https://yourapi"
excel_file = "yourfilename.xlsx"

def fetch_energy_data(offset=0):
    response = requests.get(api_url + f"&offset={offset}")
    data = response.json()

    if response.status_code == 200:
        data = response.json()
        return data["results"], data["total_count"] 
    else:
        print(f"Error fetching data: {response.status_code}")
        return [], 0

def update_excel_data(data):
    try:
        wb = load_workbook(excel_file)
        ws = wb.worksheets[0]  

        for row in range(5, ws.max_row + 1):  
            for col in range(1, 9):  
                ws.cell(row=row, column=col).value = None  

                now = datetime.now()
                current_year = now.year
                current_month = now.month

        start_date = datetime(current_year,current_month, 1) 
        end_date = datetime(current_year, current_month, 24) 

        filtered_data = [
            result
            for result in data
            if start_date <= datetime.fromisoformat(result["datetime"]).replace(tzinfo=None) <= end_date]


        for i, result in enumerate(filtered_data):  
            row = i + 5  
            ws[f"A{row}"] = result["datetime"]
            ws[f"B{row}"] = result["yourinfo"]
            ws[f"C{row}"] = result["yourinfo"]
            ws[f"D{row}"] = result["yourinfo"]
            ws[f"E{row}"] = result["yourinfo"]
            ws[f"F{row}"] = result["yourinfo"]  
            ws[f"G{row}"] = result["yourinfo"]
            ws[f"H{row}"] = result["yourinfo"]

        for row in range(5, ws.max_row + 1):
            ws[f"I{row}"] = ws[f"I{row}"].value  
            ws[f"J{row}"] = ws[f"J{row}"].value  
            ws[f"K{row}"] = ws[f"K{row}"].value  
            ws[f"L{row}"] = ws[f"L{row}"].value  

        wb.save(excel_file)
        print(f"Excel file updated: {excel_file}")
    except FileNotFoundError:
        print(f"Excel file not found: {excel_file}")
    except KeyError:
        print(f"Sheet 'Forecast PV' not found in the Excel file.")
    schedule.every().hour.do(update_excel_data)

    while True:
             schedule.run_pending()

if __name__ == "__main__":
    all_data = []
    offset = 0
    total_count = 0
    while True:
        data, total_count = fetch_energy_data(offset)  
        if not data:
            break
        all_data.extend(data)
        offset += 100  
        if offset >= total_count:  
            break


    update_excel_data(all_data)


To update a particular sheet, use the method mentioned above. best practices are done with the excel sheets index number from 0 till n-1.
as sheet names can change but sheet positions can not change.

 wb = load_workbook(excel_file)
        ws = wb.worksheets[0]

Creating a button that allows users to update on click. To achieve a button to automatically run your Python script, you need to create a button in your Excel file and write a program using the inbuilt programming language, Visual Basic for applications. Next, you write a program similar to this. An example of a VBA script is below.

Sub RunPythonScript()
    Dim shell As Object
    Dim pythonExe As String
    Dim scriptPath As String
    Dim command As String

     Path to your Python executable
    pythonExe = "C:\Path\To\Python\python.exe"

     Path to your Python script
    scriptPath = "C:\Path\To\Your\Script\script.py"

     Command to run the Python script
    command = pythonExe & " " & scriptPath

     Create a Shell object and run the command
    Set shell = CreateObject("WScript.Shell")
    shell.Run command, 1, True

     Clean up
    Set shell = Nothing
End Sub

the issue with this is some functions do not run in non-windows applications seeing that Excel and VBA are built and managed by Microsoft, there are inbuilt Windows functions for this that can only work on Windows.

However, if you are not writing a very complicated program, it will run properly.

Adding dynamic dates and time in your code

To achieve dynamic dates and times, you can use the date.now function built into Python.

now = datetime.now()
 current_year = now.year
current_month = now.month

An alternative to the Excel button is cron or Windows shell

For MacBook users, an alternative to the VBA and button feature, you can use a corn for MacBook and a Windows shell for Windows. to automate your task.

You can also make use of Google Clouds's scheduler. that allows you to automate tasks.

Instead of VBA, what else is possible?

Instead of VBA, direct Python codes can suffice. you can also use the script and run it as required.

Issues faced while writing VBA in a MacBook

The major issue lies in the fact that VBA is a Windows language and hence, has limited functions in a non-windows device.

Issues I faced while creating the button

The same issues are related to the VBA code.

Why I opted for cron
I opted for corn because it is available and easy to use to achieve the goals.
Other tools that can be used for the automation of Excel

Other tools include: