Cell functions

Read(478) Label: cell functions,

This section introduces cell functions used for report making by listing their descriptions, syntax, parameters, return values and options, and giving related examples.

avg()

Description:

Calculate average on an expandable cell or a set expression.

Syntax:

avg(exp)

avg(cellExp,exp)

Parameter:

exp

A cell or an expression on which average is computed, generally an expandable cell or a set expression

cellExp

A cell or a set of cells, which are used as the current cells to compute expression exp

Return value:

A real number

Option:

@a

Count null elements

Example:

Example 1: avg([A1:B10]) Calculate average of cells between A1 and B10, during which the current cell is used as the base cell to compute values of A1 and B10

Example 2: avg(arg1)  arg1 is an integer array, which means computing average of all elements in it

Example 3: avg(B1{})  Compute average of all cells expanded from B1 inclusive within the expansion area defined by the current master cell

Example 4: avg(B1[`0]{}) Compute average of all cells expanded from B1 inclusive under the root coordinates

Example 5: avg@a(list(2,4,null)) Return 2; return 3 when @a option is absent

Example 6: avg(A1[`0]{},B1+C1) Compute average of sum of B1 and C1 that correspond to each A1

case()

Description:

Return different values according to results of computing different expressions. Compute expressions from left to right in order, during which if an expression is eligible, the function returns its result and will not compute the rest of the expressions. If no expression can satisfy the condition and the default value expression is present, the function returns the default value; if no expression can satisfy the condition and the default value expression is absent, the function returns null.

Syntax:

case(Exp{,valueExp1:resultExp1{,valueExp2:resultExp2{,...{;defaultExp}}}})

Parameter:

Exp

The expression according to which the judgment is made

valueExp(n)

The value expression

resultExp(n)

The returned result expression

defaultExp

Default value expression; when results of all expressions are not eligible, return the result of computing this expression

Example:

Example 1: case(4,1:"Dept 1",2:"Dept 2",3:"Dept 3";"Admin Dept")

Result: " Admin Dept "

Example 2: case(4,1:"Dept 1",2:"Dept 2",3:"Dept 3")

Result: null

Example 3: case(3,1:"Dept 1",2:"Dept 2",3:"Dept 3";"Admin Dept")

Result:  "Dept 3"

cell()

Description:

Get a cell on the plane. Here the target cell is a computed one.

Syntax:

cell({cellExp}{,rowOffsetExp{,colOffsetExp}})

Parameter:

cellExp

The cell expression (the current cell when the parameter is omitted)

rowOffsetExp

The row offset expression relative to cellExp, which is an integer

colOffsetExp

The column offset expression relative to cellExp, which is an integer

Example:

Example 1: cell(,1,-5)  Get the cell that is offset from the current cell by five columns to the left in the next row

Example 2: cell(C1)  Based on the current cell, get the cell that is offset from the current cell by “the distance between the current cell’s source cell and C1”

Example 3: cell(D1,1,3)  Get the cell that is offset from D1 by three columns to the right in the next row

col()

Description:

Get ordinal number of the column holding the current cell.

Syntax:

col({cellExp})

Parameter:

cellExp

The cell expression (the current cell when the parameter is omitted)

Return value:

Integer type

Example:

Enter =col() in a cell in column B and the return value is 2. If there is a horizontally expanded cell in column B, the column number changes in order.

count()

Description:

Perform count on an expandable cell or set expression.

Syntax:

count(expression)

Parameter:

expression

A cell or expression to be counted; generally, it is an expandable cell or a set expression

Return value:

Integer type

Option:

@a

Count null elements

@d

Perform count on data from which duplicates are removed

Example:

Example 1: count([A1:B10])  Take the current cell as the base cell to count cells between A1 and B10, not counting cells whose values are empty

Example 2: count([A1[1]:B10[3]])  Count cells between the 1st cell expanded from A1 and the 3rd cell expanded from B1, not counting cells whose values are empty

Example 3: count(arg1) arg1 is a parameter array; count data in arg1

Example 4: count(B1{}) Count cells expanded from B1 inclusive within the expansion area defined by the current master cell

