Office JS API Beta(1.6)入门：Excel下拉数据验证代码示例求助

Hey there, glad to help with this beta feature for your testing environment! Below is a complete, ready-to-use code example that sets up dropdown list data validation on a target Excel range using the Office JS Beta API:

async function addDropdownValidation() {
  try {
    await Excel.run(async (context) => {
      // Target a specific range (replace with your desired range, e.g., "B2:B15")
      const targetRange = context.workbook.worksheets.getActiveWorksheet().getRange("A1:A10");
      // Alternatively, use the user's selected range: context.workbook.getSelectedRange();

      // Define the dropdown options users can choose from
      const dropdownOptions = ["Q1", "Q2", "Q3", "Q4", "FY"];

      // Configure the data validation rule
      const dataValidation = targetRange.dataValidation;
      dataValidation.rule = {
        type: Excel.DataValidationType.list,
        list: {
          inCellDropDown: true,
          source: dropdownOptions
        },
        // Optional: Set up an error alert for invalid input
        errorAlert: {
          style: Excel.DataValidationAlertStyle.stop,
          title: "Invalid Input",
          message: "Please select an option from the dropdown list only.",
          showAlert: true
        },
        // Optional: Add a prompt to guide users
        prompt: {
          title: "Select a Period",
          message: "Choose an option from the dropdown menu.",
          showPrompt: true
        }
      };

      await context.sync();
      console.log("Dropdown data validation applied successfully!");
    });
  } catch (error) {
    console.error("Error setting up validation:", error);
    if (error instanceof OfficeExtension.Error) {
      console.error("Debug details:", error.debugInfo);
    }
  }
}

Important Beta Usage Tips: #

• Ensure your add-in loads the beta version of the Office JS library: use the script tag <script src="https://appsforoffice.microsoft.com/lib/beta/hosted/office.js"></script> in your project.
• The list.source can also accept a range reference (e.g., "Sheet2!$C$1:$C$5") if you want dropdown options pulled from existing cells instead of a hardcoded array.
• Test this in a non-production environment only, as beta features are subject to change before full release.

Just trigger the addDropdownValidation() function (e.g., from a button click in your add-in UI) to apply the dropdown to your chosen range.

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