如何将datagridview的确切值导出到excelVB中-HowtoexporttheexactvaluesofthedatagridviewintoexcelVB

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:

我有一个从导入的excel文件中填充值的datagridview。我将datagridview中的headertext更改为另一个值并重新排序，然后再次将它们导出为excel文件。所发生的情况是，我得到的是更改的值，而不是顺序(顺序仍然是导入的excel文件的顺序)。如何导入datagridview的值和顺序?参考下面的代码:

'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.

这是将excel导入datagridview的顺序。这是转换后的datagridview的顺序。这是导出的datagridview到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 :)

您会注意到，导入和导出的顺序相同。我希望导出的顺序与转换后的顺序相同。我希望现在已经很清楚了，我希望有人能帮助我解决这个问题。谢谢:)

1 个解决方案