ASPOSE.Cell to read the calculated values using AddIn Formulas

Aspose.Cells Product Family
1

Hi

I am facing two issues on ASPOSE.Cell both are related to AddIn.

  1. The latest version of component I have (i.e 7.0.2.0) is corrupting the excel file when I used the SetAddInFormula method. But it worked with the ASPOSE.Cell version 4.3.0.11 (this version I got from one of the support question in the ASPOSE website).
  2. I have an excel file, which is already setup with formulas, macro’s addon etc. i.e in Excel there are few input cells and the output cells (Output field uses Macros and Addin to calculate the output). So, I should input the values into the input fields and read output from the output fields and finally save the output values into the database.

So following are the challenges that I am facing.

  1. I understands that the SetAddInFormula can be used to add the addin formula to the excel. (In my case the excel is already setup with addin formulas), But I am looking for a method which I can be used to read the calculated value. (Now the cell.Value is returning as null.)
  2. Why the SetAddInFormula method not working on my latest dll version.

Thanks.

KBB

2

Hi,

Please download and try latest version/fix: Aspose.Cells for .NET v7.0.3.6

If you still find the issue, give us sample code (preferably a sample runnable console application) with template files, you may zip it and post it here, we will check it soon.

Thank you.

3
Hi Amjad Sahi

Thanks for the reply, but there was no luck.
I have extracted the issue into a test method as follows.

[TestMethod]
public void TestMethod1()
{
License license = new License();
license.SetLicense("Aspose.Cells.lic");
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
Cell cell = sheet.Cells["B1"];
cell.SetAddInFormula("", "=CUMIPMT(0.04,360,100000,3,120,0)");
var ouput = cell.Value;
workbook.Save("D:\\test.xls");
}

The above code is not throwing any exception, but the SetAddInFormula is not really adding any formula into excel (The cell is showing formula =#N/A). When i physically open the test.xls file i don't see the formula added. But with the version 4.0.3, i was able to see the formula in the cell.

In both cases the calculated value i could not read from the cell.

Hope this will help you to guide us.

regards
K B B
4

Hi,

Please try the following code, it works fine.

C#


string path = @“F:\Shak-Data-RW\Downloads\Output.xls”;


Workbook workbook = new Workbook();

Worksheet sheet = workbook.Worksheets[0];

Cell cell = sheet.Cells[“B1”];

cell.Formula = “=CUMIPMT(0.04,360,100000,3,120,0)”;


workbook.CalculateFormula();


Debug.WriteLine(cell.Value);


workbook.Save(path);



5

Thank you very much, above said solutions work for us too.

Now, we are trying to retrieve a calculated value from a cell which has addIn formula in it.

Workbook workboook = new Workbook();
Worksheet worksheet = workboook.Worksheets[0];
Cell cell = worksheet.Cells["B1"];
//cell.Formula = "=ISOWEEKNUM(F9)"; //This also did not work for us
//cell.SetAddInFormula("myUtilityl.xla", "=ISOWEEKNUM(F9)"); // This also did not work for us.

workboook.CalculateFormula();

ReturnResult = Convert.ToString(worksheet.Cells["B1"].Value); //This return always as #Name

MessageBox.Show(ReturnResult);

workboook.Save(@"C:\Test1.xls");

The addin “myUtilityl.xla” is working properly in excel. It retrieves value for the addin function “=ISOWEEKNUM(F9)”. PFA the addin.

Please find the attached Addin file we are using for test.

The versions of Aspose.dll using which I tried are 7.0.2 and 7.0.3.6.

6

Hi,

Thanks for testing your issue with the latest versions.

I have logged your issue in our database. We will look into it and update you asap.

This issue has been logged as CELLSNET-40191.

7

Hi,

We do not support calculate addin formulas.

Please use Workbook.CalculateFormula(bool ignoreError, ICustomFunction customFunction) and implement calculating it in the customfuction.

8

ok,


Do you mean, ASPOSE.Cell does not support to read the values from a cell which has addin formula in it.?

In my case the excel file is a huge file with tons of addin functions. Is there any workaround to read the addin formula values?

To make it clear again, I don’t need to read the formula, but only the value evaluated using addin formula.
9

Hi,

Can you please attach your file with addin formulas? If it is file like xls/xlsx, then I guess, it could be loaded separately and the values can be extracted out of it.

10

Please find the attached source

  1. myUtilityl.xla is the addin
  2. Test.xls is the excel we are using

regards,

Bijith K B

11

Hi,

I tried to load your file and print the values, but I found, it is empty. Below is my code

Please try the approach mentioned in this post: 349719

C#


string filePath = “F:\Shak-Data-RW\Downloads\test\myUtilityl.xla”;


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.Worksheets[0];


foreach (Worksheet ws in workbook.Worksheets)

{

foreach (Cell cell in ws.Cells)

{

Debug.WriteLine(cell.StringValue);

}

}

12

Hi,

The code above is giving the file path as "string filePath = "F:\\Shak-Data-RW\\Downloads\\test\\myUtilityl.xla";". Infact the filePath should point to the Test.xls file attached. The add-in myUtilityl.xla should be loaded to the excel. (Please click here to see an example for adding excel add-in.).

The add-in myUtilityl.xla calculates the week number for a given date. The add-in takes value from "F9" and insert computed value into the cell where it is called.

We are putting value into the cell "F9" using "worksheet.Cells["F9"].PutValue("2/1/2011");". Now trying to retrieve value from cell "B1" using "cell.StringValue " will fetch any values that is already present in the excel. It will not recalculate upon putting a new value programatically using the above code.

When we give "workboook.CalculateFormula();" and try to retrieve value using "cell.StringValue", we get "#Name?"

What we are trying to achieve is put the input value into a cell (worksheet.Cells["F9"].PutValue("2/1/2011");) and retrieve value from another cell which calculates the output value using add-in function in vb codes.

"Please try the approach mentioned in this post: 349719" is not feasible as there is already an excel file with hundreds of addin

PFA "myUtilityl.xla" and "Test.xls".

The code is:

string path = @"C:\Test.xls";

Workbook workbook = new Workbook(path);

Worksheet worksheet = workbook.Worksheets[0];

worksheet.Cells["F9"].PutValue("2/1/2011");


foreach (Worksheet ws in workbook.Worksheets)
{
foreach (Cell cell in ws.Cells)
{
Debug.WriteLine(cell.StringValue);
}
}

Please change the value in PutValue() and test. Also test by putting value directly in to the cell "F9" after attaching the add-in.

13

Hi,

I have logged your comment in our database and requested for a solution/workaround. Once, we will get some update relating to it, we will let you know.

14

Hi,

We do not support calculating addin function because it's VBA codes in the external file.

We do not support parse the VBA codes and run them now.

Please implement them in ICustomFunction again, then we can calculate them correctly.

15

Hi @simon.zhao ,

Can you share sample custom function code?
I mean How we can implement so that we can calculate formula?
In my case I am using worksheet.copy() method so I have to use calculate formula function to re-calculate all the values.

16

@Coder365,

See the document with example code on how to implement custom calculation engine to extend the default calculation engine of Aspose.Cells for your reference.
https://docs.aspose.com/cells/net/implement-custom-calculation-engine-to-extend-the-default-calculation-engine-of-aspose-cells/