Open Door - Reporting, Charts, Enquiry Drill-Downs

Public Class ExcelColumnTotals
    Inherits Generic.List(Of ExcelColumnTotal)


    Public Function GetGrandTotal(ByVal Column As Long) As String
        Dim rv As String = ""
        Try
            Dim ColLetter As String = GetColLetter(Column)
            Dim cTot As ExcelColumnTotal = Me.Item(Column)
            With cTot
                If .TotalRows.Count > 0 Then
                    If .TotalRows.Count = 1 Then
                        rv &= "=" & ColLetter & .TotalRows(0)
                    Else
                        rv = "=("
                        Dim i As Long = 0
                        For Each rowID As Long In .TotalRows
                            i += 1
                            rv &= ColLetter & rowID
                            If i <> .TotalRows.Count Then rv &= "+"
                        Next
                        rv &= ")"
                    End If
                Else
                    'For when they're not sub-totalling
                    If .LinesStart <> 0 And .LinesEnd <> 0 Then
                        If .LinesStart <> .LinesEnd Then
                            rv = "=SUM(" & ColLetter & .LinesStart & ":" & ColLetter & .LinesEnd & ")"
                        Else
                            rv = "=" & ColLetter & .LinesStart
                        End If
                        .LinesStart = 0
                        .LinesEnd = 0
                    End If
                End If
            End With
        Catch ex As Exception
            ShowMessage(ex.Message)
        End Try
        Return rv
    End Function

    Public Function GetTotal(ByVal Column As Long) As String
        Dim rv As String = ""
        Try
            Dim ColLetter As String = GetColLetter(Column)
            Dim cTot As ExcelColumnTotal = Me.Item(Column)
            With cTot
                If .SubTotalRows.Count > 0 Then
                    If .SubTotalRows.Count = 1 Then
                        rv &= "=" & ColLetter & .SubTotalRows(0)
                    Else
                        rv = "=("
                        Dim i As Long = 0
                        For Each rowID As Long In .SubTotalRows
                            i += 1
                            rv &= ColLetter & rowID
                            If i <> .SubTotalRows.Count Then rv &= "+"
                        Next
                        rv &= ")"
                    End If
                    .SubTotalRows.Clear()
                Else
                    'For when they're not sub-totalling
                    If .LinesStart <> 0 And .LinesEnd <> 0 Then
                        If .LinesStart <> .LinesEnd Then
                            rv = "=SUM(" & ColLetter & .LinesStart & ":" & ColLetter & .LinesEnd & ")"
                        Else
                            rv = "=" & ColLetter & .LinesStart
                        End If
                        .LinesStart = 0
                        .LinesEnd = 0
                    End If
                End If
            End With
        Catch ex As Exception
            ShowMessage(ex.Message)
        End Try
        Return rv
    End Function

    Public Function GetSubTotal(ByVal Column As Long) As String
        Dim rv As String = ""
        Try
            Dim ColLetter As String = GetColLetter(Column)
            Dim cTot As ExcelColumnTotal = Me.Item(Column)
            With cTot
                If .LinesStart <> 0 And .LinesEnd <> 0 Then
                    If .LinesStart <> .LinesEnd Then
                        rv = "=SUM(" & ColLetter & .LinesStart & ":" & ColLetter & .LinesEnd & ")"
                    Else
                        rv = "=" & ColLetter & .LinesStart
                    End If
                End If
            End With
        Catch ex As Exception
            ShowMessage(ex.Message)
        End Try
        Return rv
    End Function

    Private Function GetColLetter(ByVal ColNo As Long) As String
        Dim rv As String
        Try
            Dim x2 As Double, x3 As Integer
            Dim x4 As String, x5 As Integer
            Dim x6 As String, x7 As String
            If ColNo > 702 Or ColNo < 1 Then Exit Function
            x2 = (ColNo - 1) / 26
            x3 = Int(x2)
            x4 = Chr(x3 + 64)
            x5 = ColNo Mod 26
            If x4 = "@" Then x6 = "" Else x6 = x4
            If x5 = 0 Then x7 = "Z" Else x7 = Chr(x5 + 64)
            rv = x6 & x7
        Catch ex As Exception
            ShowMessage(ex.Message)
        End Try
        Return rv
    End Function
End Class