Example 5: count(B1[`0]{})  Count cells expanded from B1 inclusive under the root coordinates

Example 6: count@a(list(3,4,null))  Return 3; return 2 if @a option is absent

Example 7: count@d(list(3,4,3))  Return 2; return 3 if @d option is absent

disp()

Description:

Get display value in a cell.

Syntax:

disp(cell)

Parameter:

cell

A cell

Return value:

String type

Example:

Example 1: if(like(disp(A1), "中国*"),true,false)

Return true if the display value in A1 contains "中国"; otherwise return false

ds()

Description:

Get a data set object according to its name. The function is used to change the data set object in an expression. It is flexible but has low performance; make sure you think twice before using it.

Syntax:

ds( stringExp )

Parameter:

stringExp

The data set name

Return value:

Data set object

Note:

The return value is an element of the expression, and cannot be used as the cell value before being computed again

Example:

Example 1: ds("ds1").select(#0)  Get a set of row numbers in data set ds1

eval()

Description:

Dynamically parse and compute an expression.

Syntax:

eval( StringExp )

eval( StringExp, SubRptExp )

eval( StringExp, DataSetExp )

Parameter:

StringExp

A to-be-computed expression string

SubRptExp

An embedded subreport object, generally a cell containing a subreport

DataSetExp

A data set object, usually the ds() function

Return value:

Result value of the expression, whose data type is determined by the expression

Example:

Example 1: eval( "1+5" )

Return 6

Example 2: eval("B2+10", A1)

A1 is an embedded subreport, meaning that the expression computes B2+10 in A1’s subreport

Example 3: eval("salary+100", ds("ds1"))

Compute salary+100 on data set ds1

fread()

Description:

Read content of a file as a string or byte[].

Syntax:

fread(fileName[, charset])

Parameter:

fileName

A file name

charset

Encoding format

Return value:

A string or byte[]

Option:

@b

Read a file as byte[]

Example:

Example 1: fread@b("C:/R50.png")  Read content of the file as byte[] and return it

Example 2: fread("D:/report.xml")  Read content of the file as a string and return it

Example 3: fread("D:/c.txt","UTF-8")  Specify encoding format as "UTF-8"

graph()

Description:

Perform computation to generate a statistical graph.

Syntax:

graph({graphTypeExp})

Parameter:

graphTypeExp

Graph type expression, whose result is an integer type. Below are values corresponding to graph types:

1: Area graph

2: Bar graph

3: 3D bar graph

4: 3D clustered bar graph

5: Stacked bar graph

6: 3D stacked bar graph

7: Column graph

8: 3D column graph

9: 3D clustered column graph

10: Stacked column graph

11: 3D stacked column graph

12: Line graph

13: Pie graph

14: Scatter graph

15: 3D area graph

16: 3D line graph

17: 3D pie graph

18: Sequence graph

19: Time graph

20: Dual-axis line graph

21: Dual-axis column graph

22: Radar graph

23: Gantt graph

24: Dashboard

25: Milestone

Return value:

A statistical graph

Example:

graph(2)  Compute and generate a bar graph

if()

Description:

Return different values according to results of computing different Boolean expressions. Compute expressions from left to right in order, during which if an expression is eligible, the function returns its result and will not compute the rest of the expressions. If no Boolean expression returns true and the default value expression is present, the function returns the default value; if no boolean expression returns true and the default value expression is absent, the function returns null.

Syntax:

if(a,b,c)

if(x1:y1,…,xk:yk;y)

Parameter:

a

A boolean expression

b

A value expression; the function returns result of computing value expression b when Boolean expression a returns true

c

A value expression; the function returns result of computing value expression c when Boolean expression a returns false

xk

A boolean expression

yk

A value expression; the function returns result of computing value expression yi when corresponding expression xi returns true

y

Default value expression; the function returns result of computing this expression when all Boolean expressions xi return false

Return value:

Indefinite data type, which is determined by the result of computing a value expression; return null if all Boolean expressions return false and there isn’t the default value expression 

Example:

Example 1: if(value()>1,"true","false")

Return true if the current cell value is greater than 1; otherwise return false

Example 2: if(value()>90:"Excellent",value()>80:"Good",value()>60:"Passed";"Failed")

Return “Excellent” if the current cell value is greater than 90; return “Good” if it is greater than 80; return “Passed” if it is greater than 60; otherwise return “Failed”

Example 3: if(A4>90:"Excellent",A4>80:"Good",A4>60:"Passed")

Return “Excellent” when A4 is greater than 90; return “Good” when it is greater than 80; return “Passed” when it is greater than 60; otherwise return null

ifn()

Description:

Judge whether value of the first expression is null, and return the specified value if it is and return the expression value if it isn’t.

Syntax:

ifn( valueExp1, valueExp2 )

Parameter:

valueExp1

A to-be-computed expression, whose value will be returned when the value isn’t null

valueExp2

A to-be-computed expression, whose value will be returned when result of valueExp1 is null

Return value:

Result value of valueExp1 or valueExp2

Example:

Example 1: ifn(A1,"")  Return an empty string if A1 is null; otherwise return A1

Example 2: ifn(value(),0)  Return 0 if the current cell value is null; otherwise return the current cell value

lefthead()

Description:

Get left master cell of the specified cell.

Syntax:

lefthead({Cell{,level}})

Parameter:

Cell

The specified cell, which is the current cell when this parameter is absent

level

The level of left master cell; 0 represents the current master cell and add 1 for each one level up; default is 0

Return value:

Cell value

Example:

Example 1: lefthead()  Get left master cell of the current cell

Example 2: lefthead(B3,1)   Get left master cell of B3’s left master cell

list()

Description:

Get a set of enumerated data.

Syntax:

list(valueExp1{,valueExp2{,valueExp3{,……}}})

Parameter:

valueExp(n)

A constant/expression, or a set expression

Return value:

A set of enumerated data

Option:

@m

Generate a recursive union and expand a list in the function

Example:

Example 1: list(1,3,5,7,9)  {1,3,5,7,9}

Example 2: list("abc","def","ghj")  {"abc","def","ghj"}

Example 3: list@m(arg1,to(1,3),"a","b","c")  Suppose arg1 is an integer array whose value is 7,8,9, then the result value of this expression is {7,8,9,1,2,3,"a","b","c"}

map()

Description:

Display value reference table function. The function lets you find the display value corresponding to value of the current cell from the reference table, and returns null if no corresponding display value can be found.

Syntax:

map(valueListExp, displayListExp)

Parameter:

valueListExp

The list of actual values; can be an expandable cell or an expression whose result is a list

displayListExp

The list of display values; can be an expandable cell or an expression whose result is a list

There is a one-to-one correspondence between the list of actual values and the list of display values correspond in order

Return value:

String type display value corresponding to actual value in the current cell, or null when there isn’t a corresponding actual value

Example:

Example 1: map(to(1,3),list("M","F","Unknown"))

Write map() function in the display value expression; display "M" when value in the current cell is 1; "F" when the value is 2; "Unknown" when the value is 3; and return the cell value itself when value in the current cell is one of the other values

Example 2: map(arg1,arg2)

Write map() function in the display value expression; in the function, arg1 is a parameter array consisting of 1,2,3 and arg2 is a string array consisting of "M","F","Unknown"; same result as Example 1

Example 3: map(list(1,2,3),list("M","F"," Unknown "))

Write map() function in the display value expression and get same result as Example 1

max()

Description:

Get the maximum value on an expandable cell or a set expression.

Syntax:

max(exp)

max(exp,cellExp)

Parameter:

exp

A cell or expression for which the maximum value will be computed; generally an expandable cell or a set expression

cellExp

A cell or a set of cells; take the cell specified in cellExp as the current cell to compute expression exp

Return value:

Indefinite data type, which is determined by the result of computing exp

Example:

Example 1: max([A1:A10])  Get the maximum value among cells between A1 and A10

Example 2: max(arg1)  arg1 is the parameter array; get the maximum value in arg1

Example 3: max(B1{})  Get the maximum value among cells expanded from B1 inclusive within the expansion area defined by the current master cell

Example 4: max(B1[`0]{})  Get the maximum value among cells expanded from B1 inclusive under the root coordinates

