AlchemyJ Studio

AlchemyJ Studio turns Excel into an API modeling environment. It is an Excel Add-in that adds an AlchemyJ ribbon item in Excel ribbon. You can learn how to add AlchemyJ Studio in Excel in Install AlchemyJ.

AlchemyJ Studio add-in has the following groups in the ribbon bar.

1. Model - Tools that help you define an AlchemyJ model.
2. API - Tools to turn an AJ model to an API.
3. Test - Tools to test an AJ model.
4. Troubleshoot - Tools for troubleshooting using API inspector.
5. Tools - Miscellaneous tools for navigation, encryption and etc.
6. Help - Online help and About AlchemyJ.

1. Category - Model

New Model

Select a new sample model template to create a new AlchemyJ workbook.

Initialize Model

Turn the current workbook into an AlchemyJ workbook by adding the following required system worksheets:

• %%SysRuntime worksheet controls the execution of the program. DO NOT change anything in this worksheet.
• %%AppConfig worksheet contains application configuration for the API.

Besides adding the above worksheets, Initialize Model also adds a VBA module to the workbook to support AlchemyJ Extended Functions. However, this requires special permission in Excel. Follow the steps below to enable this permission.

1. Go to File > Options > Trust Center.
2. Click Trust Center Settings.
3. Under Macro Settings, make sure Trust access to the VBA project object model is checked.

If this permission is not enabled, you will see the following error when clicking on the button 'Initialize Model'.

If this permission cannot be enabled (e.g. due to security concern), you can still use AlchemyJ but do note that the 'Initialize Model' button will not work.

Add Definition

Adds a new definition worksheet into the workbook.

External Resources

Adds a ##ExternalResources worksheet. It defines the data source configuration, LDAP connection and SMTP connection information used in the AlchemyJ workbook.

Java API Package

Adds a ##JavaApiPackage worksheet which defines a Java package. One Java API workbook must have one and only one Java API Package worksheet. The sheet name cannot be renamed.

Java API Class

Adds a ##JavaApiClass worksheet. One Java API workbook can have multiple Java API Class worksheets. The sheet name can be renamed if needed.

REST API

Adds a ##RestApi worksheet which defines a REST API. One REST API workbook must have one and only one REST API worksheet. The sheet name cannot be renamed.

REST API Endpoint Group

Adds a ##RestEndpointGroup worksheet. One REST API workbook can have multiple REST API Endpoint Group worksheets. The sheet name can be renamed if needed.

REST API Login Service

Adds a ##RestLoginService worksheet. It defines the authentication feature of a REST API. One REST API can only have one REST API Login Service worksheet. This worksheet is only needed if you want your API to be able to authenticate users. The sheet name cannot be renamed.

REST API Token Authorization

Adds a ##RestTokenAuthorization worksheet. It defines the token setting for an API. Add this definition if you want your API to control access by consuming access tokens. One REST API can only have one REST API Token Authorization worksheet. The sheet name cannot be renamed.

REST API User Details Service

Adds a ##RestUserDetailService worksheet. It defines how the user status and authority of a logged-in user should be obtained in a REST API. Only one REST API User Details Service worksheet can be added. This worksheet is only needed if your API requires the user status and authority lookup. The sheet name cannot be renamed.

REST API Endpoint Security

Adds a ##RestEndpointSecurity worksheet. It defines endpoint access control. Only one REST API Endpoint Security worksheet can be added. This worksheet is only needed if your API requires access control. The sheet name cannot be renamed.

Add Component

Adds a component definition worksheet to the workbook.

Data Dictionary

Adds a DataDictionary worksheet. One workbook can have multiple Data Dictionary worksheets. The sheet name can be renamed if needed.

DB Schema

Adds a DBSchema worksheet. If the current worksheet is a DB Schema Worksheet, it adds a new table schema into the current DB schema worksheet.

Exception Handling

Adds a @@ExceptionHandling worksheet. One workbook can have multiple Exception Handling worksheets. The sheet name can be renamed if needed.

Insert Snippet

Inserts a snippet which is a set of cells in a predefined format.

Data Relationship Schema (Single Table)

Adds a Data Relationship Schema. This is used to represent the relationship of the tables and would be used for constructing a data structure. It supports a single table.

Data Relationship Schema

Adds a Data Relationship Schema. This is used to represent the relationship of the tables and would be used for constructing a data structure. It supports a structure formed by multiple tables.

