How to Automate(create, update) Excel Files from APIs with Python and Openpyxl.
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 be providing a step-by-step guide on how I automated an Excel file, and different sheets on a MacBook, without the use of visual basic for applications.
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:
Power query from web feature
Power automate
Visual Basic in Excel
Follow me on Twitter Handle: https://twitter.com/mchelleOkonicha
Follow me on LinkedIn Handle: https://www.linkedin.com/in/buchi-michelle-okonicha-0a3b2b194/
Follow me on Instagram: https://www.instagram.com/michelle_okonicha/