Have you ever wanted to help your users move data from Excel to your Web application? In this article I will give an example in ASP.NET that takes copied Excel spreadsheet rows from the user’s clipboard and saves the information to a SQL Server database table.
Since ASP.NET runs at the server level, you will need to use JavaScript/JQuery to get the Excel information from the user’s clipboard. You will need to use a different command to get the information depending on the user’s browser. In this example, I code specifically for Internet Explorer, but this is just since I am giving an example. In a real-world application you would code for all of the major browsers.
Overview of the Process
The simplest way to get the clipboard information from the client browser is using JavaScript. Once you have the information in your script, you can validate it and convert it to a machine-friendly format such as XML. At that point, you’ll want to pass it to your .NET code for saving to a database table. The simplest way to transfer the information from client-side to server-side is by using a postback event on a button control, along with an invisible textbox control to do the transfer.
So the process is:
- The user opens Excel and the Web Page.
- Then the user copies a number of rows from the Excel Spreadsheet. (Note that the columns of the SpreadSheet and their order must match the expected columns & order in the code)
- The user clicks on an import button on the Web page
- The JavaScript collects the clipboard information from the user’s browser
- The JavaScript validates the information and then converts it into XML format
- The JavaScript sets the XML data to a hidden ASP.NET TextBox control
- The JavaScript returns true to allow the postback event to happen
- The .NET gets the XML from the TextBox control on PostBack and sends it to a SQL Server Stored Procedure
- The SQL Server Stored Procedure bulk saves the XML into a database table
- The .NET refreshes the GridView control that displays the information from the database table to reflect the addition of the new data.
Step 1: Getting the ClipBoard information from the Client Browser
You’ll want to first include the latest JQuery library on your Web page. This certainly isn’t necessary, but it does simplify the syntax of your script. In this case I’m including JQuery 1.9.1:
<script src="http://code.jquery.com/jquery-1.9.1.js"></script><br />Next you will need to hook up your ASP.NET button control to your JavaScript function. You can do so using the OnClientClick attribute of the ASP.NET button control.
Now you will want to code your function to do the work. In the example below, I have the JavaScript set up to accept parameters for a hypothetical pet shop.
The function starts by setting up an array of the expected Excel fields and the order of the fields. This is validated row by row against the data passed in by the user from Excel, and an error is thrown if the number of fields is less than the expected number.
Also, in the example, I am only showing code for getting the clipboard information from Internet Explorer using:
<br />var myData = window.clipboardData.getData('Text');<br />The JavaScript then loops through the rows of Excel data and then the columns, saving the general array format of:
["Pets_Name" , "Pet_Type", "Pet_Cost", "Store_Name", "City", "Address", "Zip", "Country"];Into the XML format of:
<br /><ExcelData><br /><ExcelRowData><br /><Pets_Name>Mittens</Pets_Name><br /><Pet_Type>Cat</Pet_Type><br /><Pet_Cost>100.00</Pet_Cost><br /><Store_Name>Pet Store Inc.</Store_Name><br /><City>My City</City><br /><Address>My Address</Address><br /><Zip>23123</Zip><br /><Country>USA</Country><br /></ExcelRowData><br /></ExcelData><br />As a final step, the JavaScript passes the parsed XML to the ASP.NET TextBox control. You can get the ID of the control very easily for your JavaScript instead of hard-coding it and hoping for the best using the .NET ClientId method:
document.getElementById(‘<%= TextBox1.ClientID %>’).value = XMLOut
Pretty straightforward, right? Well, there’s a small catch when it comes to parsing the Excel…
Parsing the Tab-Delimited Excel Cells
As I mentioned, you’ll need to check out how Excel passes data.
By default data is passed as tab-delimited, with blank Excel cells coming in as a single tab, while populated cells come in as the data plus a tab. This causes a slight problem that can be solved using a regular expression in a JavaScript replace statement:
tmpRow = tmpRow.replace(/\t\B/g, "\tx1a");The trick here is the Regular Expression \B (note the capitalization) matches but does not consume a non-word boundary (in our case a tab). Thus the expression \t\B will match the tabs that are next to each other that Excel is indicating are blank spaces. If we populate these with a marker for our code, we can properly format the text so that our split function on the \t (tab character) properly parses out the Excel cells.
The ASP.NET Component
I won’t go into much detail for the ASP.NET part of the code. Basically all that’s involved is grabbing the XML from the hidden TextBox control, passing it to the database to be saved, refreshing the displayed data, and then blanking the hidden TextBox control in case the user wishes to copy and paste more from Excel.
The Database Component
The stored procedure that obtains the XML data to be saved is not very complicated. You simply have it receive the XML as a parameter, that then is queried in your T-SQL like a table as part of a simple bulk save operation.
So assuming the example XML structure of:
<br /><ExcelData><br /><ExcelRowData><br /><Pets_Name>Mittens</Pets_Name><br /><Pet_Type>Cat</Pet_Type><br /><Pet_Cost>100.00</Pet_Cost><br /><Store_Name>Pet Store Inc.</Store_Name><br /><City>My City</City><br /><Address>My Address</Address><br /><Zip>23123</Zip><br /><Country>USA</Country><br /></ExcelRowData><br /></ExcelData><br />You would write your bulk insert statement as:
<br />INSERT INTO myPetShopTable<br /><%%KEEPWHITESPACE%%> SELECT<br /><%%KEEPWHITESPACE%%> SearchItems.ItemToSearch.value('Pets_Name[1]','VARCHAR(50)') AS Pets_Name<br /><%%KEEPWHITESPACE%%> SearchItems.ItemToSearch.value('Pet_Type[1]','VARCHAR(50)') AS Pet_Type<br /><%%KEEPWHITESPACE%%> SearchItems.ItemToSearch.value('Pet_Cost[1]','VARCHAR(50)') AS Pet_Cost<br /><%%KEEPWHITESPACE%%> SearchItems.ItemToSearch.value('Store_Name[1]','VARCHAR(50)') AS Store_Name<br /><%%KEEPWHITESPACE%%> SearchItems.ItemToSearch.value('City[1]','VARCHAR(50)') AS City<br /><%%KEEPWHITESPACE%%> SearchItems.ItemToSearch.value('Address[1]','VARCHAR(50)') AS Address<br /><%%KEEPWHITESPACE%%> SearchItems.ItemToSearch.value('Zip[1]','VARCHAR(50)') AS Zip<br /><%%KEEPWHITESPACE%%> SearchItems.ItemToSearch.value('Country[1]','VARCHAR(50)') AS Country<br /><%%KEEPWHITESPACE%%> FROM @CheckXML.nodes('/ExcelData/ExcelRowData') AS SearchItems(ItemToSearch)<br />The Example Web Page
Below I’m providing the sample code for the import page of our hypothetical pet shop. Note that I’ve left out a lot of validation in order to make this example clear and straight to the point. In a production page, the code would contain a significant number of checks and safeguards to make sure that the data entered is good.
<br /><%@ Page Language="C#" validateRequest="false" %><br /><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><br /><%@ Import Namespace="System.Web.Configuration" %><br /><%@ Import Namespace = "System.Data" %><br /><%@ Import Namespace = "System.Data.SqlClient" %><br /><script runat="server"><br /><%%KEEPWHITESPACE%%> protected void Button1_Click(object sender, EventArgs e)<br /><%%KEEPWHITESPACE%%> {<br /><%%KEEPWHITESPACE%%> string strSaveValue = TextBox1.Text;<br /><%%KEEPWHITESPACE%%> if (strSaveValue.Length > 10)<br /><%%KEEPWHITESPACE%%> {<br /><%%KEEPWHITESPACE%%> string strConn = WebConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;<br /><%%KEEPWHITESPACE%%> SqlConnection conn = null;<br /><%%KEEPWHITESPACE%%> SqlCommand cmd = null;<br /><%%KEEPWHITESPACE%%> string strResultId = null;<br /><%%KEEPWHITESPACE%%> try<br /><%%KEEPWHITESPACE%%> {<br /><%%KEEPWHITESPACE%%> conn = new SqlConnection(strConn);<br /><%%KEEPWHITESPACE%%> cmd = new SqlCommand();<br /><%%KEEPWHITESPACE%%> cmd.Connection = conn;<br /><%%KEEPWHITESPACE%%> cmd.CommandText = "myStoredProcedureToSaveTheData";<br /><%%KEEPWHITESPACE%%> cmd.Parameters.AddWithValue("XMLValues", strSaveValue);<br /><%%KEEPWHITESPACE%%> cmd.CommandTimeout = 30;<br /><%%KEEPWHITESPACE%%> cmd.CommandType = CommandType.StoredProcedure;<br /><%%KEEPWHITESPACE%%> conn.Open();<br /><%%KEEPWHITESPACE%%> cmd.ExecuteNonQuery();<br /><%%KEEPWHITESPACE%%> }<br /><%%KEEPWHITESPACE%%> catch (Exception ex)<br /><%%KEEPWHITESPACE%%> {<br /><%%KEEPWHITESPACE%%> }<br /><%%KEEPWHITESPACE%%> finally<br /><%%KEEPWHITESPACE%%> {<br /><%%KEEPWHITESPACE%%> if (cmd != null) cmd.Dispose();<br /><%%KEEPWHITESPACE%%> if (conn != null)<br /><%%KEEPWHITESPACE%%> {<br /><%%KEEPWHITESPACE%%> conn.Close();<br /><%%KEEPWHITESPACE%%> conn.Dispose();<br /><%%KEEPWHITESPACE%%> }<br /><%%KEEPWHITESPACE%%> }<br /><%%KEEPWHITESPACE%%> TextBox1.Text = "";<br /><%%KEEPWHITESPACE%%> myGridView.DataBind();<br /><%%KEEPWHITESPACE%%> }<br /><br />}<br /></script><br /><html xmlns="http://www.w3.org/1999/xhtml"><br /><%%KEEPWHITESPACE%%> <script src="http://code.jquery.com/jquery-1.9.1.js"></script><br /><head runat="server"><br /><%%KEEPWHITESPACE%%> <title></title><br /><%%KEEPWHITESPACE%%> <script><br /><%%KEEPWHITESPACE%%> function clipboardPaste() {<br /><%%KEEPWHITESPACE%%> if (window.clipboardData) {<br /><%%KEEPWHITESPACE%%> // This script is IE specific (window.clipboardData)<br /><%%KEEPWHITESPACE%%> var XMLOut = "<ExcelData>\r\n"<br /><%%KEEPWHITESPACE%%> var expectedArray = ["Pets_Name" , "Pet_Type", "Pet_Cost", "Store_Name", "City", "Address", "Zip", "Country"];<br /><%%KEEPWHITESPACE%%> var myData = window.clipboardData.getData('Text');<br /><%%KEEPWHITESPACE%%> var myArray = myData.split(/[\r\n]/g);<br /><%%KEEPWHITESPACE%%> $.each(myArray, function (i) {<br /><%%KEEPWHITESPACE%%> var tmpRow = "\t" + myArray[i];<br /><%%KEEPWHITESPACE%%> tmpRow = tmpRow.replace(/\t\B/g, "\tx1a");<br /><%%KEEPWHITESPACE%%> row = tmpRow.split(/\t/g);<br /><%%KEEPWHITESPACE%%> XMLOut += "<ExcelRowData>\r\n";<br /><%%KEEPWHITESPACE%%> $.each(expectedArray, function (x) {<br /><%%KEEPWHITESPACE%%> var tmpItemVal = $.trim(row[x]);<br /><%%KEEPWHITESPACE%%> tmpItemVal = tmpItemVal.replace("x1a", "");<br /><%%KEEPWHITESPACE%%> XMLOut += "<" + expectedArray[x] + ">" + tmpItemVal + "</" + expectedArray[x] + ">\r\n";<br /><%%KEEPWHITESPACE%%> });<br /><%%KEEPWHITESPACE%%> XMLOut += "</ExcelRowData>";<br /><%%KEEPWHITESPACE%%> });<br /><%%KEEPWHITESPACE%%> XMLOut += "\r\n</ExcelData>";<br /><%%KEEPWHITESPACE%%> document.getElementById('<%= TextBox1.ClientID %>').value = XMLOut<br /><%%KEEPWHITESPACE%%> return (true);<br /><%%KEEPWHITESPACE%%> } else {<br /><%%KEEPWHITESPACE%%> // Place code for WebKit browsers here<br /><%%KEEPWHITESPACE%%> }<br /><%%KEEPWHITESPACE%%> }<br /><%%KEEPWHITESPACE%%> </script><br /></head><br /><body><br /><%%KEEPWHITESPACE%%> <form id="form1" runat="server"><br /><%%KEEPWHITESPACE%%> <asp:Button ID="Button1" runat="server" Text="Button" OnClientClick="return clipboardPaste();" OnClick="Button1_Click" /><br /><%%KEEPWHITESPACE%%> <br /><br /><%%KEEPWHITESPACE%%> <br /><br /><%%KEEPWHITESPACE%%> <asp:Label ID="Label2" runat="server" Text="Received Value from Clipboard"></asp:Label><br /><%%KEEPWHITESPACE%%> <br /><br /><%%KEEPWHITESPACE%%> <asp:TextBox ID="TextBox1" runat="server" Height="117px" TextMode="MultiLine"<br /><%%KEEPWHITESPACE%%> Width="333px"></asp:TextBox><br /><%%KEEPWHITESPACE%%> <br /><br /><%%KEEPWHITESPACE%%> <asp:Label ID="Label1" runat="server" Text="Received Value in CodeBehind"></asp:Label><br /><%%KEEPWHITESPACE%%> <br /><br /><%%KEEPWHITESPACE%%> <asp:GridView ID="myGridView" runat="server" DataSourceID="SqlDataSource1"><br /><%%KEEPWHITESPACE%%> </asp:GridView><br /><%%KEEPWHITESPACE%%> <asp:SqlDataSource ID="SqlDataSource1" runat="server"></asp:SqlDataSource><br /><%%KEEPWHITESPACE%%> </form><br /></body><br /></html><br />
Justin Cooney 
Reblogged this on Sutoprise Avenue, A SutoCom Source.
Reblogged this on DOTNET Essentials.