Visual Basic

<br />
Public Sub CreatExcel()<br />
        Dim priorSum As Integer = 0<br />
        Dim newSum As Integer = 0<br />
        Dim xlProcID As Integer = 0<br />
        For Each proc As Process In Process.GetProcessesByName("excel")<br />
            priorSum += proc.Id<br />
        Next proc<br />
        Dim excelApp As Object = Nothing<br />
        Dim excelBook As Object = Nothing<br />
        Dim excelWorksheet As Object = Nothing<br />
        Try<br />
            excelApp = New Application<br />
            excelBook = excelApp.Workbooks.Add<br />
            excelWorksheet = CType(excelBook.Worksheets(1), Worksheet)<br />
            excelApp.Visible = False<br />
            For Each proc As Process In Process.GetProcessesByName("excel")<br />
                newSum += proc.Id<br />
            Next proc<br />
            xlProcID = newSum - priorSum<br />
            With excelWorksheet<br />
                ' Add/export some data to excel<br />
                ' Handy hint  <br />
                ' This code will apply border ALL the way around a group of cells 'x,x' i.e. 'A1:A5'<br />
                .Range(x, x).Borders(XlBordersIndex.xlEdgeLeft).LineStyle = XlLineStyle.xlContinuous<br />
                .Range(x, x).Borders(XlBordersIndex.xlEdgeLeft).Weight = XlBorderWeight.xlThin<br />
                .Range(x, x).Borders(XlBordersIndex.xlEdgeLeft).ColorIndex = XlColorIndex.xlColorIndexAutomatic<br />
                .Range(x, x).Borders(XlBordersIndex.xlEdgeRight).LineStyle = XlLineStyle.xlContinuous<br />
                .Range(x, x).Borders(XlBordersIndex.xlEdgeRight).Weight = XlBorderWeight.xlThin<br />
                .Range(x, x).Borders(XlBordersIndex.xlEdgeRight).ColorIndex = XlColorIndex.xlColorIndexAutomatic<br />
                .Range(x, x).Borders(XlBordersIndex.xlEdgeTop).LineStyle = XlLineStyle.xlContinuous<br />
                .Range(x, x).Borders(XlBordersIndex.xlEdgeTop).Weight = XlBorderWeight.xlThin<br />
                .Range(x, x).Borders(XlBordersIndex.xlEdgeTop).ColorIndex = XlColorIndex.xlColorIndexAutomatic<br />
                .Range(x, x).Borders(XlBordersIndex.xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous<br />
                .Range(x, x).Borders(XlBordersIndex.xlEdgeBottom).Weight = XlBorderWeight.xlThin<br />
                .Range(x, x).Borders(XlBordersIndex.xlEdgeBottom).ColorIndex = XlColorIndex.xlColorIndexAutomatic<br />
                .Range(x, x).Borders(XlBordersIndex.xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous<br />
                .Range(x, x).Borders(XlBordersIndex.xlInsideHorizontal).Weight = XlBorderWeight.xlThin<br />
                .Range(x, x).Borders(XlBordersIndex.xlInsideHorizontal).ColorIndex = XlColorIndex.xlColorIndexAutomatic<br />
<br />
            End With<br />
<br />
<br />
           ' Hany hint you can remove grid lines and name your sheets like so<br />
            With excelApp<br />
                .ActiveWindow.DisplayGridlines = False<br />
                .ActiveSheet.name = "My Export"<br />
            End With<br />
<br />
<br />
            ' Edit as required:<br />
            excelApp.ActiveWorkbook.SaveAs("My File Location Path\My File Name.xls")<br />
<br />
            ' You must save the file here are some ideas for saving the file so as not to<br />
            ' have any issues with existing files that you previously exported that may have<br />
            ' the same file name as your new exported file which of cause would throw errors<br />
<br />
<br />
            'Method 1 Unqueek File name when saving :=<br />
            'You don’t have to use this but this is just my way of ensuring file names is never <br />
            'the same as an old version export<br />
            '###############<br />
            Dim PublishDate As Date<br />
            PublishDate = Now()<br />
            Dim FormattedDateAsString As String<br />
            FormattedDateAsString = Format(PublishDate, "F") & " " & Format(PublishDate, "(h:mtt)")<br />
            Dim FixString As String = FormattedDateAsString<br />
            Dim GetDate As Date = Format(PublishDate, "d")<br />
            Dim TestString As String = GetDate.Day.ToString<br />
            If TestString.EndsWith("1") And TestString <> "11" Then<br />
                FormattedDateAsString = FixString.Insert(FixString.IndexOf(TestString) + 2, "st ")<br />
            ElseIf TestString.EndsWith("3") And TestString <> "13" Then<br />
                FormattedDateAsString = FixString.Insert(FixString.IndexOf(TestString) + 2, "rd ") 'FixString.Replace(TestString, (TestString & "rd"))<br />
            Else<br />
                FormattedDateAsString = FixString.Insert(FixString.IndexOf(TestString) + 2, "th ")<br />
            End If<br />
            TestString = GetDate.Year.ToString<br />
            FormattedDateAsString = FormattedDateAsString.Replace(TestString, (TestString & " at"))<br />
            FormattedDateAsString = FormattedDateAsString.Replace(":", ".")<br />
            ' Save the file<br />
            excelApp.ActiveWorkbook.SaveAs("C:\My Report Created on - " & FormattedDateAsString & ".xls")<br />
            '###############<br />
<br />
<br />
<br />
            'Method 2 remove old file :=<br />
            'You could also delete and replace old file if you rather<br />
            '#############################<br />
            If File.Exists("C:\My Report.xls") Then<br />
                File.Delete("C:\My Report.xls")<br />
            End If<br />
            '#############################<br />
<br />
<br />
            ' You don’t have to have this but I do to be sure everything is saved <br />
            For Each w In excelApp.Application.Workbooks<br />
                'save all the work sheets<br />
                w.Save()<br />
            Next w<br />
            ' close the work sheets with out prompting...may not need this either<br />
            excelApp.ActiveWorkbook.Close(SaveChanges:=False)<br />
<br />
<br />
            'Now kill excel but only the excel instance we created and NOT any other excel applications running at the same time<br />
            If Not excelApp Is Nothing Then<br />
                GC.Collect()<br />
                GC.WaitForPendingFinalizers()<br />
                GC.Collect()<br />
                GC.WaitForPendingFinalizers()<br />
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excelWorksheet)<br />
                excelWorksheet = Nothing<br />
                If Not excelBook Is Nothing Then<br />
                    System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excelBook)<br />
                End If<br />
                excelApp.Quit()<br />
                excelBook = Nothing<br />
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excelApp)<br />
                excelApp = Nothing<br />
                Dim proc As Process = Process.GetProcessById(xlProcID)<br />
                proc.Kill()<br />
            End If<br />
