This article describes how to import and export XLSX files and how these files handle elements specific to Jedox that are not supported by Excel.
Importing XLSX files
You can import XLSX files in Jedox Web (in Designer >File > Import), either as static files or convert them to a WSS file. The XLSX file type is the only file format besides WSS that can be imported into a Jedox workbook.
If you import a file containing raw data tables, you can use the Data-Driven Modeling process.
Exporting XLSX files
You can export (File > Export) Jedox Spreadsheets as XLSX files (with formulas), as XLSX Snapshots (with actual values but no formulas) or as XLSX OLAP Snapshots (only PALO formulas are replaced with values, other spreadsheet formulas are kept as formulas). For more information, see Snapshot and OLAP Snapshot.
The form elements (Checkboxes, ComboBoxes etc.) are by default included in all XLSX exports. To exclude only certain form elements, right click the element you want to exclude and change its visibility settings. It is also possible to have them completely excluded, for this contact Jedox Support.
Handling elements unsupported by Excel
Formulas
When exporting as an XLSX file, Jedox content and functions that are not supported by Excel will be lost. For example, the hyperlink formula of Excel supports only 2 arguments (hyperlink destination and friendly name), whereas Jedox supports many more (see HYPERLINK() Function). Any existing Jedox data transfer arguments will not be saved in the XLSX file.
As Excel doesn't support direct usage of array formulas in list boxes, ComboBoxes that reference a Subset are not exported. As a workaround, use a Named Range as a reference in the ComboBox. This reference should then point to a Subset in the spreadsheet.
Note that using XLSX OLAP Snapshot and XLSX to export files into Excel removes the SUMIFS formula from the sheet. Removing the SUMIFS formula in this situation is a normal behavior of Excel.
Formulas in Conditional Formatting are not replaced by values in an XLSX Snapshot. The Variables in these formulas are handled as described in the section below.
Variables
When exporting reports as XLSX files from Jedox Web, Variables used in cells and formulas are converted to a Named Range expression. This expression has the following format:
__wss_exported_variable_<varname>For example, the Variable @varTest becomes
__wss_exported_variable_varTestNote: this only happens in formula strings. The Named Range is not actually created, causing these cells to return a #NAME error.
When you import an XLSX file, any expression conforming to the syntax above is converted to @<varname>. This conversion is also executed when you use the Variable in a formula for a Conditional Formatting rule. In XLSX Snapshot exports, formulas containing Variables are replaced by their value.
Micro-Charts
As of Excel 2010, Microsoft has implemented its own support for Sparklines. From this version on, it is possible to exchange definitions of Micro Charts (Jedox Web) and Sparklines (Excel 2010 or later). However, Micro Charts are implemented differently in Jedox spreadsheets than in Excel spreadsheets, so that not all properties can be converted during export/import.
DynaRanges
When exporting reports to Excel, only the DynaRanges on the first sheet of the report will be calculated. When using Batch XLSX Snapshot, however, all DynaRanges on all sheets will be calculated.
Updated March 19, 2024