Back to the documentation

Spreadsheet 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
onmerge onmerge(worksheet: Object, newValue: Array, oldValue: Array) : void

Initial Settings

The initial merge cells spreadsheet properties.

Property Description
mergeCells: array Allow 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.

<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><br/>
<div id="log"></div><br/>

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

let 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'],
]

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

document.getElementById("setmerge").onclick = () => table[0].setMerge('A3', 2, 3);
document.getElementById("removemerge").onclick = () => table[0].removeMerge('A3');
document.getElementById("getmerge").onclick = () => {
    document.getElementById("log").innerHTML = JSON.stringify(table[0].getMerge());
}
document.getElementById("destroymerge").onclick = () => table[0].destroyMerge();
</script>

<input type="button" value="setMerge('A3', 2, 3)" id="setmerge" />
<input type="button" value="removeMerge('A3')" id="removemerge" />
<input type="button" value="Get all merged cells" id="getmerge" />
<input type="button" value="Destroy all merged" id="destroymerge" />

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