CellValidation does not work correctly with INDIRECT function

Aspose.Cells Product Family
1

Hi,

We have Excel documents where we have dependent drop-downs to allows the user to select form the first and that impacts the values for the second, When I try and read in the drop-down list of values, the cellValidation.Values1 array ONLY contains the first value from the Range, not the full list of values:

Attached is an example project and file illustrating the problem. The console output from running this is:

Cell B1 DD values:
Pizza
IHOP
Chinese
Cell D1 DD values:
Pancakces
Cell D3 DD values:
Pancakces
Combos
French_Toast
Waffles

The D1 values list should match D3, but does not. Is this a defect? Is there another way to do this? Am I missing something?

The attached solution will need the Aspose.Cells.dll reference added. We’re using 8.7.1.0.

Thanks,
Andy.

2

Hi,


Thanks for providing us sample project and template file.

After an initial test, I observed the issue as you mentioned by using your sample project with your template file. I found that cell’s data validation does not work correctly with INDIRECT function as you pointed out. The D1 values list should match D3, but does not. I used the following sample code with your template file:
e.g
Sample code:

…
try
{
String excelFile = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location) + @"…\Data\TestFile03.xlsx";
Workbook workbook = new Workbook(excelFile);
Worksheet wipWorksheet = workbook.Worksheets[0];

// Dispaly the first drop-down’s values:
displayDropDownValues(wipWorksheet, “B1”);

// Set the drop-down value…
wipWorksheet.Cells[“B1”].Value = “IHOP”;

// Now display the second drop-down values list (it uses INDIRECT/Ranges)
displayDropDownValues(wipWorksheet, “D1”);

// Now disply what the list should be, just using the range directly.
displayDropDownValues(wipWorksheet, “D3”);

Console.ReadLine();
}
catch (Exception ex)
{
}


private static void displayDropDownValues(Worksheet wipWorksheet, string Cell)
{
Cell wipCell = wipWorksheet.Cells[Cell];
Validation cellValidation = wipCell.GetValidation();
//OR
//Validation cellValidation = wipWorksheet.Validations.GetValidationInCell(wipCell.Row, wipCell.Column);
Console.WriteLine("Cell " + Cell + " DD values: “);
string[] ddData = ((System.Collections.IEnumerable)cellValidation.Value1).Cast()
.Select(x => x.ToString())
.ToArray();
foreach (String val in ddData)
{
Console.WriteLine(” " + val);
}

}
…

Output:
Cell B1 DD values:
Pizza
IHOP
Chinese
Cell D1 DD values:
Pancakces //Some values are missing here.
Cell D3 DD values:
Pancakces
Combos
French_Toast
Waffles

I have logged a ticket with an id “CELLSNET-44505” for your issue. We will look it soon.

Once we have an update on it, we will let you know here.

Thank you.
3

Hi,

Thanks for using Aspose.Cells.

This is to inform you that we have fixed your issue CELLSNET-44505 now. We will soon provide the fix after performing QA and including other enhancements and fixes.

4
Hi,

Please try our latest version/fix: Aspose.Cells for .NET (Latest Version)

It should fix your issue now.

Let us know your feedback.

Thank you.
5

Hi,

Thanks for the quick fix! Yes, I’ve downloaded that build and the code works. Do you know when this will be officially released and combined with the full Total release package? We use some of the otehr DLL’s and prefer to update them all at once.

Thanks,
Andy

6

Hi Andy,


We are scheduled to release our next official version of the product, i.e., Aspose.Cells for .NET v8.8.3 within a couple of weeks. When we publish our next official version of the product in Downloads section it will be automatically added to Aspose.Total package. Moreover, you will be notified once the major release is available for your testing.
7

The issues you have found earlier (filed as CELLSNET-44505) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.