I want a picklist of say several hundred items that I want in Excel.
ListDataValidationRule dvr = new ListDataValidationRule();
foreach(string val in ls)
{
if (lsV != "")
lsV += ",";
lsV += val;
}
dvr.SetValues(lsV);
sheet.Rows[iRow+1].Cells[iCell].DataValidationRule = dvr;
----
Code works fine until I hit around 18 items and the string length exceeds 250. I also tried ls.ToArray() but it always blows up with too many items in my validation list.
Is there an example to create a validation list with a lot of items???
I was going off of the example in http://www.infragistics.com/samples/windows-forms/infragistics-excel/excel-data-validation. Can't seem to get it to work with more than 18 items in my list (works fine with < 18).
Hi,
Thank you for posting in our forums.
The ListDataValidation rule is indeed limited to 255 symbols (including the commas). This is because in Excel itself you can only define a DataValidation List with no more than 255 symbols. Since Excel doesn’t support DataValidation Lists longer than 255 characters, our engine also can’t support it.
Please let me know if you have any additional questions.
Is there an example of another way of doing this with Named Formulas or something - I think you can use a table and then link in the table rather than a list (aka table is the list and not the string)?
Just wondering...
Thank you for the reply.
You could use regions for this goal. What you can do is to create a hidden sheet and add the values that you want in this sheet. Then use the SetValuesFormula method to set your DataValidationRule to this region. To create and hide the sheet you will need to use the following code:
var valuesSheet = book.Worksheets.Add("Values");
valuesSheet.DisplayOptions.Visibility = WorksheetVisibility.Hidden;
Then set the formula the following way:
for (int i = 0; i < ls.Count; i++)
valuesSheet.Rows[0].Cells[i].Value = ls[i];
dvr.SetValuesFormula("=Values!$A$1:$Q$1", null);
sheet.Rows[0].Cells[0].DataValidationRule = dvr;
Note that you will need to calculate what your region will be based on the number of values you have.
Works, thanks