How to Export Data to Excel Using VB.net and MS Access Database

Submitted by: 
Visitors have accessed this post 1087 times.

Hi guys..

In this tutorial you will learn on how to export data to Microsoft Excel step by step.

Create U.I
11Step 1
Create a database using Microsoft access
1Step 2
Connect Database to VB.net
2345678Step 3
Add References

right Click then select properties shown on number 13
910Here is the source code

  1. Imports Excel = Microsoft.Office.Interop.Excel
  2. Imports System.Data.OleDb
  3. Public Class frmgenerate
  4. Dim rdr As OleDbDataReader = Nothing
  5. Dim dtable As DataTable
  6. Dim con As OleDbConnection = Nothing
  7. Dim adp As OleDbDataAdapter
  8. Dim ds As DataSet
  9. Dim cmd As OleDbCommand = Nothing
  10. Dim dt As New DataTable
  11. Dim cs As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\GenerallPayroll.accdb;Persist Security Info=False;"
  12. Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
  13. Try
  14. con = New OleDbConnection(cs)
  15. con.Open()
  16. cmd = New OleDbCommand("select (EmployeeName) as [Employee Name], (AmountWithdrawn) as [Amount Withdrawn], (SCCC) as [SC], (OrigSalary) as [OR Salary], (NetPay) as [Net], (NameOfRecepient) as [Name of Recepient], (RTOB) as [Relationship to borrower], (Signature) as [Signature], (DueDate) as [Due Date], (RMKS) as [Remarks] from genpayfinal order by EmployeeName,DateofOrigin ", con)
  17. Dim myDA As OleDbDataAdapter = New OleDbDataAdapter(cmd)
  18.  
  19. Dim myDataSet As DataSet = New DataSet()
  20.  
  21. myDA.Fill(myDataSet, "GenPayFinal")
  22.  
  23. DataGridView3.DataSource = myDataSet.Tables("GenPayFinal").DefaultView
  24.  
  25.  
  26.  
  27. con.Close()
  28. Catch ex As Exception
  29. MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
  30. End Try
  31. End Sub
  32.  
  33. Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  34.  
  35. End Sub
  36.  
  37. Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
  38. DataGridView3.DataSource = Nothing
  39. End Sub
  40.  
  41. Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
  42. If DataGridView3.RowCount = Nothing Then
  43. MessageBox.Show("Sorry nothing to export into excel sheet.." & vbCrLf & "Please retrieve data in datagridview", "", MessageBoxButtons.OK, MessageBoxIcon.Error)
  44. Exit Sub
  45. End If
  46. Dim rowsTotal, colsTotal As Short
  47. Dim I, j, iC As Short
  48. System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
  49. Dim xlApp As New Excel.Application
  50.  
  51. Try
  52. Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
  53. Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
  54. xlApp.Visible = True
  55.  
  56. rowsTotal = DataGridView3.RowCount - 1
  57. colsTotal = DataGridView3.Columns.Count - 1
  58. With excelWorksheet
  59. .Cells.Select()
  60. .Cells.Delete()
  61. For iC = 0 To colsTotal
  62. .Cells(1, iC + 1).Value = DataGridView3.Columns(iC).HeaderText
  63. Next
  64. For I = 0 To rowsTotal - 1
  65. For j = 0 To colsTotal
  66. .Cells(I + 2, j + 1).value = DataGridView3.Rows(I).Cells(j).Value
  67. Next j
  68. Next I
  69. .Rows("1:1").Font.FontStyle = "Bold"
  70. .Rows("1:1").Font.Size = 12
  71. .Cells.Columns.AutoFit()
  72. .Cells.Select()
  73. .Cells.EntireColumn.AutoFit()
  74. .Cells(1, 1).Select()
  75.  
  76. End With
  77. Catch ex As Exception
  78. MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
  79. Finally
  80. 'RELEASE ALLOACTED RESOURCES
  81. System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
  82. xlApp = Nothing
  83. End Try
  84. End Sub
  85.  
  86. Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
  87.  
  88.  
  89. End Sub
  90.  
  91. Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
  92.  
  93. End Sub
  94.  
  95. Private Sub btnOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOK.Click
  96. Application.Exit()
  97.  
  98. End Sub
  99. End Class

Tnt Number : 09096510899
Globe : 09454339345
gmail : [email protected]
Facebook : Clyde Chectopher A. Tiu


Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. After downloading it, you will need a program like Winzip to decompress it.

Virus note: All files are scanned once-a-day by SourceCodester.com for viruses, but new viruses come out every day, so no prevention program can catch 100% of them.

FOR YOUR OWN SAFETY, PLEASE:

1. Re-scan downloaded files using your personal virus checker before using it.
2. NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.

Comments

wow awesome nice great

Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • You may insert videos with [video:URL]
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <th> <img> <h1> <h2> <h3> <iframe> [video]
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <asp>, <c>, <cpp>, <csharp>, <css>, <html4strict>, <java>, <javascript>, <mysql>, <php>, <python>, <sql>, <vb>, <vbnet>. The supported tag styles are: <foo>, [foo].
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.