Example 5: max(B1+C1,A1[`0]{}) Get the maximum sum of B1 and C1 that correspond to every A1

maxc()

Description:

Return a List of cells containing the maximum value among a cell (or a set of cells). Multiple cells containing the maximum value will all be returned if there are any.

Syntax:

maxc(cellExp1{,cellExp2{,......}})

Parameter:

cellExp1

A cell/set of cells or an expression of cell/set of cells

Return value:

A List of cells

Example:

Example 1: maxc(A1,A2,A3,A4)  Get a List of cells containing the maximum value between A1 and A4

Example 2: maxc(A1{},A2)  Get a list of cells containing the maximum value among cells expanded from A1 inclusive and A2

maxwidth()

Description:

Get the maximum length of characters among display strings in non-merge cells in the current column.

(Note: The length of characters whose ASCII code is greater than 255)

Syntax:

maxwidth()

Return value:

Integer type

Example:

Generally, the function is used in the header cell of a column to dynamically control the column width.

Example 1: Write 8*maxwidth() in the cell width property for the column cell header to dynamically adjust the current column width as “maximum length of characters for display cell value *8 pixels”

min()

Description:

Get the minimum value on an expandable cell or a set expression.

Syntax:

min(exp)

min(exp,cellExp)

Parameter:

exp

A cell or expression for which the maximum value will be computed; generally an expandable cell or a set expression

cellExp

A cell or a set of cells; take the cell specified in cellExp as the current cell to compute expression exp

Return value:

Indefinite data type, which is determined by the result of computing exp

Example:

Example 1: min([A1:A10])  Get the minimum value among cells between A1 and A10

Example 2: min(arg1)  arg1 is the parameter array; get the minimum value in arg1

Example 3: min(B1{})  Get the minimum value among cells expanded from B1 inclusive within the expansion area defined by the current master cell

Example 4: min(B1[`0]{})  Get the minimum value among cells expanded from B1 inclusive under the root coordinates

