List Validation with Tables (ListObject) and Names Issue

Aspose.Cells Product Family
1
Hi, I'm attempting to emulate this type of behavior (http://excelsemipro.com/2011/05/a-dynamic-dependent-drop-down-list-in-excel/) through Aspose, but I'm running into a snag I cannot identify. When I do this manually [within Excel] it works fine, but when I go through Aspose.Cells, the file gets "repaired" on load. I've uploaded what the expected result is, and have pasted the code to generate it below. It seems that a Name referencing a ListObject breaks that List Object.

Any help would be greatly appreciated.

Sample Code:

var workbook = new Workbook();
workbook.Worksheets.RemoveAt(0);

var listSheet = workbook.Worksheets.Add("ListSheet");
var mainSheet = workbook.Worksheets.Add("MainSheet");

var names = listSheet.Workbook.Worksheets.Names;

// Populate list data

listSheet.Cells["A1"].Value = "TableA";
listSheet.Cells["A2"].Value = "a";
listSheet.Cells["A3"].Value = "b";
listSheet.Cells["A4"].Value = "c";
listSheet.Cells["B1"].Value = "TableB";
listSheet.Cells["B2"].Value = "a";
listSheet.Cells["B3"].Value = "xa";
listSheet.Cells["B4"].Value = "ya";
listSheet.Cells["B5"].Value = "za";
listSheet.Cells["C2"].Value = "b";
listSheet.Cells["C3"].Value = "xb";
listSheet.Cells["C4"].Value = "yb";
listSheet.Cells["D2"].Value = "c";
listSheet.Cells["D3"].Value = "xc";

// Contains the field Column1 with values {a, b, c}
const string listName = "TableA";
var listIndex = listSheet.ListObjects.Add("A2", "A4", false);
listSheet.ListObjects[listIndex].DisplayName = listName;

// Contains the fields {a, b, c} with values {[xa, ya, za], [xb, yb], [xc]}
const string listName2 = "TableB";
var listRange2 = listSheet.ListObjects.Add("B2", "D5", true);
listSheet.ListObjects[listRange2].DisplayName = listName2;

var validations = mainSheet.Validations;

// Add the list to the first box

var validation2 = validations[validations.Add()];
validation2.Type = ValidationType.List;
validation2.Formula1 = "=INDIRECT(\"TableA[Column1]\")";

CellArea area2;
area2.StartRow = 0;
area2.EndRow = 999;

area2.StartColumn = 0;
area2.EndColumn = 0;

validation2.AreaList.Add(area2);
#region Comment out and file loads fine

const string listRangeName = "ListRange" + listName2;
var listRange = names.Add(listRangeName);

//=INDEX(TableB,0,MATCH(MainSheet!A1,TableB[#Headers],0))
names[listRange].RefersTo = "=INDEX(" + listName2 + ",0,MATCH(" + mainSheet.Name + "!A1," + listName2 + "[#Headers],0))";

const string trimmedRangeName = "ListRangeTrimmed" + listName2;
var trimmedRange = names.Add(trimmedRangeName);

//=OFFSET(ListRangeTableB,0,0,COUNTA(ListRangeTableB),1)
names[trimmedRange].RefersTo = "=OFFSET(" + listRangeName + ",0,0,COUNTA(" + listRangeName + "),1)";

var validation = validations[validations.Add()];
validation.Type = ValidationType.List;
validation.Formula1 = "=" + trimmedRangeName;

// Apply Validation list to the second column
CellArea area;
area.StartRow = 0;
area.EndRow = 999;

area.StartColumn = 1;
area.EndColumn = 1;

validation.AreaList.Add(area);

#endregion
workbook.Save("Test" + DateTime.Now.Ticks + ".xlsx");
2

Hi,


Thanks for providing us sample code and template file.

After an initial test, I observed the issue as you mentioned. I found an issue regarding List data validation with Tables (ListObject) and Names as you pointed out by using your sample project. The List data validation on B column is not applied fine. I have logged an investigation ticket with an id “CELLSNET-44041” for your issue. We will look into it soon.

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

Thank you.
3

Ah okay, I wasn’t too sure if it was something I’d missed. Thanks for the update.

4

Hi,

