Dim conn As OleDbConnection Dim dtr As OleDbDataReader Dim dta As OleDbDataAdapter Dim cmd As

OleDbCommand Dim dts As DataSet Dim excel As String Dim OpenFileDialog As New
OpenFileDialog OpenFileDialog.InitialDirectory =
My.Computer.FileSystem.SpecialDirectories.MyDocuments OpenFileDialog.Filter = "All Files
(*.*)|*.*|Excel files (*.xlsx)|*.xlsx|CSV Files (*.csv)|*.csv|XLS Files (*.xls)|*xls" If
(OpenFileDialog.ShowDialog(Me) = System.Windows.Forms.DialogResult.OK) Then Dim fi As New
FileInfo(OpenFileDialog.FileName) Dim FileName As String = OpenFileDialog.FileName excel =
fi.FullName conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
excel + ";Extended Properties=Excel 12.0;") dta = New OleDbDataAdapter("Select * From
[Sheet1$]", conn) dts = New DataSet dta.Fill(dts, "[Sheet1$]") DataGridView1.DataSource = dts
DataGridView1.DataMember = "[Sheet1$]" conn.Close()

Imports System.Data.SqlClient
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

Dim MyConnection As System.Data.OleDb.OleDbConnection

Dim DtSet As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
MyConnection = New
System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data
Source='c:\vb.net-informations.xls';Extended Properties=Excel 8.0;")
MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from
[Sheet1$]", MyConnection)
MyCommand.TableMappings.Add("Table", "Net-informations.com")
DtSet = New System.Data.DataSet
MyCommand.Fill(DtSet)
DataGridView1.DataSource = DtSet.Tables(0)
MyConnection.Close()

End Sub
End Class

Hello,

If this was a windows forms app we could take for example

Get

Using VS2008 code (did this many moons ago)

Public Class Form1
Private Sub Form1_Load_1( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load

DataGridView1.AllowUserToAddRows = False
Dim ClipboardData As IDataObject = Clipboard.GetDataObject()
cmdRun.Enabled = False
If Not ClipboardData Is Nothing Then
If
(ClipboardData.GetDataPresent(DataFormats.CommaSeparatedValue)) Then
cmdRun.Enabled = True
End If
End If
End Sub
Private Sub cmdRun_Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles cmdRun.Click
DataGridView1.DataSource = Nothing
Try
Dim ClipboardData As IDataObject = Clipboard.GetDataObject()

If Not ClipboardData Is Nothing Then

If
(ClipboardData.GetDataPresent(DataFormats.CommaSeparatedValue)) Then

Dim ClipboardStream As New IO.StreamReader( _

CType(ClipboardData.GetData(DataFormats.CommaSeparatedValue), IO.Stream))

Dim FormattedData As String = ""

Dim Table As New DataTable With {.TableName = "ExcelData"}

While (ClipboardStream.Peek() > 0)

Dim SingleRowData As Array
Dim LoopCounter As Integer = 0

FormattedData = ClipboardStream.ReadLine()

SingleRowData = FormattedData.Split(",".ToCharArray)

If Table.Columns.Count <= 0 Then

For LoopCounter = 0 To SingleRowData.GetUpperBound(0)
Table.Columns.Add()
Next
LoopCounter = 0
End If

Dim rowNew As DataRow

rowNew = Table.NewRow()

For LoopCounter = 0 To SingleRowData.GetUpperBound(0)

rowNew(LoopCounter) =
SingleRowData.GetValue(LoopCounter)
 Next

LoopCounter = 0

Table.Rows.Add(rowNew)

rowNew = Nothing
End While

ClipboardStream.Close()
DataGridView1.DataSource = Table
Else
MessageBox.Show("Clipboard data does not seem to be copied
from Excel!")
End If
Else
MessageBox.Show("Clipboard is empty!")
End If
Catch exp As Exception
MessageBox.Show(exp.Message)
End Try

End Sub
Private Sub cmdCopyRowToClipboard_Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles cmdCopyRowToClipboard.Click

If DataGridView1.DataSource IsNot Nothing Then

' Set data from current row of DataGridView
Clipboard.SetText( _
String.Join(","c, _
Array.ConvertAll( _
( _
From cell As DataGridViewCell In _

DataGridView1.Rows(DataGridView1.CurrentRow.Index).Cells.Cast(Of
DataGridViewCell)() _
Select cell.Value).ToArray, Function(o) o.ToString)))

' Assuming the data just set is there we now split by comma as we
just sent the
' data comma delimited we now split by comma into a string array
then for displaying
' the data back we place each element into a StringBuilder
object.
Dim ReturningData As String() =
Clipboard.GetText.Split(",".ToCharArray)
Dim sb As New System.Text.StringBuilder
For Each item In ReturningData
sb.AppendLine(item)
Next

MessageBox.Show(sb.ToString)
End If
 End Sub
End Class

Please remember to mark the replies as answers if they help and unmark them if they provide no
help, this will help others who are looking for solutions to the same or similar problem.

Hello Everyone Good Afternoon. I have a Program in VB.Net that Exports Data in
Datagridview into an Excel File like this

After Exporting it, I will Edit the Excel File and Return it into my Datagridview but sad to say
here is my output to that
As what you see on both pictures they are Different. No Commas,No Decimal Places and
the 0 in Column Total is also added but Supposed to be it is not.
My Questions is How can I achieve the same format in excel? Put Commas and Decimal
Point in Number Columns and Do not Include the Rows that has a 0 or 0.00 in
Column Total
All I want is that my Datagridview Data is also same as the Format in Excel.

Here is my code in Import

Dim conn As OleDbConnection

Dim dta As OleDbDataAdapter

Dim dts As DataSet

Dim excel As String
Dim OpenFileDialog As New OpenFileDialog

OpenFileDialog.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.MyDocuments
OpenFileDialog.Filter = "All Files (*.*)|*.*|Excel files (*.xlsx)|*.xlsx|CSV Files (*.csv)|*.csv|XLS Files
(*.xls)|*xls"

If (OpenFileDialog.ShowDialog(Me) = System.Windows.Forms.DialogResult.OK) Then

Dim fi As New FileInfo(OpenFileDialog.FileName)

Dim FileName As String = OpenFileDialog.FileName

excel = fi.FullName
 conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excel +
";Extended Properties=Excel 12.0;")
dta = New OleDbDataAdapter("Select * From [Sheet1$]", conn)
dts = New DataSet
dta.Fill(dts, "[Sheet1$]")
DataGridView1.DataSource = dts
DataGridView1.DataMember = "[Sheet1$]"
conn.Close()

With DataGridView1
.RowHeadersVisible = False
.Columns(0).HeaderCell.Value = "Item Code"
.Columns(1).HeaderCell.Value = "Description"
.Columns(2).HeaderCell.Value = "Delivery Date"
.Columns(3).HeaderCell.Value = "Stock On-Hand"
.Columns(4).HeaderCell.Value = "Ordering Level"
.Columns(5).HeaderCell.Value = "Order Qty"
.Columns(6).HeaderCell.Value = "Approved Qty"
.Columns(7).HeaderCell.Value = "UOM"
.Columns(8).HeaderCell.Value = "Unit Price"
.Columns(9).HeaderCell.Value = "Total"
.Columns(10).HeaderCell.Value = "Remarks"

====

Copy paste di datagrid

Private Sub datagrid_KeyDown(ByVal sender As System.Object, ByVal e As

System.Windows.Forms.KeyEventArgs) Handles datagrid.KeyDown
Try
If e.Control And (e.KeyCode = Keys.C) Then
Dim d As DataObject = datagrid.GetClipboardContent()
Clipboard.SetDataObject(d)
e.Handled = True
ElseIf (e.Control And e.KeyCode = Keys.V) Then
PasteUnboundRecords()
End If
Catch ex As Exception
'Log Exception
End Try
End Sub

Private Sub PasteUnboundRecords()

Try
Dim rowLines As String() = Clipboard.GetText(TextDataFormat.Text).Split(New String(0) {vbCr &
vbLf}, StringSplitOptions.None)
Dim currentRowIndex As Integer = (If(datagrid.CurrentRow IsNot Nothing,
datagrid.CurrentRow.Index, 0))
Dim currentColumnIndex As Integer = (If(datagrid.CurrentCell IsNot Nothing,
datagrid.CurrentCell.ColumnIndex, 0))
Dim currentColumnCount As Integer = datagrid.Columns.Count

datagrid.AllowUserToAddRows = False
For rowLine As Integer = 0 To rowLines.Length - 1

If rowLine = rowLines.Length - 1 AndAlso String.IsNullOrEmpty(rowLines(rowLine)) Then

Exit For
End If

Dim columnsData As String() = rowLines(rowLine).Split(New String(0) {vbTab},

StringSplitOptions.None)
If (currentColumnIndex + columnsData.Length) > datagrid.Columns.Count Then
For columnCreationCounter As Integer = 0 To ((currentColumnIndex + columnsData.Length) -
currentColumnCount) - 1
If columnCreationCounter = rowLines.Length - 1 Then
Exit For
End If
Next
End If
If datagrid.Rows.Count > (currentRowIndex + rowLine) Then
For columnsDataIndex As Integer = 0 To columnsData.Length - 1
If currentColumnIndex + columnsDataIndex <= datagrid.Columns.Count - 1 Then
datagrid.Rows(currentRowIndex + rowLine).Cells(currentColumnIndex +
columnsDataIndex).Value = columnsData(columnsDataIndex)
End If
Next
Else
Dim pasteCells As String() = New String(datagrid.Columns.Count - 1) {}
For cellStartCounter As Integer = currentColumnIndex To datagrid.Columns.Count - 1
If columnsData.Length > (cellStartCounter - currentColumnIndex) Then
pasteCells(cellStartCounter) = columnsData(cellStartCounter - currentColumnIndex)
End If
Next
End If
Next
Catch ex As Exception
'Log Exception
End Try

End Sub
The following sample shows how to
use GemBox.Spreadsheet.WindowsFormUtilities.dll to import or export ExcelFile to
the Windows.Forms DataGridView control in C# and VB.NET.
Note that not only data is imported or exported from ExcelWorksheet into a DataGridView control
but also hyperlinks, formatting, images, and more.
Input file: WinFormsUtilitiesSample.xlsx

Screenshot

See the full code below.

Copy

C#
VB.NET
1Imports GemBox.Spreadsheet
2Imports GemBox.Spreadsheet.WinFormsUtilities
3
4Public Class Form1
5
6 Public Sub New()
7 SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")
8
9 InitializeComponent()
10 End Sub
11

12 Private Sub btnLoadFile_Click(sender As System.Object, e As System.EventArgs)

Handles btnLoadFile.Click
13
14 Dim openFileDialog = New OpenFileDialog()
15 openFileDialog.Filter = "XLS files (*.xls, *.xlt)|*.xls;*.xlt|XLSX files
(*.xlsx, *.xlsm, *.xltx, *.xltm)|*.xlsx;*.xlsm;*.xltx;*.xltm|ODS files (*.ods,
*.ots)|*.ods;*.ots|CSV files (*.csv, *.tsv)|*.csv;*.tsv|HTML files (*.html,
*.htm)|*.html;*.htm"
16 openFileDialog.FilterIndex = 2
17
18 If (openFileDialog.ShowDialog() = DialogResult.OK) Then
19 Dim ef = ExcelFile.Load(openFileDialog.FileName)
20
21 ' Export Excel worksheet to DataGridView control.
22
DataGridViewConverter.ExportToDataGridView(ef.Worksheets.ActiveWorksheet,
Me.dataGridView1, New ExportToDataGridViewOptions() With {.ColumnHeaders = True})
23 End If
24
25 End Sub
26

27 Private Sub btnSave_Click(sender As System.Object, e As System.EventArgs)

Handles btnSave.Click
28
29 Dim saveFileDialog = New SaveFileDialog()
30 saveFileDialog.Filter = "XLS files (*.xls)|*.xls|XLT files
(*.xlt)|*.xlt|XLSX files (*.xlsx)|*.xlsx|XLSM files (*.xlsm)|*.xlsm|XLTX
(*.xltx)|*.xltx|XLTM (*.xltm)|*.xltm|ODS (*.ods)|*.ods|OTS (*.ots)|*.ots|CSV
(*.csv)|*.csv|TSV (*.tsv)|*.tsv|HTML (*.html)|*.html|MHTML (.mhtml)|*.mhtml|PDF
(*.pdf)|*.pdf|XPS (*.xps)|*.xps|BMP (*.bmp)|*.bmp|GIF (*.gif)|*.gif|JPEG
(*.jpg)|*.jpg|PNG (*.png)|*.png|TIFF (*.tif)|*.tif|WMP (*.wdp)|*.wdp"
31 saveFileDialog.FilterIndex = 3
32
33 If (saveFileDialog.ShowDialog() = DialogResult.OK) Then
34 Dim ef = New ExcelFile()
35 Dim ws = ef.Worksheets.Add("Sheet1")
36
37 ' Import DataGridView control to Excel worksheet.
38 DataGridViewConverter.ImportFromDataGridView(ws, Me.dataGridView1, New
ImportFromDataGridViewOptions() With {.ColumnHeaders = True})
39
40 ef.Save(saveFileDialog.FileName)
41 End If
42
43 End Sub
44End Class

Menghapus data grid dengan loop

Dim table As DataTable = dataSet1.Table(0)

2
3 If dataGridView1.SelectedRows.Count > 0 Then
4 Dim item As DataGridViewRow
5 For Each item In dataGridView1.SelectedRows
6 table.Rows.Find(CType(item.Cells(0).Value, Integer)).Delete()
7 Next
8 End If

Hapus datagrid dengan klik kanan

Delete from datagridview by Right click

There are many ways you can right click to select a row in a
Datagridview and show a menu to delete it. Here we are using
Datagridview CellMouseUp event to select row and
contextMenuStrip1_Click event to delete the row from datagridview
in vb.net.

Select row in a dataGridView by Right click

First you should drag a contextMenuStrip from your toolbox to your
form. Then you create a contextMenuStrip item "Delete Row" .
How can I select a row in datagridview when i press right clcik

When press the right button, first thing is to find the selected row
index and then show the contextMenuStrip. From the event
CellMouseUp of dataGridView to indentify the row index and show
the menu item. A global variable rowIndex is assigned the row
index value for later delete this row.

If e.Button = MouseButtons.Right Then

Me.DataGridView1.Rows(e.RowIndex).Selected = True
Me.rowIndex = e.RowIndex
Me.DataGridView1.CurrentCell = Me.DataGridView1.Rows(e.RowIndex).Cells(1)
Me.ContextMenuStrip1.Show(Me.DataGridView1, e.Location)
ContextMenuStrip1.Show(Cursor.Position)
End If

We can delete the selected row in the contextMenuStrip1_Click

event by using the global rowIdex value.

Next : DataGridView Autocomplete TextBox in VB.Net

Download Source Code

Print Source Code

Public Class Form1

Private rowIndex As Integer = 0

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As

System.EventArgs) Handles MyBase.Load
Dim dt As New DataTable()
dt.Columns.Add("Id", GetType(Integer))
dt.Columns.Add("Publisher Name", GetType(String))
dt.Columns.Add("Book", GetType(String))

For i As Integer = 1 To 10
dt.Rows.Add(i, "PubName" & i, "Book" & i)
Next
 DataGridView1.DataSource = dt
Me.DataGridView1.RowsDefaultCellStyle.BackColor = Color.Bisque
Me.DataGridView1.AlternatingRowsDefaultCellStyle.BackColor =
Color.Beige
End Sub

Private Sub DataGridView1_CellMouseUp_1(ByVal sender As System.Object,

ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles
DataGridView1.CellMouseUp
If e.Button = MouseButtons.Right Then
Me.DataGridView1.Rows(e.RowIndex).Selected = True
Me.rowIndex = e.RowIndex
Me.DataGridView1.CurrentCell =
Me.DataGridView1.Rows(e.RowIndex).Cells(1)
Me.ContextMenuStrip1.Show(Me.DataGridView1, e.Location)
ContextMenuStrip1.Show(Cursor.Position)
End If
End Sub

Private Sub ContextMenuStrip1_Click(ByVal sender As System.Object, ByVal

e As System.EventArgs) Handles ContextMenuStrip1.Click
If Not Me.DataGridView1.Rows(Me.rowIndex).IsNewRow Then
Me.DataGridView1.Rows.RemoveAt(Me.rowIndex)
End If
End Sub
End Class
Filter datagrid

DataGridView Sorting/Filtering in VB.NET

The DataGridView control provides a customizable table for
displaying data. You can extend the DataGridView control in a
number of ways to build custom behaviors into your applications. A
DataView provides a means to filter and sort data within a
DataTable. The following vb.net program shows how to filter and
sort a DataGridView by using a DataView Object.
How to sort Datagridview - vb.net
The DataGridView control in VB.Net provides automatic sorting, so
that you can sort any column in the datagridview control. You can
sort the data in ascending or descending order based on the
contents of the specified column of sort() method.

DataGridView1.Sort(DataGridView1.Columns(1), ListSortDirection.Ascending)
In the above vb.net code , datagridview sort the title column.

How to filter Datagridview - vb.net

You can use different methods to filter datagridview column . You
can sort data while fetching it from database using order by clause
in SQL statement or you can use the following method.

Dim dv As DataView
dv = New DataView(ds.Tables(0), "type = 'business' ", "type Desc",
DataViewRowState.CurrentRows)
DataGridView1.DataSource = dv
In the above vb.net code, datagridview is filter the column Type and
the column value is Business.

Next : DataGridView adding rows and columns in VB.NET

Download Source Code

Print Source Code

Imports System.Data.SqlClient
Imports System.ComponentModel
Public Class Form1
Dim ds As New DataSet
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim connectionString As String = "Data Source=.;Initial
Catalog=pubs;Integrated Security=True"
Dim sql As String = "SELECT title_id,title,type,pub_id FROM Titles"
Dim connection As New SqlConnection(connectionString)
Dim dataadapter As New SqlDataAdapter(sql, connection)
connection.Open()
dataadapter.Fill(ds, "Titles_table")
connection.Close()
 DataGridView1.DataSource = ds.Tables(0)
End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As

System.EventArgs) Handles Button2.Click
Dim dv As DataView
dv = New DataView(ds.Tables(0), "type = 'business' ", "type Desc",
DataViewRowState.CurrentRows)
DataGridView1.DataSource = dv
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As

System.EventArgs) Handles Button1.Click
DataGridView1.Sort(DataGridView1.Columns(1),
ListSortDirection.Ascending)
End Sub
End Class
NO otomatis

