Reading Excel File And Showing on Gridview in Asp.net

Hello Friends!
Today we will learn how to read the uploaded excel file and load it's data in your webpage.
We can show the contents of the excel file in any control whether it is Gridview or simple Textbox.

Step 1: To Start with, create a Webpage named Excel.aspx and write the Following mark up on the page.

         
           <div>
            <br />  <br />
            <table width="60%" align="center">
            <tr> <th colspan="2" align="center"><h3>Read Excel File </h3> </th> </tr>
            <tr> <th> Upload File </th>
            <td> <asp:FileUpload ID="FileUpload1" runat="server" />  </td>   </tr>
            
            <tr>  <td colspan="2" align="center" > 
            <asp:Button ID="btnUpload" runat="server" 
                    Height="21px" Text="Read..." Width="92px" onclick="btnUpload_Click"/> </td>   </tr>
            </table>    </div>
        <br />  

  <table width="40%" align="center">
            <tr>  <td>
        <asp:GridView ID="GridView1" runat="server" BackColor="White" BorderColor="#CCCCCC" 
                    BorderStyle="None" BorderWidth="1px" CellPadding="3">
            <FooterStyle BackColor="White" ForeColor="#000066" />
            <RowStyle ForeColor="#000066" />
            <PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
            <SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />
            <HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />
        </asp:GridView>
       </td> </tr>     </table>


Step 2: We will need the namespace  System.Data.OleDb as follows

          using System.Data.OleDb;
          using System.IO;

Step 3: Now add the following code on the btnUpload_Click Event.

      protected void btnUpload_Click(object sender, EventArgs e)
    {
        string connectionString = "";
        if (FileUpload1.HasFile)
        {
            string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
            string fileExtension = Path.GetExtension(FileUpload1.PostedFile.FileName);
            string fileLocation = Server.MapPath("~/App_Data/" + fileName);
            FileUpload1.SaveAs(fileLocation);

            //Check whether file extension is xls or xslx

            if (fileExtension == ".xls")
            {
                connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
            }
            else if (fileExtension == ".xlsx")
            {
                connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
            }

            //Create OleDB Connection and OleDb Command

            OleDbConnection con = new OleDbConnection(connectionString);
            OleDbCommand cmd = new OleDbCommand();
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.Connection = con;
            OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
            DataTable dtExcelRecords = new DataTable();
            con.Open();
            DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
            cmd.CommandText = "SELECT * FROM [" + getExcelSheetName + "]";
            dAdapter.SelectCommand = cmd;
            dAdapter.Fill(dtExcelRecords);
            con.Close();
           

            GridView1.DataSource = dtExcelRecords;
            GridView1.DataBind();
        }
    }


Output: