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.
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" 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>
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