Macro Examples
The following examples demonstrate possible uses for macros as well as their syntax. For an overview of the functions and objects exposed to Macro Engine, please refer to our Macro Engine API Documentation. You can also download this documentation as a zip archive.
Macros started by a spreadsheet function
Hiding/unhiding a row
In the example below, the two simple functions hide() and unhide() are shown. The hide() function sets the value in A1 to TRUE, while the unhide() function sets the value in A1 to FALSE.
These macros have been assigned to the buttons "Hide row" and "Unhide row", as shown in the following screenshot:
The HIDEROW(A1) function in A3 reacts to the value in A1, either hiding or unhiding row 3.
Setting a border
or
Note: only the following border types are possible: top, bottom, left, right, out, ins_horiz, ins_vert, ins, all. The values are case-sensitive.
Setting a target cell
The function setCell() defines C8 as the target cell. If the target cell contains the value "locked", then the content is deleted. If nothing is in C8, the value "locked" is written into the cell.
function setCell() {
$range = activesheet()->range('C8');
if ($range->value == 'locked') {
$newVal = '';
} else {
$newVal = 'locked';
}
$range->value=$newVal;
}
Getting a cell value from another worksheet
The function getcell() gets the value from Sheet2!B2 and writes it into cell C10 of the active sheet.
function getcell(){
$value = activeworkbook()->sheets('Sheet2')->Range('B2')->value;
$target = activesheet()->range('C10');
$target->value=$value;
}
Exporting a workbook to the hard drive
The following code exports the workbook to the server's hard drive.
function example()
{
// save the current workbook as xlsx
activeworkbook()->export('C:\\dump.xlsx', 'xlsx', 0);
// save the current workbook as full xlsx snapshot
activeworkbook()->export('C:\\dump_snapshot.xlsx', 'xlsx', 1);
// save the current workbook as wss
activeworkbook()->export('C:\\dump.wss', 'wss', 0);
// save the current workbook as full wss snapshot
activeworkbook()->export('C:\\dump_snapshot.wss', 'wss', 1);
}
Parameters | |
savepath | The file path for the exported workbook |
type | The file type of the export (XLSX for Excel 2010+ or WSS for Jedox) |
mode |
The export mode. 0 = no snapshot |
Note: this code will export the file into a local folder on the server, so it may not be accessible to remote users. For this, you have to export into a folder in the Apache docroot, then dynamically create and display a link to the file. Not all folders on the file system are accessible for the Macro Engine, nor should they be; the open_basedir directive of the Macro Engine may prevent you from exporting to a specific folder.
Checking for "error" type
It is possible to check whether some resource (cell reference, named formula, or variable) currently holds a value of "error" type, such as #Value! or #N/A. For example, the following code is executed when a workbook is opened, checks if some variable currently holds an error value, and then initializes the variable to a fixed value:
function __open() {
$varvalue = retrieve_variable("myyear");
if ( $varvalue instanceof variant_error) {
define_variable("myyear", "2011");
}
}
Wrapper function for sending an e-mail.
The function will use the e-mail server configured in the "Tasks" settings group in the Jedox Web Settings panel.
mixed mailer_send_mail(mixed to, string subject, string body, string from, mixed cc, mixed bcc, string attachment)
Parameter | Input | Description |
$to | string/array | A single e-mail address as string, or an array of e-mail addresses, definining the recipient(s) |
$subject | string | The e-mail subject. |
$body | string | A string representig plaintext, or HTML body of the e-mail message |
$from | string | Optional; a string specifying a single e-mail address of the sender, different from the account used for sending the e-mail. Note that usage of $from parameter requires the SMTP server to accept such e-mails ("spoofing"). Additionally, it has to be enabled in the Settings in Jedox Web, by adding a new key of type "boolean", with the name tasks.smtp.allow_spoofing |
$cc | string/array | Optional; a single e-mail address as string, or an array of e-mail addresses, definining the CC recipient(s) |
$bcc | string/array | Optional; a single e-mail address as string, or an array of e-mail addresses, definining the BCC recipient(s) |
$attachment | string | Optional; a path to a single file, to be sent as attachment. The file must be readable on the file system for Jedox Spreadsheet Server. Use a forward slash as path separator. |
To use the function, you have to require the mailer library in your macro; see example below. Note that the mailer does not support unencrypted sending of mails. Depending on the "starttls_enable" setting value, it will send e-mails either using TLS, or SSL.
//load the integrator macro library
require library('mailer');
function mailer_example()
{
//define several parameters
$to = 'recipient@example.com';
$subject = 'This is a test';
$body = 'I hope this test e-mail finds you well. There is no need to reply, as this is really just a test.';
//send the e-mail
$mailer_result = mailer_send_mail($to, $subject, $body);
//show the result in a popup dialog
return __msgbox('Number of e-mails sent: '.$mailer_result);
}
Macros started by a form element
The following macro examples will work only if the macro is started by a form element (combo box, check box, or button). They do not work when they are called as a spreadsheet function.
Displaying a popup message
Show_popup() gets the text of C4 and displays it in an info message box titled "Test". Warning ("warn") and error ("err") messages are also available.
function show_popup() {
$value = ActiveSheet()->;Range('C4')->;value;
// return command to show popup; arguments: message body, message header, message type
return __msgbox($value, 'Test', 'Info');
}
Executing a hyperlink
The code below will execute a hyperlink to a specific location:
function Button_Hyperlink()
{
return __hyperlink('/Demo Spreadsheets/Demos/Bikers Best/Navigation/Bikers Best Navigation', 'test link');
}
Or, if the Hyperlink is present in some cell, you can simply refer to that cell:
function Button_Hyperlink()
{
return __hyperlink('A1');
}
Recalculating a spreadsheet
function Button_Recalc() { return __recalc(); }
Logging out
function logout_btn() {
return __logout();
}
Using system variables to connect to OLAP
In the Macro Engine, Jedox functions can be defined using an already-established OLAP session.
Instead of creating the following connection:
$host = '127.0.0.1';
$port = '7777';
$username = 'admin';
$password = 'adminpass';
$connection = palo_init($host, $port, $username, $password);
You can establish the connection as follows:
$host = '127.0.0.1';
$port = '7777';
$connection = palo_init($host, $port, $_JEDOX['OLAP_SESSION_ID']);
Rather than using a static user/role, this macro uses the actual user who is logged in, including their actual rights.
An even better way to establish the connection is to also make the values for In-Memory DB host and port dynamic, to allow for portability of the code between varying Jedox environments:
$host = $_JEDOX['OLAP_HOST'];
$port = $_JEDOX['OLAP_PORT'];
$connection = palo_init($host, $port, $_JEDOX['OLAP_SESSION_ID']);
Closing the connection to OLAP
At the end of your session, close the connection to OLAP. If the connection is not closed, unwanted side effects like performance issues might occur. To close the connection, use this code:
palo_disconnect($connection);
Updated September 27, 2022