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, either as static files or in a Jedox workbook (WSS file). The XLSX file type is the only file format besides .wss that can be imported into a Jedox workbook. You can also create XLSX files with earlier versions of Excel if you have installed the free Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint.
To import an XLSX file into a Jedox workbook, select File > Import… in an open Jedox worksheet. Only XLSX files can be imported using File > Import… in Jedox Web.
If you import a file containing raw data tables, you can enable the Data-Driven Modeling procedure.
Note: With Auto Format (Excel 2003) or Format as Table (Excel 2007/2010/2013) custom formats are not supported.
Exporting XLSX files
Under File > Export you can 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 on Snapshots in Jedox, click here.
The form elements (Checkboxes, Comboboxes etc.) are included in all XLSX exports.
You can exclude form elements in XLSX, XLSX snapshot, and XLSX OLAP snapshot exports. For this you must add an entry in the file
...\core\ooxml_config.xml within the root node (parallel to <pattern_fills> and <font_fallbacks>) like in the example below:
<export_exclude export_type="snapshot" object_type="worksheet_element" object_subtype="formel"/>
Handling elements unsupported by Excel
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 which reference a subset are not exported. As a workaround, it is possible to use in the combobox a named range as a reference. This reference points to a subset on the spreadsheet.
Note: Formulas in Conditional Formatting are not replaced by values in an XLSX snapshot. The variables in these formulas are handled like described in the section below.
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 following format:
__wss_exported_variable_<varname>. For example, the variable @varTest becomes
Note: this is only done in formula strings. The named range is not actually created, so these cells return a #NAME error.
When you import an XLSX file, any expression conforming to
__wss_exported_variable_<varname>is converted to @<varname>. This conversion is also done 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.
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, and therefore not all properties can be converted during export/import processes.
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.