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.

What's new with Version 13

onchangeconfig: now (worksheet, config, spreadsheetLevel), the worksheet first like every other event

Serialization: getConfig never includes personal view state such as the selection, and column and row styles serialize as one entry on the column or row. A browser and a headless server produce byte-identical documents

setConfig: a partial configuration only changes the given properties, minDimensions growth is structural on every environment, and invalid input returns false

Documentation

Methods

The following methods allow programmatic manipulation of configuration settings.

Method	Description
`getConfig`	Get the configuration of one worksheet. Personal view state such as the selection never serializes, and column and row styles serialize as one entry on the column or row. `getConfig(spreadsheetLevel?: Boolean) => Object`
`setConfig`	Set the configuration for the worksheet. A partial configuration only changes the given properties; an invalid JSON string or a non-object returns `false`. `setConfig(options: Object \| String, level?: Boolean) => void \| false`

Events

Method	Description
`onchangeconfig`	Triggered when configuration changes occur. `onchangeconfig(worksheet: Object, config: Object, 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
`secureFormulas?: boolean`	Transform formulas to capital case. Default: true
`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
`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/v13/jspreadsheet.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v13/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: 100%; min-width: 300px; height: 100px;"></textarea><br>

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

<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('Y2E0NDZkYjNlYTk3MTM5YzQ4NzYzZTc3MmI0MjM0Njg4MzlmNTQwNzVmY2Q3OTJjM2Q4NWVhZTNjMjI5NWEwZjY2YzJiZGIyNmU5NjUwYjlmMDNlMmI3YWU0MjQyYjFmMzBiOTk1MmJjMzJiODJjZmVhNjgzZmFjOThlZjNlMmYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94Tnprd01EVXlORE01TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

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";

// 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('Y2E0NDZkYjNlYTk3MTM5YzQ4NzYzZTc3MmI0MjM0Njg4MzlmNTQwNzVmY2Q3OTJjM2Q4NWVhZTNjMjI5NWEwZjY2YzJiZGIyNmU5NjUwYjlmMDNlMmI3YWU0MjQyYjFmMzBiOTk1MmJjMzJiODJjZmVhNjgzZmFjOThlZjNlMmYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94Tnprd01EVXlORE01TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

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());
    // 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';

// 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('Y2E0NDZkYjNlYTk3MTM5YzQ4NzYzZTc3MmI0MjM0Njg4MzlmNTQwNzVmY2Q3OTJjM2Q4NWVhZTNjMjI5NWEwZjY2YzJiZGIyNmU5NjUwYjlmMDNlMmI3YWU0MjQyYjFmMzBiOTk1MmJjMzJiODJjZmVhNjgzZmFjOThlZjNlMmYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94Tnprd01EVXlORE01TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

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';

// 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('Y2E0NDZkYjNlYTk3MTM5YzQ4NzYzZTc3MmI0MjM0Njg4MzlmNTQwNzVmY2Q3OTJjM2Q4NWVhZTNjMjI5NWEwZjY2YzJiZGIyNmU5NjUwYjlmMDNlMmI3YWU0MjQyYjFmMzBiOTk1MmJjMzJiODJjZmVhNjgzZmFjOThlZjNlMmYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94Tnprd01EVXlORE01TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

// 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);
  }
}