AlchemyJ Extended Functions Index (By Category)

AlchemyJ provides over 90 Extended functions to support API modeling in Excel.

Address Functions

Function Name	Function Description
ajAddress	Returns a cell reference or a range reference as text.

Big Number Functions

Function Name	Function Description
ajBigNumAbs	Returns a BigNumber whose value is the absolute value, i.e. the magnitude, of the value of BigNumber.
ajBigNumCompareTo	Compares if BigNumber number1 is greater than number2. Returns 1 if number1 is greater than number2.
ajBigNumDivide	Returns a BigNumber whose value is the value of BigNumber number1 divided by number2.
ajBigNumDivideToInteger	Returns a BigNumber whose value is the integer part of dividing the value of BigNumber number1 by number2.
ajBigNumEquals	Returns true if the value of BigNumber number1 is equal to the value of number2, otherwise returns false
ajBigNumInt	Returns a BigNumber whose value is the value of BigNumber rounded to an integer using rounding mode round_mode.
ajBigNumIsGreater	Returns true if the value of BigNumber number1 is greater than the value of number2, otherwise returns false.
ajBigNumIsGreaterEqual	Returns true if the value of BigNumber number1 is greater than or equal to the value of number2, otherwise returns false.
ajBigNumIsInteger	Returns true if the value of BigNumber is an integer, otherwise returns false.
ajBigNumIsLess	Returns true if the value of BigNumber number1 is less than the value of number2, otherwise returns false.
ajBigNumIsLessEqual	Returns true if the value of BigNumber number1 is less than or equal to the value of number2, otherwise returns false.
ajBigNumIsNegative	Returns true if the sign of BigNumber is negative, otherwise returns false.
ajBigNumIsPositive	Returns true if the sign of BigNumber is positive, otherwise returns false.
ajBigNumIsZero	Returns true if the value of BigNumber is zero or minus zero, otherwise returns false.
ajBigNumMax	Returns a BigNumber whose value is the maximum of the arguments.
ajBigNumMin	Returns a BigNumber whose value is the minimum of the arguments.
ajBigNumMod	Returns a BigNumber whose value is the value of BigNumber number1 modulo number2, i.e. the integer remainder of dividing BigNumber number 1 by number2.
ajBigNumMultiply	Returns a BigNumber whose value is the value of BigNumber number1 multiplied by number2.
ajBigNumNegate	Returns a BigNumber whose value is the value of BigNumber negated, i.e. multiplied by -1.
ajBigNumNumberValue	Returns the value of BigNumber as an Excel number value.
ajBigNumPower	Returns a BigNumber whose value is the value of BigNumber number1 exponentiate by number2.
ajBigNumPrecision	Returns a BigNumber whose value is the value of BigNumber rounded to a precision defined in trailing_zeros.
ajBigNumRand	Returns a new BigNumber with a pseudo-random value equal to or greater than 0 and less than 1.
ajBigNumRound	Returns the value of BigNumber after rounded.
ajBigNumShift	Returns a BigNumber whose value is the value of BigNumber number1 shifted by the no. of places defined in number2.
ajBigNumSqrt	Returns a BigNumber whose value is the square root of the value of BigNumber.
ajBigNumSubtract	Returns a BigNumber whose value is the value of BigNumber number1 minus number2.
ajBigNumSum	Returns a BigNumber whose value is the value of BigNumber number1 plus number2.
ajBigNumSumRange	Returns a BigNumber whose value is the sum of the arguments.

Big String Functions

Function Name	Function Description
ajBigStrLeft	Returns the specified number of characters from the start of Big String text.
ajBigStrLen	Returns the length of Big String text.
ajBigStrLower	Converts all characters in Big String text to lowercase.
ajBigStrMid	Returns the characters from the middle of a Big String text, given a starting position and length.
ajBigStrProper	Converts the Big String text to proper case; the first letter in each word is uppercase, and all other letters to lowercase.
ajBigStrReplace	Replaces part of a Big String text with a different text string.
ajBigStrRept	Repeats Big String text a given number of times.
ajBigStrRight	Returns the specified number of characters from the end of Big String text.
ajBigStrSet	Converts a cell value to a big string.
ajBigStrSubstitute	Replaces existing text with new text in a Big String.
ajBigStrTextJoin	Concatenates a list or range of Big String text using a delimiter.
ajBigStrTextSplit	Splits Big String text with a delimiter.
ajBigStrTrim	Removes all spaces from a Big String text except for single spaces between words.
ajBigStrUpper	Converts a Big String text to all uppercase letters.
ajBigStrValue	Converts a big string to a cell value. Only the number of characters that can fit in a cell will be converted.

