Select, Edit Update in Excel File in ASP.Net using C# .Net

Here I have created sample that let you to import excel file that get loaded into GridView, Once excel data get uploaded into GridView, you can edit it, and then you can export it to Excel File as well.

Also this sample requires ClosedXML DLL File, for that you can refer below articles

http://aspsnippets.com/Articles/Import-data-from-Excel-to-GridView-in-ASPNet.aspx

 http://aspsnippets.com/Articles/Export-GridView-Data-to-Excel-using-OpenXml-in-ASPNet.aspx

I think this will help you out.

HTML

<form id="form1" runat="server">
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="btnUpload_Click" />
<br />
<asp:Label ID="Label1" runat="server" Text="Has Header ?"></asp:Label>
<asp:RadioButtonList ID="rbHDR" runat="server">
    <asp:ListItem Text="Yes" Value="Yes" Selected="True"></asp:ListItem>
    <asp:ListItem Text="No" Value="No"></asp:ListItem>
</asp:RadioButtonList>
<asp:GridView ID="GridView1" HeaderStyle-BackColor="#3AC0F2" HeaderStyle-ForeColor="White"
    runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:TemplateField HeaderText="Id">
            <ItemTemplate>
                <asp:TextBox runat="server" ID="Id" Text='<%# Eval("Id") %>'> </asp:TextBox>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Name">
            <ItemTemplate>
                <asp:TextBox runat="server" ID="Name" Text='<%# Eval("Name") %>'></asp:TextBox>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Country">
            <ItemTemplate>
                <asp:TextBox runat="server" ID="Country" Text='<%# Eval("Country") %>'></asp:TextBox>
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>
<br />
<asp:Button Text="Export" OnClick="ExportExcel" runat="server" />
</form>

Namespaces

using System.IO;
using System.Data;
using ClosedXML.Excel;
using System.Configuration;
using System.Data.OleDb;

C#

protected void btnUpload_Click(object sender, EventArgs e)
{
    if (FileUpload1.HasFile)
    {
        string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
        string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
        string FolderPath = ConfigurationManager.AppSettings["FolderPath"];

        string FilePath = Server.MapPath(FolderPath + FileName);
        FileUpload1.SaveAs(FilePath);
        Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);
    }
}
protected void ExportExcel(object sender, EventArgs e)
{
    DataTable dt = new DataTable("GridView_Data");
    foreach (TableCell cell in GridView1.HeaderRow.Cells)
    {
        dt.Columns.Add(cell.Text);
    }
    foreach (GridViewRow row in GridView1.Rows)
    {
        dt.Rows.Add();
        for (int i = 0; i < row.Cells.Count; i++)
        {
            dt.Rows[dt.Rows.Count - 1][i] = (row.Cells[i].Controls.OfType<TextBox>().ToList()[0]).Text;
        }
    }
    using (XLWorkbook wb = new XLWorkbook())
    {
        wb.Worksheets.Add(dt);

        Response.Clear();
        Response.Buffer = true;
        Response.Charset = "";
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        Response.AddHeader("content-disposition", "attachment;filename=GridView.xlsx");
        using (MemoryStream MyMemoryStream = new MemoryStream())
        {
            wb.SaveAs(MyMemoryStream);
            MyMemoryStream.WriteTo(Response.OutputStream);
            Response.Flush();
            Response.End();
        }
    }
}
private void Import_To_Grid(string FilePath, string Extension, string isHDR)
{
    string conStr = "";
    switch (Extension)
    {
        case ".xls": //Excel 97-03
            conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
            break;
        case ".xlsx": //Excel 07
            conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
            break;
    }
    conStr = String.Format(conStr, FilePath, isHDR);
    OleDbConnection connExcel = new OleDbConnection(conStr);
    OleDbCommand cmdExcel = new OleDbCommand();
    OleDbDataAdapter oda = new OleDbDataAdapter();
    DataTable dt = new DataTable();
    cmdExcel.Connection = connExcel;

    //Get the name of First Sheet
    connExcel.Open();
    DataTable dtExcelSchema;
    dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
    connExcel.Close();

    //Read Data from First Sheet
    connExcel.Open();
    cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
    oda.SelectCommand = cmdExcel;
    oda.Fill(dt);
    connExcel.Close();

    //Bind Data to GridView
    GridView1.Caption = Path.GetFileName(FilePath);
    GridView1.DataSource = dt;
    GridView1.DataBind();
}
protected void PageIndexChanging(object sender, GridViewPageEventArgs e)
{
    string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
    string FileName = GridView1.Caption;
    string Extension = Path.GetExtension(FileName);
    string FilePath = Server.MapPath(FolderPath + FileName);

    Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);
    GridView1.PageIndex = e.NewPageIndex;
    GridView1.DataBind();
}

Screenshots

1. Excel File before Upload

2. Excel File updated in GridView.

3. Updated Excel File.