SpreadJS Testing and Performance Optimization

Overview

When working with SpreadJS, developers may encounter challenges related to unit testing spreadsheet interactions, synchronizing SpreadJS tables with JSON data stores, and maintaining performance when working with multiple tables or tables with complex functionality.

This article provides guidance on unit testing SpreadJS applications, testing JSON updater functions, and improving rendering and data-update performance using recommended SpreadJS APIs and optimization techniques.

Question 1: What Are the Best Practices for Unit Testing SpreadJS Tables and JSON Updaters?

Why Documentation on Unit Testing Is Limited

SpreadJS renders entirely onto an HTML5 element. In standard test runners like Jest or Vitest running on Node.js/jsdom, the DOM does not contain individual cell elements and has no native rendering context. Traditional DOM selectors (getByText, fireEvent.click) cannot interact with cell coordinates.

Attempting to simulate pixel-level clicks inside Jest/jsdom is not practical. Instead, testing should focus on two complementary layers.

Layer A: Unit Testing the Updater Logic (Fast and Isolated)

The most resilient approach is to decouple your updater functions from SpreadJS itself.

Pure Updater Function: Write your updater functions to accept plain arguments or JSON objects, not SpreadJS event objects.

Example (updaters.ts):

export function updateJsonTable(
  jsonTable: any[],
  rowIndex: number,
  field: string,
  newValue: any
) {
  const updated = [...jsonTable];

  updated[rowIndex] = {
    ...updated[rowIndex],
    [field]: newValue
  };

  return updated;
}

Thin Adapter: Your SpreadJS event listener (ValueChanged, RangeChanged, etc.) simply reads the cell coordinates and values and passes them to the pure updater.

Unit Test: You can unit test 100% of your business transformations and table-state logic without initializing SpreadJS or mocking canvas elements.

Layer B: Headless Integration Tests with CommandManager (Jest / Vitest)

If you want to test the full loop verifying that user edits inside SpreadJS trigger your event listeners and execute your updater functions you can use the SpreadJS CommandManager.

Important: Calling sheet.setValue() will not trigger ValueChanged. In SpreadJS, setValue() is a low-level API that deliberately suppresses UI events to prevent event loops. To trigger ValueChanged, validation, and dirty tracking in tests, execute the action through the CommandManager.

Install Canvas Mocking:

bash
npm install --save-dev jest-canvas-mock

Add the following import to your test setup file so the Workbook initializes without canvas errors in jsdom:

import 'jest-canvas-mock';

Simulate User Edits via CommandManager:

import '@mescius/spread-sheets';
import { setupSpreadTable, getMyJsonState } from './mySpreadModule';

describe('SpreadJS to JSON Updater Flow', () => {
  let hostElement: HTMLDivElement;
  let spread: GC.Spread.Sheets.Workbook;
  let sheet: GC.Spread.Sheets.Worksheet;

  beforeEach(() => {
    hostElement = document.createElement('div');
    document.body.appendChild(hostElement);

    spread = new GC.Spread.Sheets.Workbook(hostElement);
    sheet = spread.getActiveSheet();
    setupSpreadTable(sheet);
  });

  afterEach(() => {
    spread.destroy();
    hostElement.remove();
  });

  it('should trigger ValueChanged and run the JSON updater when a cell is edited', () => {
    // Executes editCell command: fires ValueChanged, marks sheet dirty, and runs updaters
    spread.commandManager().execute({
      cmd: 'editCell',
      sheetName: sheet.name(),
      row: 0,
      col: 1,
      newValue: 'Updated Value',
      autoFormat: true
    });

    // Assert that your updater function synchronized the change to your JSON store
    expect(getMyJsonState()[0].name).toBe('Updated Value');
  });

  it('should update JSON on table row additions/deletions', () => {
    const table = sheet.tables.findByName('OrdersTable');

    // insertRows triggers table structure changes and dirty flags
    table.insertRows(table.dataRange().rowCount, 1, true);

    expect(getMyJsonState().length).toBe(2);
  });
});

This approach allows you to verify that SpreadJS interactions execute the expected updater functions and synchronize changes with your JSON store.

Question 2: How Can Performance Be Improved with Multiple or Heavy SpreadJS Tables?

Pages that become sluggish with multiple tables, dropdowns, checkboxes, and row modifications typically suffer from canvas repaint thrashing, redundant formula engine recalculations, or per-cell object instantiations.

The following are some of the highest-impact optimizations to consider.

1. The Paint, Calc, and Event Suspension Pattern (Crucial)

By default, any change made to a cell, style, or row forces an immediate canvas repaint and a recalculation of cell dependencies.

When adding/deleting rows or updating data in bulk, suspend painting, calculations, and events to avoid unnecessary processing.

Example:

// 1. Suspend canvas painting, calculation service, and event cascade

