Copying formula to dynamically generated Cell not taking Cell reference correct

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

  • 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

  • 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

  • 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

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

    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.

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



    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.



    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).

  • 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:

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

    Thanks & Regards,

    Aastha

  • Posted 24 December 2024, 4:44 am EST

    Hi Mayank,

    As per the development team, this is the expected behavior.

    The result of your IRange.Insert method call changes J8:J87 to M8:M87 range. The reference to formula is modified only when the reference is moved.

    Therefore, in your case, the M8’s reference is changed to =SUM(M11,M46,M62,M65,M82,M84) because (J11, J46, J62, J65,J82, J84) changed to (M11, M46, M62, M65, M82, M84). But, The M87’s reference remains =SUM(J149,J205,J261,J318,J392,J429,J443) because the (J149,J205,J261,J318,J392,J429,J443) are not moved, due to being out of your IRange.Insert call.

    As per the development team, maybe the entire column insertion suits your needs better.

    sheet.Range["J:L"].Insert();

    This will also move the M87’s formula references. Please see the attached sample project for reference.

    Attachment: ExcelMoveCols.zip

    Best Regards,

    Kartik

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels