TableArray Class

A TableArray object is analogous to an Excel range of cells that form a table. It is used in the Adaptive Calculation Engine implementation of XLOOKUP(), XMATCH(), VLOOKUP(), HLOOKUP(), MATCH(), and INDEX() functions. A TableArray object encapsulates a two dimensional array of alphanumeric, numeric and boolean elements.

Introduction

Suppose we have a range of cells B1:D9 that forms the following movie table:

Example of a range of cells B1:D9 that forms a movie table.

The below code creates a corresponding TableArray object:

Object[][] movieTable = {{"Genre",      "Title",                     "Budget"},
                         {"Comedy",     "My Spy",                 18000000.00},
                         {"Grime",      "The host",               40000000.00},
                         {"Drama",      "Little Women",           40000000.00},
                         {"Sci-Fi",     "Ad Astra",               80000000.00},
                         {"Adventure",  "The Call of the Wild",  109000000.00},
                         {"Animation",  "Missing Link",          150000000.00},
                         {"Action",     "No Time to Die",        250000000.00},
                         {"Family",     "Lion King",             260000000.00}};

TableArray movieTableArray = new TableArray(movieTable);

For any subset of the original range of cells that forms a table it is possible to create a corresponding TableArray object from a subarray of the original two dimensional array.

For the range of cells B1:C9 in our example:

Example of a subset B1:C9 of range of cells B1:D9 that forms a movie table.

the following code uses the same two dimensional array movieTable to create a corresponding TableArray object:

int startRowIndex = 0;
int startColumnIndex = 0;
int endRowIndex = 8;
int endColumnIndex = 1;

TableArray genreAndTitleTableArray = new TableArray(movieTable, startRowIndex, startColumnIndex,
        endRowIndex, endColumnIndex);

Overview

The TableArray class has three constructors:

TableArray(Object[ ][ ] tableArrayBase)

TableArray(Object[ ][ ] tableArrayBase, int startRowIndex, int startColumnIndex, int endRowIndex, int endColumnIndex)

TableArray(int numRows, int numColumns)

The first constructor creates a TableArray object from the two dimensional array tableArrayBase.

It throws two exceptions:

NullPointerException if the tableArrayBase array is null.
IllegalArgumentException if the tableArrayBase array has the elements of the invalid types.

The second constructor creates a TableArray object from a rectangle of elements inside the two dimensional array tableArrayBase. This rectangle is defined by the row and column of its upper left vertex (startRowIndex and startColumnIndex) and the row and column of its lower right vertex (endRowIndex and endColumnIndex). The row and column indexes start with 0.

It throws two exceptions:

NullPointerException if the tableArrayBase array is null.
IllegalArgumentException if the tableArrayBase array has the elements of the invalid types.
IllegalArgumentException in case of incorrect combinations of row or column indexes.

The third constructor creates an empty TableArray object such that the number of rows equals to numRows and the number of columns equals to numColumns.
It throws the IllegalArgumentException if numRows < 0, numColumns < 0 or both numRows and numColumns are zeros.

This constructor should be used in conjunction with the add(Object obj, int rowNum, int columnNum) method, which throws the IllegalArgumentException
if rowNum < 0, rowNum >= numRows, columnNum < 0, or columnNum >= numColumns.

The lowest row or column number is 0.

The TableArray class has one more method, Object[][] getTable(), which returns its elements.

Alphanumeric and numeric elements of the first row and the first column of a TableArray object are automatically sorted and internally stored, while the original order of all TableArray elements is preserved.
The sorted TableArray elements are used as the XLOOKUP(), XMATCH(), VLOOKUP(), HLOOKUP(), and MATCH() functions search keys.

Example 1

This example shows a TableArray object tableArray, corresponding to the below range of cells B1:C4:

Example of a range of cells B1:C4 that forms a produce table.

It uses the first TableArray constructor and the getTable() getter to show the tableArray elements:

import com.crystalprism.ce.usermodel.TableArray;

public class TableArrayExample1 {
    public static void main(String[] args) {
        Object[][] tableArrayBase = {{"Quantity", "Produce"},
                                     {   10,        "Apple"},
                                     {   20,         "Pear"},
                                     {   30,       "Orange"}};
        TableArray tableArray = new TableArray(tableArrayBase);
        System.out.println(tableArray.toString());
    }
}

TableArray{table=[[Quantity, Produce], [10, Apple], [20, Pear], [30, Orange]]}

Example 2

This example shows a TableArray object tableArray, corresponding to the below range of cells B2:C4:

It uses the second TableArray constructor and the getTable() getter to show the tableArray elements:

import com.crystalprism.ce.usermodel.TableArray;

public class TableArrayExample2 {
    public static void main(String[] args) {
        Object[][] tableArrayBase = {{"Quantity", "Produce"},
                                     {   10,        "Apple"},
                                     {   20,         "Pear"},
                                     {   30,       "Orange"}};
        int startRowIndex = 1;
        int startColumnIndex = 0;
        int endRowIndex = 3;
        int endColumnIndex = 1;
        TableArray quantityAndProduce = new TableArray(tableArrayBase, startRowIndex, startColumnIndex,
                endRowIndex, endColumnIndex);
        System.out.println(quantityAndProduce.toString());
    }
}

TableArray{table=[[10, Apple], [20, Pear], [30, Orange]]}

Example 3

This example shows how to use the third TableArray constructor and the add() method to create the quantityAndProduce TableArray object from the previous example:

import com.crystalprism.ce.usermodel.TableArray;

public class TableArrayExample3 {
    public static void main(String[] args) {
        TableArray quantityAndProduce = new TableArray(3, 2);
        quantityAndProduce.add(10, 0, 0);
        quantityAndProduce.add("Apple", 0, 1);
        quantityAndProduce.add(20, 1, 0);
        quantityAndProduce.add("Pear", 1, 1);
        quantityAndProduce.add(30, 2, 0);
        quantityAndProduce.add("Orange", 2, 1);
        System.out.println(quantityAndProduce.toString());
    }
}

TableArray{table=[[10, Apple], [20, Pear], [30, Orange]]}