I use LibreOffice Api to craete .xlsx files at my application. I receive Date (DD/MM/YYYY), Time (HH:MM:SS), and DateTime (DD/MM/YYYY HH:MM:SS) values as strings and save them into a calc spreadsheet as strings (formulas). How can I properly save this data or convert it to a number in Date, Time, or DateTime format?
I tried to change Cell Format but it do not work, because Data are String.
How exactly do you fill these cells with data? Method setString does what it is named for.
A string and a formula are different “things”. If you really use formulas like="2024-01-26" you could use
Calcs DATEVALUE and TIMEVALUE
https://help.libreoffice.org/6.2/en-US/text/scalc/01/04060102.html
A real Date, Time and DateTime content of a cell is a numeric value: They are floating point numbers. The integer part is the Date (elapsed days since the base date), and the Time is the fraction part: 12:00:00 = 0.5 (in unit “days”).
someCell.FormulaLocal = "2024-1-26"
Property FormulaLocal represents the string that is shown in the formula bar. Setting FormulaLocal to some ISO date writes the correct date value.
I use Libre office Java api to create a XCellRangeData and set 2-dimensional Object Array .
XCellRangeData xData = UnoRuntime.queryInterface(XCellRangeData.class, xCellRange);
Object[ ][ ] dataArray = ;
xData.setDataArray(dataArray);
Calculate in Java the differences in days between 1899-12-30 and the dates in question. The resulting integers or floating point numbers are the correct values for your data array.
Alternatively, you can fill your array with strings and use setFormulaArray instead of setDataArray.
Formula string 3.14 writes a constant decimal.
Any ISO date/time as formula string writes the correct date/time value.
A number with leading apostrophe writes a numeric string.
A string with leading = writes an actual formula.
Edit: Small demo with MRI:
MRI_Arrays.ods (19.4 KB)
2 Likes
Thank you very much for your help and interest. I have one more question about this topic.
This method takes String parameters. But if i save numbers as string with this method, they can be still calculated and proceeded as number.
What happens if i save numbers and boolean with setFormulaArray?
You have a great opportunity to do your own research and analyze the results.
A particularly interesting case is when the fractional separator of numbers is a comma.
'
Sub Test
Dim oRange
ThisComponent.Sheets(0).GetCellRangeByPosition(0,0,3,0).setFormulaArray Array(Array("Text", "1", "1.123", "False"))
ThisComponent.Sheets(0).GetCellRangeByPosition(0,1,3,1).setFormulaArray Array(Array("Text", 1, 1.123, False))
End Sub