ExcelFile Connection

Access level: read, write

With the ExcelFile connection, you can connect to Excel workbooks with XLSX format and to Jedox Web Spreadsheets with WSS format.

With Jedox 24.2, PGP decryption is supported for file-based connections, such as ExcelFile. PGP decryption must be configured in the Cloud Console for your Jedox environment. See Keys and Secrets for more information.

Connection settings

The following file types are supported

Type	The following file location types are supported: Connecting to a Jedox Files Location Connecting to a File System Location Connecting to an FTP Location Connecting to an HTTP location Connecting to an SFTP Location Connecting to a OneDrive Location Connecting to a WebDAV Location Connecting to a WebHDFS Location Connecting to an Azure (Blob Storage and Data Lake) Location
File / directory name	Path to the Excel file or directory containing Excel files. The syntax of the filename is location-specific.
Encryption method	For PGP decryption, enter the passkey that was set during key creation. Directory connections will only work if the same passphrase can be used for all files (either the files use keys without passphrase, or the keys for the files have the same passphrase).
Password of Excel file	The password for the file. Note to Excel 2016: to open password-protected Excel 2016 files, the "Java Cryptography Extension (JCE) Unlimited Strength Jurisdiction Policy Files" have to be installed in the Java Runtime Environment (JRE). These files can be downloaded from http://www.oracle.com/technetwork/java/javase/downloads/jce8-download-2133166.html. The installation must be in directory <JRE_HOME>\lib\security.

Notes:

Formulas are only evaluated for files in the XLSX format, not for files in the WSS format.
The "Strict OOXML" format is currently not supported.
The maximum array size required for Excel file processing has the default size of 100 MB (referred to 100,000,000 in array length). If needed, contact Jedox Support to increase the corresponding byteArrayMaxOverrideSizeParameter parameter.

Accessing Google Sheets with Excel Connection

It is possible to use an Excel File connection to access Google Sheets, a web-based spreadsheet program that is part of Google Drive. The Google Sheet has to be published to the web in the format "Microsoft Excel (.xlsx)". It is convenient to activate the option "Automatic republish when changes are made".

Note that the published file doesn't carry over sharing permissions, so anyone will be able to access it. Make sure that the Google account allows this public publishing. In the ExcelFile connection, the URL of the published file must be used with location "HTTP". No password is required. As publishing is done with HTTPS, the SSL mode has to be set to "trust" or "verify".

Using Google sheets allows also the extraction from the web analytics service Google Analytics. Therefore the Google Analytics spreadsheet Add-on has to be installed in Google analytics. Create a Google Sheet for the data and set a suitable interval in the Add-on to refresh the data e.g. monthly. The Google Sheet has to be published to the web as described above and can then be used in the ExcelFile connection.

Updated November 14, 2024