Upload and Display MS Excel Sheet Data in Gridview Using Asp.Net

Upload and Display MS Excel Sheet Data in Gridview Using Asp.Net

<form id="form1" runat="server">
        <div>
            <asp:Label ID="lblMessage" runat="server" ForeColor="Red"></asp:Label>
            <br />
            <br />
            <asp:FileUpload ID="FileUpload1" runat="server" />
            <br />
            <br />
            <asp:Button ID="btnUpload" runat="server" Text="Upload and Display" OnClick="btnUpload_Click" />
            <br />
            <br />
            <asp:GridView ID="GridView1" runat="server" EnableViewState="false" />
        </div>
    </form>
protected void btnUpload_Click(object sender, EventArgs e)
        {
            string uploadFilePath = "";
            if (FileUpload1.HasFile)
            {
                string filename = System.IO.Path.GetFileName(Server.MapPath(FileUpload1.FileName));
                uploadFilePath = "~/ExcelFileList/" + filename;
                FileUpload1.SaveAs(Server.MapPath(uploadFilePath));
            }
            if (uploadFilePath != "")
            {
                string connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";
                string filepath = Server.MapPath(uploadFilePath);
                DataTable dt = new DataTable();
                using (OleDbConnection conn = new OleDbConnection(string.Format(connectionstring, filepath)))
                {
                    string query = @"SELECT * FROM [Sheet1$]";
                    using (OleDbCommand cmd = new OleDbCommand(query, conn))
                    {
                        conn.Open();
                        using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
                        {
                            da.Fill(dt);
                        }
                        conn.Close();
                    };
                }
                GridView1.DataSource = dt;
                GridView1.DataBind();
            }
            else
            {
                lblMessage.Text = "Please select file to upload.";
            }
        }

2020-03-16