[Solved] LibreOffice 5.2: Convert cell date to datevalue

[owlbrudder]

I am trying to write a macro to convert a spreadsheet cell from a date string to a datevalue.

I found a post from 2011 on this topic and copied the code. I edited the code to improve brevity and reflect my requirement.

Code: Select all

Sub S_format_cellrange
    Dim Localformat as new com.sun.star.lang.Locale
    Dim objDoc As Object
    Dim objNumberFormats As Object
    Dim objNumberFormatstring As String
    Dim objNumberFormatid As Long
    Dim objSheet As Object
    Dim objRange As Object
    Dim varData As Variant
    Dim i As Integer
    Dim k As Integer
    
	If Not Globalscope.BasicLibraries.isLibraryLoaded("MRILib") Then
		Globalscope.BasicLibraries.LoadLibrary( "MRILib" )
	End If

	oMRI = CreateUnoService("mytools.Mri")
	
    objDoc = ThisComponent
    objNumberFormats = objDoc.NumberFormats
    objNumberFormatstring = "DD/MM/YYYY"
    objNumberFormatid = objNumberFormats.queryKey(objNumberFormatstring, Localformat, True)
    if objNumberFormatId = -1 then 'not existing yet
        objNumberFormatId = objNumberFormats.addNew(objNumberFormatstring,Localformat)
    end if
    objSheet = ThisComponent.Sheets.getByName("Dictionary")
    objRange = objSheet.GetCellRangeByName("A2:A2")'<-------------Cellrange to be converted
    varData = objRange.getDataArray()
    objRange.ClearContents(4)
    objRange.NumberFormat = objNumberFormatId
    oMRI.inspect varData
    for i = 0 to uBound(varData)
        for k = 0 to UBound(varData(i))
        	If Not (varData(i)(k) = "") Then
            	varData(i)(k) = datevalue(replace(varData(i)(k),"'",""))'trim single quotes from around string
            End If
        next k
    next i
    oMRI.inspect varData
    objRange.setDataArray(varData)
End Sub

My code seems to work fine, correctly converting "11/02/2017" to the date 11/02/2017 and updating the varData array correctly according to the watched variables as I step through the code.

The final 'oMRI.inspect varData' results in the error "Object Not Set" and the data is shown as a Date whose value is zero, yet the watch window at that time shows the value as Variant/Date with the value 11/02/2017 - see attached screen-shot.

If I comment out the for loop, the code runs to completion - as expected.

I would be very grateful if someone could point out my error.

Cheers,
Doug

[JeJe]

Try omitting the datevalue

[karolus]

Hallo

You should import your Stuff in the proper way to avoid all this load of crap…

Code: Select all

Sub S_format_cellrange

    Dim Localformat as new com.sun.star.lang.Locale

    doc = ThisComponent

    Localformat.Language = "de"  'replace by your Locale-settings…'
    Localformat.Country = "DE"    '… or try to comment out the line below…'
    'Localformat = doc.CharLocale'
    
    NumberFormats = doc.NumberFormats
    Formatstring = "JJJJ-MM-TT" 'replace to your needs'
    Formatid = NumberFormats.queryKey( Formatstring, Localformat, False)
    if FormatId = -1 then 'not existing yet'
        FormatId = NumberFormats.addNew( Formatstring,Localformat)
    end if
    Sheet = Doc.Sheets.getByName("Dictionary")
    oRange = Sheet.GetCellRangeByName("A2:A2")'<-------------Cellrange to be converted'
    data = oRange.getDataArray()

    oRange.NumberFormat = FormatId
    oRange.setFormulaArray( data )
End Sub

The Text-Date-input should also have a similar Entry in →Tools→Options→LanguageSettings→Language→→Datepatterns

[Villeroy]

Date'values in spreadsheets do not exist. And if a cell value is text or number hardly depends on the number format. The only format thing that matters is that any number format code with "@" (category "Text") will treat new input as literal text even if it is numeric input and even if it is a formula expression with a leading =. Oh, and any % format divides the input value by 100.

The easiest method:
1. Select the column in question
2. Any number format except "Text"
3. Data>Text to Columns...
3.1 Language: English(UK) if 11/2/17 is meant to be 11th Feb.
3.2 Unselect all column delimiters.
[OK]

Alternatively:

Edit: OpenOffice only because of the stupid "date recognition patterns" in LibreOffice

1. Selection can be a range or multiple ranges
2. Any number format except "Text"
3. Edit>Find&Replace
3.1. [More Option]
3.2. Current selection = ON
3.3. Regular expressions = ON
4. Search: .+ (a point and plus)
5. Replace: &
[Replace All]

You may write a macro based on the second method but in my honest opinion it is not worth the effort.

[Villeroy]

Anyway:

Code: Select all

REM  *****  BASIC  *****