Function Parameters

Adds a Function Parameters table. It is a format used by ajCustomFunction for defining parameters.

SQL Parameters

Adds a SQL Parameters table. It is a format used by ajRunSQL and ajRunStoredProc for defining parameters.

Filter Condition (Filter Type 1)

Adds a Filter Condition (Filter Type 1) table. It is a format use by several database related AJXFs for defining filter conditions.

Filter Condition (Filter Type 2)

Adds a Filter Condition (Filter Type 2) table. It is a format use by several database related AJXFs for defining filter conditions.

Sorting Criteria

Adds a Sorting Criteria table. It is a format used by ajDBReadRecord for defining sorting criteria.

LOB Info

Adds a LOB Info table. It is a format used by LOB related database AJXFs for defining the large object properties.

Settings

Open the Setting dialog box for modifying the settings of the current AlchemyJ workbook.

API Generation

Auto-save workbook before API Generation

• Checked - Automatically close the workbook after API Generation. Check this if your computer is low on memory during API generation.
• Unchecked - It will prompt a window to ask whether you want to save the workbook when click Generate API.

Close workbook after API Generation

• Checked - Automatically close the workbook after API generation. Check this if your computer is low on memory during API generation.
• Unchecked - Keep the workbook open.

Generation mode - There are 3 AlchemyJ generation modes. Standard, High Performance (Beta) and Debug mode.

• Standard mode: Works with all AlchemyJ model.
• High Performance mode (Beta): Use advanced algorithm to speed up run-time performance. The workbook must not use INDIRECT and OFFSET. It may take a longer time to generate the API.
• Debug mode: Use for troubleshooting with API Inspector.

Output folder - It specifies the location of the generated project.

Source Type - For REST APIs, you can select Jar or War. Select Jar if you want to test the REST API immediately using the embedded web server. Select War if you want to deploy the API to a Java application server.

For Java APIs, the only option is Jar.

Embedded Server - It specifies which type of embedded server to use when you click Run REST API Jar button.

Generate API into a package - Check this option to generate a package when building the project. Otherwise, only the source code would be generated.

Copy package file to directory - Check this option to make a copy of the package to a specified directory.

OpenAPI output directory when finished - Check this option to open the build directory after the project is generated successfully.

Start server after API generation - Check this option to start the API with an embedded web server after the API is compiled successfully. This option is only available for REST API and the Source Type must be Jar.

License

AlchemyJ License key file environment variable - An API generated by the Enterprise edition of AlchemyJ will require a license key to run. The file path of the license key file can be specified by an environment variable. This is the variable name. By using an environment variable, applications running on the server may share the same license file configuration. If the license key file environment variable and license key file location are both provided, the path provided by the license key file location will be used.

AlchemyJ License key file location - The file path of the license key file to be used for building an API. It is required for the Enterprise Edition.

Message

Show message with message type at or lower - It controls whether the message type should be shown in a message result report. It is configurable according to your needs.

• Information
• Warning
• Error

New sheet for each message report - It controls whether a new message worksheet should be created for every validation.

• Checked - A new message worksheet will be generated when each validation is triggered.
• Unchecked - Keep using the existing message worksheet for each validation check.

Include unsupported Excel function detection message - It controls whether AlchemyJ should check for unsupported Excel functions used in the workbook.

Package

AlchemyJ supports generating an API from multiple workbooks. These workbooks must be located in the same folder.

Available workbooks - Display a list of workbooks in the same folder of the current workbook.

Workbooks included in this package - Display the workbook included in this API. If the API uses only one workbook, this list should be empty. Click the Add and Remove button to modify the list.

Run

Show function point response dialog - If this option is checked, AlchemyJ Studio will show the response data structure after a function point is run if it is a REST API or the output of a Java API object. It can be used to preview the data structure in an object.

Navigate to the output cell after Preview Function Point

• Checked - Move cell focus to the first cell of the Response Data Address in Function Point.
• Unchecked - Stay at the current position.

AlchemyJ Extended Function array output overflow option - Indicates what AlchemyJ should return when an AlchemyJ Extended function returns data that is larger than the defined cell range.

• 0 - Show as many rows as possible

• 1 - Return #VALUE!

• 2 - Raise #VALUE! and raise error

• 3 - Show as many rows as possible with more data signals

Advanced

