<The previous article in this series | The table of contents of this series | The next article in this series>
How to Read from 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 Get the Value of 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, we want to get the value of the spread sheet cell.
OK.
In fact, it isn't so straightforward. The 'com.sun.star.table.XCell' UNO interface implemented by the atomic cell UNO object has a method, 'getValue', but the return type of the method is 'double'.
Um? Which means?
If the cell has any string value, the method returns 0.0; if the cell has any date, time, or datetime value, the method returns a kind of internal value (for example, 42736.0 for 2017-01-01); if the cell has any boolean value, the method returns 1.0 for TRUE and 0.0 for FALSE; if the cell has any formula, the method returns 0.0 if the result of the formula is any string value and returns the value that corresponds to the result of the formula if else.
Then, how can we get the string value?
When the cell has any string value, including when the result of the formula is any string value, we can get the string value by a method, 'getString', of the 'com.sun.star.text.XText' interface.
The atomic cell UNO component implements the 'com.sun.star.text.XText' interface.
Yes. In fact, the 'getString' method returns the expression string whatever value the cell has.
By 'expression string' you mean the string displayed on the cell?
Yes. For example, 'TRUE' for a boolean value, '1.23' for a decimal fraction value.
Hmm, anyway, when we get the value of the cell by the 'getValue' method, we can't know what is the value when the return is, say 1.0. Is it an integer, 1, a decimal fraction, 1.0, a boolean, TRUE, a date, a time, or a datetime?
I guess, when the value of the spread sheet cell is not a string, the internal value of the cell is always 'double'. The expression of the cell is controlled by the cell value expression format.
Does 'cell value expression format' mean . . . what?
It's the thing we can set by right clicking the mouse on the cell, selecting 'Formating Cells...', and doing the setting on the 'Numbers' tab. It seems to be called 'number format', but the naming seems odd: it includes formats for strings, which aren't numbers.
I see. Anyway, we have to get the cell value expression format of the cell.
We can get the cell value expression format as a property value of the atomic cell UNO object. Specifically, we can get it by the method, 'getPropertyValue', of the 'com.sun.star.beans.XPropertySet' interface implemented by the atomic cell UNO component. The property name is 'NumberFormat', and the property value is a cell value expression format key, which is an integer.
So, how can we know what the integer property value means?
We can get a 'com.sun.star.util.XNumberFormats' interface UNO proxy of the spread sheet document UNO object, and we can get cell value expression format keys and their format strings from the UNO proxy.
They are the cell value expression format keys and their format strings registered in the document.
Yes.
As we get the cell value expression format and the value of the cell, we can interpret the cell value as we wish.
All right.
What we want to do is to get the cell value in the appropriate datum type according to the cell value expression format of the cell. We will get the cell value expression string from the 'getString' method, and convert the string to the appropriate datum type. Specifically, we will get a java.time.LocalDate, java.time.LocalTime, java.time.LocalDateTime, java.lang.Boolean, java.lang.String, java.lang.Integer, or java.math.BigDecimal instance according to the cell value expression format. . . . I don't particularly claim that our way is optimal in the view of performance.
BigDecimal?
Yes. Precision is crucial when we handle data. So, we have to know what is the number of decimal places.
All right.
As for cell value expression formats, we won't deal with all the various possible expression formats here: we will deal with only specific expression formats we are going to use. As we are going to use only those specific expression formats, practically, that's enough for us.
What are those specific expression formats, exactly?
They are 'YYYY-MM-DD' for date, 'HH:MM:SS' for time, 'YYYY-MM-DD\"T\"HH:MM:SS' for datetime, 'BOOLEAN' for boolean, '@' for string, '0' for integer, and '0.0', '0.00', '0.000', . . ., 'Global', or 'Standard' for decimal fraction.
What do you mean by 'Global' or 'Standard'?
I don't know why, but 'Standard' is used when the locale setting of LibreOffice is set 'Japanese'. In most other cases, 'Global' is used.
Hmm.
What if those expression formats like 'YYYY-MM-DD\"T\"HH:MM:SS' don't exist in the document?
Actually, our program registers them if they don't exist.
Oh.
When the cell has a formula, we can get the formula itself by a method, 'getFormula', of the 'com.sun.star.table.XCell' interface.
I see.
After all, I have written this code.
import java.time.LocalDate;
import java.time.LocalTime;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.regex.Pattern;
import java.util.regex.Matcher;
import java.math.BigDecimal;
import com.sun.star.lang.Locale;
import com.sun.star.util.XNumberFormatsSupplier;
import com.sun.star.util.XNumberFormats;
import com.sun.star.util.MalformedNumberFormatException;
import com.sun.star.beans.XPropertySet;
import com.sun.star.beans.UnknownPropertyException;
import com.sun.star.text.XText;
import com.sun.star.table.CellContentType;
try {
// Gets the value of the current spread sheet cell START
String l_dateExpressionFormatString = "YYYY-MM-DD";
String l_timeExpressionFormatString = "HH:MM:SS";
String l_dateTimeExpressionFormatString = "YYYY-MM-DD\"T\"HH:MM:SS";
String l_booleanExpressionFormatString = "BOOLEAN";
String l_stringExpressionFormatString = "@";
String l_integerExpressionFormatString = "0";
String l_doubleDefaultExpressionFormatString = "Global";
Locale l_defaultLocale = new Locale ();
XNumberFormatsSupplier l_currentSpreadSheetsDocumentInXNumberFormatsSupplier = UnoRuntime.queryInterface (XNumberFormatsSupplier.class, l_currentSpreadSheetsDocumentInXSpreadsheetDocument);
XNumberFormats l_cellValueExpressionFormatsInXNumberFormats = l_currentSpreadSheetsDocumentInXNumberFormatsSupplier.getNumberFormats ();
int l_dateExpressionFormatKey = -1;
int l_timeExpressionFormatKey = -1;
int l_dateTimeExpressionFormatKey = -1;
int l_booleanExpressionFormatKey = -1;
int l_stringExpressionFormatKey = -1;
int l_integerExpressionFormatKey = -1;
int l_doubleDefaultExpressionFormatKey = -1;
l_dateExpressionFormatKey = l_cellValueExpressionFormatsInXNumberFormats.queryKey (l_dateExpressionFormatString, l_defaultLocale, false);
if (l_dateExpressionFormatKey == -1) {
l_dateExpressionFormatKey = l_cellValueExpressionFormatsInXNumberFormats.addNew (l_dateExpressionFormatString, l_defaultLocale);
}
l_timeExpressionFormatKey = l_cellValueExpressionFormatsInXNumberFormats.queryKey (l_timeExpressionFormatString, l_defaultLocale, false);
if (l_timeExpressionFormatKey == -1) {
l_timeExpressionFormatKey = l_cellValueExpressionFormatsInXNumberFormats.addNew (l_timeExpressionFormatString, l_defaultLocale);
}
l_dateTimeExpressionFormatKey = l_cellValueExpressionFormatsInXNumberFormats.queryKey (l_dateTimeExpressionFormatString, l_defaultLocale, false);
if (l_dateTimeExpressionFormatKey == -1) {
l_dateTimeExpressionFormatKey = l_cellValueExpressionFormatsInXNumberFormats.addNew (l_dateTimeExpressionFormatString, l_defaultLocale);
}
l_booleanExpressionFormatKey = l_cellValueExpressionFormatsInXNumberFormats.queryKey (l_booleanExpressionFormatString, l_defaultLocale, false);
if (l_booleanExpressionFormatKey == -1) {
l_booleanExpressionFormatKey = l_cellValueExpressionFormatsInXNumberFormats.addNew (l_booleanExpressionFormatString, l_defaultLocale);
}
l_stringExpressionFormatKey = l_cellValueExpressionFormatsInXNumberFormats.queryKey (l_stringExpressionFormatString, l_defaultLocale, false);
if (l_stringExpressionFormatKey == -1) {
l_stringExpressionFormatKey = l_cellValueExpressionFormatsInXNumberFormats.addNew (l_stringExpressionFormatString, l_defaultLocale);
}
l_integerExpressionFormatKey = l_cellValueExpressionFormatsInXNumberFormats.queryKey (l_integerExpressionFormatString, l_defaultLocale, false);
if (l_integerExpressionFormatKey == -1) {
l_integerExpressionFormatKey = l_cellValueExpressionFormatsInXNumberFormats.addNew (l_integerExpressionFormatString, l_defaultLocale);
}
l_doubleDefaultExpressionFormatKey = l_cellValueExpressionFormatsInXNumberFormats.queryKey (l_doubleDefaultExpressionFormatString, l_defaultLocale, false);
if (l_doubleDefaultExpressionFormatKey == -1) {
l_doubleDefaultExpressionFormatString = "Standard";
l_doubleDefaultExpressionFormatKey = l_cellValueExpressionFormatsInXNumberFormats.queryKey (l_doubleDefaultExpressionFormatString, l_defaultLocale, false);
if (l_doubleDefaultExpressionFormatKey == -1) {
// If your locale setting has a different default decimal fraction format string, you should use it.
}
}
CellContentType l_cellContentType = l_currentSpreadSheetCellInXCell.getType ();
XPropertySet l_currentSpreadSheetCellInXPropertySet = (XPropertySet) UnoRuntime.queryInterface (XPropertySet.class, l_currentSpreadSheetCellInXCell);
XText l_currentSpreadSheetCellInXText = (XText) UnoRuntime.queryInterface (XText.class, l_currentSpreadSheetCellInXCell);
if (l_cellContentType == CellContentType.EMPTY) {
}
else {
if (l_cellContentType == CellContentType.FORMULA) {
Publisher.show (String.format ("Got a formula: %s", l_currentSpreadSheetCellInXCell.getFormula ()));
}
int l_cellValueExpressionFormatKey = ((Integer) l_currentSpreadSheetCellInXPropertySet.getPropertyValue ("NumberFormat")).intValue ();
String l_cellValueExpression = l_currentSpreadSheetCellInXText.getString ();
if (l_cellValueExpressionFormatKey == l_dateExpressionFormatKey) {
LocalDate l_date = LocalDate.parse (l_cellValueExpression, DateTimeFormatter.ISO_LOCAL_DATE);
Publisher.show (String.format ("Got a LocalDate: %s", l_date.toString ()));
}
else if (l_cellValueExpressionFormatKey == l_timeExpressionFormatKey) {
LocalTime l_time = LocalTime.parse (l_cellValueExpression, DateTimeFormatter.ISO_LOCAL_TIME);
Publisher.show (String.format ("Got a LocalTime: %s", l_time.toString ()));
}
else if (l_cellValueExpressionFormatKey == l_dateTimeExpressionFormatKey) {
LocalDateTime l_dateTime = LocalDateTime.parse (l_cellValueExpression, DateTimeFormatter.ISO_LOCAL_DATE_TIME);
Publisher.show (String.format ("Got a LocalDateTime: %s", l_dateTime.toString ()));
}
else if (l_cellValueExpressionFormatKey == l_booleanExpressionFormatKey) {
Boolean l_boolean = Boolean.valueOf (l_cellValueExpression);
Publisher.show (String.format ("Got a Boolean: %s", l_boolean.toString ()));
}
else if (l_cellValueExpressionFormatKey == l_stringExpressionFormatKey) {
Publisher.show (String.format ("Got a String: %s", l_cellValueExpression));
}
else if (l_cellValueExpressionFormatKey == l_integerExpressionFormatKey) {
Integer l_integer = Integer.valueOf (l_cellValueExpression.replaceAll (",", ""));
Publisher.show (String.format ("Got an Integer: %s", l_integer.toString ()));
}
else {
Matcher l_matcher = Pattern.compile ("^(\\d*|\\d{1,3}(,\\d{3})*)(\\.\\d+)?([eE][-+]?\\d+)?$").matcher (l_cellValueExpression);
if (l_matcher.find ()) {
BigDecimal l_bigDecimal = new BigDecimal (l_cellValueExpression.replaceAll (",", ""));
Publisher.show (String.format ("Got a BigDecimal: %s", l_bigDecimal.toString ()));
}
else {
Publisher.show (String.format ("Got a String: %s", l_cellValueExpression));
}
}
}
// Gets the value of the current spread sheet cell END
}
catch (MalformedNumberFormatException | UnknownPropertyException | WrappedTargetException | RuntimeException l_exception) {
Publisher.show (l_exception.toString ());
}
In fact, we have implemented the above code into the test project included in the zip file cited in the preceding article. How to use the zip file is described in the article.
What are those cells at the right side of the 'Test1' button in 'TestSpreadSheetsDocumentForExecutingTests.ods'?
Those are cells in the cell value expression formats we expect. If we select one of those cells and push the 'Test1' button, the program will get the value of the selected cell, in the appropriate datum type.
<The previous article in this series | The table of contents of this series | The next article in this series>
