I'm evaluating your product.
We need to realize a conditional validation.
Example:
We have a first cell with a country name dropdown list and a second one who contains a city name dropdown list.
When the user chooses the country from the first cell, the second one dropdown list must show only allowed cities for selected country.
I've tried to realize it: the first cell dropdown works good, in the second one the dropdown button appears in the cells but list is empty; but if i go to Excel's "Data"->"Validations..." menu option and i click OK, it works.
Source code:
enum Lettera { A, B, C, D, E, F, G };
private static void Aspose_try()
{
string listSheetName = "liste";
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
Aspose.Cells.Worksheet liste = workbook.Worksheets.Add(listSheetName);
Aspose.Cells.Validations validations = workbook.Worksheets[0].Validations;
// experimenting conditional dropdown validation
Aspose.Cells.CellArea areaCond1;
areaCond1.StartRow = 7;
areaCond1.EndRow = 7;
areaCond1.StartColumn = 0;
areaCond1.EndColumn = 0;
Aspose.Cells.CellArea areaCond2;
areaCond2.StartRow = 7;
areaCond2.EndRow = 7;
areaCond2.StartColumn = 1;
areaCond2.EndColumn = 1;
// lists value inside second sheet
string[,] info = {{"europa","america nord","america sud"},
{"italia","usa", "cile"},
{"francia","canada" ,"perù"},
{"germania","alaska" ,"brasile"}};
int offset=1;
for (int i = 0; i < 4; i++)
{
for (int j = 0; j < 3; j++)
{
string s = info[i,j];
int actualrow = offset + i;
Lettera l = ((Lettera)Enum.ToObject(typeof(Lettera), j));
string cellname = l.ToString() + actualrow;
liste.Cells[cellname].PutValue(s);
}
}
// named range
Aspose.Cells.Range rs;
for (int i = 0; i < 3; i++)
{
string columnLetter = ((Lettera)Enum.ToObject(typeof(Lettera), i)).ToString();
rs = liste.Cells.CreateRange(columnLetter + "2", columnLetter + "4");
rs.Name = info[0,i].Replace(" ","_");
}
rs = workbook.Worksheets[1].Cells.CreateRange("A1", "C1");
rs.Name = "lists";
// conditional validation
Aspose.Cells.Validation conditionalValidation1 = validations[validations.Add()];
conditionalValidation1.Type = Aspose.Cells.ValidationType.List;
conditionalValidation1.Operator = Aspose.Cells.OperatorType.Between;
conditionalValidation1.Formula1 = "=lists";
conditionalValidation1.Formula2 = "";
string firstListCell = "A8";
Aspose.Cells.Validation conditionalValidation2 = validations[validations.Add()];
conditionalValidation2.Type = Aspose.Cells.ValidationType.List;
conditionalValidation2.Operator = Aspose.Cells.OperatorType.Between;
conditionalValidation2.Formula1 = "=INDIRECT(SUBSTITUTE("+firstListCell+",\" \",\"_\"))";
conditionalValidation2.Formula2 = "";
conditionalValidation1.AreaList.Add(areaCond1);
conditionalValidation2.AreaList.Add(areaCond2);
//saving
workbook.Save("c:\\experiment-aspose.xls");
}