Example 5: min(B1+C1, A1[`0]{}) Get the minimum sum of B1 and C1 that correspond to every A1

minc()

Description:

Return a List of cells containing the minimum value among a cell (or a set of cells). Multiple cells containing the minimum value will all be returned if there are any.

Syntax:

minc(cellExp1{,cellExp2{,......}})

Parameter:

cellExp1

A cell/set of cells or an expression of cell/set of cells

Return value:

A List of cells

Example:

Example 1: minc(A1,A2,A3,A4)  Get a List of cells containing the minimum value between A1 and A4

Example 2: minc(A1{},A2)  Get a list of cells containing the minimum value among cells expanded from A1 inclusive and A2

plot()

Description:

Get position of a specified value in a completely divided set.

Syntax:

plot(valueExp, ListExp)

Parameter:

valueExp

A value expression; the value can be a string, a numeric value, a date, a time, etc.

ListExp

Return an array having same data type as valueExp; members of the array should be ordered in ascending order

Return value:

Integer type

Option:

@c

A value is eligible when it is equivalent to an element of ListExp

@z

Arrange elements of ListExp in descending order; default is ascending order

Example:

Example 1: plot( 0, list(0,10,100) )  Return 1; the first subset is <0 that does not contain 0, and the second subset is >=0 and <10 where 0 belongs; the subsets are numbered from 0 - 0,1,2,3 in order, so the result is 1

Example 2: plot@c(0, list(0,10,100))  Return 0

Example 3: plot(10, list(0,10,100))   Return 2

Example 4: plot@cz(10,list(100,10,0))  Return 1

Note:

Division refers to the case that a set is divided into multiple subsets. Take the array {0,10,100} as an example. If we divide the set of integers/real numbers into 4 subsets without taking account of the equality case, the subsets are <0, >10 (elements in this subset are sure to be >=0), <100 (elements in this subset are sure to be >=10) and others (elements are >=10) in order;

If we divide the set of integers/real numbers into 4 subsets, taking account of the equality case, the subsets are <=0, <=10 (and >0), <=100 (and >10) and others (elements are >100) in order;

For array {100,10,0}, we divide it into 4 subsets without taking account of the equality case. They are >100, >10 (and <=100), >0 (and <=10) and others (elements are <=0) in order;

If we divide it by taking account of the equality case, the 4 subsets are >=100, >=10 (and <100), >=0 (and <10) and others (elements are <0) in order.

query()

Description:

Execute the specified SQL statement and return the set of result data, which can only be a single column of data. When there are multiple fields in the SQL statement, return the result value of the first field.

Syntax:

query(sqlStatement{,arg1{,arg2{,arg3{,……}}}}{;dbName})

Parameter:

sqlStatement

A legal SQL statement

arg(n)

A parameter in the SQL statement, which is a constant or an expression

dbName

Logical database name; represent the default database when the parameter is null

Return value:

A set of data, whose data type is determined by the first selected field in the SQL statement

Example:

Example 1: query("select NAME from EMPLOYEE where GENDER =?","F";"demo")

Select records meeting condition GENDER ="F" from EMPLOYEE table in demo database and return their NAME field values

Example 2: query("select * from EMPLOYEE where GENDER =? and EID <?","F",11;"demo")

Select records meeting conditions GENDER ="F" and EID <11 from EMPLOYEE table in demo database and return their NAME field values and return values of their first field

row()

Description:

Get ordinal number of the row holding the current cell.

Syntax:

row({cellExp})

Parameter:

cellExp

The cell expression (the current cell when the parameter is omitted)

Return value:

Integer type

Example:

Enter =row() in a cell in row 3 and return 3; if there is a vertically expandable cell in row 3, the row number changes in order.

seq()

Description:

Get ordinal number of a specified expandable cell among cells expanded according to same master cell – that is, the ordinal number of the expandable cell when those cells are ordered by row/column number in ascending order after expansion.

Syntax:

seq({cellExp})

Parameter:

cellExp

A cell expression that should return an expandable cell

Return value:

Integer type

Example:

Suppose that A1 is an expandable cell and B1’s master cell is A1. We enter =seq(A1) in B1 and B1’s value change from 1 to the number of cells expanded from A1 inclusive in order.

sort()

Description:

Sort elements in an array.

Syntax:

sort(arrayExp)

Parameter:

arrayExp

An array expression, such as group function, select function and list function

Return value:

An array, where data types of elements are determined by parameter arrayExp

Option:

@z

Sort in descending order

Example:

Example 1: sort([5,2,3,4])  Return {2,3,4,5}

Example 2: sort@z([5,2,3,4])  Return {5,4,3,2}

Example 3: sort(list(5,2,3,4))  Return {2,3,4,5}

Example 4: sort(ds1.select(EID))  Sort return value by EID in ascending order

Example 5: sort(ds1.group(DEPT)) Sort return value by grouped DEPT in ascending order

spl()

Description:

Dynamically parse and compute an esProc expression. This is equivalent to executing eval() function in esProc.

Syntax:

spl(StringExp ,{argExp})

Note:

Take an expression string as the result of StringExp, and parse and calculate it. The question mark (?) corresponds to result of computing argExp. If there are multiple question marks, probably there are multiple argExps and they correspond to each other one by one.

If the number of question marks is greater than that of argExps, identify their correspondence from the first argExp.

Usually, we can use the ordinal number to specify parameter for a question mark, such as spl("?2/?1", 3, 6 ), where the first question mark corresponds to the second parameter and the second question mark corresponds to the first parameter. In this example, the function returns 2.

Parameter:

StringExp

A to-be-computed string expression

argExp

A parameter expression

Keyword:

?

Represent value of argExp in a StringExp

Return value:

Expression result value, whose data type is determined by the expression

Example:

 

A

 

1

="1+3"

 

2

=spl(A1)

4

3

=4

 

4

=spl("?+5",A3)

? is a keyword representing parameter A3’s value and the function returns 9

5

=spl("replace(?,\"X\",\"*\")","ZXcm")

Use replace() function to replace value of the first parameter and return “Z*cm”

6

=spl("(?+1)/?",3,4)

Value of the first ? is 3 and that of the second ? is 4, and the function returns 1.0

7

=spl( "(?+?)*?",1, 3 )

Value of the first ? is 1, that of the second ? is 3, and that of the third ? is 1, and the function returns 4

8

=spl("?+?", 3 )

Return 6 because the number of argExps is less that that of the question marks and the parameter is repeatedly used

9

=spl("?2/?1", 3, 6 )

The first ? corresponds to the second parameter and the second ? corresponds to the first parameter, and the function returns 2.0

sublist()

Description:

Get a subset of a specified set.

Syntax:

sublist(srcList,startPos,count)

Parameter:

srcList

Source set expression

startPos

Start position; count from 0

count

The number of elements to be returned

Return value:

A subset of a set

Example:

Example 1: sublist( list(1,3,5,7,9), 1, 2 )  Return { 3, 5 }

sum()

Description:

Perform sum on an expandable cell or a set expression.

Syntax:

sum(exp)

sum(exp,cellExp)

Parameter:

exp

A to-be-summed cell or expression, generally an expandable cell or a set expression

cellExp

A cell or a set of cells; take the cell specified in cellExp as the current cell to compute expression exp

Return value:

A real number or string type data, which is determined by data type of the to-be-summed cell

Example:

Example 1: sum([A1:A10])

Compute sum of values between A1 and A10; the to-be-summed cells are numeric type or string type

Example 2: sum(arg1)

arg1 is a parameter array; compute sum of all elements in arg1

Example 3: sum(B1{}) 

Compute sum of cells expanded from B1 inclusive within the expansion area defined by the current master cell

Example 4: sum(B1[`0]{})

