将datagridview导出到excel vb.net [英] Export datagridview to excel vb.net

问题描述

大家好，



是否可以将datagridview与其颜色格式及其设置标题一起导出到Excel？我的意思是，我需要准确地将datagridview中的内容导出为ex​​cel。有一些想法或想法如何做到这一点？谢谢！

Hi All,

Is it possible to export datagridview to excel together with its color formatting and its set header? I mean, i need to export exactly what's in the datagridview to excel. Got some idea or thoughts how to do this? Thanks!

推荐答案

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    DATAGRIDVIEW_TO_EXCEL((DataGridView1)) ' THIS PARAMETER IS 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

是的，这是可能的，一定程度的近似，甚至相当不错。我不知道为什么这样做，但要使用Excel，您将需要使用Microsoft Office Interop程序集。如果安装了Excel，则在GAC上有适当版本的适当版本的Microsoft.Office.Interop.Excel程序集，因此您可以从添加引用窗口的.NET选项卡中添加它。



请参阅：

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel%28v=office.14%29.aspx [< a href =http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel%28v=office.14%29.aspxtarget =_ blanktitle =New Window> ^ ]。



（使用您想要使用的版本的文档。）



假设带有 DataGridView 的代码是您的，所以您应该知道在哪里定义所有列/单元格样式以读取它们并创建类似的Excel样式。



很多人问你是否可以在没有Office安装的情况下安装，这对于部署到任意用户来说是实际的，因为你可能会或可能不会t允许自己要求用户使用适当的Office安装作为使用产品的先决条件。我在这里提供了我的答案：

将Office文档转换为PDF而无需互操作 [ ^ ]，
您好如何使用c＃.net 在Windows应用程序中显示word文件[ ^ ]，

在不使用Interop.word dll的情况下读取word文件...不想在IIS中安装单词。 [ ^ ]。



现在，一些不太愉快的音符怎么样？所有这些都是劳动和无效的。我个人认为使用像Microsoft Office这样的专有产品对软件团队或公司来说是一件坏事，导致供应商锁定（ http://en.wikipedia.org/wiki/Vendor_lock-in [ ^ ]）。将事物导出到Office的愿望反映了非软件开发人员的观点，但由于知识和经验非常有限，缺乏经验的用户认为Office是计算机功能的主要部分的观点。您可以更轻松地使用纯.NET提供用户想要的所有内容，而不需要其他任何投资。但是，我知道我对你的产品一无所知，所以你应该更清楚你真正需要什么。

-SA

Yes, it's possible, with certain degree of approximation, and even pretty good. I have no idea why doing so, but to work with Excel, you will need to use Microsoft Office Interop assembly. If Excel is installed, you have appropriate assembly "Microsoft.Office.Interop.Excel" of appropriate version on GAC, so you can add it from the tab ".NET" of the "Add Reference" window.

Please see:
http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel%28v=office.14%29.aspx[^].

(Use the documentation of the version you want to use.)

As assume the code with DataGridView is yours, so you should know where did you define all column/cell styles to read them and create similar Excel styles.

Many ask if it's possible without Office installation, which is actual for deployment to arbitrary users, as you may or may not allow yourself demand that the users had appropriate Office installation as a prerequisite for using your product. I provided my answers here:
Convert Office-Documents to PDF without interop[^],
Hi how can i display word file in windows application using c#.net[^],
Read a word file without using Interop.word dll...Do not want to install word in IIS..[^].

Now, how about some not very pleasant note? All this is labor-taking and ineffective. I personally think that using such a proprietary product as Microsoft Office is a bad thing for a software team or a company, leading to vendor lock-in (http://en.wikipedia.org/wiki/Vendor_lock-in[^]). The desire to export things to Office reflect non a software developer point of view, but the views of a inexperienced user thinking that Office is a major part of computer functionality, due to very limited knowledge and experience. You might easier provide everything what the user wants using pure .NET and nothing else, without unwanted investments. However, I understand that I don't know anything about your product, so you should know better what you really need.

—SA

Imports System.IO
Imports Excel = Microsoft.Office.Interop.Excel

sub export
Dim default_location As String = Application.StartupPath & "\Jadwal\" & periode.Text & ".xls"
'Creating dataset to export
Dim dset As New DataSet
'add table to dataset
dset.Tables.Add()
'add column to that table
For i As Integer = 0 To dg2.ColumnCount - 1
dset.Tables(0).Columns.Add(dg2.Columns(i).HeaderText)
Next
'add rows to the table
Dim dr1 As DataRow
For i As Integer = 0 To dg2.RowCount - 1
dr1 = dset.Tables(0).NewRow
For j As Integer = 0 To dg2.Columns.Count - 1
dr1(j) = dg2.Rows(i).Cells(j).Value
Next
dset.Tables(0).Rows.Add(dr1)
Next

Dim excel As New Microsoft.Office.Interop.Excel.ApplicationClass
Dim wBook As Microsoft.Office.Interop.Excel.Workbook
Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet

excel.Visible = True
excel.UserControl = True
      
wBook = excel.Workbooks.Add(System.Reflection.Missing.Value)
wSheet = wBook.Sheets("sheet1")
excel.Range("A50:I50").EntireColumn.AutoFit()
With wBook
.Sheets("Sheet1").Select()
.Sheets(1).Name = "NameYourSheet"
End With

Dim dt As System.Data.DataTable = dset.Tables(0)
wSheet.Cells(1).value = periode.Text

For i = 0 To dg2.RowCount - 1
For j = 0 To dg2.ColumnCount - 1
wSheet.Cells(i + 1, j + 1).value = dg2.Rows(i).Cells(j).Value.tosring
Next j
Next i

wSheet.Columns.AutoFit()
Dim blnFileOpen As Boolean = False
Try
Dim fileTemp As System.IO.FileStream = System.IO.File.OpenWrite(default_location)
fileTemp.Close()
Catch ex As Exception
blnFileOpen = False
End Try

If System.IO.File.Exists(default_location) Then
System.IO.File.Delete(default_location)
End If

wBook.SaveAs(default_location)
excel.Workbooks.Open(default_location)
excel.Visible = True
end sub

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