Worksheet Data Range

In SpreadJS, the data range feature lets you define and manage specific worksheet areas within the sheet’s axis system. It is particularly useful for displaying and interacting with complex data structures. It transforms the range cells' coordinate row and col between the sheet axis and the data range axis, providing the transformation for the user to focus on the business logic instead of manual coordinate handling.

The data range abstracts the storage and style storage from the internal worksheet to a user-defined interface. This allows the user to render their specialized data structured directly into the worksheet with all the normal worksheet rendering and interactions. For example, the pivot table is a special data store that is designed for data analysis, and it uses the data range to render its data and styles into the worksheet. This allows users to design custom solutions similar to pivot tables, such as Analytical reports, Financial reports, and Virtual server data views. By retrieving and aggregating data efficiently and rendering it using data range, users can avoid manually writing data to worksheet cells, which improves performance and usability.

Note: Data ranges should not intersect and must have unique names.

Key Features

• Data Management: Defines logical boundaries within large datasets for targeted operations like visualization or analysis.

• Customizable Data Display: Supports styling, merging, and conditional formatting for enhanced readability.

• Dynamic Data Interaction: Real-time updates via event-driven hooks, sticky rows, and JSON serialization.

• Improved User Experience: Features like sticky headers, alternating row styles, and keyboard navigation optimize usability.

• Data Range IO: Export Data Ranges to JSON or Excel formats, maintaining consistent data representation.

Steps to Use a Data Range

Following the steps below, you can use a data range in the worksheet:

1. Create a custom data provider by implementing the IDataProvider interface.

2. Select a target range in the worksheet.

3. Add the data range to the worksheet using the Data Range Manager.

Setting Up a Data Provider

A data provider is a user-defined instance. You can use the GC.Spread.Sheets.DataRange.IDataProvider interface for setting values, getting values, undo changes, and also customizing cell values using the data provider.

Setting Values

The reading and writing of cell values ​​in the data range are controlled by the data provider and you can use this ability to connect your data source conveniently. Use the getValue method to fetch the values when the sheet is rendering the cells in the data range. Similarly, the setValue method lets you assign values to cells within the data range.

Here is a sample of the customer data provider to take values with the data source.

 class CustomerDataProvider {
     // Values
     dataSource = [
         ["ID", "Name", "Age"],
         ["1", "Mike", "18"],
         ["2", "Joe", "19"],
         ["3", "Nick", "30"],
         ["4", "Sam", "26"]
     ]
     getValue(row, col) {
         return this.dataSource[row][col];
     }
     setValue(row, col, value) {
         this.dataSource[row][col] = value;
         return true;
     }
 }

However, if you want to undo/redo the changes done then you need to record the change in the changes parameter and then implement the undo method in your data provider using changes parameter.

   class CustomerDataProvider {
       // Values
       dataSource = [
           ["ID", "Name", "Age"],
           ["1", "Mike", "18"],
           ["2", "Joe", "19"],
           ["3", "Nick", "30"],
           ["4", "Sam", "26"]
       ]
       getValue(row, col) {
           return this.dataSource[row][col];
       }
       setValue(row, col, value, changes) {
           var oldValue = this.getValue(row, col);
           if (changes) {
               // create a change object with necessary informations for recovering state when undo
               var change = {
                   row: row,
                   col: col,
                   oldValue: oldValue,
                   type: "value" // mark the change with some key, then convenient for you to identify when undoing
               };
               changes.push(change);
           }
           this.dataSource[row][col] = value;
           return true;
       }
       undo(change) {
           if (change.type === "value") { // marked in setValue method
               var row = change.row;
               var col = change.col;
               var oldValue = change.oldValue;
               this.setValue(row, col, oldValue);
           }
       }
   }

Styling in Data Range

Using the getStyle method, you can customize the appearance of data ranges, such as table headers and body styles, alternating row styles, and so on. You can also use the GC.Spread.Sheet.Style class to use public style properties in the data provider, such as formatter, if required.

 class CustomerDataProvider {
     //  Styles
     headerRowCount = 1;
     styles = {
         header: () => {
             var style = new GC.Spread.Sheets.Style();
             style.backColor = "#217346";
             style.foreColor = "white";
             return style;
         },
         body: () => {
             var style = new GC.Spread.Sheets.Style();
             style.foreColor = "#217346";
             style.backColor = "lightgray";
             return style;
         }
     }

     step = 2;
     alternatingRowStyle = () => {
         var style = new GC.Spread.Sheets.Style();
         style.backColor = "#EEEEEE";
         return style;
     }
     getStyle(row, col) {
         // when row index is at the row header area
         if (row < this.headerRowCount) {
             return this.styles.header();
         }

         // Alternating Row Styles
         if (row % this.step === 0) {
             return this.alternatingRowStyle();
         } else {
             return this.styles.body();
         }
     }
 }

Conditional Formatting

You can also apply conditional formatting to a column of a data range using the applyColumnIndex property to enhance the readability and interpretability of data.

