VB.Net Tutorial/Windows/Excel
Содержание
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>