Excel Data Source in ASP.NET Core

• Feb 27, 2023
• 2 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 24.1\Components\Data

Configure an Excel Data Source

Create a Data Source

To create an Excel Data Source, follow the steps below:

• 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 DashboardConfigurator.ConfigureDataConnection event:
  • Specify the ExcelDataSourceConnectionParameters at runtime. For example, set the FileConnectionParametersBase.FileName property.
  • Assign the connection parameters to the e.ConnectionParameters property.

using DevExpress.DashboardCommon;
using DevExpress.DashboardWeb;
using DevExpress.DataAccess.Excel;

DashboardExcelDataSource excelDataSource = new DashboardExcelDataSource("Excel Data Source");
excelDataSource.ConnectionName = "excelDataConnection";
excelDataSource.SourceOptions = new ExcelSourceOptions(new ExcelWorksheetSettings("Sheet1"));

configurator.ConfigureDataConnection += (s, e) => {
    if (e.ConnectionName == "excelDataConnection") {
        ExcelDataSourceConnectionParameters excelParameters = (ExcelDataSourceConnectionParameters)e.ConnectionParameters;
        excelParameters.FileName = fileProvider.GetFileInfo("Data/Sales.xlsx").PhysicalPath;
    }
};

Register the Data Source in the Storage

Call the DataSourceInMemoryStorage.RegisterDataSource method to register the data source in the data source storage. Call the DashboardConfigurator.SetDataSourceStorage to specify the data source storage for the Web Dashboard.

// Create a data source storage.
DataSourceInMemoryStorage dataSourceStorage = new DataSourceInMemoryStorage();

// Register the Excel data source.
dataSourceStorage.RegisterDataSource("excelDataSource", excelDataSource.SaveToXml());

// Register the storage for the Web Dashboard.
configurator.SetDataSourceStorage(dataSourceStorage);

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

Users can now 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 Core Dashboard Control