<The previous article in this series | The table of contents of this series | The next article in this series>
How to Know That the Specified Spread Sheet Cell Is Part of a Group of Merged Cells and Know the Cells Range of the Group in LibreOffice or Apache OpenOffice Is Explained
About: UNO (Universal Network Objects)
About: LibreOffice
About: Apache OpenOffice
About: Java Programming Language
Necessity for Knowing That the Specified Spread Sheet Cell Is Part of a Group of Merged Cells and Knowing the Cells Range of the Group
Here are -Hypothesizer and -Rebutter sitting in front of a computer screen in a room on a brook among some mountains on the Bias planet.
When we try to navigate through a spread sheet, . . .
Wait. I thought that "How to Listen to Spread Sheet Cells Selection Events and (Possibly) Trace the Current Cell Movement, Part Two" was coming.
Ah, you remember.
Of course, I remember.
Well, I didn't forget, or dissemble to have forgotten that. I was working on it and faced some additional issues, which we have to solve first.
So, we are addressing one of those issues in this article?
Yes. Be assured that the "Part Two" will come, eventually.
I am not in any position to be assured of something baselessly. In fact, you may be hit by a car in three days . . .
. . . Yes, I may. So, I just say here, it is sincerely meant to come.
Well, . . . OK.
In fact, the issue of this article arose from the preceding article, "How to Listen to Spread Sheet Cells Selection Events and (Possibly) Trace the Current Cell Movement, Part One": in order to infer the movement of the current cell , we have to navigate through the spread sheet, but if the spread sheet has merged cells, it isn't as easy as just incrementing or decrementing the row index or the column index of the current cell.
When the current cell is 'B8', and 'B8:C9' (meaning that the top leftmost cell is 'B8' and the bottom rightmost cell is 'C9', forming a 2x2 rectangle) cells are merged, the rightward movement of the current cell is to a 'D' column cell.
In order to correctly infer the movement, we have to know that a specific cell, which is 'B8' in this case, is part of a group of merged cells, and know the cells range of the group.
That issue is relevant more generally than only in an odd enterprise of inferring the movement of the current cell: we can't correctly find neighboring cells without that knowledge.
We usually want to get the 'D8' cell when we want the right cell of the 'B8' cell.
How to Handle a Group of Merged Cells
The same with the previous scene.
Before we delve into the issue, I wonder how we should handle a group of merged cells.
Well, what is unclear?
Does the top leftmost cell solely represent the group of merged cells?
That depends on what 'represent' means.
Supposing that we know that a cells range is a group of merged cells, can we handle the group solely by handling the top leftmost cell? For example, if we set the background color of the top leftmost cell, will the whole background color be set for the cells range of the group?
I thought so, . . . but didn't confirm it. Not being so would mean that each cell in the group can have its own background color, which may be a good thing or a bad thing. . . . Well, we have to do an experiment to find that out.
-Hypothesizer writes a test code in the 'testUnoExtensionToDisclose' project (he added a button, 'Handle Merged Cells', into the control panel), opens the spread sheet document file, 'TestSpreadSheetsDocumentForExecutingTests.ods', and executes the test.
Hmm, this is odd. Only the background color of the top row of the group has changed.
Huh? Let me see your test code.
-Hypothesizer shows the code in a text editor over the spread sheet window ('UnoSpreadSheet' and 'Publisher' are their utility classes; how to get the current spread sheet is described here).
UnoSpreadSheet l_currentSpreadSheet = UnoSpreadSheet.getCurrentSpreadSheet (i_componentContextInXComponentContext);
XSpreadsheet l_currentSpreadSheetInXSpreadsheet = l_currentSpreadSheet.getSpreadSheetInXSpreadsheet ();
XCell l_topLeftmostSpreadSheetCellInXCell = l_currentSpreadSheetInXSpreadsheet.getCellByPosition (1, 7);
l_topLeftmostSpreadSheetCellInXCell.setValue (1);
// Handling the top leftmost spread sheet cell. BEGINNING
XPropertySet l_topLeftmostSpreadSheetCellInXPropertySet = (XPropertySet) UnoRuntime.queryInterface (XPropertySet.class, l_topLeftmostSpreadSheetCellInXCell);
l_topLeftmostSpreadSheetCellInXPropertySet.setPropertyValue ("CellBackColor", Integer.valueOf ( (new Color (0, 255, 0)).getRGB ()));
l_topLeftmostSpreadSheetCellInXPropertySet.setPropertyValue ("IsCellBackgroundTransparent", Boolean.valueOf (false));
Publisher.show ("The top leftmost spread sheet cell has been handled.");
// Handling the top leftmost spread sheet cell. END
Nothing looks wrong with the test code.
-Hypothesizer hides the text editor, exposing the spread sheet again.
Oh. The background color has spread over the whole of the group. . . . It seems that repainting the area has made the display right.
That behavior may depend on the environment. I mean, in another environment, the display may immediately become right.
Basically, the top leftmost cell seems to solely represents the group of merged cells, but relying on that may not be desirable.
What if we handle one of the other cells in the group of merged cells?
Well, you know, we have to do another experiment to find that out. I will set the background color of the bottom rightmost cell of the group.
-Hypothesizer adds a logic into the test code, and executes the test.
// Handling the bottom rightmost spread sheet cell. BEGINNING
XCell l_bottomRightmostSpreadSheetCellInXCell = l_currentSpreadSheetInXSpreadsheet.getCellByPosition (2, 8);
l_bottomRightmostSpreadSheetCellInXCell.setValue (4);
XPropertySet l_bottomRightmostSpreadSheetCellInXPropertySet = (XPropertySet) UnoRuntime.queryInterface (XPropertySet.class, l_bottomRightmostSpreadSheetCellInXCell);
l_bottomRightmostSpreadSheetCellInXPropertySet.setPropertyValue ("CellBackColor", Integer.valueOf ( (new Color (0, 0, 255)).getRGB ()));
l_bottomRightmostSpreadSheetCellInXPropertySet.setPropertyValue ("IsCellBackgroundTransparent", Boolean.valueOf (false));
Publisher.show ("The bottom rightmost spread sheet cell has been handled.");
// Handling the bottom rightmost spread sheet cell. END
Ah, handling of the other cells seems to be invisible, or is it totally vanished? . . . What if we unmerge the group from this state?
-Hypothesizer unmerges the group on the spread sheet.
Ah, the color has appeared. So, the setting is there, but was invisible while the cell was included in the group.
When the group of merged cells is selected, what do we get as the selection? The top leftmost cell, the cells range of the group, or something else?
Actually, we get the top leftmost cell.
In the example above, when we move the current cell from 'A9' to the merged cells group by pushing the right arrow key, is something different about the selection from when we move the current cell from 'A8'?
No, as far as I know. The selection is the top leftmost cell, 'B8', in either case.
Hmm, I asked that because pushing the left arrow key returns the current cell back to 'A9', not to 'A8'. How can LibreOffice do that?
-Hypothesizer tries some such operations.
Ah, . . . certainly, when I move the current cell from 'A9', it goes back to 'A9'; if from 'A8', back to 'A8'. How is it done? I don't know . . . The selections themselves don't look different; information held somewhere else, for example the user operations history, seems to to be used . . .
Anyway, remember that behavior when you infer the current cell movement.
I will . . .
How to Know That the Specified Spread Sheet Cell Is Part of a Group of Merged Cells and Know the Cells Range
The same with the previous scene.
First, I expected that every merged cell would have a property that showed the cells range of the group of merged cells, but I couldn't find such a property. In fact, no cell seems to have such a property.
Hmm.
And the cell UNO component doesn't seem to implement any UNO interface that is useful for our purpose.
Well . . .
-Rebutter goes over the list of UNO interfaces implemented by the spread sheet cell component.
Can't we use 'com.sun.star.util.XMergeable'?
That UNO interface seems to be something used for cells ranges: there is a method, 'getIsMerged', but that doesn't seem to be useful for a single cell to know the cells range to which the single cell belongs. You know, the spread sheet cell component implements the UNO interface just because a cell is a kind of cells range.
So, in order to use the UNO interface for our purpose, first, we have to wildly guess the cells area and then, call the method, 'getIsMerged'?
That's my understanding. You know, such a guess will be too wild, for how many possibilities are there of the cells range?
Many.
It turned out that there is a thing called 'spread sheet cell cursor', which is described in a reference document.
-Hypothesizer shows the reference document in a browser, and -Rebutter scans it.
. . . I don't understand this very well. When we create a 'spread sheet cell cursor' specifying a cells range into the 'createCursorByRange' method, is the cells range what the cursor points to, or is it the space in which the cursor, which points to a single cell at a time, moves?
My guess is the former. What are described there are how to change the geometry of the cells range, not how to move a cursor through the cells range. So, the cursor seems to trace cells ranges, not cells.
So, it is really a cells range cursor, not a cell cursor . . .
Anyway, what we want to do is limited: we create a cursor of a single cell, and fit the cursor to the cells range of the group of merged cells, like this.
// Handling merged spread sheet cells range. BEGINNING
XSheetCellCursor l_spreadSheetCellsRangeCursor = l_currentSpreadSheetInXSpreadsheet.createCursorByRange ( (XSheetCellRange) UnoRuntime.queryInterface (XSheetCellRange.class, l_topLeftmostSpreadSheetCellInXCell));
l_spreadSheetCellsRangeCursor.collapseToMergedArea ();
XPropertySet l_mergedSpreadSheetCellsRangeInXPropertySet = (XPropertySet) UnoRuntime.queryInterface (XPropertySet.class, l_spreadSheetCellsRangeCursor);
l_mergedSpreadSheetCellsRangeInXPropertySet.setPropertyValue ("CellBackColor", Integer.valueOf ( (new Color (0, 255, 0)).getRGB ()));
l_mergedSpreadSheetCellsRangeInXPropertySet.setPropertyValue ("IsCellBackgroundTransparent", Boolean.valueOf (false));
Publisher.show ("The merged spread sheet cells range has been handled.");
// Handling merged spread sheet cells range. END
Ah, the method, 'collapseToMergedArea', expands the cursor to the cells range . . .
As the cursor UNO object is a kind of cells range, now, we have what we want.
Can we use that 'gotoOffset' method to know a neighboring cell, with merged cells automatically considered?
Unfortunately, no. If we offset the merged cells range, 'B8:C9', by one row, the result range will not be 'B10' or perhaps 'B10:C11' as we expect it to be, but 'B9:C10'.
Hmm.
Our test code is included in this zip file, and how to use the zip file is described in this article.
- Apache OpenOffice Wiki. (2014/01/02). Apache OpenOffice Developer's Guide. Retrieved from https://wiki.openoffice.org/wiki/Documentation/DevGuide/OpenOffice.org_Developers_Guide
<The previous article in this series | The table of contents of this series | The next article in this series>
