asked on
VB.Net Export DataGridView to CSV but with Current Column Order
Hi,
I have created the following code to export a datagridview to CSV, however the user is able to reorder the column position.
For example original layout is:
ColA, ColB, ColC
User changes the column orders:
ColC, ColA, ColB
But export to CSV is still in the original column order:
ColA, ColB, ColC
How do I export the datagridview to CSV as it looks on screen?
Thanks, Greg
I have created the following code to export a datagridview to CSV, however the user is able to reorder the column position.
For example original layout is:
ColA, ColB, ColC
User changes the column orders:
ColC, ColA, ColB
But export to CSV is still in the original column order:
ColA, ColB, ColC
How do I export the datagridview to CSV as it looks on screen?
Thanks, Greg
Private Sub exportCSV(ByRef sExportFileName As String)
Try
Dim sCSV As New List(Of String)
' get column headers
Dim sColHeaders As String = ""
For Each col As DataGridViewColumn In dgv.Columns
If col.Visible = True Then
Dim sCol As String = ""
sCol = col.HeaderText.ToString
If sCol.Contains(",") Then
sCol = """" + sCol + """"
End If
sColHeaders = sColHeaders + sCol + ","
End If
Next
sColHeaders = sColHeaders.Substring(0, sColHeaders.Length - 1)
' get row cell values
For Each row As DataGridViewRow In dgv.Rows
Dim sRow As String = ""
For Each cell As DataGridViewCell In row.Cells
If cell.Visible = True Then
Dim sValue As String = ""
If Not cell.Value Is Nothing Then
sValue = cell.Value.ToString
Else
sValue = ""
End If
If sValue.ToString.Contains(",") Then
sValue = """" + sValue + """"
End If
If sValue.ToString.ToUpper = "TRUE" Then
sValue = "Yes"
End If
If sValue.ToString.ToUpper = "FALSE" Then
sValue = "No"
End If
sRow = sRow + sValue + ","
End If
Next
sRow = sRow.Substring(0, sRow.Length - 1)
sCSV.Add(sRow)
Next
' write data to csv file
Using sw As New IO.StreamWriter(sExportFileName, False, System.Text.Encoding.UTF8)
sw.WriteLine(sColHeaders.ToString)
For Each s As String In sCSV
sw.WriteLine(s.ToString)
Next
End Using
If MessageBox.Show("CSV file created, open CSV file?", "Export Complete", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = vbYes Then
Process.Start(sExportFileName)
End If
Catch ex As Exception
MessageBox.Show(ex.ToString, "CSV Export Failed", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
Visual Basic.NET.NET Programming
Last Comment
Naitik Gamit
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks for your response, would you be able to provide an example?
ASKER
Hi,
I have now managed this by changing my code to the following after Naitik Gamit pointed me in the correct direction, thanks Naitik.
I have now managed this by changing my code to the following after Naitik Gamit pointed me in the correct direction, thanks Naitik.
Private Sub exportCSV(ByRef sExportFileName As String)
Try
Dim columnsInOrder As New List(Of DataGridViewColumn)
For Each col As DataGridViewColumn In dgv.Columns
columnsInOrder.Add(col)
Next
columnsInOrder = columnsInOrder.OrderBy(Function(c) c.DisplayIndex).ToList()
Dim sCSVExportLines As New List(Of String)
Dim sHeaderText As String = ""
For Each col As DataGridViewColumn In columnsInOrder
If col.Visible = True Then
Dim sValue As String = ""
sValue = col.HeaderText.ToString
If sValue.Contains(",") Then
sValue = """" + sValue + """"
End If
sHeaderText = sHeaderText + sValue + ","
End If
Next
sHeaderText = sHeaderText.Substring(0, sHeaderText.Length - 1)
If Not String.IsNullOrEmpty(sHeaderText) Then
sCSVExportLines.Add(sHeaderText)
End If
' get row values
For Each row As DataGridViewRow In dgv.Rows
Dim sRowValue As String = ""
For Each col As DataGridViewColumn In columnsInOrder
If col.Visible = True Then
Dim sValue As String = ""
sValue = row.Cells(col.Index).Value.ToString
If Not String.IsNullOrEmpty(sValue) Then
If sValue.ToString.Contains(",") Then
sValue = """" + sValue + """"
End If
If sValue.ToString.ToUpper = "TRUE" Then
sValue = "Yes"
End If
If sValue.ToString.ToUpper = "FALSE" Then
sValue = "No"
End If
Else
sValue = ""
End If
sRowValue = sRowValue + sValue + ","
End If
Next
sRowValue = sRowValue.Substring(0, sRowValue.Length - 1)
If Not String.IsNullOrEmpty(sRowValue) Then
sCSVExportLines.Add(sRowValue)
End If
Next
' write data to csv file
Using sw As New IO.StreamWriter(sExportFileName, False, System.Text.Encoding.UTF8)
For Each s As String In sCSVExportLines
sw.WriteLine(s.ToString)
Next
End Using
If MessageBox.Show("CSV file created, open CSV file?", "Export Complete", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = vbYes Then
Process.Start(sExportFileName)
End If
Catch ex As Exception
MessageBox.Show(ex.ToString, "CSV Export Failed", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
You welcome.