Export to XLSX

  • 12 minutes to read

This document describes how to export a report document to XLSX 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 XLSX File. Specify export options in the invoked dialog, and click OK.

To export a report document to XLSX format in code, use one of the following approaches:

  • Export a Report

    Call an XtraReport.ExportToXlsx overloaded method to export a report. To specify export options, create a XlsxExportOptions class instance and pass this instance to the invoked method, or use the XtraReport's ExportOptions.Xlsx 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!"
                    }
                }
            }
        }
    };
    // xlsxExportFile specifies the exported file's name and path. The user's Downloads folder is used as the default path.
    string xlsxExportFile = Environment.GetFolderPath(Environment.SpecialFolder.UserProfile) + @"\Downloads\" + report.Name + ".xlsx";
    // Specify export options to export the report.
    // Uncomment the lines below to specify export options in an XlsxExportOptions class instance.
    // XlsxExportOptions xlsxExportOptions = new XlsxExportOptions();
    // xlsxExportOptions.ExportMode = XlsxExportMode.SingleFile;
    // report.ExportToXlsx(xlsxExportFile, xlsxExportOptions);
    
    // Comment the lines below to if you specified export options in an XlsxExportOptions class instance above.
    report.ExportOptions.Xlsx.ExportMode = XlsxExportMode.SingleFile;
    report.ExportToXlsx(xlsxExportFile);
    
  • Export a Document

    Call a PrintingSystem.ExportToXlsx overloaded method to export a document. To specify export options, create a XlsxExportOptions class instance and pass this instance to the invoked method, or use PrintingSystem's ExportOptions.Xlsx 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();
    // xlsxExportFile specifies the exported file's name and path. The user's Downloads folder is used as the default path.
    string xlsxExportFile = Environment.GetFolderPath(Environment.SpecialFolder.UserProfile) + @"\Downloads\" + report.Name + ".xlsx";
    // Specify export options and export the document.
    // Uncomment the lines below to specify export options in an XlsxExportOptions class instance.
    // XlsxExportOptions xlsxExportOptions = new XlsxExportOptions();
    // xlsxExportOptions.ExportMode = XlsxExportMode.SingleFile;
    // report.PrintingSystem.ExportToXlsx(xlsxExportFile, xlsxExportOptions);
    
    // Comment the lines below to if you specified export options in an XlsxExportOptions class instance above.
    report.PrintingSystem.ExportOptions.Xlsx.ExportMode = XlsxExportMode.SingleFile;
    report.PrintingSystem.ExportToXlsx(xlsxExportFile);
    
  • Export a Control

    Call a PrintingLink.ExportToXlsx overloaded method to export a control. To specify export options, create a XlsxExportOptions 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;
    // xlsxExportFile specifies the exported file's name and path. The user's Downloads folder is used as the default path.
    string xlsxExportFile = Environment.GetFolderPath(Environment.SpecialFolder.UserProfile) + @"\Downloads\" + chart.Name + ".xlsx";
    // Specify export options and export the control.
    XlsxExportOptions xlsxExportOptions = new XlsxExportOptions();
    xlsxExportOptions.ExportMode = XlsxExportMode.SingleFile;
    link.ExportToXlsx(xlsxExportFile, xlsxExportOptions);
    

The XSLX format supports more rows and columns than the XLS format does (16,384 columns and 1,048,576 rows, as opposed to only 256 columns and 65,536 rows in XLS).

File Options

Use the XlsxExportOptions.ExportMode property to determine the report document export mode:

  • Single File

    The report is exported into one 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 XLSX files, then join these files in a single file.

An Excel worksheet cell may contain up to 32,767 characters. If a control's Text property is longer than that, the excess text is truncated in Excel files. No errors or warnings are displayed. See Excel specifications and limits for more information.

Document Content Options

Enable the XlsxExportOptions.RawDataMode option to export plain data and ignore images, graphic content, font and appearance settings.

Enable the XlsxExportOptions.RasterizeImages property to rasterize vector images in pictures, charts, and bar codes in the resulting Excel file. You can use the XlsxExportOptions.RasterizationResolution property to specify image resolution.

Data Shaping Options

