如何导出使用VB.NET excel datagridview的自动增量行值？ [英] How to export to excel the autoincremented row value of datagridview using VB.NET?

问题描述

我有datagridview列：FullName，Age，Birthday，Degree。



自动生成的行值显示/位于datagridview的第一列，自动生成通过以下代码：

I have datagridview with columns: FullName, Age, Birthday, Degree.

The autoincremented row value displayed/located at the first column of datagridview that is automatically generated by the following code:

Private Sub DataGridView1_CellFormatting(sender As Object, e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting
        DataGridView1.Rows(e.RowIndex).HeaderCell.Value = CStr(e.RowIndex + 1)
    End Sub

我的问题是导出为ex​​cel时不包括自动增量行值。

我w将它包含在excel的第一栏（A）中。

My problem is that the autoincremented row value is not included in exporting to excel.
I want it to be included in first column(A) in excel.

Imports Microsoft.Office.Interop

Public Class Form1

    Private Sub DataGridView1_CellFormatting(sender As Object, e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting
        DataGridView1.Rows(e.RowIndex).HeaderCell.Value = CStr(e.RowIndex + 1)
    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

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
        Dim i As Integer
        Dim j As Integer

        xlApp = New 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
                xlWorkSheet.Cells(i + 18, j + 2) = DataGridView1(j, i).Value.ToString()
            Next
        Next


        xlWorkSheet.SaveAs("C:\Users\Programmer RBP\Desktop\vbexcel.xlsx")
        xlWorkBook.Close()
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

        MsgBox("You can find the file C:\Users\Programmer RBP\Desktop\vbexcel.xlsx")
    End Sub
End Class

excel的输出：



A | B | C | D | E

----------------------------------------- --------------------

|胡安克鲁兹| 31 | 2/24/1988 |本科生

|约翰韦恩| 31 | 1/05/1988 |毕业生

|杰森达克| 31 | 2/14/1988 |本科





我想要的或预期的输出：



A | B | C | D | E

----------------------------------------- --------------------

1 |胡安克鲁兹| 31 | 2/24/1988 |本科

2 |约翰韦恩| 31 | 1/05/1988 |毕业生

3 |杰森达克| 31 | 2/14/1988 |本科



我的尝试：

The output in excel:

A | B | C | D | E
-------------------------------------------------------------
| Juan Cruz | 31 | 2/24/1988 | Undergraduate
| John Wayne| 31 | 1/05/1988 | Graduate
| Jason Dack| 31 | 2/14/1988 | Undergraduate


What I want or expected output:

A | B | C | D | E
-------------------------------------------------------------
1 | Juan Cruz | 31 | 2/24/1988 | Undergraduate
2 | John Wayne| 31 | 1/05/1988 | Graduate
3 | Jason Dack| 31 | 2/14/1988 | Undergraduate

What I have tried:

Public Class Form1

    Private Sub DataGridView1_CellFormatting(sender As Object, e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting
        DataGridView1.Rows(e.RowIndex).HeaderCell.Value = CStr(e.RowIndex + 1)
    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

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
        Dim i As Integer
        Dim j As Integer

        xlApp = New 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
                xlWorkSheet.Cells(i + 18, j + 2) = DataGridView1(j, i).Value.ToString()
            Next
        Next


        xlWorkSheet.SaveAs("C:\Users\Programmer RBP\Desktop\vbexcel.xlsx")
        xlWorkBook.Close()
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

        MsgBox("You can find the file C:\Users\Programmer RBP\Desktop\vbexcel.xlsx")
    End Sub
End Class

推荐答案

首先创建datagridview时应该创建自动编号;使用OTHER列。



即使您添加了一个空白列，您稍后会更新，它也会比您当前的努力更好。

You should have created the "auto number" when you created the datagridview in the first place; with the OTHER columns.

Even if you add a blank column you update later, it's better than your current efforts.

这篇关于如何导出使用VB.NET excel datagridview的自动增量行值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！