”工欲善其事,必先利其器。“—孔子《论语.录灵公》
首页 > 编程 > 如何使用 Python 和 Openpyxl 从 API 自动化(创建、更新)Excel 文件。

如何使用 Python 和 Openpyxl 从 API 自动化(创建、更新)Excel 文件。

发布于2024-08-16
浏览:914

How to Automate(create, update) Excel Files from APIs with Python and Openpyxl.

So I know that when automation is mentioned, a lot of people think of it most abstractly. perhaps even thinking of a mechanic shop for fixes. lol.
Anyway, automation in programming is exactly the code you write but with other techniques to help run it properly.

When I first started using Python, it was for writing data structures and algorithms but I later advanced to using it for other things like trying out my ML model development and then Python for programming.

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 = 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/

版本声明 本文转载于:https://dev.to/michellebuchiokonicha/how-to-automatecreate-update-excel-files-from-apis-with-python-and-openpyxl-2148?1如有侵犯,请联系[email protected]删除
最新教程 更多>
  • 如何使用 JSTL 迭代 HashMap 中的 ArrayList?
    如何使用 JSTL 迭代 HashMap 中的 ArrayList?
    使用 JSTL 迭代 HashMap 中的 ArrayList在 Web 开发中,JSTL(JavaServer Pages 标准标记库)提供了一组标记来简化 JSP 中的常见任务( Java 服务器页面)。其中一项任务是迭代数据结构。要迭代 HashMap 及其中包含的 ArrayList,可以使...
    编程 发布于2024-11-05
  • Encore.ts — 比 ElysiaJS 和 Hono 更快
    Encore.ts — 比 ElysiaJS 和 Hono 更快
    几个月前,我们发布了 Encore.ts — TypeScript 的开源后端框架。 由于已经有很多框架,我们想分享我们做出的一些不常见的设计决策以及它们如何带来卓越的性能数据。 性能基准 我们之前发布的基准测试显示 Encore.ts 比 Express 快 9 倍,比 Fasti...
    编程 发布于2024-11-05
  • 为什么使用 + 对字符串文字进行字符串连接失败?
    为什么使用 + 对字符串文字进行字符串连接失败?
    连接字符串文字与字符串在 C 中,运算符可用于连接字符串和字符串文字。但是,此功能存在限制,可能会导致混乱。在问题中,作者尝试连接字符串文字“Hello”、“,world”和“!”以两种不同的方式。第一个例子:const string hello = "Hello"; const...
    编程 发布于2024-11-05
  • React 重新渲染:最佳性能的最佳实践
    React 重新渲染:最佳性能的最佳实践
    React高效的渲染机制是其受欢迎的关键原因之一。然而,随着应用程序复杂性的增加,管理组件重新渲染对于优化性能变得至关重要。让我们探索优化 React 渲染行为并避免不必要的重新渲染的最佳实践。 1. 使用 React.memo() 作为函数式组件 React.memo() 是一个高...
    编程 发布于2024-11-05
  • 如何实现条件列创建:探索 Pandas DataFrame 中的 If-Elif-Else?
    如何实现条件列创建:探索 Pandas DataFrame 中的 If-Elif-Else?
    Creating a Conditional Column: If-Elif-Else in Pandas给定的问题要求将新列添加到 DataFrame 中基于一系列条件标准。挑战在于在实现这些条件的同时保持代码效率和可读性。使用函数应用程序的解决方案一种方法涉及创建一个将每一行映射到所需结果的函数...
    编程 发布于2024-11-05
  • 介绍邱!
    介绍邱!
    我很高兴地宣布发布 Qiu – 一个严肃的 SQL 查询运行器,旨在让原始 SQL 再次变得有趣。老实说,ORM 有其用武之地,但当您只想编写简单的 SQL 时,它们可能会有点让人不知所措。我一直很喜欢编写原始 SQL 查询,但我意识到我需要练习——大量的练习。这就是Qiu发挥作用的地方。 有了 Q...
    编程 发布于2024-11-05
  • 为什么 CSS 中的 Margin-Top 百分比是根据容器宽度计算的?
    为什么 CSS 中的 Margin-Top 百分比是根据容器宽度计算的?
    CSS 中的 margin-top 百分比计算当对元素应用 margin-top 百分比时,必须了解计算方式执行。与普遍的看法相反,边距顶部百分比是根据包含块的宽度而不是其高度来确定的。W3C 规范解释:根据W3C 规范,“百分比是根据生成的框包含块的宽度计算的。”此规则适用于“margin-top...
    编程 发布于2024-11-05
  • 如何解决 CSS 转换期间 Webkit 文本渲染不一致的问题?
    如何解决 CSS 转换期间 Webkit 文本渲染不一致的问题?
    解决 CSS 转换期间的 Webkit 文本渲染不一致在 CSS 转换期间,特别是缩放元素时,Webkit 中可能会出现文本渲染不一致的情况浏览器。这个问题源于浏览器尝试优化渲染性能。一种解决方案是通过添加以下属性来强制对过渡元素的父元素进行硬件加速:-webkit-transform: trans...
    编程 发布于2024-11-05
  • 使用 Reactables 简化 RxJS
    使用 Reactables 简化 RxJS
    介绍 RxJS 是一个功能强大的库,但众所周知,它的学习曲线很陡峭。 该库庞大的 API 界面,再加上向反应式编程的范式转变,可能会让新手不知所措。 我创建了 Reactables API 来简化 RxJS 的使用并简化开发人员对反应式编程的介绍。 例子 我们将构建...
    编程 发布于2024-11-05
  • 如何在 Pandas 中查找多列的最大值?
    如何在 Pandas 中查找多列的最大值?
    查找 Pandas 中多列的最大值要确定 pandas DataFrame 中多列的最大值,可以采用多种方法。以下是实现此目的的方法:对指定列使用 max() 函数此方法涉及显式选择所需的列并应用 max() 函数: df[["A", "B"]] df[[&q...
    编程 发布于2024-11-05
  • CI/CD 入门:自动化第一个管道的初学者指南(使用 Jenkins)
    CI/CD 入门:自动化第一个管道的初学者指南(使用 Jenkins)
    目录 介绍 什么是 CI/CD? 持续集成(CI) 持续交付(CD) 持续部署 CI/CD 的好处 更快的上市时间 提高代码质量 高效协作 提高自动化程度和一致性 如何创建您的第一个 CI/CD 管道 第 1 步:设置版本控制 (GitHub) 第 2 步:选择 CI/CD 工具 ...
    编程 发布于2024-11-05
  • TypeScript 如何使 JavaScript 在大型项目中更加可靠。
    TypeScript 如何使 JavaScript 在大型项目中更加可靠。
    介绍 JavaScript 广泛应用于 Web 开发,现在也被应用于不同行业的大型项目中。然而,随着这些项目的增长,管理 JavaScript 代码变得更加困难。数据类型不匹配、运行时意外错误以及代码不清晰等问题可能会导致查找和修复错误变得困难。 这就是TypeScript介入的地...
    编程 发布于2024-11-05
  • 如何使用PHP的password_verify函数安全地验证用户密码?
    如何使用PHP的password_verify函数安全地验证用户密码?
    使用 PHP 解密加密密码许多应用程序使用密码哈希等加密算法安全地存储用户密码。然而,在验证登录尝试时,将输入密码与加密的存储版本进行比较非常重要。加密问题password_hash 使用 Bcrypt,一种一元加密算法方式哈希算法,意味着加密的密码无法逆转或解密。这是一项安全功能,可确保即使数据库...
    编程 发布于2024-11-05
  • 学习 Vue 部分 构建天气应用程序
    学习 Vue 部分 构建天气应用程序
    深入研究 Vue.js 就像在 DIY 工具包中发现了一个新的最喜欢的工具——直观、灵活,而且功能强大得惊人。我接触 Vue 的第一个副业项目是一个天气应用程序,它教会了我很多关于框架功能以及一般 Web 开发的知识。这是我到目前为止所学到的。 1. Vue 入门:简单与强大 Vue...
    编程 发布于2024-11-05
  • NFT 预览卡组件
    NFT 预览卡组件
    ?刚刚完成了我的最新项目:使用 HTML 和 CSS 的“NFT 预览卡组件”! ?查看并探索 GitHub 上的代码。欢迎反馈! ? GitHub:[https://github.com/khanimran17/NFT-preview-card-component] ?现场演示:[https://...
    编程 发布于2024-11-05

免责声明: 提供的所有资源部分来自互联网,如果有侵犯您的版权或其他权益,请说明详细缘由并提供版权或权益证明然后发到邮箱:[email protected] 我们会第一时间内为您处理。

Copyright© 2022 湘ICP备2022001581号-3