Excel (XLSX) Export Format - Exportizer Documentation

Use this format to export data to Microsoft Excel 2007+ workbook (xlsx). The output file(s) can be opened in many applications like Microsoft Excel, LibreOffice Calc, Apache OpenOffice Calc etc. This format is the most configurable format comparing to other Excel output formats in Exportizer.

This is a binary format. The export process is performed by Exportizer engine with direct file access and therefore is very fast.

You can export data to Excel (XLSX) format either from Exportizer GUI or from the command line.

When exporting data from GUI, switch to Excel (XLSX) page of the Export dialog. If this page is not visible, click Favorite Export Formats button in the top-right corner of the window and make sure the corresponding format is selected.

Specifying export settings when exporting data to Excel (XLSX) format:

Classic Interface

Grid Interface

Format Specific Options

File - when exporting data from a table or SQL query, or when exporting data from a group of tables (multi-table export), specify a file name to export data to. In case of multi-table exporting, all tables will be outputted to one target file, each table on a separate Excel sheet.

Folder - when exporting data from a group of tables (multi-table export), specify a folder name to export data to. Each table will be exported to a separate file in the specified folder; file names can be specified in Table Mappings.

Sheet name - specify the name of target Excel sheet to place the source data in. Available when exporting data to one file; for multi-table exporting, sheet names can be specified at the table mappings step. Must be valid Excel sheet name, unique per target file. If empty, the application uses Sheet1 name. Note: When the number of rows on sheet exceeds the Rows per sheet value, additional sheets will be created. These additional sheets use the specified name as a base plus sequential suffix _p2, _p3 etc. Command line equivalent: /SheetName.

Range name - specify the name of target Excel cell range for exported data (including column headers). Available when exporting data to one file; for multi-table exporting, range names can be specified at the table mappings step. Must be valid Excel range name, unique per target file. If empty, the range will not be named. Note: When the number of rows on sheet exceeds the Rows per sheet value, additional sheets and ranges will be created. These additional ranges use the specified name as a base plus sequential suffix _part2, _part3 etc. Command line equivalent: /RangeName.

Table title - specify the table title, i.e. text which is shown above the table data in the target document. Available when exporting data to one file; for multi-table exporting, table titles can be specified at the table mappings step. Command line equivalent: /TableTitle.

Rows per sheet - specify how many rows should contain an Excel sheet. If the number of filled rows on the sheet reaches this value, and there are still records to be exported, a new sheet is automatically created and the data output is continued into the new sheet. Command line equivalent: /RowsPerSheet.

Append timestamp to file - specify whether to append current timestamp to the end of the target file name. The timestamp mask is _YYYYMMDD_HH24MISS. Command line equivalent: /AppendTimestamp.

Include MEMO fields - specify either to include contents of MEMO / CLOB fields in target or not; in the latter case, a constant describing the field type will be used instead of the field contents. Command line equivalent: /IncludeMemo.

Include column names - specify whether to include the column headers in the target. Command line equivalent: /IncludeColNames.

Include images - specify whether to include the graphics from BLOB fields in target. Command line equivalent: /IncludeImages.

Trim trailing spaces - specify either to trim trailing spaces and control characters in target or not. Applicable for char and varchar data only. Using this option allows to remove useless data and thus reduce the output volume. Command line equivalent: /TrimTrailingSpaces.

Use raw data - specify either to use the raw data from the source or prepare them for proper use in Excel. Using raw data can slightly speed up the exporting process, but it can produce erroneous output when some characters, not valid in Excel or XML (internally, XLSX format is based on XML), are present in the source data. Command line equivalent: /UseRawData.

Add autofilter - specify either to add auto-filter for all columns in the target Excel document. Command line equivalent: /AddAutofilter.

Target View

Specify how the target should look. Note: Using these options can slow down the exporting process for large datasets.

Use GUI settings - specify whether to use GUI view settings (fonts, colors, column widths) for target data. When using this option, table(s) in target document will look similarly to tables in Exportizer interface; otherwise, default fonts and colors will be applied. This option is effective when exported dataset has an associated GUI data grid. Command line equivalent: /UseGUIViewSettings.

Alternate row color - specify background color for every second row. The color must be specified in hexadecimal form, which is made up of sharp symbol (#), followed by three hex numbers, rr, gg, bb, that give values for red, green and blue, respectively; for example, #FF0000 represents red. If you don't want to highlight even rows, leave this field blank. Command line equivalent: /AlternateRowColor.

Table title font - specify font for the Table title. Command line equivalent: /TableTitleFont.

Column headers font - specify font for the column headers. Command line equivalent: /ColumnHeadersFont.

Rows font - specify font for the target data rows. Command line equivalent: /RowsFont.

Export mode

Specify export mode:

REPLACE+INSERT

Target file is created and filled with incoming rows; if the target file already exists, it is overwritten.

CREATE_OR_REPLACE

Blank target file (using appropriate structure) is created; if the target file already exists, it is overwritten.

Note: For multi-table exporting, the value can be overridden for each individual table-to-file pair in Table Mappings section.

Command line equivalent: /ExportMode.

Record range

Specify range of source records to be exported:

Full table - all records are exported.
Selected records only - only selected records are exported. To select rows, click the corresponding button and then select needed rows using Shift, Ctrl and arrow keys.
From current record to the last one - all data between current and the last records are exported.

Limit the record count to - specify a maximum number of records to be exported. If this option is not specified or it is less then 1, all records from the specified record range will be exported. If you just want to create a file without data exporting, use the corresponding Export mode instead. Command line equivalent: /LimitRecordCount.

Column range

Specify range of source columns to be exported:

All columns - all columns (including columns, which were temporary hidden by user) are exported.
Selected column only - only selected (current) column is exported.
Visible columns - only visible columns are exported.

Source records per target row

Specify a number of records from source database to be placed into one line of the target file. For example, if you specify 2, the source data will be placed to the target in the following way:

Source		Target
Col1	Col2		Col1	Col2	Col1	Col2
cell1	cell2		cell1	cell2	cell3	cell4
cell3	cell4		cell5	cell6	cell7	cell8
cell5	cell6
cell7	cell8

This option is useful when source have a small count of columns and a large count of rows. Using it, you can fill target area more optimally.

Command line equivalent: /RecordsPerLine.

Other options

Ask before overwrite or empty existing target - specify whether to ask the user to overwrite existing target for REPLACE+INSERT and CREATE_OR_REPLACE export modes. Reverse command line equivalent: /SuppressOverwriteOrDeletePrompt.