Compute sum of cells expanded from B1 inclusive under the root coordinates

Example 5: sum(B1+C1,A1[`0]{})

Compute sum of B1 and C1 corresponding to every A1

throwe()

Description:

Throw a ReportError exception.

Syntax:

throwe({msg})

Parameter:

msg

Exception information string

Example:

throwe("Cell value error")  Throw an exception, which reads “Cell value error”

to()

Description:

Generate a set of continuous integers.

Syntax:

to(startExp,endExp{,stepExp})

Parameter:

startExp

Expression of start data of the to-be-generated integers

endExp

Expression of end data of the to-be-generated integers

stepExp

Expression of step between integers; default is 1

Return value:

An array of continuous integers

Example:

Example 1: to(1,5)  Equivalent to list(1,2,3,4,5); start from 1 and add 1 each time until 5

Example 2: to(1,5,2)  Equivalent to list(1,3,5); start from 1 and add 2 each time until 5

Example 3: to(-5,-10,-2)  Equivalent to list(-5,-7,-9); start from -5 and subtract 2 each time until -9

Example 4: to(-10,-8)  Equivalent to list(-10,-9,-8); start from -10 and add 1 each time until -8

tophead()

Description:

Get top master cell of the specified cell.

Syntax:

tophead({Cell{,level}})

Parameter:

Cell

The specified cell, which is the current cell when the parameter is absent

level

The level of top master cell; 0 represents the current master cell and add 1 for each one level up; default is 0

Return value:

Cell value

Example:

Example 1: tophead()  Get top master cell of the current cell

Example 2: tophead(E3,1)  Get top master cell of E3’s top master cell

value()

Description:

Get the current cell’s value.

Syntax:

value()

Return value:

The current cell’s value

valueat()

Description:

Return an element of an array or a set expression according to the specified position.

Syntax:

valueat(arrayexpsuffixExp)

Parameter:

arrayexp

An array or a set expression

suffixExp

An integer for specifying ordinal number of the to-be-returned element in the set; count from 0

Return value:

An element of a set, whose data type is determined by type of elements in the set

Example:

Example 1: valueat(to(5,8),2)  Return 7

Example 2: valueat(["a","b","c"],2)  Return "c"

fexists()

Description:

Check whether a file exists or not, and return true if it does; otherwise return false.

Syntax:

fexists(fileName)

Parameter:

fileName

Name of the to-be-checked file

Return value:

Boolean type

Option:

@m

Search the main directory if the file, when it is an absolute path, cannot be found; directly search the main directory for the file when a relative path is used

@c

Search the class directory if the file, when it is an absolute path, cannot be found; directly search the class directory for the file when a relative path is used

@a

Search the application root directory if the file, when it is an absolute path, cannot be found; directly search the application root directory for the file when a relative path is used

 

Default file path is the absolute path

Example:

Example 1: fexists("D:/1.jpg") 

Example 2: fexists@m("D:/1.jpg")   false Search D:\ drive for 1.jpg and return true if it is found; otherwise search the main directory, and return true if it is found or return false if it isn’t found

Example 3: fexists@c("1.jpg")  Search class path for 1.jpg, and return true if it is found or return false if it isn’t found

Example 4: fexists@a("1.jpg")  Search application root directory for 1.jpg, and return true if it is found or return false if it isn’t found