将datagridview导出为Excel使用列标题 [英] Export datagridview to excel With Column headers

问题描述

我使用以下代码将datagridview内容提取到excel表。但问题是我无法在Excel工作表中获取列标题。请帮我在Excel工作表中获取列标题。

  Dim  ExcelApp 作为 对象，ExcelBook 作为 对象 
  Dim  ExcelSheet 作为 对象 
  Dim  i 作为 整数 
  Dim  j 作为 整数 
 
 ' 创建excel的对象 
 ExcelApp = CreateObject（  Excel.Application）
 ExcelBook = ExcelApp.WorkBooks。添加
 ExcelSheet = ExcelBook.WorkSheets（ 1 ）
 
 使用 ExcelSheet 
  For  i =  1    我。 DataGridView1.RowCount 
 .cells（i， 1 ）=  Me  .DataGridView1.Rows（i -   1 ）。单元格（  id）。值
 对于 j =  1    DataGridView1.Columns.Count  -   1  
 .cells（i，j +  1 ）= DataGridView1.Rows（i  -   1 ）。Cells（j）.Value 
  Next  
 下一步 
 结束 使用 
 
 ExcelApp.Visible =  True  
 '   
 ExcelSheet =  Nothing  
 ExcelBook =  Nothing  
 ExcelApp =  Nothing  

解决方案

在循环遍历DataGridView中的所有行之前，遍历所有列并将headertext添加到ExcelSheet。

 使用 ExcelSheet 
 对于 每个列 As  DataGridViewColumn  In  DataGridView1.Columns 
 .cells（ 1 ，column.Index +  1 ）= column.HeaderText 
  Next  
  For  i =  1   To  我 .DataGridView1.RowCount 
 .cells（i +  1 ， 1 ）=  Me  .DataGridView1.Rows（i  -   1 ）。单元格（  id）。值
 对于 j =  1    DataGridView1.Columns.Count  -   1  
 .cells（i +  1 ，j +  1 ）= DataGridView1。行（i  -   1 ）。Cells（j）.Value 
  Next  
 下一步 
 结束 使用 

I have used the following code to extract the datagridview contents to an excel sheet. But the thing is i can't able to get the column headers in the excel sheet. Please assist me to get the column header in the excel sheet.

Dim ExcelApp As Object, ExcelBook As Object
Dim ExcelSheet As Object
Dim i As Integer
Dim j As Integer

'create object of excel
ExcelApp = CreateObject("Excel.Application")
ExcelBook = ExcelApp.WorkBooks.Add
ExcelSheet = ExcelBook.WorkSheets(1)

With ExcelSheet
     For i = 1 To Me.DataGridView1.RowCount
           .cells(i, 1) = Me.DataGridView1.Rows(i - 1).Cells("id").Value
           For j = 1 To DataGridView1.Columns.Count - 1
               .cells(i, j + 1) = DataGridView1.Rows(i - 1).Cells(j).Value
           Next
     Next
End With

ExcelApp.Visible = True
'
ExcelSheet = Nothing
ExcelBook = Nothing
ExcelApp = Nothing

解决方案

Before you loop through all the rows in your DataGridView, loop through all the Columns and add the headertext to the ExcelSheet.

With ExcelSheet
   For Each column As DataGridViewColumn In DataGridView1.Columns
      .cells( 1, column.Index + 1 ) = column.HeaderText
   Next 
   For i = 1 To Me.DataGridView1.RowCount
      .cells(i + 1, 1) = Me.DataGridView1.Rows(i - 1).Cells("id").Value
      For j = 1 To DataGridView1.Columns.Count - 1
         .cells(i + 1, j + 1) = DataGridView1.Rows(i - 1).Cells(j).Value
      Next
   Next
End With

这篇关于将datagridview导出为Excel使用列标题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！