[Solved] Call OOo API using MS Com Automation interface

by **dnelson** » Fri Oct 10, 2008 5:39 am

I have a simple COBOL program (below) that calls Microsoft excel to create an excel spreadsheet with the word "test" in the first cell. I've read the automation bridge documentation and I am still confused about how to convert this program to use the open office calc api. Any help would be very much appreciated.

$set sourceformat(variable)
$set ooctrl(+P)
IDENTIFICATION DIVISION.
ENVIRONMENT DIVISION.

class-control.
MSExcel is class "$OLE$Excel.Application".

DATA DIVISION.
WORKING-STORAGE SECTION.

01 ExcelObject object reference.
01 WorkBooksCollection object reference.
01 WorkBook object reference.
01 Cell object reference.

PROCEDURE DIVISION.

*> Create a new instance of Microsoft Excel
invoke MSExcel "new" returning ExcelObject

*> Make Excel visible
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.

edit this topic's initial post · by **Villeroy** » Fri Oct 10, 2008 9:09 am

Programming OpenOffice.org with Visual Basic
Without any office installed you could experiment with the free ODF standard. Just get a skeleton of xml files from any spreadsheet, modify content.xml and put it in a zip with suffix .ods.

by **dnelson** » Fri Oct 10, 2008 3:24 pm

I'm sorry Villeroy. I want to be able to maintain the same programming style with as few alterations as possible. I don't see how your suggestion enables me to do quick conversions. 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.

edit this topic's initial post · by **Villeroy** » Fri Oct 10, 2008 3:39 pm

~~You've got to stay with Excel.~~

SORRY! You may try the "VBA compatibility API" as described in http://wiki.services.openoffice.org/wik ... _languages
For ranges on sheets with values, formulas and some basic formatting it may fit your needs. Don't expect too much, however.

[sent PM to dnelson]

by **ms777** » Sun Oct 19, 2008 1:12 pm

Hi,

I believe the original question was more targeting at how to invoke OO generally through ´the COM interface. Please see http://www.oooforum.org/forum/viewtopic ... 560#299560 as a Visual Basic Example.

I speculate that the following replacements may work:

MSExcel is class "$OLE$Excel.Application"
->
OServManager is class "$OLE$com.sun.star.ServiceManager"

invoke MSExcel "new" returning ExcelObject
->
invoke OServManager "createInstance" returning StarDesktop
invoke StarDesktop "loadComponentFromURL" using by value "private:factory/scalc" using by value "_blank" using by value 0 using (no idea how to generate empty array in COBOL) returning CalcDoc

Good luck,

ms777

by **dnelson** » Sun Oct 19, 2008 1:24 pm

Thanks a lot! It may be a day or two before I have an opportunity to try that, but I'll let you know.

by **dnelson** » Mon Oct 20, 2008 10:32 pm

Thanks for your help/suggestions. I've tried dozens of variations of what you have suggested and have looked at the link (among many others). Basically, I can only get the first line of code to work:

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

invoke StarDesktop "loadComponentFromURL" using by reference "private:factory/scalc"
invoke StarDesktop "loadComponentFromURL" using by reference "C:\temp\test.ods"
by reference "_blank"
by value 0
by value 0
returning CalcDoc

Exception 65540 not trapped by the class oleexceptionmanager.
Description: "OLE Name not found"
(80020006): Unknown name.

I'm about out of options (or persistence). It would appear that Microsoft wins again.

edit this topic's initial post · by **Villeroy** » Mon Oct 20, 2008 10:51 pm

invoke StarDesktop "loadComponentFromURL" using by reference "C:\temp\test.ods"

Use an URL like a browser: "file:///C:/temp/test.ods"

by **dnelson** » Mon Oct 20, 2008 11:00 pm

I tried that and several variations to no avail. It appears from the error message (OLE Name not found) that it is not even getting far enough to consider the file name.

edit this topic's initial post · by **Villeroy** » Tue Oct 21, 2008 1:31 am

