Xceed Workbooks for .NET – Developer’s Guide

Xceed Workbooks for .NET lets you programmatically enforce data quality in Excel files by applying robust data validation rules no Office or Excel installation required. This guide details how to implement dropdown lists, numeric ranges, and custom validation logic, all from your .NET codebase.

Why Data Validation Matters

• • Prevents User Errors: Restricts input to valid options (e.g., dropdowns, value ranges).

• • Enhances Usability: Provides instant feedback to users filling out spreadsheets.

• • Ensures Data Integrity: Guarantees downstream processes receive clean, predictable data.

Getting Started

1. 1. Install the Component:
      Install-Package Xceed.Workbooks.NET

1. 1. Reference the Namespace:
      using Xceed.Workbooks;

1. 1. Activate the License:
      Set your license key as per onboarding instructions.

Example 1: Creating a Dropdown List (List Validation)

csharp

Copy code

var workbook = new Workbook();

var sheet = workbook.Worksheets.Add(“ValidationDemo”);

// Create a dropdown in cell A1 with allowed values

var validation = sheet.DataValidations.AddListValidation(“A1”);

validation.Formula.Values.Add(“Pending”);

validation.Formula.Values.Add(“Approved”);

validation.Formula.Values.Add(“Rejected”);

sheet.Cells[“A1”].Value = “Pending”;

workbook.SaveAs(“dropdown-demo.xlsx”);

Result:

Cell A1 now has a dropdown with “Pending,” “Approved,” and “Rejected” as selectable options.

Example 2: Enforcing Numeric Ranges

csharp

Copy code

var workbook = new Workbook();

var sheet = workbook.Worksheets.Add(“ValidationDemo”);

// Allow only values between 1 and 100 in cell B2

var validation = sheet.DataValidations.AddWholeNumberValidation(“B2”);

validation.Operator = DataValidationOperator.Between;

validation.Value1 = 1;

validation.Value2 = 100;

sheet.Cells[“B2”].Value = 50; // Valid

workbook.SaveAs(“range-demo.xlsx”);

Result:

Cell B2 only accepts integer values from 1 to 100.

Example 3: Custom Formula Validation

csharp

Copy code

var workbook = new Workbook();

var sheet = workbook.Worksheets.Add(“ValidationDemo”);

// Custom formula: Value in C3 must be greater than the value in B2

var validation = sheet.DataValidations.AddCustomValidation(“C3”);

validation.Formula.Formula = “=C3>B2”;

sheet.Cells[“B2”].Value = 10;

sheet.Cells[“C3”].Value = 12; // Valid

workbook.SaveAs(“custom-validation-demo.xlsx”);

Result:

Cell C3 must contain a value greater than B2.

Best Practices

• • Always define clear error messages for invalid input to guide users.

• • Use named ranges for validation formulas when working with complex sheets.

• • Batch apply validations for large tables using range notation (e.g., “A2:A100”).

Support & Additional Resources

• • Documentation Hub: https://xceed.com/support/

• • Trial & Licensing: https://xceed.com/trial/

Call to Action

Ready to enforce data quality in your .NET Excel solutions?

Start your free trial of Xceed Workbooks for .NET and access full data validation capabilities. For more advanced scenarios, visit our Documentation Hub or reach out to our support team.

FAQ

• • Q: Can I apply validation to entire columns or tables?
    A: Yes, use range notation (e.g., “A2:A100”) to apply rules to multiple cells at once.

• • Q: Are Excel’s built-in error messages supported?
    A: Yes, you can customize the error and input messages for each validation rule.

• • Q: Is validation enforced at runtime or only in Excel?
    A: Validation rules are embedded in the .xlsx file and enforced when opened in Excel.