Excel - VLookup Calculation Formula Issue


#1

Hello,

We’re dealing with an issue in the Excel generation process.
Please check the content of the ForAspose.zip (1.9 MB) file. Inside you will find 2 excel files with their corresponding pdf export, and a screenshot compilation.The CorrectlyGenerated.xlsx file is generated by using Interop, and the ErrorInGeneration.xlsx file is generated by using Aspose 19.9.0.

If you open the CorrectlyGenerated.pdf file, and navigate to the last slide (7/7) that contains a chart, and compare it to the chart in the last slide in the ErrorInGeneration.pdf - you will notice that the data points are missing in the chart in the second file, which is incorrect.

In order to analyze why this happened, we compared the contents of the xlsx files. We found out that the broken chart reads from the Column Support!CP. That’s where we noticed the discrepancy between the 2 files. The one generated by Interop is correct (see Interop_Support_CP_Column.png (9.1 KB)), but the one generated by Aspose has #N/A instead of the actual values.

We noticed that if you copy the value from CP3 (for example) and paste into a blank cell, the value is shown correctly, see Aspose_MissingCalculation.png (53.4 KB), which is really strange.

To us it looks like a problem in the calculation, seems like it’s not being performed properly for the cells in the column Support!CP.

Let us know if you have any questions or need more details!


Not sure if it is relevant, but we had a similar issue related with the PDF export several months ago: https://forum.aspose.com/t/calculateformula-formulas-do-not-affect-charts-net/197104/9

We’ve been using Aspose.Cells for almost one year and we’re generally satisfied from your service and customer support. We’re hoping to get a helpful advice and support as always!

Best Regards,
Rystad Energy


#2

@rystadenergy,
Thank you for your query. Regarding your following comments, could you please provide us with a simplified runnable console application (with all references resolved) which can be used to generate this ErrorInGeneration.xlsx file here and create PDF as well. Try to remove all the code and worksheets which are not required to reproduce the issue. This code will help us to observe the problem here and provide assistance accordingly.


#3

Hello @ahsaniqbalsidiqui,

We managed to solve the issue with the initial excel and PDF we reported earlier, but we discovered another, similar issue in a different report that we’re generating with Aspose.

Here’s the source code: ForAspose.zip (2.4 MB).

If you unzip it and navigate to bin\Debug\netcoreapp2.2, you will notice 2 excel files:

  • _DataFile.xlsx - which is only used as a data source
  • _TemplateFile.xlsx - which is a template in which all the data is copied into. It contains all of the formulas, charts, etc.

After you run the code on your machine, you should see 2 generated files, 1 excel and 1 pdf file.
Here’s how it should look like: SourcesAndGeneratedFiles.png (14.6 KB)

If you check the generated PDF, you will notice that the values in the column Oil&Gas Project are not correctly resolved (they should not be 0, but text instead). See ChartWithMissingValues_OilAndGasProject.png (83.9 KB)

Moreover, if you manually click on the cells in Support!E2:E19 and press enter after clicking, then the values are correctly resolved, without any other fix.

Do you have any suggestion for us?

Thank you,
Best Regards


#4

@rystadenergy,
We were able to observe the issue but we need to look into it more. 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

CELLSNETCORE-36 – Chart not rendered properly in PDF

Regarding the following comments, I could not verify it. Could you please provide more detail about it?


#5

Hi @ahsaniqbalsidiqui,

Thanks for the update!
Regarding your question, see ExcelManualFix.png (49.3 KB)
This is just an observation, if you simply click on the value of the cells from E2 to E19, and click away, the value is resolved correctly. The file was generated with Aspose.


#6

@rystadenergy,
Thanks for details of manual fix.
We will let you know once an update will be available.