Documentation
Back to the documentation

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

<html>
<script src="https://jspreadsheet.com/v9/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v9/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>

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

// Create the spreadsheet
var table = jspreadsheet(document.getElementById('spreadsheet'), {
    toolbar: [
        ['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'],
    ],
    worksheets: [{
        data: data,
        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,
    }]
});
</script>

<button type="button" onclick="table[0].setMerge('A3', 2, 3);">Merge cell A3 (colspan: 2, rowspan: 3)</button>
<button type="button" onclick="table[0].removeMerge('A3');">Destroy merge from A3</button>
<button type="button" onclick="document.getElementById('console').innerHTML = JSON.stringify(table[0].getMerge());">Get all merged cells</button>
<button type="button" onclick="table[0].destroyMerge();">Destroy all merged</button>

</html>


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