spread.suspendPaint();
sheet.suspendCalcService(false); // false = pause calculations without running immediately
spread.suspendEvent();

try {
  // 2. Perform your batch changes (e.g. bulk setArray, row additions)
  sheet.setArray(startRow, 0, newRows2DArray);
} finally {
  // 3. Resume in reverse order
  spread.resumeEvent();
  sheet.resumeCalcService(true); // true = single consolidated calculation
  spread.resumePaint();          // single canvas repaint
}

This approach reduces repeated rendering and calculation operations by processing multiple changes together.

2. Architecture: Migrate High-Volume Grid Views to TableSheet

If your page is displaying multiple data-entry tables (CRUD, dropdowns, checkboxes, JSON syncing) rather than unconstrained Excel worksheets, MESCIUS strongly recommends considering TableSheet.

Why?

Standard Worksheet tables use the full spreadsheet engine, creating separate cell models and listeners for every coordinate.

TableSheet is powered by an optimized DataManager engine with true column/row virtualization, built-in change tracking, and smooth rendering for large datasets.

Native Checkboxes and Lookups (Zero Per-Cell Overhead):

import '@grapecity/spread-sheets-tablesheet';

const dataManager = spread.dataManager();
const orderTable = dataManager.addTable("orderTable", {
  data: myJsonArray
});

// Define schema once: checkboxes and dropdowns are virtualized natively
const view = orderTable.addView("ordersView", [
  { value: "id", caption: "ID", width: 60 },
  {
    value: "isCompleted",
    caption: "Done",
    width: 80,
    style: {
      cellType: new GC.Spread.Sheets.CellTypes.CheckBox()
    }
  },
  {
    value: "status",
    caption: "Status",
    width: 140,
    lookup: ["Pending", "In Review", "Done"]
  } // Built-in performant dropdown
]);

view.fetch().then(() => {
  const tableSheet = spread.addSheetTab(
    0,
    "Orders",
    GC.Spread.Sheets.SheetType.tableSheet
  );

  tableSheet.setDataView(view);
});

3. Reuse CellType Instances Across Columns

If you are using standard Worksheet tables, avoid creating new CellType instances inside row-generation loops.

Anti-pattern: Calling new GC.Spread.Sheets.CellTypes.ComboBox() inside row-generation loops creates thousands of distinct instances and event handlers.

Best Practice: Create one cell type definition and assign it to the entire column using row index -1.

const sharedCombo = new GC.Spread.Sheets.CellTypes.ComboBox();

sharedCombo.items([
  'Option 1',
  'Option 2',
  'Option 3'
]);

// Row index -1 targets all rows in column 2 efficiently

sheet.getRange(-1, 2, -1, 1).cellType(sharedCombo);

This avoids repeatedly creating equivalent cell type objects and event handlers.

4. Bulk Updates with setArray() Instead of setValue() Loops

Avoid nested loops of sheet.setValue() when updating large amounts of data.

Use sheet.setArray(row, col, 2dArray) or sheet.setDataSource() to load records directly into memory in a single operation.

Example:

sheet.setArray(row, col, newRows2DArray);

This reduces the overhead associated with performing individual cell updates.

Helpful Official Articles & Documentation Links:

End to End Testing (E2E) for a SpreadJS Application (Official Blog): https://developer.mescius.com/blogs/end-to-end-testing-for-a-spreadjs-application

GC.Spread.Sheets.findControl() API Reference: https://developer.mescius.com/spreadjs/api/v16/modules/GC.Spread.Sheets#findcontrol

Worksheet.getCellRect() API Reference: https://developer.mescius.com/spreadjs/api/v16/classes/GC.Spread.Sheets.Worksheet#getcellrect

Forum Discussion: Testing Spreadsheet in React/Jest: https://developer.mescius.com/forums/spreadjs/testing-spreadsheet-in-reactjs

Performance Optimization Guides:

How to Optimize SpreadJS Spreadsheet Component Performance: https://developer.mescius.com/blogs/how-to-optimize-spreadjs-spreadsheet-component-performance

How to Use SpreadJS DataManager to Bind TableSheets to Multiple Views: https://developer.mescius.com/blogs/how-to-use-datamanager-to-create-multiple-views-and-bind-tablesheets

SpreadJS TableSheet Architecture Documentation: https://developer.mescius.com/spreadjs/docs/features/tablesheet/overview

Conclusion

When working with SpreadJS applications, separating updater logic from spreadsheet interactions allows you to test JSON transformations independently while using integration tests to validate the complete interaction between SpreadJS and your application.

For performance optimization, suspending painting, calculations, and events during bulk updates, migrating high-volume data views to TableSheet, reusing CellType instances, and using setArray() instead of individual setValue() calls can help improve rendering and data-update performance.