Export Data from Grid to Excel File Using DataSet

Dim loSavefd As New SaveFileDialog
Dim loResult As New DialogResult
Dim lsFilePath As String = String.Empty
Dim lsAssessment As String = String.Empty
Dim lsFileName As String = String.Empty
Dim lsCharacter As String = "/\*:?<>~|'"""
Dim liData As Integer
Dim lbOpen As Boolean = False
Dim liMergeAss, liMergeAss2 As Integer
Dim liCount, liCount2 As Integer
Dim loXlApp As New Excel.Application
Dim loXlWorkBook As Excel.Workbook = loXlApp.Workbooks.Add
Dim loXlWorkSheet As Excel.Worksheet = loXlWorkBook.ActiveSheet()

Try
liMergeAss = 3
liMergeAss2 = 3
liCount = 0
liCount2 = 0

Dim frmMsgBx As New Form
Dim lblMsgText As New System.Windows.Forms.Label
Dim btnMsgOpen, btnMsgSave, btnMsgCancel As New System.Windows.Forms.Button

With frmMsgBx
.StartPosition = FormStartPosition.CenterScreen
.MinimizeBox = False
.MaximizeBox = False
.Width = 380
.Height = 150
.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedToolWindow
.ShowIcon = False
.ShowInTaskbar = False
.Text = "Exporting to Excel"
End With

With lblMsgText
.Parent = frmMsgBx
.Left = 30
.Top = 30
.Width = 300
.Height = 13
.Text = "Would you like to open the file or save it to your computer?"
.TextAlign = System.Drawing.ContentAlignment.MiddleCenter
End With

With btnMsgOpen
.Parent = frmMsgBx
.Left = 50
.Top = 80
.Width = 52
.Height = 23
.Text = "Open"
.DialogResult = System.Windows.Forms.DialogResult.OK
End With

With btnMsgSave
.Parent = frmMsgBx
.Left = 110
.Top = 80
.Width = 73
.Height = 23
.Text = "Save"
.DialogResult = System.Windows.Forms.DialogResult.Yes
End With

With btnMsgCancel
.Parent = frmMsgBx
.Left = 250
.Top = 80
.Width = 75
.Height = 23
.Text = "Cancel"
.DialogResult = System.Windows.Forms.DialogResult.Cancel
End With

frmMsgBx.ShowDialog()
lsFileName = txtCompItemId.Text.Trim.Replace("&", "and")
If lsFileName.Substring(0, 1).Equals(".") Then
lsFileName = "Dot " & lsFileName.Remove(0, 1)
End If

For liCtr As Integer = 1 To Len(lsCharacter)
lsFileName = Replace(lsFileName, Mid(lsCharacter, liCtr, 1), " ")
Next liCtr

If frmMsgBx.DialogResult = System.Windows.Forms.DialogResult.Yes Then
With loSavefd
.Filter = "Excel Files|*.xls"
.Title = "Download to Excel File"
.FileName = lsFileName
.ValidateNames = True
loResult = .ShowDialog()
lsFilePath = loSavefd.FileName
End With

loSavefd.Dispose()
End If

If loResult = System.Windows.Forms.DialogResult.OK OrElse frmMsgBx.DialogResult = System.Windows.Forms.DialogResult.OK OrElse frmMsgBx.DialogResult = System.Windows.Forms.DialogResult.Yes Then
If lsFileName.Length > 30 Then
loXlWorkSheet.Name = lsFileName.Substring(0, 30)
Else
loXlWorkSheet.Name = lsFileName
End If

'add the title
With loXlWorkSheet.Cells(1, 1)
.Font.Name = "Arial"
.Font.Bold = True
.Font.ColorIndex = 2
.Font.Size = 18
.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
End With
loXlWorkSheet.Range(loXlWorkSheet.Cells(1, 1), loXlWorkSheet.Cells(1, 4)).Merge()
loXlWorkSheet.Cells(1, 1).Value = txtCompItemId.Text.Trim
loXlWorkSheet.Range(loXlWorkSheet.Cells(1, 1), loXlWorkSheet.Cells(1, 1)).Interior.ColorIndex = 37

'add the definition competency item
With loXlWorkSheet.Cells(2, 1)
.Font.Name = "Arial"
.Font.Bold = True
.VerticalAlignment = XlVAlign.xlVAlignJustify
loXlWorkSheet.Cells(2, 1).Value = "Definition"
End With

With loXlWorkSheet.Cells(2, 2)
.RowHeight = 80
.Font.Italic = True
.Font.Name = "Arial"
.WrapText = True
.VerticalAlignment = XlVAlign.xlVAlignTop
loXlWorkSheet.Cells(2, 2).Value = txtDefinition.Text.Trim
End With
loXlWorkSheet.Range(loXlWorkSheet.Cells(2, 2), loXlWorkSheet.Cells(2, 4)).Merge()