Sub test_Text2Numbers()
REM test Text2Numbers with current selection
REM supporting selections of multiple ranges and sheets

sel = ThisComponent.getCurrentSelection()
if sel.supportsService("com.sun.star.sheet.SheetCellRange") then
REM quick method to convert a range into a collection of ranges
	rgs = sel.queryIntersection(sel.getRangeAddress())
elseif sel.supportsService("com.sun.star.sheet.SheetCellRanges") then
	rgs = sel
else
	MsgBox "No sheet cells selected", 16, "macro:Text2Number"
	exit sub
endif
Text2Numbers rgs
End Sub

Sub Text2Numbers(rgs)
for each rg in rgs
	resetTextNumberFormat(ThisComponent, rg)
next rg
sd = rgs.createReplaceDescriptor()
sd.setSearchString(".+")
sd.setReplaceString("&")
sd.SearchRegularExpression = True
rgs.replaceAll(sd)
End Sub

Sub resetTextNumberFormat(oDoc, oRange)
REM reset all text format ranges to number format 0
REM unless they are set by a cell style 
nfs = oDoc.getNumberFormats()
ufrgs = oRange.getUniqueCellFormatRanges()
e = ufrgs.createEnumeration()
while e.hasMoreElements()
	rgs = e.nextElement()
	n = rgs.NumberFormat
	nf = nfs.getByKey(n)
	if nf.Type = com.sun.star.util.NumberFormat.TEXT _
		AND rgs.getPropertyState("NumberFormat") = com.sun.star.beans.PropertyState.DIRECT_VALUE then
		rgs.NumberFormat = 0
	endif
wend
End Sub

if you want to preserve text in explicitly formatted cells, remove the foreach loop with resetTextNumberFormat. But then any numbers in text cells will be converted to text. The replacement basically "re-types" every cell value. The result depends on the interpretation context, namely text format, percent format or any other format and the number format locale.

Edit: I added a little improvement (well, at least I think it is): The text format wil only be reset if it is set by hard formatting, assuming that an explicit cell style with that number format is intentional.

[Villeroy]

To my positive surprise, the above macro overrides the very, very stupid "allowed date recognition patterns" in LibreOffice. It really converts text to numbers even if the "date recognition pattern" does not match with the cell text.
OK, the text-2-columns method does that too. But now we have a conversion that supports multiple ranges of all shapes.

[owlbrudder]

Thank you all for your helpful replies. Villeroy, as usual your reply was exactly what I needed to solve my problem. It just proves there is more than one way to code most solutions.

Once again everyone, thank you very much. "8-)

[owlbrudder]

You should import your Stuff in the proper way

It would be easy if I was importing data, but the current case is I am presenting a dialog for user input of various data, then writing the relevant items to a spreadsheet and a Writer document. I want to get the above code working so I understand how to do it - I am still on the steep part of the LO Basic learning curve.

Code: Select all

...
    data = oRange.getDataArray()

    oRange.NumberFormat = FormatId
    oRange.setFormulaArray( data )

Now that I understand Calc only understands strings and numbers with various formats, I can see how this would work. Nifty.

Thanks for your reply.
Doug

[owlbrudder]

Try omitting the datevalue

Thanks for the suggestion JeJe. I will run the code with that change to see what happens.

Thanks for your reply,
Doug

[owlbrudder]

AND rgs.getPropertyState("NumberFormat") = com.sun.star.beans.PropertyState.DIRECT_VALUE then

I had not thought of examining the beans hierarchy to use within Basic, but now that you have introduced it I will embark on yet another learning curve. Wow, it's fun learning new stuff!

Kind regards,
Doug

[owlbrudder]

oRange.setFormulaArray( data )

Karolus, your code works perfectly, although it took me two passes to realise the highlighted statement was Formula, not Data as I had previously.

Thanks very much for the help.
Doug

[owlbrudder]

sd.setReplaceString("&")

I think I understand what is going on here, but I have not seen it expressed in this way before.

If I am right, the '&' replaces the text found by the search string ".+", thus writing the same text as before into the cell, but the cell now has a numeric type and a date format, so the result is the date string becoming a number formatted as date.

Is that correct?

Your code is full of good stuff for me to get my head around and I thank you for it.

Cheers,
Doug

[Villeroy]

o the result is the date string becoming a number formatted as date.

The result is the same as if you enter the same string manually into this cell.

It depends on
1. The number format locale (if unset, then the global application locale). If the number format locale does not match the text, every method of conversion must fail. You get either unmodified cells (ok), wrong values (bad) or a mix of unmodified text and wrong values (very bad).

2. If the number format is something with "@" (text) my code will do one of two things
2a. Nothing if the number format belongs to the underlying cell style
2b. Apply number format 0 which is the "General" number format for the default number format locale before doing the replacement.

