在libreoffice calc中，如何通过libreoffice Basic更改单元格在不崩溃的情况下设置为event侦听器的值？

Question

我正在尝试创建两个桌子，这些表会自动地互相反映它们的任何一个。

为此，我添加了当用户编辑这些表的单元格时触发的事件侦听器。

不幸的是，编辑其中一张表会导致Libreoffice崩溃，即使更改确实正确地反映了，如重新打开文件时所见。
我认为崩溃可能是由于无休止的循环引用，但是在将其制成非圆形后仍然崩溃（通过评论代码的相关部分，以便更改仅反映了一种方式而不是两种方式） 。
我注意到该代码在写入没有事件侦听器设置的单元格时工作正常。

我如何将一个设置在他们的事件侦听器崩溃的情况下写给其中一个单元格？

您可能需要下载以下文件。请运行MAIN，然后尝试编辑计划表的单元格C3。任意字符串“ C”应写入服务表的单元格C4。

这是代码的简化版本：

REM  *****  BASIC  *****

const SERVICESSHEET_NUMBER = 2
const SERVICESSHEET_SERVICES_COLUMN = 2

Type cellStruct
    columnNumber As Integer
    rowNumber As Integer
End Type



Sub UpdateServicesSheet(editedCell As cellStruct, newValue As String)
    
    Dim oSheets
    Dim servicesSheet
        
    oSheets = ThisComponent.getSheets()
    servicesSheet = oSheets.getByIndex(SERVICESSHEET_NUMBER)

    servicesSheet.getCellByPosition(SERVICESSHEET_SERVICES_COLUMN, 3).setString(newValue)
    
End Sub


Private oListener, cellRange as Object

Sub AddListener
    Dim sheet, cell as Object

    sheet = ThisComponent.Sheets.getByIndex(0)  'get leftmost sheet
    servicesSheet = ThisComponent.Sheets.getByIndex(2)

    cellRange = sheet.getCellrangeByName("C3")
    oListener = createUnoListener("Modify_","com.sun.star.util.XModifyListener")  'create a listener
    cellRange.addModifyListener(oListener)  'register the listener


    cellRange = servicesSheet.getCellrangeByName("C4")
    oListener = createUnoListener("Modify_","com.sun.star.util.XModifyListener")  'create a listener
    cellRange.addModifyListener(oListener)  'register the listener
End Sub

global CircularReferenceAllowed As boolean

Sub Modify_modified(oEv)

    Dim editedCell As cellStruct
    Dim newValue As String
    
    editedCell.columnNumber = 2
    editedCell.rowNumber = 2
    

    If CircularReferenceAllowed Then
        CircularReferenceAllowed = false
        UpdateServicesSheet(editedCell, "C")
    End If

End Sub

Sub Modify_disposing(oEv)
End Sub

Sub RmvListener
    cellRange.removeModifyListener(oListener)
End Sub


Sub Main

    CircularReferenceAllowed = true
    AddListener

End Sub

crossed to：

• openoffice论坛
• libreoffice话语平台

Answer 1

似乎事件触发器在另一个事件的功能中导致崩溃。无论如何，解决方案是删除侦听器，然后在修改另一个单元格之后将其添加回。
您确实需要全球化侦听器和单元对象才能进行此工作。

该代码被简化为在第一张纸上的C3和C15上使用。它还将在C14上输出一些信息，这对于您的目的而言并不是必需的，但是我用它来查看发生了什么。您需要根据需要采用。

global goListener as Object
global goListener2 as Object
global goCellR as Object
global goCellR2 as Object
global goSheet as Object

global giRun as integer
global giUpd as Integer

Sub Modify_modified(oEv)
    Dim sCurStr$
    Dim sNewStr As String
    'xRay oEv
    
    giRun = giRun + 1
    sCurStr = oEv.source.string

    oCell = goSheet.getCellByPosition(2, 14)
    If (oCell.getString() <> sCurStr) Then
        ' only update if it's different.
        giUpd = giUpd + 1
        goCellR2.removeModifyListener(goListener2)
        oCell.setString(sCurStr)
        goCellR2.addModifyListener(goListener2)
    End If
    sNewStr =sCurStr & " M1 Run=" & giRun & " Upd=" & giUpd
    goSheet.getCellByPosition(2, 13).setString(sNewStr)    
End Sub

Sub Modify2_modified(oEv)
    Dim sCurStr$
    Dim sNewStr As String
    Dim oCell as Object
    'xRay oEv
    
    giRun = giRun + 1
    sCurStr = oEv.source.string
    oCell = goSheet.getCellByPosition(2, 2)
    If (oCell.getString() <> sCurStr) Then
        ' only update if it's different.
        giUpd = giUpd + 1
        goCellR.removeModifyListener(goListener)
        oCell.setString(sCurStr)
        goCellR.addModifyListener(goListener)
    End If
    sNewStr =sCurStr & " M2 Run=" & giRun & " Upd=" & giUpd
    goSheet.getCellByPosition(2, 13).setString(sNewStr)
End Sub

Sub Modify_disposing(oEv)
    MsgBox "In Modify_disposing"
End Sub

Sub Modify2_disposing(oEv)
    MsgBox "In Modify2_disposing"
End Sub

Sub RmvListener
    MsgBox "In RmvListener"
    goCellR.removeModifyListener(goListener)
    goCellR2.removeModifyListener(goListener2)
End Sub


Sub AddListener

    goSheet = ThisComponent.Sheets.getByIndex(0)  'get leftmost goSheet
    'servicesSheet = ThisComponent.Sheets.getByIndex(2)

    goCellR = goSheet.getCellrangeByName("C3")
    goListener = createUnoListener("Modify_","com.sun.star.util.XModifyListener")  'create a listener
    goCellR.addModifyListener(goListener)  'register the listener

    goCellR2 = goSheet.getCellrangeByName("C15")
    goListener2 = createUnoListener("Modify2_","com.sun.star.util.XModifyListener")  'create a listener
    goCellR2.addModifyListener(goListener2)  'register the listener
End Sub

Sub Main
    giRun = 0
    giUpd = 0
    AddListener

End Sub