Sub NORAC()

'--------panggil koneksi yang ada di module----------------

BukaKoneksi()
'----------------------------------------------------------

cmd = New OleDbCommand("Select * from IQC where No_laporan in (select

max(no_laporan) from iqc)", conn)
Dim hitung As Long
Dim urutan As String
RD = cmd.ExecuteReader
RD.Read()
If Not RD.HasRows Then
urutan = "001" + "/" + "IQC" + "/" + Format(Now, "ddMMyy")
Else
If Microsoft.VisualBasic.Right(RD.GetString(2), 6) <> Format(Now, "ddMMyy")
Then
urutan = "001" + "/" + "IQC" + "/" + Format(Now, "ddMMyy")
Else
hitung = Microsoft.VisualBasic.Left(RD.GetString(2), 3) + 1
urutan = Microsoft.VisualBasic.Left("00" & hitung, 3) + "/" + "IQC" + "/"
+ Format(Now, "ddMMyy")
End If
End If
TextBox10.Text = urutan
End Sub

Sub Periksa()

'--------panggil koneksi yang ada di module----------------

BukaKoneksi()
 '----------------------------------------------------------

cmd = New OleDbCommand("Select * from IQC where No_periksa in (select

max(no_periksa) from iqc)", conn)
Dim hitung As Long
Dim urutan As String
RD = cmd.ExecuteReader
RD.Read()
If Not RD.HasRows Then
urutan = "001" + Format(Now, "ddMMyy")
Else
If Microsoft.VisualBasic.Right(RD.GetString(2), 6) <> Format(Now, "ddMMyy")
Then
urutan = "001" + Format(Now, "ddMMyy")
Else
hitung = Microsoft.VisualBasic.Left(RD.GetString(2), 3) + 1
urutan = Microsoft.VisualBasic.Left("00" & hitung, 3) + Format(Now,
"ddMMyy")
End If
End If
TextBox11.Text = urutan
End Sub
This is a tutorial on how to export data in datagridview to an excel sheet in
visual basic .net
In this tutorial, first I populate Access table into a datagridview, then I export to
my excel sheet (I use Excel 2007)
For this tutorial, you need to place a datagridview control and a button to your
form

