如何将datagrid导出到excel [英] how to export datagrid to excel

问题描述

大家好



我能够在VB.net桌面应用程序中运行将DataGridView导出为ex​​cel的代码。现在，我想在使用DataGrid的WPF桌面应用程序中执行此操作。



我正在使用此代码：

Hi all

I was able to run code for exporting DataGridView to excel in VB.net desktop application. Now, I would like to do this in WPF desktop application with DataGrid.

I'm using this code:

Dim xlApp As Excel.Application = New Excel.Application
            Dim xlWorkBook As Excel.Workbook
            Dim xlWorkSheet As Excel.Worksheet
            Dim misValue As Object = System.Reflection.Missing.Value

            xlWorkBook = xlApp.Workbooks.Add(misValue)
            xlWorkSheet = DirectCast(xlWorkBook.Sheets("sheet1"), Excel.Worksheet)

            'xlApp.Visible = True

            Dim headers = (From ch In GeneralReport.Columns _
                            Let header = DirectCast(DirectCast(ch, DataGridViewColumn).HeaderCell, DataGridViewColumnHeaderCell) _
                            Select header.Value).ToArray()
            Dim headerText() As String = Array.ConvertAll(headers, Function(v) v.ToString)

            Dim items() = (From r In GeneralReport.Rows _
                    Let row = DirectCast(r, DataGridViewRow) _
                    Where Not row.IsNewRow _
                    Select (From cell In row.Cells _
                        Let c = DirectCast(cell, DataGridViewCell) _
                        Select c.Value).ToArray()).ToArray()

            Dim table As String = String.Join(vbTab, headerText) & Environment.NewLine
            For Each a In items
                Dim t() As String = Array.ConvertAll(a, Function(v) v.ToString)
                table &= String.Join(vbTab, t) & Environment.NewLine
            Next
            table = table.TrimEnd(CChar(Environment.NewLine))
            Clipboard.SetText(table)

            Dim alphabet() As Char = "abcdefghijklmnopqrstuvwxyz".ToUpper.ToCharArray
            Dim range As Excel.Range = xlWorkSheet.Range("A1:" & alphabet(headerText.Length - 1) & (items.Length + 1).ToString)
            Try
                xlWorkSheet.Columns.AutoFit()
                range.Select()
                'range.Copy()
                xlWorkSheet.Paste()
            Catch ex As Exception
                MessageBox.Show("Exception " + ex.Message)
            Finally
                GC.Collect()
            End Try
            range.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.XlLineStyle.xlLineStyleNone
            range.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.XlLineStyle.xlLineStyleNone
            With range.Borders(Excel.XlBordersIndex.xlEdgeLeft)
                .LineStyle = Excel.XlLineStyle.xlContinuous
                .ColorIndex = 24 'black
                .TintAndShade = 0
                .Weight = Excel.XlBorderWeight.xlThick
            End With
            With range.Borders(Excel.XlBordersIndex.xlEdgeTop)
                .LineStyle = Excel.XlLineStyle.xlContinuous
                .ColorIndex = 24 'black
                .TintAndShade = 0
                .Weight = Excel.XlBorderWeight.xlMedium
            End With
            With range.Borders(Excel.XlBordersIndex.xlEdgeBottom)
                .LineStyle = Excel.XlLineStyle.xlContinuous
                .ColorIndex = 24 'black
                .TintAndShade = 0
                .Weight = Excel.XlBorderWeight.xlMedium
            End With
            With range.Borders(Excel.XlBordersIndex.xlEdgeRight)
                .LineStyle = Excel.XlLineStyle.xlContinuous
                .ColorIndex = 24 'black
                .TintAndShade = 0
                .Weight = Excel.XlBorderWeight.xlMedium
            End With
            With range.Borders(Excel.XlBordersIndex.xlInsideVertical)
                .LineStyle = Excel.XlLineStyle.xlContinuous
                .ColorIndex = 24 'black
                .TintAndShade = 0
                .Weight = Excel.XlBorderWeight.xlThin
            End With
            With range.Borders(Excel.XlBordersIndex.xlInsideHorizontal)
                .LineStyle = Excel.XlLineStyle.xlContinuous
                .ColorIndex = 24 'black
                .TintAndShade = 0
                .Weight = Excel.XlBorderWeight.xlThin
            End With
            'With range.Interior
            '    .Pattern = Excel.XlPattern.xlPatternLinearGradient
            '    .Gradient.Degree = 60
            '    .Gradient.ColorStops.Clear()
            '    With .Gradient.ColorStops.Add(0)
            '        .ThemeColor = Excel.XlThemeColor.xlThemeColorAccent1
            '        .TintAndShade = 0
            '    End With
            'End With
            Dim arry As Object(,)
            arry = range.Value
            For r As Integer = 1 To arry.GetUpperBound(0)
                For c As Integer = 1 To arry.GetUpperBound(1)
                    Dim myRange As Object = arry(r, c)
                Next c
            Next r
            Dim SourceRange As Excel.Range = DirectCast(xlWorkSheet.UsedRange, Excel.Range)
            FormatAsTable(SourceRange, "Table1", "TableStyleMedium2")
            'range.Select()
            'range.ListObjects("Table1").TableStyle = "TableStyleLight2"
            If Not Directory.Exists("C:\Timer Tool Reports\") Then
                Directory.CreateDirectory("C:\Timer Tool Reports\")
            End If
            Dim dat As String = dtReport.Value.ToString("dd-MM-yyyy")
            Try
                xlWorkBook.SaveAs("C:\Timer Tool Reports\" & pathN & "_" & dat & ".xlsx") 'save our workbook
                MsgBox("You can find the file C:\Timer Tool Reports\" & pathN & ".xlsx")
            Catch ex As Exception
                MessageBox.Show("Exception " + ex.Message)
            Finally
                GC.Collect()
            End Try
            'releasing object references
            xlWorkBook = Nothing
            xlWorkBook = Nothing
            xlApp.Quit()
            xlApp = Nothing
            releaseObject(xlApp)
            releaseObject(xlWorkBook)
            releaseObject(xlWorkSheet)
            Clipboard.Clear()
            Dim proc As System.Diagnostics.Process
            For Each proc In System.Diagnostics.Process.GetProcessesByName("EXCEL")
                proc.Kill()
            Next
        End If

Public Sub FormatAsTable(SourceRange As Excel.Range, TableName As String, TableStyleName As String)
        SourceRange.Worksheet.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, SourceRange, System.Type.Missing, Excel.XlYesNoGuess.xlYes, System.Type.Missing).Name = TableName
        SourceRange.[Select]()
        SourceRange.Worksheet.ListObjects(TableName).TableStyle = TableStyleName
    End Sub

是否可以在WPF中使用此代码？当我试图实现它时，我收到错误：

Is it possible to use this code in WPF? When I'm trying to implement it I get an error in:

Dim headers = (From ch In GeneralReport.Columns _
                Let header = DirectCast(DirectCast(ch, DataGridViewColumn).HeaderCell, DataGridViewColumnHeaderCell) _
                Select header.Value).ToArray()
Dim headerText() As String = Array.ConvertAll(headers, Function(v) v.ToString)

Dim items() = (From r In GeneralReport.Rows _
        Let row = DirectCast(r, DataGridViewRow) _
        Where Not row.IsNewRow _
        Select (From cell In row.Cells _
            Let c = DirectCast(cell, DataGridViewCell) _
            Select c.Value).ToArray()).ToArray()

我不知道如何更换不受支持的会员。



感谢您的支持。

I have no idea how to change unsupported members.

Thank you for your support.

推荐答案

我想你错过了对 IEnumerable <的引用/ a> [ ^ ]

I think you missed the references to IEnumerable[^]

Imports System.Collections

和 IQuerable [ ^ ]

and IQuerable[^]

Imports System.Linq

有关名称空间的更多信息，请参阅：导入语句（.NET命名空间和类型） [ ^ ]。强烈建议使用添加引用对话框添加（或删除）引用 [< a href =http://msdn.microsoft.com/en-us/library/wkze6zky.aspxtarget =_ blanktitle =New Window> ^ ] - 如果你是初学者。



最后，我建议阅读这个 [ ^ ]之前你走得更远。按照浏览器窗口左侧的链接。

并且......如果你不知道它在做什么，请不要使用别人的代码。

For further information about namespaces, please see: Imports Statement (.NET Namespace and Type)[^]. It is strongly recommended to Add (or Remove) References By Using the Add Reference Dialog Box[^] - if you're a beginner.

Finally, i'd suggest to read this[^] before you step further. Follow the links on the left side of browser's window.
And... please, do not use someone else's code if you don't know what it's doing.

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