How to read data from excel and bind into gridview in asp.net OR How to read data from excel-sheet in asp.net

Description:-

In this article we will Read data from excel sheet and bind in Gridview control to see. To open excel sheet we will use File-upload control and check by filename, extension, and if we want header or not in Gridview. And by Changing the Page index of Gridview control we will call method for reading data from excel sheet to read another data.

Default.aspx:-

<div>
  <asp:FileUpload ID="FileUpload1" runat="server" />
  <asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="btnUpload_Click" />
  <br />
  <asp:Label ID="Label1" runat="server" Text="Has Header ?"></asp:Label>
  <asp:RadioButtonList ID="rbHDR" runat="server">
    <asp:ListItem Text="Yes" Value="Yes" Selected="True"></asp:ListItem>
    <asp:ListItem Text="No" Value="No"></asp:ListItem>
  </asp:RadioButtonList>
  <asp:GridView ID="GridView1" runat="server" OnPageIndexChanging="PageIndexChanging" AllowPaging="true"></asp:GridView>
</div>

Default.aspx.cs:-
Now Generate button click event to upload file and Check filename, Extension or etc…

protected void btnUpload_Click(object sender, EventArgs e)
{
  if (FileUpload1.HasFile)
  {
    string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
    string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
    string FolderPath = ConfigurationManager.AppSettings["FolderPath"];

    string FilePath = Server.MapPath(FolderPath + FileName);
    FileUpload1.SaveAs(FilePath);
    Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);
  }
}

Now create method for read data from excel sheet and bind in Gridview control.

private void Import_To_Grid(string FilePath, string Extension, string isHDR)
{
  string conStr = "";
  switch (Extension)
  {
    case ".xls": //Excel 97-03
      conStr = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
      break;
    case ".xlsx": //Excel 07
      conStr = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
      break;
  }
  conStr = String.Format(conStr, FilePath, isHDR);
  OleDbConnection connExcel = new OleDbConnection(conStr);
  OleDbCommand cmdExcel = new OleDbCommand();
  OleDbDataAdapter oda = new OleDbDataAdapter();
  DataTable dt = new DataTable();
  cmdExcel.Connection = connExcel;

  //Get the name of First Sheet
  connExcel.Open();
  DataTable dtExcelSchema;
  dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
  string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
  connExcel.Close();

  //Read Data from First Sheet
  connExcel.Open();
  cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
  oda.SelectCommand = cmdExcel;
  oda.Fill(dt);
  connExcel.Close();

  //Bind Data to GridView
  GridView1.Caption = Path.GetFileName(FilePath);
  GridView1.DataSource = dt;
  GridView1.DataBind();
}

Web.Config:-
For Connection String you can use in web.config file for Secure connection like below and you can use in your webpage.

<connectionStrings>
  <add connectionString="Data Source=ServerName;Initial Catalog=DatabaseName;Persist Security Info=True;User ID=UserName;Password=PassWord" name="DBCS" providerName="System.Data.SqlClient"/>
</connectionStrings>

Or I have used here my AppSetting for Getting Data for Save Data in this Folder.

<appSettings>
  <add key="FolderPath" value="Files/"/>
</appSettings>

Now Generate OageIndexChanged event of Gridview Control to read another data from excel sheet.

protected void PageIndexChanging(object sender, GridViewPageEventArgs e)
{
  string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
  string FileName = GridView1.Caption;
  string Extension = Path.GetExtension(FileName);
  string FilePath = Server.MapPath(FolderPath + FileName);

  Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);
  GridView1.PageIndex = e.NewPageIndex;
  GridView1.DataBind();
}