Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (2)

Resolved Problems with controlling OpenOffice via OLE

  1. Issue
0
Votes
Undo
  1. Daniel Vivier
    Daniel Vivier
  2. PowerBuilder
  3. Thursday, 15 October 2020 14:24 PM UTC

Using PB 2019, I'm trying to use OpenOffice through OLE to open an Excel doc and save it as CSV. (Yes, I can do that Excel, but this is for a situation where OpenOffice is available but Excel isn't.)

I'm running into two problems: the SaveAsURL (or SaveToURL) always fails with an OpenOffice IOError, and the OpenOffice application never closes (as seen on Task Manager).

Here's my code (in a function taking asFilename as its argument):

iole_app = CREATE OLEObject
OLEobject desktop, prop, props[], document
string fixedFilename, saveFilename
int pos

if iole_app.ConnectToNewObject("com.sun.star.ServiceManager") <> 0 THEN
    MessageBox('Test', 'Unable to start OpenOffice.',Exclamation!)
    DESTROY iole_app
    return FALSE
end if

desktop = iole_app.createInstance("com.sun.star.frame.Desktop")
prop = iole_app.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
prop.Name = "Hidden"
prop.Value = TRUE // don't want to show UI
props[1] = prop
fixedFilename = "file:///" + asFilename
gf_replace_all(fixedFilename, "\", "/") // replaces the 2nd arg with the 3rd throughout the 1st arg

document = desktop.loadComponentFromURL(fixedFilename, "_blank", 0, props)

pos = LastPos(Lower(fixedFilename), ".xls")
if pos = 0 then
    MessageBox("Test", "Cannot find .xls in filename")
    DESTROY iole_app
    return FALSE
end if
saveFilename = Mid(fixedFilename, 1, pos) + "csv"
props[1].Name = "FilterName"
props[1].Value = "scalc: Text - txt - csv (StarCalc)"
props[2] = iole_app.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
props[2].Name = "Overwrite"
props[2].Value = TRUE

try
    document.storeToURL(saveFilename, props)
catch (OLERuntimeError ex)
    MessageBox("Test", "Error " + ex.description + " saving to " + saveFilename + "~n~n: " + ex.text)
end try

document.close(TRUE)
DESTROY document
SetNull(document)

desktop.dispose()
DESTROY desktop
SetNull(desktop)

DESTROY iole_app
SetNull(iole_app)

return TRUE

I know I am doing overkill on the DESTROYs and also SetNull, but I'm trying everything. If I change the property "Hidden" used in the properties sent to loadComponentFromURL() to FALSE (or omit it, since FALSE is the default) the application does close after the code finishes. 

But document.storeToURL (or the alternative storeAsURL) always throws a com.sun.star.task.ErrorCodeIOException, with no further helpful details. I have checked that the file URL it is supplying is OK, and I know that the directory the file is in is writeable.

Any bright ideas? Thanks.

  1. OLE
  2. PowerBuilder (Appeon)
whatsup app
special characters search and filter
Responses (2)
Daniel Vivier
Daniel Vivier Accepted Answer Pending Moderation
  1. Tuesday, 20 October 2020 14:32 PM UTC
  2. PowerBuilder
  3. # 1

Thank. By now I have read a ton of OpenOffice forum posts - their docs are pretty weak and/or hard to understand, so you have to do huge amounts of searching to figure things out. I have most things figured out now, though some things aren't working as well as I might have hoped. But the initial task, converting Excel to CSV, is working.

And yes, that post you referred to does give the right FilterName, which has to be exactly "Text - txt - csv (StarCalc)". It also very much doesn't help that OO's error messages are so uninformative about what the problem might be.

Another bit of code that had to be added to what I have above is the following, to make sure the right CSV options are used:

// Add special FilterOptions for CSV export
// The comma-separated FilterOptions here are the ascii value of the character for the separator (comma),
// the ascii value of the quote character, the character set (76 is UTF8), and the number of the first line to export.
// See https://wiki.openoffice.org/wiki/Documentation/DevGuide/Spreadsheets/Filter_Options#Tokens_1_to_5
props[3] = of_property("FilterOptions", String(Asc(",")) + "," + String(Asc('"')) + ",76,1")

I think what solved the problem of the application not terminating was replacing desktop.dispose() with desktop.terminate().

Finally another problem I ran into was that the order of the components (year, month and day) of exported dates was following a Language option in OpenOffice, rather than your Windows short date format. That would mean that if I then tried to import the CSV, which required interpreting the dates, they might come out wrong. So I had to add the following code, before creating the desktop, to set the language to one with the right date order:

// Set the Locale
OleObject configProvider, configUpdate
String newLocale
configProvider = iole_app.createInstance("com.sun.star.configuration.ConfigurationProvider")
props[1] = of_property("nodepath", "/org.openoffice.Setup/L10N")
configUpdate = configProvider.createInstanceWithArguments("com.sun.star.configuration.ConfigurationUpdateAccess", props)

// Determine the input date format, which tells us what order the three components of a date are in.
// We have to export in a format matching that, or the import of the CSV won't work
String lsIDate
if RegistryGet("HKEY_CURRENT_USER\Control Panel\International", "iDate", REF lsIDate) <> 1 then
    gMsg.Show("Cannot determine your Windows input date format.")
    DESTROY iole_app
    return FALSE
end if
CHOOSE CASE lsIDate
    CASE "0" // month then day then year
        newLocale = "en-US" // gives outputs in format M/D/YYYY
    CASE "1" // day then month then year
        newLocale = "en-CA" // gives outputs in format DD-MM-YYYY
    CASE "2" // year then month then day
        newLocale = "zh-CN" // China Chinese! Gives outputs in format YYYY-M-D
    CASE ELSE
        gMsg.Show("Unexpected Windows input date format ~"" + lsIDate + "~".")
        DESTROY iole_app
        return FALSE
END CHOOSE
configUpdate.setPropertyValue("ooSetupSystemLocale", newLocale)
configUpdate.commitChanges()

Then after the save is finished we set it back to US English (because otherwise it stays set in OpenOffice, if anyone runs it directly!) with:

if newLocale <> "en-US" then // don't leave it changed!
    configUpdate.setPropertyValue("ooSetupSystemLocale", "en-US")
    configUpdate.commitChanges()
end if

I think that covers everything I added to get it all to work.

Comment
Armeen Mazda @Appeon
  1. Armeen Mazda @Appeon
  2. Tuesday, 20 October 2020 15:06 PM UTC
Thanks for sharing the solution!
  1. Helpful
Daniel Vivier
  1. Daniel Vivier
  2. Tuesday, 20 October 2020 21:25 PM UTC
I spoke too soon about using 76 as the 3rd element of the FilterOptions to get it to export as UTF8. Unfortunately, that creates a UTF8 file without the required 3-character BOM (byte order mark). When PowerBuilder reads that, it thinks it's an ANSI file because of the absence of the BOM, and reads it incorrectly.



If your file doesn't have accented characters, just go back to 0 as the 3rd element of the FilterOptions. Otherwise, you will have to fix the file somehow to have that BOM. (That turned out to be really difficult in PowerBuilder - I tried several things, then gave up and used a 3rd-party control I had to do that fix.)
  1. Helpful
There are no comments made yet.
René Ullrich
René Ullrich Accepted Answer Pending Moderation
  1. Tuesday, 20 October 2020 06:27 AM UTC
  2. PowerBuilder
  3. # 2

Hi Dan,

have you found this?

https://forum.openoffice.org/en/forum/viewtopic.php?f=20&t=79147

 

 

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.