This example demonstrates how to export detail sections of a master-detail report to separate Excel sheets.
The report contains information about product categories. The name and description of each category are bound to the controls in the
Detail band. The name and price of each product are
bound to the controls in the DetailReport band.
On Print Preview, product categories span across one or two report pages. To export each product category to a separate sheet of an Excel file, do the following:
- Set the DetaiReport band's PageBreak property to AfterBand to print each product category on a new report page.
- Set the report's RollPaper property to true to fit each product category to one report page.
- Set the ExportMode property to Single File Page By Page to export each page of the report to a separate Excel sheet.
- (Optional) Use the XlSheetCreated event to change the default name of each sheet to the category name.
Files to Review
- Form1.cs (VB: Form1.vb)
- XtraReport1.cs (VB: XtraReport1.vb)
Documentation
More Examples
Example Code
C#using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using DevExpress.XtraPrinting;
using DevExpress.XtraReports.UI;
namespace reporting_example_export {
public partial class Form1 : DevExpress.XtraEditors.XtraForm {
public Form1() {
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e) {
var report = new XtraReport1();
// Set the DetailReport band's PageBreak property to AfterBand
// to print each product category on a new report page.
report.Bands[BandKind.DetailReport].PageBreak = PageBreak.AfterBand;
// Set the report's RollPaper property to true
// to fit each product category to one report page.
report.RollPaper = true;
// Use SingleFilePageByPage mode
// to export each page of the report to a separate sheet.
var xlsxExportOptions = new XlsxExportOptions {
ExportMode = XlsxExportMode.SingleFilePageByPage,
ShowGridLines = true
};
// Use the label's PrintOnPage event to save the names of all the categories to a list.
var categoriesNames = new List<string>();
report.Bands[BandKind.Detail].Controls["CategoryNameLabel"].PrintOnPage += (s1, e1) => {
var categoryNameLabel = (XRLabel)s1;
categoriesNames.Add(categoryNameLabel.Text);
};
// Use the XlSheetCreated event to change the default name of each sheet
// to the category name.
report.PrintingSystem.XlSheetCreated += (s2, e2) => {
e2.SheetName = categoriesNames[e2.Index];
};
// Export the report to Excel format. Save the exported file
// to the project directory.
var projectDirPath = Path.GetDirectoryName(
Path.GetDirectoryName(
System.IO.Directory.GetCurrentDirectory()
)
);
var xlsxFilePath = Path.Combine(projectDirPath, "ProductCategories.xlsx");
report.ExportToXlsx(xlsxFilePath, xlsxExportOptions);
// Open the exported file in the default Excel viewer.
Process process = new Process();
try {
process.StartInfo.FileName = xlsxFilePath;
process.Start();
process.WaitForInputIdle();
}
catch { }
}
}
}
C#using System;
using System.Collections;
using System.ComponentModel;
using System.Drawing;
using DevExpress.XtraReports.UI;
namespace reporting_example_export {
public partial class XtraReport1 : DevExpress.XtraReports.UI.XtraReport {
public XtraReport1() {
InitializeComponent();
}
private void CategoryNameLabel_BeforePrint(object sender, CancelEventArgs e) {
var categoryNameLabel = (XRLabel)sender;
var categoryName = categoryNameLabel.Text.Split('/')[0];
categoryNameLabel.Text = categoryName;
categoryNameLabel.Value = categoryName;
}
}
}