<br />
            ' Now if you want you can open the excel file view. The advantage of this code is that<br />
            ' when the user closes the excel it wont get stuck in the system task manager<br />
            Dim p As New System.Diagnostics.Process<br />
<br />
            ' Set this to the file location and file name of ythe excel you just exported<br />
            ' Example: "My File Location Path\My File Name.xls" or "C:\My Report.xls" or "C:\My Report Created on - " & FormattedDateAsString & ".xls"<br />
            ' Edit as required:<br />
            p.StartInfo.FileName = "C:\My Report Created on - " & FormattedDateAsString & ".xls"<br />
            p.StartInfo.WindowStyle = ProcessWindowStyle.Minimized<br />
            p.Start()<br />
<br />
            'Wait until the process passes back an exit code add if you want but your application <br />
            'wont run untill the user closes the excel down    <br />
            'p.WaitForExit()<br />
<br />
            ' Free the recources ...may not need this line as will work with out but added to be save<br />
            ' Remove if you add the line above 'WaitForExit()'<br />
            p = Nothing<br />
<br />
            'Free resources associated with this process<br />
            ' add this if you add the line above 'WaitForExit()'<br />
            ' p.Close()<br />
<br />
<br />
        Catch ex As Exception<br />
            MsgBox(ex.Message & vbTab & ex.Source & vbTab & ex.HelpLink)<br />
        End Try<br />
    End Sub<br />

sum (ToNumber({A String Field}))