How to insert/delete row to/from table

Aspose.Cells Product Family
1

Hi,

In my workbook I have several tables. Some cells in the tables defined as named ranged. For a given named range (cell) I want to find out what table it belongs to and depending on some condition I want either to insert row into table below the row the cell belongs to or delete the row where the cell belongs to. I was wondering whether there is a way to achieve it.

Right now I insert/delete row in the whole worksheet, but in this case it shifts other date up or down. Ideally, I want to insert row into table only not into the full worksheet.

Thanks in advance,
Olga

2

Hi,

You can access your tables (list objects) using Worksheet.getListObjects() property.

You can also access your named ranges using workbook.Worksheets.getRangeByName() method.

You can decide if your cell lies inside the list object or range using their StartColumn, StartRow, EndColumn EndRow properties.

You can also add new cells inside the range and list object by changing the above properties i.e shrinking or expanding the area of the range.

3

Hi,

Thanks for your reply. I do have couple of more questions.

> You can also add new cells inside the range and list object by changing
the above properties i.e shrinking or expanding the area of the range.

I assume it will only increase size of the table. What if I have extra cells under the table? Does it mean they will become part of the table?

How can I insert empty row in the middle of the table? I.e. I have 2 rows, I want insert empty row in the middle. So row 1 stays the same, then I have empty row and row 2 become row 3. Also, in this case I want to insert row into the table not the row that goes across the worksheet.

Thanks!

4

Hi,

Thanks for your posting.

Please download and use the latest version:
Aspose.Cells for Java (Latest Version)

After looking into it, I found that when you insert new rows, then it does not break the table and table gets expanded automatically.

Please see the source file, it has a table, when I insert 4 new rows and add some values inside them, then all these new values are included inside the table.

Please see the output file and the screenshot for the reference. And see the sample code below.

Java


String filePath = “source.xlsx”;


// Create a Workbook object.

Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.getWorksheets().get(0);


// Insert 4 rows

worksheet.getCells().insertRows(3, 4);


// Place values inside the rows, these will be included inside the

// table.

Range rng = worksheet.getCells().createRange(“A4:F7”);


Iterator allCells = rng.iterator();


int value = 11111;


while (allCells.hasNext()) {


Cell cell = allCells.next();


cell.putValue(value);


value += 11111;

}


workbook.save(filePath + “.out.xlsx”);


Screenshot:
5

Hi,

The code provided above inserts the row across the worksheet. That is not exactly what I want to achieve. If there is data to the left or to the right of the table it gets shifted down. The goal is to insert the row into table only. So that other data is not affected.

Please, see attached spreadsheets.
Original - workbook that I have in the beginning.
Result - workbook that I get as a result of Cells().insertRow(). As you can see, data on the right was shifted down.
ExpectedResult - workbook that I would like to get when row is only inserted into the table. Data on the right is not affected.

Thanks,
Olga

6

Hi,


Thanks for the template files (original, expected etc.).

After looking your scenario, I think you may try to use Cells.insertRange(), addRange() methods etc.

See the following sample code for your reference, it will not disturb other contents or data (e.g right side etc.).

Sample code:

// Create a Workbook object.
Workbook workbook = new Workbook(“Original.xlsx”);


Worksheet worksheet = workbook.getWorksheets().get(0);

ListObject table1 = worksheet.getListObjects().get(0);

Range rng1 = table1.getDataRange();

CellArea ca = new CellArea();
ca.StartRow=2;
ca.StartColumn=0;
ca.EndRow= ca.StartRow;
ca.EndColumn=3;


// Insert range;
worksheet.getCells().insertRange(ca, 1, ShiftType.DOWN);


workbook.save(“out_Original.xlsx”);


Thank you.