Why data validation list throws argument out of range exception in WPF XlsIO?
This article explains why a data validation list throws an argument out of range exception in WPF XlsIO using C#/VB.NET.
What is a data validation list?
Data validation means allowing specific values in a cell in the worksheet based on given conditions. The data validation list contains the values that can be provided for a cell in a drop-down box. The values for the list can be given by selecting a range in the worksheet that contains values, or they can be provided directly in IDatavalidation.ListofValues by separating them using (,) operator.
When the values are given directly, MS Excel only allows values up to 255 characters in length, including the separators. XlsIO follows the same behavior as MS Excel, which results in throwing an “Argument out of range exception” when the values' character length exceeds 255. Therefore, we need to ensure the character length in IDatavalidation.ListOfValues is below 255.
To learn more about data validation in XlsIO, please refer the documentation.
The following C#/VB.NET complete code snippet demonstrates the scenario where data validation throws an argument out of range exception in XlsIO.
using Syncfusion.XlsIO; using System.IO; using System.Reflection; namespace XlsIO_Sample { class Program { public static void Main(string[] args) { // Instantiate the spreadsheet creation engine using (ExcelEngine excelEngine = new ExcelEngine()) { IApplication application = excelEngine.Excel; IWorkbook workbook = application.Workbooks.Create(1); IWorksheet worksheet = workbook.Worksheets[0]; worksheet["A1"].Text = "Select Country"; worksheet["B1"].CellStyle.ColorIndex = ExcelKnownColors.Grey_25_percent; // Adding data validation to the cell "B1" IDataValidation validation = worksheet["B1"].DataValidation; // Giving values to the data validation list directly. If one more character added, it will throw exception. validation.ListOfValues = new string[] { "Albania","Australia","Austria","Belgium","Bolivia","Canada","Chile","China","Colombia","Congo","Dubai","Egypt","England","Finland","France","Georgia","Germany","Greece","Haiti","India","Indonesia","Ireland","Japan","Malaysia","North Korea","Singapore","South Africa","South Korea","Sri Lanka","United States","Yemen"}; // Save and close the workbook Stream stream = File.Create("Output.xlsx"); worksheet.UsedRange.AutofitColumns(); workbook.SaveAs(stream); } } } }
Imports Syncfusion.XlsIO Imports System.IO Imports System.Reflection Namespace XlsIO_Sample Class Program Public Shared Sub Main(ByVal args As String()) 'Instantiate the spreadsheet creation engine Using excelEngine As ExcelEngine = New ExcelEngine() Dim application As IApplication = excelEngine.Excel Dim workbook As IWorkbook = application.Workbooks.Create(1) Dim worksheet As IWorksheet = workbook.Worksheets(0) worksheet("A1").Text = "Select Country" worksheet("B1").CellStyle.ColorIndex = ExcelKnownColors.Grey_25_percent 'Adding data validation to the cell "B1" Dim validation As IDataValidation = worksheet("B1").DataValidation 'Giving values to the data validation list directly.If one more character added, it will throw exception. validation.ListOfValues = New String() {"Albania", "Australia", "Austria", "Belgium", "Bolivia", "Canada", "Chile", "China", "Colombia", "Congo", "Dubai", "Egypt", "England", "Finland", "France", "Georgia", "Germany", "Greece", "Haiti", "India", "Indonesia", "Ireland", "Japan", "Malaysia", "North Korea", "Singapore", "South Africa", "South Korea", "Sri Lanka", "United States", "Yemen"} 'Save and close the workbook Dim stream As Stream = File.Create("Output.xlsx") worksheet.UsedRange.AutofitColumns() workbook.SaveAs(stream) End Using End Sub End Class End Namespace
Take a moment to peruse the documentation where you can find basic Excel document processing options along with the features like import and export data, chart, formulas, conditional formatting, data validation, tables, pivot tables and protect the Excel documents, and most importantly, the PDF, CSV and Image conversions with code examples.
Conclusion
I hope you enjoyed learning about why data validation list throws argument out of range exception in XlsIO.
You can refer to our XIsIO’s feature tour page to learn about its other groundbreaking features. Explore our UG documentation and online demos to understand how to manipulate data in Excel documents.
If you are an existing user, you can access our latest components from the License and Downloads page. For new users, you can try our 30-day free trial to check out XlsIO and other Syncfusion® components.
If you have any queries or require clarification, please let us know in the comments below or contact us through our support forums, Support Tickets, or feedback portal. We are always happy to assist you!