Export to XLSX

  • 11 minutes to read

This article explains how to export a report to XLSX 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 XLSX 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 XLSX file. Refer to the following section for more details: Merged Cells And Extra Columns/Rows: Troubleshooting. To export data to XLSX 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 Visual Studio Preview tab, WinForms Print Preview, and WPF Print Preview. Expand the drop-down list with export formats and select XLSX File.

Specify export mode and options in the invoked XLSX 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 XLSX.

Export in Code

Use the XtraReport.ExportToXlsx or XtraReport.ExportToXlsxAsync methods to export a report to XLSX 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 XLSX format with the default options. To change the default options, do the following:

Example

View Example: Export a Report to XLSX Format

using DevExpress.XtraPrinting;
using DevExpress.XtraReports.UI;
using System.Windows.Forms;
using System;

namespace XlsxExportExample {
    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.
            XlsxExportOptions xlsxExportOptions = new XlsxExportOptions() {
                ExportMode = XlsxExportMode.SingleFile,
                ShowGridLines = true,
                FitToPrintedPageHeight = true
            };

            // Specify the path for the exported XLSX file.  
            string xlsxExportFile =
                Environment.GetFolderPath(Environment.SpecialFolder.UserProfile) +
                @"\Downloads\" +
                report.Name +
                ".xlsx";

            // Export the report.
            report.ExportToXlsx(xlsxExportFile, xlsxExportOptions);
        }
    }
}

Export Modes

Use the Export Mode option to specify one of the following XLSX 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 XLSX 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).

Page Range

The Page Range option specifies the range of pages that should be exported to an XLSX 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 XLSX file. If you use Single File Page-By-Page mode, a consecutive index is added to the name of each sheet in the exported XLSX file.

Text Export Mode

Use the Text Export Mode option to specify one of the following XLSX export modes for the report's 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 XLSX file.

Show Grid Lines

The Show Grid Lines option specifies whether sheet gridlines are visible in the exported XLSX file.

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 XLSX file. Use the Rasterization Resolution option to specify image resolution.

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 XLSX file. Use the Type option to specify one of the following encryption types:

  • Strong (default)

    Corresponds to the Agile Encryption mechanism.

  • Compatible

    Corresponds to the Standard Encryption that is compatible with Excel 2007.

IMPORTANT

Passwords for XLSX files are stored as plain text in report definitions. Ensure that only trusted parties have access to report definition files.

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 XLSX file.

Ignore Errors

Use the Ignore Errors option to specify errors that should be ignored in the exported XLSX file.

Limitations

Common

  • Values of custom types are not exported to XLSX files. If a cell within the exported XLSX file contains a value of a custom type, the cell displays the #VALUE! error.
  • An XLSX cell may contain up to 32,767 characters. If the control's Text property contains longer text, the text is truncated in the exported XLSX 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 XLSX file, then join these files into one file.

Overlapped Controls

  • Overlapped controls might produce a corrupted layout in the exported XLSX file. In the Report Designer, overlapped controls are shown in red. Move the mouse pointer over these controls to show a detailed export warning:

    ShowExportWarnings

    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 XLSX 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 XLSX file. If you modify the control's Text property value in the BeforePrint event, the changes are not exported to the XLSX file. To modify and export the control's data, use one of the following techniques:

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 XLSX 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's HTML tags supported in XLSX 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 XLSX Sheets

This section explains how you can export each data group into an individual worksheet. You can leverage Single File Page By Page export mode if you take a few preparatory steps.

Do the following to export each report group to separate XLSX sheets:

  1. Modify the report layout to place each group on a new report page. Use one of the following methods to separate the report groups:

  2. Fit the content of each group to one report page. To do this, set the XtraReport.RollPaper property to true.

  3. Enable Single File Page By Page export mode to export each page of the report to a separate XLSX sheet.
  4. (Optional) Use the XlSheetCreated event to change the default name of each XLSX sheet.
NOTE

You may want these layout changes to be applied only to the XLSX 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 XLSX export purposes. If you create a report in the Visual Studio Designer, you can customize the report in code.

View Example: Export Report Groups to Separate Excel Sheets

Merged Cells And Extra Columns/Rows: Troubleshooting

Export options listed in these article produce 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 XLSX 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 XLSX 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 XLSX 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 XLSX 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 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 XLSX 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: