Free Support Forum - aspose.com

Validation / List

Hello,
i’m currently evaluating Aspose.Cells.
It seems to solve a lot of issues i have to face. But there is still one i can’t solve by now :

I’ve imported a list of countries in a column of my worksheet and i’m trying to apply a validation rule that would force the values of an area to be included in the list of coutries e.g. i want a dropdown list :).

Some methods seem to allow this… here is a part of my code (assume the countries are in “H” colmun)

validation.Type = ValidationType.List;
validation.InCellDropDown = true;
validation.Operator = OperatorType.Between;//?????
validation.Formula1 = "$H$0"; //????
validation.Formula2 = "$H$25";//?????

then I add the validation to a specific area...

Where am I wrong??

Regards
Cyril

Hi,

Thanks for considering Aspose.

Kindly refer to the following code which I write for your need. The code creates a validation list based on a cells range (H1:H4) data and applies the list to A1:A4 cells only:

Workbook workbook = new Workbook();

Worksheet worksheet1 = workbook.Worksheets[0];

Range range = worksheet1.Cells.CreateRange("H1", "H4");

range.Name = "MyRange";

range[0,0].PutValue("USA");

range[1,0].PutValue("UK");

range[2,0].PutValue("France");

range[3,0].PutValue("China");

Validations validations = worksheet1.Validations;

Validation validation = validations[validations.Add()];

validation.Type = Aspose.Cells.ValidationType.List;

validation.Operator = OperatorType.None;

validation.InCellDropDown = true;

validation.Formula1 = "=MyRange";

validation.ShowError = true;

validation.AlertStyle = ValidationAlertType.Stop;

validation.ErrorTitle = "Error";

validation.ErrorMessage = "Please select a valid country from the list";

CellArea area;

area.StartRow = 0;

area.EndRow = 3;

area.StartColumn = 0;

area.EndColumn = 0;

validation.AreaList.Add(area);

workbook.Save("d:\\tstvalidationlist1.xls");

Thank you.

That is exactly what i need...
And that works perfectly...

Thanks a lot!!

Regards.

I have created the list.. Now I need to open and access the list. How can I do this. I have tried like this

System.Collections.ArrayList a= m.Validations[0].AreaList;

But its returning the null list. But the formula is returning correctly

string s1 = m.Validations[0].Formula1.ToString(); //Output: D3:D7

I need to acceess the list of values in the cell.

Thanks for your help.

Thanks,

Bhuvana

Hi,

Which version of Aspose.Cells for .Net you are using. Please try the latest version (4.4.1). I tested it using a template file with the following code and it works fine.

Workbook workbook = new Workbook();
workbook.Open("d:\\test\\tstvalidlist.xls");
Worksheet worksheet1 = workbook.Worksheets[0];
ArrayList al = worksheet1.Validations[0].AreaList;
CellArea ca;
Cells cells = worksheet1.Cells;
int frow=0,fcol=0,erow=0,ecol=0;
for (int i = 0;i< al.Count;i++)
{
ca = new CellArea();
ca = (CellArea)al[i];
frow = ca.StartRow;
fcol = ca.StartColumn;
erow = ca.EndRow;
ecol = ca.EndColumn;



}

string firstareacell = cells[frow,fcol].Name;
.
.
.

Thank you.

Hi,

Thanks for the update.

Actually I want to access the list of values in the cell. Consider one validation cell.. The cell[A1] contains list of values[1, 2,3]. I need to access these values.

Regards,

Bhuvana

Hi Bhuvana,

Thanks for considering Aspose.

For obtaining list of values you may get the source range of cells and get the values:

e.g.,

//Create a range based on the source range formula1.

Range range = worksheet1.Cells.CreateRange("H1", "H3");

range.Name = "MyRange";

string first = range[0,0].StringValue;

string second = range[1,0].StringValue;

string third = range[2,0].StringValue;

.

.

.

Thank you.