Excel Data Source in ASP.NET Web Forms

Feb 27, 2023
3 minutes to read

This topic shows how to add the DashboardExcelDataSource to an in-memory data source storage, and make it available to users. The specified cell range on the defined worksheet supplies the dashboard with data.

Prepare Data

Add an Excel workbook or CSV file (.xls, .xlsx, or .csv formats) to your project.

This example uses the Sales.xlsx file. You can find other sample datasets in the following directory:

C:\Users\Public\Documents\DevExpress Demos 25.2\Components\Data

Configure an Excel Data Source

For example, your ASPX page contains the ASPxDashboard control which unique identifier is ASPxDashboardExcel:

ASPX

aspx

<!DOCTYPE html>

<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; bottom: 0; left: 0; right: 0">
            <dx:ASPxDashboard ID="ASPxDashboardExcel" runat="server" Width="100%" Height="100%">
            </dx:ASPxDashboard>
        </div>
    </form>
</body>
</html>

You can define the Excel data source in the code-behind page that has the .aspx.cs or .aspx.vb extension depending on the language used:

Create a DashboardExcelDataSource instance.
Specify the DashboardExcelDataSource.ConnectionName property to uniquely identify the data connection in code.
Set the ExcelDataSource.SourceOptions. For example, specify a worksheet that contains data.
Handle the ASPxDashboard.ConfigureDataConnection or DashboardConfigurator.ConfigureDataConnection event:
Register the created data source instance in the data source storage.

Note

A code-behind page is one of the variants where you can register the data sources. For example, you can also register them in the Global.asax.cs (Global.asax.vb) file.

C#
VB.NET

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

namespace WebFormsDashboardDataSources.Pages {
    public partial class ExcelDashboard : System.Web.UI.Page {
        protected void Page_Load(object sender, EventArgs e) {
        // ...
            // Create a data source storage.
            DataSourceInMemoryStorage dataSourceStorage = new DataSourceInMemoryStorage();

            // Register an Excel data source.
            DashboardExcelDataSource excelDataSource = new DashboardExcelDataSource("Excel Data Source");
            excelDataSource.ConnectionName = "excelDataConnection";
            excelDataSource.SourceOptions = new ExcelSourceOptions(new ExcelWorksheetSettings("Sheet1"));
            dataSourceStorage.RegisterDataSource("excelDataSource", excelDataSource.SaveToXml());

            // Set the configured data source storage.
            ASPxDashboardExcel.SetDataSourceStorage(dataSourceStorage);

            ASPxDashboardExcel.ConfigureDataConnection += ASPxDashboardExcel_ConfigureDataConnection;

            ASPxDashboardExcel.InitialDashboardId = "dashboardExcel";
        }

        private void ASPxDashboardExcel_ConfigureDataConnection(object sender, ConfigureDataConnectionWebEventArgs e) {
            if (e.ConnectionName == "excelDataConnection") {
                var excelParams = new ExcelDataSourceConnectionParameters(HostingEnvironment.MapPath(@"~/App_Data/Sales.xlsx"));
                e.ConnectionParameters = excelParams;
            }
        }
    }
}

Imports DevExpress.DashboardCommon
Imports DevExpress.DashboardWeb
Imports DevExpress.DataAccess.Excel
Imports System
Imports System.Web.Hosting

Namespace WebFormsDashboardDataSources.Pages

    Public Partial Class ExcelDashboard
        Inherits Web.UI.Page

        Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
        ' ...
            ' Create a data source storage.
            Dim dataSourceStorage As DataSourceInMemoryStorage = New DataSourceInMemoryStorage()
            ' Register an Excel data source.
            Dim excelDataSource As DashboardExcelDataSource = New DashboardExcelDataSource("Excel Data Source")
            excelDataSource.ConnectionName = "excelDataConnection"
            excelDataSource.SourceOptions = New ExcelSourceOptions(New ExcelWorksheetSettings("Sheet1"))
            dataSourceStorage.RegisterDataSource("excelDataSource", excelDataSource.SaveToXml())
            ' Set the configured data source storage.
            ASPxDashboardExcel.SetDataSourceStorage(dataSourceStorage)
            AddHandler ASPxDashboardExcel.ConfigureDataConnection, AddressOf Me.ASPxDashboardExcel_ConfigureDataConnection
            ASPxDashboardExcel.InitialDashboardId = "dashboardExcel"
        End Sub

        Private Sub ASPxDashboardExcel_ConfigureDataConnection(ByVal sender As Object, ByVal e As ConfigureDataConnectionWebEventArgs)
            If Equals(e.ConnectionName, "excelDataConnection") Then
                Dim excelParams = New ExcelDataSourceConnectionParameters(HostingEnvironment.MapPath("~/App_Data/Sales.xlsx"))
                e.ConnectionParameters = excelParams
            End If
        End Sub
    End Class
End Namespace

The Excel Data Source is now available in the Web Dashboard:

Users can bind dashboard items to data in the Web Dashboard’s UI.

Example

The example shows how to make a set of data sources available for users in the Web Dashboard application.

View Example: How to Register Data Sources for ASP.NET Web Forms Dashboard Control