Retaining Formulas during Copy Paste Actions

Spread for ASP.NET supports the copy paste operations using the keyboard shortcut keys or the CommandBar buttons. Sometimes, the cell contain formulas which are overriden when a paste operation is performed for the cell. This blog discusses how the formulas can be retained during the copy paste operations. For this, we would need to handle the copy paste operations manually from client side as well as server side. Here is the client side code that handles the KeyDown event to capture the shortcut keys:

function keyDown()  
{  
   if (window.event.keyCode == 86 && event.ctrlKey)  
   {  
      var data = window.clipboardData.getData("Text");  
      var cdata = document.getElementById("HiddenField3");  
      cdata.value = data;  
      var ss = document.getElementById("FpSpread1");  
      ss.CallBack("Custom Paste");  
    }  
    else if (window.event.keyCode == 67 && event.ctrlKey)  
    {  
       var ss = document.getElementById("FpSpread1");  
       var srow = document.getElementById("HiddenField1");  
       var scol = document.getElementById("HiddenField2");  
       var scr = ss.GetSelectedRange();  
       srow.value = scr.row;  
       if (scr.col == -1)  
       {  
           scol.value = 0;  
       }  
       else  
       {  
           scol.value = scr.col;  
       }  
       if (scr.row == -1)  
       {  
           srow.value = 0;  
       }  
       else  
       {  
          srow.value = scr.row;  
       }  
   }  
}  

On the server side, ButtonCommand event is used to accomplish the copy paste operation.

protected void FpSpread1_ButtonCommand(object sender, FarPoint.Web.Spread.SpreadCommandEventArgs e)  
 {  
    if (e.CommandName == "Custom Paste")  
    {  
       string data = Convert.ToString(HiddenField3.Value);  
       string[] rows = data.Split("\\r\\n".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);  
       string[] columns = rows[0].Split("\\t".ToCharArray());  
       int row = Convert.ToInt32(HiddenField1.Value);  
       int col = Convert.ToInt32(HiddenField2.Value);  
       int rc = rows.Length;  
       int cc = columns.Length;  
       int drow = FpSpread1.ActiveSheetView.ActiveRow;  
       int dcol = FpSpread1.ActiveSheetView.ActiveColumn;  
       string[,] forarr = new string[rc, cc];  
       for (int r = drow, i = 0; i < rc; i++, r++)  
           for (int c = dcol, j = 0; j < cc; j++, c++)  
             {  
                if (FpSpread1.ActiveSheetView.Cells[r, c].Formula != "")  
                 {  
                     forarr[i, j] = FpSpread1.ActiveSheetView.Cells[r, c].Formula;  
                 }  
                 else  
                 {  
                     forarr[i, j] = "";  
                 }  
             }  

         for (int r = drow, i = 0; i < rc; i++, r++)  
         {  
             string[] ccolumns = rows[i].Split("\\t".ToCharArray());  
             for (int c = dcol, j = 0; j < cc; j++, c++)  
             {  
                FpSpread1.ActiveSheetView.SetText(r,  c, ccolumns[j]);  
             }  
         }  
         for (int r = drow, i = 0; i < rc; i++, r++)  
           for (int c = dcol, j = 0; j < cc; j++, c++)  
           {  
                if (forarr[i, j] != "")  
                 {  
                     FpSpread1.ActiveSheetView.Cells[r, c].Formula = forarr[i, j];  
                 }  
           }  
      }  
 }  

Refer to the attached sample for complete implementation. Download C# Sample Download VB Sample