Bind a Report to an Excel Workbook (Runtime Sample)
- 5 minutes to read
Tip
Online Example: How to bind a report to an Excel Workbook at runtime
This example demonstrates how to bind a report to a Microsoft Excel workbook and specify the report layout at runtime.
Do the following to bind a report to an Excel file:
Create the ExcelDataSource class’s instance and specify the full path to the source Excel file using the ExcelDataSource.FileName property.
Select the required worksheet by creating a new ExcelWorksheetSettings object and using its ExcelWorksheetSettings.WorksheetName property.
Create the ExcelSourceOptions class’s instance to specify the required options for extracting data from the workbook.
Assign the ExcelSourceOptions.ImportSettings property to the previously created ExcelWorksheetSettings object.
Disable the ExcelSourceOptions.SkipHiddenRows and ExcelSourceOptions.SkipHiddenColumns options to include hidden rows and columns to the resulting data source.
Set the ExcelDataSource.SourceOptions property to this ExcelSourceOptions object.
The report engine automatically generates a data source schema by default. Do the following to manually define the data source schema if you need to customize the data fields’ display names and types:
- Create as many FieldInfo class instances as there are columns in the selected worksheet.
- Set the FieldInfo.Name and FieldInfo.Type properties of each data field, and specify whether to include the field in the resulting data source using the FieldInfo.Selected property.
- Add the created fields to the ExcelDataSource.Schema collection using its FieldInfoList.AddRange method. The order in which the fields are added should match the order of the columns in the source file.
- Assign the created data source to the report’s XtraReportBase.DataSource property.
- Provide data to report controls using the XRControl.ExpressionBindings or XRControl.DataBindings property depending on the report’s data binding mode.
Imports System
Imports System.Windows.Forms
Imports DevExpress.DataAccess.Excel
Imports DevExpress.XtraReports.UI
Imports DevExpress.XtraReports.Configuration
' ...
Namespace BindingReportToExcelWorkbook
Partial Public Class Form1
Inherits Form
Public Sub New()
InitializeComponent()
End Sub
Private Sub button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles button1.Click
' Create an empty report.
Dim report As New XtraReport()
' Create a new Excel data source.
Dim excelDataSource As New ExcelDataSource()
excelDataSource.FileName = "..//..//Northwind.xlsx"
' Select a required worksheet.
Dim excelWorksheetSettings As New ExcelWorksheetSettings()
excelWorksheetSettings.WorksheetName = "Sheet_Categories"
' Specify import settings.
Dim excelSourceOptions As New ExcelSourceOptions()
excelSourceOptions.ImportSettings = excelWorksheetSettings
excelSourceOptions.SkipHiddenRows = False
excelSourceOptions.SkipHiddenColumns = False
excelDataSource.SourceOptions = excelSourceOptions
' Create new fields and specify their settings.
Dim fieldCategoryID As FieldInfo = New FieldInfo With { _
.Name = "CategoryID", _
.Type = GetType(Double), _
.Selected = False _
}
Dim fieldCategoryName As FieldInfo = New FieldInfo With { _
.Name = "CategoryName", _
.Type = GetType(String) _
}
Dim fieldDescription As FieldInfo = New FieldInfo With { _
.Name = "Description", _
.Type = GetType(String) _
}
' Add the created fields to the data source schema in the order that matches the column order in the source file.
excelDataSource.Schema.AddRange(New FieldInfo() { fieldCategoryID, fieldCategoryName, fieldDescription })
' Assign the data source to the report.
report.DataSource = excelDataSource
' Add a detail band to the report.
Dim detailBand As New DetailBand()
detailBand.Height = 50
report.Bands.Add(detailBand)
' Create a new label.
Dim label As New XRLabel()
' Specify the label's binding depending on the data binding mode.
If Settings.Default.UserDesignerOptions.DataBindingMode = DataBindingMode.Bindings Then
label.DataBindings.Add("Text", Nothing, "CategoryName")
Else
label.ExpressionBindings.Add(New ExpressionBinding("BeforePrint", "Text", "[CategoryName]"))
End If
' Add the label to the detail band.
detailBand.Controls.Add(label)
' Show the report's print preview.
report.ShowPreview()
End Sub
End Class
End Namespace