How can I persist the row values OnRowEditing

Question

I have an asp.net gridview and the data in the gridview is populated from an excel file and I seek to edit the file using the gridview and save the edits, however when a row is selected for editing the values for that selected row are not visible. If I rebind the data to the grid view it disappears. How can I persist the visibility of the values on the selected row while editing?

    <form id="form1" runat="server">
<div>
          <asp:ScriptManager runat="server" />
           <asp:UpdatePanel ID="grdUpdatePanel" runat="server" UpdateMode="Conditional">
               <ContentTemplate>
                    <asp:GridView ID="gridViewTest" runat="server" OnRowDataBound="gv_RowDataBound" OnRowEditing="gv_RowEditing">
                    </asp:GridView>
               </ContentTemplate>
           </asp:UpdatePanel>
</div>
<br/>
<div>
    <asp:Button ID="btnExport" runat="server" Text="Export" 
        onclick="btnExport_Click" />&nbsp;&nbsp;
    <asp:FileUpload ID="UploadField"
        runat="server" />&nbsp;<asp:Button ID="btnImport" runat="server" Text="Import" 
        onclick="btnImport_Click" />
    </div>
</form>

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

    internal DataTable dt = new DataTable();


    protected void Page_Load(object sender, EventArgs e)
    {
        // Disable Export button
        this.btnExport.Enabled = false;

    }

     #region Import operation

    /// <summary>
    ///  Import Excel Data into GridView Control 
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    /// 


    protected void btnImport_Click(object sender, EventArgs e)
    {


        // The condition that FileUpload control contains a file 
        if (UploadField.HasFile)
        {
            // Get selected file name
            string filename = Server.MapPath(UploadField.FileName);

            // Get the extension of the selected file
            string fileExten = Path.GetExtension(filename);


            // The condition that the extension is not xlsx
            if (!fileExten.Equals(".xlsx", StringComparison.OrdinalIgnoreCase))
            {
                Response.Write("<script language=\"javascript\">alert('The extension of selected file is incorrect ,please select again');</script>");
                return;
            }

            // Read Data in excel file
            try
            {

               dt = ReadExcelFile(filename);

                if (dt.Rows.Count == 0)
                {
                    Response.Write("<script language=\"javascript\">alert('The first sheet is empty.');</script>");
                    return;
                }

                // Bind Datasource
                this.gridViewTest.DataSource = dt;
                this.gridViewTest.DataBind();

                // Enable Export button
                this.btnExport.Enabled = true;
            }
            catch (IOException ex)
            {
                string exceptionmessage = ex.Message;
                Response.Write("<script language=\"javascript\">alert(\""+exceptionmessage+"\");</script>");
            }
        }
        else if (IsPostBack) {

            this.gridViewTest.DataSource = dt;
            this.gridViewTest.DataBind();

        } else {
            Response.Write("<script language=\"javascript\">alert('You did not specify a file to import');</script>");

        }                                                 
    }


    protected override void Render(HtmlTextWriter writer)
    {
        for (int i = 0; i < gridViewTest.Rows.Count; i++)
            ClientScript.RegisterForEventValidation(gridViewTest.UniqueID, "Edit$" + i);
        base.Render(writer);
    }


    protected void gv_RowDataBound(object sender, GridViewRowEventArgs e)
    {

        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            string script = ClientScript.GetPostBackClientHyperlink(this.gridViewTest, "Edit$" + e.Row.RowIndex.ToString());
            e.Row.Attributes.Add("onclick", script);
        }

    }

   //xlApp.ActiveWorkbook.Signatures.AddSignatureLine();

    protected void gv_RowEditing(object sender, GridViewEditEventArgs e)
    {
        gridViewTest.EditIndex = e.NewEditIndex;
    }


    /// <summary>
    ///  Read Data from selected excel file on client
    /// </summary>
    /// <param name="filename">File Path</param>
    /// <returns></returns>
    private DataTable ReadExcelFile(string filename)
    {
        // Initializate an instance of DataTable
        dt = new DataTable();

        try
        {
            // Use SpreadSheetDocument class of Open XML SDK to open excel file
            using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filename, false))
            {
                // Get Workbook Part of Spread Sheet Document
                WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;

                // Get all sheets in spread sheet document 
                IEnumerable<Sheet> sheetcollection = spreadsheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();

                // Get relationship Id
                string relationshipId = sheetcollection.First().Id.Value;

                // Get sheet1 Part of Spread Sheet Document
                WorksheetPart worksheetPart = (WorksheetPart)spreadsheetDocument.WorkbookPart.GetPartById(relationshipId);

                // Get Data in Excel file
                SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();
                IEnumerable<Row> rowcollection = sheetData.Descendants<Row>();

                if (rowcollection.Count() == 0)
                {
                    return dt;
                }

                // Add columns
                foreach (Cell cell in rowcollection.ElementAt(0))
                {
                    dt.Columns.Add(GetValueOfCell(spreadsheetDocument, cell));
                }

                // Add rows into DataTable
                foreach (Row row in rowcollection)
                {
                    DataRow temprow = dt.NewRow();
                    for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
                    {
                        temprow[i] = GetValueOfCell(spreadsheetDocument, row.Descendants<Cell>().ElementAt(i));
                    }

                    // Add the row to DataTable
                    // the rows include header row
                    dt.Rows.Add(temprow);
                }
            }

            // Here remove header row
            dt.Rows.RemoveAt(0);
            return dt;
        }
        catch(IOException ex)
        {
            throw new IOException(ex.Message);
        }
    }

    /// <summary>
    ///  Get Value in Cell 
    /// </summary>
    /// <param name="spreadsheetdocument">SpreadSheet Document</param>
    /// <param name="cell">Cell in SpreadSheet Document</param>
    /// <returns>The value in cell</returns>
    private static string GetValueOfCell(SpreadsheetDocument spreadsheetdocument, Cell cell)
    {
        // Get value in Cell
        SharedStringTablePart sharedString =spreadsheetdocument.WorkbookPart.SharedStringTablePart;
        if (cell.CellValue == null)
        {
            return string.Empty;
        }

        string cellValue = cell.CellValue.InnerText;

        // The condition that the Cell DataType is SharedString
        if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
        {
            return sharedString.SharedStringTable.ChildElements[int.Parse(cellValue)].InnerText;
        }
        else
        {
            return cellValue;
        }
    }

    #endregion Import operation

    #region Export operation

    /// <summary>
    ///  Export Data in GridView control to Excel file
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnExport_Click(object sender, EventArgs e)
    {
        // Initialize an instance of DataTable
        DataTable dt = CreateDataTable(this.gridViewTest);

        // Save the exported file 
        string appPath = Request.PhysicalApplicationPath;
        string filename = Guid.NewGuid().ToString() + ".xlsx";
        string filePath = appPath+ filename;

        new CreateSpreadSheetProvider().ExportToExcel(dt, filePath);

        string savefilepath = "Export Excel file successfully, the exported excel file is placed in: " + filePath;
        Response.Write("<script language='javascript'>alert('"+savefilepath.Replace("\\","\\\\")+"');</script>");
    }

    /// <summary>
    ///  Create DataTable from GridView Control
    /// </summary>
    /// <param name="girdViewtest">GridView Control</param>
    /// <returns>An instance of DataTable Object</returns>
    private DataTable CreateDataTable(GridView girdViewtest)
    {
        dt = new DataTable();

        // Get columns from GridView
        // Add value of columns to DataTable 
        try
        {
            for (int i = 0; i < gridViewTest.HeaderRow.Cells.Count; i++)
            {
                dt.Columns.Add(gridViewTest.HeaderRow.Cells[i].Text);
            }
        }
        catch
        {
            throw;
        }


        // Get rows from GridView
        foreach (GridViewRow row in gridViewTest.Rows)
        {
            DataRow datarow = dt.NewRow();
            for (int i = 0; i < row.Cells.Count; i++)
            {
                datarow[i] = row.Cells[i].Text.Replace("&nbsp;", " ");
            }

            // Add rows to DataTable
            dt.Rows.Add(datarow);
        }

        return dt;
    }

    #endregion Export operation
}