Thanks for your posting and considering Aspose.Cells.

We have found this error so you were not missing anything. We are hopeful this error will be fixed within couple of weeks. Once, there is some fix or other update available for you, we will share it with you asap.

5

Hi Shakeel, great to hear! Thanks for the update.

6

Hi,

Thanks for using Aspose.Cells.


Please create an excepted file in MS Excel and post it here. MS Excel
all shows error when we create the list object with your defined names.

7

Hi,

Thanks for using Aspose.Cells.

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

8

Hi Shakeel, thanks for the update! Looking forward to the fix. I’ve attached Test_ExectedOutput.xlsx, not sure exactly what you guys were looking for based on the previous message. It contains what the output of the code would be if it didn’t error out on initial load (manually created). If it’s not relevant, then it can just be ignored :slight_smile:

9

Hi,

Thanks for providing us the expected excel file and using Aspose.Cells.

It is good you have provided the expected excel file. We have logged your comment and expected excel file in our database for product team further consideration and investigation. It will be helpful for them to fix the issue thoroughly in case if there is still something needs to be fixed.

10

Hi,

Thanks for using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells
for .NET v8.6.1.6 and let us know your feedback.

11
Hey Shakeel, the file no longer errors when it loads, but there is one problem that's resulting in the behavior not quite being correct.

This code (Snippet from original post):
names[listRange].RefersTo = "=INDEX(" + listName2 + ",0,MATCH(" + mainSheet.Name + "!A1," + listName2 + "[#Headers],0))";

