invoke StarDesktop "loadComponentFromURL" using by reference "C:\temp\test.ods"
...programs that read SQL Server database tables and build data into Microsoft Excel spreadsheets ...
Ideally, I simply want to be able to replace the name of the Microsoft class and methods with the name of the Open Office class and methods where needed.
OServManager is class "$OLE$com.sun.star.ServiceManager"
invoke OServManager "new" returning StarDesktop
After that, nothing works. Any variation of the following always returns some kind of error (which follows).
Some are created on the fly as users pick and choose the columns they want.
It is advertised as being 100% Office Suite Compatible.
Even the developer's guide states that the API is supposed to offer the same type of control over the product that is comparable to the MS Office API.
$set sourceformat(variable)
$set ooctrl(+P)
IDENTIFICATION DIVISION.
ENVIRONMENT DIVISION.
class-control.
OServManager is class "$OLE$com.sun.star.ServiceManager"
oleSafeArray is class "olesafea" *> OLE SafeArray class
chararray is class "chararry" *> Character array class
CharacterArray is class "chararry"
olesup is class "olesup"
OLEVariant is class "olevar"
olebase is class "olebase"
.
DATA DIVISION.
WORKING-STORAGE SECTION.
copy mfole.cpy.
copy olesafea.cpy. *> SafeArray records
01 theSafeArrayObj object reference.
01 saBound SAFEARRAYBOUND occurs 1.
01 ExcelObject object reference.
01 WorkBooksCollection object reference.
01 WorkBook object reference.
01 Cell object reference.
01 ServManager object reference.
01 StarDesktop object reference.
01 CoreReflection object reference.
01 PropertyValue object reference.
01 ObjPropertyValue object reference.
01 aPropertyValue pic x(40).
01 CalcDoc object reference.
01 iIndex pic x(4) comp-5 occurs 1.
01 hIndex pic x(4) comp-5.
01 vIndex pic x(4) comp-5.
01 iValue pic x(4) comp-5.
01 iString pic x(10).
01 theCharArray object reference.
01 theStringLength pic x(4) comp-5.
01 theString pic x(40).
01 hResult pic x(4) comp-5.
01 theData POINTER.
01 aClass object reference.
01 dimensions pic x(4) comp-5.
01 dimensionSize pic x(4) comp-5.
*01 intSafeArray object reference.
01 lBound pic x(4) comp-5.
01 uBound pic x(4) comp-5.
*01 hResult pic x(4) comp-5.
01 var-Type pic x(4) comp-5.
01 OLEBOOL-false PIC s9(4) COMP-5 value 0.
01 OLEBOOL-true PIC s9(4) COMP-5 value -1.
01 vData2 VARIANT.
*01 v VARIANT.
01 theVariantObj object reference.
PROCEDURE DIVISION.
*Set objServiceManager= WScript.CreateObject("com.sun.star.ServiceManager")
display 'invoke OServManager "new" returning ServManager ' upon syserr
invoke OServManager "new" returning ServManager
display 'ServManager "' ServManager '"' upon syserr
display ' ' upon syserr
*Set objDesktop= objServiceManager.createInstance("com.sun.star.frame.Desktop")
display 'invoke ServManager "createInstance" using "com.sun.star.frame.Desktop" returning StarDesktop' upon syserr
invoke ServManager "createInstance" using "com.sun.star.frame.Desktop" returning StarDesktop
display 'StarDesktop "' StarDesktop '"' upon syserr
display ' ' upon syserr
*********************************** olesafearray
display 'invoke olesafearray "new"....' upon syserr
move 0 to llBound of saBound(1)
move 1 to cElements of saBound(1)
invoke olesafearray "new" using
by value VT-VARIANT *> VT-SAFEARRAY *>VT-DISPATCH *> VT-ARRAY *> VT-VARIANT *> Type:
by value 1 *> Single dimension
by reference saBound(1) *> Dimension boundaries
returning theSafeArrayObj
display 'theSafeArrayObj "' theSafeArrayObj '"' upon syserr
* stop "Press a key to continue... "
invoke olesup "getClass" using by value theSafeArrayObj returning aClass
perform get-class
* perform Getting-Info-About-a-SafeArray
display ' ' upon syserr
*********************************** CoreReflection
* The args array is an array of "PropertyValue" objects - create by invoking OO.o reflection
display 'invoke ServManager "createInstance" using "com.sun.star.reflection.CoreReflection" returning CoreReflection' upon syserr
invoke ServManager "createInstance" using "com.sun.star.reflection.CoreReflection" returning CoreReflection
display 'CoreReflection "' CoreReflection '"' upon syserr
display ' ' upon syserr
* Is it really necessary to create the empty propertyvalue object like the foxpro example does and like oo seems to suggest?
* The vbscript example doesn't need the Dim oPropertyValue.
* loPropertyValue = CREATEOBJECT("Empty")
*// create empty array of PropertyValue structs, needed for loadComponentFromURL
*PropertyValue[] loadProps = new PropertyValue[0];
* loCoreReflection.forName("com.sun.star.beans.PropertyValue").createobject(@loPropertyValue)
display 'invoke CoreReflection "forName" using "com.sun.star.beans.PropertyValue" returning PropertyValue' upon syserr
invoke CoreReflection "forName" using "com.sun.star.beans.PropertyValue" returning PropertyValue
display 'PropertyValue "' PropertyValue '"' upon syserr
invoke olesup "getClass" using by value PropertyValue returning aClass
perform get-class
display ' ' upon syserr
display 'invoke PropertyValue "createObject" using "Empty" null returning ObjPropertyValue' upon syserr
invoke PropertyValue "createObject" using "Empty" -1 returning ObjPropertyValue
* move 0 to v
* move -1 to VARIANT-VT-BOOL of v
* move VT-BOOL to VARIANT-vartype of v
** display 'invoke oleVariant "new" returning theVariantObj' upon syserr
** invoke oleVariant "new" returning theVariantObj
* display 'invoke oleVariant "newwithData" using v returning theVariantObj' upon syserr
* invoke oleVariant "newwithData" using v returning theVariantObj
* display 'invoke PropertyValue "createObject" using "ReadOnly" theVariantObj returning ObjPropertyValue' upon syserr
* invoke PropertyValue "createObject" using "ReadOnly" theVariantObj returning ObjPropertyValue
move 0 to vData2
move 0 to VARIANT-VT-I2 of vData2
move VARIANT-VT-I2 to variant-vartype of vData2
display 'invoke PropertyValue "createObject" using "ReadOnly" OLETRUE returning ObjPropertyValue' upon syserr
invoke PropertyValue "createObject" using "ReadOnly" z"0" returning ObjPropertyValue
display 'ObjPropertyValue "' ObjPropertyValue '"' upon syserr
display ' ' upon syserr
* display 'invoke PropertyValue "setName" using "ReadOnly"' upon syserr
* invoke ObjPropertyValue "setPropertyValues" using "ReadOnly"
* display 'invoke PropertyValue "setValue" using 0' upon syserr
* invoke ObjPropertyValue "setValue" using 0 *> True = 1, False = 0
*********************************** _GetStruct
display 'invoke ServManager "_GetStruct" using "com.sun.star.beans.PropertyValue" returning PropertyValue' upon syserr
invoke ServManager "_GetStruct" using "com.sun.star.beans.PropertyValue" returning PropertyValue
display 'PropertyValue "' PropertyValue '"' upon syserr
invoke olesup "getClass" using by value PropertyValue returning aClass
perform get-class
display ' ' upon syserr
* can't call Getting-Info-About-a-SafeArray because it's not a safearray, its a COM object
* perform Getting-Info-About-a-SafeArray
display 'invoke PropertyValue "setName" using "ReadOnly"' upon syserr
invoke PropertyValue "setName" using "ReadOnly"
display 'invoke PropertyValue "setValue" using 0' upon syserr
invoke PropertyValue "setValue" using 0 *> True = 1, False = 0
set theData to address of PropertyValue *> "putElement" reads data from an address pointer
move 0 to iIndex(1)
display 'invoke theSafeArrayObj "putOLEObject" ' upon syserr
invoke theSafeArrayObj "putOLEObject" *> "putOLEObjectAsVariant" "putOLEObject"
using iIndex(1)
by value PropertyValue *>theData PropertyValue
returning hResult
display 'hResult "' hResult '"' upon syserr
*********************************** loadComponentFromURL
display 'invoke StarDesktop "loadComponentFromURL"' upon syserr
* invoke StarDesktop "loadComponentFromURL" using by reference "C:\temp\test.ods"
* invoke StarDesktop "loadComponentFromURL" using by reference "file:///C:/temp/test.ods"
invoke StarDesktop "loadComponentFromURL" using "private:factory/scalc"
"_blank"
0
theSafeArrayObj *> Array
* PropertyValue *> Array
* ObjPropertyValue *> Array
returning CalcDoc
display 'CalcDoc "' CalcDoc '"' upon syserr
***********************************
*> I haven't tried to convert the rest yet.
*> Make Excel visible
display 'invoke ExcelObject "setVisible" using by value 1 ' upon syserr
invoke ExcelObject "setVisible" using by value 1
*> Get the collection of WorkBooks
invoke ExcelObject "getWorkBooks" returning WorkBooksCollection
*> Add a new WorkBook to the collection
invoke WorkBooksCollection "Add" returning WorkBook
invoke ExcelObject "getCells" using by value 1 by value 1 returning Cell
invoke Cell "setValue" using by reference z"test"
invoke Cell "finalize" returning Cell
invoke WorkBook "SaveAs" using by reference z"C:\TEMP\EXCLTEST.XLS"
*> Close the WorkBook.
invoke WorkBook "Close" using by value 0
*> Finalize all objects
invoke WorkBook "finalize" returning WorkBook
invoke WorkBooksCollection "finalize" returning WorkBooksCollection
*> Exit Excel
invoke ExcelObject "Quit"
invoke ExcelObject "Finalize" returning ExcelObject
GOBACK.
get-class.
if aClass = CharacterArray
display "a CharacterArray" upon syserr
else
if aClass = olebase
display "A COM object" upon syserr
else
if aClass = oleSafeArray
display "a SafeArray" upon syserr
else
if aClass = OLEVariant
display "a Variant" upon syserr
else
display "Not supported by COM Automation" upon syserr
end-if
end-if
end-if
end-if.
Getting-Info-About-a-SafeArray.
*>---Get the number of dimensions of the array
display 'invoke theSafeArrayObj "getDim" returning dimensions' upon syserr
invoke theSafeArrayObj "getDim" returning dimensions
move 1 to dimensions
*>---hResult is the Windows status code returned when the
*> SafeArray is queried. Zero indicates success, non-zero
*> indicates failure. Error codes are defined in copyfile
*> MFOLE.CPY, as level-78 data items.
display 'invoke theSafeArrayObj "getLBound"' upon syserr
invoke theSafeArrayObj "getLBound"
using by value dimensions
by reference lBound
returning hResult
display 'invoke theSafeArrayObj "getUBound"' upon syserr
invoke theSafeArrayObj "getUBound"
using by value dimensions
by reference uBound
returning hResult
*>---Calculate the dimension size
subtract lBound from uBound
add 1 to uBound giving dimensionSize
*>---Find out the type of data in the array
display 'invoke theSafeArrayObj "getVarType" returning var-Type' upon syserr
invoke theSafeArrayObj "getVarType" returning var-Type
display "dimensions " dimensions upon syserr
display "dimensionSize" dimensionSize upon syserr
display "lBound " lBound upon syserr
display "uBound " uBound upon syserr
display "hResult " hResult upon syserr
display "var-Type " var-Type upon syserr
.
dnelson wrote:Here is the code to set the background color of an excel cell.
Users browsing this forum: No registered users and 2 guests