使用标题文本导出到Excel

I have code that is supposed to include header text when exporting to Excel.

我有一些代码,在导出到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

However, this code replaces the first data row with the header text instead of inserting it above. If I remove the If statement which extracts the header text, I get all rows out, but I don't get header text.

但是,此代码使用标题文本替换第一个数据行,而不是将其插入上面。如果我删除提取标题文本的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

Any ideas on how to solve this?

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

2 个解决方案

#1

After tracing your code it is clear you are having an indexing problem in the two for loops. It appears the code you supplied is missing the first row of data.

在跟踪代码之后,很明显你在两个for循环中遇到索引问题。看来您提供的代码缺少第一行数据。

As you commented:

正如你评论的那样:

this code replaces the first data row with the header text instead of inserting it above...

此代码使用标题文本替换第一个数据行,而不是将其插入上面...

This is not correct, it is not replacing the row it is simply skipping the first row of data in the DataGridView. Below is your code to explain.

这是不正确的,它不是替换它只是跳过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

Basically this loops through the rows then the columns. The problem is in the If statement and the index i. In this If statement you check to see if this is the first time around to get the headers. If it is the first time around you write the headers to excel and proceed. This is going to skip the first row of data because the loop variable i is used as an index into the DataGridView rows with the assignment:

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

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

When entering the j loop the first time around i is zero (0). A check is made with cellRowIndex to determine if the headers need to be output. In this case they do… the headers are output then exit this if and loop back up to the next header. When all headers are output you exit the j loop and loop back up to the i loop. This will increment i to 1 and enter the j loop… Since i has already been 0 when the headers were output we will skip/miss row 0 in the DataGridView. I hope this makes sense.

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

A simple solution for what you have would be to simply start i at -1 with:

你所拥有的一个简单的解决方案就是简单地在-1处启动i:

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

This will solve the problem you are having however the code is not easy to follow. I recommend using a foreach loop for looping through the DataGridView rows and separating the column output from the rows output. This does create two loops but the first loop will only loop once to add the headers. The next loop goes through all the rows. This will make indexing easier to handle and easier to read in the future.

这将解决您遇到的问题,但代码不容易遵循。我建议使用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

Hope this helps.

希望这可以帮助。

#2

Below code creates an Excel file with header from DataGridView. I have tested it in Visual Studio 2010. First you have to add the reference of Microsoft Office assembly.

下面的代码创建一个带有DataGridView标头的Excel文件。我在Visual Studio 2010中测试过它。首先,您必须添加Microsoft Office程序集的引用。

Microsoft.Office.Interop.Excel (Version - 12.0.0.0)

Microsoft.Office.Interop.Excel(Version - 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

#1