Evaluate data validation and implement dropdown validation in Excel spreadsheet in .NET

Aspose.Cells Product Family
1

i have to fill and existing dropdown with selected value. dropdown control and its value will be given in dictionary format. how do i fill the dropdown with selected value and save the excel. this needs to be done in C#.
Also dropdown created from validation list. how to assign value to it from list ?

2

@snneha

Thanks for using Aspose APIs.

Please see the following sample code. It shows how to get the drop down list validation values and then use them. Please see the sample Excel file used in this code and the output Excel file generated by it. Also see the screenshot for better help.

For your other issues, please create one sample Excel file (like I have provided) manually using Microsoft Excel and provide it to us. We will look into it and help you out with Aspose.Cells APIs sample code.

Download Link:
Sample Input and Output Excel Files.zip (14.3 KB)

C#

//Load the source Excel file
Workbook wb = new Workbook("sampleExcel.xlsx");

//Access first worksheet
Worksheet ws = wb.Worksheets[0];

//Get all the drop down list validation values
object[] dropdownValues = ws.Validations[0].Value1 as object[];

//Put the values in cells - H1 to H6
ws.Cells["H1"].PutValue(dropdownValues[0]);
ws.Cells["H2"].PutValue(dropdownValues[1]);
ws.Cells["H3"].PutValue(dropdownValues[2]);
ws.Cells["H4"].PutValue(dropdownValues[3]);
ws.Cells["H5"].PutValue(dropdownValues[0]);
ws.Cells["H6"].PutValue(dropdownValues[1]);

//Save the output Excel file
wb.Save("output.xlsx");

Screenshot:

3

i will be getting key value pair.
eg dropdown control name ,“Orange”. and an empty excel form . i need to fill that form based on key value pairs.

if its validation list. how do i assign orange to the control. also for dropdown shape how it needs to be done.

4

@snneha

You can validate the values of the cell that should match validation list using the Cell.GetValidationValue(). If you will input some wrong value, it will return false and if you will input some right value it will return true.

Please see the following sample code, its comments and its console output for your reference.

C#

Workbook wb = new Workbook("sampleExcel.xlsx");

Worksheet ws = wb.Worksheets[0];

Cell h2 = ws.Cells["H2"];

//This is wrong value
h2.PutValue("Orange1");
Console.WriteLine("Does H2 has correct value: " + h2.GetValidationValue());

//This is correct value
h2.PutValue("Apple");
Console.WriteLine("Does H2 has correct value: " + h2.GetValidationValue());

//This is again correct value
h2.PutValue("Pear");
Console.WriteLine("Does H2 has correct value: " + h2.GetValidationValue());

//This is again wrong value
h2.PutValue("Pear1");
Console.WriteLine("Does H2 has correct value: " + h2.GetValidationValue());

Console Output

Does H2 has correct value: False
Does H2 has correct value: True
Does H2 has correct value: True
Does H2 has correct value: False

Reference Article:

Please also see this article relating to this topic for more help.

5

I get a form . i needtofill the validation list dropdown with the value sent by the client… I do following steps

  1. Get the validation control
  2. If the control is same as the control sent by the client. I putvalue.

To do all this i need to find the cell which contains this list validation. That i am not able to find.

Correct me if i am wrong.
Note: when i will get excel template. I will not be knowing which cell has this validation list.

6

@snneha

Thanks for considering Aspose APIs.

Please use Cell.GetValidation() method for your needs. If cell has validation, this method will return validation control, otherwise it will return null.

Please see the following code for a help.

C#

Cell c4 = worksheet.Cells["C4"];

if(c4.GetValidation()==null)
{
    Console.WriteLine("This cell does not have any validation");
}
else
{
    Validation validationControl = c4.GetValidation();
    Console.WriteLine("This cell does have validation");
}
7

GetValidation or getvalidationvalue not present in aspose.cell version 7.7. i can use this as per licence.
is there any workaround?

8

@snneha

Thanks for using Aspose APIs.

In this case, you will have to write your own method and algorithm which works like GetValidation(). It means, whenever you try to put some value, you compare it with array of values that are present inside the list validation values. You will do it one by one.

Suppose your list validation values are

A
B
C
D
E

and you put value

G

Then before you put value you will compare it with the list validation values one by one and you will find G does not exist in those values.