How to import excel file data in asp.net webform grid

How to import excel file data in asp.net webform grid and also Crud operation Using Oledb and ExcelDataReader

using System;

using System.Data;

using System.Data.OleDb;

using System.Web.UI.WebControls;

protected void btnUpload_Click(object sender, EventArgs e)

{

    if (fileUpload.HasFile)

    {

        string filePath = Server.MapPath("~/UploadedFiles/") + fileUpload.FileName;

        fileUpload.SaveAs(filePath);

        // Excel file connection string (adjust for .xlsx or .xls)

        string excelConStr = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={filePath};Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";

        using (OleDbConnection con = new OleDbConnection(excelConStr))

        {

            OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", con);

            OleDbDataAdapter da = new OleDbDataAdapter(cmd);

            DataTable dt = new DataTable();

            da.Fill(dt);

            // Bind DataTable to GridView

            GridView1.DataSource = dt;

            GridView1.DataBind();

        }

    }

}

ASP.NET Markup (ASPX):

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

<asp:Button ID="btnUpload" Text="Upload Excel" OnClick="btnUpload_Click" runat="server" />

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="true"></asp:GridView>

Using ExcelDataReader

protected void btnUpload_Click(object sender, EventArgs e)

{

    if (FileUpload1.HasFile)

    {

        string fileExtension = Path.GetExtension(FileUpload1.FileName);

        if (fileExtension == ".xls" || fileExtension == ".xlsx")

        {

            string filePath = Server.MapPath("~/Temp/" + FileUpload1.FileName);

            FileUpload1.SaveAs(filePath);

            using (FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read))

            {

                IExcelDataReader reader = null;

                if (fileExtension == ".xls")

                {

                    reader = ExcelReaderFactory.CreateBinaryReader(stream);

                }

                else if (fileExtension == ".xlsx")

                {

                    reader = ExcelReaderFactory.CreateOpenXmlReader(stream);

                }

               DataSet result = reader.AsDataSet();

                reader.Close();

                 //Bind first DataTable to GridView

               GridView1.DataSource = result.Tables[0];

                GridView1.DataBind();

            }

        }

        else

        {

            Response.Write("Please upload an Excel file.");

        }

    }

}

---

2. GridView से डेटा Excel में सेव करना

Step 1: GridView का डेटा Excel में Export करें

using System.IO;

using System.Web;

protected void btnExport_Click(object sender, EventArgs e)

{

    Response.Clear();

    Response.Buffer = true;

    Response.AddHeader("content-disposition", "attachment;filename=GridData.xlsx");

    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

    using (StringWriter sw = new StringWriter())

    {

        using (HtmlTextWriter hw = new HtmlTextWriter(sw))

        {

            // Remove paging if enabled

            GridView1.AllowPaging = false;

            GridView1.RenderControl(hw);

            // Write data to response

            Response.Output.Write(sw.ToString());

            Response.Flush();

            Response.End();

        }

    }

}

// Ensure this override exists to allow rendering

public override void VerifyRenderingInServerForm(Control control)

{

    // Required to avoid runtime error

}

ASP.NET Markup (ASPX):

<asp:Button ID="btnExport" Text="Export to Excel" OnClick="btnExport_Click" runat="server" />