Export Data DataGridView ke Excel VB.NET

Belajar Vb.NET tutorial Pemula : cara mudah export data dari datagridview (dari database) ke Excel 2010, excel 2012, 2013 dan 2016 menggunakan vb.net, tutorial vb.net selengkapnya di sector code

Export data Vb.Net - Cara mudah untuk menampilkan data dari database MySQL ke DataGridView kemudian bisa diexport ke excel, Biodata dalam jumlah banyak yang sudah tampil dalam datagridview vb.net bisa kita export kedalam format excel hanya buth waktu beberapa detik saja, bagi kamu yang belum tau cara untuk menampilkan data dari database silahkan lihat postingan kami sebelumnya tentang CRUD VB.NET (Create,Read,Update,Delete) Database SQL Server, dalam tutorial ini merupakan lanjutan dari pembahasan aplikasi CRUD di atas, hanya saja kita perlu menambah sebuah button untuk tombol export datanya. simak terus tutorialnya ya.

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 :
Export Data DataGridView ke Excel VB.NET
Sebelum menuliskan coding export to excel, silahkan tambahkan references Microsoft.Office.Interop.Excel ke dalam project kamu, caranya klik kanan pada References > add references > pilih COM dan centang Microsoft Excel 16.0 Object Library dan OK.

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 _
        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
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 :
Export Data DataGridView ke Excel VB.NET

Jika kamu kurang paham dengan source code diatas boleh bertanya pada kotak komentar dibawah ini. dan jangan lupa share jika bermanfaat, terima kasih :)

COMMENTS

Next Recomended


Feel free to code it up and send us a pull request.

Hi everyone, let's me know how much this lesson can help your work. Please Subscribe and Follow Our Social Media 'kodeajaib[dot]com' to get Latest tutorials and will be send to your email everyday for free!, Just hit a comment if you have confused. Nice to meet you and Happy coding :) all ^^



Follow by E-Mail


Name

ADO.NET,3,Ajax,6,Android,9,AngularJS,4,ASP.NET,4,Blogger Tutorials,7,Bootstrap,7,C++,1,Codeigniter,2,Cplusplus,6,Crystal Report,6,CSharp,25,Ebook Java,2,FlyExam,1,FSharp,3,Game Development,2,Java,35,JDBC,2,Laravel,84,Lumen,2,MariaDB,2,Ms Access,3,MySQL,31,ODBC,6,OleDB,1,PHP,14,PHP Framework,5,PHP MYSQLI,9,PHP OOP,5,Python,8,Python 3,4,SQL Server,4,SQLite,4,Uncategorized,5,Vb 6,2,Vb.Net,89,Video,48,Vue Js,4,WPF,2,Yii,3,
ltr
item
KODE AJAIB: Export Data DataGridView ke Excel VB.NET
Export Data DataGridView ke Excel VB.NET
Belajar Vb.NET tutorial Pemula : cara mudah export data dari datagridview (dari database) ke Excel 2010, excel 2012, 2013 dan 2016 menggunakan vb.net, tutorial vb.net selengkapnya di sector code
http://2.bp.blogspot.com/-5_x4gs81Z5g/VkoIEaLkDjI/AAAAAAAABMc/17AheDPjEsM/s320/export-data-to-exel.jpg
http://2.bp.blogspot.com/-5_x4gs81Z5g/VkoIEaLkDjI/AAAAAAAABMc/17AheDPjEsM/s72-c/export-data-to-exel.jpg
KODE AJAIB
https://www.kodeajaib.com/2015/11/export-data-datagridview-ke-excel-vbnet.html
https://www.kodeajaib.com/
https://www.kodeajaib.com/
https://www.kodeajaib.com/2015/11/export-data-datagridview-ke-excel-vbnet.html
true
3214704946184383982
UTF-8
Loaded All Posts Not found any posts VIEW ALL Readmore Reply Cancel reply Delete By Home PAGES POSTS View All RECOMMENDED FOR YOU LABEL ARCHIVE SEARCH ALL POSTS Not found any post match with your request Back Home Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sun Mon Tue Wed Thu Fri Sat January February March April May June July August September October November December Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec just now 1 minute ago $$1$$ minutes ago 1 hour ago $$1$$ hours ago Yesterday $$1$$ days ago $$1$$ weeks ago more than 5 weeks ago Followers Follow THIS CONTENT IS PREMIUM Please share to unlock Copy All Code Select All Code All codes were copied to your clipboard Can not copy the codes / texts, please press [CTRL]+[C] (or CMD+C with Mac) to copy