Data Grid Settings

JSpreadsheet offers extensive configuration options to customize spreadsheet functionality, appearance, and behaviour. Configuration has two levels: global settings (spreadsheet-level) that affect the entire instance, and individual settings (worksheet-level) that control specific worksheets.

Documentation

Methods

The following methods allow programmatic manipulation of configuration settings.

Method	Description
`getConfig`	Get the configuration of one worksheet. `getConfig(spreadsheetLevel?: Boolean) => Object`
`setConfig`	Set the configuration for the worksheet `setConfig(options: Object, level?: Boolean) => void`

Events

Method	Description
`onchangeconfig`	Triggered when configuration changes occur. `getConfig(config: string, spreadsheetLevel?: boolean) => void`

Spreadsheet Configuration

These settings apply to the entire spreadsheet instance and affect all worksheets within it.

Property	Description
`application?: string`	Your application name
`guid?: string`	Remote configuration with Jspreadsheet Server
`root?: HTMLElement`	DOM element for binding JavaScript events. Used when JSS runs as a web component.
`definedNames?: Record<string, string>`	Global defined names for range variables
`sorting?: Function`	Global sorting handler function
`toolbar?: boolean \| Toolbar \| ToolbarItem[]`	Enable and configure toolbar. Default: false
`editable?: boolean`	Allow spreadsheet edition. Default: true
`allowExport?: boolean`	Allow data export. Default: true
`includeHeadersOnDownload?: boolean`	Include headers in exported data. Default: true
`forceUpdateOnPaste?: boolean`	Force update on paste for read-only cells. Default: false
`fullscreen?: boolean`	Render in fullscreen mode. Default: false
`debugFormulas?: boolean`	Enable formula debugging. Default: false
`parseFormulas?: boolean`	Execute formulas. Default: true
`editorFormulas?: boolean`	Enable formula editor. Default: true
`keyboardFormulas?: boolean`	Enable formula picker with keyboard. Default: true
`autoIncrement?: boolean`	Auto increment when copying cells. Default: true
`autoCasting?: boolean`	Auto cast numbers in formulas. Default: true
`parseHTML?: boolean`	Parse HTML in cell values. Default: false
`bar?: boolean`	Show extension bar. Default: true
`tabs?: boolean \| Tabs`	Enable worksheet tabs. Default: false
`allowDeleteWorksheet?: boolean`	Allow deleting worksheets. Default: true
`allowRenameWorksheet?: boolean`	Allow renaming worksheets. Default: true
`allowMoveWorksheet?: boolean`	Allow moving worksheets. Default: true
`moveDownOnEnter?: boolean`	Move cursor down on Enter. Default: true
`contextMenu?: Contextmenu \| null`	Custom context menu configuration
`about?: string`	About information
`worksheets?: Worksheet[]`	Array of worksheet configurations
`validations?: Validation[]`	Global validation rules
`plugins?: Record<string, Plugin>`	Plugin configurations
`style?: string[]`	Global style definitions
`snapToGrid?: boolean`	Snap cells to grid when scrolling. Default: false
`spacing?: number`	Space between table and container end. Default: 100
`preCalculation?: boolean`	Pre-calculate formulas and cache results. Default: true
`columnNamesInFormulas?: boolean`	Use column names in formulas. Default: true
`namespace?: string`	Namespace for cross-calculation conflict resolution
`international?: International`	International configuration settings
`persistence?: Function`	Persistence handler function
`autoSelect?: boolean`	Create selection during openWorksheet. Default: true
`autoId?: boolean`	Assign GUID to row ID when not provided. Default: false

Worksheet Configuration

These settings apply to individual worksheets and can be configured separately for each worksheet within a spreadsheet.

