Excel to Spread Copy/Paste V9

Spread for ASP.NET is a useful web tool for providing Excel-like functionality to users on a webpage. It might be useful to give users the ability to paste ranges from their Excel Spreadsheets into Spread. While this feature is not currently in Spread for ASP.NET, code can be written to achieve the same functionality. Rather than using purely client-side code to implement the pasting of cells from Excel to Spread, this tutorial sends HTML strings back to the server for parsing to recreate the copied cell range in Spread. Copying a range of cells from Excel and pasting into Spread ASP Copying a range of cells from Excel and pasting into Spread ASP The previous blog about implementing copying/pasting from Excel to Spread using client-side code with Spread Studio V8 can be found here: Excel to Spread Copy/Paste V8 This blog uses the HTMLAgilityPack for parsing through HTML strings in server-side code. The website for that can be found here: HTML Agility Pack To download the sample used in this blog post, see here: ExcelSpreadCopyPasteV9

Adding a Spread Component and Setting up the Page

For a tutorial on adding a Spread component to a web site in Visual Studio 2015, see here: http://sphelp.grapecity.com/WebHelp/SpreadNet9/ASP2/webframe.html#spweb-startvs2015.html After adding a Spread component to the page, add a div that will be used for pasting cells from Excel into Spread. In addition, add a hidden element for use in sending HTML strings back to the server for parsing:


<div>  
    <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="true" style="background:#c7c7c7">  
        <p>Paste cells here!</p>  
        <input id="Hidden1" type="hidden" runat="server" />  
    </div>  
    <FarPoint:FpSpread ID="FpSpread1" runat="server" BorderColor="#A0A0A0" BorderStyle="Solid" BorderWidth="1px" Height="582px" Width="947px" OnButtonCommand="FpSpread1_ButtonCommand">  
        <commandbar backcolor="#F6F6F6" buttonfacecolor="Control" buttonhighlightcolor="ControlLightLight" buttonshadowcolor="ControlDark">  
        </commandbar>  
        <sheets>  
            <FarPoint:SheetView SheetName="Sheet1">  
            </FarPoint:SheetView>  
        </sheets>  
    </FarPoint:FpSpread>  

</div>

Adding Script Code

In order to implement this functionality, some script code needs to be added. The copy/paste works by users copying a cell/cell range from Excel and pasting it into the contenteditable div above the instance of Spread. To account for this, we will need to write a function for the onpaste event of that div:


var newDiv, hiddenDiv;  

window.onload = function () {  
    var spread = document.getElementById("<%=FpSpread1.ClientID %>");  

    document.getElementById("contentEditor").onpaste = function (e) {                
        var text = (e.originalEvent || e).clipboardData.getData('text/html');  

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

        e.preventDefault();  
    }  

}

The way that this works is that the copied cell/cell range from Excel is pasted as an HTML table. Inside that onpaste function, we will need to write code to save the HTML table as a string into the hidden element on the page so that it can be parsed through on the server. To do this, we will write a function to convert the HTML table to a string and a function that sets the value of the hidden field to that string:


function GetHTMLTableString(table) {  
    var tableAttr = table.attributes;  

    var tableString = "<" + table.nodeName.toLowerCase();  

    for (var i = 0; i < tableAttr.length; i++) {  
        tableString += " " + tableAttr[i].name + "='" + tableAttr[i].value + "'";  
    }  

    tableString += ">" + table.innerHTML + "";  
    return tableString;  
}  

function SaveTable() {  
    document.getElementById("Hidden1").value = GetHTMLTableString(newDiv.children[6]);  
}

Going back to the onpaste function, we will add a call to that new function, as well as a callback to the server (which we will implement later) and code to hide the HTML table that was pasted into the contenteditable div:


SaveTable();  

FpSpread1.CallBack("PasteSpread", false);  
if (document.getElementById("contentEditor").children != null) {  
    for (var c = 2; c < document.getElementById("contentEditor").children.length; c++) {  
        document.getElementById("contentEditor").children[c].hidden = true;  
    }  
}

Adding Server-Side Code

The callback function in the script code calls back to the ButtonCommand method in the code-behind for the page. This method will need to read the contents of the hidden element on the page and parse through the HTML table to set specific properties and data in Spread. To do this, we will use the HTMLAgilityPack mentioned earlier. The pack can be downloaded from the website linked or added to the project via the NuGet Package Manager. Once the pack has been downloaded and added to the project, just add a reference to it at the top of the code-behind:


using HtmlAgilityPack;

To start implementing this functionality, create a new HtmlDocument variable that we will use to load the HTML table from the script code. In addition, add some variables to get the table and body from the HtmlDocument:


protected void FpSpread1_ButtonCommand(object sender, FarPoint.Web.Spread.SpreadCommandEventArgs e)  
{  
    if (e.CommandName == "PasteSpread")  
    {  
        FarPoint.Web.Spread.SheetView activeSheet = FpSpread1.ActiveSheetView;  

        HtmlDocument doc = new HtmlDocument();  
        doc.LoadHtml(Hidden1.Value);  

        HtmlNode table = doc.DocumentNode.SelectNodes("//table")[0];  
        HtmlNode body = table.SelectNodes("tbody")[0];  
    }  
}

Now create a FOR loop to parse each row in the table. In addition, set the height of the row from the height attribute in the row’s Attributes property:


