导出到带有标题文本的 Excel

Question

我的代码应该在导出到 Excel 时包含标题文本。

For i As Integer = 0 To DataGridView2.Rows.Count - 2
For j As Integer = 0 To DataGridView2.Columns.Count - 1
' Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check.
If cellRowIndex = 1 Then
worksheet.Cells(cellRowIndex, cellColumnIndex) = DataGridView2.Columns(j).HeaderText
Else
worksheet.Cells(cellRowIndex, cellColumnIndex) = DataGridView2.Rows(i).Cells(j).Value
End If
cellColumnIndex += 1
Next
cellColumnIndex = 1
cellRowIndex += 1
Next

但是，此代码将第一个数据行替换为标题文本，而不是将其插入到上方。如果我删除提取标题文本的 If 语句，我会得到所有行，但我没有得到标题文本。

For i As Integer = 0 To DataGridView2.Rows.Count - 2
For j As Integer = 0 To DataGridView2.Columns.Count - 1
worksheet.Cells(cellRowIndex, cellColumnIndex) = DataGridView2.Rows(i).Cells(j).Value
cellColumnIndex += 1
Next
cellColumnIndex = 1
cellRowIndex += 1
Next

关于如何解决这个问题的任何想法？

Answer 1

下面的代码创建一个 Excel 文件，其中包含来自 DataGridView 的标头。我已经在Visual Studio 2010中对其进行了测试。首先，您必须添加Microsoft办公室程序集的引用。

• Microsoft.Office.Interop.Excel (版本 - 12.0.0.0)

  Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.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 i As Integer
  Dim j As Integer
  xlApp = New Microsoft.Office.Interop.Excel.Application
  xlWorkBook = xlApp.Workbooks.Add(misValue)
  xlWorkSheet = xlWorkBook.Sheets("sheet1")
  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
  xlWorkSheet.SaveAs("C:vbToexcel.xlsx")
  xlWorkBook.Close()
  xlApp.Quit()
  releaseObject(xlApp)
  releaseObject(xlWorkBook)
  releaseObject(xlWorkSheet)
  MsgBox("File successfully created - C:vbToexcel.xlsx")   End Sub
  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
  

Answer 2

跟踪代码后，很明显您在两个for循环中遇到了索引问题。您提供的代码似乎缺少第一行数据。

正如您评论的那样：

此代码将第一个数据行替换为标题文本，而不是将其插入到上方...

这是不正确的，它不是替换行，它只是跳过DataGridView中的第一行数据。下面是要解释的代码。

For i As Integer = 0 To DataGridView1.Rows.Count - 2
For j As Integer = 0 To DataGridView1.Columns.Count - 1
If cellRowIndex = 1 Then
worksheet.Cells(cellRowIndex, cellColumnIndex) = DataGridView1.Columns(j).HeaderText
Else
worksheet.Cells(cellRowIndex, cellColumnIndex) = DataGridView1.Rows(i).Cells(j).Value
End If
cellColumnIndex += 1
Next
cellColumnIndex = 1
cellRowIndex += 1
Next

基本上，这遍历行然后遍历列。问题出在If语句和索引i中。在此If语句中，您将检查这是否是第一次获取标头。如果是第一次，您编写标题以 excel 并继续。这将跳过第一行数据，因为循环变量i用作带有赋值的 DataGridView 行的索引：

worksheet.Cells(cellRowIndex, cellColumnIndex) = DataGridView1.Rows(i).Cells(j).Value

进入j循环时，i周围的第一次为零 (0)。使用cellRowIndex进行检查以确定是否需要输出标头。在这种情况下，他们确实...输出标头，然后退出此if并循环回下一个标头。输出所有标头后，退出j循环并循环回i循环。这会将i增加到 1 并进入j循环...由于输出标头时i已经是 0，我们将跳过/错过DataGridView中的第 0 行。我希望这是有道理的。

一个简单的解决方案是简单地从 -1 开始i：

For i As Integer = -1 To DataGridView1.Rows.Count - 2

这将解决您遇到的问题，但是代码不容易遵循。我建议使用foreach循环来循环DataGridView行并将列输出与行输出分开。这确实会创建两个循环，但第一个循环只会循环一次以添加标头。下一个循环遍历所有行。这将使索引在将来更易于处理和阅读。

For Each column In DataGridView1.Columns
worksheet.Cells(1, column.Index + 1).Value = column.Name
Next
Dim rowIndex = 2
For Each row As DataGridViewRow In DataGridView1.Rows
If Not row.IsNewRow Then
For colIndex As Integer = 0 To DataGridView1.Columns.Count - 1
worksheet.Cells(rowIndex, colIndex + 1).Value = row.Cells(colIndex).Value.ToString
Next
End If
rowIndex += 1
Next

希望这有帮助。