locked
How to export only the visible columns from a datatable. RRS feed

  • Question

  • If I set the visible columns through a column chooser dialog on a grid when I export from the datatable even the hidden columns export.  Does anyone have sample code on how to accomplish only exporting the data with the visible columns?  This is my current code for exporting.  I keep looking for a "visible column" type parameter but can't seem to find one.  Any help would be appreciated.  The DoIds section will include the guid fields when exporting.

    Thanks in advance.

        Private Sub cmdExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdExport.Click
            Dim sTempFile As String
            Dim DoShell As Boolean = False
            Try
                sTempFile = "c:\export.csv"
                SaveFileDialog1.AddExtension = True
                SaveFileDialog1.DefaultExt = "csv"
                SaveFileDialog1.CheckPathExists = True
                SaveFileDialog1.CreatePrompt = False
                SaveFileDialog1.FileName = sTempFile
                SaveFileDialog1.Title = "Save Export File As ..."
                SaveFileDialog1.ValidateNames = True
                SaveFileDialog1.ShowHelp = False
                SaveFileDialog1.OverwritePrompt = True
                SaveFileDialog1.InitialDirectory = "C:\"
                SaveFileDialog1.Filter = "Comma Delimited Files (*.csv)|*.csv"
                If Not SaveFileDialog1.ShowDialog() = Windows.Forms.DialogResult.Cancel Then
                    sTempFile = SaveFileDialog1.FileName
                    If Not oDataTable Is Nothing Then
                        If oDataTable.Rows.Count > 0 Then
                            If Me.uGridMain.Rows.VisibleRowCount > 0 Then
                                ExportData(Me.oDataTable, True, , sTempFile)
                                DoShell = True
                            End If
                        Else
                            If Not DataSet Is Nothing Then
                                If DataSet.Tables.Count > 0 Then
                                    ExportData(Me.DataSet.Tables(0), True, , sTempFile)
                                    DoShell = True
                                End If
                            End If
                        End If
                    Else
                        If Not DataSet Is Nothing Then
                            If DataSet.Tables.Count > 0 Then
                                ExportData(Me.DataSet.Tables(0), True, , sTempFile)
                                DoShell = True
                            End If
                        End If
                    End If
                    If DoShell Then
                        ShellFile(sTempFile)
                    End If

                End If
            Catch ex As Exception
                HandleError(ex, "BaseSearchForm", "cmdExport_Click")
            End Try
        End Sub

      Public Function ExportData(ByVal oTable As DataTable,
        Optional ByVal PrintColumnHeaders As Boolean = True,
        Optional ByVal FieldDelim As String = """,""",
        Optional ByVal TempFile As String = Nothing) As String
            Dim oRow As DataRow = Nothing
            Dim oCol As DataColumn = Nothing
            Dim sCSV As String = ""
            Dim loFileInfo As FileInfo
            Dim loStreamWriter As StreamWriter
            Dim DoIds As Boolean
            Dim dlgresult As DialogResult
            Dim FirstCol As Boolean = True
            Try
                dlgresult = MessageBox.Show("Include Identifier (ID) fields?", "Export Format", MessageBoxButtons.YesNo, MessageBoxIcon.Asterisk, MessageBoxDefaultButton.Button2)
                DoIds = dlgresult = DialogResult.Yes
                sCSV = ""
                'export the column headers
                If PrintColumnHeaders = True Then
                    If DoIds Then
                        For Each oCol In oTable.Columns
                            If sCSV = "" Then
                                sCSV = sCSV & """" & oCol.ColumnName.ToString()
                            Else
                                sCSV = sCSV & FieldDelim & oCol.ColumnName.ToString()
                            End If
                        Next
                    Else
                        For Each oCol In oTable.Columns
                            'pdb - v8.8.0 - 05/14/2018 - Added  AndAlso Not oCol.ColumnName.EndsWith("id") to handle the "rowguid" field.
                            If Not oCol.ColumnName.EndsWith("Id") AndAlso Not oCol.ColumnName.EndsWith("id") Then
                                If sCSV = "" Then
                                    sCSV = sCSV & """" & oCol.ColumnName.ToString()
                                Else
                                    sCSV = sCSV & FieldDelim & oCol.ColumnName.ToString()
                                End If
                            End If
                        Next
                    End If
                End If
                sCSV = sCSV & """" & Environment.NewLine
                'export the rows
                For Each oRow In oTable.Rows
                    FirstCol = True
                    If DoIds Then
                        For Each oCol In oTable.Columns
                            If FirstCol Then
                                sCSV = sCSV & """" & oRow.Item(oCol.ColumnName).ToString.Replace(Chr(10), " ").Replace(Chr(13), " ").Replace(Chr(10) & Chr(13), " ").Replace(Chr(13) & Chr(10), " ").Replace(vbCrLf, " ")
                                FirstCol = False
                            Else
                                sCSV = sCSV & FieldDelim & oRow.Item(oCol.ColumnName).ToString.Replace(Chr(10), " ").Replace(Chr(13), " ").Replace(Chr(10) & Chr(13), " ").Replace(Chr(13) & Chr(10), " ").Replace(vbCrLf, " ")
                            End If
                        Next
                    Else
                        For Each oCol In oTable.Columns
                            If Not oCol.ColumnName.EndsWith("Id") AndAlso Not oCol.ColumnName.EndsWith("id") Then
                                If FirstCol Then
                                    sCSV = sCSV & """" & oRow.Item(oCol.ColumnName).ToString.Replace(Chr(10), " ").Replace(Chr(13), " ").Replace(Chr(10) & Chr(13), " ").Replace(Chr(13) & Chr(10), " ").Replace(vbCrLf, " ")
                                    FirstCol = False
                                Else
                                    sCSV = sCSV & FieldDelim & oRow.Item(oCol.ColumnName).ToString.Replace(Chr(10), " ").Replace(Chr(13), " ").Replace(Chr(10) & Chr(13), " ").Replace(Chr(13) & Chr(10), " ").Replace(vbCrLf, " ")
                                End If
                            End If
                        Next
                    End If
                    sCSV = sCSV & """" & Environment.NewLine
                Next
                'write to a temp file if specified
                If Not TempFile Is Nothing Then
                    'get instance of a fileinfo object for the path specified
                    loFileInfo = New FileInfo(TempFile)
                    'write the error to the error log
                    loStreamWriter = File.CreateText(TempFile)
                    loStreamWriter.Write(sCSV.ToCharArray)
                    loStreamWriter.Close()
                End If
                ExportData = sCSV
            Catch ex As Exception
                HandleError(ex, "ExportData")
            Finally
                If Not oRow Is Nothing Then oRow = Nothing
                If Not oCol Is Nothing Then oCol.Dispose()
            End Try
        End Function

    Saturday, April 27, 2019 6:18 PM