3. If the number format is a percent format, the numeric string will be divided by 100. "23.99" becomes 0.2399 formatted as 23.99% and "2/3/2014" becomes 4170000.00% because 2/3/14 is day number 41700. 41700 formatted as percent is displayed as 4170000%, formatted as date, the same value is displayed as 2/3/2014.

Apart from point 2b, the code does not touch formatting attributes. It simply re-types all the formulas. If you don't want 2b, you can apply proper cell styles or comment out the for..next loop calling resetTextNumberFormat.

[owlbrudder]

The result is the same as if you enter the same string manually into this cell.

... because the ampersand in the regexp has been populated with the text found by the search term, which finds the whole content of the cell - no? I am fairly comfortable with regexps, but had not seen one constructed quite this way, so wanted to confirm I understood it.

The rest of your reply is very clear thank you.

I have worked through the code you posted earlier and identified the uno documentation each module, interface and value you used, so it has been a great education. I think I learned more doing that than I have in the whole week. "8-)

Half the battle for me is knowing where to look for the method or constant I want, but I am understanding more as time goes by.

One area I am not up to speed on is the executeDispatch options. If I record a macro, I get a range of dispatch statements and it would be really helpful to have a document explaining all the available options. Is this asking too much of the documentation? Is there a source code file or module where I would find these?

I really appreciate the time you have taken to help me.

Cheers,
Doug

[RoryOF]

Does this not help somewhat?
https://www.openoffice.org/api/docs/com ... elper.html

[Villeroy]

The ampersand is NOT part of the cell value.

Enter '123 into an ordinary cell A1.
=LEFT(A1) => 1
LEN(A1) => 3

Search for the ampersand. It will not be found.

[owlbrudder]

The ampersand is NOT part of the cell value.

I didn't explain myself clearly. The regexp consists of a search term ".+" (which finds the entire text contained in the cell) and a result term "&" which is populated with the text found by the search term (which is the entire text contained in the cell).

When objRanges.replaceAll(objSearchReplace) executes, it iterates over the cells, searching for all the text in a cell via the search term ".+" and rewriting it with the same text contained in the "&" term.

I am going to play with regular expressions in the search/replace object to see how complex they can be. I don't have a use for the complex expressions yet, but I want to know. It comes of being a programmer for 30 years before I retired - I'm inquisitive. "8-)

Cheers,
Doug

[owlbrudder]

Does this not help somewhat?

Yes - very useful as far as it goes, but I am still digging to find a description of each possible option. For example:

Code: Select all

executeDispatch(document, ".uno:SaveAs", "", 0, args1())

Is the dispatch that calls the Save As dialog.

The parameter 'document' is clearly the document on which the dispatch is to be executed, which may not be ThisComponent.

The option "uno.SaveAs" is obviously one of many uno actions, but I can't find a list of these. This term is documented as "URL", which makes me wonder if it can contain a document url for some dispatch actions.

The third null string option is documented as TargetFrameName and I expect the null string means use the current frame. Clearly it could also contain the name of a different active frame - perhaps a second document being manipulated by the macro?

The parameter containing 0 is documented as "SearchFlags - optional search parameter for finding the frame if no special TargetFrameName was used", but there is no indication of what these flags are and what their valid values are.

The empty array is documented as"Arguments - optional arguments for this request They depend on the real implementation of the dispatch object." Hmmm. Where do I find documentation of the real implementation of the dispatch object?

The level of documentation is fantastic, considering it is put together by volunteers and I am not asking to be spoon-fed with answers, only to be pointed in the right direction, even if this means reading source code - I just need to know where to look. "8-)

Cheers,
Doug

[Villeroy]

Dispatch macros can be useful but they can not cover more than 1% of what you expect from macro programming.
The API is self-documenting and there are at least 2 addons which help you query the objects for their properties, methods, interfaces and services:
viewtopic.php?f=74&t=49294

[owlbrudder]

This code walks through the list(s) of uno commands available to a given frame. It gives me the information I was looking for and others may find it useful. Kudos to those who developed the online API documentation.

Code: Select all

Sub subInspectUnoCommands
	Dim seqCommandGroups As Variant
	Dim seqCommandDetails As Variant
	Dim cmdGroup As Integer
	
	If Not Globalscope.BasicLibraries.isLibraryLoaded("MRILib") Then
		Globalscope.BasicLibraries.LoadLibrary( "MRILib" )
	End If
	oMRI = CreateUnoService("mytools.Mri")

	'Find the list of command groups supported by this frame
	seqCommandGroups = ThisComponent.CurrentController.Frame.getSupportedCommandGroups()
	
	For Each cmdGroup In seqCommandGroups
		'Get the details of the commands
		seqCommandDetails = ThisComponent.CurrentController.Frame.getConfigurableDispatchInformation(cmdGroup)
		oMRI.inspect seqCommandDetails
	Next	
End Sub

Thanks to those who have helped me find this solution to my problem.