所以我知道一提到自動化，很多人想到的都是最抽象的。也許甚至考慮去機械店修理。哈哈。
無論如何，程式設計中的自動化就是您編寫的程式碼，但使用其他技術來幫助其正確運作。
當我第一次開始使用 Python 時，它是用於編寫資料結構和演算法，但後來我進一步將其用於其他用途，例如嘗試我的 ML 模型開發，然後使用 Python 進行程式設計。
在本文中，我將提供有關如何在 MacBook 上自動化 Excel 檔案和不同工作表的逐步指南，而不使用 Visual Basic for 應用程式。
首先，要開始使用，您不需要是 Python 開發人員，因為我將在此處貼上程式碼片段。
我們將考慮的不同面向：
使用 openpyxl 在 Python 中建立 Excel 工作表非常簡單。
如果您要從 API 取得數據，您所需要做的就是安裝 openpyxl、pandas 和 requests。
前往 openpyxl 文檔，以了解如何將其匯入到您的應用程式以及您想要使用的套件中。
import pandas import requests from openpyxl import Workbook, load_workbook from openpyxl.utils import get_column_letter
接下來，
您建立了一個新工作簿
將其設定為活動工作簿
新增標題和標題並填入資料
使用您喜歡的 Excel 名稱和 tada 儲存新工作簿！
您已經建立了第一個 Excel 檔案。
# 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')
在 Excel 檔案中建立特定工作表是類似的過程。但是，您需要指定要使用工作表名稱建立的工作表。
# create sheet
wb.create_sheet('Test')
print(wb.sheetnames)
要修改 Excel 工作表而不是整個文件，
載入要修改的工作簿
他們使用其名稱或索引指定要修改的特定工作表。使用索引更安全，以防名稱最終會變更。
在下面的程式碼片段中，我使用了 Sheet 標籤
# 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
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')
要使用 Python 合併 Excel 中的不同儲存格，
載入工作簿
指示活動工作簿
指示您要合併的儲存格
# Merging excel cells
wb = load_workbook('mimi.xlsx')
ws = wb.active
ws.merge_cells("A1:D2")
wb.save("mimi.xlsx")
要使用 python 取消合併 Excel 中的不同儲存格，
載入工作簿
指示活動工作簿
指示您要取消合併的儲存格
# merging excel cells
wb = load_workbook('mimi.xlsx')
ws = wb.active
ws.unmerge_cells("A1:D1")
wb.save("mimi.xlsx")
插入新單元格
載入工作簿
指示活動工作簿
使用 insert_rows 和 insert_columns 根據偏好插入新行或新列。
# 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")
使用內部資料更新現有 Excel 檔案
添加您的數組和物件並獲取所需的資訊
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")
使用 Python 和 API 更新現有 Excel 檔案
要使用 Python 和 API 更新 Excel 文件，您需要使用 Get 要求將 API 呼叫到您的文件中。
如上所述設定活動 Excel 文件，然後執行腳本。
這是一個例子：
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]
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.
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
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, direct Python codes can suffice. you can also use the script and run it as required.
The major issue lies in the fact that VBA is a Windows language and hence, has limited functions in a non-windows device.
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:
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/
以上是如何使用 Python 和 Openpyxl 從 API 自動化（建立、更新）Excel 檔案。的詳細內容。更多資訊請關注PHP中文網其他相關文章！
