Is there a way to parse Excel formula - ideally receiving the formula name and a list of arguments?
Thanks, Tal
Thanks for your query.
Well, Aspose.Cells has its formula calculation engine to read, write, manipulate and calculate MS Excel formulas. You may use Cell.Formula Property to get/set the formula string of the cell, later on you may write your own code to split complete formula string for your needs. And you may use Cell.StringValue Property to get the resultant value. To get the result of a formula at runtime, you will use this property after calling Workbook.CalculateFormula() method, so all the formulas/functions in workbook would be calculated. See the documents in the section for your complete reference:
e.g
Hope, this helps a bit.
Thank you for the quick reply, Amjad.
I am aware of the options you mentioned. The problem I am looking for help with is the splitting of the complete formula string into the formula and its arguments (ideally with identification each argument). I have been experimenting with writing my own code for it, but since I would like to support every possible function, that would be a huge undertaking. Are you aware of a tool (by Aspose or otherwise) that could help me with this task?
Since you can calculate functions, it seems like you have the ability to parse and understand formulas and their arguments, at least internally.
Thanks,
Tal
I am afraid, we might not disclose internal modules of formula calculation engine, you have to write your own logic and code by yourselves.
Also, I am not sure if there is any tool available to parse the MS Excel formulas the way you want.
Thanks, Amjad!
If anyone in the community is aware f such a tool, please share it.
Tal
You are welcome and feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.
@talyifat - Hi, I faced with the same investigation, have you found anything that helped you with the task?
If you need to get some details about functions in formula, you may try custom engine for calculation. The example code:
internal class MyFunctionViewer : AbstractCalculationEngine
{
public override void Calculate(CalculationData data)
{
Console.WriteLine("Got function of " + data.FunctionName);
if (data.ParamCount < 1)
{
Console.WriteLine(" No parameters");
}
else
{
Console.WriteLine(" " + data.ParamCount + " parameters:");
for (int i = 0; i < data.ParamCount; i++)
{
Console.WriteLine(" " + data.GetParamText(i));
}
}
}
public override bool IsParamLiteralRequired
{
get { return true; }
}
public override bool ProcessBuiltInFunctions
{
get { return true; }
}
}
...
Workbook wb = ...;
CalculationOptions copts = new CalculationOptions();
copts.CustomEngine = new MyFunctionViewer();
//view formula expression directly
wb.Worksheets[0].CalculateFormula("=sum(1,2,3)", copts);
//or you may just calculate the workbook which contains formulas
//wb.CalculateFormula(copts);
For using custom calculation engine, here is the document for you reference.
Hello @johnson.shi,
Thank you for your quick reply! The solution you provided works perfectly, and I appreciate your help.
I have a few additional questions. When I calculate formulas with functions like =SUM(A1,B2), the input parameters (A1 and B2) are displayed as expected, and the formulas work correctly. This behavior is consistent with similar formulas. Brilliant!
However, when I try to calculate a formula with a function like =SUM([1]!Table1[#All]) (a reference to a table in an external workbook), I encounter an issue. The calculated value appears as #NAME?, and the parameter name is displayed as #REF. When I debug the code, I notice the value is "TABLE-NEED-CONTEXT-TO-BUILD-STRING-EXPRESSION".
For reference, I used the demo version of the library (24.12). I’ve attached a backup containing the code, logs, and Excel workbooks used for testing.
My question is: Does the com.aspose.cells library support parsing references to tables in external workbooks (External Structured References)?
For context, please see the following documentation: Using structured references with Excel tables - Microsoft Support ).
Thank you for your help,
Oleh
Backup.zip (17.3 KB)
Thanks for sharing the scenario with sample files.
It seems parsing and handling external structured references (references to tables in external workbooks) may not be supported in the context to custom calculation engine. Anyways, we will evaluate it thoroughly and get back to you with updates.
External table references are not supported yet when getting parameter expression in custom calculation engine.
We will support it in later fix/version. We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.
Issue ID(s): CELLSNET-57494
You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.
This is to inform you that we have supported to get expression of external table reference while calculating formulas with custom calculation engine (Ticket ID: “CELLSNET-57494”). The enhancement/fix will be included in the next release (Aspose.Cells v25.1) scheduled for release in early January 2025. We will notify you when the new version is available.
Dear Team,
Thank you for your support regarding this feature. I am truly grateful for your assistance and am excited to have the opportunity to try it out. Please feel free to let me know if you require any additional information from my side.
Best regards,
Oleh
@olehhordiienko
Thank you for your feedback. You are welcome. We will notify you promptly once the version v25.1 is released.