How to export only the visible columns from a datatable.

[mrbill65]

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

Continue reading...