SwapDataSet()

'add the headers table column
Dim i As Integer = moPniDataSetSwap.Tables(0).Columns.Count - 1
For c As Integer = 0 To i
With loXlWorkSheet.Cells(3, c + 1)
.Borders.Weight = XlBorderWeight.xlMedium
.Font.Bold = True
.Font.Size = 12
.WrapText = True
.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
.VerticalAlignment = XlVAlign.xlVAlignTop
loXlWorkSheet.Cells(3, c + 1).Value = moPniDataSetSwap.Tables(0).Columns(c).ColumnName
End With
Next
loXlWorkSheet.Range(loXlWorkSheet.Cells(3, 1), loXlWorkSheet.Cells(3, i + 1)).Interior.ColorIndex = 34

'add the values table
liData = moPniDataSetSwap.Tables(0).Rows.Count - 1

For r As Integer = 0 To moPniDataSetSwap.Tables(0).Rows.Count - 1
'column 1
With loXlWorkSheet.Cells(r + 4, 1)
.VerticalAlignment = XlVAlign.xlVAlignTop
.ColumnWidth = 15
.WrapText = True
.Font.Bold = True
.Borders.Weight = XlBorderWeight.xlThin
If moPniDataSetSwap.Tables(0).Rows(liData)(0).ToString.Trim.Equals("") Then
liCount = liCount + 1
lsAssessment = moPniDataSetSwap.Tables(0).Rows(liData)(0).ToString.Trim
loXlWorkSheet.Range(loXlWorkSheet.Cells(liMergeAss, 1), loXlWorkSheet.Cells(liMergeAss + liCount, 1)).Merge()
Else
liMergeAss = liMergeAss + liCount + 1
liCount = 0
lsAssessment = moPniDataSetSwap.Tables(0).Rows(liData)(0).ToString.Trim
loXlWorkSheet.Cells(r + 4, 1).Value = lsAssessment
End If
End With

'column 2
With loXlWorkSheet.Cells(r + 4, 2)
.VerticalAlignment = XlVAlign.xlVAlignTop
.ColumnWidth = 40
.WrapText = True
.Borders.Weight = XlBorderWeight.xlThin
If moPniDataSetSwap.Tables(0).Rows(liData)(1).ToString.Trim.Equals("") AndAlso lsAssessment.Equals("") Then
liCount2 = liCount2 + 1
loXlWorkSheet.Range(loXlWorkSheet.Cells(liMergeAss2, 2), loXlWorkSheet.Cells(liMergeAss2 + liCount2, 2)).Merge()
Else
liMergeAss2 = liMergeAss2 + liCount2 + 1
liCount2 = 0
loXlWorkSheet.Cells(r + 4, 2).Value = moPniDataSetSwap.Tables(0).Rows(liData)(1).ToString.Replace(";", "").Trim
End If
End With

'column 3,4
For c As Integer = 2 To i
With loXlWorkSheet.Cells(r + 4, c + 1)
.VerticalAlignment = XlVAlign.xlVAlignTop
.ColumnWidth = 40
.WrapText = True
.Borders.Weight = XlBorderWeight.xlThin
loXlWorkSheet.Cells(r + 4, c + 1).Value = moPniDataSetSwap.Tables(0).Rows(liData)(c).ToString.Replace(";", "").Trim
End With
Next
liData -= 1
Next
liCount = loXlWorkBook.Sheets.Count

For no As Integer = liCount To 2 Step -1
loXlWorkSheet = loXlWorkBook.Sheets.Item(no)
loXlWorkSheet.Delete()
Next

If frmMsgBx.DialogResult = System.Windows.Forms.DialogResult.OK Then
lbOpen = True
loXlApp.Visible = True
loXlWorkBook.Activate()
ElseIf loResult = System.Windows.Forms.DialogResult.OK Then
loXlApp.DisplayAlerts = False
loXlWorkBook.SaveAs(lsFilePath)
loXlWorkBook.Close(True)
loXlApp.DisplayAlerts = True
loXlApp.Quit()
MsgBox("Download Complete", MsgBoxStyle.Information, "Information")
End If
frmMsgBx.Dispose()
End If
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Information")
Finally
moPniDataSetSwap.Clear()
Runtime.InteropServices.Marshal.ReleaseComObject(loXlApp)
Runtime.InteropServices.Marshal.ReleaseComObject(loXlWorkBook)
Runtime.InteropServices.Marshal.ReleaseComObject(loXlWorkSheet)
loXlApp = Nothing
loXlWorkBook = Nothing
loXlWorkSheet = Nothing
End Try