Failing to add table row

GemBox.Spreadsheet
1

Hello

I have two areas in excel one above another formatted as tables. The bottom table (‘Table2’), has more columns than ‘Table1’. Now the invokation of TableRowCollection.Add(), throws InvalidOperationException: ‘Cannot split table ‘Table2’.’. It works fine when both tables have same count of columns.

image
image1083×528 47 KB

2

Hi Vismantas,

That is expected behavior.
You see, if you try this same action with Microsoft Excel:

image1
image1746×357 26.1 KB

You will get this error message:
image2
image2477×237 9.3 KB

This is because adding a table row inserts cells while shifting existing cells down. In this case, that’s not possible because you’d shift only the first two cells of the second table, while the last cell would remain where it is.

I presume you would like to add a new row to the worksheet and have the table extend to include that inserted row. In that case, try the following:

var workbook = ExcelFile.Load("input.xlsx");
var worksheet = workbook.Worksheets[0];

var table = worksheet.Tables[2];
worksheet.Rows.InsertCopy(table.Range.LastRowIndex, worksheet.Rows[table.DataRange.LastRowIndex]);
foreach (var cell in worksheet.Rows[table.DataRange.LastRowIndex].AllocatedCells)
    cell.Value = null;

workbook.Save("output.xlsx");

I hope this helps.

Regards,
Mario