DataGridView Export to XLS File

Project -> Brown -> add -> COM Microsoft Excel 11.0 Object Library

Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Data
Imports System.Data.SqlClient
Imports System.Windows.Forms
Imports System.Data.OleDb
Imports System.Text
Imports System.Runtime.InteropServices

Public Class Form1

Const con As String = “Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;Persist Security Info=False”

Const SQLExpression As String = “SELECT CustomerID AS ID, CompanyName AS Company,City, Region, Country FROM Customers”

Dim adp As SqlDataAdapter
Dim ds As DataSet

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles MyBase.Load
Dim cn As New SqlConnection(con)
Dim cmd As New SqlCommand(SQLExpression, cn)
cmd.CommandType = CommandType.Text
cn.Open()
adp = New SqlDataAdapter(cmd)
Dim builder As SqlCommandBuilder = New SqlCommandBuilder(adp)
ds = New DataSet
adp.Fill(ds, “XL”)
Me.DataGridView1.DataSource = ds.Tables(“XL”).DefaultView
cn.Close()
cn = Nothing
End Sub

Private Sub Update_Btn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles Update_Btn.Click
With Me
.Validate()
.adp.Update(Me.ds.Tables(“XL”))
.ds.AcceptChanges()
End With
End Sub

Private Sub Close_Btn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles Close_Btn.Click
ds.Dispose()
adp.Dispose()
ds = Nothing
adp = Nothing
Me.Close()
End Sub

Private Sub Export_Btn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles Export_Btn.Click

Dim RowsCount As Int32 = Me.DataGridView1.SelectedRows.Count – 1

If RowsCount > -1 Then
‘The array for field names.
Dim FldNames() As String = {“ID”, “Company”, “City”, “Region”, “Country”}
‘The array for the selected records.
Dim DataArr(RowsCount, 4) As Object
Dim ColsCounter As Int32 = 0

‘Populate the data array – The list is sorted in ascending order.
For RowsCounter As Int32 = 0 To RowsCount
For Each cell As DataGridViewCell In Me.DataGridView1 _
.SelectedRows(RowsCount – RowsCounter) _
.Cells
DataArr(RowsCounter, ColsCounter) = cell.Value
ColsCounter = ColsCounter + 1
Next
ColsCounter = 0
Next

‘Variables for Excel.
Dim xlApp As New Excel.Application
Dim xlWBook As Excel.Workbook = xlApp.Workbooks.Add( _
Excel.XlWBATemplate.xlWBATWorksheet)
Dim xlWSheet As Excel.Worksheet = CType(xlWBook.Worksheets(1), Excel.Worksheet)
Dim xlCalc As Excel.XlCalculation

‘Save the present setting for Excel’s calculation mode and turn it off.
With xlApp
xlCalc = .Calculation
.Calculation = Excel.XlCalculation.xlCalculationManual
End With

‘Write the field names and the data to the targeting worksheet.
With xlWSheet
.Range(.Cells(1, 1), .Cells(1, 5)).Value = FldNames
.Range(.Cells(2, 1), .Cells(RowsCount + 2, 5)).Value = DataArr
.UsedRange.Columns.AutoFit()
End With

With xlApp
.Visible = True
.UserControl = True
‘Restore the calculation mode.
.Calculation = xlCalc
End With

‘Relase objects from memory.
xlWSheet = Nothing
xlWBook = Nothing
xlApp = Nothing
GC.Collect()
End If

End Sub
End Class