should result in this within the excel document (Name: ListRangeTableB):
=INDEX(TableB,0,MATCH(MainSheet!A1,TableB[#Headers],0))

however, what comes out is:
=INDEX(TableB,0,MATCH(MainSheet!B1,TableB[#Headers],0))

this causes the dependent list boxes to not work on the correct cell. After loading, if I change the B1 to the A1, my results are as expected. I can't quite figure out if it's something I'm doing incorrectly or something relating to Aspose.Cells. Thanks again for all the help, it's much appreciated.
12

Hi,

Thanks for your feedback and using Aspose.Cells.

We have logged your comment in our database for product team investigation. We will look into it and see if this issue can be resolved. Once there is some news for you, we will let you know asap.

13

Hi,


Please change your code as:
names[listRange].RefersTo = “=INDEX(” + listName2 + “,0,MATCH(” + mainSheet.Name + “!$A$1,” + listName2 + “[#Headers],0))”;
if name’s formula is different according to each cell when the reference is relative.

Let us know if you still have any issue.

Thank you.
14

Hi Amjad,


I did try this previously, but like you said, it causes the cell reference to be literal, instead of relative. The reference needs to be relative, as each rows second cells list needs to be dependent on the value chosen in the first cell. Is there any reason that shouldn’t work? Appreciate the updates.
15

Hi,

Thanks for your feedback and using Aspose.Cells.

We have logged your comment in our database for product team investigation. We will look into it and update you with some workaround or solution if possible. Once there is some news for you, we will let you know asap.

16

Hi,


We have evaluated your issue further. If you just want the row is relative, please use formula as following:
“=INDEX(” + listName2 + “,0,MATCH(” + mainSheet.Name + “!$A1,” + listName2 + “[#Headers],0))”;

Let us know your feedback.

Thank you.
17

Hi,

I am new to Aspose tool. Currently I have a requirement in which I need to create a report with chart and table in RDLC and export it with dropdown. So when I select a value from dropdown it should show chart based on filtered value and also table with filtered value.

I notice there is some code on forum through which I got a dropdown working which is like this.



var cats = categorySales.Tables[0].AsEnumerable().Select(al => al.Field(“Category”)).Distinct().ToList();

var list = new List();

foreach (var item in cats)

{

list.Add(item);

}

finalWorkbook = new Aspose.Cells.Workbook(serverfileName);

//Specify the separator for the data list.

var flatList = string.Join(",", list.ToArray());

var worksheet1 = finalWorkbook.Worksheets[1];



//Get the validations collection

var validations = worksheet1.Validations;

CellArea area;

area.StartRow = 14;

area.EndRow = 14;

area.StartColumn = 1;

area.EndColumn = 1;

//Create a new validation to the validations list

var validation = validations[validations.Add(area)];

//Set the validation type

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

//Set the operator

validation.Operator = OperatorType.Between;



//Set the in cell drop down

validation.InCellDropDown = true;

//Set the formula1

validation.Formula1 = flatList;

//Enable it to show error, you can turn it ON or OFF as desired

validation.ShowError = true;

//Set the alert type severity level

validation.AlertStyle = ValidationAlertType.Stop;

// Set the error title

validation.ErrorTitle = “Invalid Name Error”;

//Set the error message

validation.ErrorMessage = “Please select a Name from the drop down”;

//Add the validation area

validation.AreaList.Add(area);



Aspose.Cells.Cell cell = worksheet1.Cells[“B15”];

//Create a style object

Style style = cell.GetStyle();



//Setting the line style of the top border

style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thick;



//Setting the color of the top border

style.Borders[BorderType.TopBorder].Color = Color.Black;



//Setting the line style of the bottom border

style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thick;



//Setting the color of the bottom border

style.Borders[BorderType.BottomBorder].Color = Color.Black;



//Setting the line style of the left border

style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thick;



//Setting the color of the left border

style.Borders[BorderType.LeftBorder].Color = Color.Black;



//Setting the line style of the right border

style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thick;



//Setting the color of the right border

style.Borders[BorderType.RightBorder].Color = Color.Black;



//Apply the border styles to the cell

cell.SetStyle(style);







But dropdown arrow is showing only when I click on excel cell ( although it shows all values). Is there any way It shows every time? also how to filter chart and table based on selected value. I see there is some Range code but I couldn’t get it how it will work? Also I need to make some value to be default like a “TOTAL”

18

Hi Saurabh,


Thank you for contacting Aspose support.

First of all, I do not think it is possible to create a dynamic chart based on the Validation List (Dropdown) while using Aspose.Cells APIs or Excel application but I will look into it further for confirmation. However, it is possible to create a dynamic chart whose data source is a Table (ListObject from Aspose.Cells perspective) so that when filter on the table is changed, the data representation of the dependent chart changes accordingly. Please find the attachment for sample spreadsheet (output.xlsx) containing a table and 2 charts, Both charts use the aforementioned table as their data source. Please note, the chart on the right was created using Excel whereas the chart on left was created using Aspose.Cells for .NET API as per code provided at the bottom of this post. If this is what you are looking for, I would suggest you to go through the following articles in order to get acquainted with Chart & ListObject concepts from Aspose.Cells for .NET perspective. In case this is not what you require then please manually create the desired results using Excel application and share the spreadsheet in a new thread along with details.


C#

Workbook workbook = new Workbook(@“List_Object.xlsx”);
Worksheet sheet = workbook.Worksheets[0];
var index = sheet.Charts.Add(Charts.ChartType.Column3DClustered, 21, 0, 35, 9);
var chart = sheet.Charts[index];
chart.SetChartDataRange(“A1:D12”, true);

//Optional as per requirements
//Above statement has added all table headers as series to the chart
//following statements remove all unwanted series from the collection while leaving required ones
//this is just to mimic the Excel generated chart
ArrayList list = new ArrayList();
for (index = 0; index < chart.NSeries.Count; index++)
{
var series = chart.NSeries[index];
if (series.DisplayName != “Cost” && series.DisplayName != “Profit”)
{
list.Add(index);
}
}
for (index = 0; index < list.Count; index++)
{
chart.NSeries.RemoveAt((int)list[index]);
}

chart.NSeries.CategoryData = “A2:B12”;
chart.Calculate();
workbook.Save(@“output.xlsx”);


Note: It is advised to always create a new thread for every distinct problem/inquiry for better tracking & management of your requests. Moreover, it is appropriate that you should create your own thread rather than posting on an existing thread in order to get automated notifications. As you are currently not an owner of this thread, you may not get the notification of this post unless you have subscribed to this thread.
19

Hi,
Did you get a chance to look into excel which I sent you in email?

Thanks

20

Hi Saurabh,


Thank you for sharing the sample file via email. Yes, I have checked your provided sample, and I require some time to investigate if the said behaviour can be achieved using Aspose.Cells APIs. I will keep you posted with updates in this regard.