<The previous article in this series | The table of contents of this series | The next article in this series>
How to Write to an Arbitrary Spread Sheet Cell in the Appropriate Datum Type 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 Set an Arbitrary Value to an Arbitrary Spread Sheet Cell in the Appropriate Datum Type?
Now that we have a reference to the representative cell of an arbitrary spread sheet cell and know how to read from the spread sheet cell in the appropriate datum type, let's set an arbitrary value to the spread sheet cell.
OK.
As you can guess from the way in which the value is internally stored in the cell, we can set the value as the double value when the value isn't a string or a formula. However, to do so, we have to transform the value to the appropriate double value.
For example, we have to transform the date value, 2017-01-01, to the double value, 42736.0.
Apparently, 42736 is the day index of 2017-01-01 as 1 is of 1900-01-01, and we can perform the transformation based on that assumption. However, as we don't want to implement our codes based on such internal specifications, we will turn to another way to set an arbitrary date value to the cell.
Well, won't that another way cause some significant performance degradation?
Actually, I tested it: in order to set the date value 10,000 times, setting the double value took 15.0 seconds and setting in our way took 15.6 seconds.
Ah, the difference will be insignificant enough for us.
We can set an arbitrary date value using the 'setFormula' method of the 'com.sun.star.table.XCell' UNO interface implemented by the UNO component of the representative cell UNO object.
Do we use the 'setFormula' method while we don't set a formula?
That may seem odd, but we do so. After we set the cell value expression format (so-called number format), we call the 'setFormula' method with the cell value expression string as the argument.
Hmm.
For time, datetime, and boolean values, we set the value in the same way.
How about integer and double values?
We can set them in the same way, but we will directly set the double value for them, for in that way, we don't need to create the cell value expression string.
Well, I understand that that's a less cumbersome way for integer and double values.
As we argued in the preceding article, precision of data is crucial for us. So, we have to set the appropriate cell value expression format for an arbitrary double value.
Ah, so we judge the number of decimal places of the double value, and set the cell value expression format of that number of decimal places. . . . How do we judge the number of decimal places?
When we are dealing with an arbitrary BigDecimal value, we can know it from the value itself. If we are dealing with an arbitrary Double value, it will be have to be specified otherwise if the precision is important.
I see.
For string values, we set the value by calling the 'insertString' method of the 'com.sun.star.text.XText' UNO interface implemented by the UNO component of the representative cell UNO object. In order to call the method, we have to first get a 'com.sun.star.text.XTextCursor' instance from the representative cell UNO object using the 'com.sun.star.text.XText' UNO interface.
Don't we have to remove the existing value first before we insert the new string value?
The existing double value seems to be going to be automatically discarded. As for the existing string value, we will clear the string by the 'setFormula' method with the argument, "".
Can't we set the string value directly by the 'setFormula' method?
If the string value to be set can be interpreted as a formula, for example "= A8 + 1", it will be set as a formula, not as a string value. So, we don't set string values by the 'setFormula' method.
Ah-ha.
Will the string value be also automatically discarded when an arbitrary double based value is set?
It seems so.
I see.
We can set an arbitrary formula using the 'setFormula' method.
Ah-ha.
After all, I have written this at the end of the try block of the previous article.
// Sets the inputted value to the current spread sheet cell START
String l_specifiedValue = JOptionPane.showInputDialog (null, "Specify a value in the format, '<datum type>: <value>'");
String l_datumType = null;
String l_valueExpression = null;
if (l_specifiedValue != null) {
int l_colonDelimiterPositionIndex = l_specifiedValue.indexOf (": ");
if (l_colonDelimiterPositionIndex >= 0) {
l_datumType = l_specifiedValue.substring (0, l_colonDelimiterPositionIndex);
l_valueExpression = l_specifiedValue.substring (l_colonDelimiterPositionIndex + 2);
}
if (l_datumType != null) {
switch (l_datumType) {
case "Formula":
l_currentSpreadSheetCellInXCell.setFormula (l_valueExpression);
break;
case "LocalDate":
l_currentSpreadSheetCellInXPropertySet.setPropertyValue ("NumberFormat", new Integer (l_dateExpressionFormatKey));
l_currentSpreadSheetCellInXCell.setFormula (l_valueExpression);
break;
case "LocalTime":
l_currentSpreadSheetCellInXPropertySet.setPropertyValue ("NumberFormat", new Integer (l_timeExpressionFormatKey));
l_currentSpreadSheetCellInXCell.setFormula (l_valueExpression);
break;
case "LocalDateTime":
l_currentSpreadSheetCellInXPropertySet.setPropertyValue ("NumberFormat", new Integer (l_dateTimeExpressionFormatKey));
l_currentSpreadSheetCellInXCell.setFormula (l_valueExpression);
break;
case "Boolean":
l_currentSpreadSheetCellInXPropertySet.setPropertyValue ("NumberFormat", new Integer (l_booleanExpressionFormatKey));
l_currentSpreadSheetCellInXCell.setFormula (l_valueExpression);
break;
case "String":
l_currentSpreadSheetCellInXPropertySet.setPropertyValue ("NumberFormat", new Integer (l_stringExpressionFormatKey));
l_currentSpreadSheetCellInXCell.setFormula ("");
XTextCursor l_textCursorInXTextCursor = l_currentSpreadSheetCellInXText.createTextCursor();
l_currentSpreadSheetCellInXText.insertString (l_textCursorInXTextCursor, l_valueExpression, true);
break;
case "Integer":
Integer l_integer = new Integer (l_valueExpression);
l_currentSpreadSheetCellInXPropertySet.setPropertyValue ("NumberFormat", new Integer (l_integerExpressionFormatKey));
l_currentSpreadSheetCellInXCell.setValue (l_integer.intValue ());
break;
case "BigDecimal":
BigDecimal l_bigDeciaml = new BigDecimal (l_valueExpression);
int l_scale = l_bigDeciaml.scale ();
if (l_scale > 0) {
// Register and set the cell value expression format of a specific precision START
String l_doubleOfSpecificPrecisionExpressionFormatString = String.format (String.format ("%%.%df", l_scale), 0.0);
int l_doubleOfSpecificPrecisionExpressionFormatKey = l_cellValueExpressionFormatsInXNumberFormats.queryKey (l_doubleOfSpecificPrecisionExpressionFormatString, l_defaultLocale, false);
if (l_doubleOfSpecificPrecisionExpressionFormatKey == -1) {
l_doubleOfSpecificPrecisionExpressionFormatKey = l_cellValueExpressionFormatsInXNumberFormats.addNew (l_doubleOfSpecificPrecisionExpressionFormatString, l_defaultLocale);
}
// Register and set the cell value expression format of a specific precision END
l_currentSpreadSheetCellInXPropertySet.setPropertyValue ("NumberFormat", new Integer (l_doubleOfSpecificPrecisionExpressionFormatKey));
l_currentSpreadSheetCellInXCell.setValue (l_bigDeciaml.doubleValue ());
}
else {
l_currentSpreadSheetCellInXPropertySet.setPropertyValue ("NumberFormat", new Integer (l_integerExpressionFormatKey));
l_currentSpreadSheetCellInXCell.setValue (l_bigDeciaml.intValue ());
}
break;
case "Double":
Double l_double = new Double (l_valueExpression);
l_currentSpreadSheetCellInXPropertySet.setPropertyValue ("NumberFormat", new Integer (l_doubleDefaultExpressionFormatKey));
l_currentSpreadSheetCellInXCell.setValue (l_double.doubleValue ());
break;
default:
Publisher.show ("That datum type isn't recognized.");
break;
}
}
else {
Publisher.show ("Wrong format.");
}
}
// Sets the inputted value to the current spread sheet cell END
Besides, I have added these import directives.
import javax.swing.JOptionPane;
import com.sun.star.text.XTextCursor;
import com.sun.star.beans.PropertyVetoException;
And I have added this exception type in the catch block.
- PropertyVetoException
Hmm, we are setting the value specified in an input dialog to the current spread sheet cell.
The expected format of the input is '<datum type>: <value>', for example 'LocalDateTime: 2017-01-01T00:00:00' and 'BigDecimal: 12.3400'. Note that the space after the colon is necessary.
I see.
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. However, just don't select the 'H1' cell before the 'Test1' button is pushed, because that cell is used in the code of the next article.
<The previous article in this series | The table of contents of this series | The next article in this series>
