The options that can be specified for a document exported to an XLS file are stored in the XlsExportOptions class, and can be accessed via a report's ExportOptions.Xls property.
Among these options, the XlsExportOptions.ExportMode property determines the way in which a document is exported to XLS. 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 XLS 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.
As a workaround, use subreports to combine multiple reports to a single document. Alternatively, export all your reports to XLS files separately and then join all the exported data to a single file.
To overcome the warning on exceeding the number of rows and columns permissible in an XLS format, use the XlsExportOptions.Suppress256ColumnsWarning and XlsExportOptions.Suppress65536RowsWarning properties. These options determine whether or not you should be notified if the resulting XLS file exceeds these limitations. The number of rows and columns permitted has been significantly increased in the more recent XLSX format (Microsoft Excel® 2007).
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.
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.
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).
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 XLS, ignoring non-relevant elements (such as images, graphic content, font and appearance settings), use the XlExportOptionsBase.RawDataMode option.
Only the report controls that do not intersect with each other can be correctly exported to XLS. In other cases, the resulting XLS file layout may be corrupted.
To make sure that your report layout will be preserved in an XLS 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).
The export to XLS 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.
Data format options
The XlExportOptionsBase.TextExportMode property determines whether both the .NET and native Excel formatting of data fields in the bound dataset should be preserved for the cells in the resulting XLS document. If this property is set to Text, all data fields are exported to the XLS 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.
If you are using the XRRichText control in your report during export to XLS, all the control's formatting will be lost and its content will be exported as plain text.
Because the XLS format accepts only the Double type for values with a floating point, we recommend that you set this data type instead of Float.
Values of custom types are not exported to XLS 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 replaced with a best matching substitute from the following list of supported Excel formats.
($* #,##0);($* (#,##0);($* "-");(@_)
(* #,##0);(* (#,##0);(* "-");(@_)
($* #,##0.00);($* (#,##0.00);($* "-"??);(@_)
(* #,##0.00);(* (#,##0.00);(* "-"??);(@_)
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). To learn about the available language identifiers, refer to the following topic in MSDN: Language Identifier Constants and Strings.
You can export a report to XLS using one of the following methods with or without the options listed above passed as parameters: