Forums

Overview » VB .NET » How to Import Data to Excel via DataGridView with VB.NET
Reply

How to Import Data to Excel via DataGridView with VB.NET

Yaths Ho
Member



Since: 08 Apr 2012
Posts: 87
Posted 07 Aug 2012 09:53:52

In order to get useful data and display it, we may need to import data to Excel from Database. So, I will share a method to import data to Excel through DataGridView with VB.NET and the VB .NET Excel component, Spire.XLS for .NET is used for realizing this function more quickly and easily.

Firstly, design the form. Add DataGridView and Run button in this form. Then, double click button to use the following code to import data.

Imports System
Imports System.Data
Imports System.Windows.Forms
Imports Spire.Xls

		Private Sub button1_Click(ByVal sender As Object, ByVal e As EventArgs)
			Dim workbook As New Workbook()

			'Initialize worksheet
			workbook.CreateEmptySheets(1)
			Dim sheet As Worksheet = workbook.Worksheets(0)

			'Insert DataTable to Excel
			sheet.InsertDataTable(CType(Me.dataGridView1.DataSource, DataTable), True, 2, 1, -1, -1)

			'Set Excel Style
			Dim Style As CellStyle = workbook.Styles.Add("Style")
			Style.Borders(BordersLineType.EdgeLeft).LineStyle = LineStyleType.Thin
			Style.Borders(BordersLineType.EdgeRight).LineStyle = LineStyleType.Thin
			Style.Borders(BordersLineType.EdgeTop).LineStyle = LineStyleType.Thin
			Style.Borders(BordersLineType.EdgeBottom).LineStyle = LineStyleType.Thin
			Style.Borders.Color = Color.DarkCyan
			Style.Color = Color.Lavender
			Style.Font.FontName = "Calibri"
			Style.Font.Size = 12
			Dim range As CellRange = sheet.Range("A3:F26")
			range.CellStyleName = Style.Name

			'Set Header Style
			Dim styleHeader As CellStyle = sheet.Rows(0).Style
			styleHeader.Borders(BordersLineType.EdgeLeft).LineStyle = LineStyleType.Thin
			styleHeader.Borders(BordersLineType.EdgeRight).LineStyle = LineStyleType.Thin
			styleHeader.Borders(BordersLineType.EdgeTop).LineStyle = LineStyleType.Thin
			styleHeader.Borders(BordersLineType.EdgeBottom).LineStyle = LineStyleType.Thin
			styleHeader.Borders.Color = Color.DarkCyan
			styleHeader.VerticalAlignment = VerticalAlignType.Center
			styleHeader.HorizontalAlignment = HorizontalAlignType.Center
			styleHeader.KnownColor = ExcelColors.Cyan
			styleHeader.Font.FontName = "Calibri"
			styleHeader.Font.Size = 14
			styleHeader.Font.IsBold = True

			'Set Row Height and Column Width
			sheet.AllocatedRange.AutoFitColumns()
			sheet.AllocatedRange.AutoFitRows()
			sheet.Range("A3:F26").RowHeight = 16
			sheet.Rows(0).RowHeight = 20

			'Save and Launch File
			workbook.SaveToFile("DataImport.xlsx", ExcelVersion.Version2010)
			System.Diagnostics.Process.Start(workbook.FileName)
		End Sub

		Private Sub Form1_Load_1(ByVal sender As Object, ByVal e As EventArgs)
			'Load Data from Database to DataGridView
			Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & ControlChars.CrLf & "                                Data Source=D:\work\VIP.mdb;Persist Security Info=False;"
			Dim dataTable As New DataTable()
			Using conn As New OleDbConnection(connString)
				conn.Open()
				Dim sql As String = "select Name,Gender,Birthday,Email,Number,Country from VIP"
				Dim dataAdapter As New OleDbDataAdapter(sql, conn)
				dataAdapter.Fill(dataTable)
			End Using
			Me.dataGridView1.DataSource = dataTable
		End Sub

In order to get useful data and display it, we may need to import data to Excel from Database. So, I will share a method to import data to Excel through DataGridView with VB.NET and the VB .NET Excel component, Spire.XLS for .NET is used for realizing this function more quickly and easily.

Firstly, design the form. Add DataGridView and Run button in this form. Then, double click button to use the following code to import data.

Imports System
Imports System.Data
Imports System.Windows.Forms
Imports Spire.Xls

		Private Sub button1_Click(ByVal sender As Object, ByVal e As EventArgs)
			Dim workbook As New Workbook()

			'Initialize worksheet
			workbook.CreateEmptySheets(1)
			Dim sheet As Worksheet = workbook.Worksheets(0)

			'Insert DataTable to Excel
			sheet.InsertDataTable(CType(Me.dataGridView1.DataSource, DataTable), True, 2, 1, -1, -1)

			'Set Excel Style
			Dim Style As CellStyle = workbook.Styles.Add("Style")
			Style.Borders(BordersLineType.EdgeLeft).LineStyle = LineStyleType.Thin
			Style.Borders(BordersLineType.EdgeRight).LineStyle = LineStyleType.Thin
			Style.Borders(BordersLineType.EdgeTop).LineStyle = LineStyleType.Thin
			Style.Borders(BordersLineType.EdgeBottom).LineStyle = LineStyleType.Thin
			Style.Borders.Color = Color.DarkCyan
			Style.Color = Color.Lavender
			Style.Font.FontName = "Calibri"
			Style.Font.Size = 12
			Dim range As CellRange = sheet.Range("A3:F26")
			range.CellStyleName = Style.Name

			'Set Header Style
			Dim styleHeader As CellStyle = sheet.Rows(0).Style
			styleHeader.Borders(BordersLineType.EdgeLeft).LineStyle = LineStyleType.Thin
			styleHeader.Borders(BordersLineType.EdgeRight).LineStyle = LineStyleType.Thin
			styleHeader.Borders(BordersLineType.EdgeTop).LineStyle = LineStyleType.Thin
			styleHeader.Borders(BordersLineType.EdgeBottom).LineStyle = LineStyleType.Thin
			styleHeader.Borders.Color = Color.DarkCyan
			styleHeader.VerticalAlignment = VerticalAlignType.Center
			styleHeader.HorizontalAlignment = HorizontalAlignType.Center
			styleHeader.KnownColor = ExcelColors.Cyan
			styleHeader.Font.FontName = "Calibri"
			styleHeader.Font.Size = 14
			styleHeader.Font.IsBold = True

			'Set Row Height and Column Width
			sheet.AllocatedRange.AutoFitColumns()
			sheet.AllocatedRange.AutoFitRows()
			sheet.Range("A3:F26").RowHeight = 16
			sheet.Rows(0).RowHeight = 20

			'Save and Launch File
			workbook.SaveToFile("DataImport.xlsx", ExcelVersion.Version2010)
			System.Diagnostics.Process.Start(workbook.FileName)
		End Sub

		Private Sub Form1_Load_1(ByVal sender As Object, ByVal e As EventArgs)
			'Load Data from Database to DataGridView
			Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & ControlChars.CrLf & "                                Data Source=D:\work\VIP.mdb;Persist Security Info=False;"
			Dim dataTable As New DataTable()
			Using conn As New OleDbConnection(connString)
				conn.Open()
				Dim sql As String = "select Name,Gender,Birthday,Email,Number,Country from VIP"
				Dim dataAdapter As New OleDbDataAdapter(sql, conn)
				dataAdapter.Fill(dataTable)
			End Using
			Me.dataGridView1.DataSource = dataTable
		End Sub

Reply to this topic

Message
Reply
Follow us on twitter Subscribe to our RSS feed
Activate your free membership today | Login | Currency