Python macro vs Calc date field

Python macro vs date field

In a python macro, I get the data from a range of cells from the calc using the command ‘.getDataArray()’, but in one of these cells there is a date field (05/05/21) which is converted to float (44321.0). How do I re-convert this to the string date format as it is displayed in the spreadsheet (05/05/21)?

I tried it with python’s datetime, but it didn’t work. Converted float (44321.0) to wrong date (01/01/1970)

# -*- coding: utf-8 -*-
from __future__ import unicode_literals
import uno
from import MessageBoxButtons as MSG_BUTTONS

CTX = uno.getComponentContext()
SM = CTX.getServiceManager()

def create_instance(name, with_context=False):
    if with_context:
        instance = SM.createInstanceWithContext(name, CTX)
        instance = SM.createInstance(name)
    return instance

def openConsole(event=None):
    from apso_utils import console
    console(BACKGROUND=0x0, FOREGROUND=0xD3D7CF)

def test(*args):

    ODOC = XSCRIPTCONTEXT.getDocument()
    oSheets = ODOC.getSheets()
    planAgenda = oSheets.getByName("Sheet1")
    cellContent = planAgenda.getCellRangeByName('A2:C3')
    valueContent = cellContent.getDataArray()
    import datetime

    for line in valueContent:
        for col in line:
            if isinstance(col, float):
                mydate = datetime.datetime.fromtimestamp(col).strftime("%d/%m/%Y")

                text = f'''float date: {col} to string date: {mydate}'''

Example file:

If in doubt, please wet your feet at Datetime-documentation


# unlike Basic, python does *not* share the primitive `Floating-point-days since LO_epoch`-arithmetik
# so you have to calculate the the Python-datetime in some way.

from datetime import date, datetime as dt, timedelta
from time import gmtime

doc = XSCRIPTCONTEXT.getDocument()

unix_epoch = dt(*gmtime(0)[:6]) # 1970-01-01
lo_epoch = doc.NullDate            # !default!: 1890-30-12


lo_epoch_as_dt = dt(lo_epoch.Year,lo_epoch.Month, lo_epoch.Day)

DAYDIFF2UNIX = (unix_epoch - lo_epoch_as_dt).days

def lo_to_pydate(fp): 
    # substract the day_difference,
    # calculate the seconds since unix_epoch
    # and feed gmtime(seconds)
    return dt(*gmtime((fp - DAYDIFF2UNIX)*24*60*60)[:6])


def lo_to_pydate2(fp):
    # -> returns date from given integer days since
    # gregorian date `0001-01-01`
    # ….toordinal do the inverse, but both only with integers!
    days, dayfraction = divmod(fp, 1)
    days = int(days)
    second = dayfraction*24*60*60
    return dt.fromordinal(int(days)+lo_epoch_as_dt.toordinal()) + timedelta(seconds=second)


# for representing dates as string, see
# ….isoformat()

Thank you for your help