Excel Data Source

• Dec 04, 2020
• 2 minutes to read

This tutorial shows how to add the DashboardExcelDataSource to data source storage and make it available to users. The specified cell range on the defined 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 20.2\Components\Data directory.

2. In the Default.aspx.cs (or .vb) file, set resource access rules to allow access to all file directories.

   C#

   protected void Page_Load(object sender, EventArgs e) {
       // ...
   
       // Sets resource access rules.
       AccessSettings.DataResources.TrySetRules(DirectoryAccessRule.Allow());
   }
   

   VB.NET

   Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
       ' ...
   
       ' Sets resource access rules.
       AccessSettings.DataResources.TrySetRules(DirectoryAccessRule.Allow())
   End Sub    
   

   Note

   Refer to the IAccessRule topic for additional information about access to file directories and URLs.

3. In the same file, create a public method that returns the configured dashboard’s data source storage (DataSourceInMemoryStorage) and define the Excel data source.

   C#

   using System;
   using DevExpress.DashboardCommon;
   using DevExpress.DataAccess.Excel;
   
   public DataSourceInMemoryStorage CreateDataSourceStorage() {
       DataSourceInMemoryStorage dataSourceStorage = new DataSourceInMemoryStorage();
   
       // Registers an Excel data source.
       DashboardExcelDataSource excelDataSource = new DashboardExcelDataSource("Excel Data Source");
       excelDataSource.FileName = HostingEnvironment.MapPath(@"~/App_Data/Sales.xlsx");
       excelDataSource.SourceOptions = new ExcelSourceOptions(new ExcelWorksheetSettings("Sheet1"));
       dataSourceStorage.RegisterDataSource("excelDataSource", excelDataSource.SaveToXml());
   
       return dataSourceStorage;
   }
   

   VB.NET

   Imports System
   Imports DevExpress.DashboardCommon
   Imports DevExpress.DataAccess.Excel
   
   Public Function CreateDataSourceStorage() As DataSourceInMemoryStorage
       Dim dataSourceStorage As New DataSourceInMemoryStorage()
   
       ' Registers an Excel data source.
       Dim excelDataSource As New DashboardExcelDataSource("Excel Data Source")
       excelDataSource.FileName = HostingEnvironment.MapPath("~/App_Data/Sales.xlsx")
       excelDataSource.SourceOptions = New ExcelSourceOptions(New ExcelWorksheetSettings("Sheet1"))
       dataSourceStorage.RegisterDataSource("excelDataSource", excelDataSource.SaveToXml())
   
       Return dataSourceStorage
   End Function
   

4. Call the ASPxDashboard.SetDataSourceStorage method to configure the data source storage. Use the created CreateDataSourceStorage method as the SetDataSourceStorage parameter.

   C#

   using DevExpress.DashboardWeb;
   
   protected void Page_Load(object sender, EventArgs e) {
       // ...  
   
       // Configures the data source storage.
       ASPxDashboard1.SetDataSourceStorage(CreateDataSourceStorage());
   
       // Sets resource access rules.
       AccessSettings.DataResources.TrySetRules(DirectoryAccessRule.Allow());
   }
   

   VB.NET

   Imports DevExpress.DashboardWeb
   
   Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
       ' ...          
   
       '  Configures the data source storage.
       ASPxDashboard1.SetDataSourceStorage(CreateDataSourceStorage())
   
       ' Sets resource access rules.
       AccessSettings.DataResources.TrySetRules(DirectoryAccessRule.Allow())
   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 ASP.NET Web Forms Dashboard Control