Excel Data Source

• Feb 27, 2023
• 2 minutes to read

This tutorial 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 worksheet supplies the dashboard with data.

1. In your application, add the Financial.xls file to the App_Data folder from the C:\Users\Public\Documents\DevExpress Demos 24.2\Components\Data directory.

2. In the dashboard configuration file (for example, DashboardConfig.cs), create a public method that returns the configured dashboard’s data source storage (DataSourceInMemoryStorage) and define the Excel data source.

   C#

   using DevExpress.DashboardWeb;
   
   public static DataSourceInMemoryStorage CreateDataSourceStorage() {
       DataSourceInMemoryStorage dataSourceStorage = new DataSourceInMemoryStorage();
   
       DashboardExcelDataSource excelDataSource = new DashboardExcelDataSource("Excel Data Source");
       excelDataSource.SourceOptions = new ExcelSourceOptions(new ExcelWorksheetSettings("Current_Stocks"));
       dataSourceStorage.RegisterDataSource("excelDataSource", excelDataSource.SaveToXml());
   
       return dataSourceStorage;
   }
   

   VB.NET

   Imports DevExpress.DashboardWeb
   
   Public Shared Function CreateDataSourceStorage() As DataSourceInMemoryStorage
       Dim dataSourceStorage As New DataSourceInMemoryStorage()
   
       Dim excelDataSource As New DashboardExcelDataSource("Excel Data Source")
       excelDataSource.SourceOptions = New ExcelSourceOptions(New ExcelWorksheetSettings("Current_Stocks"))
       dataSourceStorage.RegisterDataSource("excelDataSource", excelDataSource.SaveToXml())
   
       Return dataSourceStorage
   End Function
   

3. Call the DashboardConfigurator.SetDataSourceStorage method to configure the data source storage. Use the created CreateDataSourceStorage method as the SetDataSourceStorage parameter and handle the DashboardConfigurator.ConfigureDataConnection event to pass the connection parameters to the Excel data source.

   C#

   using DevExpress.DashboardWeb;
   using DevExpress.DataAccess.Excel;
   
   public static void RegisterService(RouteCollection routes) {
       routes.MapDashboardRoute("dashboardControl", "DefaultDashboard");
   
       // ...
   
       DashboardConfigurator.Default.SetDataSourceStorage(CreateDataSourceStorage());        
       DashboardConfigurator.Default.ConfigureDataConnection += Default_ConfigureDataConnection;
   }
   private static void Default_ConfigureDataConnection(object sender, ConfigureDataConnectionWebEventArgs e) {
       if(e.DataSourceName.Contains("Excel Data Source")) {
           var excelParams = new ExcelDataSourceConnectionParameters(HostingEnvironment.MapPath(@"~/App_Data/_Financial.xls"));
           e.ConnectionParameters = excelParams;
       }
   }
   

   VB.NET

   Imports DevExpress.DashboardWeb
   Imports DevExpress.DataAccess.Excel
   
   Public Shared Sub RegisterService(ByVal routes As RouteCollection)
       routes.MapDashboardRoute("dashboardControl", "DefaultDashboard")
   
       ' ...
   
       DashboardConfigurator.Default.SetDataSourceStorage(CreateDataSourceStorage())
       AddHandler DashboardConfigurator.Default.ConfigureDataConnection, AddressOf Default_ConfigureDataConnection
   End Sub
   Private Shared Sub Default_ConfigureDataConnection(ByVal sender As Object, ByVal e As ConfigureDataConnectionWebEventArgs)
       If e.DataSourceName.Contains("Excel Data Source") Then
           Dim excelParams As New ExcelDataSourceConnectionParameters(HostingEnvironment.MapPath("~/App_Data/_Financial.xlsx"))
           e.ConnectionParameters = excelParams
       End If
   End Sub
   

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 the ASP.NET MVC Dashboard Extension