Introduction

Spreadsheet Form Element allow users to perform many actions in an excel-like environment, depending on the use cases. A few examples of uses of spreadsheets are:

Database editing
Configuration controlling
Data merging
Workforce planning
Sales reporting
Financial analysis

Figure 1 : Sample Spreadsheet Form Element in the Userview

Spreadsheet Properties

Main Properties

Name

Description

Screens (Click to view)

Spreadsheet

Element ID of the form element.

Figure 2 : Spreadsheet Properties

Label

Spreadsheet label/title.

Columns

The spreadsheet column(s) is defined here.

Sub-element	Description
Value	Field ID of the column.
Label	Column header title/label. You can also include a tooltip that shows up when someone hovers over the column label. Sample Example label: <span title='This tooltip will show when someone hovers over the column'>Column Label A</span>
Format Type	Defines the type of input to store. Default format type is Text. Available Data Types: Text - format the value as text Numeric - accepts a numeric value (See Format documentation) Date - accepts a date type value Time - accepts a time type value Checkbox - appears as a checkbox to check Dropdown - appears as a dropdown menu to select options Autocomplete - autocompletes any known phrases Password - obscures any alphanumerical value into a password File - Accept file upload and render a file download link Image - Accept image file upload and render a thumbnail URL - Convert value to an URL Hidden - Hidden column for storing formular value Custom - Use custom setting to configure the column. Examples: Spreadsheet Custom Formats
Format	Defines the format to show data on the spreadsheet based on the chosen format type. Numeric - Key in format accepted by numbrojs library. (For example, 0.00 is to create 2 decimal places) Date - Key in format accepted by moment.js library. (Default value: DD/MM/YYYY) Time - Key in format accepted by moment.js library.(Default value: h:mm:ss a) Dropdown - Key in the Form ID that contains the equivalent dropdown element. Dynamic Cascading Select Box Dynamic Cascading Select Box is supported when the target select box is making use of Options Binder and with the attribute "Use AJAX for cascade options?" is checked. Autocomplete - See handsontable for samples. File- Key in Form Def ID that contains File Upload form element. Image - Key in Form Def ID that contains Image Upload form element. URL - Provide an URL syntax. Example: http://www.joget.org?id={id}&name={name} Hidden - Key in default value to be saved. Custom - See handsontable for samples. Please put the setting json in {{ and }}. Example: {{"editor":"text"}} Please note that any string within {{ and }} will be treat as setting json and used to configure the column.
Regex Validation	Validates input value to match the defined Regular Expression pattern.
Formula	Defines a formula to perform a calculation, for example: "Quantity * Unit_Price" in Formula column. Please refer to formula.
Readonly	Defines if the column is editable.

UI Properties

Name	Description	Screens (Click to view)
Enable Header Sorting?	Determines if users can sort spreadsheet data by column in ascending or descending order.	Figure 3 : UI Properties
Data Order Field ID	Field to keep the ordering sequence. Must correspond with a field id in the target form.
Readonly	Defines if the entire spreadsheet is editable.
Disable Add Feature	Determines if a new row can be added.
Disable Delete Feature	Determines if a row can be removed.
Show Row Numbering?	Show additional column on the leftmost to denote numbering. Sample Row numbering checked.
Number of columns to fixed on left	Allows to specify the number of fixed (or frozen) columns on the left of the table. Default Value: 0
Number of spare rows	Number of spare row to be added automatically after lines with values. Sample 1 spare row
Custom Settings (JSON)	Refer to plugins and APIs from https://handsontable.com/docs/6.2.2/tutorial-introduction.html for more customizations. Example: { selectionMode: 'single' }

Validation & Data Binder Properties

Name	Description	Screens (Click to view)
Validator	Attach a Validator plugin to validate the input value. Please see Form Validator. When will validation takes place? Validation will takes place whenever form is submitted except when it is submitted as "Save as Draft".	Figure 4: Validation & Data Binder properties
Min Number of Row Validation (Integer)	Defines the minimum number of rows required for input.
Max Number of Row Validation (Integer)	Defines the maximum number of rows possible for input.
Error Message	Error message to be shown when row requirements set above is not met.
Data Binder
Load & Save Binders	Load Binder allows you to customize how and where the data are retrieved from, to populate the multi-row spreadsheet in the form. Save Binder allows you to customize how and where the spreadsheet row records are saved to. Empty by default. An empty binder means the spreadsheet records will be saved/loaded as a JSON format in the parent form & database table. See the list of available Form Binders. Recommended binder to use is the Multirow Form Binder so that each record are saved into a child database table via a subform definition. The binder will update the foreign key into each record that points to the parent database table.

Supported Formula Functions

