NassauSky Posted December 5, 2023 Share Posted December 5, 2023 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? Link to comment Share on other sites More sharing options...
Starocotes Posted January 12 Share Posted January 12 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? Link to comment Share on other sites More sharing options...
donnyh13 Posted January 12 Share Posted January 12 (edited) @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 LibreOffice UDF ; Scite4AutoIt Spell-Checker Using LibreOffice Spoiler "Life is chiefly made up, not of great sacrifices and wonderful achievements, but of little things. It is oftenest through the little things which seem so unworthy of notice that great good or evil is brought into our lives. It is through our failure to endure the tests that come to us in little things, that the habits are molded, the character misshaped; and when the greater tests come, they find us unready. Only by acting upon principle in the tests of daily life can we acquire power to stand firm and faithful in the most dangerous and most difficult positions." Link to comment Share on other sites More sharing options...
GMK Posted February 5 Author Share Posted February 5 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? Link to comment Share on other sites More sharing options...
Starocotes Posted February 6 Share Posted February 6 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. Link to comment Share on other sites More sharing options...
GMK Posted February 6 Author Share Posted February 6 Are you using LibreOffice or OpenOffice and which version, please? Link to comment Share on other sites More sharing options...
Starocotes Posted February 7 Share Posted February 7 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 Link to comment Share on other sites More sharing options...
GMK Posted February 8 Author Share Posted February 8 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? Link to comment Share on other sites More sharing options...
GMK Posted February 8 Author Share Posted February 8 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 1 Link to comment Share on other sites More sharing options...
donnyh13 Posted February 9 Share Posted February 9 (edited) 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 LibreOffice UDF ; Scite4AutoIt Spell-Checker Using LibreOffice Spoiler "Life is chiefly made up, not of great sacrifices and wonderful achievements, but of little things. It is oftenest through the little things which seem so unworthy of notice that great good or evil is brought into our lives. It is through our failure to endure the tests that come to us in little things, that the habits are molded, the character misshaped; and when the greater tests come, they find us unready. Only by acting upon principle in the tests of daily life can we acquire power to stand firm and faithful in the most dangerous and most difficult positions." Link to comment Share on other sites More sharing options...
Starocotes Posted February 9 Share Posted February 9 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. Link to comment Share on other sites More sharing options...
Starocotes Posted February 9 Share Posted February 9 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 ",". Link to comment Share on other sites More sharing options...
GMK Posted February 9 Author Share Posted February 9 If you changed that one line in _OOoCalc_WriteFromArray earlier, you may want to change it back. Link to comment Share on other sites More sharing options...
Starocotes Posted February 12 Share Posted February 12 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>" Link to comment Share on other sites More sharing options...
Starocotes Posted February 12 Share Posted February 12 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. Link to comment Share on other sites More sharing options...
rohmanabdur Posted February 12 Share Posted February 12 A newbie to AutoIt here.. I just want to thank @GMK for this awesome UDF. Absolutely amazing and inspiring. Starocotes 1 Link to comment Share on other sites More sharing options...
.thumb.png.82dc9d6dc958dcab3f977d6fefcf7ef4.png)
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now