The XlsxExportOptionsEx.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 XLSX documents: native Excel grouping, sorting and filtering settings, improved support for Excel formulas.

    WinForms printing links and WPF printing links do not support this export type. To use the data-aware export, call a control's equivalent methods (for instance, the GridControl.ExportToXlsx method).

  • WYSIWYG Export

    The control cells layout is retained in the resulting Excel document. Data shaping options may not be retained. For instance, summary values may 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;
// xlsxExportFile specifies the exported file's name and path. The user's Downloads folder is used as the default path.
string xlsxExportFile = Environment.GetFolderPath(Environment.SpecialFolder.UserProfile) + @"\Downloads\" + chart.Name + ".xlsx";
// Specify export options and export the control.
XlsxExportOptionsEx xlsxExportOptionsEx = new XlsxExportOptionsEx();
xlsxExportOptions.ExportType = DevExpress.Export.ExportType.WYSIWYG;
link.ExportToXlsx(xlsxExportFile, xlsxExportOptionsEx);

Use the XslxExportOptions.ExportHyperlinks option to specify whether to include hyperlinks in the export file. The following hyperlinks are supported in XLSX 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 are supported for file hyperlinks.

Cross-Reference Options

The following requirements apply to cross-references in XLSX documents:

  • Cross-references should be related as one-to-one or many-to-one.
  • The cross-referencing elements should be assigned identical anchors (the anchors should be unique for each cross-relation).
  • The brick that contains a cross-reference should be assigned an appropriate URL.
  • The referenced brick should have its Brick.AnchorName property assigned.

Within a single workbook, cross-sheet references are maintained for the following XlsxExportOptions.ExportMode values:

Data Format Options

Use the XlsxExportOptions.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 XLSX 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 XLSX. The cells in the export 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 XLSX format string can contain specific locale code. For example, the following XLSX 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.

If the report contains a control that displays a DateTimeOffset value and this control's XlsxFormatString property is not set, the TextFormatString property value is converted into the Excel cell's format string. If TextFormatString is not set, the time zone offset value is added to the cell's format string.

You can enable the following options that apply when an exported Excel document is printed:

When both properties are set to true, the "Fit Sheet on One Page" option becomes enabled.

Encryption Options

Specify the XlEncryptionOptions.Password property to encrypt an XLSX file. Assign an XlEncryptionOptions class instance to the XlsxExportOptions.EncryptionOptions property.

IMPORTANT

Excel passwords are stored as plain text in report definitions. Ensure that only trusted parties have access to report definition files.

Select the encryption type in the XlEncryptionOptions.Type property:

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!"
                }
            }
        }
    }
};
// xlsxExportFile specifies the exported file's name and path. The user's Downloads folder is used as the default path.
string xlsxExportFile = Environment.GetFolderPath(Environment.SpecialFolder.UserProfile) + @"\Downloads\" + report.Name + ".xlsx";
// Specify export options to export the report.
XlsxExportOptions xlsxExportOptions = new XlsxExportOptions();
xlsxExportOptions.EncryptionOptions.Password = "Password";
report.ExportToXlsx(xlsxExportFile, xlsxExportOptions);

Rich Text Support

XLSX export converts the following rich-text content from XRLabel and XRRichText controls into Excel-native rich-text content:

HTML-style Tags for XRLabel

HTML Tags and RTF Equivalents for XRRichText

Text format

<b>, <i>, <u>, <s>

<b>, <i>, <u>, <s>, <strong>, <em>

Line break

<br>

<br>

Non-breaking space

<nbsp>

&nbsp;

Font

<font=[font name]>

<font face=[font name]>

Font size

<size=[font size]>

<font size=[font size]>

Foreground color

<color=[color]>

<font color=[color]>

See the XRLabel.AllowMarkupText property description for a list of tags that XRLabel supports.
See the HTML Tag Support topic for a list of tags that XRRichText supports.

Overlapped Controls

Report controls should not intersect to be correctly exported to XLSX. Otherwise, the file layout can be corrupted.

Overlapped controls are highlighted in reports. Users can hover the mouse pointer over these controls for information about what to fix.

ShowExportWarnings

TIP