Problems with controlling OpenOffice via OLE

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.

Daniel Vivier Accepted Answer Pending Moderation

Tuesday, 20 October 2020 14:32 PM UTC
PowerBuilder
# 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
Tuesday, 20 October 2020 15:06 PM UTC

Thanks for sharing the solution!

Helpful 0

Daniel Vivier
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.)

Helpful 0

There are no comments made yet.

René Ullrich Accepted Answer Pending Moderation

Tuesday, 20 October 2020 06:27 AM UTC
PowerBuilder
# 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.

Resolved Problems with controlling OpenOffice via OLE