如何无需打开工作簿批量刷新Excel Power Queries数据？

核心需求回顾 #

数十个依赖Power Query从SharePoint拉取数据的Excel工作簿，需实现夜间自动批量刷新、等待刷新完成后保存、无需终端用户操作，解决此前VBA/Power Automate因未等待刷新完成导致的不稳定问题。

一、Excel Primary Interop Assembly (PIA) #

最可控的本地/服务器端方案，精准处理刷新等待逻辑。

• 操作步骤：
  1. 在专用服务器上安装对应版本的Excel及PIA组件
  2. 编写C#/VB.NET代码，实例化后台Excel应用，批量打开目标工作簿
  3. 遍历工作簿内所有Power Query连接，调用刷新并循环等待状态完成
  4. 全部刷新完成后保存、关闭工作簿，退出Excel应用
• 关键代码示例：

  using Microsoft.Office.Interop.Excel;
  using System;
  
  class PowerQueryRefresh
  {
      static void Main(string[] args)
      {
          Application excelApp = new Application 
          { 
              Visible = false, 
              DisplayAlerts = false,
              EnableEvents = false
          };
  
          // 批量遍历工作簿路径列表
          string[] workbookPaths = new string[] 
          { 
              @"\\SharePoint\Site\Docs\Workbook1.xlsx",
              @"\\SharePoint\Site\Docs\Workbook2.xlsx"
          };
  
          foreach (string path in workbookPaths)
          {
              Workbook wb = excelApp.Workbooks.Open(path);
              foreach (Query q in wb.Queries)
              {
                  q.Refresh();
                  // 循环等待刷新完成
                  while (q.Refreshing)
                  {
                      System.Threading.Thread.Sleep(1000);
                  }
              }
              wb.Save();
              wb.Close(false);
              System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
          }
  
          excelApp.Quit();
          System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
      }
  }
  

• 优缺点：
  • 优势：完全掌控刷新流程，确保等待完成后再保存，稳定性拉满；可结合Windows任务计划实现夜间定时执行
  • 劣势：需服务器安装Excel，存在COM对象泄漏风险（需严格释放资源）；依赖客户端环境版本

二、Office Scripts + Power Automate #

适配云端SharePoint Online环境，解决Power Automate原有的等待问题。

• 操作步骤：
  1. 在Excel Online中打开目标工作簿，创建Office Script编写刷新逻辑

  function main(workbook: ExcelScript.Workbook) {
      const queries = workbook.getQueries();
      // 逐个刷新并等待完成
      for (const query of queries) {
          query.refresh();
          while (query.getIsRefreshing()) {
              ExcelScript.sleep(1000);
          }
      }
      workbook.save();
  }
  

  2. 在Power Automate中创建定时流，设置每日夜间触发，批量遍历所有目标工作簿并调用对应Office Script
• 优缺点：
  • 优势：云端执行无需本地环境；Office Script原生支持等待刷新状态，彻底解决之前的不稳定问题
  • 劣势：仅支持Excel Online/SharePoint Online托管的工作簿；需配置对应工作簿的编辑权限

三、Office Online Server (OOS) + PowerShell #

适合已部署OOS的企业，利用服务器端后台处理能力。

• 操作步骤：
  1. 确认OOS与SharePoint集成完成
  2. 编写PowerShell脚本，调用OOS的REST API触发工作簿刷新，通过任务ID轮询状态直到完成
  3. 用Windows任务计划或SharePoint计时器作业调度夜间批量执行
• 优缺点：
  • 优势：无需本地安装Excel，服务器端处理更稳定；适配企业内部SharePoint环境
  • 劣势：OOS部署维护成本高；API调用逻辑相对复杂

四、Office Data Connection (ODC) 文件 #

集中管理连接，结合SharePoint计时器作业触发刷新。

• 操作步骤：
  1. 将所有Power Query连接导出为ODC文件，存储到SharePoint数据连接库
  2. 配置ODC文件的刷新属性，启用"后台刷新"及"刷新完成后自动保存"
  3. 通过SharePoint计时器作业批量触发关联工作簿的刷新
• 优缺点：
  • 优势：集中管理数据源连接，便于后续修改；无需打开工作簿即可触发
  • 劣势：刷新状态监控难度大，无法精准确认所有任务完成；仅适用于基于ODC的连接

方案选择建议 #

• 追求最高稳定性且有服务器资源：优先选Excel PIA
• 纯云端SharePoint Online环境：优先选Office Scripts + Power Automate
• 已部署OOS的企业：可考虑OOS + PowerShell

内容的提问来源于stack exchange，提问作者Ben Bennett