Creating a listener for Cell Data change in OpenOffice calc.

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Creating a listener for Cell Data change in OpenOffice calc.

Sathya C
Hi,
  I am writing an add-in for OpenOffice Calc. I want my program to get
notified when something is entered into a the spreadsheet cell or the data
has been modified in the data cell. Can you please advice me which Listener
I should be using. I tried using XEventListener and
XActivationEventListener. But none of these interfaces are delivering me the
events when the datacell change occurs.

Thanks in advance,
-Sathya
Reply | Threaded
Open this post in threaded view
|

Re: Creating a listener for Cell Data change in OpenOffice calc.

Niklas Nebel
Sathya C wrote:
>   I am writing an add-in for OpenOffice Calc. I want my program to get
> notified when something is entered into a the spreadsheet cell or the data
> has been modified in the data cell. Can you please advice me which Listener
> I should be using. I tried using XEventListener and
> XActivationEventListener. But none of these interfaces are delivering me the
> events when the datacell change occurs.

Use the XModifyBroadcaster interface of the cell or cell range you want
to observe.

Niklas

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Creating a listener for Cell Data change in OpenOffice calc.

Sathya C
Hi Niklas,

   Thank you very much. I was able to get the Data modified events using
XModifyBroadcaster interface

Thanks & Regards,
-Sathya

On 4/18/06, Niklas Nebel <[hidden email]> wrote:

>
> Sathya C wrote:
> >   I am writing an add-in for OpenOffice Calc. I want my program to get
> > notified when something is entered into a the spreadsheet cell or the
> data
> > has been modified in the data cell. Can you please advice me which
> Listener
> > I should be using. I tried using XEventListener and
> > XActivationEventListener. But none of these interfaces are delivering me
> the
> > events when the datacell change occurs.
>
> Use the XModifyBroadcaster interface of the cell or cell range you want
> to observe.
>
> Niklas
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>
Reply | Threaded
Open this post in threaded view
|

Re: Creating a listener for Cell Data change in OpenOffice calc.

Sathya C
In reply to this post by Niklas Nebel
Hi,
  Is there a way to know which cell in the range was modified?

Thanks & Regards,
-Sathya

On 4/18/06, Niklas Nebel <[hidden email]> wrote:

>
> Sathya C wrote:
> >   I am writing an add-in for OpenOffice Calc. I want my program to get
> > notified when something is entered into a the spreadsheet cell or the
> data
> > has been modified in the data cell. Can you please advice me which
> Listener
> > I should be using. I tried using XEventListener and
> > XActivationEventListener. But none of these interfaces are delivering me
> the
> > events when the datacell change occurs.
>
> Use the XModifyBroadcaster interface of the cell or cell range you want
> to observe.
>
> Niklas
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>
Reply | Threaded
Open this post in threaded view
|

Re: Creating a listener for Cell Data change in OpenOffice calc.

Niklas Nebel
Sathya C wrote:
>   Is there a way to know which cell in the range was modified?

