Exporting / Importing XLSX Files in Jedox Web

image_pdfimage_print

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_excludes>
   <export_exclude export_type="snapshot" object_type="worksheet_element" object_subtype="formel"/>
</export_excludes>

Parameter Supported entries
export_type
  • xlsx – standard export
  • xlsx_snapshot – standard snapshot export
  • xlsx_olap_snapshot – OLAP snapshot export
  • snapshot – a combination of all the snapshot exports, for example ‘xlsx_snapshot’|’xlsx_olap_snapshot’
object_type
  • worksheet_element
object_subtype
  • formel – filters out form elements
  • dchart – filters out dynamic charts
  • hchart – filters out success charts
  • chart – filters out classic charts
  • img – filters out images
  • paloview – filters out Palo paste view
  • page_setup – filters out page setup
  • panescnf – filters out split or frozen pane.

To exclude only certain form elements, right click the element you want to exclude and change its visibility settings.

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 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.

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 following format: __wss_exported_variable_<varname>. For example, the variable @varTest becomes __wss_exported_variable_varTest.

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.

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, and therefore not all properties can be converted during export/import processes.

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.

image_pdfimage_print