1

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. columns1 This is the order of the CONVERTED datagridview. columns2 This is the order of the EXPORTED datagridview to excel. columns3

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 :)

1 Answer 1

1

Try changing the values in the dt with the values you want during convert instead of changing the datagridview and make that dt with the converted value your dgv datasource. I think the export gets the values inside the dt and not the datagridview.

Sign up to request clarification or add additional context in comments.

1 Comment

Your suggestion is late but yes, it does work like that. I figured it out and now it works perfectly. Still, thanks for the answer. :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.