OOo/LibO Calc UDF

[NassauSky]

Hi all,

I noticed when I use

$saveResult = _OOoCalc_BookSaveAs($oCalc, $savePath & $saveFilename, "MS Excel 97", True),

it doesn't set the current working directory for Open Office. How do I set it so that using the file open menu in OO brings me to my $savepath?

[Starocotes]

Hi,

we seem to have a problem with the UDF not properly inserting values into a Calc sheet.

We use the WriteFromArray Function but all numbers are displayed as text and when I switch the celltype manualy a ' is inserted before the value. 

We allready tried to switch the numberformat (decimal point to comma) und unsing a template, but both didn't help. Any way we can force numbers as numbers?

[donnyh13]

@Starocotes, Until GMK gets back to you.

Edit* — I was wrong.

As a temporary fix, you could change the line used in _OOoCalc_WriteFromArray from this:

$oRange.setDataArray($avOOoArray)

To this:

$oRange.setData($avOOoArray)

 

setDataArray inserts data as strings. SetData inserts (and supports) only numbers.

Edited February 9 by donnyh13

[GMK]

Sorry @Starocotes.  I didn't even see your comment until now.  Do you have an example--some code that you could produce that shows the issue?

[Starocotes]

Func __export2OO(byref $bOoTemplate, byref $sOoTemplateFileName, byref $sOoTemplateStartCell, byref $sOutFileName, byref $oQuery)
    Local $oCalc = _OOoCalc_BookNew(False)
    $RC = _OOoCalc_WriteFromArrayDirect($oCalc, __SQLQuery2Array($oQuery), $sOoTemplateStartCell,-1,-1,False,'~') ;Writes the array at the cell
    $RC = _OOoCalc_BookSaveTo($oCalc, $sOutFileName, $sSaveType)
    _OOoCalc_BookClose($oCalc)
EndFunc

That's basically it. The $oQuery is getting Data from an MS SQL Database.

[GMK]

Are you using LibreOffice or OpenOffice and which version, please?

[Starocotes]

14 hours ago, GMK said:

Are you using LibreOffice or OpenOffice and which version, please?

Version: 7.5.9.2 (X86_64) / LibreOffice Community
Build ID: cdeefe45c17511d326101eed8008ac4092f278a9
CPU threads: 4; OS: Windows 10.0 Build 22621; UI render: Skia/Vulkan; VCL: win
Locale: de-DE (de_DE); UI: de-DE
Calc: threaded

[GMK]

I'll have to play around with it at home this evening.  In the meantime, is it possible to loop through an array from __SQLQuery2Array and get all the data types to see if it's what you're expecting?

[GMK]

On 1/12/2024 at 8:09 AM, donnyh13 said:

setDataArray inserts data as strings. SetData inserts (and supports) only numbers.

setDataArray is supposed to insert both numbers and strings:

Quote

Description fills the cell range with values from an array. The size of the array must be the same as the size of the cell range. Each element of the array must contain a double or a string.

 

[donnyh13]

4 hours ago, GMK said:

setDataArray is supposed to insert both numbers and strings:

You're right GMK, my apologies. I had that mixed up with Formulas not inserting correctly using that.

I'm guessing you are correct, I bet the values are being returned as strings from in the SQL array.

Best wishes,

Edited February 9 by donnyh13

[Starocotes]

15 hours ago, GMK said:

I'll have to play around with it at home this evening.  In the meantime, is it possible to loop through an array from __SQLQuery2Array and get all the data types to see if it's what you're expecting?

You are right, they are all strings. 

Now I have to figure out how to get the REAL fieldtypes.

[Starocotes]

Okay, I found this one problem ans fixed it. Not the array contains int32 and double values no strings anymore. 

But the the output in LibreOffice is still considered a string I guess it has something to do with the localisation and the different handling of "." and ",".

[GMK]

If you changed that one line in _OOoCalc_WriteFromArray earlier, you may want to change it back.

[Starocotes]

On 2/9/2024 at 10:17 PM, GMK said:

If you changed that one line in _OOoCalc_WriteFromArray earlier, you may want to change it back.

No I did not change that line. 

It sill is rendered as Text

"<text:p>1.77</text:p>"

 

[Starocotes]

The problem seems to be the way I call _OOoCalc_WriteFromArrayDirect

$RC = _OOoCalc_WriteFromArrayDirect($oCalc, __SQLQuery2Array($oQuery), $sOoTemplateStartCell,-1,-1,False,'~') ;Writes the array at the cell

The ~ as the last parameter converts the whole array into strings.

$RC = _OOoCalc_WriteFromArrayDirect($oCalc, __SQLQuery2Array($oQuery), $sOoTemplateStartCell,-1,-1,False) ;Writes the array at the cell

Finaly works.
 

[rohmanabdur]

A newbie to AutoIt here..
I just want to thank @GMK for this awesome UDF.
Absolutely amazing and inspiring.