Answers

  • Hello,

    If a column is meant to be hidden then set it's ColumnMapping property to Hidden then when exporting use assertion to see if a column is marked as hidden via If someRow.ColumnMapping <> MappingType.Hidden. So if not Hidden use the column and if hidden don't use the column.

    We can go deeper if need be, just let me know.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Proposed as answer by Alex-KSGZ Monday, April 29, 2019 7:19 AM
    • Marked as answer by mrbill65 Friday, September 6, 2019 4:06 PM
    Sunday, April 28, 2019 7:22 PM

All replies

  • Hello,

    If a column is meant to be hidden then set it's ColumnMapping property to Hidden then when exporting use assertion to see if a column is marked as hidden via If someRow.ColumnMapping <> MappingType.Hidden. So if not Hidden use the column and if hidden don't use the column.

    We can go deeper if need be, just let me know.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Proposed as answer by Alex-KSGZ Monday, April 29, 2019 7:19 AM
    • Marked as answer by mrbill65 Friday, September 6, 2019 4:06 PM
    Sunday, April 28, 2019 7:22 PM
  • Hi,

    You can create a new Datatable and then use your mothod.

    Public Class Form1
        Dim dt1 As New DataTable
        Dim dt2 As New DataTable
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            dt1.Columns.Add(New DataColumn With {.ColumnName = "A", .ColumnMapping = MappingType.Hidden})
            dt1.Columns.Add(New DataColumn With {.ColumnName = "B"})
            dt1.Columns.Add(New DataColumn With {.ColumnName = "C", .ColumnMapping = MappingType.Hidden})
            dt1.Columns.Add(New DataColumn With {.ColumnName = "D"})
            dt1.Rows.Add(New Object() {"1", "2", "3", "4"})
            dt1.Rows.Add(New Object() {"4", "5", "6", "7"})
            dt1.Rows.Add(New Object() {"7", "8", "9", "10"})
    
            dt2 = dt1
            Dim j = 0
            For i = 0 To dt2.Columns.Count - 1
                If dt2.Columns(j).ColumnMapping = MappingType.Hidden Then
                    dt2.Columns.RemoveAt(j)
                    j = j - 1
                End If
                j = j + 1
            Next
    
        End Sub
    End Class

    Best Regards,

    Alex



    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, April 29, 2019 7:31 AM