The Spreadsheet element implemented partial of Excel formula functions as follows. For detail usage, please refer to Excel Formula Functions .

ABS(number)
ACOS(number)
ACOSH(number)
ACOT(number)
ACOTH(number)
ADD(num1, num2)
AGGREGATE(function_num, options, ref1, ref2)
AND(logical1, [logical2], ...)
ARABIC(text)
ARGS2ARRAY(arg1, [arg1], ...)
ASIN(number)
ASINH(number)
ATAN(number)
ATAN2(number_x, number_y)
ATANH(number)
AVEDEV(number1, [number2], ...)
AVERAGE(number1, [number2], ...)
AVERAGEA(number1, [number2], ...)
BASE(number, radix, min_length)
BESSELI(x, n)
BESSELJ(x, n)
BESSELK(x, n)
BESSELY(x, n)
BETA.DIST(x, alpha, beta, cumulative, A, B)
BETA.INV(probability, alpha, beta, A, B)
BETADIST(x, alpha, beta, cumulative, A, B)
BETAINV(probability, alpha, beta, A, B)
BIN2DEC(number)
BIN2HEX(number, places)
BIN2OCT(number, places)
BINOM.DIST(successes, trials, probability, cumulative)
BINOM.DIST.RANGE(trials, probability, successes, successes2)
BINOM.INV(trials, probability, alpha)
BINOMDIST(successes, trials, probability, cumulative)
BITAND(number1, number2)
BITLSHIFT(number, shift)
BITOR(number1, number2)
BITRSHIFT(number, shift)
BITXOR(number1, number2)
CEILING(number, significance, mode)
CEILINGMATH(number, significance, mode)
CEILINGPRECISE(number, significance, mode)
CHAR(number)
CHISQ.DIST(x, k, cumulative)
CHISQ.DIST.RT(x, k)
CHISQ.INV(probability, k)
CHISQ.INV.RT(p, k)
CHOOSE(index_num, value1, [value2], ...)
CLEAN(text)
CODE(text)
COMBIN(number, number_chosen)
COMBINA(number, number_chosen)
COMPLEX(real, imaginary, suffix)
CONCATENATE(arg1, [arg1], ...)
CONFIDENCE(alpha, standard_dev, size)
CONFIDENCE.NORM(lpha, standard_dev, size)
CONFIDENCE.T(lpha, standard_dev, size)
CONVERT(number, from_unit, to_unit)
CORREL(array1, array2)
COS(number)
COSH(number)
COT(number)
COTH(number)
COUNT(arg1, [arg1], ...)
COUNTA(arg1, [arg1], ...)
COUNTBLANK(arg1, [arg1], ...)
COUNTUNIQUE(arg1, [arg1], ...)
COVARIANCE.P(array1, array2)
COVARIANCE.S(array1, array2)
CSC(number)
CSCH(number)
CUMIPMT(rate, periods, value, start, end, type)
CUMPRINC(rate, periods, value, start, end, type)
DATE(year, month, day)
DATEVALUE(date_text)
DAY(serial_number)
DAYS(end_date, start_date)
DAYS360(start_date, end_date, method)
DB(cost, salvage, life, period, month)
DDB(cost, salvage, life, period, factor)
DEC2BIN(number, places)
DEC2HEX(number, places)
DEC2OCT(number, places)
DECIMAL(number, radix)
DEGREES(number)
DELTA(number1, number2)
DEVSQ(number1, [number2], ...)
DIVIDE(dividend, divisor)
DOLLAR(number, decimals)

