By default, users can export their documents to Excel using the XLSX export feature.

Excel export button in GTAnswer

This feature allows a user to work on an Excel document to create the required data and then import this data into the qst using the Excel import feature.

The generation of the XLSX file is done natively by GTAnswer and does not require Excel on the workstation.

When the XLSX file is generated, all GT features identical to Excel are exported: formatting, formulas, charts, etc. However, there may be differences between the qstx and the XLSX file in the some cases (see chapter: Differences with Excel)

Document design

Securing the export

When designing in Design, you can :

• Prevent the export of the complete questionnaire to an XLSX by changing the “Allow export to Excel” option from the Design Options menu.
• Prevent XLSX export for hidden pages of the questionnaire by using the Export status if hidden page property of each document sheet in Design (in the grid properties).
• Protecting specific sheets.

Protecting the sheets in the exported Excel workbook

From Design, right-click on the sheet to bring up the pop-up menu and select the “Protect exported sheet” item.

You can then specify the protection password for the sheet and the elements that will be protected in the exported Excel workbook sheet.

All cells without components are considered locked in the exported Excel workbook.

The different elements that can be protected in the exported Excel workbook are similar to those available when protecting a sheet from Excel.

– Select locked cells
– Select unlocked cells
– Cell format
– Column format
– Row format
– Insert columns
– Insert rows
– Insert hypertext links
– Delete columns
– Delete rows
– Sort
– Use the automatic filter
– Use pivot table reports
– Modify objects
– Modify scenarios

Design precautions

Specific GT functions

The GT functions of the calculation engine have no equivalent in Excel.

When generating the XLSX workbook, each formula using one of the GT functions will be replaced by the result of the complete formula, which may render some formulas in the Excel workbook non-operational.

For example, a formula =mod(gtpatnum();2) will be replaced by the result 0 or 1 at the time of export (there will be no dynamism in Excel).

An exception concerns the GTMS functions, which are translated in the XLSX workbook into formulas on the cells on each of the sheets; this formula will thus remain operational in Excel but will not take into account the addition/removal of sheets in Excel.

When specific GT functions are to be used, it is best to :

• limit their use to the strict minimum so that the exported Excel workbook remains operational
• reduce any formula using a GT function to the GT function only; for example: instead of writing the formula =INDIRECT(GTMOGETSHEETNAME(“Item1″)&”!A1″), separate the indirect and the GTMOGETSHEETNAME into two cells.
• Find equivalents that work in both QSTX and XLSX by combining Excel functions and/or data from the DBMS whenever possible; for example, a =GTPATNUM() formula could be converted into =ROW()-ROW($A$1)
• Limite the use of formulas that return a different result in the document and in Excel (allows the use of GT functions without Excel equivalents and vice versa); for example, =IF(ISERROR(leftb(“a”)); “We are in GT”; “We are in Excel”)

Component names

Component names must be compatible with Excel field names to allow import into GTAnswer from the produced XLSX workbook.

A warning is generated when importing Excel->Design and when exporting GTAnswer to XLSX when component names that are not compatible with the Excel field names exist.

Export to GTAnswer

Addition of information by GTAnswer in the XLSX product

GTAnswer adds information to the XLSX to enable data to be imported into GTAnswer (Import Answer Excel) from the produced XLSX workbook.

When generating the XLSX file, GTAnswer adds a name field for each cell with a component in GT, except for cells with controls. This field will be used when importing the Excel data.

GTAnswer also adds :

• Name fields corresponding to the sheets in the case of a qstx with multi-tab compartments.
• Name fields to designate the end of the patterns (PatternName.FOOTER) of the document.
• Name fields on the data ranges defined when the document was created.

Name fields exported for components in patterns are prefixed by the pattern name followed by a ?

Component names in a pattern induce an Excel field name of the type PatternName?ComponentName

Differences with Excel

• Colour Themes are not exported.
• Comments are not displayed by default in the XLSX file.
• Formulas using GT-specific functions (GTSGMO, GTPATNUM…) are replaced by the result value.
• Groups of radio buttons are converted into drop-down lists (using Excel’s “Data Validation” feature).
• Checkboxes are converted to TRUE or FALSE values in the cell corresponding to the component.
• Attachments are exported as the file name in the cell
• Some chart properties are not managed by GTAnswer but read and exported to XLSX