for (int r = 0; r < body.SelectNodes("tr").Count; r++)  
{  
    HtmlNode row = body.SelectNodes("tr")[r];  
    foreach (HtmlAttribute style in row.Attributes)  
    {  
        if (style.Name == "height")  
            activeSheet.Rows[r].Height = Convert.ToInt32(style.Value);  
    }  
}

More code can be added to set other properties from the style in that same FOREACH loop:


foreach (HtmlAttribute style in cell.Attributes)  
{  
    if (style.Name == "height")  
        activeSheet.Rows[r].Height = Convert.ToInt32(style.Value);  
    else if (style.Name == "width")  
        activeSheet.Columns[c].Width = Convert.ToInt32(style.Value);  
    else if (style.Name == "align")  
    {  
        if (style.Value.Contains("left"))  
            activeSheet.Cells[r, c].HorizontalAlign = HorizontalAlign.Left;  
        else if (style.Value.Contains("right"))  
            activeSheet.Cells[r, c].HorizontalAlign = HorizontalAlign.Right;  
    }  
}

The code above will read the attributes of the DOM elements in the table and apply them to the cells in the FpSpread component. However, some of the style elements are inside of a STYLE element on the page just above the TABLE element, and it contains information about the different styles for the different classes that are used by the cells in the TABLE. We can access this STYLE element and parse through it in a separate function, and we can call that function both when we read a STYLE or CLASS attribute:


else if (style.Name == "class")  
{  
    className = style.Value;  
    SetStyles(activeSheet, style, className, r, c);  
}  
else if (style.Name == "style")  
{  
    SetStyles(activeSheet, style, className, r, c);  
}

Now we can implement the SetStyles function to actually go through the sheet in the FpSpread component and set the styles for both the attributes in the STYLE element, and the attributes in the STYLES DOM element for the particular CLASS of each cell:


protected void SetStyles(FarPoint.Web.Spread.SheetView activeSheet, HtmlAttribute style, string className, int r, int c)  
{  
    int styleIndex = -1;  
    for (int s = 0; s < stylesClasses.Length; s++)  
    {  
        if (Array.IndexOf(stylesClasses[s], className) != -1)  
            styleIndex = s;  
    }  

    if (className != "")  
    {  
        string[] currentClassStyles = new string[stylesClasses[styleIndex].Length];  
        Array.Copy(stylesClasses[styleIndex], currentClassStyles, stylesClasses[styleIndex].Length);  

        SetStyleAttributes(currentClassStyles, activeSheet, r, c);  
    }  

    string[] styleAttributes = style.Value.Split(new string[] { ";" }, StringSplitOptions.None);  
    SetStyleAttributes(styleAttributes, activeSheet, r, c);  
}

The above code makes calls to the SetStyleAttributes function, which is used to actually set the different properties in the cells from both the Class styles and the properties in the STYLE attribute. In the following code, we parse through the styles and set the correct properties in the cells:


protected void SetStyleAttributes(string[] styleAttributes, FarPoint.Web.Spread.SheetView activeSheet, int r, int c)  
{  
    foreach (string attribute in styleAttributes)  
    {  
        string[] attributeNameValue = attribute.Split(new string[] { ":" }, StringSplitOptions.None);  
        for (int v = 0; v < attributeNameValue.Length; v++)  
        {  
            attributeNameValue[v] = attributeNameValue[v].Replace("\\n", string.Empty);  
            attributeNameValue[v] = attributeNameValue[v].Replace(" ", string.Empty);  
            attributeNameValue[v] = attributeNameValue[v].Replace(";", string.Empty);  
        }  
        if (attributeNameValue[0].Contains("color"))  
        {  
            activeSheet.Cells[r, c].ForeColor = GetColor(attributeNameValue[1]);  
        }  
        else if (attributeNameValue[0].Contains("background"))  
        {  
            activeSheet.Cells[r, c].BackColor = GetColor(attributeNameValue[1]);  
        }  
        else if (attributeNameValue[0].Contains("font-size"))  
        {  
            activeSheet.Cells[r, c].Font.Size = new FontUnit(attributeNameValue[1]);  
        }  
        else if (attributeNameValue[0].Contains("font-family"))  
        {  
            activeSheet.Cells[r, c].Font.Name = attributeNameValue[1];  
        }  
        else if (attributeNameValue[0].Contains("font-weight"))  
        {  
            if (Convert.ToInt32(attributeNameValue[1]) >= 700)  
                activeSheet.Cells[r, c].Font.Bold = true;  
        }  
        else if (attributeNameValue[0].Contains("text-align"))  
        {  
            if (attributeNameValue[1].Contains("left"))  
                activeSheet.Cells[r, c].HorizontalAlign = HorizontalAlign.Left;  
            else if (attributeNameValue[1].Contains("right"))  
                activeSheet.Cells[r, c].HorizontalAlign = HorizontalAlign.Right;  
        }  
    }  
}  

protected Color GetColor(string color)  
{  
    Color colorVar;  
    if (color.StartsWith("#"))  
        colorVar = ColorTranslator.FromHtml(color);  
    else  
        colorVar = Color.FromName(color);  
    return colorVar;  
}

Figure2 In this blog, we took HTML tables that were pasted from Excel, and sent them to server-side code to parse through and set the same values and properties in the FpSpread component. By utilizing the HTMLAgilityPack, we were able to parse through the HTML table in server-side code.

MESCIUS inc.

comments powered by Disqus