Table Insert Row Above/Below

Aspose.Cells Product Family
1

What is the aspose cells code equivalent to right clicking on a row in a table in excel and then clicking:

  1. Insert\Add Row Below
  2. Insert\Add Row Above

I would like to be able to enumerate through all the tables in a worksheet and add rows and data to each of the tables.

Thanks,
Phil

2

@philipstone,

Thanks for your query.

Well, you may try to use Worksheet.Cells.InsertRange() or Worksheet.Cells.InsertRow(s) method for the task.

3

  1. Worksheet.Cells.InsertRow shifts cells that are outside the table so that is not equivalent to “Insert Row Below” in excel

  2. Below is the code I am using for Worksheet.Cells.InsertRange. This took a bit of trying to get:)

    Dim workbook As New Workbook(“C:\Users\pstone\Desktop\dd.xlsx”)
    Dim worksheet As Worksheet = workbook.Worksheets(0)

    Dim area As New CellArea
    For Each table As Tables.ListObject In worksheet.ListObjects
    area.StartColumn = table.StartColumn
    area.EndColumn = table.EndColumn
    If table.ShowHeaderRow Then
    area.StartRow = table.StartRow + 2
    Else
    area.StartRow = table.StartRow + 1
    End If

      area.EndRow = area.StartRow

  Dim ROWS_TO_INSERT As Integer = 10

  worksheet.Cells.InsertRange(area, ROWS_TO_INSERT, ShiftType.Down, True)

  Dim sourceRange As Range = worksheet.Cells.CreateRange(area.StartRow - 1, area.StartColumn, 1, area.EndColumn - area.StartColumn)
  Dim destinationRange As Range = worksheet.Cells.CreateRange(area.StartRow, area.StartColumn, ROWS_TO_INSERT - 1, area.EndColumn - area.StartColumn)
  destinationRange.Copy(sourceRange)

    Next

4

Thanks for your help. Phil

5

@philipstone,

Thanks for the code segment.

It looks like Worksheet.Cells.InsertRange() works for your needs. Let us know with details and sample files if you still find any issue, we will be happy to assist you soon.