Python macro vs Calc date field

[mrkalvin]

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 com.sun.star.awt import MessageBoxButtons as MSG_BUTTONS

CTX = uno.getComponentContext()
SM = CTX.getServiceManager()
ODOC = XSCRIPTCONTEXT.getDocument()

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

def openConsole(event=None):
    """ 
        https://extensions.libreoffice.org/extensions/apso-alternative-script-organizer-for-python
    """
    create_instance("apso.python.script.organizer.impl")
    from apso_utils import console
    #console()
    console(BACKGROUND=0x0, FOREGROUND=0xD3D7CF)
    return

def test(*args):
    openConsole()

    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}'''
                print(text)

---

Example file:
test.ods

[karolus]

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

[karolus]

Hallo

# 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

print(f'{lo_epoch.Year}-{lo_epoch.Month}-{lo_epoch.Day}')

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

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])

print(lo_to_pydate(44444.333))

def lo_to_pydate2(fp):
    #….fromordinal(days:integer) 
    # -> 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)



print(lo_to_pydate2(44444.333))

# for representing dates as string, see
# https://docs.python.org/3/library/datetime.html#strftime-and-strptime-format-codes
# ….isoformat()

[mrkalvin]

Thank you for your help