Copying formula to dynamically generated Cell not taking Cell reference correct

Copying formula to dynamically generated Cell not taking Cell reference correct

Posted by: mayank.singh on 4 December 2024, 6:55 am EST

• 

  mayank.singh

  • Post Options:
  • Link
    Copy

  Posted 4 December 2024, 6:55 am EST - Updated 4 December 2024, 6:57 am EST

  In our application, we dynamically generate three columns by shifting the last column to the right to create space for these new columns. After that, we copy the format and formulas from the shifted column to the newly generated columns. However, for some rows, the formulas are copied correctly, while for others, they are not. Specifically, we observed that after row 86, the formulas in the newly generated columns reference incorrect cells.

  Below is the code we used for Insert and Copying

  For Insert
  
  sheet.Range[7, 9, 80,3].Insert(InsertShiftDirection.Right);

  Copy Formula and format
  
  for (var i = 0; i < 3; i++)
  
  {
  
  sheet.Range[7, 12, 80, 1].Copy(sheet.Range[7, 9 + (i)], PasteType.Default);
  
  }

  NOTE: For more details, you can refer to the formula in column J of the attached original_sheet.xlsx.

  After dynamically generating the column, you can refer to the formula in column J. However, after row 86 in AfterGenerate.xlsx, the reference changes to column G.

  this issue is replicating on **gc **spread and FarpointOrignal_Sheet.zipAfterGenerate.zip

• 

  priyam.kushwaha

  

  • Post Options:
  • Link
    Copy

  Posted 5 December 2024, 7:50 am EST - Updated 5 December 2024, 7:55 am EST

  Hi,

  Based on my understanding, you are attempting to shift a column to the right and then copy the shifted column into three new columns but are encountering issues with incorrect cell references in both SpreadJS and FarPoint.

  I tried replicating the behavior on my end using the provided details but could not reproduce the issue in SpreadJS. On my end, I was able to shift the column, add three new columns, and copy the shifted column without any problems. Refer to the attached GIF “Steps.gif” and the provided sample.

  Gif:

  Sample: https://jscodemine.mescius.io/share/1HyU5wQD70OLhGWHWLgFVw/?defaultOpen={"OpenedFileName"%3A["%2Findex.html"%2C"%2Fpackage.json"%2C"%2Fsrc%2Fapp.js"]%2C"ActiveFile"%3A"%2Fpackage.json"}

  If you are facing this issue in SpreadJS, to assist you better, could you please:

  • Share a minimal working sample along with the steps to replicate the behavior you have observed or modify the existing sample to replicate the issue?
  • Provide a GIF or video illustrating the problem.

  Additionally, for the FarPoint issue, could you share the details of the FarPoint version, the technology you are using, and a sample replicating the issue? This will help us provide more specific guidance.

  Regards,
  
  Priyam

• 

  mayank.singh

  • Post Options:
  • Link
    Copy

  Posted 5 December 2024, 8:31 am EST - Updated 5 December 2024, 9:35 am EST

  Hi,
  
  thank you for your response,

  The example you provided is implemented in JavaScript. However, the code implementation mentioned above uses GrapeCity.Documents.Excel in C# to obtain the sheet object.
  
  below is the Image attached for GC document
  
  

  

  1510×285 47.2 KB

  For Farpoint, we are using version 12.45.20191.0. and below is the Image attached for farpoint
  
  

  

  1472×492 82.2 KB

  Code Observation
  
  same observation for both Farpoint and GCDocumnet

  As observed in the code, after shifting a column to the right and creating space for three new columns, the formula reference for Row 10 (as shown in the attached screenshot) updates correctly to Column M on the shifted column.

  

  1572×147 4.14 KB

  However, for Row 87, the formula reference does not update to Column M; instead, it remains in Column J with an offset of -3.

  

  1577×187 5.94 KB

  
  
  Subsequently, when we copy the formula and format from Column M to the newly created columns(J,K,L), the same behavior is replicated for Row 87 with offset of -3.
  
  

  

  1732×677 25.6 KB

  
  
  On Row 87, Column J should contain the formula =SUM(J149,J205,J261,J318,J392,J429,J443). However, it incorrectly navigates to Column G, with the formula =SUM(G149,G205,G261,G318,G392,G429,G443).

• 

  aastha.gupta

  

  • Post Options:
  • Link
    Copy

  Posted 6 December 2024, 9:16 am EST

  Hi Mayank,

  We were able to reproduce the issue with the GC Document product and have escalated it to our developers with internal tracking ID: DOCXLS-11692. We’ll update you as soon as we hear back from them.

  However, we could not reproduce the issue with the latest version of the Spread.NET product. Please check the implementation in the attached file: CopyRangeTest.zip

  The GIF below shows the behavior on our side:

  https://gccontent.blob.core.windows.net/discourse-forum-uploads/file-e6fe6ea9-6a73-4a72-b064-cfd1e229c086.gif(image larger than 4 MB)

  We recommend you update to the latest version of Spread.NET to achieve the expected behavior.

  Thanks & Regards,
  
  Aastha