OpenOffice.org Calc Library

This is a collection of functions and procedures that provide an API to the OpenOffice.org Calc spreadsheet application. These were developed in Progress 9.1D using OpenOffice.org version 1.1.2 and have not yet been verified to work with version 2+.

This collection of functions and procedures will allow you to programmatically create Calc spreadsheets, add data, format columns, rows and cells and more!



AttachmentSize
calcUtils.i.txt33.94 KB

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Error while open existing document?

Hi,
I'm using Progress 10.1B and LibreOffice 4. I get sheet error while I try to open existing file ant write cell?

Regards,
Mucip:)


This include is not working!

Hi,
Did anybody test this include progress 10.1b and Ooo 3.2.0? I don't know why but I didn't get roeking result. I tryed below code.

{calcutils.i}

DEF VAR Sutun AS INTEGER.
DEF VAR Satir AS INTEGER.
DEF VAR tur AS INTEGER.

Sutun = 10.
Satir = 10.

RUN OPEN_Calc.

RUN NEW_book.

DO tur = 1 TO 10:
RUN write_cell_data (Sutun, Tur, "Mucip").
END.

But Only I get "Mucip" string in A1 cell...

What is the proble with ths include?. By the way "RUN open_book("c:\temp\kdv.ods")." is not working either!...

Regards,
Mucip:)


manuelml's picture

Works in Progress 10.2B and OOO 3.2.1

Hi,
I've tested this Include on Progress 10.2B and OOO 3.2.1 and it works fine!

I needed to change the Assign in save_book procedure to make it work from:

