[Solved] [Python] [Unohelper] Understanding the behaviour of setFormulaArray

[TTP]

Hello,

I'm fairly new to the libreoffice space, i've only picked it up recently after learning that the python integration enables me to create an xlsx generator that responds better to my needs than other options (especially openpyxl)

Despite some setbacks things seem to be advancing very well and the benchmarks are excellent so far, the only issue i'm having is understanding the behaviour of the function setFormulaArray when i'm trying to copy a formula from one cell to a range


my formula:
'=IF(NOT(ISNA(VLOOKUP($W2;$\'Clef RV\'.$A:$C;2;FALSE())));VLOOKUP($W2;$\'Clef RV\'.$A:$C;2;FALSE());
IF(NOT(ISNA(VLOOKUP($Y2;$\'Clef RV\'.$A:$C;2;FALSE())));VLOOKUP($Y2;$\'Clef RV\'.$A:$C;2;FALSE());
IF(NOT(ISNA(VLOOKUP($Y2*1;$\'Clef RV\'.$A:$C;2;FALSE())));VLOOKUP($Y2*1;$\'Clef RV\'.$A:$C;2;FALSE());"")))'

things i've tried so far:

#OK
cell_rv1 = sheet.getCellByPosition(0,1)
var = cell_rv1.getFormulaArray()
target_1_range = sheet.getCellRangeByName("A3")
target_1_range.setFormulaArray(var)

#KO, RuntimeException
cell_rv1 = sheet.getCellByPosition(0,1)
var = cell_rv1.getFormulaArray()
target_1_range = sheet.getCellRangeByName("A3:A10")
target_1_range.setFormulaArray(var)

#KO, Conversion Error
cell_rv1 = sheet.getCellByPosition(0,1)
formula1 = cell_rv1.Formula
formula1_tuple = ([formula1 for _ in range(1,nb_row)]) #also tuple([formula1 for _ in range(1,nb_row)])
target_1_range.setFormulaArray(var)

so and so,
basically i'm clearly lost to the embarassing point of not understanding the basics of this function, and i've been unfortunate in my search for documentation or souce code for this function (especially that it's on an another environment than what i normally use so my IDE isn't helping much)

What i'd appreciate is just any references or examples or pointers as to how i can take a formula on one cell and copy it onto the range without doing too many api calls (like how setDataArray is super efficient) , i can rebuild the formulas dynamically if needed i just haven't been satisfied with the benchmark of looping over the range and pasting the formula.

Thank you in advance and sorry for this amateur question

Edit 1: i found a reference to the function in the documentation, the runtime explaination is listed there and it's entirely my lack of understanding that caused the second case

[FJCC]

Here is a rough version of working code.

Code: Select all

sheet = ThisComponent.Sheets.getByName("Sheet1")
cell_rv1 = sheet.getCellByPosition(0,1)
var = cell_rv1.getFormulaArray()(0)

target_1_range = sheet.getCellRangeByName("A3:A10")
target_1_array = target_1_range.getFormulaArray()

for i = 0 to UBOUND(target_1_array())
    target_1_array(i) = var
next i

target_1_range.setFormulaArray(target_1_array())

[TTP]

Thank you for your reply, it's given me a breakthrough on this issue

for future people who might need help on this: my final code and takeaways

Code: Select all

            
            cell_rv1 = sheet.getCellByPosition(0,1)
            formula1 = cell_rv1.getFormulaArray()[0]
            target_1_range = sheet.getCellRangeByName(f"A3:A{nb_row}")
            target_1_array = list(target_1_range.getFormulaArray())
            for i in range(len(target_1_array)):
                target_1_array[i] = formula1 
            target_1_range.setFormulaArray(tuple(target_1_array))            

- getFormulaArray() returns a tuple so you'd have to cast it on python
- the formula remains static, i'll have to redynamically make the formula in the for loop but that's something that's up to me to figure out

Thank you for the response

[Villeroy]

Code: Select all

from com.sun.star.sheet.FillDirection import TO_BOTTOM
from com.sun.star.sheet.FillMode import SIMPLE

oTopRow.fillSeries(TO_BOTTOM, SIMPLE,0,0,0)