Formulas not working with the new dll

Aspose.Cells Product Family
1

Hi

Following formula which use to work with the older version of aspose.cells (4.4.0.1). But doesn't seem to be working with the new dll.

=VALUE(TEXT((StartDate_Input-NOW())/24,"[h]"))

I downloaded the latest dll because the old one doesn't support array formulas, but the new one is still giving errors.The array formula i am using is:

{=VLOOKUP(Currency, TRIM(ExchangeRates),2,FALSE)}

Will look forward to your help.

Thanks

Ira

2

Hi,

Thank you for Considering Aspose.

I have tested your issue and the formulas you have mentioned are working fine in the Latest Version of Aspose.Cells. Please try the attached Latest Aspose.Cells fix. If you still face a problem, please share your template file and code, we will look into it. Following is my Sample Code.

Sample Code:-

Workbook workbook = new Workbook();

workbook.Open("F:\\Excels\\Book.xls");

Worksheet sheet = workbook.Worksheets[0];

sheet.Cells["A1"].Formula = "=VALUE(TEXT((39974 - NOW())/24,\"[h]\"))";

sheet.Cells["B2"].SetArrayFormula("=VLOOKUP(\"Currency\", A2:A10,1,FALSE)", 5, 3);

workbook.Save("F:\\Excels\\Book.xls");

Thank you & Best Regards,

3

Thanks for the reply.

I am attaching my excel sheet. I have all the formulas in the spreadsheet, so I am not assigning formulas to the cell through the code.

So the only thing I do is open the workbook and call the function calculateformula. It then gives me the error "Index was outside the bounds of the array. Error in calculating cell D6 in Worksheet Outputs "

D6 in outputs sheet contains the formula =VALUE(TEXT((StartDate_Input-NOW())/24,"[h]"))

and C11 in Outputs sheet contains the array Formula.

Waiting ofr your reply.

Thanks again

Ira

4

Hi Ira,

Thanks for providing us the template file.

Yes, we found the issue as you have described. We can reproduce the issue calling Workbook.CalculateFormula() method i.e.., "Index was outside the bounds of the array. Error in calculating cell D6 in Worksheet Outputs ".

We will figure it out soon.

Thank you.

5

Hi,

Thank you for considering Aspose.

We have fixed the issue you have mentioned. Please try the attached latest version. Please let us know if you still face a problem. Now Aspose.Cells also support the TRIM function.

Thank you & Best Regards,

6

Thanks, it now works and doesn't give error on workbook.calculateformula(), but it gives an error value for any cell you do

Range Premium = workbook.Worksheets.GetRangeByName( "Cancellation_Premium" );

Premium[0,0].IsErrorValue is always true. I tried different named ranges, but all of them always errors.

Looking forward for your help again.

Thanks

Ira

7

Hi,

Please make sure that you are using the latest fix that we have provided you in this thread. I don't get the problem as the range cell Premium[0,0].IsErrorValue returns me false value which is correct.

Thank you.

8

Hi,

I am using the latest dll, as I wasn't getting this error with the old dll. I am trying to get value of the named range month which is also giving me error. The fix you provided earlier is working except this.

Any help would be highly appreciated.

Thanks

Ira

9

Hi,

Well, we could you not find the issue you have mentioned using your template file with the latest fix (4.5.1.16). We appreciate if you could create a sample console application with all the files, zip it and post it here to reproduce the issue, we will check it soon.

Thank you.

10

Hi,

Sorry for the inconvenience, i found the error in my version of spreadsheet and it's not giving in the spreadsheet you have.

I am attaching my version of the spreadsheet again.

Thanks

Ira

11

Hi Ira,

Thanks for providing us the template file.

Yes, we found the issue after an initial test, we will figure it out soon.

Thank you.

12

Hi,

Thank you for Considering Aspose.

Please try the attached latest fix. We have fixed the issue you have mentioned. Please do let us know if you still face any problem.

Thank you & Best Regards,

13

Hi,

Thank you for Considering Aspose.

Please try the attached latest fix. We have fixed the issue you have mentioned.

Thank you & Best Regards,

14

Hi,

It works now :)

Thanks for all the help.

Ira

15

Hi,

I am again having problems.

When I do

Range Premium = workbook.Worksheets.GetRangeByName( "PL_Premium" );

Premium[0,0].Value is always 0.

and

Range r1= workbook.Worksheets.GetRangeByName( "PL_Deductible" );

r1[0,0].IsErrorvalue is always error.

I am attaching my sample spreadsheet.

Looking forward for your help.

Thanks

Ira

16

Hi,

Well, I have used your provided template file and it is working fine with the Aspose.Cells fix (V4.5.1.17). The values I am getting are Premium[0,0].Value = 429.0 and r1[0, 0].IsErrorValue = false.

Please make sure that the dll is properly added in your application. Copy the dll to the bin folder of your application. Remove the previous Reference of the Aspose.Cells from the application and add the reference of the newly added Aspose.Cells dll in your bin folder.

Please do let us know if you still face any problem.

Thank you & Best Regards,

17

Hi,

I am using the latest version Aspose.Cells fix (V4.5.1.17), but still getting the same 0 value and error.

Would you want to give the dll again , if in case it had something different.

Thanks

Ira

18

Hi,

I have also tested your scenario with the Latest Fix of Aspose.Cells V4.5.1.18 and it is working fine. Please try this attached latest fix.

Thank you & Best Regards,

19

Hi,

I figured out why it is not working on my side, I am calling workbook.CalculateFormula() before getting the values out of the ranges specified. If I don't call the calculateformula then it doesn't give me 0 value and error.

Hopefully this will help.

Thanks

Ira

20

Hi Ira,

Thanks for further details.

Yes, calling Workbook.CalculateFormula() is giving 0 value for "PL_Premium" range and IsErrorValue boolean attribute is true for "PL_Deductible" range i.e. r1[0,0].IsErrorValue, We will figure it out soon.

Thank you.