Common Function Collection Plugin

1. Version

2. Application scenarios

Put some commonly used functions into the plug-in, which is convenient for users to use.

1. Plugin installation

Click to download the plugin: 

Common Function Collection.zip

Designer plugin installation method reference: Designer plugin management

Server installation plugin method refer to: Server plugin management

2. Operation method

Right-click on the cell, select Cell Element > Insert Formula, click the plug-in function in the pop-up formula definition interface, and double-click the function name with the left mouse button to insert the function, as shown in the following figure:

3. Introduction to functions

1. ScriptEval

Once the plugin is installed, JavaScript syntax can be used in the SciptEval function.

1) Parameter description

ScriptEval (parameter, parameter name 1, parameter value 1, parameter name 2, parameter value 2, ...): The first parameter needs to be a string, the string is a legal JavaScript expression, and the subsequent parameters represent JavaScript parameter name 1, parameter value 1, parameter name 2, parameter value 2, etc. Since the parameter name and parameter value always appear in pairs, the total number of parameters must be an odd number, and the set parameter name can be Used in JavaScript.

Note: The only requirements for JavaScript expressions are that they conform to JavaScript syntax and have a return value. For example: ScriptEval('return Math.max(p1, p2)', "p1", A1, "p2", B1) is equivalent to max(A1, B1).

2) Judgment in the formula

In the template, cell A4 expands down with the value [1,2,3,4,5] and cell B4 inserts the following condition:

When the value of cell A4 is greater than 4, the value of A4 * 2 is returned. When the value of cell A4 is less than 4 and greater than 1, the absolute value of A4 minus 2 is returned. In other cases, the value of cell A4 is returned twice. square.

The traditional way of writing a function is:

if(A4 > 4, A4 * 2, if(and(A4 < 4, A4 > 1), abs(A4 - 2), power(A4, 2)))

The ScriptEval function is written as:

ScriptEval('if(a > 4) {return a * b;} else if (a < 4 && a > 1){return Math.abs(a - b);} else {return Math.pow(a, b) }', "a", A4, "b", 2)

You can see that there is no difference at all in the results, but the expression using JavaScript syntax is more flexible.

3) Array filtering

Insert the following formula in cell B4 to filter out all even values in cell A4 and return it as an array. Cell B4 does not expand and the parent and child cells are None.

ScriptEval('var r = [];origin.forEach(function(item, i){if(item % 2 == 0){r.push(item);}});return r;',"origin", A4)

2. StringReverse

1) Description

StringReverse(parameter): reverse the string, the parameter is a string.

2) Example

StringReverse("abc") returns cba

3. StringFind

1) Description

Find the string index that a string contains.

2) Example

StringFind("ba", "a"): Returns the position 1 of the first occurrence of a in the string

StringFind("ba", "c"): Indicates that -1 is returned when the string cannot be found

Note: Positions are counted from 0. That is, StringFind("ba", "b") returns 0.

The function can be passed a third parameter, false means to search from the end.

4. StringMatch

1) Description

StringMatch(string, string): Determines whether the string matches the regular expression of the string, and returns true if the result matches, otherwise returns false.

2) Example

StringMatch("abcdefg", "bc"): returns true

5. StringFetch

1) Description

Extract the matching string according to the regular expression, and finally return an array. If there is a third parameter, it means to return the value of the index position represented by the third parameter in the array.

2) Example

StringFetch("2012-08-12 2012-12 abcde 2012-08-23", "\\d{4}[ /-]{1}\\d{2}([ /-]{1}\\d {2})?"): return 2012-08-12, 2012-12, 2012-08-23

StringFetch("2012-08-12 2012-12 abcde 2012-08-23", "\\d{4}[ /-]{1}\\d{2}([ /-]{1}\\d {2})?", 2): return 2012-08-23

For an introduction to regular expressions, please refer to the document: Regular Expression Definition Rules

6. StringPinyin

1) Description

StringPinyin(string, parameter 1, parameter 2): Output the pinyin of the given string.

Parameter 1 represents the spacer between pinyin.

Parameter 2 represents the output format of Pinyin, 1 represents no tone output, and -1 represents the digital representation of tone.

2) Example

StringPinyin("你好啊", " ", -1): return ni3 hao3 a1

7. StringShortPinyin

1) Description

Output the initial pinyin (lowercase) of the given string.

2) Example

StringShortPinyin("你好啊"): returns nha

8. StringSwapCase

1) Description

Swap the case of letters in a string.

2) Example

StringSwapCase("abcDeFg"): returns ABCdEfG

9. NumberToEnglish

1) Description

Translate numbers into English.

2) Example

NumberToEnglish("1234567"): Returns One Million, Two Hundred And Thirty-Four Thousand, Five Hundred And Sixty-Seven

10. GetIP

1) Description

Pass the built-in sessionID parameter to get the IP address.

2) Example

GetIP(): returns 0:0:0:0:0:0:0:1

11. ProcessErrorValue

1) Description

Handle formulas that return error values. When an error occurs in a general function, it will output something like #ERROR_NAME. This function can output a custom value when the function returns an error value, and other values remain unchanged.

2) Example

ProcessErrorValue(SQRT(), "wrong value"): returns the wrong value

12. JSONPathFinder

1) Description

When designing a report, not all the data are strings or arrays that can be directly recognized by the report, and sometimes the data is passed in JSON format. At this time, we need to use the JSONPathFinder function to obtain the data we want for different query conditions. The data.

