[WTA] Put the date value from Date Field control into a cell using Python macro

[akrogun]

Hi…
I can’t figure it out how to do it. How to put the date value from Date Field control into a cell using Python macro?

Thanks in advance.

[Villeroy]

Spreadsheets store serial day numbers instead of date values. In most cases, day zero is 1899-12-30. So you have to identify the document’s “NullDate” property which is a struct com.sun.star.util.Date, the control’s value which is of the same type, convert both structs into Python dates (tuple of numbers), calculate the difference in days and write this number into the cell.

P.S. easier alternative:
Concatenate year-month-day to an ISO date string and then set the cell’s formula.
oCell.FormulaLocal = "1999-12-31"
Setting property FormulaLocal does the same as entering the string into the formula bar.

[karolus]

Hallo
some additional info to the already given answer from @Villeroy

from datetime import datetime as dt #python
from com.sun.star.util import Date  #uno-api
# roundtrip from uno-Date via python-datetime to calc
uno_date = Date()
uno_date.Year, uno_date.Month, uno_date.Day = 2023, 5, 25 
py_date = dt(year=uno_date.Year, month=uno_date.Month, day=uno_date.Day)
calc_zero = dt(1899, 12, 30)
# see:
# https://docs.python.org/3/library/datetime.html?highlight=toordinal#datetime.datetime.toordinal
calc_date = py_date.toordinal() - calc_zero.toordinal()

[mikekaganski]

So, your Python macro would to the following:

1. Get the form control (maybe using the respective draw page’s getForms(), and its getByIndex()).
2. Get the control’s Date property (when set, it would be a com.sun.star.util.Date)
3. Get the spreadsheet’s epoch
   3.1. Get the config provider, instantiating ConfigurationProvider service
   3.2. Create a 1-element sequence of com.sun.star.beans.PropertyValue, with Name equal to "nodepath", and Value set to "org.openoffice.Office.Calc/Calculate/Other/Date"
   3.3. Call config provider’s createInstanceWithArguments, passing "com.sun.star.configuration.ConfigurationAccess" and the sequence from 3.2, to get a ConfigurationAccess service instance pointing to the key with the epoch data
   3.4. Get the year, month, and date from the key, like

  dt(year=oKey.getByName("YY"), month=oKey.getByName("MM"), day=oKey.getByName("DD"))

4. Create the Python date from the two UNO date from step 2, and assign the date ordinals’ difference (between the control’s date and epoch) to the wanted cell’s value.

A similar BASIC function, illustrating the sequence, is

Sub DateToCell
 myControl = thisComponent.DrawPages(0).Forms(0)(0)
 myDate = myControl.Date

 oConfigProvider = createUnoService("com.sun.star.configuration.ConfigurationProvider")
 Dim aNodePath(0) as new com.sun.star.beans.PropertyValue
 aNodePath(0).Name = "nodepath"
 aNodePath(0).Value = "org.openoffice.Office.Calc/Calculate/Other/Date"
 oKey = oConfigProvider.createInstanceWithArguments("com.sun.star.configuration.ConfigurationAccess", aNodePath())

 myDate0 = DateSerial(oKey.getByName("YY"), oKey.getByName("MM"), oKey.getByName("DD"))

 thisComponent.Sheets(0).getCellByPosition(0,0).Value = CDateFromUnoDate(myDate) - myDate0
End Sub

Note that assigning cell’s formula or FormulaLocal can have side effects of also setting the cell’s number format, which may or may not be wanted.

And indeed, all my complexity of getting the epoch is an idiocy, @Villeroy’s advise to get the document’s NullDate property is way better.

[Villeroy]

def some_date_control_event(ev):
    dt = ev.Source.getDate()
    doc = XSCRIPTCONTEXT.getDocument()
    some_cell = doc.Sheets(0).getCellByPosition(0,0)
    some_cell.FormulaLocal = str(dt.Year) +'-'+ str(dt.Month) +'-'+ str(dt.Day)

FormulaLocal takes care of the document’s NullDate and applies ISO format on the fly if the cell has number format “General”.

[mikekaganski]

def UnoDateToPythonDateTime(date_uno):
    return datetime(year=date_uno.Year, month=date_uno.Month, day=date_uno.Day)

def UnoDateToDocSerialDate(date_uno, doc):
    return UnoDateToPythonDateTime(date_uno).toordinal() - UnoDateToPythonDateTime(doc.NullDate).toordinal()

[karolus]

@Villeroy

some_cell.FormulaLocal = str(dt.Year) +'-'+ str(dt.Month) +'-'+ str(dt.Day)

better:

some_cell.FormulaLocal = f"{dt.Year}-{dt.Month}-{dt.Day}"

[akrogun]

Thank you mikekaganski. Trying to convert those code into Python.