Build 4.5.0.45 of xlsgen adds read, write and calculation support for spill ranges.


Spill range example (notice =SUM(A2#) in the formula bar)

Spill range is a special cell range where the beginning of the range is specified, but not the end. Spill range have been announced on September 2018 by Microsoft and they will be avalaible in the course of 2019 in Office 365. It's important to realize that this feature is not going to be made available in any regular Excel version included Excel 2019 (released on September 2018).

xlsgen provides read, write and calculation support for spill ranges, so as long as your scenario does not imply opening the resulting Excel file in a version of Excel that does not support them, which is many of them, xlsgen can get the scenario up and running, regardless of your Excel version (in fact xlsgen does not expect any Excel version installed on the computer where xlsgen runs).

Spill range is of interest whenever the cell area being referenced changes over time. Indeed, every time a new row is added, or removed, a fix cell area would break the integrity of calculations, whereas a dynamic cell area would include both new rows and removed rows. This is exactly what spill range is.

The notation is subtle. An anchor sign is added to a cell reference. This anchor sign means the bottom of the range isn't specified by the user, which in turn means it's up to xlsgen to calculate it based on what's in that range, that is whether that's raw data or an existing array formula, i.e. a formula that spans across multiple rows.

In the screen shot above, it's very easy to understand the result of suming the spill range A2#, as in =SUM(A2#). It's the equivalent of =SUM(A2:A11).