Bug 62254

Summary: OFFSET function fails when 2nd or 3rd arguments are missing
Product: POI Reporter: gallon.fizik <gallon.fizik>
Component: SS CommonAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: minor    
Priority: P2    
Version: 3.17-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: All   
Attachments: patch
test case

Description gallon.fizik@gmail.com 2018-04-03 19:59:54 UTC
Created attachment 35836 [details]
patch

According to the Microsoft documentation, 2 and 3 arguments to the OFFSET function are mandatory. However, Excel 2010 allows the function to be written as OFFSET(ref, , ); empty arguments are treated as zeros. 

The patch for Offset.java seems pretty easy to implement:
172,173c172,173
< 			int rowOffset = evaluateIntArg(args[1], srcCellRow, srcCellCol);
< 			int columnOffset = evaluateIntArg(args[2], srcCellRow, srcCellCol);
---
> 			int rowOffset = (args[1] instanceof MissingArgEval) ? 0 : evaluateIntArg(args[1], srcCellRow, srcCellCol);
> 			int columnOffset = (args[2] instanceof MissingArgEval) ? 0 : evaluateIntArg(args[2], srcCellRow, srcCellCol);

I also attach a test case

There was a patch https://bz.apache.org/bugzilla/show_bug.cgi?id=58339 that introduced the same behavior for optional arguments 4, 5.
Comment 1 gallon.fizik@gmail.com 2018-04-03 20:00:44 UTC
Created attachment 35837 [details]
test case
Comment 2 PJ Fanning 2018-04-03 21:53:01 UTC
patch added using https://svn.apache.org/viewvc?view=revision&revision=1828288