Export to XLSX
- 6 minutes to read
This document describes the specifics of exporting a document to the XLSX format, introduced by Microsoft in Excel® 2007.
TIP
A code example illustrating how to export a report to XLSX is available at How to export a report to XLSX format.
The XSLX format supports a greater number of 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).
The options that can be specified for a document exported to an XLSX file are stored in the XlsxExportOptions class, and can be accessed via a report's ExportOptions.Xlsx property.
File options
Among these options, the XlsxExportOptions.ExportMode property determines the way in which a document is exported to XLSX. For instance, it may be exported to a single file (with a single page header at the beginning and a single page footer at the end). Or it may be exported page-by-page to either a single file or different files.
To export every report page to an individual sheet of an XLSX file, use the page-by-page setting along with single file. The sheets are then named by appending consecutive indexes to the specified XlExportOptionsBase.SheetName value.
NOTE
Composite report documents created from multiple merged documents cannot be exported to the XLSX format in the XlsxExportMode.SingleFile (continuous) mode.
As a workaround, use subreports to combine multiple reports to a single document. Alternatively, export all your reports to XLSX files separately and then join all the exported data to a single file.
When the number of characters in a control's XRControl.Text exceeds 32,767, which is the total number of characters that a worksheet cell can contain, the remaining text is truncated in the exported Excel document without displaying any warning. To learn more, see Excel specifications and limits.
Encryption Options
To encrypt an XLSX file, specify the XlEncryptionOptions.Password property of the XlEncryptionOptions object returned by the XlExportOptionsBase.EncryptionOptions property. The default empty password cannot be used for file encryption.
IMPORTANT
Excel passwords are saved with report definitions in clear text. Make sure that only trusted parties have access to report definition files.
After specifying the password, you can select the encryption type using the XlEncryptionOptions.Type property. The following encryptions are supported for XLSX files.
XlEncryptionType.Strong (the default value)
Indicates the Agile Encryption mechanism.
-
Indicates the Standard Encryption that is compatible with Excel 2007.
The encryption is supported for both the WYSIWYG and DataAware export modes.
Print options
To enable the "Fit All Columns on One Page" option available in the Print dialog of an Excel document, use the XlExportOptionsBase.FitToPrintedPageHeight property.
To enable the "Fit All Rows on One Page" option available in the Print dialog of an Excel document, use the XlExportOptionsBase.FitToPrintedPageWidth property.
When both these properties are set to true, this enables the "Fit Sheet on One Page" option in the Print dialog of an Excel document.
Data shaping options
The following two modes of Excel export are available.
Data-Aware Export
This export mode is optimized for subsequent data analysis within Microsoft Excel. Various data shaping options that are applied within the control are retained in the output XLS-XLSX documents (such as native Excel grouping, sorting and filtering settings, as well as improved support for Excel formulas).
Printing links (in WinForms and WPF) do not support this export mode and to use the data-aware export, it is required to call the corresponding methods of specific controls (e.g., GridControl).
WYSIWYG Export
With this export mode, the layout of a control's cells is retained in the resulting Excel documents while specific data shaping options may not be retained.
This is the only export method that is supported by printing links.
Document content options
To only export your report's actual data to XLSX, ignoring non-relevant elements (such as images, graphic content, font and appearance settings), use the XlExportOptionsBase.RawDataMode option.
To specify whether or not vector images (e.g., pictures, charts or bar codes) should be rasterized on export to Excel, use the PageByPageExportOptionsBase.RasterizeImages property. When this option is enabled, you can also define the image resolution using the PageByPageExportOptionsBase.RasterizationResolution property.
Document layout options
Only the report controls that do not intersect with each other can be correctly exported to XLSX. In other cases, the resulting XLSX file layout may be corrupted.
To make sure that your report layout will be preserved in an XLSX format, enable the report's DesignerOptions.ShowExportWarnings property at design time, and check to ensure there are no exclamation marks shown for intersecting controls (colored in red).
Hyperlink options
The export to XLSX supports the following three types of links.
- Email address - a URL must start with the "mailto:" prefix.
- Web page - a URL must start with the "https://" or "http://" prefix.
File - a URL must start with the "file:///" prefix.
At present, only absolute URLs are supported for file hyperlinks.
Cross-reference options
The following requirements apply to cross-references, regarding their support in XLSX.
- The cross-references must be related as one-to-one or many-to-one.
- The cross-referencing elements must be assigned identical anchors (that must also be unique for each cross-relation).
- A brick containing a cross-reference must be assigned an appropriate URL.
- A referenced brick must have its Brick.AnchorName property assigned.
Within a single workbook, cross-sheet references are maintained only for the following XlsxExportOptions.ExportMode values.
Data format options
The XlExportOptionsBase.TextExportMode property determines whether both the .NET and native XLSX formatting of data fields in the bound dataset should be preserved for the cells in the resulting XLSX document. If this property is set to Text, all data fields are exported to the XLSX file as strings (with the corresponding formatting embedded into those strings), and the XRControl.XlsxFormatString property of the report's controls will have no effect.
When the XlExportOptionsBase.TextExportMode property is set to Value, you can supply a native XLSX format string to the content of the XRLabel and XRTableCell controls via their XRControl.XlsxFormatString property. The formatting specified via this property will be applied to the corresponding cells in the resulting XLSX file.
Values of custom types are not exported to XLSX and the corresponding cells display the "#VALUE!" error.
When exporting values that have been assigned only the .NET format without specifying their native Excel format at the same time, the original .NET format is automatically converted to a corresponding XLSX 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). To learn about the available language identifiers, refer to the following topic in MSDN: Language Identifier Constants and Strings.