OK, what are you trying to do? For simple spreadsheets you may consider to generate them from scratch. ODF is a free file format that wraps xml into zip-archives. You can generate valid office files without having any office software installed. Create a template with all the required formatting but no content. Fill in some example records and watch content.xml.
If your source data happen to be xml, you can use xslt. Recent thread with simple import filter: [Solved] Is there a way to open XML file in Calc?

by **dnelson** » Tue Oct 21, 2008 4:07 am

Alright, since you asked, here's the long of it. We have been writing reports for several years that are quite extensive. These reports are produced by Micro Focus Net Express COBOL programs that read SQL Server database tables and build data into Microsoft Excel spreadsheets. The programs use a lot of different methods to build these spreadsheets. Some of the reports are quite involved, some are simple. Some are created on the fly as users pick and choose the columns they want. There are totals, different colors assigned, some rows and columns are hidden, some columns are plain text, others are numbers with formatting (two decimal places), some are numbers without formatting, and dates, some are bold, etc (you get the picture). I have no complaints about the ease with which these reports can be produced using the COM interface for Excel. In a sense, we just use it right out of the box and it works. Here are some examples of the types of statements that are being used in these programs:

invoke MSExcel "new" returning ExcelObject
invoke ExcelObject "setVisible" using by value 1
invoke ExcelObject "getWorkBooks" returning WorkBooksCollection
invoke WorkBooksCollection "Add" returning WorkBook
invoke ExcelObject "getWorkSheets" returning WorkSheetsCollection
invoke WorkSheetsCollection "Add" returning WorkSheet
invoke WorkSheet "setName" using z"Company 01"
invoke WorkSheetsCollection "getItem" using by value 1 returning WorkSheet
invoke WorkSheet "activate"
invoke ExcelObject "getColumns" using z"A:M"
invoke getColumns "Select"
invoke getColumns "setNumberFormat" using by reference "@"
invoke getColumns "setNumberFormat" using by reference "0"
invoke getColumns "setNumberFormat" using by reference "$0.00"
invoke getColumns "finalize" returning getColumns
invoke getColumns "AutoFit"
invoke getColumns "getEntireColumn" returning getEntireColumn
invoke getEntireColumn "setHidden" using by value 1
invoke getColumns "finalize" returning getColumns
invoke ExcelObject "getCells" using by value 1 by value 1 returning Cell
invoke Cell "setValue" using by reference WORK-FIELD
invoke Cell "finalize" returning Cell
invoke ExcelObject "getRange" using z"A1:U2"returning CellRange
invoke CellRange "Select"
invoke CellRange "getFont" returning Font
invoke Font "setName" using z"Comic Sans MS"
invoke Font "setSize" using by value 36 size 4
invoke font "setBold" using by value 1 size 4
invoke font "setColorIndex" using by value 45 *> orange
invoke CellRange "finalize" returning CellRange
invoke ExcelObject "getRows" using WORK-FIELD returning getRows
invoke getEntireRow "setHidden" using by value 1
invoke getRows "finalize" returning getRows
invoke WorkBook "SaveAs" using by reference filename by value FileFormat by reference z"password"
invoke WorkBook "Close" using by value 0
invoke WorkBook "finalize" returning WorkBook
invoke WorkBooksCollection "finalize" returning WorkBooksCollection
invoke ExcelObject "Quit"
invoke ExcelObject "Finalize" returning ExcelObject

Now, maybe I should have just presented all of this to begin with. My hope was that if I could just convert some of the simplest of our programs to use OO Calc instead of MS Excel, then I would be on my way. I don't even know if all of the calls that are available in Excel are available in Calc. Maybe this is futile. I was hoping some OO experts would either tell me that this can be done because OO is so good, or that there is no way, so just give up trying. All of the OO documentation seems to suggest that a lot of effort has been poured into OO to make it a viable alternative for MS Office. I don't particularly look forward to converting all of these applications from MS Office to OO, but the pressure from management to save money is great. We need to know if OO is really viable. When you are talking about dozens and potentially hundreds of employees being able to live without MS Office, then obviously that is a lot of money. I figured if I could get it to work for our Excel applications then I could also convert our MS Word applications to OO Writer.