If you add the listener to a cell range or sheet, no, and you'll also be
notified only once if several cells are modified at the same time. If
you want to observe individual cells (and there's not too many of them),
you can try adding a listener to each cell.

Niklas

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Creating a listener for Cell Data change in OpenOffice calc.

Gerrit Jasper
Niklas Nebel wrote:

> Sathya C wrote:
>
>>   Is there a way to know which cell in the range was modified?
>
>
> If you add the listener to a cell range or sheet, no, and you'll also
> be notified only once if several cells are modified at the same time.
> If you want to observe individual cells (and there's not too many of
> them), you can try adding a listener to each cell.
>
> Niklas


Sathya, Niklas,

1.    This reaction is late, but I hope it's not too late. The demo I
tried to write caused me some trouble,
and then I didn't have time and so on.

2.   Niklas is right where he says "and there's not too many of them".  
For a cell range of say 10 columns by 20 rows
you would need 200 listeners. You could reduce this number by attaching
a listener to every column and every row
in the range, resulting in 10 + 20 = 30 listeners. When one cell is
modified two listeners will provide respectively their
column and row number and you'll have a position.  When several cells
are modified at the same time you'll get a lot
of calls from a bunch of listeners.

3.   A workable solution with only one listener for the whole range
might be the use of two arrays for comparison and
two others to store the result. The first one should contain the data in
the range before modification and the second
the data after modification.  The listener should trigger copying of  
the modified data to the second array.
Then compare both arrays row by row, cell by cell, and only when there
is a difference store column and row numbers
respectively in two one dimensional arrays.  These two arrays then
contain the coordinates of your modified cells.
(Thanks to Andrew Pitonyak for pointing out the use of arrays if you
want speed.) For this to work the first thing you'll
want to do when you open the document is load the current contents of
the range into the first array, so you should
attach that action to the "Open Document" event using
Tools-Customize-Events. At the same time install the listener,
so I'ld combine that in one subroutine. After that every time you have
done whatever you want to do with the modified
cells, you must copy the then current contents of the range to the first
array to be ready for a next comparison.

4.   The trouble I mentioned above was that sometimes the first array
lost its data and turned up empty (do you use
Bernard Marcelly's XrayTool? It's great!).  I had no idea what caused
this, and actually I still am not sure.  Today I have
been tinkering with Dim and Redim of the arrays and I think it's doing
what it should now. But don't pin me down on it, please.
Maybe someone on the list sees weak spots.  The demo code to watch a
range called "chckRng" looks like this:

Global oDocument    As Object
Global oDocSecond As Object, oSheet As Object, oCompSheet As Object,
oRange As Object, oCell As Object
Global sSheetName$, sRangeName$, sChartName As String
Global mRngWas() As Double, mRngIs() As Double                        
'Arrays  
Global mCols() As Integer, mRows() As Integer                            
    'Arrays  
Global i%, j%, k    As Integer

Sub setupOpenDoc                                                'Attach
this to the "Open Document" event.
    Call uCrngListener
    Call getRngWas                                                  
End Sub

Sub uCrngListener  
    oDocument = ThisComponent  
    oCrng = oDocument.Sheets.getByName( "Sheet1" ).GetCellRangeByName(
"chckRng" )  
    oCrngData = CreateUnoListener( "CrngListener_",
"com.sun.star.util.XModifyListener" )
    oCrng.addModifyListener( oCrngData )
End Sub

Sub CrngListener_modified( oEvent )
    Call compareArrays                          
End Sub

Sub CrngListener_queryInterface( oEvent )
      '    MsgBox    "queryInterface"                        'Sub must exist
End Sub

Sub CrngListener_disposing( oEvent )
    '    MsgBox    "disposing"                                     'Sub
must exist
End Sub


Sub getRngWas                                                          
'Gets old = current data
    oDocument = ThisComponent  
    oSheet = oDocument.Sheets.getByName( "Sheet1" )
    oRange = oSheet.GetCellRangeByName("chckRng")
    mRngWas = oRange.getDataArray()
End Sub  
   
Sub getRngIs                                                            
     'Gets modified data.
    oDocument = ThisComponent      
    oSheet = oDocument.Sheets.getByName( "Sheet1" )
    oRange = oSheet.GetCellRangeByName( "chckRng" )
    mRngIs = oRange.getDataArray()
End Sub
           
Sub compareArrays
    Call getRngIs
   
    k = 0
    ReDim mCols( k )
    ReDim mRows( k )      
    For i = LBound( mRngIs ) To UBound( mRngIs )
        oRowIs = mRngIs( i )
        oRowWas = mRngWas( i )                  
        For j = LBound( oRowIs ) To UBound( oRowIs )          
            if oRowIs( j ) <> oRowWas( j ) then
                mCols( k ) = j
                mRows( k ) = i
                k = k + 1
                ReDim Preserve mCols( k )
                ReDim Preserve mRows( k )                                
           
            end if                            
        Next     j          
    Next i
   
    Call getRngWas            'store new contents as "was"
   
    Call showDiff
End Sub

Function showDiff
    'xray mCols()
    'xray mRows()
    For m = 0 to k - 1
        MsgBox "col = " & mCols( m ) & " row = " & mRows( m )      
    Next m
End Function



---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Creating a listener for Cell Data change in OpenOffice calc.

Gerrit Jasper
In reply to this post by Niklas Nebel
I just got my e-mail back from the OOo list. What a shambles.
It looked all right when I composed it.I suppose I'll have to take a
course composing e-mails. So here it goes again.


Niklas Nebel wrote:

> Sathya C wrote:
>
>>   Is there a way to know which cell in the range was modified?
>
>
> If you add the listener to a cell range or sheet, no, and you'll also
> be notified only once if several cells are modified at the same time.
> If you want to observe individual cells (and there's not too many of
> them), you can try adding a listener to each cell.
>
> Niklas


Sathya, Niklas,

1.    This reaction is late, but I hope it's not too late. The demo I
tried to write caused me some trouble,and then I didn't have time and
so on.

2.   Niklas is right where he says "and there's not too many of them".
For a cell range of say 10 columns by 20 rows you would need 200
listeners. You could reduce this number by attaching a listener to every
column and every row in the range, resulting in 10 + 20 = 30 listeners.
When one cell is modified two listeners will provide respectively their
column and row number and you'll have a position.  When several cells
are modified at the same time you'll get a lot of calls from a bunch of
listeners.

3.   A workable solution with only one listener for the whole range
might be the use of two arrays for comparison and two others to store
the result. The first one should contain the data in the range before
modification and the second the data after modification.  The listener
should trigger copying of  the modified data to the second array.
Then compare both arrays row by row, cell by cell, and only when there
is a difference store column and row numbers respectively in two one
dimensional arrays.  These two arrays then contain the coordinates of
your modified cells. (Thanks to Andrew Pitonyak for pointing out the use
of arrays if you want speed.) For this to work the first thing you'll
want to do when you open the document is load the current contents of
the range into the first array, so you should attach that action to the
"Open Document" event using Tools-Customize-Events. At the same time
install the listener, so I'ld combine that in one subroutine. After that
every time you have done whatever you want to do with the modified
cells, you must copy the then current contents of the range to the first
array to be ready for a next comparison.

4.   The trouble I mentioned above was that sometimes the first array
lost its data and turned up empty (do you use Bernard Marcelly's
XrayTool? It's great!).  I had no idea what caused this, and actually I
still am not sure.  Today I have been tinkering with Dim and Redim of
the arrays and I think it's doing what it should now. But don't pin me
down on it, please. Maybe someone on the list sees weak spots.
The demo code to watch a range called "chckRng" looks like this:

Global oDocument    As Object
Global oDocSecond As Object, oSheet As Object, oCompSheet As Object,_
                oRange As Object, oCell As Object
Global sSheetName$, sRangeName$, sChartName As String
Global mRngWas() As Double, mRngIs() As Double   'Arrays
Global mCols() As Integer, mRows() As Integer   'Arrays
Global i%, j%, k    As Integer

Sub setupOpenDoc              'Attach this to the "Open Document" event.
    Call uCrngListener
    Call getRngWas
End Sub

Sub uCrngListener
    oDocument = ThisComponent
    oCrng = oDocument.Sheets.getByName( "Sheet1" ). _
                GetCellRangeByName( "chckRng" )
    oCrngData = CreateUnoListener( "CrngListener_", _
                "com.sun.star.util.XModifyListener" )
    oCrng.addModifyListener( oCrngData )
End Sub

Sub CrngListener_modified( oEvent )
    Call compareArrays
End Sub

Sub CrngListener_queryInterface( oEvent )
     '    MsgBox    "queryInterface"   'Sub must exist
End Sub

Sub CrngListener_disposing( oEvent )
    '    MsgBox    "disposing"       'Sub must exist
End Sub


Sub getRngWas 'Gets old = current data
    oDocument = ThisComponent
    oSheet = oDocument.Sheets.getByName( "Sheet1" )
    oRange = oSheet.GetCellRangeByName("chckRng")
    mRngWas = oRange.getDataArray()
End Sub

Sub getRngIs 'Gets modified data.
    oDocument = ThisComponent
    oSheet = oDocument.Sheets.getByName( "Sheet1" )
    oRange = oSheet.GetCellRangeByName( "chckRng" )
    mRngIs = oRange.getDataArray()
End Sub

Sub compareArrays
    Call getRngIs

    k = 0
    ReDim mCols( k )
    ReDim mRows( k )
    For i = LBound( mRngIs ) To UBound( mRngIs )
        oRowIs = mRngIs( i )
        oRowWas = mRngWas( i )
        For j = LBound( oRowIs ) To UBound( oRowIs )
            if oRowIs( j ) <> oRowWas( j ) then
                mCols( k ) = j
                mRows( k ) = i
                k = k + 1
                ReDim Preserve mCols( k )
                ReDim Preserve mRows( k )
            end if
        Next j
    Next i

    Call getRngWas            'store new contents as "was"

    Call showDiff
End Sub

Function showDiff
    'xray mCols()
    'xray mRows()
    For m = 0 to k - 1
        MsgBox "col = " & mCols( m ) & " row = " & mRows( m )
    Next m
End Function


I hope this helps,
Sincerely,
Gerrit Jasper




---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]