URGENT Cell.Formula always returns null for unsupported formula

Aspose.Cells Product Family
1

Is there anything I can do to get the text of the formula?



Here is a file to demonstrate the forumulas I want to be able to see.



Aspose.Cells v 4.5.1.0

2

Hi,

Thanks for your inquiry.

Well, your template file contains external link, you may try the following code to get the formula string

e.g..,

Workbook wb = new Workbook();
wb.Open(@"f:\test\Book1.xls");
ExternalLinks elnks = wb.Worksheets.ExternalLinks;
ExternalLink elnk = elnks[0];
string source = elnk.DataSource.ToString();

Thank you.

3

I think I haven’t communicated what I’m after. This code sort of gives me a path to the .xla file. That isn’t what I want.



Let’s say I’m running this code and I have a cell with an unsupported formula at location B3.



If I use this code…



Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();



wb.Open(excelFilePath);



//the value of this cell formula will be null



wb.WorkSheets[0].Cells[“B3”].Formula



Now I can’t figure out what the text of the formula is through Aspose. However I know hte formula is there, but I don’t want Aspose to process the formula in any way. I just want the text of the formula. In this case I hoped/expected to see something like “HsGetText(…” or something like that.



So is this possible?



4

I think I haven’t communicated what I’m after. This code sort of gives me a path to the .xla file. That isn’t what I want.



Let’s say I’m running this code and I have a cell with an unsupported formula at location B3.



If I use this code…



Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();



wb.Open(excelFilePath);



//the value of this cell formula will be null



wb.WorkSheets[0].Cells[“B3”].Formula



Now I can’t figure out what the text of the formula is through Aspose. However I know hte formula is there, but I don’t want Aspose to process the formula in any way. I just want the text of the formula. In this case I hoped/expected to see something like “HsGetText(…” or something like that.



So is this possible?

5

I figured this out, and will post the solution so other people can benefit.



I need to make a class that implements



Aspose.Cells.ICustomFunction



//Then I open the excel file

Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();

wb.Open(excelFilePath);



//call the CalculateFormulaMethod



wb.CalculateFormula(true, new MyFormulaClass());



your class MyFormulaClass will implement the Aspose.Cells.ICustomFunction Interface



you provide the implementation for the CalculateCustomFunction method. When this method is called you will get the name and arguments of your custom function passed to the method. This is where you process the formula and do whatever you want with the result.







6

Hi,

Please try the attached version, we have supported to get the formula string (whether the formula utilizes external links) using Cell.Formula from the template file now.

It is to be noted here, we will return the formula of the cell e.g.., from cell B3:

='\Hyperion\SmartView\Bin\HsTbar.xla'!HSGETTEXT("Discovery2","Scenario#Actual;Ye
ar#1999;Period#June;View#YTD;Entity#Connecticut.Stamford;Value#USD;Account#NetPr
ofit;ICP#[ICP None];Custom1#GolfBalls;Custom2#AllCustomers;Custom3#[None];Custom
4#Increases")

But MS Excel will add volume code (path) to the formula (at runtime), such as, if the template file's formula has volume C, MS Excel will display the formula as

='C:\Hyperion\SmartView\Bin\HsTbar.xla'!HSGETTEXT("Discovery2","Scenario#Actual;Ye
ar#1999;Period#June;View#YTD;Entity#Connecticut.Stamford;Value#USD;Account#NetPr
ofit;ICP#[ICP None];Custom1#GolfBalls;Custom2#AllCustomers;Custom3#[None];Custom
4#Increases")

We cannot evaluate on which volume the file is stored/saved while we get the formula using Cell.Formula property, so we cannot not simply add volume code to the formula while obtaining the formula string. Hopefully, you understand now.

Thanks for your time and understanding.