Database Functions

Function Name	Function Description
ajDBCountRecord	Counts the number of records in a database table or a view.
ajDBCreateRecord	Inserts the data into a database table.
ajDBDeleteRecord	Delete records from a database table.
ajDBExportToWorkbook	Reads records from specified database table and writes to a specific range in the target workbook.
ajDBGetLOB	Retrieve a large object (such as a file) from database table and save it as a file.
ajDBImportFromWorkbook	Reads a range of cell values in the source workbook and inserts new records or update the existing records in a database table.
ajDBMergeRecord	Inserts a new record or update existing records in a database table.
ajDBReadRecord	Retrieves records from a database table or a view.
ajDBRunSQL	Executes a SQL statement and returns the result.
ajDBRunSPToWorkbook	Executes a stored procedure and writes the result to the target workbook..
ajDBRunStoredProc	Executes a stored procedure and returns the result.
ajDBType	Returns the database type of a data source.
ajDBUpdateLOB	Uploads a file to a database table.
ajDBUpdateRecord	Update existing records in a database table.

Error Handling Functions

Function Name	Function Description
ajModelError	Retrieves error records from the Model Error List.
ajRaiseError	Adds an error element into the Model Error List.
ajSystemError	Retrieves error records from the System Error List.

External Resources Functions

Function Name	Function Description
ajCustomFunction	Executes a native Java class method at runtime.
ajSendEmail	Sends an email to recipients with the given subject, message, and attachments.
ajWebService	Returns data from a web service on the internet or intranet.
ajWebServiceREST	Returns data from a REST web service on the internet or intranet.
ajWebServiceSOAP	Returns data from a SOAP web service on the internet or intranet.

File Functions

Function Name	Function Description
ajCopyFile	Copies a file from source folder to the destination folder
ajDeleteFile	Deletes the specified file.
ajFileExists	Checks if the file exists. Returns True if exists, otherwise False.
ajFileProperty	Returns full list of file properties of the specified file/folder.
ajMoveFile	Moves the file from source folder to the destination folder.
ajReadFile	Imports file data into an Excel worksheet.
ajReadWorkbook	Reads a range of cell values in the target workbook into the current workbook.
ajRenameFile	Changes the name of a file.
ajWriteFile	Exports a range of cell values to a file.
ajWriteWorkbook	Writes a range of cell values to a specific range in the target workbook.

Folder Functions

Function Name	Function Description
ajCopyFolder	Copies file(s) from source folder to the destination folder
ajCreateFolder	Creates a folder in the destination path.
ajDeleteFolder	Deletes the specified folder.
ajFolderExists	Checks if the folder exists. Returns True if exists, otherwise False.
ajFolderList	Lists all files/sub-folders information from a folder.
ajMoveFolder	Moves the folder from source path to the destination path.
ajRenameFolder	Changes the name of a folder.

JSON Functions

Function Name	Function Description
ajJSON	Converts a range of name and value pair data to a JSON string.
ajJSONFromSchema	Converts multiple ranges of data or tables to a JSON string. The relationship between the ranges is based on a data relationship schema.
ajJSONFromTable	Converts a single range of data or a table to a JSON string.
ajJSONPath	Gets the result of a JSON path expression based on the supplied JSON string.
ajJSONToTable	Converts a JSON string to an array of data.

LDAP Functions

Function Name	Function Description
ajLDAPGroupMembers	Returns the user or sub-groups in a group from a LDAP directory.
ajLDAPLogin	Authenticates users with LDAP directory.
ajLDAPUserDetails	Returns the user attributes of a user.
ajLDAPUserInGroups	Returns the groups a user is in.
ajLDAPUserList	Returns a list of users the match the filter criteria.

Validate Functions

Function Name	Function Description
ajCheckDataItem	Validates and transform data based on a given data dictionary component.

XML Functions

Function Name	Function Description
ajXML	Converts a range of name and value pair data to an XML string.
ajXMLFromSchema	Converts multiple ranges of data or tables to an XML string. The relationship between the ranges is based on a data relationship schema.
ajXMLFromTable	Converts a single range of data or a table to an XML string.
ajXMLToTable	Converts an XML string to an array of data.

Other Functions

Function Name	Function Description
ajCache	Transforms array formula into individual cell formula.
ajEnviron	Returns the value of an environment variable.
ajFilter	Filters a range of data based on the defined criteria.
ajRegExpCount	Counts the number of times that a pattern occurs in a string using regular expression.
ajRegExpMatch	Searches an input string for a substring that matches a regular expression pattern.
ajSort	Sorts the contents of a range or array.
ajTextSplit	Splits text with a delimiter.