如何使用 vb.net 将 datagridview 导出到 excel？

How to export datagridview to excel using vb.net?

提问by Michael Z.

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.

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

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

我打算将它导出到 excel 以使我能够从 datagridview 生成报告。

Can you provide me ways to do this?

你能提供我这样做的方法吗？

回答by RAJ

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

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

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

首先将引用（Microsoft Office 12.0 对象库）添加到您的项目

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

回答by Michael Z.

Excel Method

Excel方法

This method is different than many you will see. Others use a loop to write each cell and write the cells with text data type.

这种方法与您将看到的许多方法不同。其他人使用循环写入每个单元格并使用文本数据类型写入单元格。

This method creates an object array from a DataTableor DataGridViewand then writes the array to Excel. This means I can write to Excel without a loop and retain data types.

此方法从DataTableor创建一个对象数组DataGridView，然后将该数组写入 Excel。这意味着我可以在没有循环的情况下写入 Excel 并保留数据类型。

I extracted this from my library and I think I changed it enough to work with this code only, but more minor tweaking might be necessary. If you get errors just let me know and I'll correct them for you. Normally, I create an instance of my class and call these methods. If you would like to use my library then use this link to download it and if you need help just let me know.
https://zomp.co/Files.aspx?ID=zExcel

我从我的库中提取了它，我认为我对其进行了足够的更改以仅使用此代码，但可能需要进行更小的调整。如果您发现错误，请告诉我，我会为您纠正。通常，我创建我的类的一个实例并调用这些方法。如果您想使用我的图书馆，请使用此链接下载它，如果您需要帮助，请告诉我。
https://zomp.co/Files.aspx?ID=zExcel

---

---

After copying the code to your solution you will use it like this.

将代码复制到您的解决方案后，您将像这样使用它。

In your button code add this and change the names to your controls.

在您的按钮代码中添加此项并将名称更改为您的控件。

WriteDataGrid("Sheet1", grid)

WriteDataGrid("Sheet1", grid)

To open your file after exporting use this line

要在导出后打开您的文件，请使用此行

System.Diagnostics.Process.Start("The location and filename of your file")

System.Diagnostics.Process.Start("The location and filename of your file")

In the WriteArraymethod you'll want to change the line that saves the workbook to where you want to save it. Probably makes sense to add this as a parameter.

在该WriteArray方法中，您需要将保存工作簿的行更改为您想要保存的位置。将其添加为参数可能是有意义的。

wb.SaveAs("C:\MyWorkbook.xlsx")

wb.SaveAs("C:\MyWorkbook.xlsx")

---

---

Public Function WriteArray(Sheet As String, ByRef ObjectArray As Object(,)) As String
    Try
        Dim xl As Excel.Application = New Excel.Application
        Dim wb As Excel.Workbook = xl.Workbooks.Add()
        Dim ws As Excel.Worksheet = wb.Worksheets.Add()
        ws.Name = Sheet
        Dim range As Excel.Range = ws.Range("A1").Resize(ObjectArray.GetLength(0), ObjectArray.GetLength(1))
        range.Value = ObjectArray

        range = ws.Range("A1").Resize(1, ObjectArray.GetLength(1) - 1)

        range.Interior.Color = RGB(0, 70, 132)  'Con-way Blue
        range.Font.Color = RGB(Drawing.Color.White.R, Drawing.Color.White.G, Drawing.Color.White.B)
        range.Font.Bold = True
        range.WrapText = True

        range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
        range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter

        range.Application.ActiveWindow.SplitColumn = 0
        range.Application.ActiveWindow.SplitRow = 1
        range.Application.ActiveWindow.FreezePanes = True

        wb.SaveAs("C:\MyWorkbook.xlsx")
        wb.CLose()
        xl.Quit()
        xl = Nothing
        wb = Nothing
        ws  = Nothing
        range = Nothing
        ReleaseComObject(xl)
        ReleaseComObject(wb)
        ReleaseComObject(ws)
        ReleaseComObject(range)

        Return ""
    Catch ex As Exception
        Return "WriteArray()" & Environment.NewLine & Environment.NewLine & ex.Message
    End Try
End Function