I'm not really intending to become a C, Java or VB programmer. COBOL has worked quite well, but integrating it with a lot of "free stuff" is new, although we have incorporated quite a bit of Perl into it.

So, am I barking up the wrong tree? OO 2.4 is my first experience with OO and I if we can't get any of this stuff to work there is no sense in even looking at OO 3.0. So, to answer your question more directly, these are not simple spreadsheets. These are robust reporting and letter generation mechanisms. I've downloaded and studied the Open Office.org 2.3 Developer's Guide (1250 pages!) and I still have a lot of confusion.

edit this topic's initial post · by **Villeroy** » Tue Oct 21, 2008 9:55 am

You don't need to write programs to create a new document and format by means of hard-coded property values. Just load a prepared template.

...programs that read SQL Server database tables and build data into Microsoft Excel spreadsheets ...

Where is that part? You don't need a program neither. Calc (and Excel as well) provide tools to bind cell ranges to database recordsets.

by **dnelson** » Tue Oct 21, 2008 4:02 pm

Forgive me if I sound short. What part of "Some are created on the fly as users pick and choose the columns they want." do you not understand? Most of these reports are customized by the users who choose options from a screen that serves as an interface. Your statement, "Just load a prepared template" tells me that you are trying to over-simplify this. The beauty and flexibility of what has already been created lies in the fact that it is not tied to prepared templates. Again, my original intent was to see if we can do using OO what was easily done using MS Office. It sounds like the answer is no - not that it can't be done, but that it can't be done easily. This is a classic case of spending more money in time and resources trying to get free stuff to work than would be saved if we just used the purchased software.

edit this topic's initial post · by **Villeroy** » Tue Oct 21, 2008 5:15 pm

10/Oct: Subject: How to call OO API using Microsoft Com Automation interface
Answered with a link Programming OpenOffice.org with Visual Basic

Then you revealed that you do not want to access the OOo Api. You want to access the Excel API in OOo.

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.

First I thought this would be impossible, then I found the Excel-compatibility-API which may even do that trick to some extend.

Then you obviously decided to use the OOo-API anyway but don't get the translation from the VB examples to your preferred language.

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

At this point you are completely on your own, unless you find somebody who managed to access this API using Cobol via COM.

Some are created on the fly as users pick and choose the columns they want.

Admittedly, I missed that part.

It looks as if you really have to stay with Excel -- forever, since I would not hold my breath waiting for an Excel clone before 2020 (seminar on historic APIs and data archeology).

by **dnelson** » Tue Oct 21, 2008 6:16 pm

Sorry for the confusion. Apparently I haven't stated my intentions very well. In all seriousness, I thought that Calc was supposed to be an Excel clone. Am I mistaken about that? 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. I still suspect that it will work (using COBOL), but that you are correct in saying that I am on my own. And yes, you are correct in that I "don't get the translation from the VB examples to your preferred language". I find it interesting though that most of the development I did using COBOL to access the MS COM API was done by translating VB examples (both Excel and Word). I do appreciate your efforts in trying to assist me.

edit this topic's initial post · by **Villeroy** » Tue Oct 21, 2008 11:21 pm

This office suite is an application on it's own right since 1985. http://en.wikipedia.org/wiki/StarOffice
It offers to load other programs documents more or less well. I believe it is the second best option for Microsoft documents but it can't be perfect since it has been reverse engineered, many features are implemented differently, other features are missing in this office and a handful of features are missing in the other office.

It is advertised as being 100% Office Suite Compatible.

Where? I hope that this kind of hype is not spread from the openoffice.org domain nor any of the main contributors.

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.

The same type of control over a different product.

by **dnelson** » Sat Jan 24, 2009 4:49 am

