Excel Automation using VB.NET

the following code can be used for Excel automation using VB.Net

Excel Automation

Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click
' An Excel spreadsheet involves a hierarchy of objects, from Application
' to Workbook to Worksheet.
Dim excelApp As New Excel.Application()
Dim excelBook As Excel.Workbook = excelApp.Workbooks.Add
Dim excelWorksheet As Excel.Worksheet = _
CType(excelBook.Worksheets(1), Excel.Worksheet)

' Unlike the Word demo, we'll make the spreadsheet visible so you can see
' the data being entered.
excelApp.Visible = True

With excelWorksheet
' Set the column headers and desired formatting for the spreadsheet.
.Columns().ColumnWidth = 21.71
.Range("A1").Value = "Item"
.Range("A1").Font.Bold = True
.Range("B1").Value = "Price"
.Range("B1").Font.Bold = True
.Range("C1").Value = "Calories"
.Range("C1").Font.Bold = True

' Start the counter on the second row, following the column headers
Dim i As Integer = 2
' Loop through the Rows collection of the DataSet and write the data
' in each row to the cells in Excel.
Dim dr As DataRow
For Each dr In dsMenu.Tables(0).Rows
.Range("A" & i.ToString).Value = dr("Item")
.Range("B" & i.ToString).Value = dr("Price")
.Range("C" & i.ToString).Value = dr("Calories")
i += 1
Next

' Select and apply formatting to the cell that will display the calorie
' average, then call the Average formula. Note that the AVERAGE function
' is localized, so the below code may need to be updated based on the
' locale the application is deployed to.
.Range("C7").Select()
.Range("C7").Font.Color = RGB(255, 0, 0)
.Range("C7").Font.Bold = True
excelApp.ActiveCell.FormulaR1C1 = "=AVERAGE(R[-5]C:R[-1]C)"
End With
End Sub


Speak merlin

' Handles the Speak button click event, which makes Merlin say whatever the
' user enters into the TextBox.
Private Sub btnSpeak_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSpeak.Click
If Not txtSpeak.Text = "" Then
agentCharacter.Speak(txtSpeak.Text)
End If
End Sub
To animate merlin
' Handles the click events of all the Merlin animations.
Private Sub rdoAnimations_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles optDontRecognize.Click, optWrite.Click, optWave.Click, optApplaud.Click, optSurprised.Click, optSuggest.Click, optAnnounce.Click, optDoMagic.Click, optExplain.Click, optCongrats.Click
' Have Merlin immediately cease what he is doing when the user selects a
' new animation, or else the animations will stack up in a queue and might
' appear not to work when selected.
agentCharacter.StopAll()

Dim radio As RadioButton = CType(sender, RadioButton)
Dim radioTag As String = CStr(radio.Tag)
With agentCharacter
Select Case radioTag
Case "GetAttention"
.Play("GetAttention")
.Play("GetAttentionReturn")
Case "Explain"
.Play("Explain")
Case "Congratulate"
.Play("Congratulate")
Case "Announce"
.Play("Announce")
Case "Applaud"
.Play("Congratulate_2")
Case "DontRecognize"
.Play("DontRecognize")
Case "Write"
.Play("Write")
Case "Surprised"
.Play("Surprised")
Case "Suggest"
.Play("Suggest")
Case "Wave"
.Play("Wave")
End Select
End With
End Sub




To hide merlin
' Handles the CheckChanged event for the checkbox that hides or shows Merlin.
Private Sub chkMerlinOnOff_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles chkMerlinHide.CheckedChanged
With agentCharacter
If chkMerlinHide.Checked Then

.StopAll()
.Hide()
Else
.Show()
End If
End With
End Sub