Studio log level - It controls the log level in AlchemyJ Studio. It is configurable according to your needs.

• 1 - Debug
• 2 - Information
• 3 - Warning
• 4 - Error

Java Options for Compiler - Advanced Java options for AlchemyJ compiler to compile the program.

Java Options for Maven - Advanced Java options for installing compiled package to Maven.

Java Options for Embedded Server - Advanced Java options for embedded server to execute the REST API .

Preview Function Point

Runs an AlchemyJ Function Point. The list of function points to run is retrieved from the Functions section of all Java API Class Worksheets and Function Points section of all REST API Endpoint Group Worksheets in the current workbook. If you are using multiple workbooks to define your model, you need to go to the respective workbooks to run the function points.

2. Category - API

Validate

Validates the setting and formula of an API. A ##Messages worksheet will be shown if any issue is found. You can click on the Related Cell Address link to go to the specific worksheet and cell that triggers the error.

Generate API

Start generating the API. For workbooks which contain many cells, the process might take a while.

Generate Data Dictionary JavaScript

Generates a Data Dictionary JavaScript file base on the setup in DataDictionary worksheet.

Run REST API Jar

Run the REST API using the embedded web server.

Launch OpenAPI

Launch the OpenAPI website of the API.

3. Category - Test

Generate Test Template

It creates a test case template for a function point. The columns of input data and expected results will be created automatically based on the function definition.

Execute Test Run

Run test cases defined on a test run worksheet. If there are more than one test run worksheet, it will allow you to select the one to run. The button only works on a Test case template workbook.

Test Tools

It contains other test tools. These tools should be used on a Test case template.

Append Test Cases

Default is 10 test cases and users can revise the number of cases to be appended. The maximum is 100.

Load Test Cases to Model

Loads the data in the current focused test case to its AlchemyJ model worksheet.

Copy Test Result to Empty Expected Result

Copies the 'Studio Test Result' columns value to empty 'Studio Expected Result' and 'API Expected Result' columns. If an Expected Result column is not empty, the existing value there will not be replaced.

Copy Selected Result to Expected Result

Copies values in the 'Studio Test Result' columns to the 'Studio Expected Result' columns. Values are always replaced no matter the cell is empty or not.

Add Test Run

Adds a new '##Test Run' worksheet. Multiple '##Test Run' worksheets can be created in one function point test template, because different ##Test Run worksheet can define different test case scope to be executed.

Backup Test Template

Saves a copy of the current workbook for backup purpose. The backup file will have a file creation timestamp appended at the end of its filename.

4. Category - Troubleshoot

Inspector Setting

Adds an API Inspector Settings worksheet (##InspectorSettings). One workbook may have only one such worksheet. The sheet name cannot be renamed.

Activate Inspector Setting

Activates the settings defined on the API Inspector Settings. The next run of any function point will be using the latest updated settings.

API Inspector

Launches AlchemyJ API Inspector Console.

5. Category - Tools

Go to Sheet

Go to a specified worksheet.

Go to Address

Navigate to a cell if the current focused cell contains a cell / range address or worksheet name. Otherwise, it opens the Go To dialog box in Excel.

More Tools

A collection of other AlchemyJ tools.

Configure Function Point Related Worksheets

Enables user to select the related worksheets for the function point. Worksheets' name with prefix '##' or '%%' will be treated as system worksheets and no need to be defined in the Related Worksheets in ##RestEndpointGroup or ##JavaApiClass.

Configure Big String Value

Create, view or modify a big string in the selected cell.

Encrypt Passwords

Enables user to encrypt password fields ##ExternalResources. The encrypted password will be enclosed with ENC(). For example, the original value of the password field is ABC. After encryption, the value will become something like ENC(X32HF6g).

Encryption Tool

Launches the encryption tool which encrypt a given value. The encrypted value can be used in the application configuration file (yml).

Run AlchemyJ Function Proxy

Launches the AlchemyJ Function Proxy.

6. Category - Help

Online Help

Launches the AlchemyJ Documentation website. It will try to open the relevant page of the current selection. The detection sequence is:

• First AlchemyJ Extended Function reference page. For example, ajAddress
• Snippet page. For example, SQL Parameters Snippet
• Worksheet page. For example, Application Configuration Worksheet
• Overview page. When there has no related page could be found, it will launch the Overview page by current AlchemyJ version.

About

View the AlchemyJ version and the software license agreement information.