VB.Net Tutorial/Windows/Excel

Calculate Expression

public class Test
   public Shared Sub Main
        Dim objExcel As Excel.Application
        objExcel = New Excel.Application

    
        Dim strMath As String
        strMath = "cos(3.673/4)/exp(-3.333)"
        If strMath <> "" Then
            Try
                Console.WriteLine(objExcel.Evaluate(strMath).ToString)
            Catch exc As Exception
                Console.WriteLine(exc.Message)
            End Try
        End If
        objExcel.Workbooks.Close()
        objExcel.Quit()
        objExcel = Nothing
   End Sub
End class

Create a Spreadsheet

public class Test
   public Shared Sub Main
        Dim objExcel As Excel.Application
        objExcel = New Excel.Application
    
        Dim objSheet As New Excel.Worksheet
        Dim objRange As Excel.Range
        Dim intRow, intCol As Integer
        objExcel.Visible = True
        "Add a worksheet and then add some content to it.
        objSheet = objExcel.Workbooks.Add.Worksheets.Add
        With objSheet
            .Cells(2, 1).Value = "1st Quarter"
            .Cells(2, 2).Value = "2nd Quarter"
            .Cells(2, 3).Value = "3rd Quarter"
            .Cells(2, 4).Value = "4th Quarter"
            .Cells(2, 5).Value = "Year Total"
            .Cells(3, 1).Value = 123.45
            .Cells(3, 2).Value = 435.56
            .Cells(3, 3).Value = 376.25
            .Cells(3, 4).Value = 425.75
            .Range("A2:E2").Select()
            With objExcel.Selection.Font
                .Name = "Verdana"
                .FontStyle = "Bold"
                .Size = 12
            End With
        End With
        "Set the alignment.
        objSheet.Range("A2:E2").Select()
        objExcel.Selection.Columns.AutoFit()
        objSheet.Range("A2:E2").Select()
        With objExcel.Selection
            .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
        End With
        "Format some numbers.
        objSheet.Range("A3:E3").Select()
        With objExcel.Selection.Font
            .Name = "Verdana"
            .FontStyle = "Regular"
            .Size = 11
        End With
        "Display summary information.
        objSheet.Cells(3, 5).Value = "=Sum(A3:D3)"
        objRange = objSheet.UsedRange
        For intCol = 1 To objRange.Columns.Count
            For intRow = 1 To objRange.Rows.Count
                Console.WriteLine(objRange.Cells(intRow, intCol).value)
            Next
        Next
        objExcel.Workbooks.Close()
        objExcel.Quit()
        objExcel = Nothing
   End Sub
End class

Create function in Excel

public class Test
   public Shared Sub Main
        Dim objExcel As New Excel.Application
        objExcel.Visible = True
        objExcel.Workbooks.Add()
        objExcel.Range("A1").Select()
        objExcel.ActiveCell.FormulaR1C1 = "75"
        objExcel.Range("B1").Select()
        objExcel.ActiveCell.FormulaR1C1 = "125"
        objExcel.Range("C1").Select()
        objExcel.ActiveCell.FormulaR1C1 = "255"
        objExcel.Range("D1").Select()
        objExcel.ActiveCell.FormulaR1C1 = "295"
        objExcel.Range("A1:D1").Select()
        objExcel.Range("E1").Activate()
        objExcel.ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"
        objExcel.Range("A1:E1").Select()
        objExcel.Selection.Font.Bold = True
        objExcel = Nothing
   End Sub
End Class

Import data

public class Test
   public Shared Sub Main
        Dim objExcel As Excel.Application
        objExcel = New Excel.Application
    
        Dim objSheet As New Excel.Worksheet
        Dim objData As Excel.Range
        Dim intCol, intRow As Integer
        objExcel.Visible = True
        TextBox1.Clear()
        objSheet = objExcel.Workbooks.Open("C:\Temp\Test.xls").Worksheets.Item(1)
        objExcel.Range("A2:E3").Select()
        objData = objExcel.Selection
        For intCol = 1 To 5
            For intRow = 1 To 2
                Console.WriteLine(objData(intRow, intCol).Value)
            Next
        Next
        objExcel.Workbooks.Close()
        objExcel.Quit()
        objExcel = Nothing
   End Sub
End class

Sort imported data

public class Test
   public Shared Sub Main
        Dim objExcel As Excel.Application
        objExcel = New Excel.Application
    
        Dim objSheet As New Excel.Worksheet
        Dim objData As Excel.Range
        Dim intCol, intRow As Integer
        Call OpenExcel()
        objExcel.Visible = True
        objSheet = objExcel.Workbooks.Open("C:\Temp\Test.xls").Worksheets.Item(1)
        objExcel.Range("A2:E3").Select()
        objData = objExcel.Selection
        objData.Sort(Key1:=objData.Range("A2"), Order1:=Excel.XlSortOrder.xlAscending)
        For intCol = 1 To 5
            For intRow = 1 To 2
                Console.WriteLine(objData(intRow, intCol).value)
            Next
        Next

        objExcel.Workbooks.Close()
        objExcel.Quit()
        objExcel = Nothing
   End Sub
End class