DOLLARDE(dollar, fraction)
DOLLARFR(dollar, fraction)
E()
EDATE(start_date, months)
EFFECT(rate, periods)
EOMONTH(start_date, months)
EQ(value1, value2)
ERF(lower_bound, upper_bound)
ERFC(x)
EVEN(number)
EXACT(text1, text2)
EXPON.DIST(x, lambda, cumulative)
EXPONDIST(x, lambda, cumulative)
F.DIST(x, d1, d2, cumulative)
F.DIST.RT(x, d1, d2)
F.INV(probability, d1, d2)
F.INV.RT(p, d1, d2)
FACT(number)
FACTDOUBLE(number)
FALSE()
FDIST(x, d1, d2, cumulative)
FDISTRT(x, d1, d2)
FIND(find_text, within_text, position)
FINV(probability, d1, d2)
FINVRT(p, d1, d2)
FISHER(x)
FISHERINV(y)
FIXED(number, decimals, no_commas)
FLOOR(number, significance)
FORECAST(x, data_y, data_x)
FREQUENCY(data, bins)
FV(rate, periods, payment, value, type)
FVSCHEDULE(principal, schedule)
GAMMA(number)
GAMMA.DIST(value, alpha, beta, cumulative)
GAMMA.INV(probability, alpha, beta)
GAMMADIST(value, alpha, beta, cumulative)
GAMMAINV(probability, alpha, beta)
GAMMALN(number)
GAMMALN.PRECISE(x)
GAUSS(z)
GCD(GCD)
GEOMEAN(number1, [number2], ...)
GESTEP(number, step)
GROWTH(known_y, known_x, new_x, use_const)
GTE(num1, num2)
HARMEAN(number1, [number2], ...)
HEX2BIN(number, places)
HEX2DEC(number)
HEX2OCT(number, places)
HOUR(serial_number)
HTML2TEXT(value)
HYPGEOM.DIST(x, n, M, N, cumulative)
HYPGEOMDIST(x, n, M, N, cumulative)
IF(test, then_value, otherwise_value)
IMABS(inumber)
IMAGINARY(inumber)
IMARGUMENT(inumber)
IMCONJUGATE(inumber)
IMCOS(inumber)
IMCOSH(inumber)
IMCOT(inumber)
IMCSC(inumber)
IMCSCH(inumber)
IMDIV(inumber1, inumber2)
IMEXP(inumber)
IMLN(inumber)
IMLOG10(inumber)
IMLOG2(inumber)
IMPOWER(inumber, number)
IMPRODUCT(inumber1, inumber2, ...)
IMREAL(inumber)
IMSEC(inumber)
IMSECH(inumber)
IMSIN(inumber)
IMSINH(inumber)
IMSQRT(inumber)
IMSUB(inumber1, inumber2)
IMSUM(inumber1, inumber2, ...)
IMTAN(inumber)
INT(number)
INTERCEPT(known_y, known_x)
INTERVAL(second)
IPMT(rate, period, periods, present, future, type)
IRR(values, guess)
ISBINARY(number)
ISBLANK(value)
ISEVEN(number)
ISLOGICAL(ISNONTEXT)

ISNONTEXT(ISNONTEXT)
ISNUMBER(value)
ISODD(number)
ISOWEEKNUM(date)
ISPMT(rate, period, periods, value)
ISTEXT(value)
JOIN(array, separator)
KURT(number1, [number2], ...)
LCM(number1, [number2], ...)
LEFT(text, number)
LEN(text)
LINEST(data_y, data_x)
LN(number)
LOG(number, base)
LOG10(number)
LOGEST(data_y, data_x)
LOGNORM.DIST(x, mean, sd, cumulative)
LOGNORM.INV(probability, mean, sd)
LOGNORMDIST(x, mean, sd, cumulative)
LOGNORMINV(probability, mean, sd)
LOWER(text)
LT(num1, num2)
LTE(num1, num2)
MATCH(lookupValue, lookupArray, matchType)
MAX(number1, [number2], ...)
MAXA(number1, [number2], ...)
MEDIAN(number1, [number2], ...)
MID(text, start, number)
MIN(number1, [number2], ...)
MINA(number1, [number2], ...)
MINUS(num1, num2)
MINUTE(serial_number)
MIRR(values, finance_rate, reinvest_rate)
MOD(dividend, divisor)
MODE.MULT(number1, [number2], ...)
MODE.SNGL(number1, [number2], ...)
MODEMULT(number1, [number2], ...)
MODESNGL(number1, [number2], ...)
MONTH(serial_number)
MROUND(number, multiple)
MULTINOMIAL(number1, [number2], ...)
MULTIPLY(factor1, factor2)
NE(value1, value2)
NEGBINOM.DIST(k, r, p, cumulative)
NEGBINOMDIST(k, r, p, cumulative)
NETWORKDAYS(start_date, end_date, holidays)
NOMINAL(rate, periods)
NORM.DIST(x, mean, sd, cumulative)
NORM.INV(probability, mean, sd)
NORM.S.DIST(z, cumulative)
NORM.S.INV(probability)
NORMDIST(x, mean, sd, cumulative)
NORMINV(probability, mean, sd)
NORMSDIST(x, mean, sd, cumulative)
NORMSINV(probability)
NOT(logical)
NOW()
NPER(rate, payment, present, future, type)
NPV(arg1, [arg2], ...)
NUMBERS(arg1, [arg2], ...)
NUMERAL(number, format)
OCT2BIN(number, places)
OCT2DEC(number)
OCT2HEX(number, places)
ODD(number)
OR(logical1, [logical2], ...)
PDURATION(rate, present, future)
PEARSON(data_x, data_y)
PERMUT(number, number_chosen)
PERMUTATIONA(number, number_chosen)
PHI(x)
PI()
PMT(rate, periods, present, future, type)
POISSON.DIST(x, mean, cumulative)
POISSONDIST(x, mean, cumulative)
POW(base, exponent)
POWER(number, power)
PPMT(rate, period, periods, present, future, type)
PRODUCT(number1, [number2], ... )
PROPER(text)
PV(rate, periods, payment, future, type)
QUOTIENT(numerator, denominator)
RADIANS(number)
RAND()
RANDBETWEEN(bottom, top)
RATE(periods, payment, present, future, type, guess)
REFERENCE(context, reference)
REGEXEXTRACT(text, regular_expression)
REGEXMATCH(text, regular_expression, full)

