Aspose Cells 8.3.2 cannot read value of a cell- Indirect

Aspose.Cells Product Family
1

Hi ,


I have a problem during the reading of a workbook.

In the sheet “Alfa” there is a cell named “Beta” in which there is the value that I want to read.

In the sheet “Gamma” I have the formula
=IF(NOT(ISERROR(INDIRECT($F33&"!"&C$15)));INDIRECT($F33&"!"&C$15);"-")
where F33 contains “Alfa” and C15 contains "Beta"

In Excel the cell with the specified formula contains the right value but when I read it with Aspose it finds a ZERO.

I try to read the value with the method
worksheet.Cells.ExportDataTable(firstRow, firstColumn, count, tableColumnNumber, new ExportTableOptions() { IsVertical = false, ExportColumnName = true });

At Debug time I can see that I’m reading the correct portion of the sheet because I read other rows that are correctly read.

Thanks in advance for the support.

2

Hi,

Thanks for your posting and using Aspose.Cells.

Please call Workbook.CaclulateFormula() method and then read the cell value and see if it fixes your issue.

Also, download and try the latest version: Aspose.Cells
for .NET v8.4.0.4 and check if it makes any difference.

If the problem still occurs, then please provide us your source Excel file containing your formulas. We will look into it and help you asap.

3

Thanks for your quick reply!


I call Workbook.CalculateFormula() before I save the file, do I need to call it also after open and before read it?

I’ll attach the file, the cells that i cannot read are B2, B3, B4 in the last sheet named "Consolidation"

P.S. I’ve already tried with Aspose Cells 8.4.0.4 but nothing changed
4

Hi Michele,


Thank you for sharing the sample spreadsheet.

We have noticed that Aspose.Cells for .NET 8.4.0.4 return 0 for the cells B2, B3 & B4 in worksheet Consolidation even after calling the Workbook.CalculateFormula method. We have logged this incident in our bug tracking system under the ticket CELLSNET-43529 for further investigation. Please spare us little time to properly analyze the problem cause, and to provide the fix, if applicable. In the meanwhile, we will keep you posted with updates in this regard.

Please note, you need to call the Workbook.CalculateFormula method once before reading the cell values or exporting the data. However, if you have changed any cell value on which some formula field is dependent then you need to call it again to calculate the formula values based on the changed cell values.
5

Hi Babar,


Thanks for the attention.

Regarding the call of the method CalculateFormula, can you confirm that if I use it before saving the Workbook, then I don’t have to call it before reading if there hasn’t been any changes. The way Aspose Cells works is exactly like Excel does right ? :slight_smile:

I hope to hear news about the fix asap.
6

Hi,


Please note:
If you need to retrieve the calculated values (against formulas) from the cells or you are exporting data to fill some DataTable or arrays with updated values, you should call Workbook.CalculateFormula() method before it. You do not need to call the method again before saving the Excel file formats. But if you have changed the values in the cells later on in your codes (which may depend upon formulas in the spreadsheets) dynamically and later on you need to get the updated values or export the updated values to another table or arrays, you should call the method again.

Hope, you understand now.

Thank you.


7

Hi everybody,


Any news about the fix?
8

Hi,

Thanks for your posting and using Aspose.Cells.

We are afraid, there is no update for you at this moment regarding this issue. However, we have logged your comment in our database against this issue and requested the product team to provide fix or some ETA for this issue. Once, there is a fix or some news for you, we will let you know asap.

9

Hi,

Thanks for your using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells for .NET v8.4.0.6 and let us know your feedback.

10

Himshakeel.faiz is not online. Last active: 04-08-2015, 4:10 AM Shakeel,


Thanks for the support, we’re using Aspose.Cells 8.3.2 in production environment, change version would imply a significant cost of time for internal testing and UAT, could you tell me if the version 8.4.0.6 will resolve our problem? Is it possible to request a patch for the bug alternatively?
11

Hi Michele,


Thank you for writing back.

Yes, the latest version of Aspose.Cells for .NET 8.4.0.6 fixes the problem logged earlier as CELLSNET-43529 in our bug tracking system. We have tested the case with the following piece of code and then shared the download link in this thread.

C#

var book = new Workbook(“D:/test.xlsm”);
var sheet = book.Worksheets[“Consolidation”];
book.CalculateFormula();
Console.WriteLine(sheet.Cells[“B2”].DisplayStringValue);
Console.WriteLine(sheet.Cells[“B3”].DisplayStringValue);
Console.WriteLine(sheet.Cells[“B4”].DisplayStringValue);
//var table = sheet.Cells.ExportDataTable(0, 0, sheet.Cells.MaxDataRow + 1, sheet.Cells.MaxDataColumn + 1, new ExportTableOptions() { IsVertical = false, ExportColumnName = true });

bombean1:
Is it possible to request a patch for the bug alternatively?

I am afraid, we cannot honour your request of patching your current version of the API with this fix because it would be against the company policy. Please note, we can only provide the fix on the top of the latest API release.
12

Hi Babar,


I still cannot read the values, using the same file I’ve uploaded and even using .CalculateFormula() after initializing the Workbook I stille read zeros in B1, B2, B3 cells


also sheet.Cells[“B2”].DisplayStringValue prints zero.

Do I have to use .CalculateFormula() after initializing the sheet? I confirm that I don’t modify any cell after opening it.
13

Hi Michele,


Please check the attached archive for a sample console application. Please either try the project (Visual Studio 2013) or run the executable from bin folder to see if you get the result as attached snapshot. If the results are the same, try rebuilding your main project. You may confirm the assembly version using the CellsHelper.GetVersion static method. In case you get different results then please share the following details for further investigation in this regard.

  • Operating system version
  • Operating system architecture (32bit/64bit)
  • Service pack version
  • Target .NET Framework version
  • Type of application (preferably share a sample)
14

The issues you have found earlier (filed as CELLSNET-43529) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.