How to replace rows in a table one a workbook with the rows of a table in another workbook

Aspose.Cells Product Family
1

How can I replace the rows in a table one a workbook with the rows of a table in another workbook? Both tables have the same columns.

2

@kippow
You can achieve your requirements through range copying. Regarding range copying, please refer to the following document.

3

I am able to copy the rows fine.

Next I need to redefine the name of the range that contains the copied rows. I am not able to get the name from the workbook’s names. The name is definitely in the workbook, but it does not appear in the workbooks defined names.

I load the workbook:

        Workbook targetWorkbook = new Workbook("SalesSummaryPivot.xlsx");

This returns null.

        Name pivotDefinedName = targetWorkbook.Worksheets.Names["View_SalesSummary"];

The workbook’s names does not contain this defined name.

        foreach (Name name in targetWorkbook.Worksheets.Names)
          Debug.WriteLine($"Workbook: {targetWorkbook.FileName}; Name: {name**.Text}");

Here is the workbook, which definitely contains the defined name.

SalesSummaryPivot.zip (36.6 KB)

The name can be seen in Excel

image.png (30.0 KB)

4

@kippow,

Thanks for the template Excel file and screenshot.

Your named range is table/list object oriented, so you may get the named ranges in this way:
e.g.
Sample code:

Workbook targetWorkbook = new Workbook("g:\\test2\\SalesSummaryPivot.xlsx");
Aspose.Cells.Range[] ranges = targetWorkbook.Worksheets.GetNamedRangesAndTables();
for(int i= 0; i<ranges.Length; i++)
{
  Console.WriteLine("Name: " + ranges[i].Name + " Refer to: " + ranges[i].RefersTo);
}

Hope, this helps a bit.

5

Thanks.

I can get that range with no problem using:

        Range? pivotRange = targetWorkbook.Worksheets.GetNamedRangesAndTables().FirstOrDefault(r => r.Name == PivotDataRangeName);

But how do I change the Refersto of the table. In other words, how do I set the bounds of the table?

6

This might help…

image.png (105.3 KB)

7

@kippow
After copying the range data, you can use ListObject.Resize method to achieve the goal.

The sample code as follows:

Workbook book = new Workbook(filePath + "SalesSummaryPivot.xlsx");
Aspose.Cells.Range[] ranges = book.Worksheets.GetNamedRangesAndTables();
for (int i = 0; i < ranges.Length; i++)
{
    Console.WriteLine("Name: " + ranges[i].Name + " Refer to: " + ranges[i].RefersTo);
}
Worksheet worksheet  = book.Worksheets[0];
Range sourceRange = worksheet.Cells.CreateRange("A5", "H11");

Range destRange = worksheet.Cells.CreateRange("A12", "H18");
destRange.Copy(sourceRange);

ListObject table = worksheet.ListObjects[0];
table.Resize(table.StartRow, table.StartColumn, table.EndRow + destRange.RowCount, table.EndColumn, true);

ranges = book.Worksheets.GetNamedRangesAndTables();
for (int i = 0; i < ranges.Length; i++)
{
    Console.WriteLine("Name: " + ranges[i].Name + " Refer to: " + ranges[i].RefersTo);
}
            
book.Save(filePath + "out_net.xlsx");

Hope helps a bit.