Merged cells

This section covers creating and managing merged cells in your online spreadsheets.

Documentation

Methods

The following methods help to deal with the merged cells programmatically.
Method Description
setMerge Set the defined merged cell by the number of columns and rows given.
setMerge(cells: String|Object, colspan?: Number, rowspan?: number) : void
@Param {string} cellName, for example A1, A2. Can be used as an object to apply multiple merge operations.
@Param {number?} colspan - Number of columns
@Param {number?} rowspan - Number of rows
getMerge Get a merged cell or all merge cells.
getMerge(cells?: String) : mixed
@param {string?} Cell name such as A1 or null to return all merged cells.
@return {string|null} - cell name or null for all cells with merge properties.
removeMerge Destroy the merged cells by the cell name.
removeMerge(cells: String|Object) : null
@param {string|Object} - Cell name, such as A1 or an object with all cell names. For example: { A1: true, D1: true }
destroyMerge Destroy all merged cells
destroyMerge() : null


Events

Spreadsheet merge cells related events.
Method Description
onmergeonmerge(worksheet: Object, newValue: Array, oldValue: Array) : void


Initial Settings

The initial merge cells spreadsheet properties.
PropertyDescription
mergeCells: arrayAllow the user to define the initial default merged cells.


Known limitations

Merged cells over hidden or frozen rows and columns can cause unexpected results. We expect to handle exceptions in future releases.


Examples

A basic example of how to initiate and programmatically change the merged cells definitions.




The methods for merged cell management are: setMerge, getMerge, removeMerge and destroyMerge





Open the merged cells example on JSFiddle.

Source code

JavaScript example

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

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

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

<input type="text" value="setMerge('A3', 2, 3)" onclick="table[0].setMerge('A3', 2, 3);" />
<input type="text" value="removeMerge('A3')" onclick="table[0].removeMerge('A3');" />
<input type="text" value="Get all merged cells" onclick="log.value = JSON.stringify(table[0].getMerge());" />
<input type="text" value="Destroy all merged" onclick="table[0].destroyMerge();" />

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

// Create the spreadsheet
var table = jspreadsheet(document.getElementById('spreadsheet'), {
    toolbar: true,
    worksheets: [{
        data: [
            ['Mazda', 2001, 2000, '2006-01-01 12:00:00'],
            ['Peugeot', 2010, 5000, '2005-01-01 13:00:00'],
            ['Honda Fit', 2009, 3000, '2004-01-01 14:01:00'],
            ['Honda CRV', 2010, 6000, '2003-01-01 23:30:00'],
        ],
        columnDrag: true,
        worksheetName: 'Merged Cells',
        minDimensions: [50, 5000],
        tableOverflow: true,
        tableWidth: '800px',
        tableHeight: '300px',
        columns: [
            {
                type: 'text',
                width: '300px',
                title: 'Model',
            },
            {
                type: 'text',
                width: '80px',
                title: 'Year',
            },
            {
                type: 'text',
                width: '100px',
                title: 'Price',
            },
            {
                type: 'calendar',
                width: '150px',
                title: 'Date',
                options: {
                    format: 'DD/MM/YYYY HH24:MI',
                    time: 1,
                }
            },
        ],
        mergeCells: {
            A1: [2, 2]
        }
    }]
});
</script>
</html>

React example

import React, { useRef } from "react";
import { Spreadsheet, Worksheet } from "@jspreadsheet/react";

const license = 'MTRkNWE3YWU3NGY5YjRjNGVmYTIxYTc0M2E1ZjkzM2I1ZmRkNzMxOTI2MjNmZjc3MjQ5MjgyN2M2NTQ2N2Y3MGNjZWIzMjRkZDZkMDY3MmJhMzY0YTg1MTFjMzllNzg3ZWQwNWE3YThhM2M3MjE4MjUwMjEzY2E2YmIzOWViZGMsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTROVGczTlRnME9Dd2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09';

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

    const worksheets = [{
        data: [
            ['Mazda', 2001, 2000, '2006-01-01 12:00:00'],
            ['Peugeot', 2010, 5000, '2005-01-01 13:00:00'],
            ['Honda Fit', 2009, 3000, '2004-01-01 14:01:00'],
            ['Honda CRV', 2010, 6000, '2003-01-01 23:30:00'],
        ],
        columnDrag: true,
        worksheetName: 'Merged Cells',
        minDimensions: [50, 5000],
        tableOverflow: true,
        tableWidth: '800px',
        tableHeight: '300px',
        columns: [
            {
                type: 'text',
                width: '300px',
                title: 'Model',
            },
            {
                type: 'text',
                width: '80px',
                title: 'Year',
            },
            {
                type: 'text',
                width: '100px',
                title: 'Price',
            },
            {
                type: 'calendar',
                width: '150px',
                title: 'Date',
                options: {
                    format: 'DD/MM/YYYY HH24:MI',
                    time: 1,
                }
            },
        ],
        mergeCells: {
            A1: [2,2]
        },
        columnDrag: true,
    }]

    return (
        <>
            <Spreadsheet ref={spreadsheet} license={license} worksheets={worksheets} />
            <div ref={log}></div>
            <input type="text" value="setMerge('A3', 2, 3)" onclick="spreadsheet.current[0].setMerge('A3', 2, 3);" />
            <input type="text" value="removeMerge('A3')" onclick="spreadsheet.current[0].removeMerge('A3');" />
            <input type="text" value="Get all merged cells"
                onclick="log.current.value = JSON.stringify(spreadsheet.current[0].getMerge());" />
            <input type="text" value="Destroy all merged" onclick="spreadsheet.current[0].destroyMerge();" />
        </>
    )
}