2) Example

If you have data in JSON structure like this: see here. This is a JSON data describing a bookstore. Now we need to query the names of all books whose price is less than 10. Just insert the following formula:

JSONPathFinder("http://fine-doc.oss-cn-shanghai.aliyuncs.com/book.json","$.store.book[?(@.price<10)].title")

There are two books that qualify, so the formula returns an array: [Sayings of the Century,Moby Dick]

3) JSON Path Query Tutorial

For details, please refer to: Jayway JsonPath

13. MathFrequency

1) Description

Count the number and frequency of word occurrences.

2) Example

MathFrequency([1, 3, 2, 4, 2], 3): Returns 1, indicating that 3 appears only once in the array.

MathFrequency([1, 3, 2, 4, 2], 3,3): Returns 0.2, indicating that the frequency of 3 occurrences is 0.2, that is, if the third parameter is given, it will be expressed as frequency.

14. MathVariance

1) Description

Find the variance of the elements in an array.

2) Example

MathVariance(1,1,2,4,5) : returns 3.3

15. MathStandardDeviation

1) Description

Find the standard deviation of the elements in an array.

2) Example

MathStandardDeviation(1,2,3,4,5): returns 1.5811388300841898

16. MathSumOfSquares

1) Description

Find the sum of squares of the elements in an array.

2) Example

MathSumOfSquares(1,1,2,4,5): returns 47

17. MathGeometricMean

1) Description

Find the geometric mean of the elements in an array.

2) Example

MathGeometricMean(1,1,2,4,5): returns 2.0912791051825463

18. MathMean

1) Description

Finds the arithmetic mean of the elements in an array.

2) Example

MathMean(1,1,2,4,5): returns 2.6

19. EncryptShaHex

1) Description

SHA encryption.

2) Example

EncryptShaHex("abc"): The encrypted result is a9993e364706816aba3e25717850c26c9cd0d89d

EncryptShaHex(123): The encryption result is 40bd001563085fc35165329ea1ff5c5ecbdbbeef

Note: English double quotation marks must be included when encrypting fixed strings, and digital encryption can be added or not.

20. EncryptSha256Hex

1) Description

SHA256 encryption.

2) Example

EncryptSha256Hex("abc"): The encrypted result is ba7816bf8f01cfea414140de5dae2223b00361a396177a9cb410ff61f20015ad

EncryptSha256Hex(123): The encrypted result is a665a45920422f9d417e4867efdc4fb8a04a1f3fff1fa07e998e86f7f7a27ae3

Note: English double quotation marks must be included when encrypting fixed strings, and digital encryption can be added or not.

21. EncryptSha384Hex

1) Description

SHA384 encryption.

2) Example

EncryptSha384Hex("abc"): The encryption result is [B@3391f875

EncryptSha384Hex(123): The encryption result is [B@202bddc

Note: English double quotation marks must be included when encrypting fixed strings, and digital encryption can be added or not.

22. EncryptSha512Hex

1) Description

SHA512 encryption.

2) Example

EncryptSha512Hex("abc"): The encryption result is

ddaf35a193617abacc417349ae20413112e6fa4e89a97ea20a9eeee64b55d39a2192992a274fc1a836ba3c23a3feebbd454d4423643ce80e2a9ac94fa54ca49f

EncryptSha512Hex(123): The encryption result is

3c9909afec25354d551dae21590bb26e38d53f2173b8d3dc3eee4c047e7ab1c1eb8b85103e3be7ba613b31bb5c9c36214dc9f14a42fd7a2fdb84856bca5c44c2

Note: English double quotation marks must be included when encrypting fixed strings, and digital encryption can be added or not.

23. EncryptMd5Hex

1) Description

MD5 encryption.

2) Example

EncryptMd5Hex("abc"): The encrypted result is 900150983cd24fb0d6963f7d28e17f72

EncryptMd5Hex(123): Encryption result is 202cb962ac59075b964b07152d234b70

Note: English double quotation marks must be included when encrypting fixed strings, and digital encryption can be added or not.

24. EncryptDes

1) Description

DES encryption.

EncryptDes (parameter 1, parameter 2): The first parameter is the text to be encrypted, and the second parameter is the encrypted key.

2) Example

As shown in the figure below, EncryptDes(A1,B1): return LDiFUdf0iew=

Note: The key needs to be an 8-bit random variable string.

  Note: English double quotation marks must be included when encrypting fixed strings, and digital encryption can be added or not.

25. DecryptDes

1) Description

DES decryption.

DecryptDes (parameter 1, parameter 2): The first parameter is the text to be decrypted, and the second parameter is the key required for decryption.

2) Example

As shown in the figure below, DecryptDes(A1,B1): returns 123

26. HtmlFinder

1) Description

HTML page element lookup function.

HtmlFinder(parameter 1, parameter 2): The first parameter is the address of the HTML page to be found, and the second parameter is the DOM tag of the query.

2) Example

HtmlFinder("http://www.baidu.com", "a[href]"): You can find all hyperlinks on the Baidu homepage.    

For other grammar rules, please refer to: Use selector-syntax to find elements

27. Image

Converts the given parameter to an image, supports local files, remote files and relative paths.

28. StringConditionConcat

1) Description

Concatenates the eligible elements in the given array according to the condition, the built-in parameter index represents the index position, and item represents the element value.

2) Example

StringConditionConcat(["two", "b", "oriole", "asd", "singing green willow"], index % 2 = 1): return two orioles singing green willow