Excel Gateway for Oracle APEX

No possibility to let your users access your APEX app? Consider using our tool to handle that in a good and modern way.

Create your own Excel template(s) in APEX, email them to your recipient(s) and then upload the finished file.
Now you can check and correct the data. If everything is well, publish the data for further analysis, reports etc.

Requirement

• Oracle Application Express 21.1 (or higher)
• Oracle Database 12.2 (or higher)

Installation

Go into the APEX workspace, where you like to install the "Excel Gateway for Oracle APEX" app and import the file "/src/apex/excel_gateway_for_oracle_apex.sql" as a database application.

This will install all DB-Objects and the application

Getting Started

1. Create Template

First step is to create a new Excel template

1. Click "Create Template" and follow the wizard
2. Give your template a unique name
3. Optional you can enter a deadline which is needed to calculate when the application will send reminders
4. You need a sheet protection to prevent various actions. Then choose a password for your workbook here
5. You can enter a maximum number of rows which indicates how many rows are available to the editor
6. Adopt specifications from existing templates

Second step is to add column headings to the template

1. Drag and drop the titles from the left area (1) to the right (2)
   Use the up or down arrows to change the order
   The title can be deleted with the recycle bin
2. If you need new titles, click "Add Header" (3)

1. Enter the name of the title
2. Enter the width for the column in your Excel Spreadsheet
3. If the column in your table needs validation, choose one here (for example number, date, email...)
4. If you want the column shows a dropdown list, enter the values here

Next step is to add header-groups to the template (optional)

Choose a heading group for each column that needs merge cells
If you need new header-groups, click on "Add Header-Group" and create a new one
For this you have to enter the name, background and font color

The next step is to set the background and font color for the columns

You can use the Color Picker for this

In the last step, formulas or min/max values can be set for the validations

For number or date validations, use Formula 1 as the minimum and Formula 2 as the maximum value.
If the validation is "Formula", enter the formula you need in Formula 1.
Click "Show Columns/Info" for more details and examples.

Finally, you get an overview.

Before you create the template you can download a preview file. To do this, click the "Preview" button.
Everything is fine, click "Save Template" to create.

2. Send Template

First of all the template has to be selected (1).
Next, person(s) need to be added (2). All person(s) involved in the process are listed in the grid (3).

If you want to add person(s), click on "Add Person" and select the person(s) in the modal dialog.

Click "Add Person" to continue.
Now all persons involved in the process are displayed and the template can be sent by email.
To do this, a mailtype must be selected first.

There are three different types:

1. Initial Mail - all templates that have not yet been sent and processed
2. Correction Mail - all templates where corrections must be made
3. Reminder Mail - all templates where the deadline has passed

When a selection is made, the grid is always updated and only the affected recipients are displayed.
For example, the initial email can be sent only once a time and a reminder can be sent only when the deadline is exceeded.

So first select "Initial Mail" and then click "Send Mail" to send everyone the initial email with the selected template.

All available emails can also be sent automatically.
To do this, click on "Automations". The dialog shows how the status of the automation is and on which days it should be sent if the function is enabled.

3. Upload Template

After sending e-mails, the recipient must fill them out and send them back.
To upload the finished template, navigate to "Upload template" in the navigation menu and click "Upload".

Note: If something should fail during the upload, you can check the error log to find out what the problem is.

4. Check Data

If the upload was successful, the data can be checked.
To do this, navigate to "Check data" and select the one to be checked.

This example shows data without errors that were detected by the application via the previously defined validations.

If incorrect data were detected by the application, this is displayed in the "Validation" column.
For example, an incorrect email address was detected here.

Now there are two options.

1. The incorrect data can be corrected directly in the application or
2. a new excel file will be created in which all incorrect rows are listed. To create this, click "Provide Correction" and then sent it as a "Correction Mail" (navigate to "Send Mail" and select the mailtype "Correction Mail").

If everything is fine, the status can be set to "Completed".
All the data that are "Completed" are available for export.

5. Publication / Export Data

All data are displayed here and are available for download. Alternatively, interactive reporting can be done here.

How to contribute as a developer to this project

This github repository is for developers willing to contribute to the upcoming version of Excel Gateway for Oracle APEX

1. Clone/fork the repository apex-excel-gateway to get your own copy.
2. Create a workspace with the ID 33850085021086653. For this you will need your own APEX environment.
3. Run /src/install_all_scratch_dev.sql.
   When prompted enter the parameters.
   This will install all DB-Objects and the application with fixed ID 445.
   Make sure you have Application ID 445 free for this.
4. Make your changes in the app and/or db objects.
5. Commit your changes in your own branch. Preferable a dedicated branch for the feature you're working on.
6. Send in a pull request for review.
   We will then verify the changes before accepting the pull request.
   We might ask you to update your pull request based on our findings.

Some important rules:

1. Retain Workspace ID and Application ID, otherwise each and every file of the application export will be marked as changed. Easiest way to achieve this is to use the provided development install script mentioned above.
2. Always enable "Export as ZIP" and "Export with Original IDs".

Getting in touch

Send a DM on Twitter to Timo Herwix (@therwix) to get involved.