Export DataGridView To CSV,TSV,HTML,EXCEL 2010 | DASAR PROGRAMER

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 

Jika Anda menyukai Artikel di blog ini, Silahkan klik disini untuk berlangganan gratis via email, dengan begitu Anda akan mendapat kiriman artikel setiap ada artikel yang terbit di dasarprogrammer.blogspot.com

0 komentar:

Posting Komentar