Thursday, December 12, 2013

Read a Excelsheet from the fileupload in ASP.Net

In this article we are going to see how to read the Excel Sheet data while uploading from the File Upload control in ASP.Net. when user upload the Excel sheet we are going to read the data from the excel sheet and show in gridview.


Html :

<body>
    <form id="form1" runat="server">
    <fieldset style="width:700px;">
    <legend>Application Register</legend>
    <div>
    File Name :
    <asp:FileUpload runat="server"id="fileupload"/>
    <br />
    <br />
    <br />
   
    <asp:Button  ID="upload"  runat="server"Text="Upload"onclick="upload_Click"
            style="height: 26px;position:relative;left:80px"/>
    <br />
    <br />
    <br />
    Data's :
    <asp:GridView ID="content"  runat="server">
    </asp:GridView>
    </div>
    </fieldset>
    </form>
</body>



C#:

  public partial class ReadExcel : System.Web.UI.Page
    {

        System.Data.OleDb.OleDbConnectionoleconnection;

        protected voidPage_Load(object sender, EventArgs e)
        {

        }

        protected voidupload_Click(object sender, EventArgs e)
        {
            string filepath = Server.MapPath("\\"+fileupload.FileName);

            fileupload.SaveAs(filepath);

            if (Path.GetExtension(filepath) == ".xls")
            {
                oleconnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"");
            }
            else if (Path.GetExtension(filepath) == ".xlsx")
            {
                oleconnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"");
            }

           
            OleDbCommand olecommand = newOleDbCommand("SELECT Serial,Name FROM [Sheet1$]",oleconnection);
            OleDbDataAdapter oleadapter = new OleDbDataAdapter(olecommand);
            DataSet ds = new DataSet();
            oleadapter.Fill(ds);

            content.DataSource = ds;
            content.DataBind();


        }
    }



Output:





I hope this article will help you to upload the excel file and the same time bind the data from the excel to Gridview.

No comments:

Post a Comment