[Python][PGSQL] passing dates through setDataArray

[TTP]

Hello,

I'm trying to use libreoffice as a wrapper to transfer postgreSQL data onto a templated xlsx file (work constriants) and so far i am very pleased with the many features it gives. I'm facing a few difficulties as i am relatively new to this and one blocking difficulty is pasting data.

So far i've been working with a CSV as a data source to ensure that i have the basics of I/O down and now that i've moved to importing data directly using psycopg2 i've been getting errors because of the datetime format.

a simplified version of what i've been trying (same calls, just without parametered wrappers)

Code: Select all

	data = cursor.fetchall() #returns list of tuples
	nb_row = len(data)
        nb_col = len(data[0]) if nb_row > 0 else 0
        cell_range = sheet.getCellRangeByPosition(start_column, start_row, start_column + nb_col -1, start_row + nb_row -1)
        cell_range.setDataArray(tuple(data))

gives this error

Code: Select all

uno.com.sun.star.uno.RuntimeException: Couldn't convert 2020-07-01 to a UNO type; caught exception: <class 'AttributeError'>: 'datetime.datetime' object has no attribute 'getTypes', traceback follows 

solutions I've tried:

converting any instance of datetime in data to uno.createUnoStruct("com.sun.star.util.Date") by referencing the member data documentation in the libreoffice doc (could've been a misuse)

Code: Select all

            if isinstance(value, datetime):
                uno_date = uno.createUnoStruct("com.sun.star.util.Date")
                uno_date.Year = value.year
                uno_date.Month = value.month
                uno_date.Day = value.day
                processed_row.append(uno_date)

Threw a Runtime Exception

Another idea was just to do processed_row.append(str(value)) but that made me lose the formatting (without a manual intervention on my part after the xlsx was generated)

A Final idea was to do processed_row.append(value.date()) in hopes that python would reformat it in a way that OO likes but it threw the same runtime exception as the original issue

beyond these I've looked for similar questions on the forum and haven't found exactly what I'm looking for and so I solicite your help and remarks.

Bonus question (I will mark this thread as solved even without this as it's not a big issue but I didn't feel appropriate to open two threads in a row) :

suppose I have a formula in A2 that references say, the cell B2
and i want to copy this formula over a range A3:A15000
the way i've been doing it is by using re.sub to replace the 2 with the row index, getting all that in a tuple then using setFormulaArray over that tuple but sometimes the setFormulaArray call takes about a minute, is this normal or am I doing the wrong approach?

[Villeroy]

menu:File>New>Database...
Connect to existing database
Type: PostgreSQL or ODBC
Specify the URL or ODBC name
[X] Register the database
Save the database document.
Add some meaningful queries.

In Calc, get the data source window [Ctrl+Shift+F4].
Expand your registered data source and drag any table or query icon from the left pane onto a sheet cell. This creates a linked database range named "Import1".

Code: Select all

objDoc.DatabaseRanges.getByName("Import1").refresh()

refreshes that range.
[Tutorial] Using registered datasources in Calc

[Villeroy]

[Tutorial] Ten concepts that every Calc user should know (part 3 about dates]

Regarding your self-made approach, just enter a date into a sheet cell and inspect the cell.
The cell has an integer day number counting days since 1899-12-30. The integer is displayed in whatever format you apply to that number. In case of number format "General", it is displayed in some locale dependent default format for dates. Formatted numbers 23/12/2024, 12/23/2024 and $45,649.00 represent the exact same cell value 45649 (today's day number).

Your Python program can calculate the correct day numbers. Alternatively, you may use setFormulaArray instead of setDataArray.
setFormulaArray accepts arrays with 3 types integer, float and string.
- The empty string represents a blank cell.
- A string with a leading apostroph represents a numeric string.
- A string with a leading = will be interpreted as formula expression.
- An ISO string like '1999-12-31' enters the correct, but unformatted, day number.
- Decimals and integers are just that.