所以我知道一提到自动化，很多人想到的都是最抽象的。也许甚至考虑去机械店修理。哈哈。
无论如何，编程中的自动化就是您编写的代码，但使用其他技术来帮助其正确运行。
当我第一次开始使用 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中文网其他相关文章！
