| Entering data: Drop-down lists |
GS-Calc (ver. 17 and later) can be configured to use functions written by users in C/C++ as plain DLL libraries. Such functions can be even used as a replacement for the default built-in functions. They can accept and return all the argument types and additionally they can also "return" images displayed directly in worksheets or messages displayed after each update. What's important, using these functions GS-Calc retains the complete multicore support, the same speed and memory requirements (e.g. no extra arrays are allocated when passing ranges as arguments). You can add virtually any number of such functions.
Libraries are added by the Settings > Imported Function Libraries command as shown on the screenshot below. The internal function names MUST match the original C/C++ names. The displayed names are what is entered in worksheet cells and the only requirement is that they must be unique.
The added functions must be declared as
typedef double(__cdecl *DLL_EXT_FUNCTION)(GSCalcArg *arg);
where GSCalcArg is a structure enabling you to pass up to 15 arguments: numbers, strings and ranges/arrays.
struct ArrayItem
{
uint8_t type; // DLL_ARGT_EMPTY | DLL_ARGT_DOUBLE | DLL_ARGT_TEXT
uint8_t err_code; // ERROR1_DIV_BY_ZERO...ERROR1_SYNTAX_ERROR
uint16_t col; // [0, 4095]
uint32_t row; // [0, 12582911]
double num; // numeric cell
char text[MAXINPUT + 1];// text cell; max. 1024+NULL char. utf-8 string
};
typedef int(__cdecl *READ_ARRAY)(void *env, int array_index, ArrayItem *val);
typedef int(__cdecl *WRITE_ARRAY)(void *env, int array_index, ArrayItem *val);
typedef void* (__cdecl *MALLOC)(size_t s);
typedef void(__cdecl *MFREE)(void *_Memory);
struct GSCalcArg
{
uint8_t types[16]; // input: DLL_ARGT_NUMBER | DLL_ARGT_TEXT | DLL_ARGT_ARRAY | DLL_ARGT_EMPTY
// output: as above + DLL_ARGT_IMAGE | DLL_ARGT_MESSAGE
uint8_t errors[16]; // ERROR1_DIV_BY_ZERO...ERROR1_SYNTAX_ERROR
double numbers[16];
char *strings[16]; // input (UTF-8) 0...14 strings are preallocated by GS-Calc and must not be overwritten;
// strings[15] is used for output strings and already points to a MAXINPUT+1 char. temp. buffer
struct Dims
{
uint32_t cx;
uint32_t cy;
} array_dims[16]; // dimensions of the subsequent and grouped together range/array arguments passed from GS-Calc
struct
{
BYTE *data; // DIB data if you return types[15] = DLL_ARGT_IMAGE; must be (de-)allocated with memory_alloc/memory_free
size_t size; // DIB data size
Dims dims; // optional resizing when displaying the image (returned either as a DIB or a file path)
MALLOC memory_alloc;
MFREE memory_free;
} image;
READ_ARRAY read_array; // returns ArrayItem.type
WRITE_ARRAY write_array; // returns ArrayItem.type or -1 if the out-of-memory condition occurs
void *env; // internal data that must be passed back in read_array/write_array calls
};
Indices 0...14 in types[] and errors[] in GSCalcArg represent all subsequent argument types and errors from left to right passed from GS-Calc.
Indices 0...14 in numbers[], strings[] and array_dims[] point to argument values. The original parameters are already divided into these 3 groups (numbers, strings and ranges/arrays) when your functions is called. For example, if some worksheet cell execute your function
my_function(1.0, 2, "abc", a5:b15,)
the parameters passed to the DLL in the GSCalcArg structure will be as follows:
types[0] = DLL_ARGT_DOUBLE, types[1] = DLL_ARGT_DOUBLE, types[2] = DLL_ARGT_TEXT, types[3] = DLL_ARGT_ARRAY, types[4] = DLL_ARGT_EMPTY
numbers[0] = 1, numbers[1] = 2, strings[0] = "abc", array_dims[0] = {2, 10}
You can access numbers and strings directly. Array elements can be read (and write on output) using the read_array() (and write_array())
functions as shown below in the filter function example. The first argument is the "env" internal pointer from the GSCalcArg structure, the 2nd is the array index (0-14) as passed in array_dims[] and the last is a pointer to the ArrayItem structure with the "col" and "row" of the requested element.
Note: for very large array, for best possible performance while retrieving data from data from arrays or writing data to arrays, subsequent read_array() (and write_array()) calls MUST access the array elements in the left-to-right and top-to-bottom order.
On output, the index 15 (DLL_ARGC_RET) is used to specify the returned type (in types[15]), possible error code (in errors[15]) and value
(in numbers[15], strings[15] or array_dims[15]). The values types[15] and errors[15] MUST always be set correctly on return.
Note: the strings[15] already contain a valid buffer (up to 1024 characters + NULL) allocated by GS-Base and if a given function
returns a text value, it must be copied to that buffer as a NULL terminated string.
Examples:
// messageIf(condition, text)
//
// arguments:
// condition, message text
// returns:
// if condition != 0, 'messageIf' displays 'text' as a message box after each update/recalculation
// otherwise the message text is displayed in a cell as normal text
__declspec(dllexport) double __cdecl messageIf(GSCalcArg *arg)
{
uint8_t errorCode = 0;
if (arg->types[0] != DLL_ARGT_DOUBLE && arg->types[0] != DLL_ARGT_EMPTY || arg->types[1] != DLL_ARGT_TEXT)
errorCode = ERROR1_INVALID_VALUE;
if (arg->types[2] != DLL_ARGT_NONE) // more than 2 arguments detected
errorCode = ERROR1_SYNTAX_ERROR;
if (!errorCode && arg->errors[0])
errorCode = arg->errors[0];
if (!errorCode && arg->errors[1])
errorCode = arg->errors[1];
if (!errorCode)
{
::strncpy(arg->strings[DLL_ARGC_RET], arg->strings[0], 1024);
arg->strings[DLL_ARGC_RET][1024] = 0;
return arg->types[DLL_ARGC_RET] = (arg->numbers[0] ? DLL_ARGT_MESSAGE : DLL_ARGT_TEXT);
}
else
{
arg->errors[DLL_ARGC_RET] = errorCode;
return arg->types[DLL_ARGC_RET] = DLL_ARGT_EMPTY;
}
return 0;
}
// filter(range, column, number)
//
// arguments:
// range - a range/array
// column - column index (from 0 to the number of columns in 'range' - 1)
// number - numeric value to perform equality check
// returns:
// an array consisting of rows of 'range' containing 'number' in the specified column
__declspec(dllexport) double __cdecl filter(GSCalcArg *arg)
{
uint8_t errorCode = 0;
if (arg->types[0] != DLL_ARGT_ARRAY || arg->types[1] != DLL_ARGT_DOUBLE || arg->types[2] != DLL_ARGT_DOUBLE)
errorCode = ERROR1_INVALID_VALUE;
if (arg->types[3] != DLL_ARGT_NONE)
errorCode = ERROR1_SYNTAX_ERROR;
if (!errorCode && arg->numbers[0] >= arg->array_dims[0].cx)
errorCode = ERROR1_INVALID_VALUE;
if (!errorCode && arg->errors[0])
errorCode = arg->errors[0];
if (!errorCode && arg->errors[1])
errorCode = arg->errors[1];
if (!errorCode && arg->errors[2])
errorCode = arg->errors[2];
ArrayItem x = { 0 };
int outRow = 0;
for (uint32_t inRow = 0; inRow < arg->array_dims[0].cy && !errorCode; ++inRow)
{
x.row = inRow;
x.col = static_cast<uint16_t>(arg->numbers[0]);
if (arg->read_array(arg->env, 0, &x) == -1)
{
errorCode = x.err_code;
break;
}
bool match = false;
if (x.type == DLL_ARGT_DOUBLE)
match = (x.num == arg->numbers[1] || x.err_code && x.err_code == arg->errors[2]);
else if (x.type == DLL_ARGT_EMPTY)
match = (arg->numbers[1] == 0);
else
errorCode = ERROR1_INVALID_VALUE;
if (match)
{
for (x.col = 0; x.col < arg->array_dims[0].cx && !errorCode; ++x.col)
{
x.row = inRow;
arg->read_array(arg->env, 0, &x);
x.row = outRow;
// for best performance when writing to very large arrays, try to write in the left-to-right and top-to-bottom order
if (arg->write_array(arg->env, DLL_ARGC_RET, &x) == -1) // -1 means the out-of-memory condition or an invalid row/column
errorCode = x.err_code;
}
++outRow;
}
}
arg->errors[DLL_ARGC_RET] = (!outRow ? ERROR1_NULL_VALUE : 0);
return arg->types[DLL_ARGC_RET] = DLL_ARGT_ARRAY;
return 0;
}
// image(path, cx, cy)
//
// arguments:
// path - a bmp, jpg, png, gif file path
// cx/cy - display image size (optional)
// returns:
// loads an image (bmp, jpg, png, gif) from a disk and displays it, optionally resizing it
// to the cx/cy dimensions (in screen pixels)
__declspec(dllexport) double __cdecl image(GSCalcArg *arg)
{
uint8_t errorCode = 0;
if (arg->types[0] != DLL_ARGT_TEXT ||
arg->types[1] != DLL_ARGT_DOUBLE && arg->types[1] != DLL_ARGT_EMPTY ||
arg->types[2] != DLL_ARGT_DOUBLE && arg->types[2] != DLL_ARGT_EMPTY)
errorCode = ERROR1_INVALID_VALUE;
if (arg->types[3] != DLL_ARGT_NONE)
errorCode = ERROR1_SYNTAX_ERROR;
if (!errorCode && arg->errors[0])
errorCode = arg->errors[0];
::strcpy_s(arg->strings[DLL_ARGC_RET], MAXINPUT, arg->strings[0]);
arg->image.dims.cx = static_cast<uint32_t>(arg->numbers[0]);
arg->image.dims.cy = static_cast<uint32_t>(arg->numbers[1]);
// arg->image.data must remain NULL
arg->errors[DLL_ARGC_RET] = errorCode;
return arg->types[DLL_ARGC_RET] = DLL_ARGT_IMAGE;
}
Complete gsclib.h header file that must be included in each DLL project
A sample complete project with the messageIf, filter, image and fractal functions: