Click here to Skip to main content
Click here to Skip to main content
Go to top

Exporting Filtered Data from DevExpress Dashboard Viewer Object

, 11 Jun 2013
Rate this:
Please Sign up or sign in to vote.
A class to export the data from the DevExpress dashboard viewer object

Introduction

This code was designed to read the DevExpress Dashboard Viewer object and pull the filtered information, if any, and export it into a CSV file for the end user to play with. Some knowledge of the DevExpress Dashboard Designer tool and viewer object are needed to expand my code for your unique purposes.

Background

We wanted to implement the beautifully done dashboard control from DevExpress. It contains within it 98% of what we were looking for in a dashboard. The main item lacking was an ability to export the data presented on the dashboard into a file that the user could further design metrics with. My requirements were to be able to export the filtered data if it exists or all the data, and allow this class to be utilized in a windows or web environment. So, as with all things, I had a deadline to push this out so I apologize up front as my code isn't as streamlined as it could be, but it works with the dashboard types we've tried and is documented for others to expand as they see fit.

Using the Code

The first class is the worker class that will handle the requests. There are two methods that can be called from an outside class. The first is ExportToExcel into which you feed the dashboard viewer object, a file name, and a boolean on whether to print all data if no filter exists. This method and its sub-methods are the bulk of the code within the class. The other method (ExportImage) simply takes a snapshot of your screen and saves the image out. The DevExpress 2013 dashboard viewer object allows for the user to export the image of part of the dashboard, but for the life of me, I couldn't figure out how to export the whole thing and since it was easy enough, I just implemented it. Below is the completed class for easy copy and paste.

' ***********************************************************************
' Assembly         : Dashboard Demo
' Author           : butterj
' Created          : 06-07-2013
'
' Last Modified By : butterj
' Last Modified On : 06-07-2013
' ***********************************************************************
' <copyright file="DashboardExportUtility.vb" company="United Suppliers, Inc">
'     Copyright (c) United Suppliers, Inc. All rights reserved.
' </copyright>
' <summary></summary>
' ***********************************************************************
Imports System.Reflection
Imports System.Text
Imports DevExpress.DashboardCommon
Imports System.IO
Imports DevExpress.DashboardWin
Imports System.Collections.Generic
Imports System.Data
Imports System.Drawing
Imports System.Windows.Forms

