Re: [dev] 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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]