Articles in this section
Category / Section

Why data validation list throws argument out of range exception in XlsIO?

4 mins read

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.

Download Complete sample

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!

 

 

 

 

 

 

 

 

 

Did you find this information helpful?
Yes
No
Help us improve this page
Please provide feedback or comments
Comments (0)
Please  to leave a comment
Access denied
Access denied