Resources » Code Snippets » DataGridView

Import Excel Sheet Data To DataGridView


Posted Date: 29-Apr-2008  Last Updated:   Category: DataGridView    
Author: Member Level: Gold    Points: 8



To import data from an excel sheet we use ASP.NET's DataGridView control. The code sample consists of an .aspx page followed by the code behind c-sharp file.
The web page gives users options to upload the excel sheet (.xls file). And the code behind file does the magic of loading the data into DataGridview.

HTML CODE
----------


<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ImportFromExcel.aspx.cs" Inherits="ImportFromExcel" %&RT;

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"&RT;

<html xmlns="http://www.w3.org/1999/xhtml" &RT;
<head runat="server"&RT;
<title&RT;Untitled Page</title&RT;
</head&RT;
<body&RT;
<form id="form1" runat="server"&RT;
<div&RT;
<table style="position: relative; left: 200px; top: 98px;"&RT;
<tr&RT;
<td colspan="3"&RT;
<asp:Label ID="lblError" runat="server" Visible="False" ForeColor="Red" Width="231px" style="position: static"&RT;</asp:Label&RT;</td&RT;
</tr&RT;
<tr&RT;
<td colspan="2"&RT;
Select Your Excel File</td&RT;
<td style="width: 100px"&RT;
<asp:FileUpload ID="FileUpload1" runat="server" /&RT;</td&RT;
</tr&RT;
<tr align="center"&RT;
<td colspan="3"&RT;
<asp:Button ID="btnImport" runat="server" Text="Import" OnClick="btnImport_Click" /&RT;
<asp:Button ID="btnCancel" runat="server" Text="Cancel" /&RT;</td&RT;
</tr&RT;
<tr&RT;
<td colspan="3"&RT;
<asp:Label ID="lblMessage" runat="server" Text="" ForeColor="green"&RT;</asp:Label&RT;
<asp:GridView ID="MyGrid" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None"
Style="left: 23px; position: relative; top: 13px" Width="204px" AllowPaging="True" OnPageIndexChanging="MyGrid_PageIndexChanging" PageSize="5"&RT;
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /&RT;
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" /&RT;
<EditRowStyle BackColor="#999999" /&RT;
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" /&RT;
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" /&RT;
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /&RT;
<AlternatingRowStyle BackColor="White" ForeColor="#284775" /&RT;
</asp:GridView&RT;
</td&RT;
</tr&RT;
</table&RT;
</div&RT;
</form&RT;
</body&RT;
</html&RT;


Code Behind (.cs)





using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Data.OleDb;


public partial class ImportFromExcel : System.Web.UI.Page
{
public SqlConnection sqlcon = new SqlConnection("Data Source = MINDS3; User ID = sa; Password = minds; Initial Catalog=simple");
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
lblError.Visible = false;
}

}
protected void btnImport_Click(object sender, EventArgs e)
{
string ExcelFilename = FileUpload1.PostedFile.FileName;
string ExcelFileNameonly = ExcelFilename.Substring(ExcelFilename.LastIndexOf("\\") + 1);
string FileExt = ExcelFilename.Substring(ExcelFilename.LastIndexOf(".") + 1);
string Filenamewithoutextn = ExcelFileNameonly.Remove(ExcelFileNameonly.LastIndexOf("."));
if(FileExt.ToLower() == "xls")
{
try
{
FileUpload1.PostedFile.SaveAs(Server.MapPath("Imported Files/") + Filenamewithoutextn + ".xls");
string strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("Imported Files/") + Filenamewithoutextn + ".xls;Extended Properties=Excel 8.0;";

OleDbConnection olecon = new OleDbConnection(strConnectionString);

int RecordCount = 0;
lblMessage.Visible = false;
lblError.Visible = false;
olecon.Open();
OleDbCommand cmd = olecon.CreateCommand();
cmd.CommandText = "SELECT * FROM [Import$]";
DataSet ds = new DataSet();
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
OleDbDataReader dr = cmd.ExecuteReader();

sqlcon.Open();
string deletestr = "delete from importdata";
SqlCommand sqlcmd = new SqlCommand(deletestr, sqlcon);
sqlcmd.ExecuteNonQuery();

while (dr.Read())
{
SqlCommand cmd1 = sqlcon.CreateCommand();
cmd1.CommandText = "INSERT INTO ImportData values ('" + dr[0] + "','" + dr[1] + "')";
cmd1.ExecuteNonQuery();
RecordCount++;
lblMessage.Visible = true;
lblMessage.Text = " Processed Record # " + RecordCount.ToString();
}
dr.Close();
olecon.Close();
BindGrid();
sqlcon.Close();
}
catch (Exception ex)
{
lblError.Visible = true;
lblError.Text = ex.Message;
}
}

}

private void BindGrid()
{
try
{
lblError.Visible = false;
SqlCommand cmd2 = sqlcon.CreateCommand();
cmd2.CommandText = "SELECT * FROM ImportData order by UserID";

DataSet sds = new DataSet();

SqlDataAdapter sda = new SqlDataAdapter(cmd2);

sda.Fill(sds, "ImportData");
MyGrid.DataSource = sds.Tables["ImportData"].DefaultView;
MyGrid.DataBind();
}
catch (Exception ex)
{
lblError.Visible = true;
lblError.Text = ex.Message;
}

}
protected void MyGrid_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
MyGrid.PageIndex = e.NewPageIndex;
BindGrid();
}
}


Did you like this resource? Share it with your friends and show your love!

Responses to "Import Excel Sheet Data To DataGridView"

No responses found. Be the first to respond...

Feedbacks      

Post Comment:




  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:   Sign In to fill automatically.
    Email: (Will not be published, but required to validate comment)



    Type the numbers and letters shown on the left.


    Submit Article     Return to Article Index

    Subscribe to Subscribers
    Awards & Gifts
    Talk to Webmaster Tony John

    Online Members

    Priya jain
    More...
    Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India