HLOOKUP optional parameter

Aspose.Cells Product Family
1

I’ve just had a problem calculating a worksheet with Aspose Cells for Java 7.7.1. I finally tracked it down to a formula using HLOOKUP. The formula was


=HLOOKUP(Data!$I$3,Seasonality!C5:N37,28,)

and the trailing comma in the parameter list caused “com.aspose.cells.CellsException: null”. The fourth parameter is optional. Excel doesn’t seem to mind the comma, but it seems that Aspose assumes there will be a boolean value/expression after it.

I’d suggest Aspose should match Excel and not treat this as an error.

In my case I had the calculate method failing on error so I got the exception. But with calculate set to ignore errors the result was #VALUE!

Regards,
Martin
2

Hi,

Could you provide us your template file and sample code to reproduce the issue on our end regarding HLOOKUP formula/function, we will check it soon.

Also, kindly download and try our latest version/fix: Aspose.Cells for Java (Latest Version) if it makes any difference.

Thank you.

3

I’ve tried 7.7.2.3 and the problem still occurs. I’ve attached a simple workbook which has the problem formula in D6.


My code is simply

public static void main(String[] args) throws Exception {
Workbook workbook = new Workbook(“c:\hlookup.xlsx”);
workbook.calculateFormula(false);
}

Using workbook.calculateFormula(true) obviously suppresses the CellsException, but it doesn’t calculate the correct value.
4

Hi Martin,

Thank you for providing the sample spreadsheet.

We are able to observe the said exception (com.aspose.cells.CellsException: null) while using the latest build of Aspose.Cells for Java 7.7.2.3 with your sample file. A ticket (CELLSJAVA-40756) has been logged in our bug tracking system to probe further into this matter. Please spare us little time to properly analyze the problem cause, and to provide a fix at earliest. In the meanwhile, we will keep you posted with updates in this regard.

5

Hi Martin,

Thanks for using Aspose.Cells for Java.

We have fixed this issue.

Please download and try the latest version: Aspose.Cells
for Java v7.7.2.5 and let us know your feedback.

6

Thanks - that’s fixed it.


Martin
7

Hi,


Good to know that your issue is resolved by the latest version/fix. Feel free to contact us any time if you need further help or have some other issue or queries, we will happy to assist you soon.

Thank you.
8

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

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