Excel Data Source in ASP.NET Web Forms
- 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 24.1\Components\Data
Configure an Excel Data Source
For example, your ASPX page contains the ASPxDashboard control which unique identifier is ASPxDashboardExcel:
<!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:
- Specify the ExcelDataSourceConnectionParameters at runtime. For example, set the FileConnectionParametersBase.FileName property.
- Assign the connection parameters to the e.ConnectionParameters property.
The selected event depends on the server-side API used in your app.
- 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.
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;
}
}
}
}
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.
