Binding to Microsoft Excel Workbooks

  • 4 minutes to read

The Dashboard Designer allows you to select required data from Microsoft Excel workbooks (XLS, XLSX or XLSM). You can select all data from the specified worksheet or you can select the cell range referenced by the specified defined/table name.

Use the AccessSettings class to explicitly allow the path to the data file. To accomplish this, configure rules in the DataResources property to restrict file system access to the specified folders. You can call the SetRules(IAccessRule[]) method when the application starts to specify rules before a dashboard control sets its rules. The SetRules(IAccessRule[]) method can be called only once at the application startup. Otherwise, the method will raise an exception. Alternatively, you can use the TrySetRules(IAccessRule[]) method, which does not raise an exception.

Create a Data Source in the Data Source Wizard

To bind a dashboard to a Microsoft Excel workbook, do the following.

  1. Click the New Data Source button in the Data Source ribbon tab.


  2. On the first page of the invoked Data Source Wizard dialog, select Microsoft Excel workbook / CSV file and click Next.


  3. On the next page, locate the required workbook by clicking an ellipsis button and selecting the file.


    Click Next.


    If the workbook is protected by a password, the following window will be invoked.


    Specify a password in the Password field and click OK. Note that if you enable the Save password flag, the password will be saved to a dashboard definition as plain text.

  4. Then, specify import settings used to extract data from the workbook.


    The following options can be specified.

    • Use values of the first row as field names - Specifies whether to use the values of the first row as field names. If you disable this option, field names will be generated automatically.
    • Skip empty rows - Specifies whether to include the empty rows into the resulting data source.
    • Skip hidden rows - Specifies whether to ignore hidden rows when importing data to a data source.
    • Skip hidden columns - Specifies whether to ignore hidden columns when importing data to a data source.

    Click Next.

  5. On the next page, you can select the worksheet containing the required data, the table or the defined name referring to the specified cell range.


    Click Next.

  6. On the final page, you can select columns to be included to a data source and specify their settings. The Name column allows you to specify the column name while Type allows you to specify its type.


    Click Finish to create a data source. This creates the data source and displays its fields in the Data Source Browser.

Create a Data Source in Code

The DashboardExcelDataSource class allows you to extract data from Microsoft Excel workbooks (XLS, XLSX or XLSM) or CSV files stored on the disk or stream.

To extract data from the specified workbook or CSV file, do the following.

Finally, add the created DashboardExcelDataSource object to the Dashboard.DataSources collection.

The following code snippet shows how to create an Excel data source that gets data from the A1:L100 range of cells located on the Data worksheet in the SalesPerson.xlsx workbook.

using DevExpress.DashboardCommon;
    DashboardExcelDataSource excelDataSource = new DashboardExcelDataSource()
        FileName = "SalesPerson.xlsx",
        SourceOptions = new DevExpress.DataAccess.Excel.ExcelSourceOptions(
            new DevExpress.DataAccess.Excel.ExcelWorksheetSettings()
                WorksheetName = "Data",
                CellRange = "A1:L100"