Public Function WriteDataGrid(SheetName As String, ByRef dt As DataGridView) As String
        Try
            Dim l(dt.Rows.Count + 1, dt.Columns.Count) As Object
            For c As Integer = 0 To dt.Columns.Count - 1
                l(0, c) = dt.Columns(c).HeaderText
            Next

            For r As Integer = 1 To dt.Rows.Count
                For c As Integer = 0 To dt.Columns.Count - 1
                    l(r, c) = dt.Rows(r - 1).Cells(c)
                Next
            Next

            Dim errors As String = WriteArray(SheetName, l)
            If errors <> "" Then
                Return errors
            End If

            Return ""
        Catch ex As Exception
            Return "WriteDataGrid()" & Environment.NewLine & Environment.NewLine & ex.Message
        End Try
    End Function


 Public Function WriteDataTable(SheetName As String, ByRef dt As DataTable) As String
        Try
            Dim l(dt.Rows.Count + 1, dt.Columns.Count) As Object
            For c As Integer = 0 To dt.Columns.Count - 1
                l(0, c) = dt.Columns(c).ColumnName
            Next

            For r As Integer = 1 To dt.Rows.Count
                For c As Integer = 0 To dt.Columns.Count - 1
                    l(r, c) = dt.Rows(r - 1).Item(c)
                Next
            Next

            Dim errors As String = WriteArray(SheetName, l)
            If errors <> "" Then
                Return errors
            End If

            Return ""
        Catch ex As Exception
            Return "WriteDataTable()" & Environment.NewLine & Environment.NewLine & ex.Message
        End Try
    End Function

---

---

I actually don't use this method in my Database program because it's a slow method when you have a lot of rows/columns. I instead create a CSV from the DataGridView. Writing to Excel with Excel Automation is only useful if you need to format the data and cells otherwise you should use CSV. You can use the code after the image for CSV export.

我实际上没有在我的数据库程序中使用这种方法，因为当你有很多行/列时，它是一种缓慢的方法。我改为从 DataGridView 创建一个 CSV。仅当您需要格式化数据和单元格时，使用 Excel 自动化写入 Excel 才有用，否则您应该使用 CSV。您可以使用图像后的代码进行 CSV 导出。

---

---

CSV Method

CSV 方法

Private Sub DataGridToCSV(ByRef dt As DataGridView, Qualifier As String)  
        Dim TempDirectory As String = "A temp Directory"  
        System.IO.Directory.CreateDirectory(TempDirectory)
        Dim oWrite As System.IO.StreamWriter
        Dim file As String = System.IO.Path.GetRandomFileName & ".csv"
        oWrite = IO.File.CreateText(TempDirectory & "\" & file)

        Dim CSV As StringBuilder = New StringBuilder()

        Dim i As Integer = 1
        Dim CSVHeader As StringBuilder = New StringBuilder()
        For Each c As DataGridViewColumn In dt.Columns
            If i = 1 Then
                CSVHeader.Append(Qualifier & c.HeaderText.ToString() & Qualifier)
            Else
                CSVHeader.Append("," & Qualifier & c.HeaderText.ToString() & Qualifier)
            End If
            i += 1
        Next

        'CSV.AppendLine(CSVHeader.ToString())
        oWrite.WriteLine(CSVHeader.ToString())
        oWrite.Flush()

        For r As Integer = 0 To dt.Rows.Count - 1

            Dim CSVLine As StringBuilder = New StringBuilder()
            Dim s As String = ""
            For c As Integer = 0 To dt.Columns.Count - 1
                If c = 0 Then
                    'CSVLine.Append(Qualifier & gridResults.Rows(r).Cells(c).Value.ToString() & Qualifier)
                    s = s & Qualifier & gridResults.Rows(r).Cells(c).Value.ToString() & Qualifier
                Else
                    'CSVLine.Append("," & Qualifier & gridResults.Rows(r).Cells(c).Value.ToString() & Qualifier)
                    s = s & "," & Qualifier & gridResults.Rows(r).Cells(c).Value.ToString() & Qualifier
                End If

            Next
            oWrite.WriteLine(s)
            oWrite.Flush()
            'CSV.AppendLine(CSVLine.ToString())
            'CSVLine.Clear()
        Next

        'oWrite.Write(CSV.ToString())

        oWrite.Close()
        oWrite = Nothing    

        System.Diagnostics.Process.Start(TempDirectory & "\" & file)   

        GC.Collect()

    End Sub

回答by HIRAM

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    DATAGRIDVIEW_TO_EXCEL((DataGridView1)) ' PARAMETER: YOUR DATAGRIDVIEW
End Sub

Private Sub DATAGRIDVIEW_TO_EXCEL(ByVal DGV As DataGridView)
    Try
        Dim DTB = New DataTable, RWS As Integer, CLS As Integer

        For CLS = 0 To DGV.ColumnCount - 1 ' COLUMNS OF DTB
            DTB.Columns.Add(DGV.Columns(CLS).Name.ToString)
        Next

        Dim DRW As DataRow

        For RWS = 0 To DGV.Rows.Count - 1 ' FILL DTB WITH DATAGRIDVIEW
            DRW = DTB.NewRow

            For CLS = 0 To DGV.ColumnCount - 1
                Try
                    DRW(DTB.Columns(CLS).ColumnName.ToString) = DGV.Rows(RWS).Cells(CLS).Value.ToString
                Catch ex As Exception

                End Try
            Next

            DTB.Rows.Add(DRW)
        Next

        DTB.AcceptChanges()

        Dim DST As New DataSet
        DST.Tables.Add(DTB)
        Dim FLE As String = "" ' PATH AND FILE NAME WHERE THE XML WIL BE CREATED (EXEMPLE: C:\REPS\XML.xml)
        DTB.WriteXml(FLE)
        Dim EXL As String = "" ' PATH OF/ EXCEL.EXE IN YOUR MICROSOFT OFFICE
        Shell(Chr(34) & EXL & Chr(34) & " " & Chr(34) & FLE & Chr(34), vbNormalFocus) ' OPEN XML WITH EXCEL

    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try

End Sub

回答by Francois Borgies

The following code works fine for me :)

以下代码对我来说很好用:)

