how to export datagrid to excel

See more:

VB

WPF

DataGrid

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

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.