Answer 1

You might want to check the row state when binding the data. What happens is that the gridview binds the data on every postback (at least that's my guess). So everything is fine when you bind your data for the first time where every row state = normal or alternate|normal. But when you click the edit button a postback occurs and a "rebind" is triggered only that your selected row will be on edit mode or alternate|edit mode.

Try this:

if (e.Row.RowState == DataControlRowState.Normal || e.Row.RowState == DataControlRoState.Alternate|DataControlRowState.Normal)
{
         if (e.Row.RowType == DataControlRowType.DataRow)
        {
            string script = ClientScript.GetPostBackClientHyperlink(this.gridViewTest,
             "Edit$" + e.Row.RowIndex.ToString());
            e.Row.Attributes.Add("onclick", script);
        }
}

So now your custom handling will trigger when the row is in normal or alternate|normal state. If you want to handle the row's edit state try:

 if (e.Row.RowState == DataControlRowState.Edit || e.Row.RowState == DataControlRoState.Alternate|DataControlRowState.Edit)
     {
        //your logic
     }

Answer 2

You need to rebind your GridView in the RowEditing event.

Store that DataTable in a Session variable during your click event:

dt = ReadExcelFile(filename);
Session["userDataTable"] = dt;

And then use that to rebind your Gridview on row edit:

protected void gv_RowEditing(object sender, GridViewEditEventArgs e)
{
    gridViewTest.EditIndex = e.NewEditIndex;
    gridViewTest.DataSource = (DataTable)Session["userDataTable"];
    gridViewTest.DataBind();
}

Since you are doing custom databinding, you need to handle the edit event by setting the edit index to the row that was clicked, and you need to make sure all the data is still there (by re-binding).

Answer 3

Given the custom binding you're already using, the RowUpdated event is probably what you're looking for. The GridViewUpdatedEventArgs object that it provides includes a NewValues dictionary containing the edited values, which you can then apply to your underlying data source.

Alternatively, you could code an ObjectDataSource. Which as they say, allows you to "data bind to a custom business object".