Vue example

<template>
    <Spreadsheet ref="spreadsheet" :license="license" :worksheets="worksheets"/>
    <div ref="log"></div>
    <input type="text" value="setMerge('A3', 2, 3)" onclick="this.$refs.spreadsheet.current[0].setMerge('A3', 2, 3);" />
    <input type="text" value="removeMerge('A3')" onclick="this.$refs.spreadsheet.current[0].removeMerge('A3');" />
    <input type="text" value="Get all merged cells"
        onclick="this.$refs.log.value.value = JSON.stringify(this.$refs.spreadsheet.current[0].getMerge());" />
    <input type="text" value="Destroy all merged" onclick="this.$refs.spreadsheet.current[0].destroyMerge();" />
</template>

<script>
import { Spreadsheet, Worksheet } from "@jspreadsheet/vue";
import formula from "@jspreadsheet/formula-pro";

const license = 'MTRkNWE3YWU3NGY5YjRjNGVmYTIxYTc0M2E1ZjkzM2I1ZmRkNzMxOTI2MjNmZjc3MjQ5MjgyN2M2NTQ2N2Y3MGNjZWIzMjRkZDZkMDY3MmJhMzY0YTg1MTFjMzllNzg3ZWQwNWE3YThhM2M3MjE4MjUwMjEzY2E2YmIzOWViZGMsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTROVGczTlRnME9Dd2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09';

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        const worksheets = [{
            data: [
                ['Mazda', 2001, 2000, '2006-01-01 12:00:00'],
                ['Peugeot', 2010, 5000, '2005-01-01 13:00:00'],
                ['Honda Fit', 2009, 3000, '2004-01-01 14:01:00'],
                ['Honda CRV', 2010, 6000, '2003-01-01 23:30:00'],
            ],
            columnDrag: true,
            worksheetName: 'Merged Cells',
            minDimensions: [50, 5000],
            tableOverflow: true,
            tableWidth: '800px',
            tableHeight: '300px',
            columns: [
                {
                    type: 'text',
                    width: '300px',
                    title: 'Model',
                },
                {
                    type: 'text',
                    width: '80px',
                    title: 'Year',
                },
                {
                    type: 'text',
                    width: '100px',
                    title: 'Price',
                },
                {
                    type: 'calendar',
                    width: '150px',
                    title: 'Date',
                    options: {
                        format: 'DD/MM/YYYY HH24:MI',
                        time: 1,
                    }
                },
            ],
            mergeCells: {
                A1: [2,2]
            },
            columnDrag: true,
        }]

        return {
            worksheets,
            license,
        };
    }
}
</script>

Angular example

import { Component, ViewChild, ElementRef } from "@angular/core";
import * as jspreadsheet from "jspreadsheet";
import formula from "@jspreadsheet/formula-pro";

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

// Set the extension
jspreadsheet.setExtensions({ formula });

// Create component
@Component({
    selector: "app-root",
    template: `<div #spreadsheet></div>
        <div #log></div>
        <input type="text" value="setMerge('A3', 2, 3)" onclick="this.worksheets[0].setMerge('A3', 2, 3);" />
        <input type="text" value="removeMerge('A3')" onclick="this.worksheets[0].removeMerge('A3');" />
        <input type="text" value="Get all merged cells"
            onclick="this.log.nativeElement.value = JSON.stringify(this.worksheets[0].getMerge());" />
        <input type="text" value="Destroy all merged" onclick="this.worksheets[0].destroyMerge();" />`;
})
export class AppComponent {
    @ViewChild("spreadsheet") spreadsheet: ElementRef;
    @ViewChild("log") log: ElementRef;
    // Worksheets
    worksheets: jspreadsheet.worksheetInstance[];
    // Create a new data grid
    ngOnInit() {
        // Create spreadsheet
        this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
            toolbar: true,
            worksheets: [{
                data: [
                    ['Mazda', 2001, 2000, '2006-01-01 12:00:00'],
                    ['Peugeot', 2010, 5000, '2005-01-01 13:00:00'],
                    ['Honda Fit', 2009, 3000, '2004-01-01 14:01:00'],
                    ['Honda CRV', 2010, 6000, '2003-01-01 23:30:00'],
                ],
                columnDrag: true,
                worksheetName: 'Merged Cells',
                minDimensions: [50, 5000],
                tableOverflow: true,
                tableWidth: '800px',
                tableHeight: '300px',
                columns: [
                    {
                        type: 'text',
                        width: '300px',
                        title: 'Model',
                    },
                    {
                        type: 'text',
                        width: '80px',
                        title: 'Year',
                    },
                    {
                        type: 'text',
                        width: '100px',
                        title: 'Price',
                    },
                    {
                        type: 'calendar',
                        width: '150px',
                        title: 'Date',
                        options: {
                            format: 'DD/MM/YYYY HH24:MI',
                            time: 1,
                        }
                    },
                ],
                mergeCells: {
                    A1: [2, 2]
                }
            }]
        });
    }
}


Batch operations

The batch operation enables you to apply with single command multiple merged operations.

Example

// To apply set merge in multiple cells at the same time
instance.setMerge({ A1: [2,2], E1: [2,2] });

// To remove merge in multiple cells at the same time
instance.removeMerge({ A1: true, E1: true });

Releases notes

Differences from version 9


worksheet.updateMergeThis method is deprecated.
worksheet.mergedInternal merge controllers have been updated.