Using structured references with Excel tables

How do I insert Structured References in Tables in Excel?


Using PutCellValue will just show the Formula on the screen.
Using Cells[].Formula will convert to #REF!

Trying something like: =SUM(TEST_TABLE[@[SCORE_1]:[SCORE_3]])

I attached the Excel template and VS solution.

++++++++++++++++++++++++++++++

Workbook wb = new Workbook(“C:\Users\wbrixon\Desktop\TEST_TEMPLATE.xlsx”);

Worksheet ws = wb.Worksheets[“TEST_SHEET”];

Cell cell = ws.Cells[“B3”];
ListObject table = cell.GetTable();

table.PutCellValue(1,0,“Dude Smith”);
table.PutCellValue(1, 1, “Do Stuff”);
table.PutCellValue(1, 2, 100);
table.PutCellValue(1, 3, 90);
table.PutCellValue(1, 4, 80);
table.PutCellValue(1, 5, “=SUM(TEST_TABLE[@[SCORE_1]:[SCORE_3]])”);

table.PutCellValue(2, 0, “Jane Smith”);
table.PutCellValue(2, 1, “Do MORE Stuff”);
table.PutCellValue(2, 2, 110);
table.PutCellValue(2, 3, 190);
table.PutCellValue(2, 4, 180);
ws.Cells[table.StartRow + 2, table.StartColumn + 5].Formula = “=SUM(TEST_TABLE[@[SCORE_1]:[SCORE_3]])”;

wb.CalculateFormula();

Hi,


Thanks for the template file and sample project.

Please try our latest version/fix: Aspose.Cells for .NET v17.2.0.
Well, ListObject.PutCellValue only specifies the string as value and not formula to the cell, so you should not use it. I think you may try to use ListColumn.Formula attribute instead. See the sample updated code segment for your reference. I have tested it with v17.2.0 using your template file and it works fine:
e.g
Sample code:

Workbook wb = new Workbook(“TEST_TEMPLATE.xlsx”);

Worksheet ws = wb.Worksheets[“TEST_SHEET”];

Cell cell = ws.Cells[“B3”];
ListObject table = cell.GetTable();

table.PutCellValue(1,0,“Dude Smith”);
table.PutCellValue(1, 1, “Do Stuff”);
table.PutCellValue(1, 2, 100);
table.PutCellValue(1, 3, 90);
table.PutCellValue(1, 4, 80);
table.PutCellValue(2, 0, “Jane Smith”);
table.PutCellValue(2, 1, “Do MORE Stuff”);
table.PutCellValue(2, 2, 110);
table.PutCellValue(2, 3, 190);
table.PutCellValue(2, 4, 180);

table.ListColumns[5].Formula = “=SUM(TEST_TABLE[@[SCORE_1]:[SCORE_3]])”;
wb.CalculateFormula();


Hope, this helps a bit.

Thank you.

Thanks, that worked. I did find it much easier to build out the table into a range and then convert it into a ListObject.


salaryWorksheet.Cells.Columns[14].ApplyStyle(decStyle, decFlag);
salaryWorksheet.Cells.Columns[15].ApplyStyle(decStyle, decFlag);

ListObjectCollection listObjects = salaryWorksheet.ListObjects;
listObjects.Add(TITLE_ROW, FIRST_DATA_COL, lastDataRow, lastDataCol, true);
listObjects[0].DisplayName = “SALARY”;
listObjects[0].TableStyleType = TableStyleType.TableStyleMedium19;

listObjects[0].ListColumns[“TIG”].Formula = “=(TODAY()-SALARY[Labor Grade Dt])/365.25”;
listObjects[0].ListColumns[“YOS”].Formula = “=(TODAY()-SALARY[Hire Dt])/365.25”;

Hi,


Good to know that you have sorted your issue now. Feel free to write us back if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you.