It's been a while since I posted here, but I have continued to work on this. I contacted Micro Focus, but didn't receive any help from them. They are biased towards Microsoft.

Here is a link that explains how it was done in FoxPro

http://www.tedroche.com/Present/VFPOOoAutomation.htm

I don't know FoxPro and can't seem to convert it. It basically says that in FoxPro, an array cannot be empty and I think the same is true in COBOL. I cannot figure out how to simulate/create an empty array in COBOL. I am also trying to create the array with one or more element, but can't figure that out either.

I can get a VB script example to work with an array with zero (empty) or more elements as it is easy to create an empty array in VB script.

I am uploading my test program (calctst4) in which I have experimented with all sorts of things to get the variantToAny error resolved. It may be of use to someone who looks at this problem. I realize this is a long shot, but maybe someone who has experimented with these types of things in COBOL can lend a hand.

No matter how I tried to upload the program as an attachment, I kept getting the error "The extension is not allowed.". I tried with an extension of .cbl, .txt, and no extension. So here is the program code. I realize not all of the code is necessary for the solution, but as I said, I was experimenting with a lot of things.

Code: Select all Expand view Collapse view: $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 .

by **Ferda** » Sun Mar 22, 2009 5:43 pm

I have been a COBOL (NetExpress) programmer for a long time.
The example you have given was most useful and many thanks for it.
However I could not find any code example of changing the background color of an excel cell.
Your help in this respect will be most welcome.
Thank you in advance.

by **dnelson** » Mon Mar 23, 2009 8:38 pm

Here is the code to set the background color of an excel cell.

01 Interior object reference.

invoke Cell "getInterior" returning Interior
invoke Interior "setColorIndex" using by value 41 *> blue

edit this topic's initial post · by **Villeroy** » Tue Mar 24, 2009 12:52 am

dnelson wrote:Here is the code to set the background color of an excel cell.

Wrong forum. OOo Calc is not Excel. Calc is just a part of a platform independent application with a completely different API.

Normally you don't need any code to apply a formatting attribute or combinations of attributes. Function STYLE and conditional formatting can do this reliably and fast without writing any additional code. Of course this requires that you know how to work with the application, utilizing templates and styles rather than hacking on blank documents.

All the questions in this thread have nothing to do with Excel, Calc or any particular programming language. You can access the very same API from any language in the same way if you know how to access properties methods in your particular language. There is plenty of documentation, example code and introspection tools. My favourite inspector is http://extensions.services.openoffice.org/project/MRI
Install it, restart the office, select some cell(s) and call menu:Tools>Add-Ons> "MRI <--selection".
A cell, a range and even a multiple selection of ranges, in Calc as well as in Writer tables, support service com.sun.star.table.CellProperties which provides property CellBackColor: http://api.openoffice.org/docs/common/r ... lBackColor

by **OlivierS** » Fri Jun 12, 2009 11:03 am

here is a small program for cobol microfocus netexpress with a successful call to openoffice write

$set ooctrl(+P)
IDENTIFICATION DIVISION.
ENVIRONMENT DIVISION.

class-control.
OServManager is class "$OLE$com.sun.star.ServiceManager"
oleSafeArray is class "olesafea" OLE SafeArray class
.

DATA DIVISION.
WORKING-STORAGE SECTION.

copy mfole.cpy.

*---SafeArray dimension structure SafeArrayBound
* celements is the total number of elements
* lBound is the lower boundary (usually 0)
01 tagSafeArrayBound is typedef.
05 celements usage ULONG.
05 llbound usage LONG.
01 SafeArrayBound is typedef usage tagSafeArrayBound.
01 saBound SafeArrayBound.

01 ServManager object reference.
01 StarDesktop object reference.
01 aDoc object reference.
01 atext object reference.

01 Group1SafeArray object reference.

01 theVarType VARTYPE.

01 cDims UINT.

*-----------------------------------------------------------------
PROCEDURE DIVISION.

Start-Up. # Start-Up
*--- create the document
perform CreateDocument-Stup.

