SpreadASP Excel Copy/Paste

Spread for ASP.NET is a useful component for providing Spreadsheet functionality on a web server. While Spread has many great features, it might be beneficial for a user to paste cell ranges into the Spread component, without having to worry about reformatting the cells or writing any code. In this tutorial, we will create an ASP Web Application that allows the users to paste cell ranges from Excel into Spread while keeping the formatting. Download this project: ExcelSpreadCopyPaste To start off, we must create an ASP Web Application and add a Spread component to a web page. For more information about adding a Spread component, see the tutorial here: http://sphelp.grapecity.com/WebHelp/SpreadNet8/ASP2/webframe.html#spweb-startvs2013.html

Set Up the Page

Once the default page has been set and a Spread component has been added to it, specify a “ContentEditable” div above the Spread component like the code below:

<h1>Excel-Spread Copy-Paste</h1>  
<p>You can copy cells from an Excel file and put them into the Spreadsheet below!</p>  
<div class="toggle" id="contentEditor" contenteditable style="background: #ffffff">  
    <p>Paste cells here!</p>  
</div>  
<FarPoint:FpSpread ID="FpSpread1" runat="server" BorderColor="#A0A0A0" BorderStyle="Solid" BorderWidth="1px" Height="400px" Width="1000px">  
    <commandbar backcolor="#F6F6F6" buttonfacecolor="Control" buttonhighlightcolor="ControlLightLight" buttonshadowcolor="ControlDark">  
    </commandbar>  
    <sheets>  
        <FarPoint:SheetView SheetName="Sheet1">  
        </FarPoint:SheetView>  
    </sheets>  
</FarPoint:FpSpread>  

The web page should now look like the following screen shot: The default page with content editable div and Spread on it.

Add Spread Code

Add a script to the web page for the client-side code, and define the “window.onload” function:

<script lang=”javascript” type=”text/javascript”>  

window.onload = function () {  

}  

</script>  

The rest of the code for this tutorial will be inside of this function. Define the Spread component in the script by getting the element on the page:

var spread = document.getElementById("<%=FpSpread1.ClientID %>");

The “ContentEditable” div is where the Excel cell ranges are going to be pasted, so define the function for the “onPaste” event:

$('[contenteditable]').on('paste', function (e) {}):

The rest of the code for this tutorial will be defined within the function specified above. To get the clipboard data to paste into Spread, define a “text” variable:

var text = (e.originalEvent || e).clipboardData.getData('text/html');

A new div element will be created on the page to help with merging the data into spread:

var newDiv = document.createElement("div");  
newDiv.innerHTML = text;  
newDiv.id = "spreadTable";  
document.getElementById("contentEditor").appendChild(newDiv);

Define a function called “addToSpread” that will handle adding the content from that new div to the Spread component with the correct formatting. For simplicity, define a variable called “table” in this function to refer to the table containing the data and formatting from Excel:

var table = document.getElementById('spreadTable').getElementsByTagName('table')[0];

Go through the table to get the values and properties to set in Spread using For loops:

for (var i = 0, row; row = table.rows[i]; i++) {  
    for (var j = 0, cell; cell = row.cells[j]; j++) {}  
}

Within the inner For loop, set the value of the cells in Spread to the table values:

spread.SetValue(I, j, cell.textContent);

Set the column width to match the table:

if (cell.width > 0) {  
    spread.setColWidth(j, cell.width);  
}

Get the rules of the css in order to get the background and foreground colors of cells in the table and apply them to the Spread component:

var rules = document.styleSheets.item(4);  
rules = rules.cssRules || rules.rules;  
for (var k = 0; k < rules.length; k++) {  
    if (rules.item(k).selectorText == "." + cell.className) {  
        var currentCell = spread.Cells(i, j);  
        currentCell.SetBackColor(rules.item(k).style.backgroundColor);  
        currentCell.SetForeColor(rules.item(k).style.color);  
        break;  
    }  
}

Set the height of the rows for both the rows and row headers:

if (cell.height > 0) {  
    var spreadRowHeader = document.getElementById('MainContent\_FpSpread1\_rh');  
    if (spreadRowHeader.rows[i].style.height != null) {  
        spreadRowHeader.rows[i].style.height = cell.height + 'px';  
    }  
    var spreadTable = document.getElementById('MainContent\_FpSpread1\_viewport');  
    if (spreadTable.rows[i].style.height != null) {  
        spreadTable.rows[i].style.height = cell.height + 'px';  
    }  
}

Outside of the For loops, set the range selected in the Spread component to the cells just pasted in:

spread.SetSelectedRange(0, 0, i, j);

Then call the function just defined and hide the div element used to paste cells:

addToSpread();  
var removeDiv = document.getElementById('contentEditor');  
if (removeDiv) {  
    removeDiv.hidden = true;  
}

A user should be able to copy cells from an Excel file and paste them into the “ContentEditable” div on the page. Once they do so, the cells will be copied into the Spread component with some formatting applied to them. This is just one example of how Spread can provide an easy way for users to interact with Spread with no coding knowledge required. The Excel file that was copied from. The Spread page with the copied Excel cell range pasted into it.