Protected Sub ExportToExcel(sender As Object, e As EventArgs) Handles ExportExcel.Click
        Try
            Response.Clear()
            Response.Buffer = True
            Response.AddHeader("content-disposition", "attachment;filename=ExportEthias.xls")
            Response.Charset = ""
            Response.ContentType = "application/vnd.ms-excel"
            Using sw As New StringWriter()
                Dim hw As New HtmlTextWriter(sw)
                GvActifs.RenderControl(hw)
                'Le format de base est le texte pour éviter les problèmes d'arrondis des nombres
                Dim style As String = "<style> .textmode { } </style>"
                Response.Write(Style)
                Response.Output.Write(sw.ToString())
                Response.Flush()
                Response.End()
            End Using
        Catch ex As Exception
            lblMessage.Text = "Erreur export Excel : " & ex.Message
        End Try
    End Sub
    Public Overrides Sub VerifyRenderingInServerForm(control As Control)
        ' Verifies that the control is rendered
    End Sub

Hopes this help you.

希望这对你有帮助。

回答by Jay Riggs

Regarding your need to 'print directly from datagridview', check out this article on CodeProject:

关于您需要“直接从 datagridview 打印”，请查看 CodeProject 上的这篇文章：

The DataGridViewPrinter Class

DataGridViewPrinter 类

There are a number of similar articles but I've had luck with the one I linked.

有许多类似的文章，但我对链接的文章很幸运。

回答by Seenu

Dim rowNo1 As Short Dim numrow As Short Dim colNo1 As Short Dim colNo2 As Short