*--- fill the document with some text
perform FillDocument-Stup.

exit program.
stop run.

*-----------------------------------------------------------------
CreateDocument-Stup. # CreateDocument-Stup
*---
invoke OServManager "new" returning ServManager

*---
invoke ServManager "createInstance"
using "com.sun.star.frame.Desktop"
returning StarDesktop.

*--- olesafearray
move VT-VARIANT to thevarType.
move 1 to cDims.
move 0 to cElements OF saBound.
move 0 to llBound OF saBound.
invoke olesafearray "new" using by value thevarType
by value cDims
by reference saBound
returning Group1SafeArray.

*--- loadComponentFromURL
invoke StarDesktop "loadComponentFromURL"
using "private:factory/swriter" change 'swriter' with 'scalc' to call openOffice Calc
"_blank"
"0"
Group1SafeArray
returning aDoc.

*-----------------------------------------------------------------
FillDocument-Stup. # FillDocument-Stup
invoke aDoc "GetText" returning aText. remove this line if you call 'Calc'
invoke aText "SetString" using "Hello World". remove this line if you call 'Calc'

*-----------------------------------------------------------------

by **dnelson** » Fri Jun 12, 2009 11:27 pm

Many thanks to Olivier Stainier of Ollysoft who finally cracked this code! Eight months later I finally have the solution! I am absolutely astounded at how close the solution was and yet how much it continued to escape me. In comparing Olivier's sample code (that I am so grateful for) to the sample program (calctst4) that I had posted on the forum on Jan 24, 2009, I was able to determine that the only thing I needed to do to get my sample program to work was to put the zero (the third parameter in the call to loadComponentFromURL) in double quotes as "0". That's it! I had spent countless hours trying to decipher the error message returned by OpenOffice.

invoke StarDesktop "loadComponentFromURL" using "private:factory/swriter" "_blank" "0" theSafeArrayObj returning aDoc *> This works!
invoke StarDesktop "loadComponentFromURL" using "private:factory/swriter" "_blank" 0 theSafeArrayObj returning aDoc *> This does not work and returns the following error:

Exception 65537 not trapped by the class oleexceptionmanager.
Description: "Server defined OLE exception"
(80020009): InterfaceOleWrapper_Impl::Invoke :
[automation bridge]UnoConversionUtilities<T>::variantToAny
Cannot convert the value of vartype :"8" to the expected UNO type of type class: 6

I had always assumed that the error message was somehow objecting to the way I had my safe array defined (the fourth parameter in the call to loadComponentFromURL), but my safe array was just fine. I literally just had to put the double quotes around the zero and everything worked.

