Thursday, October 14, 2010

How to paste Excel data in a web page (any tabular data) using GridView [ IE only ]

Demo:


Markup:
<%@ Page Language="C#" %>
<%@ Register Assembly="ActiveTest" Namespace="ActiveTest" TagPrefix="asp" %>
<html>
<head id="Head2" runat="server">
    <style>
        body { background-color:#eee; }
        div.WorkingArea { border:solid 10px #cccbackground-color:#fffheight:500px; }
    </style>
    <script language="javascript" type="text/javascript">
        function paste() {
            document.getElementById('ClipboardContent').value =
                                        window.clipboardData.getData('Text');
            return (true);
        } 
    </script>
    <script runat="server">
        protected void Paste(object sender, EventArgs e)
        {
            if (!string.IsNullOrEmpty(this.ClipboardContent.Value))
            {
                this.grvData.DataSource = 
                        DataMapper.GetDataTable(this.ClipboardContent.Value, true); ;
                this.grvData.DataBind();
            }
        }        
    </script>
</head>
<body>
    <form id="form2" runat="server">
    <div>
        <asp:Button ID="btnPaste" runat="server" Text="Paste" 
                            OnClick="Paste" OnClientClick="return paste();" />
        <hr />
        <div class="WorkingArea">
            <asp:BulkGridView ID="grvData" runat="server" />
        </div>
        <asp:HiddenField ID="ClipboardContent" runat="server" ClientIDMode="Static" />
    </div>
    </form>
</body>
</html>
DataMapper Class:
public class DataMapper
{
    public const char ColumnDelemiter = '\t';
    public const char RowDelemiter = '\n';
    public static List<T> GetData<T>(string content)
    {
        List<T> value = new List<T>();
        T obj;
        string[] rowValues;
        string[] colValues;
        int colIndex = 0;
        PropertyInfo pInfo;
        if (string.IsNullOrEmpty(content) || content == "null")
            return null;
        content = content.Replace("\r""");
        rowValues = content.Split(RowDelemiter);
        foreach (string rowItem in rowValues)
        {
            if (string.IsNullOrEmpty(rowItem))
                continue;
 
            colValues = new string[rowItem.Split(ColumnDelemiter).Length];
            colIndex = 0;
            obj = (T)Activator.CreateInstance(typeof(T));
 
            foreach (string colItem in rowItem.Split(ColumnDelemiter))
            {
                pInfo = obj.GetType().GetProperties()[colIndex];
                pInfo.SetValue(obj, colItem, null);
                colIndex++;
            }
            value.Add(obj);
        }
 
        return value;
    }
    public static DataTable GetDataTable(string content, bool IsFirstColumnHeader)
    {
        DataTable value = new DataTable();
        string[] rowValues;
        string[] colValues;
        int colIndex = 0;
        int rowIndex = 0;
 
        if (string.IsNullOrEmpty(content) || content == "null")
            return null;
 
        content = content.Replace("\r""");
        rowValues = content.Split(RowDelemiter);
 
        foreach (string rowItem in rowValues)
        {
            if (string.IsNullOrEmpty(rowItem))
                continue;
 
            colValues = new string[rowItem.Split(ColumnDelemiter).Length];
            colIndex = 0;
 
            foreach (string colItem in rowItem.Split(ColumnDelemiter))
            {
                if (rowIndex == 0 && IsFirstColumnHeader)
                    value.Columns.Add(colItem);
                else if (rowIndex == 0)
                    value.Columns.Add();
 
                if ((rowIndex == 0 && !IsFirstColumnHeader) || rowIndex != 0)
                    colValues[colIndex] = colItem;
 
                colIndex++;
            }
            if ((rowIndex == 0 && !IsFirstColumnHeader) || rowIndex != 0)
                value.Rows.Add(colValues);
 
            rowIndex++;
        }
 
        return value;
    }
}
BulkGridView Control:
namespace ActiveTest
{
    public class BulkGridView : GridView
    {
        protected override GridViewRow CreateRow(int rowIndex, int dataSourceIndex,
                                                    DataControlRowType rowType, DataControlRowState rowState)
        {
            return base.CreateRow(rowIndex, dataSourceIndex, rowType, rowState | DataControlRowState.Edit);
        }
    }
}


References
Many thanks for these people for their work.
Real World GridView: Bulk Editing - By Mattdotson
Copy And Paste Excel / Clipboard Data To Gridview In ASP.net - By Jebarson

1 comment:

Monsterbadboll said...

nice, how you combined the two! awesome :)

iPhone Launch Screen Sizes

iPhone Portrait iOS 8 Retina HT 5.5 = 1242 X 2208 Retna HD 4.7 = 750 X 1134 iPhone Landscape iOS 8 Retina HD 5.5  2208 X 1242 iPho...