Export DataGridView To CSV,TSV,HTML,EXCEL 2010

Halo Sobat semua... pada kesempatan kali ini saya akan share tentang cara export datagridview ke file. Nah, saya akan contohkan export ke CSV (Comma Separated Values), TSV (Tab Separated Values), HTML (Hyper Text Markup Language) dan Microsoft Excel 2010. Lihat gambar dibawah ini, ini adalah data Empolyee dari database Northwind pada datagridview.

Untuk function-nya saya sudah siapkan (khusus untuk Visual Basic .NET)

1.Export To CSV

Public Function DataGridViewToCSV(ByVal pDGview As DataGridView, ByVal pFullPath As String) As Boolean

        Try

            If pDGview.RowCount = 0 Then Return False

            Dim varSeparator As String = "," 'comma

            Dim varText As String

            Dim varTargetFile As IO.StreamWriter

            varTargetFile = New IO.StreamWriter(pFullPath, False)

            With varTargetFile

                'save header

                varText = ""

                For Each column As DataGridViewColumn In pDGview.Columns

                    varText = varText + column.HeaderText + varSeparator

                Next

                varText = Mid(varText, 1, varText.Length - 1)

                .Write(varText)

                .WriteLine()

                'save data

                For Each row As DataGridViewRow In pDGview.Rows

                    varText = ""

                    For i As Integer = 0 To pDGview.ColumnCount - 1

                        varText = varText + IIf(IsDBNull(row.Cells(i).Value) = True, varSeparator, row.Cells(i).Value.ToString + varSeparator)

                    Next

                    varText = Mid(varText, 1, varText.Length - 1)

                    .Write(varText)

                    .WriteLine()

                Next

                .Close()

            End With

            Return True

        Catch ex As Exception

            MessageBox.Show(ex.Message & vbCrLf & ex.Source, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

            Return False

        End Try

    End Function

2.Export To TSV

Public Function DataGridViewToTSV(ByVal pDGview As DataGridView, ByVal pFullPath As String) As Boolean

        Try

            If pDGview.RowCount = 0 Then Return False

            Dim varSeparator As String = Chr(9) 'tab

            Dim varText As String

            Dim varTargetFile As IO.StreamWriter

            varTargetFile = New IO.StreamWriter(pFullPath, False)

            With varTargetFile

                'save header

                varText = ""

                For Each column As DataGridViewColumn In pDGview.Columns

                    varText = varText + column.HeaderText + varSeparator

                Next

                varText = Mid(varText, 1, varText.Length - 1)

                .Write(varText)

                .WriteLine()

                'save data

                For Each row As DataGridViewRow In pDGview.Rows

                    varText = ""

                    For i As Integer = 0 To pDGview.ColumnCount - 1

                        varText = varText + IIf(IsDBNull(row.Cells(i).Value) = True, varSeparator, row.Cells(i).Value.ToString + varSeparator)

                    Next

                    varText = Mid(varText, 1, varText.Length - 1)

                    .Write(varText)

                    .WriteLine()

                Next

                .Close()

            End With

            Return True

        Catch ex As Exception

            MessageBox.Show(ex.Message & vbCrLf & ex.Source, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

            Return False

        End Try

    End Function

3.Export To HTML

Untuk function Export To HTML, Click HERE

4.Export To MS.EXCEL 2010

Sebelumnya tambahkan terlebih dahulu Reference Microsoft.Office.Interop.Excel (sudah tahu kan cara add reference...)

Setelah itu tambahkan di baris paling atas code editor :

Imports Excel = Microsoft.Office.Interop.Excel

Nah selanjutnya ini dia function-nya :

Public Function funcDataGridViewToExcel(ByVal pDGView As DataGridView, ByVal pPathFile As String) As Boolean

        Try

            If pDGView.RowCount = 0 Then Return False

            Dim varExcelApp As Excel.Application

            Dim varExcelWorkBook As Excel.Workbook

            Dim varExcelWorkSheet As Excel.Worksheet

            Dim misValue As Object = System.Reflection.Missing.Value

            varExcelApp = New Excel.ApplicationClass

            varExcelWorkBook = varExcelApp.Workbooks.Add(misValue)

            varExcelWorkSheet = varExcelWorkBook.Sheets("sheet1")

            'add header

            For i As Integer = 0 To pDGView.ColumnCount - 1

                varExcelWorkSheet.Cells(1, i + 1) = pDGView.Columns(i).HeaderText

            Next

            'add data

            For i As Integer = 0 To pDGView.RowCount - 1

                For j As Integer = 0 To pDGView.ColumnCount - 1

                    varExcelWorkSheet.Cells(i + 2, j + 1) = IIf(IsDBNull(pDGView.Rows(i).Cells(j).Value.ToString()) = True, "", pDGView.Rows(i).Cells(j).Value.ToString())

                Next

            Next

            varExcelWorkSheet.SaveAs(pPathFile)

            varExcelWorkBook.Close()

            varExcelApp.Quit()

            releaseObject(varExcelApp)

            releaseObject(varExcelWorkBook)

            releaseObject(varExcelWorkSheet)

            Return True

        Catch ex As Exception

            MessageBox.Show(ex.Message & vbCrLf & ex.Source, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

            Return False

        End Try

    End Function

    Private Sub releaseObject(ByVal obj As Object)

        Try

            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)

            obj = Nothing

        Catch ex As Exception

            obj = Nothing

        Finally

            GC.Collect()

        End Try

    End Sub

Semoga artikel ini bermanfaat... sampai jumpa lagi di posting selanjutnya.. Rajin rajin mampir kesini ya, Thanks !

Tags:
save datagridview as , export datagridview to excel, export datagridview to csv , export datagridview to tsv, export datagridview to html,  export datagridview to xml, comma separated values, tab separated values, hyper text markup language, extensible markup language, vb net, c#, example, source code, save datagrid to    mecrosoft excel, function, data, save datagridview to file, txt, text, datagridview to excel, to tsv, to csv, to html, to xml