AlchemyJ Studio

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

There have following categories button in the ribbon.

1. Model - A set of functions that help user to define business model
2. Preview - A set of functions that let user to preview the function execution result in AlchemyJ Studio.
3. Test - A set of functions that are used to do the unit test for the function points.
4. Export - Validate the definitions and Generate API
5. Troubleshoot - A set of functions that trigger API Inspector.
6. Tools - Encrypt password and Go to specified worksheet
7. Help - 'About' function to check AlchemyJ version and license information

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 the 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. You can create a new AlchemyJ workbook by making a copy of Blank AlchemyJ workbook.xlsm found in Sample Models folder in the AlchemyJ installation package.

Add Definition

Adds a new definition worksheet into the 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 your API requires access control. The sheet name cannot be renamed.

REST API Token Authorization

Adds a ##RestTokenAuthorization worksheet. It defines the token which is used for REST API authentication. 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.

Insert Snippet

Insert a snippet. As for now, only one snippet Data Relationship Schema is provided.

Properties

This is the dialog box for modifying the properties of AlchemyJ.

API Generation

Enable automatic sheet detection - If this option is checked, you do not need to fill in the section "Related Worksheets". All worksheets will be included for compilation except the system worksheets (e.g. %%AppConfig, %%SysRuntime and so on).

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

• Standard mode: Generate the logic in sequence.
• High Performance mode (Beta): Calculate the dependencies before generation to reduce the repetitive operations to achieve a high performance of the program.
• Debug mode: This is similar to Standard mode and enables the user to use the Debugging Console to debug the program.

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 built-in Tomcat server. Select War if you want to deploy the API to a Java application server.

For Java APIs, the only option is Jar.

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.

Open API 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 a built-in Tomcat server after the API is compiled successfully. This option is only available for REST API and the Source Type must be Jar.

Data Source

Database type - The type of the database. It supports MySQL, Oracle, and MSSQL.

DB connection string - The database connection string for running database related AlchemyJ Extended Functions in AlchemyJ Studio. It is required if the API uses database related AlchemyJ Extended Functions such as ajExecuteSql and ajExecuteSp.

For Oracle:

Provider=OraOLEDB.Oracle.1;Password=[password];Persist Security Info=True;User ID=[user id];Data Source=[data source];Extended Properties=""

Example:

Provider=OraOLEDB.Oracle.1;Password=myPassword;Persist Security Info=True;User ID=myUsername;Data Source=192.168.0.1:1521/service_name;Extended Properties=""

For MySQL:

Driver={MySQL ODBC 5.2 Unicode Driver}; Server=[server address];DB=[database name];UId=[user id]; PWD=[password];OPTION=3;

Example:

Driver={MySQL ODBC 5.2 Unicode Driver}; Server=192.168.0.1;DB=test_db;UId=myUsername; PWD=myPassword;OPTION=3;

For MSSQL:

Driver={SQL Server}; Server=[server address];Database=[database name];UId=[user id]; Pwd=[password];

Example:

Driver={SQL Server}; Server=192.168.0.1;Database=test_db;UId=myUsername; Pwd=myPassword;

API Inspector

API Inspector listening port - The port number used by the API Inspector.

API Inspector setting file location - The location of the API Inspector command action file.

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.

Misc

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

Validation

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

Turn off warning - It controls whether a warning message should be shown in a validation result report. It is configurable according to your needs.

• Checked - Warning messages will be shown.
• Unchecked - Warning messages will not be shown. If there is no error, a validation report will not be created.

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

• Checked - New validation worksheet will be generated when each validation is triggered.
• Unchecked - Keep using existing validation worksheet to add the validation warning or error message.

Package

AlchemyJ supports building an API using multiple workbooks. These workbooks must be located in the same folder.

Available workbooks - Display a list of the workbook 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.

2. Category - Preview

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

Calculate All

Calculates all worksheets of all opened workbooks without running any AlchemyJ function. It is the same as the Calculate now (F9) function in the Formulas tab.

Calculate sheet

Calculates the current worksheet without running any AlchemyJ function. It is the same as the Calculate Sheet (Shift+F9) function in the Formulas tab.

3. Category - Test

Generate Test Template

Asks user to select a function point in the AlchemyJ workbook and creates a template with test case and test run worksheet. When creating test case worksheet, the case input data and expected result shall be automatically created base on the selected function point input and output parameter defined in API worksheet.

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.

Execute Test Run

Selects one ##Test Run worksheet and trigger the test cases execution. If there is only one ##Test Run worksheet, will execute the test case directly and no need to select the ##Test Run worksheet.

Test Cases

Append Test Cases

Appends new test cases in the selected test case worksheet. Default is 10 test cases and user can revise the no. of cases to be appended, maximum is 100.

Load Test Case to Model

Loads the selected test case data into function point model cells in API definition workbook.

Copy Test Result

Copy Test Result to Empty Expected Test Result

Copies the 'Studio Test Result' columns value to empty 'Studio Expected Result' and 'API Expected Result' columns. If 'Studio Expected Result' and 'API Expected Result' column is not empty, the value should not be copied from 'Studio Test Result' column.

Copy Selected Result to Expected Test Result

Selects one test case and copies 'Studio Test Result' column values to 'Studio Expected Result' columns. Even if the 'Studio Expected Result' column values are not empty, the values shall be override.

Backup Test Template

Creates a copy for the test template file in the same folder, file creation time stamp shall be appended in the backup test template file name.

4. Category - Export

Validate

Validates the setting and formula of an API. A validation 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

Selects a project to generate API.

5. Category - Troubleshoot

Inspector Setting

Adds a ##InspectorSetting worksheet. One workbook can only have one Inspector Setting worksheet. The sheet name cannot be renamed.

Activate Inspector Setting

Generates a JSON file according to the defined inspector settings in the ##InspectorSetting worksheet.

API Inspector

Launches AlchemyJ API Inspector Console.

6. Category - Tools

Go to Sheet

Go to a specified worksheet.

Encrypt Password

Enables user to encrypt password fields on the workbook such as the data source password. 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).

7. Category - Help

About

View the AlchemyJ version and the software license agreement information.