Bug 62254 - OFFSET function fails when 2nd or 3rd arguments are missing
Summary: OFFSET function fails when 2nd or 3rd arguments are missing
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: 3.17-FINAL
Hardware: PC All
: P2 minor (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2018-04-03 19:59 UTC by gallon.fizik@gmail.com
Modified: 2018-04-03 21:53 UTC (History)
0 users

patch (394 bytes, text/plain)
2018-04-03 19:59 UTC, gallon.fizik@gmail.com
test case (592 bytes, text/plain)
2018-04-03 20:00 UTC, gallon.fizik@gmail.com

Note You need to log in before you can comment on or make changes to this bug.
Description gallon.fizik@gmail.com 2018-04-03 19:59:54 UTC
Created attachment 35836 [details]

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:
< 			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