I have a datagridview filled with values from an imported excel file. I change the headertext in the datagridview into another value and reorder them, and then export them again into an excel file. What's happening is I get the values that are changed but not the order (the order is still the order of the imported excel file). How can I import the values and the order of my datagridview? Refer to the code below:
'Code for Import
Private Sub btnImport_Click(sender As System.Object, e As System.EventArgs) Handles btnImport.Click
Dim result As DialogResult = OpenFileDialog1.ShowDialog()
Dim path As String = OpenFileDialog1.FileName
Me.TextBox1.Text = path.ToString
Try
Me.dgvFile.DataSource = Nothing
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim DtSet As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & Me.TextBox1.Text & "';Extended Properties=Excel 8.0;")
MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection)
MyCommand.TableMappings.Add("Table", "Net-informations.com")
DtSet = New System.Data.DataSet
MyCommand.Fill(DtSet)
Me.dgvFile.DataSource = DtSet.Tables(0)
MyConnection.Close()
MessageBox.Show("File successfully imported")
Catch ex As Exception
MessageBox.Show("Error")
End Try
End Sub
'Code for Convert
Private Sub btnConvert_Click(sender As System.Object, e As System.EventArgs) Handles btnConvert.Click
MysqlConn = New MySqlConnection
MysqlConn.ConnectionString = "server=localhost;userid=root;password=NewPass;database=converter"
Dim MysqlReader As MySqlDataReader
'Convert for Headers
MysqlConn.Open()
Dim MysqlQuery As String
MysqlQuery = "SELECT * FROM headers"
MysqlComm = New MySqlCommand(MysqlQuery, MysqlConn)
MysqlReader = MysqlComm.ExecuteReader
Dim y = 0
Dim arrayContain As New List(Of String)
Dim arrayRemove As New List(Of String)
'Dim colIndex
While MysqlReader.Read
Dim header = MysqlReader.GetString("Header")
Dim convert = MysqlReader.GetString("Convert")
Dim strHeader = System.Convert.ToString(header)
Dim strConvert = System.Convert.ToString(convert)
Dim x = 0
For Each column As DataGridViewColumn In dgvFile.Columns
If column.HeaderText = strHeader Then
column.HeaderText = strConvert
dgvFile.Columns(x).DisplayIndex = y
y = y + 1
arrayContain.Add(column.HeaderText)
Else
x = x + 1
End If
Next
End While
For Each remove As DataGridViewColumn In dgvFile.Columns
If arrayContain.Contains(remove.HeaderText) = False Then
arrayRemove.Add(remove.HeaderText)
End If
Next
For count As Integer = 0 To arrayRemove.Count - 1
dgvFile.Columns.Remove(arrayRemove(count))
Next
MysqlComm.Dispose()
MysqlReader.Close()
MysqlConn.Close()
'Code for Export
Private Sub btnExport_Click(sender As System.Object, e As System.EventArgs) Handles btnExport.Click
Dim xlApp As Microsoft.Office.Interop.Excel.Application
Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
Dim misValue As Object = System.Reflection.Missing.Value
Dim sPath As String = String.Empty
Dim dlgSave As New SaveFileDialog
Dim i As Integer
Dim j As Integer
dlgSave.DefaultExt = "xlsx"
dlgSave.Filter = "Microsoft Excel|*.xlsx"
If dlgSave.ShowDialog = System.Windows.Forms.DialogResult.OK Then
xlApp = New Microsoft.Office.Interop.Excel.Application
xlWorkBook = xlApp.Workbooks.Add(misValue)
xlWorkSheet = xlWorkBook.Sheets("sheet1")
For i = 0 To dgvFile.RowCount - 1
For j = 0 To dgvFile.ColumnCount - 1
For k As Integer = 1 To dgvFile.Columns.Count
xlWorkSheet.Cells(1, k) = dgvFile.Columns(k - 1).HeaderText
xlWorkSheet.Cells(i + 2, j + 1) = "'" & dgvFile(j, i).Value.ToString()
Next
Next
Next
Dim sFileName As String = dlgSave.FileName
'Dim finalFilename As String = sFileName & ".xlsx"
xlWorkSheet.SaveAs(sFileName)
xlWorkBook.Close()
xlApp.Quit()
releaseObject(xlApp)
releaseObject(xlWorkBook)
releaseObject(xlWorkSheet)
Dim res As MsgBoxResult
res = MsgBox("Process completed, Would you like to open file?", MsgBoxStyle.YesNo)
If (res = MsgBoxResult.Yes) Then
Process.Start(sFileName)
End If
End If
End Sub
'releaseObject Code
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
EDIT
This is the order of the IMPORTED excel into datagridview.
This is the order of the CONVERTED datagridview.
This is the order of the EXPORTED datagridview to excel.

You will notice that the IMPORTED and the EXPORTED have the same order. I want the EXPORTED to be the same order as the CONVERTED. I hope it's clear now and I hope someone can help me fix this. Thanks :)