Fix#4061. CONCATENATE, which has slightly different behavior than CONCAT, and which MS has deprecated for that reason, behaves in an unexpected way when a cell range is presented to it and the spreadsheet does not allow for array results. This would almost certainly occur only for Legacy spreadsheets, but such is what was presented in the issue. The code is changed so that when an array of cells is presented to CONCATENATE, and RETURN_ARRAY_AS_VALUE is in effect, the array will be treated as if it were wrapped in the SINGLE pseudo-function (which is what Excel does by somewhat mysteriously prefixing the cell range with `@`).
This is a niche case. This one stands out because of its deprecation and replacement function. It is possible that other functions exhibit this behavior. I have made no attempt to identify others. A similar approach can probably be applied if issues are raised for others.
When the Dynamic Array PR #3962 was introduced, it left the default as Return Array as Value. At some point, the default should be changed to Return Array as Array. This would, of course, be a breaking change, one which will not be part of Release 4. However, it will possibly be part of Release 5.
Rather than relying on the default setting, this PR explicitly sets Return Array as Value when tests require that setting. This will make it easier to identify potential breaks when the default is changed. The entire test suite will now succeed with either setting as default.
In making these changes, a few minor problems were discovered with how Array as Array is handled. These are fixed with this PR.
Till now we have used a static variable/getter/setter to decide what type of result should be returned when a formula is evaluated and an array is the result. This is messy; it would be much better to use an instance variable instead. We cannot eliminate `setArrayReturnType` and `getArrayReturnType` because that would be a BC break. I am considering whether they should be deprecated. In the meantime, I have added a new instance property `instanceArrayReturnType` with getter and setter methods. The property is initially null, and, if it remains so when needed, the static property will be used instead. However, if it is set, its value will be used.
The CONCATENATE function has been treated as equivalent to CONCAT. This is not how it is treated in Excel; it is closer to (and probably identical to) the ampersand concatenate operator. The difference manifests itself when any of the arguments is an array (typically a cell range). Code is added to support this difference.
Support for array results is added to Csv Writer, Html Writer, and Ods Reader and Writer. I have not figured out how to get it to work with Xls.
oleibman