SharePoint文档库数据同步Excel及宏启用工作簿运行问题咨询

Hey there, let's break down your two questions one by one—they're both common scenarios when working with SharePoint and Excel, so I've got you covered.

1. Fetching Data from SharePoint Document Library & Writing to Excel via Excel Services REST API #

To pull data from a SharePoint document library and write it to an Excel worksheet using the Excel Services REST API, follow these structured steps:

Step 1: Locate the target Excel workbook #

First, you need to get the path or unique ID of your .xlsm file in the document library. Use the SharePoint REST API to query the library:

GET /_api/web/lists/getbytitle('YourLibraryName')/items?$select=FileRef,FileLeafRef&$filter=FileLeafRef eq 'YourWorkbook.xlsm'

This returns the FileRef (full relative path to the file) which you'll use in subsequent API calls.

Step 2: Authenticate your requests #

Ensure your code uses valid authentication:

• For SharePoint Online: Use Azure AD OAuth tokens or PnP JS (simplifies auth in SPFx/JavaScript projects).
• For SharePoint On-Premises: Use NTLM/Kerberos authentication (common in .NET or PowerShell scripts).

Step 3: Get the workbook's session & worksheet details #

Call the Excel Services API to retrieve the workbook model, which includes worksheet names and range info:

GET /_vti_bin/ExcelRest.aspx{FileRef}/model

Extract the target worksheet name (e.g., Sheet1) from the response JSON.

Step 4: Write data to the worksheet #

Send a POST request to update a specific range in the worksheet. Example request:

• URL: /_vti_bin/ExcelRest.aspx{FileRef}/model/Ranges('Sheet1!A1:C3')/values
• Headers: Content-Type: application/json
• Body:

  {
    "values": [
      ["Country Name", "ISO Code", "Region"],
      ["United States", "US", "North America"],
      ["Germany", "DE", "Europe"]
    ]
  }
  

Example: JavaScript (SPFx with PnP JS) #

Here's a quick snippet to implement this in an SPFx web part:

import { sp } from "@pnp/sp";

async function writeCountryDataToExcel() {
  // Fetch the workbook from the library
  const libraryName = "CountryDataLibrary";
  const workbookName = "CountryData.xlsm";
  const workbookItem = await sp.web.lists.getByTitle(libraryName)
    .items.filter(`FileLeafRef eq '${workbookName}'`)
    .select("FileRef")
    .get();

  if (workbookItem.length === 0) {
    console.error("Workbook not found!");
    return;
  }

  const fileUrl = workbookItem[0].FileRef;
  const excelRestUrl = `${_spPageContextInfo.webAbsoluteUrl}/_vti_bin/ExcelRest.aspx${fileUrl}/model/Ranges('Sheet1!A1:C3')/values`;

  // Prepare your data
  const countryData = {
    values: [
      ["Country Name", "ISO Code", "Region"],
      ["Canada", "CA", "North America"],
      ["Japan", "JP", "Asia"]
    ]
  };

  // Send the write request
  try {
    await sp.web.fetch(excelRestUrl, {
      method: "POST",
      headers: {
        "Content-Type": "application/json",
        "Accept": "application/json"
      },
      body: JSON.stringify(countryData)
    });
    console.log("Data successfully written to Excel!");
  } catch (error) {
    console.error("Error writing data:", error);
  }
}

2. Can Macro-Enabled Excel Workbooks (.xlsm) Stored in SharePoint Run Macros? #

The ability to run macros depends on your SharePoint environment (Online vs. On-Premises) and Excel's trust settings:

SharePoint Online (Modern Sites) #

• By default, when you download an .xlsm file from a modern SharePoint site, Excel marks it as "from the internet" and disables macros automatically. Users will see a security warning and need to click Enable Content to run macros.
• To skip manual enablement, add the SharePoint document library URL to Excel's Trusted Locations:
  1. Open Excel > File > Options > Trust Center > Trust Center Settings > Trusted Locations
  2. Click Add new location, paste the library URL, and check "Trust subfolders of this location"
• Note: Macros cannot run in Excel Online (browser-based editing)—they only work when the file is opened in the desktop Excel client.

SharePoint Server (On-Premises) #

• You can configure the document library as a trusted location at the site level, allowing macros to run automatically when users open the file in desktop Excel.
• Additionally, adjust Excel's macro security level (File > Options > Trust Center > Trust Center Settings > Macro Settings) to allow macros (e.g., "Enable all macros" or "Enable macros from trusted publishers").

Key Caveats #

• Always ensure macros are from a trusted source—enabling macros from untrusted files poses security risks.
• Classic SharePoint sites have more flexibility with macro trust settings compared to modern sites, but the core behavior (desktop-only execution) remains the same.

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