Click here to Skip to main content
14,691,375 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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.
Posted

1 solution

I think you missed the references to IEnumerable[^]
Imports System.Collections

and IQuerable[^]
Imports System.Linq


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.
   
v3

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900