Export to XLS
- 13 minutes to read
This article explains how to export a report to XLS format from Preview (Visual Studio, WinForms, WPF), Document Viewer (Web Platforms), and in code. The article also describes export modes and export options you can specify for the exported XLS file.
Note
Export options listed in this article generate WYSIWYG output. Report layouts with misaligned columns, empty spaces, and misaligned headers and footers might produce merged cells and extra columns/rows in the exported XLS file. Refer to the following section for more details: Merged Cells And Extra Columns/Rows: Troubleshooting. To export data to XLS without exact layout replication, use the Spreadsheet Document API instead of the reporting export engine.
Export from Preview (Visual Studio, WinForms, WPF)
You can export a report from the Visual Studio Preview tab, WinForms Print Preview, and WPF Print Preview. Expand the drop-down list of export formats and select XLS File.
Specify export mode and options in the invoked XLS Export Options dialog and click OK.
Export from Document Viewer (Web Platforms)
You can export a report from ASP.NET Web Forms Document Viewer, ASP.NET MVC Document Viewer, or ASP.NET Core Document Viewer. Specify export options in the Export Options dialog, expand the Export To drop-down list in the toolbar, and select XLS.
Export in Code
Use the XtraReport.ExportToXls or XtraReport.ExportToXlsAsync methods to export a report to XLS format. Specify the path/stream to which the report should be exported as the first argument in these methods. If you specify only the path/stream, the report is exported to XLS format with the default options. To change the default options, do the following:
- If you create a report in code: create a XlsExportOptions object, specify export options for this object, then pass the object to the XtraReport.ExportToXls or XtraReport.ExportToXlsAsync methods as the second argument.
- If you create a report in the Visual Studio Designer: change the options in the Properties panel.
Example
using DevExpress.XtraPrinting;
using DevExpress.XtraReports.UI;
using System.Windows.Forms;
using System;
namespace XlsExportExample {
public partial class Form1 : Form {
public Form1() {
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e) {
// Create a report.
XtraReport report = new XtraReport() {
Name = "Report Example",
Bands = {
new DetailBand() {
Controls = {
new XRLabel() {
Text = "Some content goes here...",
}
}
}
}
};
// Specify export options.
XlsExportOptions xlsExportOptions = new XlsExportOptions() {
ExportMode = XlsExportMode.SingleFile,
ShowGridLines = true,
FitToPrintedPageHeight = true
};
// Specify the path for the exported XLS file.
string xlsExportFile =
Environment.GetFolderPath(Environment.SpecialFolder.UserProfile) +
@"\Downloads\" +
report.Name +
".xls";
// Export the report.
report.ExportToXls(xlsExportFile, xlsExportOptions);
}
}
}
Export Modes
Use the Export Mode option to specify one of the following XLS export modes.
Single File
Exports a report to a file that contains one sheet with all report pages. The report’s page headers, footers, and top/bottom margins appear only once, at the beginning and end of the sheet.
Single File Page-By-Page
Saves every report page as one sheet. The report’s page headers, footers, and top/bottom margins appear on every sheet.
Different Files
Exports a report to multiple files. Each file contains a sheet that corresponds to one report page. Page headers, footers, and top/bottom margins appear on every sheet.
Export Options
The following options are available in the XLS Export Dialog, which you can open from the Visual Studio Print tab, WinForms Print Preview, WPF Print Preview, and in the Export Options dialog of the Document Viewer (Web platforms):
The options below are available only in the Properties panel in the Visual Studio Report Designer, WinForms End-User Report Designer, WPF End-User Report-Designer, …
… and in the Export options dialog of the Document Viewer (Web platforms).
- Image Export Options
- Print Options
- Encryption Options
- Suppress Export Exceptions Options
- Workbook Color Palette Compliance
- Right-to-Left Document
- Ignore Errors
Page Range
The Page Range option specifies the range of pages that should be exported to an XLS file. For example, the “1,3,5-12” range exports pages one, three, and five through twelve.
Sheet Name
Use the Sheet Name option to specify the name of the sheet in the exported XLS file. If you use Single File Page-By-Page mode, a consecutive index is added to the name of each sheet in the exported XLS file.
Text Export Mode
Use the Text Export Mode option to specify one of the following XLS export modes for report data:
Value (default)
Exports all data with the same formatting as in the original document. Integer, double, and Boolean values are exported with their original types. All other values are exported as strings. For the XRLabel control, the XlsxFormatString property value is used as a format string. If the property is not specified, the TextFormatString property value is used as a format string.
Text
Exports all data as strings.
Note
If a report contains an XRLabel control that displays a DateTimeOffset value and neither XlsxFormatString nor TextFormatString is specified, a format string with the time zone offset is created for a cell with the control’s value in the exported XLS file.
Show Grid Lines
The Show Grid Lines option specifies whether sheet gridlines are visible in the exported XLS file.
Export Hyperlinks
Use the Export Hyperlinks option to specify whether to include hyperlinks in the exported XLS file. Only absolute URLs of the following types are supported:
URL Type | Example |
|---|---|
Email address | mailto:office@example.com?subject=Inquiries |
Web page | https://www.example.com |
File | file:///D:/Files/Agreement.html |
Raw Data Mode
Enable the Raw Data Mode option to export plain data and ignore images, graphics, font, and appearance settings.
Image Export Options
Enable the Rasterize Images option to rasterize vector images in pictures, charts, and barcodes in the exported XLS file. Use the Rasterization Resolution option to specify image resolution.
Print Options
Enable the Fit To Printed Page Height/Fit To Printed Page Width option to shrink the height/width of the exported document’s printout to one page. Enable both of these options to shrink the width and height of the exported document’s printout to one page.
Encryption Options
Use the Password option to set a password for the exported XLS file. XLS files support ARC4 encryption (except for RC4CryptoAPI). Refer to the following topic for more information: Office Document Cryptography Structure.
Important
Passwords for XLS files are stored as plain text in report definitions. Ensure that only trusted parties have access to report definition files.
Suppress Export Exceptions Options
Use the options below to suppress exceptions that raise if the exported XLS file contains more than 256 columns or 65536 rows:
Workbook Color Palette Compliance
Use the WorkbookColorPaletteCompliance property to specify color palette compatibility with different workbook versions.
Right-to-Left Document
If you use right-to-left fonts in a report, enable the Right-to-Left Document option to use the right-to-left layout for sheets in the exported XLS file.
Ignore Errors
Use the Ignore Errors option to specify errors that should be ignored in the exported XLS file.
Limitations
Common
- Values of custom types are not exported to XLS files. If a cell within the exported XLS file contains a value of a custom type, the cell displays the #VALUE! error.
- An XLS cell may contain up to 32,767 characters. If the control’s Text property contains longer text, the text is truncated in the exported XLS file. No errors or warnings are displayed. Refer to the following Microsoft topic for more information: Excel specifications and limits.
- Cross-sheet references work in Single File export only.
Single File Mode
- If a report uses CachedReportSource, changes made in Preview are not included in Single File exports.
- If a report includes a page merged from another document, you cannot export the report 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 or export each report to a single XLS file, then join these files into one file.
Overlapped Controls
Overlapped controls might produce a corrupted layout in the exported XLS file. In the Report Designer, overlapped controls are shown in red. Move the mouse pointer over these controls to show a detailed export warning:
If you create a report in code, use the HasExportWarningControls collection to check if the report contains controls with export warnings.
The XRLabel control placed over the XRPictureBox control is exported to an XLS file under the picture.
The BeforePrint Event
If a control is bound to a data source field and the Text Export Mode option is set to Value, data from this field is exported directly to an XLS file. If you modify the control’s Text property value in the BeforePrint event, the changes are not exported to the XLS file. To modify and export the control’s data, use one of the following techniques:
- Assign data to the control’s
Valueproperty instead of the Text property in the implemented BeforePrint event. - Use the EvaluateBinding event instead of BeforePrint. Assign a new value to the e.Value property.
- Set the Text Export Mode option to Text. Note that all values are exported as strings for this mode.
HTML/RTF Export
You can specify HTML content for the XRLabel control and HTML/RTF content for the XRRichText control. Only the following groups of HTML tags and their RTF equivalents are supported in XLS files:
Tag Group | Tags Example |
|---|---|
Text format | <b>, <i>, <u>, <s> |
Font settings (name, size, and foreground color) | <font=[font name]> |
Line break | <br> |
Non-breaking space | <nbsp> |
Refer to the AllowMarkupText property description for information on XRLabel HTML tags supported in XLS files. See the following topic for details on HTML tags that XRRichText supports: HTML Support Limitations: Supported and Unsupported Tags.
Export Report Groups to Separate XLS Sheets
This section explains how you can export each data group into an individual worksheet. You can leverage the Single File Page By Page export mode if you take a few preparatory steps.
Do the following to export each report group to separate XLS sheets:
Modify the report layout to place each group on a new report page. Use one of the following methods to separate the report groups:
- If the groups are stored in one report, use the XRPageBreak control or enable the Band.PageBreak property.
- If each group is stored as a separate report, use the XRSubreport control with the GenerateOwnPages property enabled.
Fit the content of each group to one report page. To do this, set the XtraReport.RollPaper property to true.
- Enable Single File Page By Page export mode to export each page of the report to a separate XLS sheet.
- (Optional) Use the XlSheetCreated event to change the default name of each XLS sheet.
Note
You may want these layout changes to be applied only to the XLS export and not to on-screen preview or other export formats. In such cases, we recommend that you create a copy of the report and adjust this copy specifically for XLS export purposes. If you create a report in the Visual Studio Designer, you can customize the report in code.
Merged Cells And Extra Columns/Rows: Troubleshooting
Export options listed in this article produce WYSIWYG output. Report layouts with misaligned columns, empty spaces, misaligned headers and footers, and overlapped controls might produce merged cells and extra columns/rows in the exported XLS file. This section describes how you can modify a report layout to resolve these issues.
Misaligned Columns
The image below shows a report with a table whose header’s columns are not aligned with the table cells:
The exported XLS file has the following layout:
Only A, C, E, and G columns are visible. Extra B, D, F, and H columns are added to the exported XLS file. The problem occurs because the table header and the cells in the Detail band are not aligned. The image below shows the borders of the misaligned columns:
To remove extra columns, align header and detail cells horizontally:
Empty Spaces
The image below shows a report with empty spaces between controls:
The exported XLS file has the following layout:
In the image below, cells with exported controls have a yellow background. As you can see, the export generated additional cells to replicate empty spaces between the controls.
To fix this issue, move or resize report controls to eliminate empty space. Use the TextAlignment and Padding properties to adjust text position.
Misaligned Headers And Footers
The image below shows a report with a header in which the control’s borders are not lined up with the report’s table columns.
The exported XLS file has the following layout:
The image below highlights the controls that generate extra columns:
To remove these extra columns, align the controls in the report header and footer to table columns:
Overlapped Controls
The following image shows a report layout with two overlapped labels:
The exported Excel file appears as follows:
You can see that an extra column named B appears in the exported file. To remove this column, modify your report layout such that the controls do not overlap.
After the modification, the exported file does not contain the extra column:
Post-Process XLS Files
You can post-process the resulting XLS files with the help of a dedicated DevExpress library: Spreadsheet Document API. This product helps you edit, merge, split, password-protect, and digitally sign DOCX files.
You may also use Spreadsheet Document API to generate Excel documents from scratch. If that code-only approach suits your requirements, you don’t need to construct a report in the designer at all.
Spreadsheet Document API works in desktop and web applications that target a variety of platforms (Windows Forms, WPF, ASP.NET Web Forms, ASP.NET MVC, ASP.NET Core, Blazor, MAUI) and operating systems (Windows, Linux, macOS).
You need a license for the DevExpress Office File API Subscription or DevExpress Universal Subscription to use the Spreadsheet Document API in production code.
If you haven’t yet done so, download our fully-functional 30-day trial version to try out DevExpress controls and libraries in your projects: