Export to Microsoft Excel

By default, Iron Speed Designer’s Application places an “Export to Excel” button in each table panel, such as is found on a Table Report page.  Exported files are saved in a special XML file format that is recognized and imported by Microsoft Excel.

Excel versions supported

The Export to Excel feature works on Microsoft Excel 2003, Microsoft 2007 and later versions.  The Export to Excel feature uses the new Excel XML format that Microsoft developed for Excel 2003 and Excel 2007.

Microsoft Excel 2000 does not support the Excel XML format and hence the data is displayed as XML in an Excel 2000 file.  There may be an Excel 2000 add-in that provides this feature at http://support.microsoft.com/default.aspx/kb/924074.

Exported data rows

Only the rows in the currently displayed data set are exported.  This is convenient for application users who perform a search using the Search control and wish to export only the data subset that matches the search.  There is no limit to the number of rows that may be exported.

Exported data columns

The exported rows include all data fields displayed in the associated table control; fields that are not displayed are not exported.  There are several caveats:

Column Type

Caveat

Date fields

Date fields are exported as per their display format strings and will be exported as they are displayed in the Table Report panel.

The default format is m/d/yyyy

Currency, number and percentage fields

Currency, number and percentage fields are exported as numeric values to enable sorting in Microsoft Excel.

The default formats are:

Currency fields

#,##0.00

Number fields

#,##0

Percentage fields

0.00%

Foreign key fields

Foreign key fields are exported as shown in the table control.  If the “Display as” database property is set for the field, then the selected text field will be exported instead of the underlying key value.

Password fields

Password fields are exported as shown in the table control.

Large text fields

Large text fields, such as the Memo field in Microsoft Access and the nText data type in Microsoft SQL Server, are exported as text strings.

Word-wrapping in Excel is automatically enabled for large text fields.

Binary and image fields

Binary fields, such as Image and BLOB's (binary large object), are not exported.

The column order in the exported file is the same as the column order in the underlying database table or view schema.  To achieve a different export order, we recommend creating a database view with the fields arranged in the desired column order.

The exported data includes column headings in the first row.

Avoiding exporting duplicate records

In certain situations it is possible the exported data set will contain duplicate records if the underlying database table or view does not have a primary key declared in the database or a virtual primary key declared in Iron Speed Designer. Without a primary key, the database may return duplicate records.

Export times out

There is no limit on the number of rows you can export.  However, if you are exporting a large number of rows, the export process may time out.  You may wish to adjust (increase) the executionTimeout parameter in the httpRuntime section of your application’s Web.config file if you experience time out errors.

Efficiency considerations for Export

There is an option in Configure Options to set the maximum number of rows that can be exported in Excel. By default it is set to 10000. This setting will govern whether the values will be exported to Excel, and whether foreign keys will be exported as foreign key value or display as foreign key.

For example on the products table, it has CategoryId field as a foreign key, which is configured to display Category Name. If the number of products is less than the threshold, then Category Name will be exported into either Excel or CSV as requested. If there are at least as many products as the threshold, then CategoryId will be exported instead of Category Name. In addition, when export is requested to Excel, values will be exported to CSV instead. When the threshold is met, the application user will be prompted about the export from the current table.

 

See Also

Part I:  Getting Started

A Quick Tour of Iron Speed Designer

What is an Iron Speed Designer Application?

Function Buttons

PDF Report

Microsoft Word Report

Export to Microsoft Excel

Export to ASCII CSV

Data Import Wizard

Large List Selectors