VB.Net Tutorial/Windows/Excel

Материал из VB Эксперт
Перейти к: навигация, поиск

Calculate Expression

<source lang="vbnet">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</source>

Create a Spreadsheet

<source lang="vbnet">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</source>

Create function in Excel

<source lang="vbnet">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</source>

Import data

<source lang="vbnet">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</source>

Sort imported data

<source lang="vbnet">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</source>