How to: Connect the Web Dashboard to an Excel Workbook

This example shows how to use DashboardExcelDataSource to connect the Web Dashboard to an Excel workbook and how to select the A1:L2000 range of cells located on the SalesPerson worksheet.

using System;
using System.Collections.Generic;
using System.Web.Hosting;
using DevExpress.DashboardCommon;
using DevExpress.DashboardWeb;
using DevExpress.DataAccess.Excel;

namespace WebDesignerExcelDataSource
{
    public partial class Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            DashboardFileStorage dashboardFileStorage = new DashboardFileStorage("~/App_Data/Dashboards");
            ASPxDashboard1.SetDashboardStorage(dashboardFileStorage);

            // Creates an Excel data source and selects the specific cell range from the SalesPerson worksheet.
            DashboardExcelDataSource excelDataSource = new DashboardExcelDataSource("Excel Data Source");
            excelDataSource.FileName = HostingEnvironment.MapPath(@"~/App_Data/ExcelDataSource.xlsx");
            ExcelWorksheetSettings worksheetSettings = new ExcelWorksheetSettings("SalesPerson", "A1:L2000");
            excelDataSource.SourceOptions = new ExcelSourceOptions(worksheetSettings);

            // Specifies the fields that will be available for the created data source.
            IExcelSchemaProvider schemaProvider = excelDataSource.GetService(typeof(IExcelSchemaProvider))
                as IExcelSchemaProvider;
            FieldInfo[] availableFields = schemaProvider.GetSchema(excelDataSource.FileName, null,
                ExcelDocumentFormat.Xlsx, excelDataSource.SourceOptions, System.Threading.CancellationToken.None);
            List<string> fieldsToSelect = new List<string>() { "CategoryName", "ProductName", "Country", "Quantity", 
                "Extended Price"};
            foreach (FieldInfo field in availableFields) {
                if (fieldsToSelect.Contains(field.Name)) {
                    excelDataSource.Schema.Add(field);
                }
                else {
                    field.Selected = false;
                    excelDataSource.Schema.Add(field);
                }
            }

            // Adds the created data source to the data source storage.
            DataSourceInMemoryStorage dataSourceStorage = new DataSourceInMemoryStorage();
            dataSourceStorage.RegisterDataSource("excelDataSource", excelDataSource.SaveToXml());
            ASPxDashboard1.SetDataSourceStorage(dataSourceStorage);
        }

        protected void ASPxDashboard1_ConfigureDataConnection(object sender, ConfigureDataConnectionWebEventArgs e) {
            if (e.DataSourceName == "Excel Data Source") {
                ((ExcelDataSourceConnectionParameters)e.ConnectionParameters).FileName =
                    HostingEnvironment.MapPath(@"~/App_Data/ExcelDataSource.xlsx");
            }
        }
    }
}
<%@ Page Language="vb" AutoEventWireup="true" CodeBehind="Default.aspx.vb" Inherits="WebDesignerExcelDataSource.Default" %>

<%@ Register Assembly="DevExpress.Dashboard.v16.2.Web, Version=16.2.1.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a"
    Namespace="DevExpress.DashboardWeb" TagPrefix="dx" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div style="position: absolute; top: 0; left: 0; right: 0; bottom: 0;">
        <dx:ASPxDashboard ID="ASPxDashboard1" runat="server" Width="100%" Height="100%" 
            onconfiguredataconnection="ASPxDashboard1_ConfigureDataConnection">
        </dx:ASPxDashboard>
    </div>
    </form>
</body>
</html>
Imports System
Imports System.Collections.Generic
Imports System.Web.Hosting
Imports DevExpress.DashboardCommon
Imports DevExpress.DashboardWeb
Imports DevExpress.DataAccess.Excel

Namespace WebDesignerExcelDataSource
    Partial Public Class [Default]
        Inherits System.Web.UI.Page

        Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
            Dim dashboardFileStorage As New DashboardFileStorage("~/App_Data/Dashboards")
            ASPxDashboard1.SetDashboardStorage(dashboardFileStorage)

            ' Creates an Excel data source and selects the specific cell range from the SalesPerson worksheet.
            Dim excelDataSource As New DashboardExcelDataSource("Excel Data Source")
            excelDataSource.FileName = HostingEnvironment.MapPath("~/App_Data/ExcelDataSource.xlsx")
            Dim worksheetSettings As New ExcelWorksheetSettings("SalesPerson", "A1:L2000")
            excelDataSource.SourceOptions = New ExcelSourceOptions(worksheetSettings)

            ' Specifies the fields that will be available for the created data source.
            Dim schemaProvider As IExcelSchemaProvider =
                TryCast(excelDataSource.GetService(GetType(IExcelSchemaProvider)), IExcelSchemaProvider)
            Dim availableFields() As FieldInfo = schemaProvider.GetSchema(excelDataSource.FileName,
                                                                          Nothing,
                                                                          ExcelDocumentFormat.Xlsx,
                                                                          excelDataSource.SourceOptions,
                                                                          System.Threading.CancellationToken.None)
            Dim fieldsToSelect As New List(Of String)() _
                From {"CategoryName", "ProductName", "Country", "Quantity", "Extended Price"}
            For Each field As FieldInfo In availableFields
                If fieldsToSelect.Contains(field.Name) Then
                    excelDataSource.Schema.Add(field)
                Else
                    field.Selected = False
                    excelDataSource.Schema.Add(field)
                End If
            Next field

            ' Adds the created data source to the data source storage.
            Dim dataSourceStorage As New DataSourceInMemoryStorage()
            dataSourceStorage.RegisterDataSource("excelDataSource", excelDataSource.SaveToXml())
            ASPxDashboard1.SetDataSourceStorage(dataSourceStorage)
        End Sub

        Protected Sub ASPxDashboard1_ConfigureDataConnection(ByVal sender As Object,
                                                             ByVal e As ConfigureDataConnectionWebEventArgs)
            If e.DataSourceName = "Excel Data Source" Then
                CType(e.ConnectionParameters, ExcelDataSourceConnectionParameters).FileName =
                    HostingEnvironment.MapPath("~/App_Data/ExcelDataSource.xlsx")
            End If
        End Sub
    End Class
End Namespace