How to replace some range from source excel to target excel for .NET

Aspose.Cells Product Family
1

Hello Team,
I want to replace some particular range from my source excel to target excel. If I add/delete row/column in that range it should automatically adjust other values accordingly.

Code Snippet :

//Aspose.Cells.Range[] targetRanges -> this is range of target worksheet range
//Aspose.Cells.Range[] sourceRanges -> this is range of source worksheet range

Aspose.Cells.Range range = sourceRanges.FirstOrDefault(x => x.Name == "Sheet1" + "!" + "Temp1");
if (range != null)
{
     Aspose.Cells.Range tRange = targetRanges.FirstOrDefault(x => x.Name == range.Name);
     if (tRange != null)
         tRange.Copy(range);
}

When I use this copy function, it adds a new row but doesn’t adjust the contents below that range. I want to adjust the content in the other ranges as well.

Could you please provide an alternative solution to achieve this?

Thanks.

2

@AlpeshChaudhariDev

To replace a specific range from a source Excel file to a target Excel file while ensuring that the contents below the range adjust automatically when you add or delete rows or columns, you can use the following approach.

Instead of using the Copy method directly, which may not adjust the contents below the range as you desire, you can manually handle the insertion of rows or columns and then copy the values accordingly. Here’s a sample code snippet that demonstrates this:

// Assuming you have already defined your source and target ranges
Aspose.Cells.Range sourceRange = sourceRanges.FirstOrDefault(x => x.Name == "Sheet1!Temp1");
Aspose.Cells.Range targetRange = targetRanges.FirstOrDefault(x => x.Name == sourceRange.Name);

if (sourceRange != null && targetRange != null)
{
    // Insert rows/columns in the target range to accommodate the source range
    int rowCount = sourceRange.RowCount;
    int columnCount = sourceRange.ColumnCount;

    // Adjust the target range by inserting rows/columns
    targetRange.Worksheet.Cells.InsertRow(targetRange.FirstRow, rowCount);
    targetRange.Worksheet.Cells.InsertColumn(targetRange.FirstColumn, columnCount);

    // Now copy the values from the source range to the target range
    targetRange.Copy(sourceRange);
}

Explanation:

  1. Identify the Source and Target Ranges: The code first identifies the source and target ranges based on their names.
  2. Insert Rows/Columns: Before copying, it inserts the necessary number of rows and columns in the target range to ensure that the existing data below the target range shifts down or to the right.
  3. Copy Values: Finally, it copies the values from the source range to the target range.

This method ensures that when you add or delete rows or columns in the specified range, the other values in the target worksheet adjust accordingly.

For more detailed information on managing named ranges and copying them in Aspose.Cells for .NET, you can refer to the official documentation on managing named ranges here and here for additional insights.

Sources:

[1]: Copy Named Ranges in Excel | Aspose.Cells .NET Excel Processing API
[2]: Managing Named Ranges in Excel | Aspose.Cells .NET Excel Processing API