REGEXREPLACE(text, regular_expression, replacement)
REPLACE(text, position, length, new_text)
REPT(text, number)
RIGHT(text, number)
ROMAN(number)
ROUND(number, digits)
ROUNDDOWN(number, digits)
ROUNDUP(number, digits)
RRI(periods, present, future)
RSQ(data_x, data_y)
SEARCH(find_text, within_text, position)
SEC(number)
SECH(number)
SECOND(serial_number)
SERIESSUM(x, n, m, coefficients)
SIGN(number)
SIN(number)
SINH(number)
SKEW(number1, [number2], ...)
SKEW.P(number1, [number2], ...)
SKEWP(number1, [number2], ...)
SLN(cost, salvage, life)
SLOPE(data_y, data_x)
SPLIT(text, separator)
SQRT(number)
SQRTPI(number)
STANDARDIZE(x, mean, sd)
STDEV.P(number1, [number2], ...)
STDEV.S(number1, [number2], ...)
STDEVA(number1, [number2], ...)
STDEVP(number1, [number2], ...)
STDEVPA(number1, [number2], ...)
STDEVS(number1, [number2], ...)
STEYX(data_y, data_x)
SUBSTITUTE(text, old_text, new_text, occurrence)
SUBTOTAL(function_code, ref1)
SUM(number1, [number2], ...)
SUMPRODUCT(array1, [array2], [array3], ...)
SUMSQ(number1, [number2], ...)
SUMX2MY2(array_x, array_y)
SUMX2PY2(array_x, array_y)
SUMXMY2(array_x, array_y)
SWITCH(expression, value1, result1, [value2, result2], [value3, result3], ..., [default] )
SYD(cost, salvage, life, period)
T(value)
T.DIST(x, df, cumulative)
T.DIST.2T(x, df)
T.DIST.RT(x, df)
T.INV(probability, df)
T.INV.2T(probability, df)
TAN(number)
TANH(number)
TBILLEQ(settlement, maturity, discount)
TBILLPRICE(settlement, maturity, discount)
TBILLYIELD(settlement, maturity, price)
TDIST(x, df, cumulative)
TDIST2T(x, df)
TDISTRT(x, df)
TEXT(value, format)
TIME(hour, minute, second)
TIMEVALUE(time_text)
TINV(probability, df)
TINV2T(probability, df)
TODAY()
TRANSPOSE(matrix)
TREND(data_y, data_x, new_data_x)
TRIM(text)
TRUE()
TRUNC(number, digits)
UNICHAR(number)
UNICODE(text)
UNIQUE(arg1, [arg2], ...)
UPPER(text)
VALUE(text)
VAR.P(number1, [number2], ...)
VAR.S(number1, [number2], ...)
VARA(number1, [number2], ...)
VARP(number1, [number2], ...)
VARPA(number1, [number2], ...)
VARS(number1, [number2], ...)
WEEKDAY(serial_number, return_type)
WEEKNUM(serial_number, return_type)
WEIBULL.DIST(x, alpha, beta, cumulative)
WEIBULLDIST(x, alpha, beta, cumulative)
WORKDAY(start_date, days, holidays)
XNPV(rate, values, dates)
XOR(logical1, [logical2], ...)
YEAR(serial_number)
YEARFRAC(start_date, end_date, basis)

Special Function

FORMDATA(formDefId, primaryKey, fieldName) in Spreadsheet "Formula" column.

where:

formDefId: The form id of the source form containing the setup data to 'pull' from.
primaryKey: The dependent pulldown field id in your spreadsheet design, that provides the WHERE condition to search the data to populate.
fieldName: The field id of the source form to populate in this spreadsheet column based on the above 'primaryKey' value.

Use this function to 'pull' and populate a spreadsheet cell based on available data in other forms.

For example, to make spreadsheet pull and display the population value after the user picks the city name (pulldown menu), use FORMDATA("city_formId",select_city,"population") where 'select_city' is the first field id in your spreadsheet.

Below is a sample app to demonstrate the use of Spreadsheet form element:

Example app using Spreadsheet in Form APP_spreadsheet app.jwa.

Space shortcuts

Page tree