OOobbs3/43

  0
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105

Sub AutoSumForCalc
  oDoc = ThisComponent
  If NOT oDoc.supportsService("com.sun.star.sheet.SpreadsheetDocument") Then Exit Sub
  
  nCellFlags = com.sun.star.sheet.CellFlags
  nCellType = com.sun.star.table.CellContentType
  
  oSelection = oDoc.getCurrentSelection()
  
  If oSelection.supportsService("com.sun.star.sheet.SheetCell") Then
    ' single cell mode
    oCell = oSelection
    oSheet = oCell.getSpreadsheet()
    aCellAddr = oCell.getCellAddress()
    
    oCellRange = oSheet.getCellRangeByPosition( _
        aCellAddr.Column, 0, aCellAddr.Column, aCellAddr.Row - 1)
    
    oRanges = oCellRange.queryContentCells(nCellFlags.VALUE + nCellFlags.FORMULA + nCellFlags.STRING)
    If oRanges.getCount() Then
      oRange = oRanges.getByIndex(oRanges.getCount() - 1)
      aRangeAddr = oRange.getRangeAddress()
      If aRangeAddr.EndRow < aCellAddr.Row - 1 Then
        oRange = oSheet.getCellRangeByPosition( _
            aRangeAddr.StartColumn, aRangeAddr.StartRow, _
            aRangeAddr.EndColumn, aCellAddr.Row - 1)
      End If
      
      sFormula = "=SUM(" & GetRangeAddress(oRange) & ")"
      
      oCell.setFormula(sFormula)
      ToEditMode(oDoc)
    End If
    
  ElseIf oSelection.supportsService("com.sun.star.sheet.SheetCellRange") Then
    oCellRange = oSelection
    aRangeAddr = oCellRange.getRangeAddress()
    
    ' check all last rows are empty
    bAllEmpty = True
    nRow = aRangeAddr.EndRow - aRangeAddr.StartRow
    For i = 0 To (aRangeAddr.EndColumn - aRangeAddr.StartColumn) step 1
      oCell = oCellRange.getCellByPosition(i, nRow)
      If oCell.getType() <> nCellType.EMPTY Then
        bAllEmpty = False
      End If
    Next
    If bAllEmpty Then
      ' set sum in each cell of the last row
      If (aRangeAddr.EndRow - aRangeAddr.StartRow) > 0 Then
        For i = 0 To (aRangeAddr.EndColumn - aRangeAddr.StartColumn) step 1
          oCell = oCellRange.getCellByPosition(i, nRow)
          oCellRange2 = oCellRange.getCellRangeByPosition(i, 0, i, nRow - 1)
          sFormula = "=SUM(" & GetRangeAddress(oCellRange2) & ")"
          oCell.setFormula(sFormula)
        Next
      End If
    Else
      oSheet = oCellRange.getSpreadsheet()
      
      On Error GoTo IndexOutOfBoundExceptionHandle
      For i =aRangeAddr.StartColumn To aRangeAddr.EndColumn step 1
        oCell = oSheet.getCellByPosition(i, nRow)
        oRange = oSheet.getCellRangeByPosition(i, aRangeAddr.StartRow, i, aRangeAddr.EndRow)
        
        sFormula = "=SUM(" & GetRangeAddress(oRange) & ")"
        oCell.setFormula(sFormula)
      Next
      IndexOutOfBoundExceptionHandle:
    End If
  End If
End Sub
 
 
Function GetRangeAddress(oRange As Object) As String
  sAddress = ""
  If oRange.supportsService("com.sun.star.sheet.SheetCellRange") Then
    aRangeAddr = oRange.getRangeAddress()
    sAddress = ConvertToColumn(aRangeAddr.StartColumn) & CStr(aRangeAddr.StartRow + 1) & ":" & _
        ConvertToColumn(aRangeAddr.EndColumn) & CStr(aRangeAddr.EndRow + 1)
  End If
  GetRangeAddress = sAddress
End Function
 
 
Function ConvertToColumn(nColumn As Long) As String
  nCol = nColumn
  nMod = nCol mod 26
  sAddr = chr(65 + nMod)
  nCol = (nCol - nMod) / 26
  While nCol > 26
    nMod = nCol mod 26
    sAddr = chr(65 + nMod - 1) & sAddr
    nCol = (nCol - nMod) / 26
  WEnd
  If nCol >= 1 Then sAddr = chr(65 + nCol - 1) & sAddr
  
  ConvertToColumn = sAddr
End Function
 
 
Sub ToEditMode(oDoc)
  dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
  dispatcher.executeDispatch(oDoc.getCurrentController(), _
      ".uno:SetInputMode", "", 0, Array())
End Sub