Support for structured references

Does Aspose Cells support structured references in Excel formlas? We’ve tried it and it doesn’t seem to work – when we open the sheet, the formulas don’t calculate. If we replace the structured references with regular references (such as B2, D5, etc.), the formulas do work.

Using structured references with Excel tables - Microsoft Support

Hi,


Thanks for providing us some details.

Do you mean you open the Excel file using Aspose.Cells APIs by instantiating Workbook object and then get the values against formulas at runtime, you would call Workbook.CalculateFormula() method before obtaining the calculated results against formulas present in the workbook, see the documents for your reference:

If you still find the issue, please provide us your template Excel file and share the sample code with us, so we could evaluate your issue soon.

PS. Please try our latest version/fix: Aspose.Cells for .NET v8.5.2 (if you are not already using it) as we made more enhancements regarding formula calculation engine in recent releases.

Thank you.

Thank you for your quick response! No, we’re not opening the Excel file using Aspose, we’re creating it using Aspose. When we open the file created using Aspose, if the formulas use structured references (like [Sales], where Sales is the column label) they don’t work, but if the formula uses the cell reference instead (like B2) it works.

Hi,


Thanks for providing us further details.

Could you create a sample console demo application (runnable) with our latest Aspose.Cells for .NET version v8.5.2 (on how you are specifying the formulas for the given list object/ table in the sheet to save to Excel file format), zip it and post us here to reproduce the issue on our end, we will check it soon. Also attach your current output Excel file (via Aspose.Cells APIs) and your expected file which you may create it (having your desired formulas set for the table) in Ms Excel manually. All this helps us to evaluate your issue precisely to consequently figure it out soon.

Thank you.

I attached a code sample + the Excel output that we receive.

If I enter the same formula manually in the Excel file instead of using Aspose, it works fine.

Hi,


Well, since you are using Shared Formula feature to set formula to the List Object/ table cells in your code which may not work as Share Formula feature only works for simple data set in the same worksheet. Please use Cell.Formula attribute instead to apply formulas to your desired cells in the List object/ table.

Please change the following line of code:
i.e.,
cells[1, 0].SetSharedFormula("=IF(ISBLANK([Location Name*]),"",TRIM([Location Name*]))", wb.Worksheets[0].Cells.Rows.Count - 1, 1);

to:

cells[1, 0].Formula = “=IF(ISBLANK([Location Name*]),”",TRIM([Location Name*]))";
cells[2, 0].Formula = “=IF(ISBLANK([Location Name*]),”",TRIM([Location Name*]))";

it would work fine.

Thank you.