Property	Description
`url?: string`	Load data from external server URL
`data?: Array<Array<any>> \| Array<Record<string, any>>`	Load data from array of rows or objects
`rows?: Row[] \| Record<number, Row>`	Row properties definitions (title, height, etc.)
`columns?: Array<Column>`	Column properties and editor definitions
`cells?: Record<string, Cell>`	Cell properties that override column definitions
`nestedHeaders?: Array<Array<Nested>>`	Nested headers configuration
`defaultColWidth?: number \| string`	Default column width. Default: 50px
`defaultRowHeight?: number \| string`	Default row height. Default: null
`minSpareRows?: number`	Minimum spare rows. Default: 0
`minSpareCols?: number`	Minimum spare columns. Default: 0
`minDimensions?: [number, number]`	Minimum table dimensions [columns, rows]
`csv?: string`	CSV data source URL
`csvFileName?: string`	Default filename for exports. Default: worksheetName
`csvHeaders?: boolean`	First line as header. Default: false
`csvDelimiter?: string`	CSV delimiter. Default: ','
`columnSorting?: boolean`	Allow column sorting
`columnSortingOnDblClick?: boolean`	Sort on double-click header. Default: true
`columnDrag?: boolean`	Allow column dragging
`columnResize?: boolean`	Allow column resizing
`rowResize?: boolean`	Allow row resizing
`rowDrag?: boolean`	Allow row dragging
`editable?: boolean`	Allow table editing
`allowInsertRow?: boolean`	Allow inserting rows
`allowManualInsertRow?: boolean`	Allow rows via Tab key. Default: true
`allowInsertColumn?: boolean`	Allow inserting columns
`allowManualInsertColumn?: boolean`	Allow columns via Enter key. Default: true
`allowDeleteRow?: boolean`	Allow deleting rows. Default: true
`allowDeleteColumn?: boolean`	Allow deleting columns. Default: true
`allowRenameColumn?: boolean`	Allow renaming columns. Default: true
`allowComments?: boolean`	Enable cell comments functionality. Default: true
`fillHandle?: boolean`	Enable corner selection and data cloning. Default: true
`mergeCells?: Record<string, [number, number]>`	Merged cells configuration
`search?: boolean`	Allow search functionality
`pagination?: number`	Records per page for pagination
`paginationOptions?: Array<number>`	Page size options [10,25,50,100]
`textOverflow?: boolean`	Text overflow handling. Default: false
`tableOverflow?: boolean`	Enable table overflow scrolling. Default: false
`tableHeight?: number \| string`	Table height (e.g., '300px')
`tableWidth?: number \| string`	Table width (e.g., '800px')
`virtualizationX?: boolean`	Enable column virtualization. Default: true
`virtualizationY?: boolean`	Enable row virtualization. Default: true
`comments?: Record<string, string\|Comment[]>`	Initial comments
`meta?: Record<string, any>`	Initial meta information
`style?: Record<string, string\|number>`	Style definitions
`freezeColumns?: number\|number[]`	Frozen columns [4,5,6]
`freezeRows?: number\|number[]`	Frozen rows [4,5,6]
`freezeColumnControl?: boolean`	Manual freeze column control. Default: false
`freezeRowControl?: boolean`	Manual freeze row control. Default: false
`worksheetId?: string`	Unique worksheet ID
`worksheetName?: string`	Worksheet name
`worksheetState?: 'hidden' \| undefined`	Worksheet visibility state
`filters?: boolean \| string`	Enable column filters
`footers?: any[][]`	Footer configuration
`applyMaskOnFooters?: boolean`	Apply mask on footers. Default: true
`locked?: boolean`	Internal lock controller (use editable instead)
`selectUnLockedCells?: boolean`	Allow selecting unlocked cells. Default: true
`selectLockedCells?: boolean`	Allow selecting locked cells. Default: true
`resize?: 'horizontal' \| 'vertical' \| 'both' \| 'none'`	Enable worksheet resizing. Default: none
`gridline?: boolean`	Show worksheet gridlines. Default: true
`media?: Media[]`	Floating images or charts
`cache?: Record<string, string\|number>`	Cached values for calculations
`zoom?: number`	Zoom level. Default: 1
`autoNames?: boolean`	Detect column names from data. Default: true

Examples

Manual Persistence of Data Grid Configuration

This example illustrates how to retrieve and clone spreadsheet configuration settings to initialize a new spreadsheet instance.

See this example on JSFiddle

<html>
<script src="https://jspreadsheet.com/v12/jspreadsheet.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v12/jspreadsheet.css" type="text/css" />
<script src="https://jsuites.net/v6/jsuites.js"></script>
<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>
<div id="spreadsheet-clone"></div><br>

<textarea id="console" style="width: 600px; height: 100px;"></textarea><br>

<input type="button" value="Clone the data grid above" class="button main" id="btn1" />