ASSIGN
chrFileName = "file:///" + ip_OutputPath + ip_FileName + ".sxc"
chrFileName = chrFileName + "\"
chrFileName = REPLACE(chrFileName, "\\", "\")
chrFileName = REPLACE(chrFileName, "/\", "\")
chrFileName = REPLACE(chrFileName, "\", "/").

To:
ASSIGN
chrFileName = "file:///" + ip_OutputPath + ip_FileName + ".odt"
chrFileName = REPLACE(chrFileName, "\\", "\")
chrFileName = REPLACE(chrFileName, "/\", "\")
chrFileName = REPLACE(chrFileName, "\", "/").

I also added the following procedures:

/*******************************************************************/
PROCEDURE new_sheet:
/* Purpose: Create new sheet in the workbook with the the name passed. */
/*******************************************************************/
DEF INPUT PARAM ip_Label AS CHAR NO-UNDO.
DEF INPUT PARAM ip_Last AS LOG NO-UNDO. /* TRUE: We add the sheet at the end; FALSE: We add the sheet at the begining */

DEF VAR lastSheet AS INT.

IF ip_Last THEN lastSheet = chWorkBook:Sheets:getCount().
ELSE lastSheet = 0.

chWorkBook:Sheets:insertNewByName(ip_Label,lastSheet).
chWorkSheet = chWorkBook:Sheets:GetByName(ip_Label).
END PROCEDURE. /* new_sheet */

/*******************************************************************/
PROCEDURE clear_book:
/* Purpose: Delete all sheets of the workbook and create new one with the name passed */
/*******************************************************************/
DEF INPUT PARAMETER ip_sheetName AS CHAR. /* Name for the first sheet */

RUN new_sheet("Main",TRUE).

chWorkSheet = chWorkBook:Sheets:GetByIndex(0).

DO WHILE (chWorkBook:Sheets:getCount() > 1):
chWorkBook:Sheets:removeByName(chWorkSheet:getName() ).
END.
END PROCEDURE.

The code I've used to test all this is:

RUN open_calc.
RUN new_book.
RUN clear_book("Main").
RUN new_sheet("Other1", TRUE).
RUN write_cell_data (2, 1, "Content1").
RUN new_sheet("Other2",TRUE).
RUN write_cell_data (2, 1, "Content2").
RUN save_book ("c:\TEST_OOO\", "file1").
PAUSE 2.
RUN close_calc.
RUN CleanUp.

It creates a book with 3 sheets (Main, Other1 and Other2) and fill one cell in Other1 and other cell in Other2. Also save the book to c:\TEST_OOO\file1.odt


How to disable printer function in Calc

Hi:

How to disable printer function in Calc from Progress 4GL.

Thanks


alonb's picture

I'd write it in Python

I'd write it in Python (OpenOffice comes with a Python installation).

This way it can also work on UNIX/Linux.


progress

Can you call python from progress? Not just os-command, but access to an API and that can be exposed to openedge.


alonb's picture

I'd launch both OpenOffice

I'd launch both OpenOffice and Python once and communicate using TCP/IP.

It's very common to launch OpenOffice and communicate using UNO over TCP/IP.

For example: soffice -accept="socket,host=localhost,port=8100;urp;" -norestore -nofirststartwizard -nodefault -nologo -headless

(You might need to re-launch OpenOffice if it crashes).

Anyway it's what we did with libxlsx to convert docs.

Tried it on RedHat linux and Sun Solaris, and of course Windows. Works like a charm.


UNO vs COM

I take it that it isn't possible to capture OpenOffice events like we can with Microsoft Word? For example, in my current code I have:
ch-wordApplication:enable-events("WordEvents").
and then later an internal procedure WordEvents.quit.

This enables me to not only open a Word session for a user, but to determine when the user has finished, allowing me to print the document or store it in the DB.

We're now trying to switch to OpenOffice Writer, but I can't seem to get this type of functionality.

Any solutions/ideas?


sollution

Hello,

did you find a solution to this problem? I've got the same issue.

Thanks,

Jan


UNO vs COM

Hi Jan,

We did, after enormous effort get a solution. We sent it to Progress and they put it in the Knowledgebase:
ID: P151868
Title: "4GL/ABL: How to trap OpenOffice Word Document events?"

Since OpenOffice does not present events in the usual manner we cannot use the Enable-Events statement to trap events in it.
The easiest way to trap OpenOffice events is to write a DLL that receives events from the OpenOffice object and pass then back to Progress to use the Enable-Events statement.  The following sample dlls are written in VB6 and contain the servers for the eventlisteners, only the disposing event is trapped at present but the VB code is easily expanded.
here is VB6 Source code:
Option Explicit
'create a Public Event Matching the OOoevent sub below
'so it gets passed back to calling program
'To fire this event, use RaiseEvent with the following syntax:
'RaiseEvent xxxxxx[(arg1, arg2, ... , argn)] in the
'corresponding Sub below
'Event can be trapped in calling prog by
'objEventListener:enable-events("OOoEvents").
'and creating int proc called OOoevents.xxxxx
Public Event disposing(source)
Public Event terminate(source)
Public Event notifyevent(eventname, source)
 Private interfaces(0) As String
 Public Property Get Bridge_ImplementedInterfaces() As Variant
     Bridge_ImplementedInterfaces = interfaces
 End Property

 Private Sub Class_Initialize()
     'interfaces(0) = "com.sun.star.lang.XEventListener" 'service level
     interfaces(0) = "com.sun.star.document.XEventListener" 'document level
 End Sub

 'create a sub for each OOoevent to be trapped
 Public Sub disposing(ByVal source As Object)
     RaiseEvent disposing(source)
 End Sub
'these two dont appear to work odd!
 Public Sub terminate(ByVal source As Object)
     RaiseEvent terminate(source)
 End Sub
'if this worked would solve all problems
Public Sub notifyevent(ByVal eventname As String, ByVal source As Object)
     RaiseEvent notifyevent(eventname, source)
 End Sub
Change the interface(0) in class_initialize depending on what level you want to trap events at.  or use array for multilevels!?. The service exposed is given by the name in in the project properties.
OOoEvent.dll -- listens to OpenOffice service level events ( highest level)
OOoDocumentEvent.dll -- listens to OpenOffice document level events.
copy the dlls to windows/system32 and register them.
In Progress we could then write:
def var ch-office               as com-handle no-undo.
def var ch-document        as com-handle no-undo.
def var OOeventlistener   as com-handle no-undo.
Def var DocEventListener  as com-handle no-undo.
/* create the event listener on the OO System dont have to just
     if you want to trap events at service level ie whole of OO closing*/
  create "OOoevent.Listener" OOEventListener.
  OOEventListener:enable-events("OOoEvents").
 
/* after the creation of openoffice we attach it to the listener */
/* add our listener to office if we want to trap event */
   ch-office:AddEventListener(OOEventListener).
 /* create the event listener on the document */
  create "OOoDocumentEvent.Listener" DocEventListener.
  DocEventListener:enable-events("DocEvents").
/* and after openning the document we attach it to its listener */
ch-document = ch-application:loadComponentFromURL("private:factory/swriter",
                                                    "_blank",
                                                    0,
                                                    extraArgs).
/* add our listener to the document */ 
   ch-document:AddEventListener(DocEventListener). /* fires dispose first if both are used */ 
/*  dont forget to release the listener objects when you are finished
 probably in main block on close of this-procedure! */
release object OOEventListener no-error.
release object DocEventListener no-error.
/* The events can now be trapped and processed in the usual way .*/
PROCEDURE DocEvents.Disposing : /* traps Document level disposing */
def input-output param pv-source as com-handle no-undo.
/* pv-source is handle to object causing event to fire not a lot of use
   at present but never know maybe later! */
 
message Program-name(1) "Disposing From " string(pv-source)
view-as alert-box title 'Document Event'.
END PROCEDURE.
PROCEDURE OOoEvents.disposing : /* traps service (top) level disposing */
def input-output param pv-source as com-handle no-undo.
/* pv-source is handle to object causing event to fire not a lot of use
   at present but never know maybe later! */
message Program-name(1) "Disposing From " string(pv-source)
view-as alert-box title 'Service Event'.
END PROCEDURE.

The contractor who figured this out is Phil White (philw@metronet.co.uk) - you could contact him if you wanted someone to work out this part of your project.

Good luck!
Anne Disney
GUI Development Team
Great Valley Technologies


How to Working with Hidden Files in Progress 4GL

I want to use OpenOffice.org Calc working with Hidden Files in Progress 4GL. But I don't know how to hidden OpenOffice.org Calc file in run Progress 4GL program. Anybody can help me and have samples code.

Thanks


can't get the cell to center align

hi,

This project is great, but the align_cell procedure is not working for me. it produces an error "Error occurred while accessing component property/method: HoriJustify". Does anybody know how to fix this?

Thanks.


Stefan Drissen's picture

Sample code?

Do you have some sample code illustrating the error?
Versions of OpenOffice and Progress?
Platform?

See also the OpenOffice API project for some good reference material:

http://api.openoffice.org/docs/common/ref/com/sun/star/table/CellHoriJus...


Sample Code

Sorry, forgot to mention the OS, DLC and OOo versions :D

I'm using Progress 9.1A on WinXP. My OOo version is 2.0.3

here's a sample code

/* start */
{CalcUtils.i}

RUN open_calc.

RUN new_book.

RUN write_cell_data (1, 2, "TESTING TESTING").

RUN set_col_width(2,10000).

RUN align_cell(0,0,"center","",""). /* this line triggers the error */

RUN CleanUp.
/* end */

Thanks!


Copy and Paste

Hello

Dear,

Sorry for my poor English!

I need some routines that are not developed:
Copy, Paste (Cell and Worksheet
Will someone help me?

Thank you,

Tiago Pinheiro.


Stefan Drissen's picture

Hmmm... even the first

Hmmm... even the first write_cell_data is not positioning the 'test' correctly. In my case it is being place in the top left corner (cell 0,0). Adding a SetValue to the align_cell (and removing the :HoriJustify) then does result in the debug test being positioned at the supplied coordinates.

Using XP / 9.1E04 / 2.4.0


Stefan Drissen's picture

Well...

Wasn't that fun, don't we all love global variables...

The PROCEUDRE write_cell_data is incorrectly referencing the procedure scoped variables iCol and iRow instead of the INPUT parameters ip_Col and ip_Row...

This does not solve the align error but does restore some sanity.

Note that googling on HoriJustify and OpenOffice returns among others:

In a TextTable, the cells have com.sun.star.text.CellProperties.
In a Spreadsheet, the cells have com.sun.star.table.CellProperties.

So it would seem something is wrong...


write_cell_data

Dear Mr. Stefan Drissen,

"
Sorry, but I cant get the point how to fix the PROCEDURE write_cell_data, so it's will work correctly.
"

OOOPSSS, there's must be something on my eyes :) ok, got the point ....

Regards,
Virgani D


Overriting the cell value

Dear Mr. Stefan Drissen,

I have temp table records and i want to add those values to individual cell.

For the reason i have added the three fields in the table whaich as RowNo,ColNo and CelValue.

I did add code like

for each ttExport.
write_cell_data(ttExport.cn,ttExport.rn,ttExport.val).
end.

My Issue:

It overrite it first cell itself. The last added cell value showed in the first row, first column.

I tried another way

insert_row(1,10)
for each ttExport.
insert_col(ttExport.cColNo,8 ).
write_cell_data(ttExport.cn,ttExport.rn,ttExport.val).
end.

Even though the overrite to the same column itself.

Thanks,
Rajasekat

how to fix this


Hello, I found out where is

Hello,

I found out where is the error for procedure write_cell_data only change this:

ASSIGN chCell = chWorkSheet:GetCellByPosition(iCol,iRow).

for this:

ASSIGN chCell = chWorkSheet:GetCellByPosition(ip_Col,ip_Row).

Thanks,
Pierre Blitzkow.