有没有办法将数据从DataGridView连续导出到Excel电子表格

Question

所以我正在VB.NET中编写一个图形用户界面，它允许用户从Arduino Uno的串行监视器读取数据。问题是，我只能在测量结束时使用以下代码导出数据：

Private Sub Button_Save_To_Excel_Click(sender As Object, e As EventArgs) Handles Button_Save_To_Excel.Click
Button_Save_To_Excel.Height = 37
Button_Save_To_Excel.Text = "Please Wait..."
Button_Save_To_Excel.Enabled = False
Button_Start_Recording.Enabled = False
ProgressBar_Save_To_Excel.Visible = True
ProgressBar_Save_To_Excel.Value = 1
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 i As Integer
Dim j As Integer
ProgressBar_Save_To_Excel.Value = 3
xlApp = New Microsoft.Office.Interop.Excel.Application
xlWorkBook = xlApp.Workbooks.Add(misValue)
xlWorkSheet = xlWorkBook.Sheets(1)
ProgressBar_Save_To_Excel.Value = 5
For i = 0 To DataGridView1.RowCount - 2
For j = 0 To DataGridView1.ColumnCount - 1
For k As Integer = 1 To DataGridView1.Columns.Count
xlWorkSheet.Cells(1, k) = DataGridView1.Columns(k - 1).HeaderText
xlWorkSheet.Cells(i + 2, j + 1) = DataGridView1(j, i).Value.ToString()
Next
Next
Next
ProgressBar_Save_To_Excel.Value = 8
Dim savePath As String = Nothing
Using sd As New SaveFileDialog
With sd
.RestoreDirectory = True
.Filter = "Excel XLS Files(*.xls)|*.xls|Excel Macro Embedded Files(*.xlsm)|*.xlsm|Excel XLSX Files(*.xlsx)|*.xlsx"
.FilterIndex = 3
If .ShowDialog = DialogResult.OK Then
savePath = .FileName
End If
End With
End Using
If savePath IsNot Nothing AndAlso savePath.Trim <> "" Then
xlWorkBook.SaveAs(savePath, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue)
End If
xlWorkBook.Close(True, misValue, misValue)
xlApp.Quit()
ProgressBar_Save_To_Excel.Value = 10
MsgBox("Successfully saved" & vbCrLf & "Files are saved at : " & savePath, MsgBoxStyle.Information, "Information")
ProgressBar_Save_To_Excel.Visible = False
Process.Start(savePath)
Button_Save_To_Excel.Height = 50
Button_Save_To_Excel.Text = "Save To MS Excel"
Button_Save_To_Excel.Enabled = True
Button_Start_Recording.Enabled = True

End Sub

有没有什么方法可以修改代码，这样我就可以用来自串行监视器的新数据连续覆盖现有的Excel文件？我尝试过使用循环来实现这一点，但没有成功。

图形用户界面将被用作数据采集系统，它将长时间甚至几天记录数据。因此，我不能只在测量结束后导出数据。如果发生停电，所有测量数据都将丢失。

Answer 1

1. 您不需要使用Microsoft.Office.Interop.Excel.Application来创建Excel文件。有更好的图书馆
2. 您可以通过ADO.net使用Excel，并且所有操作都可以以常规的ADO.net方式完成，就像它是一个DB表一样。以下是示例https://stackoverflow.com/a/16296106/1704458
3. 这里的关键是使用数据绑定。然后，您不将行添加到网格中，而是将它们添加到数据源对象datatable/dataview中。在DataAdupter上设置UPDDATE命令，并连接一个触发器来调用dataAdapter.Update

您将不需要按下按钮