<The previous article in this series | The table of contents of this series | The next article in this series>
How to Manipulate the String of an Arbitrary String Type Spread Sheet Cell from Java or Basic Macro Programs Is Explained
About: UNO (Universal Network Objects)
About: LibreOffice
About: Apache OpenOffice
About: Java Programming Language
A Note
This series is about developing UNO extensions that consist of Java UNO components, but most of its descriptions also apply to using UNO in external Java programs or Basic macros (handling spread sheets is inevitably using UNO because spread sheets are UNO objects). The only difference for using UNO in external Java programs is how to get the component context, and the difference is dealt with in the series, 'How to Use UNO (Handle LibreOffice or Apache OpenOffice Documents) in External Java Programs'. As for using UNO in Basic macros, in addition to how to get the component context, there are some other differences, and those differences are dealt with in the series, 'Notes About Using UNO (Handling LibreOffice or Apache OpenOffice Documents) in Basic Macros'.
How Can We Manipulate the String of an Arbitrary String Type Spread Sheet Cell?
Previously, we set an arbitrary string to an arbitrary spread sheet cell by erasing the existing string and setting the new string.
So we did.
We can also insert an arbitrary string in any position of the existing string, replace any area of the existing string with an arbitrary string, or erase any area of the existing string.
We can also do so by getting the existing string, manipulating the gotten string in Java, erasing the existing string, and setting the manipulated string. Which is more efficient?
When the existing string is very long, the latter is far more efficient. I don't know why, but manipulating the existing long string in the former way takes uncannily much CPU resource.
What degree of length are you talking about?
Thousands of characters. In our extremely low-speed computer, it takes about 20 seconds just to add one character.
I know that the computer is extremely, extremely low-speed, but isn't the inner algorithm somehow inappropriate?
I guess, such long strings aren't supposed to be set in spread sheet cells. I think, there should be a better algorithm if long strings are taken in consideration.
What if the existing string isn't so long?
I haven't measured precise performances in such cases. If we aren't processing large amount of data, there would be no noticeable difference.
Well, we can judge it when we encounter the necessity. Anyway, is there any necessity for us to manipulate cell strings in the former way?
Ah, there is a case which requires the former way. If parts of the existing string are formatted differently, we can't just replace the whole string.
You mean, for example only a word in the string is italic?
Yes. Of course we can replace the whole string and set the format again, but that may be cumbersome to do: we will have to acquire the existing formatting information of the whole string and reapply the format.
Hmm, I don't know whether it's really cumbersome or not, but it seems natural to manipulate the existing string in the former way if the string isn't so long.
If we want to apply formats part by part in a long cell string, we will have to contrive something.
As we aren't handling a word processor, we don't seem to need intricate formats, but we may want to have markers to indicate certain words in the string.
Ah, we may, but we won't delve into it right now.
OK.
As we did in the previous article, we get a 'com.sun.star.text.XTextCursor' instance from the representative cell UNO object using the 'com.sun.star.text.XText' UNO interface. The UNO interface, 'com.sun.star.text.XTextCursor', is a sub interface of 'com.sun.star.text.XTextRange', and represents a selected area of the string of the cell.
So, the 'com.sun.star.text.XTextCursor' instance isn't just a single point cursor.
When the selected area shrinks so that no character is selected, the 'com.sun.star.text.XTextCursor' instance becomes a single point cursor.
I see.
If we get a 'com.sun.star.text.XTextCursor' instance by the method, 'createTextCursor', the whole string is initially selected. By using the method, 'createTextCursorByRange', we can get a 'com.sun.star.text.XTextCursor' instance in which the cursor is placed at the beginning or at the end of the string.
Oh.
The cursor can be moved to right or left by the method 'goRight' or 'goLeft', respectively. In those methods, we can specify by how many characters the cursor should be moved.
The cursor can be moved to the beginning or to the end of the string by the method 'gotoStart' or 'gotoEnd', respectively.
How can we select an arbitrary area of the string?
Those four methods have an argument in which we specify whether the selected area should be expanded.
Hmm, I guess, if we want to select an arbitrary area, we can move the cursor to the left border of the area with the argument off, and then, move the cursor to the right border of the area with the argument on.
Yes. Of course, we can do it backward, placing the cursor to the right border of the area and expanding to left.
I see.
After we adjusted the state of the cursor, we insert an arbitrary string by calling the 'insertString' method of the 'com.sun.star.text.XText' UNO interface. We pass the 'com.sun.star.text.XTextCursor' instance and the string to be inserted as the first and the second arguments, respectively. The third argument specifies whether the selected area should be replaced. If not, the new string will be inserted in front of the selected area.
After the string is inserted, what will be the state of the cursor?
Whether the selected area is replaced or not, the cursor is placed after the inserted string as a single point cursor.
Ah-ha.
If we want to erase the selected area, we can call the method, 'insertString' with the empty string replacing the area.
I see.
After all, I have written this code after the code of the previous article.
// Manipulates the string of a spread sheet cell START
// Sets the spread sheet cell, H1, with a string value, '0123456789', as the preparation START
try {
l_spreadSheetCellInXCell = l_currentSpreadSheetInXSpreadsheet.getCellByPosition (7, 0);
}
catch (com.sun.star.lang.IndexOutOfBoundsException l_exception) {
Publisher.show (l_exception.toString ());
}
XPropertySet l_spreadSheetCellInXPropertySet = (XPropertySet) UnoRuntime.queryInterface (XPropertySet.class, l_spreadSheetCellInXCell);
l_spreadSheetCellInXPropertySet.setPropertyValue ("NumberFormat", new Integer (l_stringExpressionFormatKey));
l_spreadSheetCellInXCell.setFormula ("");
XText l_spreadSheetCellInXText = (XText) UnoRuntime.queryInterface (XText.class, l_spreadSheetCellInXCell);
XTextCursor l_textCursorInXTextCursor = l_spreadSheetCellInXText.createTextCursor ();
l_spreadSheetCellInXText.insertString (l_textCursorInXTextCursor, "0123456789", true);
// Sets a spread sheet cell with a string value as the preparation END
l_textCursorInXTextCursor = l_spreadSheetCellInXText.createTextCursorByRange (l_spreadSheetCellInXText.getEnd ());
l_textCursorInXTextCursor.gotoStart (false);
l_spreadSheetCellInXText.insertString (l_textCursorInXTextCursor, "A", true);
l_textCursorInXTextCursor.goRight ( (short) 1,false);
l_spreadSheetCellInXText.insertString (l_textCursorInXTextCursor, "B", true);
l_textCursorInXTextCursor.goRight ( (short) 1, false);
l_textCursorInXTextCursor.goRight ( (short) 1, true);
l_spreadSheetCellInXText.insertString (l_textCursorInXTextCursor, "C", true);
l_spreadSheetCellInXText.insertString (l_textCursorInXTextCursor, "D", true);
l_textCursorInXTextCursor.gotoEnd (false);
l_spreadSheetCellInXText.insertString (l_textCursorInXTextCursor, "E", true);
l_textCursorInXTextCursor.goLeft ( (short) 2, false);
l_spreadSheetCellInXText.insertString (l_textCursorInXTextCursor, "F", true);
l_textCursorInXTextCursor.goLeft ( (short) 2, false);
l_textCursorInXTextCursor.goLeft ( (short) 1, true);
l_spreadSheetCellInXText.insertString (l_textCursorInXTextCursor, "G", true);
l_textCursorInXTextCursor.goLeft ( (short) 2, false);
l_textCursorInXTextCursor.goLeft ( (short) 1, true);
l_spreadSheetCellInXText.insertString (l_textCursorInXTextCursor, "", true);
// Manipulates the string of a spread sheet cell END
We have first forcefully set the spread sheet cell, 'H1', with a string value, '0123456789', and then, manipulated the string value.
Yes. As we need an existing string value cell as the preparation, we did that preparation at the beginning of the code.
We have gotten the cursor initially placed at the end of the existing string, and moved it to the beginning of the string.
Yes. Of course, we can get the cursor initially placed at the beginning of the existing string. We have done as we did, in order to demonstrate calling the 'gotoStart' method.
I know.
The final cell string must be "A0B1CD346G8F9E".
In fact, we have implemented the above code into the test project included in the zip file cited in a previous article. How to use the zip file is described in the article.
<The previous article in this series | The table of contents of this series | The next article in this series>