<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('OWNmMjA2YzllYjU3NWY1MDc1YTYyMWRlN2UxMzFhYjBjNzA4OWM5YTFiMGRhNTNiN2RkZWFkYzBhYTBlMmVjMzk1NzIyZGRlYmI5NzIxNDgyZDk0ZTQzZDlmY2UyYmJkNWZhNTE4MzY3N2ZlZjNhNmU5NjYxY2IzNzFkZGJkZmMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azJNVFl5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

const clone = function() {
    // Get the data grid configuration
    let config = JSON.stringify(grid[0].parent.getConfig());
    // Show on the textarea
    document.getElementById('console').value = config;
    // Destroy any existing spreadsheet
    jspreadsheet.destroy(document.getElementById('spreadsheet-clone'));
    // Parse
    config = JSON.parse(config);
    // Create a new spreadsheet
    jspreadsheet(document.getElementById('spreadsheet-clone'), config);
}

// Create the JavaScript sample data grid
let grid = jspreadsheet(document.getElementById('spreadsheet'), {
    tabs: true,
    toolbar: true,
    worksheets: [{
        data: [[1,2,3]],
        minDimensions: [6, 6],
    }],
});

document.getElementById('btn1').onclick = clone
</script>
</html>

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

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('OWNmMjA2YzllYjU3NWY1MDc1YTYyMWRlN2UxMzFhYjBjNzA4OWM5YTFiMGRhNTNiN2RkZWFkYzBhYTBlMmVjMzk1NzIyZGRlYmI5NzIxNDgyZDk0ZTQzZDlmY2UyYmJkNWZhNTE4MzY3N2ZlZjNhNmU5NjYxY2IzNzFkZGJkZmMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azJNVFl5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

export default function App() {
  // Spreadsheet array of worksheets
  const spreadsheet = useRef();
  const consoleRef = useRef();
  const copy = useRef();

  // Method to clone the data grid
  const clone = () => {
    // Get the data grid configuration
    let conf = JSON.stringify(
      spreadsheet.current[0].parent.getConfig(),
      (key, value) => {
        if (
          key.startsWith('_') ||
          key.includes('react') ||
          key.includes('fiber')
        ) {
          return undefined;
        }
        return value;
      }
    );
    // Show on the textarea
    consoleRef.current.value = conf;
    // Destroy any existing spreadsheet
    jspreadsheet.destroy(copy.current);
    // Parse
    conf = JSON.parse(conf);
    // Create a new spreadsheet
    jspreadsheet(copy.current, conf);
  };

  return (
    <>
      <Spreadsheet ref={spreadsheet}>
        <Worksheet data={[[1, 2, 3]]} minDimensions={[8, 8]} />
      </Spreadsheet>
      <div ref={copy}></div>
      <textarea ref={consoleRef}></textarea>
      <button onClick={clone}>Clone the data grid above</button>
    </>
  );
}

<template>
  <Spreadsheet ref="spreadsheet" :tabs="true" :toolbar="true">
    <Worksheet :data="[[1, 2, 3]]" :minDimensions="[6, 6]" />
  </Spreadsheet>
  <div ref="copy"></div>
  <br />
  <textarea ref="console"></textarea><br />
  <button @click="clone">Clone the data grid above</button>
</template>

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

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('OWNmMjA2YzllYjU3NWY1MDc1YTYyMWRlN2UxMzFhYjBjNzA4OWM5YTFiMGRhNTNiN2RkZWFkYzBhYTBlMmVjMzk1NzIyZGRlYmI5NzIxNDgyZDk0ZTQzZDlmY2UyYmJkNWZhNTE4MzY3N2ZlZjNhNmU5NjYxY2IzNzFkZGJkZmMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azJNVFl5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

export default {
  components: {
    Spreadsheet,
    Worksheet,
  },
  methods: {
    // Method to clone the data grid
    clone() {
      // Get the data grid configuration
      let config = JSON.stringify(
        this.$refs.spreadsheet.current[0].parent.getConfig()
      );

      // Show on the textarea
      this.$refs.console.value = config;
      // Destroy any existing spreadsheet
      jspreadsheet.destroy(this.$refs.copy);
      // Parse
      config = JSON.parse(config);
      // Create a new spreadsheet
      jspreadsheet(this.$refs.copy, config);
    },
  }
};
</script>

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

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('OWNmMjA2YzllYjU3NWY1MDc1YTYyMWRlN2UxMzFhYjBjNzA4OWM5YTFiMGRhNTNiN2RkZWFkYzBhYTBlMmVjMzk1NzIyZGRlYmI5NzIxNDgyZDk0ZTQzZDlmY2UyYmJkNWZhNTE4MzY3N2ZlZjNhNmU5NjYxY2IzNzFkZGJkZmMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azJNVFl5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Create component
@Component({
  standalone: true,
  selector: 'app-root',
  template: `
        <div #spreadsheet></div>
        <div #copy></div><br>
        <textarea #console></textarea><br>
        <input type="button" value="Clone the data grid above" (click)="this.clone()" />
    `,
})
export class AppComponent implements AfterViewInit {
  @ViewChild('spreadsheet') spreadsheet: ElementRef;
  @ViewChild('console') console: ElementRef;
  @ViewChild('copy') copy: ElementRef;
  // Worksheets
  worksheets: jspreadsheet.worksheetInstance[];
  // Create a new data grid
  ngAfterViewInit() {
    // Create spreadsheet
    this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
      tabs: true,
      toolbar: true,
      worksheets: [
        {
          data: [[1, 2, 3]],
          minDimensions: [6, 6],
        },
      ],
    });
  }

  // Clone the data grid
  clone() {
    // Get the data grid configuration
    let config = this.worksheets[0].parent.getConfig();
    // Show on the textarea
    this.console.nativeElement.value = JSON.stringify(config);
    // Destroy any existing spreadsheet
    jspreadsheet.destroy(this.copy.nativeElement);
    // Create a new spreadsheet
    jspreadsheet(this.copy.nativeElement, config);
  }
}

Edit this page on GitHub