将 rowNo1 调为短 将 numrow 调为短 将 colNo1 调为短 将 colNo2 调为短

    rowNo1 = 1
    colNo1 = 1
    colNo2 = 1
    numrow = 1

    ObjEXCEL = CType(CreateObject("Excel.Application"), Microsoft.Office.Interop.Excel.Application)
    objEXCELBook = CType(ObjEXCEL.Workbooks.Add, Microsoft.Office.Interop.Excel.Workbook)
    objEXCELSheet = CType(objEXCELBook.Worksheets(1), Microsoft.Office.Interop.Excel.Worksheet)

    ObjEXCEL.Visible = True

    For numCounter = 0 To grdName.Columns.Count - 1
        ' MsgBox(grdName.Columns(numCounter).HeaderText())
        If grdName.Columns(numCounter).Width > 0 Then
            ObjEXCEL.Cells(1, numCounter + 1) = grdName.Columns(numCounter).HeaderText()
        End If
        ' ObjEXCEL.Cells(1, numCounter + 1) = grdName.Columns.GetFirstColumn(DataGridViewElementStates.Displayed)
    Next numCounter

    ObjEXCEL.Range("A:A").ColumnWidth = 10
    ObjEXCEL.Range("B:B").ColumnWidth = 25
    ObjEXCEL.Range("C:C").ColumnWidth = 20
    ObjEXCEL.Range("D:D").ColumnWidth = 20
    ObjEXCEL.Range("E:E").ColumnWidth = 20
    ObjEXCEL.Range("F:F").ColumnWidth = 25

    For rowNo1 = 0 To grdName.RowCount - 1
        For colNo1 = 0 To grdName.ColumnCount - 1
            If grdName.Columns(colNo1).Width > 0 Then
                If Trim(grdName.Item(colNo1, rowNo1).Value) <> "" Then
                    'If IsDate(grdName.Item(colNo1, rowNo1).Value) = True Then
                    '    ObjEXCEL.Cells(numrow + 1, colNo2) = Format(CDate(grdName.Item(colNo1, rowNo1).Value), "dd/MMM/yyyy")
                    'Else
                    ObjEXCEL.Cells(numrow + 1, colNo2) = grdName.Item(colNo1, rowNo1).Value
                    'End If
                End If
                If colNo2 >= grdName.ColumnCount Then
                    colNo2 = 1
                Else
                    colNo2 = colNo2 + 1
                End If
            End If
        Next colNo1
        numrow = numrow + 1
    Next rowNo1

回答by Joppi Silangit

In design mode: Set DataGridView1 ClipboardCopyModeproperties to EnableAlwaysIncludeHeaderText

在设计模式下：将DataGridView1 ClipboardCopyMode属性设置为EnableAlwaysIncludeHeaderText

or on the program code

或在程序代码上

DataGridView1.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText

In the run time select all cells content (Ctrl+A) and copy (Ctrl+C) and paste to the Excel Program. Let the Excel do the rest

在运行时选择所有单元格内容 (Ctrl+A) 并复制 (Ctrl+C) 并粘贴到 Excel 程序。让 Excel 完成剩下的工作

Sorry for the inconvenient, I have been searching the method to print data directly from the datagridvew (create report from vb.net VB2012) and have not found the satisfaction result. Above code just my though, wondering if my applications user can rely on above simple step it will be nice and I could go ahead to next step on my program progress.

不便之处，我一直在寻找直接从datagridvew（从vb.net VB2012创建报表）打印数据的方法，没有找到满意的结果。上面的代码只是我的，想知道我的应用程序用户是否可以依赖上面的简单步骤，这会很好，我可以继续我的程序进度的下一步。

回答by Werner

A simple way of generating a printable report from a Datagridview is to place the datagridview on a Panel object. It is possible to draw a bitmap of the panel.

从 Datagridview 生成可打印报告的一种简单方法是将 datagridview 放在 Panel 对象上。可以绘制面板的位图。

Here's how I do it.

这是我如何做到的。

'create the bitmap with the dimentions of the Panel Dim bmp As New Bitmap(Panel1.Width, Panel1.Height)

'用面板的尺寸创建位图 Dim bmp As New Bitmap(Panel1.Width, Panel1.Height)

'draw the Panel to the bitmap "bmp" Panel1.DrawToBitmap(bmp, Panel1.ClientRectangle)

'将面板绘制到位图 "bmp" Panel1.DrawToBitmap(bmp, Panel1.ClientRectangle)

I create a multi page tiff by "breaking my datagridview items into pages. this is how i detect the start of a new page:

我通过“将我的 datagridview 项目分成页面来创建多页 tiff。这就是我检测新页面开始的方式：

'i add the rows to my datagrid one at a time and then check if the scrollbar is active. 'if the scrollbar is active i save the row to a variable and then i remove it from the 'datagridview and roll back my counter integer by one(thus the next run will include this 'row.

'我将行一次添加到我的数据网格中，然后检查滚动条是否处于活动状态。'如果滚动条处于活动状态，我将该行保存到一个变量中，然后我将它从 'datagridview 中删除，并将我的计数器整数回滚一个（因此下一次运行将包括该行。

Private Function VScrollBarVisible() As Boolean
    Dim ctrl As New Control
    For Each ctrl In DataGridView_Results.Controls
        If ctrl.GetType() Is GetType(VScrollBar) Then
            If ctrl.Visible = True Then
                Return True
            Else
                Return False
            End If
        End If
    Next
    Return Nothing
End Function

I hope this helps

我希望这有帮助