<The previous article in this series | The table of contents of this series | The next article in this series>
To Know How to Handle (Read or Write) LibreOffice or Apache OpenOffice Calc Spread Sheets via Extensions from Java or Macro Programs
How Can We Insert Spread Sheet Rows or Columns?
Another frequently encountered use case will be to insert and delete spread sheet rows and columns.
I guess so. Although we can accomplish the same thing by moving and deleting spread sheet cell data, it will be too cumbersome and too slow to do so when we have data in many cells.
It will be so.
The UNO component of the spread sheet UNO object implements a UNO interface, 'com.sun.star.sheet.XCellRangeMovement', by which we can insert and delete spread sheet rows and columns.
I see.
We can insert rows or columns by calling the 'insertCells' method of the UNO interface.
Um? 'insertCells'? The name sounds as if it can insert cells groups of more general shapes than just rows or columns.
In fact, it can insert squared cells groups.
Hmm, so, a squared area is inserted into the sheet, and what happens to the rest of the sheet?
The existing specified area and the right side of the area can move right, or the existing specified area and the down side of the area can move down.
So, we can specify which?
Yes. Although that wasn't in our scope of interest, we will try that too later.
OK.
The method takes two arguments: the area to be inserted and the mode of the insertion. The first argument is a struct, which is mapped to a class in Java. When we insert rows, we specify the sheet index, and the start row index and the end row index of the inserted area. The second argument is an enum, which is also mapped to a class in Java. When we insert rows, we specify 'com.sun.star.sheet.CellInsertMode.ROWS'.
Ah-ha.
When we insert columns, we specify the sheet index, the start column index and the end column index of the inserted area, and 'com.sun.star.sheet.CellInsertMode.COLUMNS'.
The struct has those 5 members. When we insert, say, rows, do we have to set specific values to the other irrelevant members?
No, we don't. Irrelevant members are just ignored.
I see.
How Can We Delete Spread Sheet Rows or Columns?
We can delete rows or columns by calling the 'removeRange' method of the UNO interface.
Ah, it also can deal with squares of cells.
Yes. The right side of the specified area can move left, or the down side of the specified area can move up.
I see.
The method takes two arguments: the area to be deleted and the mode of the deletion. The first argument is the same struct as before. When we delete rows, we specify the sheet index, and the start row index and the end row index of the deleted area. The second argument is another enum. When we delete rows, we specify 'com.sun.star.sheet.CellDeleteMode.ROWS'.
Ah-ha.
When we delete columns, we specify the sheet index, the start column index and the end column index of the deleted area, and 'com.sun.star.sheet.CellDeleteMode.COLUMNS'.
I see.
How Can We Insert or Delete Spread Sheet Squares of Cells?
To insert a square of cells, to the first argument, we specify the sheet index, and the start row index, the end row index , the start column index, and the end column index of the inserted area. To the second argument, in order to move the existing specified area and the right side of the area right, we specify 'com.sun.star.sheet.CellInsertMode.RIGHT'; in order to move the existing specified area and the down side of the area down, we specify 'com.sun.star.sheet.CellInsertMode.DOWN'.
Ah-ha.
To delete a square of cells, to the first argument, we specify the sheet index, and the start row index, the end row index , the start column index, and the end column index of the deleted area. To the second argument, in order to move the right side of the specified area left, we specify 'com.sun.star.sheet.CellDeleteMode.LEFT'; in order to move the down side of the specified area up, we specify 'com.sun.star.sheet.CellDeleteMode.UP'.
I see.
After all, I have written this code after the code of the previous article.
XCellRangeMovement l_currentSpreadSheetInXCellRangeMovement = (XCellRangeMovement) UnoRuntime.queryInterface (XCellRangeMovement .class, l_currentSpreadSheet );
CellRangeAddress l_cellRangeAddress = new CellRangeAddress ();
l_cellRangeAddress.Sheet = (short) 2;
l_cellRangeAddress.StartRow = 17;
l_cellRangeAddress.EndRow = 18;
l_currentSpreadSheetInXCellRangeMovement. insertCells (l_cellRangeAddress, CellInsertMode.ROWS);
l_cellRangeAddress.StartColumn = 3;
l_cellRangeAddress.EndColumn = 3;
l_currentSpreadSheetInXCellRangeMovement. insertCells (l_cellRangeAddress, CellInsertMode.COLUMNS);
l_cellRangeAddress.StartRow = 24;
l_cellRangeAddress.EndRow = 25;
l_currentSpreadSheetInXCellRangeMovement. removeRange (l_cellRangeAddress, CellDeleteMode.ROWS);
l_cellRangeAddress.StartColumn = 8;
l_cellRangeAddress.EndColumn = 9;
l_currentSpreadSheetInXCellRangeMovement. removeRange (l_cellRangeAddress, CellDeleteMode.COLUMNS);
l_cellRangeAddress.StartRow = 36;
l_cellRangeAddress.EndRow = 37;
l_cellRangeAddress.StartColumn = 2;
l_cellRangeAddress.EndColumn = 3;
l_currentSpreadSheetInXCellRangeMovement. insertCells (l_cellRangeAddress, CellInsertMode.RIGHT);
l_cellRangeAddress.StartRow = 41;
l_cellRangeAddress.EndRow = 42;
l_cellRangeAddress.StartColumn = 2;
l_cellRangeAddress.EndColumn = 3;
l_currentSpreadSheetInXCellRangeMovement. insertCells (l_cellRangeAddress, CellInsertMode.DOWN);
l_cellRangeAddress.StartRow = 36;
l_cellRangeAddress.EndRow = 37;
l_cellRangeAddress.StartColumn = 2;
l_cellRangeAddress.EndColumn = 3;
l_currentSpreadSheetInXCellRangeMovement. removeRange (l_cellRangeAddress, CellDeleteMode.LEFT);
l_cellRangeAddress.StartRow = 41;
l_cellRangeAddress.EndRow = 42;
l_cellRangeAddress.StartColumn = 2;
l_cellRangeAddress.EndColumn = 3;
l_currentSpreadSheetInXCellRangeMovement. removeRange (l_cellRangeAddress, CellDeleteMode.UP);
I also have added these import directives.
import com.sun.star.sheet.XCellRangeMovement;
import com.sun.star.table.CellRangeAddress;
import com.sun.star.sheet.CellInsertMode;
import com.sun.star.sheet.CellDeleteMode;
I see.
<The previous article in this series | The table of contents of this series | The next article in this series>
