Copy Excel Row not working

[David Bassion]

Hi,

I'm trying to mimic the functionality in Excel when you copy a row and then insert it below.  In this case in Excel, the new row has the same formulas, just adjusted to look one row further down (ex., "=A1+A2" becomes "=A2+A3").  Any rows after that should also adjust down one more row (same example above applies).

I'm on version 26.1.42 of the Nuget package Syncfusion.XlsIO.Net.Core.  I've tried using the code at https://www.syncfusion.com/forums/179002/copy-row-in-excel but while this inserts a new row, none of the formulas get copied.  Here's my code if needed

worksheet.InsertRow(rowToCopyTo, 1, ExcelInsertOptions.FormatAsBefore);
IRange source = worksheet.Rows[rowToCopyTo - 1];
IRange destination = worksheet.Rows[rowToCopyTo];
source.CopyTo(destination, ExcelCopyRangeOptions.All);


I've also tried the method from your website at https://support.syncfusion.com/kb/article/2557/paste-only-the-formula-value-of-excel-cell-in-c-vb-net and downloaded the sample file.  The sample file is using version 17.1.0.50 in Nuget of Syncfusion.XlsIO.WinForms and when I run this code it works.  This is using worksheet.Range instead of worksheet.Rows.  If I change it to worksheet.Rows it doesn't work.  Also, if I update to the latest version in Nuget, then worksheet.Range stops working as well.  

Am I doing something wrong?  Is deprecated code and I should be using different functionality?  I can send sample projects if need be.  

[Atchaya Sekar]

Hi

We have checked and validated this scenario. The InsertRow method uses a one-based index, while worksheet.Row uses a zero-based index. In your code, when you insert a row, a new row is formed as the first row, and now the first row is empty. You take the first row as the source and paste it into the destination row (second row), causing the second row to become empty.

Code Snippet:

// Specify the row index where you want to insert a new row.

var rowToCopyTo = 1;

// Insert a new row at row index 1 (second row), shifting existing rows down.

// Since InsertRow uses a one-based index, the new row will be inserted above the specified row (row 1),

// and row 1 will become empty after the insertion.

worksheet.InsertRow(rowToCopyTo, 1, ExcelInsertOptions.FormatAsBefore);

// Get the source row, which is the row just before the inserted row (row 0).

IRange source = worksheet.Rows[rowToCopyTo - 1];

// This refers to row 0 (first row, A1)

// Get the destination row, which is the row where you want to paste the data (row 1).

IRange destination = worksheet.Rows[rowToCopyTo];

// This refers to row 1 (second row, A2) // At this point, the row 1 (A1) is empty because of the insertion, so if you try to copy data from it, // it will copy nothing into row 1 (A2), leaving the second row also empty.

We have attached the runnable sample for your reference. If you are facing any issues or have any other questions, kindly reach out to us. If you are facing any other issues, please share the sample along with the input documents.

Regards,

Atchaya S.

Attachment: ConsoleAppInsertRow_14e482b9.zip