Why data validation list throws argument out of range exception in XlsIO?
This article explains why data validation list throws argument out of range exception in WPF XlsIO using C#/VB.NET.
What is data validation list?
Data validation means allowing values to the cell in the worksheet based on given conditions. The data validation list contains the values that can be given for a cell in a drop-down box. The values for the list can be given by selecting a range in the worksheet which contains values or it can be given directly in IDatavalidation.ListofValues by separating them using (,) operator.
When the values are given directly, MS-Excel only allow values up to 255 characters length including the separator. XlsIO follows the same behavior as MS-Excel which results in throwing “Argument out of range exception” when the values character length more than 255. So, we need to ensure the characters length in the IDatavalidation.ListofValues is below 255.
To know more about data validation in XlsIO, please refer the documentation.
The following C#/VB.NET complete code snippet shows the scenario where data validation throws 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
Conclusion
I hope you enjoyed learning about why data validation list throws argument out of range exception in XlsIO.
You can refer to our WPF XlsIO’s feature tour page to know about its other groundbreaking feature representations.
For current customers, you can check out our WPF from the License and Downloads page. If you are new to Syncfusion, you can try our 30-day free trial to check out our WPF XIsIO and other WPF components.
If you have any queries or require clarifications, please let us know in comments below. You can also contact us through our support forums, Direct-Trac, or feedback portal. We are always happy to assist you!
