Office-JS侧边栏插件能否调用Excel-DNA注册的UDF？

Great question! The short answer is yes—you absolutely can make your Office.js sidebar add-in call UDFs registered by an Excel-DNA add-in, even though there’s no direct official API for plugin-to-plugin communication. Let’s walk through the practical approaches to make your workflow work.

Approach 1: Write the UDF Formula Directly to Cells #

This is the most straightforward method since Excel-DNA UDFs act just like native Excel functions once registered. Here’s how it maps to your workflow:

1. User opens your Office.js sidebar → Your sidebar loads as normal.
2. User fills out the form → Capture the input values in your sidebar’s JavaScript.
3. Office-JS calls the Excel-DNA UDF → Use Office.js to write the UDF formula (with the user’s inputs as arguments) to a target cell or range.
4. UDF returns an array and displays in Excel → Excel will automatically evaluate the formula and render the array result (use array formulas if your UDF returns multi-dimensional data).

Code Example (Office.js):

async function runDNAAddInUDF() {
  // Capture user input from your sidebar form fields
  const userInput1 = document.getElementById("input1").value;
  const userInput2 = document.getElementById("input2").value;

  await Excel.run(async (context) => {
    const activeSheet = context.workbook.worksheets.getActiveWorksheet();
    // Target range (adjust based on your UDF's expected array size)
    const targetRange = activeSheet.getRange("A1:C3");
    
    // Write the Excel-DNA UDF as an array formula
    targetRange.formulaArray = `=MyDNAAddInUDF("${userInput1}", ${userInput2})`;
    
    await context.sync();
  }).catch(error => {
    console.error("Error executing UDF:", error);
    // Add user-friendly error message in your sidebar here
  });
}

Approach 2: Call the UDF via Application.Run (Background Execution) #

If you don’t want to display the formula in cells and prefer to fetch the UDF result programmatically first, you can use Excel’s Application.Run method—Office.js exposes this via context.workbook.application.run. This works because Excel-DNA UDFs can be invoked as macros if configured correctly.

Note for Excel-DNA Setup:
For your UDF to be callable via Application.Run, mark it as macro-compatible in your Excel-DNA code. In C#, add the IsMacroType=true attribute:

[ExcelFunction(Name = "MyDNAAddInUDF", IsMacroType = true)]
public static object MyDNAAddInUDF(string input1, int input2) {
  // Your UDF logic returning an array
  return new object[,] { { "Result 1", input2 }, { "Result 2", input1 } };
}

Office.js Code Example:

async function fetchDNAAddInUDFResult() {
  const userInput1 = document.getElementById("input1").value;
  const userInput2 = parseInt(document.getElementById("input2").value);

  await Excel.run(async (context) => {
    // Call the Excel-DNA UDF directly via Application.Run
    const udfResult = context.workbook.application.run("MyDNAAddInUDF", userInput1, userInput2);
    udfResult.load("value");
    
    await context.sync();

    // Dynamically resize the target range to match the UDF's array output
    const activeSheet = context.workbook.worksheets.getActiveWorksheet();
    const targetRange = activeSheet.getRange("A1").getResizedRange(
      udfResult.value.length - 1, 
      udfResult.value[0].length - 1
    );
    targetRange.values = udfResult.value;
    
    await context.sync();
  }).catch(error => {
    console.error("Error fetching UDF result:", error);
  });
}

Key Considerations #

• Permissions: Ensure your Office.js add-in has the WriteDocument scope (configured in your manifest) to modify cells and call Application.Run.
• Array Handling: When fetching results via Application.Run, dynamically resize your target range to match the dimensions of the UDF’s array output to avoid data truncation.
• Error Handling: Add error catching in both your Office.js code and Excel-DNA UDF to handle invalid inputs, calculation failures, or missing dependencies.
• Compatibility: Test across Excel versions—both Office.js and Excel-DNA support modern Excel (2016+), but older versions may have limited functionality.

This should fully enable your desired workflow. Feel free to tweak the code to match your specific UDF arguments and UI setup!

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