`
Workbook workbook = new Workbook(@“C:\Users\test\Desktop\sampleExcel.xlsx”);
Worksheet worksheet = workbook.Worksheets[0];
Cells cells= workbook.Worksheets[0].Cells;
var a = cells.GetCell(2, 7).Value;
Validation v = cell.GetValidation();` - This piece of code is not supported - I dont see GetValidation() method existing .
Please help me to also set the value of drop down line to select “Orange” if it is in drop down
Thanks in advance
Thanks for your query.
Well, you may try to use Cell.GetValidation method to get the validation of the cell. Alternatively, you may try to use ValidationCollection.GetValidationInCell() method for your needs. To set a value, you may directly set the value to the cell (But you need to evaluate by yourself using your own code it is from drop down values list prior inserting). See the following sample code with the template file (attached) for your reference:
e.g
Sample code:
//Instantiate the workbook from template Excel file
Workbook workbook = new Workbook("e:\\test2\\Bk_datavalidationlist1.xlsx");
//Access its first worksheet
Worksheet worksheet = workbook.Worksheets[0];
//It can take only the values Between greater than 1
Cell cell = worksheet.Cells["A1"];
//Access the valditation applied on this cell and read all the dropdown list (validation) value.
Validation validation = cell.GetValidation();
if (validation.Type == ValidationType.List)
{
StringBuilder sbuf = new StringBuilder();
object[] itemArray = (object[])validation.Value1;
foreach (object item in itemArray)
{
sbuf.Append(item);
sbuf.AppendLine();
}
Console.WriteLine(sbuf.ToString());
}
/* Alternatively you may try
var validationForA1 = worksheet.Validations.GetValidationInCell(0, 0);
if (validationForA1.Type == ValidationType.List)
{
StringBuilder sbuf = new StringBuilder();
object[] itemArray = (object[])validationForA1.Value1;
foreach (object item in itemArray)
{
sbuf.Append(item);
sbuf.AppendLine();
}
Console.WriteLine(sbuf.ToString());
}
//*/
//Also Read various properties of the validation
Console.WriteLine("Reading Properties of Validation");
Console.WriteLine("--------------------------------");
Console.WriteLine("Type: " + validation.Type);
Console.WriteLine("Operator: " + validation.Operator);
Console.WriteLine("Formula1: " + validation.Formula1);
Console.WriteLine("Formula2: " + validation.Formula2);
Console.WriteLine("Ignore blank: " + validation.IgnoreBlank);
cell.PutValue("nein");//Set the value.
workbook.Save("e:\\test2\\out1.xlsx");
files1.zip (5.8 KB)