How to Read data From Excel Sheet in Asp.net

How to Read data From Excel Sheet in Asp.net?

How to Read data From Excel Sheet in C# ?

Step-1:

Download Jet Driver and Microsoft Access database Engine  and Install it to your computer. 

Step -2 :

Create a File Upload Control and a Button

<div style="width:100%;">

<asp:FileUpload ID="fu_Excel" runat="server" />

<asp:Button ID="btn_Upload" runat="server" Text="Upload" onclick="btn_Upload_Click"/>

<asp:Label ID="lbl_Error" runat="server"/>

</div>

<div style="width:100%;">

 <asp:GridView ID="gv_Data" runat="server" RowStyle-BackColor="Aqua" HeaderStyle-BackColor="Bisque" HeaderStyle-Font-Bold="true"></asp:GridView>

</div>

Step-3: 

Write the following Code in your Code Behind file

protected void btn_Upload_Click(object sender,EventArgs e)

{

string FileName = fu_Excel.PostedFile.FileName;

if (Path.GetExtension(FileName) == ".xls" || Path.GetExtension(FileName) == ".xlsx")

        {

fu_Excel.SaveAs(Server.MapPath("~/upload/"));

              string connectString = string.Empty;

             if (Path.GetExtension(FileName) == ".xls")

               connectString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("~/upload/"+FileName) + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=0\"";

              else if (Path.GetExtension(FileName) == ".xlsx")

                    connectString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("~/upload/"+FileName) + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";

                        OleDbConnection con = new OleDbConnection(connectString);

                        con.Open();

 OleDbDataAdapter adpt = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", con);

                        string col = "";

                        DataTable dtab = new DataTable();

                        adpt.Fill(dtab);

gv_Data.DataSource=dtab.DefaultView;

gv_Data.DataBind();

                }

                catch (Exception ex)

                {

                }

                

            }

else

lbl_Error.Text="Invalid File";

return;

}