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.
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
Public Class DashboardExportUtility
Private dashboardfilter As New List(Of String)
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
dashboardfilter.Clear()
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
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 whereClause.Length > 0 Then
whereClause.Remove(InStrRev(whereClause.ToString, "AND") - 1, 3)
Else
If Not printUnfilteredData Then
Continue For
Else
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
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
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
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
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
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
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.
