We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

HLOOKUP optional parameter

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

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.

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.

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.

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.

Thanks - that’s fixed it.


Martin

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.

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.