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);
    

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);

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.

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 to the Excel cell's format string. If TextFormatString is not set, the time zone offset value is added to the cell's format string.

The following options can be applied when an exported Excel document is printed:

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);

Rich Text Support

XLS 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 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.

ShowExportWarnings

TIP