Overview

Generate Excel files from an OQL statement you provide. Since you define the OQL statement, the data in the file is fully under your control!

Supports Excel formulas, pivot tables, conditional formatting, and more!

I've also written a Medium article explaining the creation and methodology behind this module.

When upgrading to version 2.0.0+, be sure to remove the old dependencies in the project's userlib folder.

Documentation

OQL to Excel

When upgrading to version 2.0.0+, be sure to remove the old dependencies in the project's userlib folder.

Typical usage scenario

Ever needed an Excel file whose contents can dynamically change in the runtime? Maybe the user can select some filters in the UI that appear in the file. Or maybe the user can dynamically select which columns to export. This module provides an interface to translate an OQL statement and some styling structure into a highly customizable excel file. This module intends for you to create 'template' objects in the runtime without committing. This has the primary benefit of not having to sync model reflection and set up templates in every environment.

This module assumes you have an understanding of OQL statements. It does not help you generate OQL statements.

This module includes 10+ working examples and test data, so you can visually dissect how the module should be used. See the Installation section below for details. The documentation for each field and entity type is directly in the module's domain model and in the admin pages. For your first time using this module, I recommend skimming the domain model, admin pages, and then the examples.

Only use this module if the Excel Export module cannot satisfy your requirements. Some examples include:

• The data in the file can be filtered dynamically in the runtime
• The structure of the file or query changes dynamically in the runtime
• A column that is calculated by other columns
• Cell(s) that use Excel formulas to calculate values
• The file needs to have multiple queries that append after one another
• You need a basic pivot table

Features

• Provides an admin interface for testing your own files and includes 10+ working examples
• Define custom column widths
• Define custom row heights
• Define regions of cells to merge
• Add basic Pivot tables
• Define the cell styling with more granular control than the Excel Export module
• Control a date field's localization
• Supports Excel functions. If the cell starts with an '=', Excel will interpret it as a formula (i.e. '=SUM(A2:A7)').
• OQL statements can include the token '{r}' that will be replaced with the row number in the Excel file (i.e. '=E{r}*G{r}/100').
• OQL queries automatically apply batching
• Cells can be chained together. E.g. after running OQL statement x, skip a row and run OQL statement y. 
• Conditional formatting (i.e. make a cell red when the value is >= 100,000)

Java Actions

Core functionality:

• OQL Count - Get a quick database count for a provided OQL statement. Useful if you need to know ahead of time how much data will be put in the file.
• OQL to Excel - Get a quick Excel file for a provided OQL statement. All the styling is defaulted based on data type and constants.
• Advanced OQL to Excel - Provide an ExWorkSheet object and its children to generate an Excel file with greater control.

Misc Utilities:

• Zip File List - Provide a list of objects that inherit System.FileDocument to zip together. See example: ACT_MultipleFilesZippedExample
  • Note: the file list is not downloaded, so DeleteAfterDownload won't be triggered. Depending on your use case, you may want to delete the file list manually after the zip file is generated.
• UnZip to File List - Provide an existing zip file to extract into a list of objects that inherit System.FileDocument. Does NOT extract sub-directories in the zip file.
• OQL to CSV - copied and simplified from the OQL module.
• Retrieve Self-Reference Children by Association - Returns the children of a self-reference in memory. Useful if you have a self-reference with objects that have not been committed yet.

Limitations

• It takes longer to implement than the Excel Export module 
• You must already understand how to write OQL statements 

Dependencies 

These are only required if you want to use the testing pages in your app. If not, delete the 99. Resources => 01. Admin Pages folder.

• Combo box widget
• Data Widgets module

Installation

The module can be used directly after downloading. No setup is required.

But if you want to use the examples or the admin pages for testing, follow the following steps:

1. Add the page OQLToExcel_Home to your admin menu
2. Grant your admin application role the Admin module role for OQLToExcel
3. Run the project
4. Go to where you added the OQLToExcel_Home page to your admin menu
5. Go to the Test Data tab and generate the test data
6. Go to the Examples tab and enjoy!

Configuration & Constants

The module provides the following constants:

• CONST_DEFAULT_FONT - The name of the font used for the OQLToExcel Java action or for the AdvancedOQLToExcel Java action if no value is provided.
• CONST_DEFAULT_FONT_SIZE - The size of the font used for the OQLToExcel Java action or for the AdvancedOQLToExcel Java action if no value is provided.
• CONST_OQL_BATCH_SIZE - The limit of records to retrieve for each batch.
• CONST_OQL_MAX_ROWS - The absolute maximum amount of rows for an OQL statement. If the value is set to 0, there is no limit. 
• CONST_OQLTOEXCEL_LOGNODE - The log node that debug/trace logs of the Java actions will use.