Free Support Forum - aspose.com

Code for 4.7 not working in 4.8: #REF!

I'm working on a .NET 3.0 C# app for a client. I started the project with Aspose.Cells 4.7 and all of my unit tests pass. The client upgraded to 4.8 so I recompiled my app with that version. Now, some of my unit tests are failing.

I've narrowed down the problem to a statement where I'm attempting to compare cell values across Cells objects. With version 4.8, the second part of this statement is generating #REF!.

if (expenseCells[TEMP_FORMULA_CELL].Value.ToString() != invoiceCells[excelInvoiceCellMetadata.GetExpenseTotalCellLocation()].Value.ToString())

Again, this worked fine in 4.7.

While debugging, I tried replacing the value in brackets with a known valid cell ("U20") but still get #REF!.

Why in version 4.8 is a valid cell now considered an invalid reference? Please reply with some ideas on this.

Thanks,

-Tim

Here's the deal with this issue... in case this helps someone else.

The client's test XLS files had an invalid reference in cell U20 on a worksheet (it had a URL pointing to a inaccessible website). The cell should've had a SUM formula in it.

In Aspose.Cells 4.7, getting the value of the cell that has the invalid reference returned 0 (zero). However, in 4.8 the value of the cell is #REF!. So, several of my tests were passing with version 4.7 because I was expecting zero in that cell... but the change in 4.8 revealed the invalid reference.

I'm sure 4.8 is 99.9% backward compatible with 4.7, but it looks like I found one of the cases where the two versions are behaving differently. Fortunately, the failed tests led me to the problematic cell formula so that's one positive finding through this process.

-Tim

Hi Tim,

Thanks for providing us details.

We appreciate if you could create a simple console application to reproduce the issue (you have mentioned), zip the project and post it here, we will check it soon.

Thank you.

I'm up against a deadline so I can't put the sample app together right now but here's some more info.

The .xlsx I was given had a value reference in a cell, such as this:

=+'https://stg.xyzcorp.com/InvMgmt/[InvoiceTemplate_v4.xlsx]T&E Expenses'!P19

I didn't open the file in Excel, otherwise I would've seen the invalid reference.

My application retrieved the value of the cell that had the bad reference. In Aspose.Cells 4.7 the value property gave me 0 (zero) but in 4.8 the value is #REF!.

-td

Hi,

Well, for your information, Aspose.Cells for .NET does not support to read or calculate an external referenced formula (you can only apply the external referenced formulas to the cells), so I think you are getting #REF! as a calculated value.

We still appreciate if you could provide us the template file or simple project for the issue so that we can look into it soon.

Thank you.