Olivier - how did you ever discover the need for the double quotes? Every example that I had ever looked at did not have the zero enclosed in double quotes. There are examples in C++, VB, VB script, FoxPro and other languages and none of them have double quotes around the zero (including all of the examples provided in the OpenOffice Developer's Guide (1250 pages)). Notice the following example from the Developer's Guide (page 165):

'Open a new empty writer document
Dim args()
Set objDocument= objDesktop.loadComponentFromURL("private:factory/swriter", "_blank", 0, args)

I was also able to get short programs to work with both writer and calc. As I get time, I will continue my efforts to convert some of our more complex programs from Microsoft Office (Word and Excel) to OpenOffice.

by **dnelson** » Mon Jun 15, 2009 6:55 pm

I opened an incident with the vendor (Micro Focus) and here is their response:

In your VB example:
Set objDocument= objDesktop.loadComponentFromURL("private:factory/swriter", "_blank", 0, args)

The third parameter 0 will be treated as an integer and will be passed on the stack as “value type”.

In Net Express COBOL:
invoke StarDesktop "loadComponentFromURL" using "private:factory/swriter" "_blank" 0 theSafeArrayObj returning aDoc

The third parameter 0 will be treated as a 32-bit integer, but it will be passed by reference as this is the COBOL default if nothing is specified.

So there is a problem with the run-time system in converting an integer passed by reference to an integer passed by value. When the quotes are added to make “0” the parameter is no longer an integer, but it is instead a one byte string passed by reference. The runtime seems to handle this conversion OK.

The correct syntax should actually be:
invoke StarDesktop "loadComponentFromURL"
using by reference "private:factory/swriter"
by reference "_blank"
By value 0
By reference theSafeArrayObj
returning aDoc

by **GregMcC** » Fri Nov 27, 2009 3:37 pm

Great thread - This sounds 'Fixed' - if you ( DNelson ) thinks it is sorted / resolved then could you change the status on the header of this thread ...

Thanks Greg

by **dnelson** » Fri Nov 27, 2009 5:25 pm

I have continued to do further development in Net Express COBOL using the OpenOffice API and it seems to be stable and I'm finding it to be a lot more robust than I had originally anticipated.

by **ronaldo_dachi** » Tue Dec 06, 2016 3:35 pm

I have been a COBOL (NetExpress) programmer for a long time.
The example you have given was most useful and many thanks for it.
However I could not find any code example of changing the borders of an excel cell.
Your help in this respect will be most welcome.
Thank you in advance.

by **dnelson** » Tue Dec 06, 2016 4:53 pm

Your post is not very specific. Are you wanting to change the borders by making them bold or are you wanting to change the borders by combining cells, or something else?

Have you tried doing the tasks manually and recording a macro in Excel (assuming that what you're intending to do in Excel can also be done in Calc) so that you can look at the VB code? It usually is helpful to me to do things in Excel before doing them in Calc (again, assuming you have Excel).

by **ronaldo_dachi** » Tue Dec 06, 2016 5:02 pm

I would like to put borders in the cell

by **ronaldo_dachi** » Tue Dec 06, 2016 5:05 pm

I am using cobol net express 3.1

by **dnelson** » Tue Dec 06, 2016 5:08 pm

I haven't used Net Express 3.1 for more than 10 years so I'm not sure what all is available. I don't have much time to help you right now (unfortunately). We are using Net Express 5.1 and will be converting to Visual COBOL 2.3 soon. I know there are a lot of bugs in 3.1 that have been fixed - some specific to the COM automation interface.

[Solved] Call OOo API using MS Com Automation interface

[Solved] Call OOo API using MS Com Automation interface

Re: How to call OO API using Microsoft Com Automation interface

Re: How to call OO API using Microsoft Com Automation interface

Re: How to call OO API using Microsoft Com Automation interface

Re: How to call OO API using Microsoft Com Automation interface

Re: How to call OO API using Microsoft Com Automation interface

Re: How to call OO API using Microsoft Com Automation interface

Re: How to call OO API using Microsoft Com Automation interface

Re: How to call OO API using Microsoft Com Automation interface

Re: How to call OO API using Microsoft Com Automation interface

Re: How to call OO API using Microsoft Com Automation interface

Re: How to call OO API using Microsoft Com Automation interface

Re: How to call OO API using Microsoft Com Automation interface

Re: How to call OO API using Microsoft Com Automation interface

Re: How to call OO API using Microsoft Com Automation interface

Re: How to call OO API using Microsoft Com Automation interface

Re: How to call OO API using Microsoft Com Automation interface

Re: How to call OO API using Microsoft Com Automation interface

Re: How to call OO API using Microsoft Com Automation interface

Re: How to call OO API using Microsoft Com Automation interface

Re: How to call OO API using Microsoft Com Automation interface

Re: How to call OO API using Microsoft Com Automation interface

Re: How to call OO API using Microsoft Com Automation interface

Re: How to call OO API using Microsoft Com Automation interface

Re: How to call OO API using Microsoft Com Automation interface

Re: [Solved] Call OOo API using MS Com Automation interface

Re: [Solved] Call OOo API using MS Com Automation interface

Re: [Solved] Call OOo API using MS Com Automation interface

Re: [Solved] Call OOo API using MS Com Automation interface

Re: [Solved] Call OOo API using MS Com Automation interface

Who is online