」工欲善其事,必先利其器。「—孔子《論語.錄靈公》
首頁 > 程式設計 > 如何使用 Python 和 Openpyxl 從 API 自動化(建立、更新)Excel 檔案。

如何使用 Python 和 Openpyxl 從 API 自動化(建立、更新)Excel 檔案。

發佈於2024-08-16
瀏覽:773

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如有侵犯,請聯絡study_golang@163 .com刪除
最新教學 更多>
  • 我可以將加密從McRypt遷移到OpenSSL,並使用OpenSSL遷移MCRYPT加密數據?
    我可以將加密從McRypt遷移到OpenSSL,並使用OpenSSL遷移MCRYPT加密數據?
    將我的加密庫從mcrypt升級到openssl 問題:是否可以將我的加密庫從McRypt升級到OpenSSL?如果是這樣,如何? 答案:是的,可以將您的Encryption庫從McRypt升級到OpenSSL。 可以使用openssl。 附加說明: [openssl_decrypt()函數要求...
    程式設計 發佈於2025-07-15
  • 在程序退出之前,我需要在C ++中明確刪除堆的堆分配嗎?
    在程序退出之前,我需要在C ++中明確刪除堆的堆分配嗎?
    在C中的顯式刪除 在C中的動態內存分配時,開發人員通常會想知道是否有必要在heap-procal extrable exit exit上進行手動調用“ delete”操作員,但開發人員通常會想知道是否需要手動調用“ delete”操作員。本文深入研究了這個主題。 在C主函數中,使用了動態分配變量(...
    程式設計 發佈於2025-07-15
  • FastAPI自定義404頁面創建指南
    FastAPI自定義404頁面創建指南
    response = await call_next(request) if response.status_code == 404: return RedirectResponse("https://fastapi.tiangolo.com") else: ...
    程式設計 發佈於2025-07-15
  • Python高效去除文本中HTML標籤方法
    Python高效去除文本中HTML標籤方法
    在Python中剝離HTML標籤,以獲取原始的文本表示Achieving Text-Only Extraction with Python's MLStripperTo streamline the stripping process, the Python standard librar...
    程式設計 發佈於2025-07-15
  • 反射動態實現Go接口用於RPC方法探索
    反射動態實現Go接口用於RPC方法探索
    在GO 使用反射來實現定義RPC式方法的界面。例如,考慮一個接口,例如:鍵入myService接口{ 登錄(用戶名,密碼字符串)(sessionId int,錯誤錯誤) helloworld(sessionid int)(hi String,錯誤錯誤) } 替代方案而不是依靠反射...
    程式設計 發佈於2025-07-15
  • 人臉檢測失敗原因及解決方案:Error -215
    人臉檢測失敗原因及解決方案:Error -215
    錯誤處理:解決“ error:((-215)!empty()in Function Multultiscale中的“ openCV 要解決此問題,必須確保提供給HAAR CASCADE XML文件的路徑有效。在提供的代碼片段中,級聯分類器裝有硬編碼路徑,這可能對您的系統不准確。相反,OPENCV提...
    程式設計 發佈於2025-07-15
  • 對象擬合:IE和Edge中的封面失敗,如何修復?
    對象擬合:IE和Edge中的封面失敗,如何修復?
    To resolve this issue, we employ a clever CSS solution that solves the problem:position: absolute;top: 50%;left: 50%;transform: translate(-50%, -50%)...
    程式設計 發佈於2025-07-15
  • Java是否允許多種返回類型:仔細研究通用方法?
    Java是否允許多種返回類型:仔細研究通用方法?
    在Java中的多個返回類型:一種誤解類型:在Java編程中揭示,在Java編程中,Peculiar方法簽名可能會出現,可能會出現,使開發人員陷入困境,使開發人員陷入困境。 getResult(string s); ,其中foo是自定義類。該方法聲明似乎擁有兩種返回類型:列表和E。但這確實是如此嗎...
    程式設計 發佈於2025-07-15
  • 為什麼PYTZ最初顯示出意外的時區偏移?
    為什麼PYTZ最初顯示出意外的時區偏移?
    與pytz 最初從pytz獲得特定的偏移。例如,亞洲/hong_kong最初顯示一個七個小時37分鐘的偏移: 差異源利用本地化將時區分配給日期,使用了適當的時區名稱和偏移量。但是,直接使用DateTime構造器分配時區不允許進行正確的調整。 example pytz.timezone(&#...
    程式設計 發佈於2025-07-15
  • 如何修復\“常規錯誤:2006 MySQL Server在插入數據時已經消失\”?
    如何修復\“常規錯誤:2006 MySQL Server在插入數據時已經消失\”?
    How to Resolve "General error: 2006 MySQL server has gone away" While Inserting RecordsIntroduction:Inserting data into a MySQL database can...
    程式設計 發佈於2025-07-15
  • 可以在純CS中將多個粘性元素彼此堆疊在一起嗎?
    可以在純CS中將多個粘性元素彼此堆疊在一起嗎?
    [2这里: https://webthemez.com/demo/sticky-multi-header-scroll/index.html </main> <section> { display:grid; grid-template-...
    程式設計 發佈於2025-07-15
  • PHP陣列鍵值異常:了解07和08的好奇情況
    PHP陣列鍵值異常:了解07和08的好奇情況
    PHP數組鍵值問題,使用07&08 在給定數月的數組中,鍵值07和08呈現令人困惑的行為時,就會出現一個不尋常的問題。運行print_r($月份)返回意外結果:鍵“ 07”丟失,而鍵“ 08”分配給了9月的值。 此問題源於PHP對領先零的解釋。當一個數字帶有0(例如07或08)的前綴時,PHP...
    程式設計 發佈於2025-07-15
  • Python中何時用"try"而非"if"檢測變量值?
    Python中何時用"try"而非"if"檢測變量值?
    使用“ try“ vs.” if”來測試python 在python中的變量值,在某些情況下,您可能需要在處理之前檢查變量是否具有值。在使用“如果”或“ try”構建體之間決定。 “ if” constructs result = function() 如果結果: 對於結果: ...
    程式設計 發佈於2025-07-15
  • 如何使用“ JSON”軟件包解析JSON陣列?
    如何使用“ JSON”軟件包解析JSON陣列?
    parsing JSON與JSON軟件包 QUALDALS:考慮以下go代碼:字符串 } func main(){ datajson:=`[“ 1”,“ 2”,“ 3”]`` arr:= jsontype {} 摘要:= = json.unmarshal([] byte(...
    程式設計 發佈於2025-07-15
  • Spark DataFrame添加常量列的妙招
    Spark DataFrame添加常量列的妙招
    在Spark Dataframe ,將常數列添加到Spark DataFrame,該列具有適用於所有行的任意值的Spark DataFrame,可以通過多種方式實現。使用文字值(SPARK 1.3)在嘗試提供直接值時,用於此問題時,旨在為此目的的column方法可能會導致錯誤。 df.withco...
    程式設計 發佈於2025-07-15

免責聲明: 提供的所有資源部分來自互聯網,如果有侵犯您的版權或其他權益,請說明詳細緣由並提供版權或權益證明然後發到郵箱:[email protected] 我們會在第一時間內為您處理。

Copyright© 2022 湘ICP备2022001581号-3