''' <summary>
''' Class DashboardExportUtility allows to export data 
''' from the dashboard or the entire dashboard as an image
''' </summary>
''' <history rel="" name="butterj" 
''' date="6/7/2013 4:47:09 PM"></history>
Public Class DashboardExportUtility
    ''' <summary>
    ''' The dashboardfilter
    ''' </summary>
    Private dashboardfilter As New List(Of String)

    ''' <summary>
    ''' Exports filtered data to a CSV file. 
    ''' If there is no filter it will not produce a file.
    ''' </summary>
    ''' <history rel="" name="butterj" 
    ''' date="6/7/2013 4:47:08 PM"></history>
    ''' <param name="userDashboard">The dashboard viwer.</param>
    Public Sub ExportToExcel(ByVal userDashboard As DashboardViewer, _
    ByVal filePath As String, Optional ByVal printUnfilteredData As Boolean = False)
        Dim whereClause As New StringBuilder
        Dim sourceCounter As Integer = 0

        Try
            ' clear previous run
            dashboardfilter.Clear()

            ' Add the filter for these item types to a master filter list
            For Each item As DashboardItem In userDashboard.Dashboard.Items
                Select Case TypeName(item)
                    Case "CardDashboardItem", "DataDashboardItem", _
                    "GaugeDashboardItem", "ChartDashboardItem", _
                    "GridDashboardItem", "PieDashboardItem", _
                    "PivotDashboardItem"
                        Dim temp As String = FilterUsed(CType(item, DataDashboardItem))

                        If Not temp Is Nothing Then
                            Dim subFilter As String() = temp.Split({"And"}, _
                            StringSplitOptions.RemoveEmptyEntries)

                            For Each strVal As String In subFilter
                                If Not dashboardfilter.Contains(strVal.Trim) Then
                                    dashboardfilter.Add(strVal.Trim)
                                End If
                            Next
                        End If
                    Case Else
                End Select

            Next

            For Each source As DataSource In userDashboard.Dashboard.DataSources
                Dim completeTable As New DataTable
                Dim sourceTable As DB.DataProvider = CType(source.GetType.GetProperty_
                ("DataProvider", BindingFlags.Instance Or BindingFlags.Public)._
                GetValue(source, Nothing), DB.DataProvider)

                whereClause.Clear()

                If Not source.Data Is Nothing Then
                    ' add filter information for the current table
                    For Each table As DevExpress.Xpo.DB.DBTable In sourceTable.Tables
                        For Each item As String In dashboardfilter
                            If item.Contains(table.Name) Then
                                whereClause.Append(String.Format("{0} AND ", item))
                            End If
                        Next

                    Next

                    ' If nothing in the filter ask user if they wish to export, 
                    ' if not, move to next data source
                    If whereClause.Length > 0 Then
                        whereClause.Remove(InStrRev(whereClause.ToString, "AND") - 1, 3)
                    Else
                        If Not printUnfilteredData Then
                            Continue For
                        Else
                            ' Clear out anything in the filter
                            whereClause.Clear()
                        End If

                    End If

                    completeTable = BuildDataTable(source)

                    Using output As New StreamWriter(String.Format_
                    ("{0}({1}).csv", filePath, source.Name), False)
                        output.AutoFlush = True
                        WriteOutHeaders(completeTable.Columns, output)
                        WriteOutRows(completeTable.Select(ReplaceAll(whereClause.ToString)), _
			output)

                        output.Close()
                    End Using
                End If

                sourceCounter += 1
            Next

        Catch ex As Exception
            Throw New UnauthorizedAccessException(String.Format_
            ("{0} {1} {2} {3} {4}", ex.Message, vbCrLf, _
            ex.InnerException, vbCrLf, ex.StackTrace))
        End Try
    End Sub

    ''' <summary>
    ''' Builds the data table. Table will contain all data from the data source
    ''' </summary>
    ''' <history rel="" name="butterj" 
    ''' date="6/7/2013 4:47:08 PM"></history>
    ''' <param name="source">The data source to iterate through.</param>
    ''' <returns>DataTable.</returns>
    Private Function BuildDataTable(ByVal source As DataSource) As DataTable
        Dim table As New DataTable
        Dim columnCounter As Integer = 0

        For Each prop As System.ComponentModel.PropertyDescriptor In _
        (CType(source.Data, DevExpress.DashboardCommon.DB.DataView)).ColumnDescriptors
            table.Columns.Add(prop.Name)
            table.Columns(table.Columns.Count - 1).Caption = _
            prop.Name.Substring(InStr(prop.Name, "."))
        Next


        For Each row As DevExpress.Xpo.DB.SelectStatementResultRow In _
        (CType(source.Data, DevExpress.DashboardCommon.DB.DataView)).DataRows
            Dim tempRow As DataRow = table.NewRow

            columnCounter = 0

            For Each value As String In CType(row.Values, Array)
                If value Is Nothing Then
                    tempRow(columnCounter) = ""
                Else
                    tempRow(columnCounter) = ReplaceAll(value.ToString.Trim)
                End If

                columnCounter += 1
            Next

            table.Rows.Add(tempRow)
        Next

        Return table
    End Function

    ''' <summary>
    ''' Writes the out table headers.
    ''' </summary>
    ''' <history rel="" name="butterj" 
    ''' date="6/7/2013 4:47:08 PM"></history>
    ''' <param name="cols">The cols.</param>
    ''' <param name="output">The output.</param>
    Private Sub WriteOutHeaders(ByVal cols As DataColumnCollection, _
    ByVal output As StreamWriter)
        Dim row As New StringBuilder

        For Each col As DataColumn In cols
            row.Append(String.Format("{0},", col.Caption))
        Next

        row.Remove(InStrRev(row.ToString, ",") - 1, 1)

        output.WriteLine(row.ToString)
    End Sub

    ''' <summary>
    ''' Writes the out table rows.
    ''' </summary>
    ''' <history rel="" name="butterj" 
    ''' date="6/7/2013 4:47:08 PM"></history>
    ''' <param name="table">The table.</param>
    ''' <param name="output">The output.</param>
    Private Sub WriteOutRows(ByVal table As DataRow(), ByVal output As StreamWriter)
        Dim rowValues As New StringBuilder

        For Each row As DataRow In table
            rowValues.Clear()

            For index As Integer = 0 To row.ItemArray.Length - 1
                rowValues.Append(String.Format("{0},", row(index)))
            Next

            rowValues.Remove(InStrRev(rowValues.ToString, ",") - 1, 1)

            output.WriteLine(rowValues.ToString)
        Next
    End Sub

    ''' <summary>
    ''' Replaces all chars in a string that could affect the csv output.
    ''' </summary>
    ''' <history rel="" name="butterj" 
    ''' date="6/7/2013 4:47:08 PM"></history>
    ''' <param name="oldValue">The old value.</param>
    ''' <returns>System.String.</returns>
    Private Function ReplaceAll(ByVal oldValue As String) As String
        Dim returnVal As New StringBuilder(oldValue)
        Dim invalidChars As Char() = {CChar(","), _
        CChar(vbCrLf), CChar(vbLf), CChar(vbCr)}

        For Each item As Char In invalidChars
            If returnVal.ToString.Contains(item) Then
                returnVal.Remove(InStr(returnVal.ToString, item) - 1, 1)
            End If
        Next

        Return returnVal.ToString
    End Function

    ''' <summary>
    ''' Pull the master filters the used.
    ''' </summary>
    ''' <history rel="" name="butterj" 
    ''' date="6/7/2013 4:47:07 PM"></history>
    ''' <param name="item">The dashboard chart item.</param>
    ''' <returns>System.String.</returns>
    Private Function FilterUsed(ByVal item As DataDashboardItem) As String
        Try
            Dim prop As String = item.GetType().GetProperty_
            ("ActualFilterCriteria", BindingFlags.NonPublic _
            Or BindingFlags.Instance).GetValue(item, Nothing).ToString
            Dim name As New StringBuilder(item.GetType.GetProperty_
            ("NamePrefix", BindingFlags.Static Or _
            BindingFlags.NonPublic Or BindingFlags.Public Or _
            BindingFlags.Instance Or BindingFlags.DeclaredOnly).GetValue_
            (item, Nothing).ToString)

            name.Append(" ")
            name.Append(item.Dashboard.Items.IndexOf(item) + 1)

            If Not prop Is Nothing Then
                Dim filterValue As New StringBuilder(prop)

                For Each x In CType(item.GetType.GetProperty_
                ("DataItemRepository", BindingFlags.NonPublic Or _
                BindingFlags.Instance).GetValue(item, Nothing), _
                IEnumerable(Of KeyValuePair(Of DataItem, String)))

                    If filterValue.ToString.Contains(x.Value) Then
                        filterValue.Replace(x.Value, x.Key.DataMember)
                    End If
                Next

                With filterValue
                    .Replace("{", "")
                    .Replace("}", "")
                End With

                Return filterValue.ToString
            End If

            Return Nothing
        Catch ex As Exception
            Return Nothing
        End Try

    End Function

    ''' <summary>
    ''' Saves the dashboard viewer as an image. Windows Forms only method!!
    ''' </summary>
    ''' <history rel="" name="butterj" 
    ''' date="6/7/2013 4:47:07 PM"></history>
    ''' <param name="screen">The screen.</param>
    Public Sub ExportImage(screen As Rectangle, ByVal filePath As String)
        Dim screenshot As New Bitmap(screen.Width, screen.Height, _
        System.Drawing.Imaging.PixelFormat.Format32bppArgb)
        Dim graph As Graphics
        Dim pict As New PictureBox

        Application.DoEvents()
        Threading.Thread.Sleep(100)

        graph = Graphics.FromImage(screenshot)
        graph.CopyFromScreen(screen.X, screen.Y, 0, 0, _
        screen.Size, CopyPixelOperation.SourceCopy)
        pict.Image = screenshot

        pict.Image.Save(filePath & ".jpeg")
    End Sub
End Class

In order to interact with the class, I have a form with the dashboard viewer object on it. When the form opens, it receives an XML file that the dashboard will load and display to the end user. They can right-click on the titles of any chart control to get a pop-up menu and select the options.

Imports System.Text
Imports System.Reflection
Imports DevExpress.DashboardCommon
Imports System.IO

Public Class frmPreview
    Private dashUtil As New DashboardExportUtility

    Private Sub frmPreview_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim msg As New OpenFileDialog() With {.Title = "Select Dashboard File", _
            .Filter = "XML|*.xml|All|*.*", .Multiselect = False}

        If msg.ShowDialog() = Windows.Forms.DialogResult.OK Then
            userDashboard.LoadDashboard(msg.FileName)
            AddClickEvent(userDashboard.Controls)
        Else
            Me.Close()
        End If

    End Sub

    Private Sub AddClickEvent(parent As Control.ControlCollection)
        For Each item As Control In parent

            If item.Controls.Count > 0 Then
                AddClickEvent(item.Controls)
            End If

            AddHandler item.MouseDown, AddressOf userDashboard_MouseDown
        Next
    End Sub

    Private Sub userDashboard_MouseDown(sender As Object, _
                   e As MouseEventArgs) Handles Me.MouseDown
        If e.Button = Windows.Forms.MouseButtons.Right Then
            Dim menu As New ContextMenuStrip()

            With menu
                .Items.Add("Export Data to Excel")
                .Items.Add("Save Dashboard as image")

                AddHandler .Items(0).Click, AddressOf ExportToExcel
                AddHandler .Items(1).Click, AddressOf ExportImage

                .DropShadowEnabled = True
                .LayoutStyle = ToolStripLayoutStyle.Flow
                .Show(Control.MousePosition)
            End With

        End If
    End Sub

    Private Sub ExportToExcel(sender As Object, e As EventArgs)
        Dim save As New SaveFileDialog() With {.InitialDirectory = _
          Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)}

        If save.ShowDialog() = Windows.Forms.DialogResult.OK Then
            dashUtil.ExportToExcel(Me.userDashboard, save.FileName, True)
        End If
    End Sub

    Private Sub ExportImage(sender As Object, e As EventArgs)
        Dim save As New SaveFileDialog() With {.InitialDirectory = _
          Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)}

        If save.ShowDialog() = Windows.Forms.DialogResult.OK Then
            dashUtil.ExportImage(New Rectangle(Me.Location, Me.Size), save.FileName)
        End If
    End Sub
End Class

That is really it, in terms of pulling data from the dashboard viewer. I hope it gives you a push in the right direction in terms of what your unique situation requires.

License

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

Share

About the Author

hawk3434

United States United States
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web02 | 2.8.140926.1 | Last Updated 11 Jun 2013
Article Copyright 2013 by hawk3434
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid