We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. (Last updated on: June 24, 2019).
Unfortunately, activation email could not send to your email. Please try again.
Essential JS2

Syncfusion jQuery based widgets are no longer in active development. Switch to our pure JavaScript based next generation Essential JS 2 library.

Syncfusion Feedback

How to copy and paste the data with cell formats and formula from one Spreadsheet to another Spreadsheet

Platform: jQuery |
Control: ejSpreadSheet |
Published Date: November 26, 2018 |
Last Revised Date: May 22, 2019

Description

This knowledge base explains how to copy and paste the data with cell formats and formula from one Spreadsheet to another.

Solution

In the below sample, external button ‘Click to Copy’ is clicked to copy data with format and formula from Spreadsheet1 using getRangeData method, and ‘Click to Paste’ button is clicked to paste it on Spreadsheet2 by using the updateData client-side method.

 

HTML

 

<input type="button" value="Click to Copy" id="copy" />
<div id="Spreadsheet1"></div>
<input type="button" value="Click to Paste" id="paste" />
<div id="Spreadsheet2"></div>

 

JS

 

$("#Spreadsheet1").ejSpreadsheet({
    // window.defaultData from http://js.syncfusion.com/demos/web/scripts/xljsondata.min.js file.
    sheets: [{ rangeSettings: [{ dataSource: window.defaultData }] }],
    loadComplete: "onLoadComplete"
});
$("#Spreadsheet2").ejSpreadsheet({
    sheets: [{ rangeSettings: [{ dataSource: window.defaultData }] }]
});

 

Razor

 

<input type="button" value="Click to Copy" id="copy" />
@(Html.EJ().Spreadsheet<object>("Spreadsheet1")
    .Sheets(sheet =>
    {
        sheet.RangeSettings(range =>
        {
            <!-- you can define datasource at server side. -->
            range.Datasource((IEnumerable<object>)ViewBag.Datasource).Add();
        }).Add();
    })
    .ClientSideEvents(
        events => events.LoadComplete("onLoadComplete")
    )
)
<input type="button" value="Click to Paste" id="paste" />
@(Html.EJ().Spreadsheet<object>("Spreadsheet2")
    .Sheets(sheet =>
    {
        sheet.RangeSettings(range =>
        {
            <!-- you can define datasource at server side. -->
            range.Datasource((IEnumerable<object>)ViewBag.Datasource).Add();
        }).Add();
    })
)

 

ASPX

 

<input type="button" value="Click to Copy" id="copy" />
<ej:Spreadsheet ID="Spreadsheet1" runat="server">
    <ClientSideEvents LoadComplete="onLoadComplete" />
    <Sheets>
        <ej:Sheet>
            <RangeSettings>
                <!-- you can define datasource at server side. -->
                <ej:RangeSetting StartCell="A1" ShowHeader="true" />
            </RangeSettings>
        </ej:Sheet>
    </Sheets>
</ej:Spreadsheet>
<input type="button" value="Click to Paste" id="paste" />
<ej:Spreadsheet ID="Spreadsheet2" runat="server">
    <Sheets>
        <ej:Sheet>
            <RangeSettings>
                <!-- you can define datasource at server side. -->
                <ej:RangeSetting StartCell="A1" ShowHeader="true" />
            </RangeSettings>
        </ej:Sheet>
    </Sheets>
</ej:Spreadsheet>

 

var range, data, spreadsheetID = "Spreadsheet";
 
//Tigger after loading Spreadsheet1.To apply Format and formula in Spreadsheet1.
function onLoadComplete(args) {
    var xlEdit = this.XLEdit;
    xlEdit.updateValue('C2', '=SQRT(25)');
    xlEdit.updateValue('C3', '=SUM(25,5)');
    this.XLFormat.format({ style: { "background-color": "#C0C0C0" } }, "C1:D4");
    this.XLSelection.selectRange("C1:D4");
}
 
//Trigger while clicking the copy button.
$("#copy").click(function () {
    var excelObj = $("#" + spreadsheetID + "1").data("ejSpreadsheet");
    range = excelObj.getSheet(excelObj.getActiveSheetIndex()).selectedRange;
    data = excelObj.getRangeData({ range: range, property: ["value", "value2", "type", "formatStr", "decimalPlaces", "thousandSeparator", "range", "format", "border", "wrap", "formats"] }); // to get the copied data.
});
 
//Trigger while clicking the paste button.
$("#paste").click(function () {
    var excelObj = $("#" + spreadsheetID + "2").data("ejSpreadsheet");
    if (range) {
        var sheetIndex = excelObj.getActiveSheetIndex(), actRange = excelObj.getActiveCell(sheetIndex), selectedCells,
            rowDiff = range[2] - range[0], colDiff = range[3] - range[1], pasteRange = [actRange.rowIndex, actRange.colIndex, actRange.rowIndex + rowDiff, actRange.colIndex + colDiff];
        excelObj.XLSelection.selectRange(pasteRange); // select the pasted range.
        selectedCells = excelObj.XLSelection.getSelectedCells(sheetIndex);
        for (var i = 0; i < selectedCells.length; i++) {
            cell = selectedCells[i];
            rowIndex = parseInt(cell.parentElement.getAttribute('data-idx'));
            colIndex = cell.cellIndex;
            excelObj.updateData([data[i]], [rowIndex, colIndex, rowIndex, colIndex]); // To paste the copied cells.
        }
        excelObj.updateFormulaBar();
    }
});

 

The below screenshots display the result of above code example,

 

spreadsheet paste sample preview

Figure: Spreadsheet1

spreadsheet paste operation sample preview

Figure: Spreadsheet2

ADD COMMENT
You must log in to leave a comment

Please sign in to access our KB

This page will automatically be redirected to the sign-in page in 10 seconds.

Up arrow icon

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

Live Chat Icon For mobile
Live Chat Icon