в 16:40, 26 мая 2010

2010-05-26T16:40:30Z

Admin: 1 версия

2010-05-26T12:56:13Z

1 версия

Новая страница

==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>

← Предыдущая	Версия 16:40, 26 мая 2010
(нет различий)

VB.Net Tutorial/Windows/Excel - История изменений

в 16:40, 26 мая 2010

Admin: 1 версия