Export to XLS
- 11 minutes to read
This document describes how to export a report document to XLS format.
You can export a report from the Report Designer’s Preview and the Document Viewer on all supported platforms (WinForms, WPF, ASP.NET Web Forms, ASP.NET MVC, and ASP.NET Core). To do this, expand the Export Document drop-down list and select XLS File. Specify export options in the invoked dialog, and click OK.
To export a report document to XLS format in code, use one of the following approaches:
Export a Report
Call an XtraReport.ExportToXls overloaded method to export a report. To specify export options, create a XlsExportOptions class instance and pass this instance to the invoked method, or use the XtraReport‘s ExportOptions.Xls property.
using System; using DevExpress.XtraPrinting; using DevExpress.XtraReports.UI; // ... XtraReport report = new XtraReport() { Name = "HelloWorld", Bands = { new DetailBand(){ Controls={ new XRLabel(){ Text="Hello World!" } } } } }; // xlsExportFile specifies the exported file's name and path. The user's Downloads folder is used as the default path. string xlsExportFile = Environment.GetFolderPath(Environment.SpecialFolder.UserProfile) + @"\Downloads\" + report.Name + ".xls"; // Specify export options to export the report. // Uncomment the lines below to specify export options in an XlsExportOptions class instance. // XlsExportOptions xlsExportOptions = new XlsExportOptions(); // xlsExportOptions.ExportMode = XlsExportMode.SingleFile; // report.ExportToXls(xlsExportFile, xlsExportOptions); // Comment the lines below to if you specified export options in an XlsExportOptions class instance above. report.ExportOptions.Xls.ExportMode = XlsExportMode.SingleFile; report.ExportToXls(xlsExportFile);
Export a Document
Call a PrintingSystem.ExportToXls overloaded method to export a document. To specify export options, create a XlsExportOptions class instance and pass this instance to the invoked method, or use PrintingSystem‘s ExportOptions.Xls property.
using System; using DevExpress.XtraPrinting; using DevExpress.XtraReports.UI; // ... XtraReport report = new XtraReport() { Name = "HelloWorld", Bands = { new DetailBand(){ Controls={ new XRLabel(){ Text="Hello World!" } } } } }; // Create a document from the report. report.CreateDocument(); // xlsExportFile specifies the exported file's name and path. The user's Downloads folder is used as the default path. string xlsExportFile = Environment.GetFolderPath(Environment.SpecialFolder.UserProfile) + @"\Downloads\" + report.Name + ".xls"; // Specify export options and export the document. // Uncomment the lines below to specify export options in an XlsExportOptions class instance. // XlsExportOptions xlsExportOptions = new XlsExportOptions(); // xlsExportOptions.ExportMode = XlsExportMode.SingleFile; // report.PrintingSystem.ExportToXls(xlsExportFile, xlsExportOptions); // Comment the lines below to if you specified export options in an XlsExportOptions class instance above. report.PrintingSystem.ExportOptions.Xls.ExportMode = XlsExportMode.SingleFile; report.PrintingSystem.ExportToXls(xlsExportFile);
Export a Control
Call a PrintingLink.ExportToXls overloaded method to export a control. To specify export options, create a XlsExportOptions class instance and pass this instance to the invoked method.
using System; using DevExpress.XtraPrinting; using DevExpress.XtraReports.UI; // ... DevExpress.XtraCharts.ChartControl chart = new DevExpress.XtraCharts.ChartControl() { Name = "IncomeByQuarter", Series = { new DevExpress.XtraCharts.Series("2019", DevExpress.XtraCharts.ViewType.Bar) } }; // Create a printing link. PrintingSystem printingSystem = new PrintingSystem(); PrintableComponentLink link = new PrintableComponentLink(); printingSystem.Links.Add(link); link.Component = chart; // xlsExportFile specifies the exported file's name and path. The user's Downloads folder is used as the default path. string xlsExportFile = Environment.GetFolderPath(Environment.SpecialFolder.UserProfile) + @"\Downloads\" + chart.Name + ".xls"; // Specify export options and export the control. XlsExportOptions xlsExportOptions = new XlsExportOptions(); xlsExportOptions.ExportMode = XlsExportMode.SingleFile; link.ExportToXls(xlsExportFile, xlsExportOptions);
Tip
Code example: How to export a report to XLS.
File Options
Use the XlsExportOptions.ExportMode property to determine the report document export mode:
Single File
The report is exported to one Excel file that contains one sheet. This sheet combines all report pages. The report’s page headers, footers, top and bottom margins appear only once, at the beginning and end of the resulting sheet.
Single File Page-by-Page
Every report page is saved as an individual sheet. Each sheet uses the XlExportOptionsBase.SheetName value and a consecutive index for its name. The report’s page headers, footers, and top and bottom margins appear on every page (the same way as in the report’s Preview).
Different Files
The report is exported to multiple files. Each file contains a sheet that corresponds to a report document page. The report’s page headers, footers, and top and bottom margins appear on every sheet (the same as in the report’s Preview).
Note
You cannot export reports that use other reports’ pages as a Single File. The exported file contains only one of the merged reports.
As a workaround:
- Use subreports to combine multiple reports into a single document.
- Export all your reports to separate XLS files, then join these files in a single file.
An Excel worksheet cell can contain up to 32,767 characters. If a control’s Text property is longer than 32,767 characters, the excess text is truncated in Excel files. No errors or warnings are displayed. See Excel specifications and limits for more information.
Online example: How to export a report to the different sheets in the XLS file.
Document Content Options
Enable the XlsExportOptions.RawDataMode option to export plain data and ignore images, graphic content, font and appearance settings.
Enable the PageByPageExportOptionsBase.RasterizeImages property to rasterize vector images in pictures, charts, and bar codes in the resulting Excel file. You can use the PageByPageExportOptionsBase.RasterizationResolution property to specify image resolution.
Data Shaping Options
The XlsExportOptionsEx.ExportType property toggles the following export types:
Data-Aware Export
This export type is optimized for data analysis within Microsoft Excel. The data shaping options applied within the control are retained in XLS documents: native Excel grouping, sorting and filtering settings, improved support for Excel formulas.
WinForms printing links and WPF printing links are not supported in this export type. To use data-aware export, call a control’s equivalent methods (for instance, the GridControl.ExportToXls method).
WYSIWYG Export
Control cells’ layout is retained in the resulting Excel document. Data shaping options may not be retained. For instance, summary values can be exported as text strings instead of formulas.
using System;
using DevExpress.XtraPrinting;
using DevExpress.XtraReports.UI;
// ...
DevExpress.XtraCharts.ChartControl chart = new DevExpress.XtraCharts.ChartControl() {
Name = "IncomeByQuarter",
Series = {
new DevExpress.XtraCharts.Series("2019", DevExpress.XtraCharts.ViewType.Bar)
}
};
// Create a printing link.
PrintingSystem printingSystem = new PrintingSystem();
PrintableComponentLink link = new PrintableComponentLink();
printingSystem.Links.Add(link);
link.Component = chart;
// xlsExportFile specifies the exported file's name and path. The user's Downloads folder is used as the default path.
string xlsExportFile = Environment.GetFolderPath(Environment.SpecialFolder.UserProfile) + @"\Downloads\" + chart.Name + ".xls";
// Specify export options and export the control.
XlsExportOptionsEx xlsExportOptionsEx = new XlsExportOptionsEx();
xlsExportOptions.ExportType = DevExpress.Export.ExportType.WYSIWYG;
link.ExportToXls(xlsExportFile, xlsExportOptionsEx);
Hyperlink Options
Use the XlsExportOptions.ExportHyperlinks option to specify whether to include hyperlinks in the export file. The following hyperlinks are supported in XLS files:
- Email address
The URL should start with mailto:.
For instance, mailto: office@example.com?subject=Inquiries. - Web page
The URL should start with https:// or http://.
For instance, http://www.example.com. - File
The URL should start with file:///.
For instance, file:///D:/Files/Agreement.htm.
Only absolute URLs can be used for file hyperlinks.
Data Format Options
Use the XlsExportOptions.TextExportMode property to specify how to export text data from controls:
- Set this property to Text to export all data fields as strings. The formatting is embedded in these strings.
- Set this property to Value to supply a XLS format string to the XRLabel and XRTableCell control content. The format string is specified in a control’s XlsxFormatString property.
Values of custom types are not exported to XLS. The cells in the exported file display the #VALUE! error.
If only a .NET format is specified for a value, this format is converted to a matching Excel format.
An XLS format string can contain specific locale code. For example, the following XLS format string appends the Euro sign to data values: “[$€-0407] 0.00” (where “0407” is an identifier for the German language). See Language Identifier Constants and Strings for more information on the available language identifiers.
Print Options
The following options can be applied when an exported Excel document is printed:
Fit All Columns on One Page
Enable the XlsExportOptions.FitToPrintedPageHeight property.
Fit All Rows on One Page
Enable the XlsExportOptions.FitToPrintedPageWidth property.
When both properties are set to true, the “Fit Sheet on One Page“ option is enabled.
Encryption Options
Specify the XlEncryptionOptions.Password property to encrypt an XLS file. Assign an XlEncryptionOptions class instance to the XlsExportOptions.EncryptionOptions property.
Important
Excel passwords are stored as plain text in report definitions. Ensure that only trusted parties have access to report definition files.
XLS files support ARC4 encryption (except for RC4CryptoAPI). The XlEncryptionOptions.Type property value is ignored. See Office Document Cryptography Structure for more information.
Encryption is supported for WYSIWYG and DataAware data shaping options.
using System;
using DevExpress.XtraPrinting;
using DevExpress.XtraReports.UI;
// ...
XtraReport report = new XtraReport() {
Name = "HelloWorld",
Bands = {
new DetailBand(){
Controls={
new XRLabel(){
Text="Hello World!"
}
}
}
}
};
// xlsExportFile specifies the exported file's name and path. The user's Downloads folder is used as the default path.
string xlsExportFile = Environment.GetFolderPath(Environment.SpecialFolder.UserProfile) + @"\Downloads\" + report.Name + ".xls";
// Specify export options to export the report.
XlsExportOptions xlsExportOptions = new XlsExportOptions();
xlsExportOptions.EncryptionOptions.Password = "Password";
report.ExportToXls(xlsExportFile, xlsExportOptions);
Overlapped Controls
Report controls should not intersect to be correctly exported to XLS. Otherwise, the file layout can be corrupted.
Overlapped controls are highlighted in reports. Users can hover the mouse pointer over these controls to see what needs to be fixed.
Tip
- Use a report’s HasExportWarningControls collection to check if there are controls that have export warnings.
- Disable a report’s DesignerOptions.ShowExportWarnings property to remove highlights on overlapped controls.