Result:
First add a reference to Microsoft Excel 12.0 Object Library
In your project menu click on Project - Add Reference - go to COM tab

Add Microsoft Excel 12.0 Object Library

Go to your code page and add the following references above your Public
Class Form1 Line
?
1 Imports System.Data.OleDb
2 Imports Excel = Microsoft.Office.Interop.Excel
Then add the following declarations below your Public Class Form1 line
?
1 'Change "C:\Users\Jimmy\Documents\Merchandise.accdb" to your database location
2 Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data
3 Source=C:\Users\Jimmy\Desktop\test.accdb"
4 'Change "C:\Users\Jimmy\Desktop\test.xlsx" to your excel file location
5 Dim excelLocation As String = "C:\Users\Jimmy\Desktop\test.xlsx"
Dim MyConn As OleDbConnection
6
Dim da As OleDbDataAdapter
7 Dim ds As DataSet
8 Dim tables As DataTableCollection
9 Dim source1 As New BindingSource
10 Dim APP As New Excel.Application
11 Dim worksheet As Excel.Worksheet
Dim workbook As Excel.Workbook
12
Add the following form load event code:
?
1
Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
2 workbook = APP.Workbooks.Open(excelLocation)
3 worksheet = workbook.Worksheets("sheet1")
4 MyConn = New OleDbConnection
5 MyConn.ConnectionString = connString
6 ds = New DataSet
tables = ds.Tables
7
da = New OleDbDataAdapter("Select * from [Items]", MyConn) 'Change items to your
8 database name
9 da.Fill(ds, "Items") 'Change items to your database name
10 Dim view As New DataView(tables(0))
11 source1.DataSource = view
12 DataGridView1.DataSource = view
DataGridView1.AllowUserToAddRows = False
13 End Sub
14
Then add the following code that will handle your Export button click event
?
1 Private Sub Export_Click(sender As System.Object, e As System.EventArgs) Handles Export.Cl
2 'Export Header Names Start
Dim columnsCount As Integer = DataGridView1.Columns.Count
3 For Each column In DataGridView1.Columns
4 worksheet.Cells(1, column.Index + 1).Value = column.Name
5 Next
6 'Export Header Name End
7
8
9 'Export Each Row Start
For i As Integer = 0 To DataGridView1.Rows.Count - 1
10 Dim columnIndex As Integer = 0
11 Do Until columnIndex = columnsCount
12 worksheet.Cells(i + 2, columnIndex + 1).Value =
13 DataGridView1.Item(columnIndex, i).Value.ToString
14 columnIndex += 1
Loop
15 Next
16 'Export Each Row End
17 End Sub
18
19
Then add the following code to save your excel file when your close your form
?
1 Private Sub Form1_FormClosed(sender As Object, e As System.Windows.Forms.FormClosedEventA
2 Handles Me.FormClosed
workbook.Save()
3 workbook.Close()
4 APP.Quit()
5 End Sub

Imports System.Data.SqlClient
Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

Dim connectionString As String = "Data Source=.;Initial

Catalog=pubs;Integrated Security=True"
Dim sql As String = "SELECT * FROM Authors"
Dim connection As New SqlConnection(connectionString)
Dim dataadapter As New SqlDataAdapter(sql, connection)
Dim ds As New DataSet()
connection.Open()
dataadapter.Fill(ds, "Authors_table")
connection.Close()
DataGridView1.DataSource = ds
DataGridView1.DataMember = "Authors_table"

End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As

System.EventArgs) Handles Button2.Click
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim misValue As Object = System.Reflection.Missing.Value

Dim i As Int16, j As Int16

xlApp = New Excel.ApplicationClass

xlWorkBook = xlApp.Workbooks.Add(misValue)
xlWorkSheet = xlWorkBook.Sheets("sheet1")

For i = 0 To DataGridView1.RowCount - 2
For j = 0 To DataGridView1.ColumnCount - 1
 xlWorkSheet.Cells(i + 1, j + 1) = DataGridView1(j,
i).Value.ToString()
Next
Next

xlWorkBook.SaveAs("c:\vb.net-informations.xls",
Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue,
_
Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue,
misValue, misValue)
xlWorkBook.Close(True, misValue, misValue)
xlApp.Quit()

releaseObject(xlWorkSheet)
releaseObject(xlWorkBook)
releaseObject(xlApp)

MessageBox.Show("Over")
End Sub

Private Sub releaseObject(ByVal obj As Object)

Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
obj = Nothing
Catch ex As Exception
obj = Nothing
MessageBox.Show("Exception Occured while releasing object " +
ex.ToString())
Finally
GC.Collect()
End Try
End Sub

End Class