locked
Export all pages to Excel RRS feed

  • Question

  • I'm using a utility to export my datagrids to excel.  How can I make this utility export all pages?
     
    Imports System.Windows
    Imports System.Windows.Data
    Imports System.Windows.Media
    Imports System.Windows.Controls
    Imports System.Collections
    Imports System.Collections.Generic
    Imports System.Collections.ObjectModel
    Imports System.Linq
    Imports System.Text
    Imports System.IO
    Imports System.Reflection
    Imports System.Xml.Linq
    
    Public NotInheritable Class DataGridExtensions
        Private Sub New()
        End Sub
        Public Shared Sub Export(ByVal dg As DataGrid)
            ExportDataGrid(dg)
        End Sub
    
        Public Shared Sub ExportDataGrid(ByVal dGrid As DataGrid)
            Dim objSFD As New SaveFileDialog() With { _
             .DefaultExt = "csv", _
             .Filter = "CSV Files (*.csv)|*.csv|Excel XML (*.xml)|*.xml|All files (*.*)|*.*", _
             .FilterIndex = 1 _
            }
            If objSFD.ShowDialog() = True Then
                Dim strFormat As String = objSFD.SafeFileName.Substring(objSFD.SafeFileName.IndexOf("."c) + 1).ToUpper()
                Dim strBuilder As New StringBuilder()
                If dGrid.ItemsSource Is Nothing Then
                    Return
                End If
                Dim lstFields As New List(Of String)()
                If dGrid.HeadersVisibility = DataGridHeadersVisibility.Column OrElse dGrid.HeadersVisibility = DataGridHeadersVisibility.All Then
                    For Each dgcol As DataGridColumn In dGrid.Columns
                        lstFields.Add(FormatField(dgcol.Header.ToString(), strFormat))
                    Next
                    BuildStringOfRow(strBuilder, lstFields, strFormat)
                End If
                For Each data As Object In dGrid.ItemsSource
                    lstFields.Clear()
                    For Each col As DataGridColumn In dGrid.Columns
                        Dim strValue As String = ""
                        Dim objBinding As Binding = Nothing
                        If TypeOf col Is DataGridBoundColumn Then
                            objBinding = TryCast(col, DataGridBoundColumn).Binding
                        End If
                        If TypeOf col Is DataGridTemplateColumn Then
                            'This is a template column... let us see the underlying dependency object
                            Dim objDO As DependencyObject = TryCast(col, DataGridTemplateColumn).CellTemplate.LoadContent()
                            Dim oFE As FrameworkElement = DirectCast(objDO, FrameworkElement)
                            Dim oFI As FieldInfo = oFE.[GetType]().GetField("TextProperty")
                            If oFI IsNot Nothing Then
                                If oFI.GetValue(Nothing) IsNot Nothing Then
                                    If oFE.GetBindingBLOCKED EXPRESSION IsNot Nothing Then
                                        objBinding = oFE.GetBindingBLOCKED EXPRESSION.ParentBinding
                                    End If
                                End If
                            End If
                        End If
                        If objBinding IsNot Nothing Then
                            If objBinding.Path.Path <> "" Then
                                Dim pi As PropertyInfo = data.[GetType]().GetProperty(objBinding.Path.Path)
                                If pi IsNot Nothing Then
                                    If pi.GetValue(data, Nothing) IsNot Nothing Then
                                        strValue = pi.GetValue(data, Nothing).ToString()
                                    Else
                                        strValue = ""
                                    End If
                                End If
    
                            End If
                            If objBinding.Converter IsNot Nothing Then
                                If strValue <> "" Then
                                    strValue = objBinding.Converter.Convert(strValue, GetType(String), objBinding.ConverterParameter, objBinding.ConverterCulture).ToString()
                                Else
                                    strValue = objBinding.Converter.Convert(data, GetType(String), objBinding.ConverterParameter, objBinding.ConverterCulture).ToString()
                                End If
                            End If
                        End If
                        lstFields.Add(FormatField(strValue, strFormat))
                    Next
                    BuildStringOfRow(strBuilder, lstFields, strFormat)
                Next
                Dim sw As New StreamWriter(objSFD.OpenFile())
                If strFormat = "XML" Then
                    'Let us write the headers for the Excel XML
                    sw.WriteLine("<?xml version=""1.0"" encoding=""utf-8""?>")
                    sw.WriteLine("<?mso-application progid=""Excel.Sheet""?>")
                    sw.WriteLine("<Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet"">")
                    sw.WriteLine("<DocumentProperties xmlns=""urn:schemas-microsoft-com:office:office"">")
                    sw.WriteLine("<Author>Arasu Elango</Author>")
                    sw.WriteLine("<Created>" & DateTime.Now.ToLocalTime().ToLongDateString() & "</Created>")
                    sw.WriteLine("<LastSaved>" & DateTime.Now.ToLocalTime().ToLongDateString() & "</LastSaved>")
                    sw.WriteLine("<Company>Atom8 IT Solutions (P) Ltd.,</Company>")
                    sw.WriteLine("<Version>12.00</Version>")
                    sw.WriteLine("</DocumentProperties>")
                    sw.WriteLine("<Worksheet ss:Name=""Silverlight Export"" xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet"">")
                    sw.WriteLine("<Table>")
                End If
                sw.Write(strBuilder.ToString())
                If strFormat = "XML" Then
                    sw.WriteLine("</Table>")
                    sw.WriteLine("</Worksheet>")
                    sw.WriteLine("</Workbook>")
                End If
                sw.Close()
            End If
        End Sub
        Private Shared Sub BuildStringOfRow(ByVal strBuilder As StringBuilder, ByVal lstFields As List(Of String), ByVal strFormat As String)
            Select Case strFormat
                Case "XML"
                    strBuilder.AppendLine("<Row>")
                    strBuilder.AppendLine([String].Join(vbCr & vbLf, lstFields.ToArray()))
                    strBuilder.AppendLine("</Row>")
                    Exit Select
                Case "CSV"
                    strBuilder.AppendLine([String].Join(",", lstFields.ToArray()))
                    Exit Select
            End Select
        End Sub
        Private Shared Function FormatField(ByVal data As String, ByVal format As String) As String
            Select Case format
                Case "XML"
                    Return [String].Format("<Cell><Data ss:Type=""String"">{0}</Data></Cell>", data)
                Case "CSV"
                    Return [String].Format("""{0}""", data.Replace("""", """""""").Replace(vbLf, "").Replace(vbCr, ""))
            End Select
            Return data
        End Function


     

    Wednesday, September 22, 2010 3:19 PM

All replies

  • Hi

    Please define what your "pages" are in this context.

    Wednesday, September 22, 2010 4:36 PM
  • I'm using data grids with data pagers.  When I export, only one page is exporting and not all pages in the grid. 

    Wednesday, September 22, 2010 4:58 PM
  • The obvious workaround to that problem is to load a Collapsed DataGrid in the background with the entire collection as ItemSource. Then when the user requests the export, that Collapsed DataGrid is what should be passed to the export method instead of the visible paged one.

    I know this is not very cool from a performance perspective, but it gets the job done...

    Wednesday, September 22, 2010 5:15 PM
  • Doesn't work well with large amounts of data.

    Thursday, September 23, 2010 12:38 PM
  • Indeed it doesn't but that's the name of the game if you wish to use that lovely export from DataGrid functionality.

    You could emulate a "downloading export file" feature by not loading the background DataGrid and in particular it's ItemsSource, until the user clicks the export button and from the Click Event start a BusyIndicator with a suitable text. When the loading is completed you pop the SaveFileDialog.

    Thursday, September 23, 2010 12:46 PM
  • I'm going to try this one out and get back to you. I have a few more things to fix.

    Thursday, September 23, 2010 6:38 PM