class CustomerDataProvider {
    // Conditional Formatting
    headerRowCount = 2;
    applyColumnIndex = 2; // the applied conditional format column index
    condition = (value) => value > 18;
    highlightStyle = () => {
        var style = new GC.Spread.Sheets.Style();
        style.foreColor = "red";
        style.cellPadding = "10 0 0 0";
        style.watermark = "ADULT";
        style.labelOptions = { visibility: 0, foreColor: "#CCCCCC", font: '10px Arial' };
        style.vAlign = GC.Spread.Sheets.VerticalAlign.center;
        return style;
    }
    normalStyle = () => {
        var style = new GC.Spread.Sheets.Style();
        style.vAlign = GC.Spread.Sheets.VerticalAlign.center;
        return style;
    }
    getStyle(row, col) {
        if (row < this.headerRowCount) {
            var style = new GC.Spread.Sheets.Style();
            style.backColor = "#217346";
            style.foreColor = "white";
            return style;
        }
        if (row >= this.headerRowCount) {
            // if match the condition
            if (col === this.applyColumnIndex && this.condition(this.getValue(row, col))) {
                return this.highlightStyle();
            }
            return this.normalStyle();
        }
    }
}

Spanning Cells

You can create multi-row or multi-column headers by using the getSpans method in your data provider.

class CustomerDataProvider{
    headerRowCount = 2;

    // Spans
    spans = [new GC.Spread.Sheets.Range(0, 0, 2, 1), new GC.Spread.Sheets.Range(0, 1, 1, 2)]
    getSpans(row, col, rowCount, colCount) {
        const spans = [];
        for (let span of this.spans) {
            if (span.intersect(row, col, rowCount, colCount)) {
                spans.push(span);
            }
        }
        return spans;
    }
}

Hooks and Events

The table below displays different hook actions and their return parameters. You can implement them in your data provider as per your requirements.

Using Data Range Manager

The sheet.dataRanges property help manage multiple data ranges within a worksheet. You can perform the tasks mentioned in the sections below.

To add a new data range, with a name, data provider instance, and range, use the add method of dataRanges class.

sheet.dataRanges.add(
  "DataRangeName",
  CustomDataProvider,
  new GC.Spread.Sheets.Range(2, 2, 4, 4)
);

To remove a data range by its data range name, use the remove method of dataRanges class.

sheet.dataRanges.remove("DataRangeName");

To clear all the data ranges in the sheet, use the clear method of the dataRanges class.

sheet.dataRanges.clear();

To get a data range by its data range name, use the get method of the dataRanges class which takes the name of the data range as its parameter.

var dataRange = sheet.dataRanges.get("DataRangeName");

To get all the data ranges in the sheet, use the all method of the dataRanges class.

var dataRanges = sheet.dataRanges.all();

After your data source is changed, you can repaint and change the data range size and position, as per your requirements.

To repaint the data range area itself, use the repaint method.

dataRange.repaint();

To change the size of data range, you can pass the new rowCount and colCount in range method

var oldRange = dataRange.range();
dataRange.range(oldRange.row, oldRange.col, oldRange.rowCount + 1, oldRange.colCount + 1);

To change the position of the data range, you can pass the new row and colindex in the range method.

var oldRange = dataRange.range();
dataRange.range(oldRange.row + 1, oldRange.col + 1, oldRange.rowCount, oldRange.colCount);

Managing Data Range Options

You can make the data range top rows stick as the first visible row in the sheet, and the sticky rows count can be defined using sticky property and it can be changed later. Use the IDataRangeOptions interface to configure additional options like sticky rows.

sheet.dataRanges.add("DataRangeName", CustomDataProvider, new GC.Spread.Sheets.Range(2, 2, 4, 4), {
  sticky: { top: 2 }
});

Exporting Data Ranges

SpreadJS allows exporting data ranges to JSON, SJS, and Excel formats, ensuring a consistent presentation of the data. Following are the basic considerations for exporting data ranges.

JSON Format

Custom data provider classes cannot be serialized directly. Therefore, you must define a typeName with a string value that matches the same class name in the data provider. Ensure that your data provider class is accessible in the global instance, like window, so that the corresponding custom data provider class can be located during deserialization.

class CustomDataProvider {
  constructor () {
    this.typeName = "CustomDataProvider";
  }
}
window.CustomDataProvider = CustomDataProvider;

SJS Format

While exporting data ranges to SpreadJS format for use in SpreadJS applications, all exported JSON data will be stored in a lossless format.

Excel Format

Since Excel does not support data ranges, these ranges are exported as standard cells, including their values and styles.

Data Range Policies

Here are some important policies to keep in mind when working with data ranges in SpreadJS.

• Row and column index must be between 0 and the total number of rows and columns in the sheet.

• There are no limits on the number of rows and columns, but any data range areas outside the worksheet will be ignored.

• All input parameters for the data provider hooks are based on the data range axes, not the sheet axes (e.g., row and column).

• The data range will be moved when rows or columns are inserted or deleted before it and no action will be taken on the data range when rows or columns are inserted or deleted outside of the data range.

• When the clear range contains the full data range, the data range will be removed.

• The sort options in the Filter dialog and context menu will be hidden when the current context is a data range.

• If there is no toJSON hook implemented in the data provider, a default JSON data output will be generated using the custom data provider's typeName property.

• When using the fromJSON, if there is no matching data provider, the corresponding data range will not be created.