DocumentationTables

Worksheet Tables

Worksheet tables define named regions with headers and filters. Full compatibility with other spreadsheet software allows users to create calculations using specific syntax to reference these structured data regions. A table follows the structure of the worksheet: inserting, deleting or moving rows and columns shifts, grows or shrinks its range, with exact undo, and table names are unique across the workbook.

What's new with Version 13

  • Structural changes: tables follow inserts, deletes and moves of rows and columns with exact undo, where version 12 could crash on structural changes near a table
  • Unique names: creating a table with a name owned by another worksheet is refused

Documentation

Methods

Methods available for programmatic table interaction:

Method Description
getTable(name?) Without arguments, return the tables of the worksheet, or undefined when it has none. With a name, return that table from any worksheet of the spreadsheet, or undefined when it does not exist.
getTable(name?: string) => Table[] | Table | undefined
setTable(config) Create or update one or more tables. name and range are required. A name already used in the same worksheet replaces that table; a name used in another worksheet is refused. Refused entries are skipped. Returns false when the worksheet is not editable or no entry was applied.
setTable(config: Table | Table[]) => void | false
resetTable(name) Remove the named table, with its formatting and filters. Returns false when the worksheet is not editable.
resetTable(name: string) => void | false

Settings

Property Description
tables: Table[] Define structured table ranges within the worksheet

Table[]

Property Description
type?: 'table' Table type identifier
name: string Unique table identifier across the spreadsheet. Required
range: string Cell range definition (e.g., 'A1:D10'). Required
headerRow?: boolean Treat the first row of the range as the column names. Without it, the first row is data and the names come from headers. Default: false
totalRow?: boolean Mark the last row of the range as the total row, writing Total in its first cell when empty
bandedRows?: boolean Apply alternating row background colors
firstColumn?: boolean Highlight the first column
lastColumn?: boolean Highlight the last column
filters?: boolean Enable dropdown filters in header row

Examples

<html>
<script src="https://jspreadsheet.com/v13/jspreadsheet.js"></script>
<script src="https://jsuites.net/v6/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v13/jspreadsheet.css" type="text/css" />
<link rel="stylesheet" href="https://jsuites.net/v6/jsuites.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />

<div id="spreadsheet"></div>

<script>
// You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox.
// The license is valid for one day, after which the spreadsheet will become read-only.
// For a longer trial period, you can create a free account and generate a demo license with an extended expiration date.
jspreadsheet.setLicense('NWEzODM4NjI2NTQ0MDE3MDRmN2JiZGJhZTI1ZTBmYjg5MDJhOTkxMzE2NDU1OGZhM2Y1NTVlMzA1YzlmNDkzMjA2YmIwOTc2MmY0MTBjMjZjYzExNDU3NjgwMWYyYTY4Y2JmNzllYjM3MGJkNmRiZjI5NTcyZmQ0MjBlNWIwYmIsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpreU16YzFNVGcyTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

// Create the spreadsheet
let spreadsheet = jspreadsheet(document.getElementById('spreadsheet'), {
    tableOverflow: true,
    tableWidth: 700,
    worksheets: [{
        minDimensions: [10,10],
        tables: [{
            name: 'TABLE1',
            range: 'B2:E7',
            bandedRows: true,
            headerRow: true,
            filters: true,
        }]
    }]
});
</script>
</html>

import React, { useRef } from "react";
import { Spreadsheet, Worksheet } from "@jspreadsheet/react";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox.
// The license is valid for one day, after which the spreadsheet will become read-only.
// For a longer trial period, you can create a free account and generate a demo license with an extended expiration date.
jspreadsheet.setLicense('NWEzODM4NjI2NTQ0MDE3MDRmN2JiZGJhZTI1ZTBmYjg5MDJhOTkxMzE2NDU1OGZhM2Y1NTVlMzA1YzlmNDkzMjA2YmIwOTc2MmY0MTBjMjZjYzExNDU3NjgwMWYyYTY4Y2JmNzllYjM3MGJkNmRiZjI5NTcyZmQ0MjBlNWIwYmIsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpreU16YzFNVGcyTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

export default function App() {
    const spreadsheet = useRef();

    // Table configuration
    const tables = [{
        name: 'TABLE1',
        range: 'B2:E7',
        bandedRows: true,
        headerRow: true,
        filters: true,
    }];

    // Render react component
    return (
        <Spreadsheet ref={spreadsheet} tableOverflow={true} tableWidth={700}>
            <Worksheet minDimensions={[10,10]} tables={tables} />
        </Spreadsheet>
    );
}

<template>
    <Spreadsheet ref="spreadsheet" :tableOverflow="true" :tableWidth="700">
        <Worksheet :minDimensions="[10,10]" :tables="tables" />
    </Spreadsheet>
</template>

<script>
import { Spreadsheet, Worksheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox.
// The license is valid for one day, after which the spreadsheet will become read-only.
// For a longer trial period, you can create a free account and generate a demo license with an extended expiration date.
jspreadsheet.setLicense('NWEzODM4NjI2NTQ0MDE3MDRmN2JiZGJhZTI1ZTBmYjg5MDJhOTkxMzE2NDU1OGZhM2Y1NTVlMzA1YzlmNDkzMjA2YmIwOTc2MmY0MTBjMjZjYzExNDU3NjgwMWYyYTY4Y2JmNzllYjM3MGJkNmRiZjI5NTcyZmQ0MjBlNWIwYmIsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpreU16YzFNVGcyTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Table configuration
        const tables = [{
            name: 'TABLE1',
            range: 'B2:E7',
            bandedRows: true,
            headerRow: true,
            filters: true,
        }];

        return {
            tables,
        };
    }
}
</script>

import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import "jspreadsheet/dist/jspreadsheet.css"
import "jsuites/dist/jsuites.css"

// You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox.
// The license is valid for one day, after which the spreadsheet will become read-only.
// For a longer trial period, you can create a free account and generate a demo license with an extended expiration date.
jspreadsheet.setLicense('NWEzODM4NjI2NTQ0MDE3MDRmN2JiZGJhZTI1ZTBmYjg5MDJhOTkxMzE2NDU1OGZhM2Y1NTVlMzA1YzlmNDkzMjA2YmIwOTc2MmY0MTBjMjZjYzExNDU3NjgwMWYyYTY4Y2JmNzllYjM3MGJkNmRiZjI5NTcyZmQ0MjBlNWIwYmIsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpreU16YzFNVGcyTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

@Component({
    standalone: true,
    selector: "app-root",
    template: `<div #spreadsheet></div>`,
})
export class AppComponent {
    @ViewChild("spreadsheet") spreadsheet: ElementRef;
    // Worksheets
    worksheets: jspreadsheet.worksheetInstance[];
    // Create a new data grid
    ngAfterViewInit() {
        // Create spreadsheet
        this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
            tableOverflow: true,
            tableWidth: 700,
            worksheets: [{
                minDimensions: [10,10],
                tables: [{
                    name: 'TABLE1',
                    range: 'B2:E7',
                    bandedRows: true,
                    headerRow: true,
                    filters: true,
                }]
            }]
        });
    }
}