更改 Excel 文件并通过 Visual Basic 应用程序更新

Question

您好，我有此表单将Excel文件导入Datagridview，我想知道是否有办法更改数据并将更改保存在我加载到Datagrid中的Excel文件中？

Note：此问题总结整理于:Change Excel file and Update via Visual Basic application

Answer 1

如何更改数据并将更改保存在 Excel 文件中

根据您的代码进行测试后，您可以尝试以下代码将更改保存到 Excel 文件。

Imports Excel = Microsoft.Office.Interop.Excel  

'...'  
    Dim isChanged As Boolean = False  
    Dim sheetName As String  
    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click  
        sheetName = cboSheet.Text  
        If sheetName IsNot Nothing Then  
            SaveToExcel(TextBox1.Text, sheetName)  
            isChanged = False  
        End If  
    End Sub  
    Private Sub cboSheet_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cboSheet.SelectedIndexChanged  
        If isChanged Then  
            Dim confirmResult = MessageBox.Show("DataGridView cell value have been changed, do you wang to save it?", "Confirm save", MessageBoxButtons.YesNo)  
  
            If confirmResult = DialogResult.Yes Then  
                SaveToExcel(TextBox1.Text, sheetName)  
                isChanged = False  
            Else  
                isChanged = False  
            End If  
        End If  
        Dim dt As DataTable = tables(cboSheet.SelectedItem.ToString())  
        DataGridView1.DataSource = dt  
    End Sub  
    Private Sub DataGridView1_CellValueChanged(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellValueChanged  
        isChanged = True  
        sheetName = cboSheet.Text  
    End Sub  
    Private Sub SaveToExcel(ByVal filePath As String, ByVal sheetName As String)  
  
        Dim xlApp As Excel.Application = New Excel.Application()  
        Dim xlWorkBook As Excel.Workbook = xlApp.Workbooks.Open(filePath)  
        xlApp.Visible = False  
        Dim xlWorkSheets As Excel.Sheets = xlWorkBook.Worksheets  
        Dim xlWorkSheet As Excel.Worksheet = CType(xlWorkSheets(sheetName), Excel.Worksheet)  
  
        For i = 0 To DataGridView1.RowCount - 2  
            For j = 0 To DataGridView1.ColumnCount - 1  
                xlWorkSheet.Cells(i + 2, j + 1) = DataGridView1.Item(j, i).Value.ToString()  
            Next  
        Next  
        xlApp.DisplayAlerts = False  
        xlWorkBook.SaveAs(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)  
        xlApp.Quit()  
        releaseObject(xlWorkSheet)  
        releaseObject(xlWorkBook)  
        releaseObject(xlApp)  
    End Sub  
    Private Sub releaseObject(ByVal obj As Object)  
        Try  
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)  
            obj = Nothing  
        Catch ex As Exception  
            obj = Nothing  
            MessageBox.Show("Exception Occured while releasing object " & ex.ToString())  
        Finally  
            GC.Collect()  
        End Try  
    End Sub  

---

如果答案是正确的，请点击“接受答案”并点赞。 如果您对此答案还有其他疑问，请点击“评论”。

注意：如果您想接收相关电子邮件，请按照我们的文档中的步骤启用电子邮件通知 此线程的通知。