Export data ke Excel
Buka project CRUD kamu yang sudah dibahas minggu kemaren, jadi tinggal tambah button untuk exportnya saja, kira - kira tampilannya seperti gambar berikut :
langsung saja kita berpusing ria, import name space di atas class frm1.vb
Imports System.IO
Functions Export Data
Private Sub SendtoExcel(ByVal Data As DataGridView)
Dim CreateData As New StreamWriter(NamaFile, False)
With CreateData
.WriteLine("<?xml version=""1.0""?>")
.WriteLine("<?mso-application " _
+ "progid=""Excel.Sheet""?>")
.WriteLine("<Workbook " _
+ "xmlns=""urn:schemas-microsoft-com:office:spreadsheet"">")
.WriteLine("<Styles>")
.WriteLine("<Style ss:ID=""header"">")
.WriteLine("<Alignment ss:Horizontal=""Center""/>")
.WriteLine("<Borders>")
.WriteLine("<Border ss:Position=""Left"" " _
+ "ss:LineStyle=""Continuous"" ss:Weight=""1""/>")
.WriteLine("<Border ss:Position=""Bottom"" " _
+ "ss:LineStyle=""Continuous"" ss:Weight=""1""/>")
.WriteLine("<Border ss:Position=""Right"" " _
+ "ss:LineStyle=""Continuous"" ss:Weight=""1""/>")
.WriteLine("<Border ss:Position=""Top"" " _
+ "ss:LineStyle=""Continuous"" ss:Weight=""1""/>")
.WriteLine("</Borders>")
.WriteLine("<Font ss:FontName=""Calibri"" " _
+ "ss:Size=""11"" ss:Bold=""1""/>")
.WriteLine("<Interior ss:Color=""#eff4ff"" " _
+ "ss:Pattern=""Solid""/>")
.WriteLine("</Style>")
.WriteLine("<Style ss:ID=""head"">")
.WriteLine("<Alignment ss:Vertical=""Bottom""/>")
.WriteLine("<Borders/>")
.WriteLine("<Font ss:FontName=""Calibri""/>")
.WriteLine("</Style>")
.WriteLine("<Style ss:ID=""data"">")
.WriteLine("<Borders>")
.WriteLine("<Border ss:Position=""Bottom"" " _
+ " ss:LineStyle=""Continuous"" ss:Weight=""1""/>")
.WriteLine("<Border ss:Position=""Left"" " _
+ "ss:LineStyle=""Continuous"" ss:Weight=""1""/>")
.WriteLine("<Border ss:Position=""Right"" " _
+ "ss:LineStyle=""Continuous"" ss:Weight=""1""/>")
.WriteLine("<Border ss:Position=""Top"" " _
+ "ss:LineStyle=""Continuous"" ss:Weight=""1""/>")
.WriteLine("</Borders>")
.WriteLine("<Font ss:FontName=""Calibri"" " _
+ "ss:Size=""11"" ss:Color=""#000000""/>")
.WriteLine("</Style>")
.WriteLine("</Styles>")
If Data.Name = "DataGrid" Then
.WriteLine("<Worksheet ss:Name=""DataGrid"">")
.WriteLine("<Table>")
.WriteLine("<Column ss:Width=""20""/>")
.WriteLine("<Column ss:Width=""93""/>")
.WriteLine("<Column ss:Width=""84""/>")
.WriteLine("<Column ss:Width=""100""/>")
.WriteLine("<Column ss:Width=""84""/>")
End If
.WriteLine("<Row ss:StyleID=""head"">")
For x As Integer = 0 To Data.Columns.Count - 1
Application.DoEvents()
.WriteLine("<Cell ss:StyleID=""header"">")
.WriteLine("<Data ss:Type=""String"">{0}</Data>",
Data.Columns.Item(x).HeaderText)
.WriteLine("</Cell>")
Next
.WriteLine("</Row>")
For KolomBaris As Integer = 0 To Data.RowCount - 1
Application.DoEvents()
.WriteLine("<Row ss:StyleID=""head"" " _
+ "ss:utoFitHeight =""0"">")
For kolomData As Integer = 0 To Data.Columns.Count - 1
Application.DoEvents()
.WriteLine("<Cell ss:StyleID=""data"">")
.WriteLine("<Data ss:Type=""String"">{0}</Data>",
Data.Item(kolomData, KolomBaris).Value.ToString)
.WriteLine("</Cell>")
Next
.WriteLine("</Row>")
Next
.WriteLine("</Table>")
.WriteLine("</Worksheet>")
.WriteLine("</Workbook>")
.Close()
End With
End Sub
Panggil Export Data
klik 2x pada tombol export to excel dan tuliskan code berikut :Private Sub Button5_Click(sender _Penjelasan
As Object, e As EventArgs) _
Handles Button5.Click
If DataGridView1.RowCount = 0 _
Then Exit Sub
Button5.Text = "Exporting Data ..."
Button5.Enabled = False
Application.DoEvents()
Dim DataGridViewVb As _
New DataGridView
With DataGridViewVb
.AllowUserToAddRows = False
.Name = "DataGrid"
.Visible = False
.Columns.Clear()
.Columns.Add(0, "ID")
.Columns.Add(1, "Nama")
.Columns.Add(2, "N I S N")
.Columns.Add(3, "Kelas")
.Columns.Add(4, "Alamat")
End With
With DataGridView1
If .Rows.Count > 0 Then
For x As Integer = 0 _
To .Rows.Count - 1
Application.DoEvents()
DataGridViewVb.Rows.Add(.Rows(x).Cells(0).Value,
.Rows(x).Cells(1).Value, .Rows(x).Cells(2).Value,
.Rows(x).Cells(3).Value, .Rows(x).Cells(4).Value)
Next
End If
End With
NamaFile = Application.StartupPath & "\BackUp " _
& Now.Day & "-" & Now.Month & "-" & Now.Year & ".xls"
If File.Exists(NamaFile) Then File.Delete(NamaFile)
SendtoExcel(DataGridViewVb)
DataGridViewVb.Dispose()
DataGridViewVb = Nothing
Process.Start(Application.StartupPath & "\BackUp " _
& Now.Day & "-" & Now.Month & "-" & Now.Year & ".xls")
Button5.Text = "Export to Exel"
Button5.Enabled = True
End Sub
Penjelasan dari aplikasi diatas : ketika data sudah ditampilkan dalam datagridview silahkan klik tombol export to excel, aplikasi akan memanggil fungsi sendtoexcel() dan membuat sebuah file dengan nama "Backup tanggalhariini-bulansekarang-tahunsekarang.xls" pada folder bin project visual basic net kamu. jika nama file sudah ada dalam folder tersebut maka file lama akan dihapus, selanjutnya aplikasi akan langsung membuka file yang barusan di export dengan Excel.
berikut penampakan data setelah di export kedalam excel :
Jika kamu kurang paham dengan source code diatas boleh bertanya pada kotak komentar dibawah ini. dan jangan lupa share jika bermanfaat, terima kasih :)