Calculate formula with reference using Aspose.Cells for .NET in C#

Aspose.Cells Product Family
1

Hi, i have this formula in an excel sheet

=SI($C12=0;"";SI($C12=1;IMPORTARDATOSDINAMICOS(“Soles”;$D$10;“Mes”;AP$6;“Supervisor”;$D12);SI($C12=2;IMPORTARDATOSDINAMICOS(“Soles”;$D$10;“Mes”;AP$6;“Supervisor”;$D12;“Mesa”;$E12);IMPORTARDATOSDINAMICOS(“Soles”;$D$10;“Mes”;AP$6;“Supervisor”;$D12;“Mesa”;$E12;“NombreVendedor”;$F12))))

I can read the formula from aspoce cells:

Cell _cell = ws.Cells[“AG3”];
string q = _cell.Formula;

// q = =IF($C12=0,"",IF($C12=1,GETPIVOTDATA(“Soles”,$D$10,“Mes”,AP$6,“Supervisor”,$D12),IF($C12=2,GETPIVOTDATA(“Soles”,$D$10,“Mes”,AP$6,“Supervisor”,$D12,“Mesa”,$E12),GETPIVOTDATA(“Soles”,$D$10,“Mes”,AP$6,“Supervisor”,$D12,“Mesa”,$E12,“NombreVendedor”,$F12))))

if i use CalculateFormula:

var results = ws.CalculateFormula(q);
// results = #REF!

But if i go to excel and hit f2 + enter then it works

Why is the difference?
Is it a trial limitation?
Or do i have to do something else?

2

@mcolca,
Please share your sample file and code snippet with us for our testing. We will reproduce the problem and provide our feedback after analysis.

3

Greetings,

Here are the links for the files you requested:
https://drive.google.com/file/d/1bzvEbBG7HNLhdGlyt4irB-2E-Y77cS0f/view?usp=sharing

This one contains the code snippet
( the code that throws the error is inside the folder “Virtual Excel” row 551 )
https://drive.google.com/file/d/1bopt67VRSAtJwP4sC4Y8KfkODXlgtt7Q/view?usp=sharing

The output file needs this folder to be saved:
(and have a “temp” folder inside )
D:\JNJ\primario

To run the program run this code in the console:
dotnet Pazuzu.dll _VEGA_CENTRO.xml

That will take the excel, run the queries, and paste it in the file, then use CalculateFormula in the cell [AG3] that’s inside Soles_Distribuidora

4

@mcolca,
I have tried this project but as its huge project and contains references to databases as well, therefore it is not helpful for us to reproduce this issue. Could you please provide us a standalone simplified console application which contains all the data (no reference to database etc) and can be used to observe the issue here? We will analyse it and provide our feedback at the earliest.

5

Hello, I am attaching the excel file that I am trying to update and the code that makes the calculation of Excel sheets. As you can see, the Excel file has blank cells (see image number 1). By pressing the “Refresh all” button we get image number 2. If I execute the attached code, the Excel file remains as image 1, but it should look like number 2. Am I doing something wrong? Is there any way to get to what is shown in image 2?

Image 1 (7.4 KB)
Image 2 (9.3 KB)

Pazuzu_test.zip (4.8 MB)

6

@mcolca,
We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSNET-46987 - Can’t calculate formula when reference cells
7

Hello, any news?

8

@mcolca,
This issue is logged too recently and may take 3 to 5 days to be resolved. We will write back here as soon as some feedback is ready to share.

9

@mcolca,
This is to inform you that we have fixed your issue (logged earlier as “CELLSNET-46987”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

10

The issues you have found earlier (filed as CELLSNET-46987) have been fixed in Aspose.Cells for .NET v19.11. This message was posted using Bugs notification tool by Amjad_Sahi