如何使用vb.net将datagridview导出到excel？ [英] How to export datagridview to excel using vb.net?

问题描述

我在vb.net中有一个datagridview从数据库中填写。我已经研究了，我发现没有内置的支持从datagridview直接打印。我不想使用水晶报表，因为我不熟悉它。

我打算将其导出为ex​​cel，使我能够生成报告datagridview。

你可以提供我这样做的方式吗？

解决方案

下面的代码创建Excel文件并保存在D：驱动器
它使用Microsoft Office 2007

第一次添加REFERRANCE（Microsoft Office 12.0对象库）到您的项目

然后添加下面的代码给导出按钮单击事件 -

  Private Sub Export_Button_Click（ByVal sender As System.Object，ByVal e As 
 System.EventArgs）处理VIEW_Button.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我是Inte ger 
 Dim j As Integer 
 
 xlApp =新的Microsoft.Office.Interop.Excel.ApplicationClass 
 xlWorkBook = xlApp.Workbooks.Add（misValue）
 xlWorkSheet = xlWorkBook .Sheets（sheet1）
 
 
对于i = 0 To DataGridView1.RowCount  -  2 
对于j = 0到DataGridView1.ColumnCount  -  1 
对于k As整数= 1到DataGridView1.Columns.Count 
 xlWorkSheet.Cells（1，k）= DataGridView1.Columns（k  -  1）.HeaderText 
 xlWorkSheet.Cells（i + 2，j + 1）= DataGridView1 （j，i）.Value.ToString（）
下一个
下一个
下一个
 
 xlWorkSheet.SaveAs（D：\vbexcel.xlsx）
 xlWorkBook.Close（）
 xlApp.Quit（）
 
 releaseObject（xlApp）
 releaseObject（xlWorkBook）
 releaseObject（xlWorkSheet）
 
 MsgBox（你可以找到文件D：\vbexcel.xlsx）
 End Sub 
 
 Private Sub releaseObject（ByVal obj As Object）
尝试
 System.Runtime.InteropServices.Marshal.ReleaseComObject（obj）
 obj = Nothing 
 Catch ex As Exception 
 obj = Nothing 
最后
 GC.Collect（）
结束尝试
 End Sub 
  

I have a datagridview in vb.net that is filled up from the database. I've researched and I found out that there is no built in support to print directly from datagridview. I don't want to use crystal report because I'm not familiar with it.

I'm planning to export it to excel to enable me to generate report from the datagridview.

Can you provide me ways to do this?

解决方案

Code below creates Excel File and saves it in D: drive It uses Microsoft office 2007

FIRST ADD REFERRANCE (Microsoft office 12.0 object library ) to your project

Then Add code given bellow to the Export button click event-

Private Sub Export_Button_Click(ByVal sender As System.Object, ByVal e As 
System.EventArgs) Handles VIEW_Button.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.ApplicationClass
    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("D:\vbexcel.xlsx")
    xlWorkBook.Close()
    xlApp.Quit()

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

    MsgBox("You can find the file D:\vbexcel.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

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