Validation / List

Aspose.Cells Product Family
1

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

2

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.

3

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

Thanks a lot!!

Regards.

4

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

5
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.

6

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

7

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.