如何在Excel JS Office Add-In自定义函数中获取当前加载项位置

Great question! The pain point you're hitting is super common—desktop Excel's custom function runtime doesn't expose the browser's window object, so window.location won't work there. Luckily, Office JS has a built-in, cross-platform solution for this exact scenario. Let's break it down:

1. Use Office.addin.getFileUrl() (Recommended Approach) #

This is the official, reliable way to get your add-in's base URL across all platforms (web, desktop, Mac). It’s designed specifically to avoid browser/runtime dependencies like window.location.

Since custom functions are async by nature, you’ll need to use async/await to call this method:

// Helper function to get the full dialog URL dynamically
async function getDynamicDialogUrl() {
  // Get the URL of your add-in's main entry file (e.g., taskpane.html)
  const addinRootUrl = await Office.addin.getFileUrl();
  
  // Use the URL constructor to resolve your dialog's relative path
  // Replace "./dialogs/your-dialog.html" with your actual dialog path
  const dialogRelativePath = "./dialogs/your-dialog.html";
  const dialogFullUrl = new URL(dialogRelativePath, addinRootUrl).href;
  
  return dialogFullUrl;
}

// Example custom function that opens the dialog
async function OPENMYDIALOG() {
  try {
    const dialogUrl = await getDynamicDialogUrl();
    
    // Open the dialog using Office's UI API
    Office.context.ui.displayDialogAsync(dialogUrl, 
      { height: 40, width: 60 }, // Adjust dimensions as needed
      (result) => {
        if (result.status === Office.AsyncResultStatus.Succeeded) {
          // Handle successful dialog launch (e.g., listen for messages)
          const dialog = result.value;
          dialog.addEventHandler(Office.EventType.DialogMessageReceived, (args) => {
            console.log("Message from dialog:", args.message);
          });
        } else {
          throw new Error(`Failed to open dialog: ${result.error.message}`);
        }
      }
    );
    
    return "Dialog opened successfully!";
  } catch (error) {
    return `Error: ${error.message}`;
  }
}

Why this works: #

• Office.addin.getFileUrl() returns the absolute URL of your add-in's main file (the one defined in your manifest's SourceLocation), regardless of the runtime.
• The URL constructor safely resolves relative paths to the add-in's root, so you don't have to hardcode any domain or folder structure.

2. Alternative: Storing the Base URL from the Taskpane (Less Flexible) #

If you need a fallback (or if you're working with an older Office JS version), you can store the base URL in Office.context.settings when the taskpane loads, then retrieve it in your custom function. Note: This requires the taskpane to be opened at least once during the session.

// In your taskpane.js (run on initialization)
Office.onReady(async () => {
  const addinRootUrl = await Office.addin.getFileUrl();
  const baseUrl = new URL("./", addinRootUrl).href;
  
  // Store the base URL in settings
  Office.context.settings.set("addinBaseUrl", baseUrl);
  await Office.context.settings.saveAsync();
});

// In your custom function
async function getStoredBaseUrl() {
  return new Promise((resolve, reject) => {
    Office.context.settings.get("addinBaseUrl", (result) => {
      if (result.status === Office.AsyncResultStatus.Succeeded) {
        resolve(result.value);
      } else {
        reject(new Error("Could not retrieve base URL from settings"));
      }
    });
  });
}

Key Considerations #

• Office Version Support: Office.addin.getFileUrl() works in Office 2019+, Office 365, and all modern web clients. Make sure you're using the latest Office JS library (via CDN or npm).
• Async Requirement: Custom functions must be marked async to use await with Office JS async methods.
• Dialog Permissions: Ensure your manifest allows dialogs (the default setup usually includes this, but double-check the Permissions element if you run into issues).

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