Как экспортировать в excel значение строки autoincremented datagridview с помощью VB.NET-что?
У меня есть datagridview со столбцами: полное имя, возраст, день рождения, степень.
Значение строки autoincremented, отображаемое/расположенное в первом столбце datagridview, автоматически генерируется следующим кодом:
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
Моя проблема заключается в том, что значение строки autoincremented не включено в экспорт в excel.
Я хочу, чтобы он был включен в первый столбец(а) в 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 | Бакалавриат
Что я уже пробовал:
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