Microsoft 365 自动化

Excel 不只是表格，SharePoint 不只是网盘——Power Automate 让它们活起来。

Microsoft 365 自动化全景

Power Automate 核心场景

"""
Power Automate 流程模板
"""
from dataclasses import dataclass
@dataclass
class PAFlow:
name: str
trigger: str
actions: list[str]
category: str
# 常用 Power Automate 流程模板
FLOW_TEMPLATES = [
PAFlow(
"审批流", "SharePoint: 新建列表项",
[
"获取列表项详情",
"发送审批请求给主管",
"根据结果更新列表项状态",
"通知申请人结果",
],
"流程审批"
),
PAFlow(
"邮件附件归档", "Outlook: 收到带附件邮件",
[
"获取附件",
"上传到 OneDrive/SharePoint",
"发送确认邮件",
"记录到 Excel",
],
"文档管理"
),
PAFlow(
"请假自动化", "Forms: 提交请假申请",
[
"获取表单数据",
"发审批给直属上级",
"更新 SharePoint 请假日历",
"Teams 通知团队",
],
"HR"
),
PAFlow(
"每周汇报", "定时: 每周五 16:00",
[
"查询 SharePoint 本周任务",
"生成汇总 Excel",
"发邮件给团队",
],
"报告"
),
]
print("=== Power Automate 流程模板 ===")
for flow in FLOW_TEMPLATES:
print(f"\n[{flow.category}] {flow.name}")
print(f"  触发: {flow.trigger}")
for i, action in enumerate(flow.actions, 1):
print(f"  {i}. {action}")

Excel 高级自动化

"""
Excel 数据处理自动化 (Python openpyxl 示例)
"""
from dataclasses import dataclass
@dataclass
class ExcelReport:
"""Excel 报表生成器"""
@staticmethod
def sales_formulas() -> dict:
"""常用 Excel 销售公式"""
return {
"同比增长": "=(B2-B14)/B14",
"环比增长": "=(B2-B1)/B1",
"达成率": "=B2/C2",
"累计": "=SUM(B$2:B2)",
"移动平均": "=AVERAGE(OFFSET(B2,-2,0,3,1))",
"条件求和": '=SUMIFS(Sales,Region,"华东",Month,">=2024-01")',
"VLOOKUP": "=VLOOKUP(A2,ProductDB!A:D,4,FALSE)",
"INDEX+MATCH": "=INDEX(C:C,MATCH(A2,A:A,0))",
"数据透视": "Power Query → 建模 → 透视",
}
@staticmethod
def python_excel_example() -> str:
"""Python 处理 Excel 代码模板"""
return '''
import openpyxl
from pathlib import Path
def process_report(filepath: str) -> dict:
"""处理 Excel 报表"""
wb = openpyxl.load_workbook(filepath)
ws = wb.active
data = []
for row in ws.iter_rows(min_row=2, values_only=True):
data.append({
"日期": row[0],
"产品": row[1],
"销量": row[2],
"金额": row[3],
})
# 数据汇总
total = sum(d["金额"] for d in data if d["金额"])
count = len(data)
# 写入汇总
summary_ws = wb.create_sheet("汇总")
summary_ws.append(["指标", "值"])
summary_ws.append(["总销售额", total])
summary_ws.append(["订单数", count])
summary_ws.append(["平均客单价", total/count if count else 0])
wb.save("output_report.xlsx")
return {"total": total, "count": count}
'''
report = ExcelReport()
print("=== Excel 常用公式 ===")
for name, formula in report.sales_formulas().items():
print(f"  {name}: {formula}")

SharePoint 自动化

Office Script 示例

# Office Script 是 Excel Web 版的 TypeScript 脚本
OFFICE_SCRIPT_EXAMPLE = """
// Office Script: 自动格式化报表
function main(workbook: ExcelScript.Workbook) {
let sheet = workbook.getActiveWorksheet();
let range = sheet.getUsedRange();
// 添加表头样式
let header = sheet.getRange("A1:E1");
header.getFormat().getFill().setColor("#4472C4");
header.getFormat().getFont().setColor("white");
header.getFormat().getFont().setBold(true);
// 自动调整列宽
range.getFormat().autofitColumns();
// 添加条件格式 — 负数标红
let dataRange = sheet.getRange("E2:E100");
let rule = dataRange.addConditionalFormat(
ExcelScript.ConditionalFormatType.cellValue
);
// 配置条件格式规则
}
"""
print("=== Office Script 示例 ===")
print(OFFICE_SCRIPT_EXAMPLE)

下一章：Google Workspace 自动化——Apps Script 打通 Google 生态的一切。