Java Reporting Tools Finereport usage Summary of common functions (text and date functions)

Text functions

CHAR

CHAR: Returns the corresponding character according to the specified number. The Char function converts the numeric code of other types of computers to characters.

Number: The digit that specifies the character, in 1Number: The number used to specify a character, between 165535 (including 1 and 65535).

Example:

CHAR (88) equals "X".

CHAR (45) equals "-".

CODE

Code (TEXT): Computes the numeric code of the first character in a text string. The returned code corresponds to the character set used by the computer.

Text: a literal or cell reference that needs to be evaluated for the first character code.

Example:

CODE ("S") equals 83.

CODE ("Spreadsheet") equals 83.

Concatenate

Concatenate (Text1,text2,...): combines several strings into a single string.

TEXT1,TEXT2,...: A text item that needs to be merged into a single text, which can be a character, a number, or a cell reference.

Note:

You can also use "&" instead of the CONCATENATE function to merge text items.

Example:

Concatenate ("Average", "Price") equals "Average price", which is equivalent to "Average" & "" & "Price".

Concatenate ("1", "2") equals 12.

Endwith

Endwith (STR1,STR2): Determines whether the string str1 ends with str2. Note: Both STR1 and str2 are case-sensitive.

Example:

Endwith ("FineReport", "Report") equals True

Ennumber. Endwith ("FineReport", "Fine") equals false. Endwith ("FineReport", "Report" Ennumber

Ennumber (value): Converts a number of a given BigDecimal type into a string of English amount.

Enmoney

Enmoney (value): Converts the number of a given bigdemical type into an English amount string.

Example:

Enmoney (23.49) equals twenty.

Ennumer

Ennumber (value): Converts a number of a given BigDecimal type into a string of English amount.

EXACT

EXACT (TEXT1,TEXT2): detects whether two sets of text are the same. If it is identical, the exact function returns true; otherwise, it returns false. The exact function can be case-sensitive, but ignores formatting differently. You can also use the exact function to detect text in the input document.

TEXT1: The first set of text that needs to be compared.

TEXT2: The second set of text that needs to be compared.

Example:

EXACT ("Spreadsheet", "Spreadsheet") equals True.

EXACT ("Spreadsheet", "S Preadsheet") equals false.

EXACT ("Spreadsheet", "Spreadsheet") equals false.

FIND

Find (Find_text,within_text,start_num): Finds another text string in a text string, and returns to the starting position number of the find_text from the first character in Within_text. You can also use the Seaerch function to find another text string in a text string, except that the find function can be case-sensitive, but wildcard characters are not allowed.

Find_text: The text you want to find or a cell reference that contains text.

Within_text: Contains text or cell references that need to find text.

Start_num: Specifies the starting position of the find character. In within_text, the starting position character number is 1. If start_num is omitted, the value is assumed to be 1.

Note:

If find_text is not in within_text, the Find function returns an error message *value!.

If the start_num is not much more than the 0,find function returns an error message *value!.

If start_num is greater than within_text length, the Find function returns an error message *value!.

If find_text is a blank text, the Find function will match the first character in the search string (that is, a character numbered start_num or 1).

Example:

FIND ("I", "Information") equals 1.

FIND ("I", "Information") equals 9.

FIND ("O", "Information", 2) equals 4.

FIND ("O", "Information", 12) equals *value!.

INDEXOF

INDEXOF (Str1,index): Returns the character of the string str1 at the index position.

Note:

Index is counted starting at 0.

Example:

INDEXOF ("FineReport", 0) equals ' F '.

INDEXOF ("FineReport", 2) equals ' n '.

INDEXOF ("FineReport", 9) equals ' t '.

Left

Left (Text,num_chars): Returns the first or previous characters in a text string based on the specified number of characters.

Text: Contains a string or cell reference that requires the selection of characters.

Num_chars: Specifies the length of the string returned.

Note:

The value of the num_chars must be equal to or greater than 0.

If Num_chars is greater than the length of the entire text, the left function returns all the text.

If Num_chars is omitted, the default value is 1.

Example:

Left ("Fine software", 8) equals "Fine sof".

Left ("Fine software") equals "F".

If cell A3 contains "China", then left (a3,2) equals "Ch".

Len

LEN (text): Returns the number of characters in a text string.

Text: a literal whose length is required, and the space is counted as a character.

Example:

LEN ("Evermore Software") equals 17.

LEN ("") equals 1.

LOWER

LOWER (text): Converts all uppercase letters to lowercase letters.

Text: A string that needs to be converted to lowercase letters. The lower function does not convert non-alphabetic characters in the text string.

Example:

LOWER ("a.m.10:30") equals "a.m.10:30".

LOWER ("China") equals "China".

MID

MID (Text,start_num,num_chars): Returns a certain number of characters from a text string that begin at a specified position, as specified by the user.

Text: Contains a string of characters to extract.

Start_num: The starting position in the text where the characters need to be extracted. The first character in the text has a start_num of 1, and so on.

Num_chars: Returns the length of the character.

Note:

If the start_num is greater than the text length, the Mid function returns "" (empty).

If the start_num is less than the text length, and start_num plus num_chars is greater than the text length, the mid function will start from the start_num specified starting character until all characters are at the end of the text.

If the start_num is less than the 1,mid function, the error message *value! is returned.

If Num_chars is negative, the Mid function returns an error message *value!.

Example:

MID ("Finemore software", 10,8) returns "Software".

MID ("Finemore software", 30,5) returns "" (empty text).

MID ("Finemore software", 0,8) returns *value!.

MID ("Finemore software", 5,-1) returns *value!.

Numto

Numto (number): Returns the Chinese representation of number.

Example: Numto (2345) equals 2345.

PROPER

PROPER (text): Converts the first letter in the text and all non-alphabetic characters to uppercase, and the other letters to lowercase.

Text: A formula that needs to be converted to text, a text string referenced by double quotation marks, or a cell reference.

Example:

PROPER ("Finemore Integrated Office") equals "Finemore Integrated Office".

PROPER ("percent") equals "percent".

PROPER ("SpreaDSheEt") equals "SpreaDSheEt".

Regexp

REGEXP (str, pattern): whether the string str matches the regular expression pattern.

Example:

REGEXP ("Aaaaac", "A*c") equals True.

REGEXP ("abc", "A*c") equals false.

REGEXP (str, pattern, intnumber): Whether the string str matches the regular expression pattern intnumber with the given pattern.

Note:

case_insensitive = 0 Enables case-insensitive matching.

Flags are combined with this flag to enable Unicode-aware, case-insensitive matching. By default, case-insensitive matching assumes that only characters in the US-ASCII character set are matched. You can specify unicode_case by specifying the

MULTILINE = 1 enables multiline mode.

Dotall = 2 enables Dotall mode.

In Dotall mode, an expression can match any character, including a line terminator. By default, this expression does not match the line terminator.

Unicode_case = 3 Enable Unicode-aware case folding

When this flag is specified, when enabled by the CASE_INSENSITIVE flag, case-insensitive matching is done in a Unicode-compliant manner.

CANON_EQ = 4 Enables specification equivalence.

When this flag is specified, two characters are considered a match when and only if their full canonical decomposition matches.

Unix_lines = 5 enables UNIX line mode.

In this mode, only the ' \ n ' line terminator is recognized in the behavior of., ^, and $.

LITERAL = 6 The literal parsing of the enabled mode.

When this flag is specified, the input string for the specified pattern is treated as a sequence of literal characters. The metacharacters or escape sequences in the input sequence do not have any special meaning, and the flags case_insensitive and unicode_case when used with this flag will have a shadow on the match, and the other flags become superfluous.

Whitespace and annotations are allowed in COMMENTS = 7 mode.

This mode ignores whitespace and #开头的嵌入式注释 before the end line.

Example:

REGEXP ("Aaaaabbbbc", "A*b*c", 3) equals True

REGEXP ("Aaaaabbbbc", "A*b*c", 1) equals false.

REPLACE

Replace (Old_text,start_num,num_chars,new_text): Replaces portions of a text string with other text strings based on the specified number of characters.

Old_text: Text or cell references that need to be replaced with partial characters.

Start_num: You need to replace the starting position of the characters in the old_text with New_text.

Num_chars: You need to use New_text to replace the number of characters in the old_text.

New_text: You need to replace the text of some old text.

Example:

REPLACE ("0123456789", 5, 4, "*") equals "0123*89".

REPLACE ("1980", 3, 2, "99") equals "1999".

REPT

REPT (text,number_times): Displays the text repeatedly according to the specified number of times. The REPT function can be used to display the same string and to populate the cells. If Number_times is the 0,rept function will return "" (empty text). If Number_times is not an integer, it will be rounded. The final result of the REPT function is usually no greater than 32,767 characters.

Text: A literal that needs to be displayed repeatedly or a cell reference that contains text.

Number_times: Specifies the number of times the text repeats and is positive.

Note:

This function can be used to create a simple histogram in a worksheet.

Example:

REPT ("$", 4) equals "$ $".

If cell B10 's content is "Hello", REPT (b10,3.6) equals "Hello Hello hello".

Right

Right (Text,num_chars): Returns the last or several characters in a text string based on the specified number of characters.

Text: A string or cell reference that contains the characters you want to extract.

Num_chars: Specifies the number of characters that the right function extracts from the text string. Num_chars cannot be less than 0.

If Num_chars is greater than the length of the text string, the right function returns the entire text. If you do not specify Num_chars, the default value is 1.

Example:

Right ("It is interesting", 6) equals "esting".

Right ("Share Holder") equals "R".

Right ("Huge Sale", 4) equals "Sale".

SPLIT

Split (STRING1,STRING2): Returns an array of strings made up of String2 split String1.

String1: A string in double quotation marks.

String2: A delimiter in double quotation marks. For example, comma ","

Note:

If there is only one argument, an error is returned.

If there are multiple parameters, only the first two work.

Example:

SPLIT ("Hello,world,yes", ",") = ["Hello", "World", "yes"].

SPLIT ("This is very good", "") = [' This ', ' is ', ' very ', ' good '].

Startwith

Startwith (STR1,STR2): Determines whether the string str1 starts with str2.

Note:

Both STR1 and str2 are case-sensitive.

Example:

Startwith ("FineReport", "Fine") equals True.

Startwith ("FineReport", "Report") equals false.

Startwith ("FineReport", "Fine") equals false.

Substitute

Substitute (Text,old_text,new_text,instance_num): Replaces Old_text in a text string with new_text.

Text: A literal that needs to be replaced by a character, or a cell reference that contains text.

Old_text: Part of the text that needs to be replaced.

New_text: The text used to replace the old_text.

Instance_num: Specifies to replace the first occurrence of old_text with new_text. If Instance_num is specified, only old_text at the specified position is replaced, otherwise all old_text that appear in the text string are replaced by New_text.

Note:

Use the substitute function if you need to replace the specified text in a text string, or replace any text at a specified position in a text string.

Example:

Substitute ("Data base", "base", "model") equals "Data Model".

Substitute ("July 28, 2000", "2", "1", 1) equals "July 18, 2000".

Substitute ("July 28, 2000", "2", "1") equals "July 18, 1000".

Substitute ("July 28, 2000", "2", "1", 2) equals "July 28, 1000".

ToDouble

ToDouble (text): Converts text to a double object.

Text: A literal that needs to be converted.

Example:

ToDouble ("123.21") equals new Double (123.21).

Tointeger

Tointeger (text): Converts text to an integer object.

Text: A literal that needs to be converted.

Example:

The Tointeger ("123") equals the new Integer (123).

TRIM

TRIM (text): Clears all spaces in the text, except for a single space between words, or for text with irregular spaces.

Text: A space that needs to be cleared.

Example:

TRIM ("Monthly report") equals Monthly report.

Toimage

Toimage (PATH): Returns the image under path.

UPPER

UPPER (text): Converts all characters in the text to uppercase.

Text: A literal that needs to be converted to uppercase characters, or a cell reference that contains text.

Example:

UPPER ("notes") equals "notes".

If cell E5 has a value of "Examples", then Upper (E5) equals "Examples".

Date and Time functions

Where a date is used as a parameter factor in a function, the form of the date must be YY/MM/DD. and must be quoted in the English environment under double quotation marks ("").

DATE

Date (Year,month,day): Returns a series number that represents a specific date.

Year: Represents an annual number, which can be one to four digits.

Month: Represents the months.

If 1<=month<= 12, the function takes the parameter value as the month.

If month>12, the function is incremented from the beginning of the year January. For example: DATE (2000,25,2) equals the number of series on January 2, 2002.

Day: Representative days.

If the date is less than or equal to the number of days of a specified month, the function takes this parameter value as a day.

If the date is greater than the number of days of a specified month, the function is incremented from the first day of the specified month. If the date is greater than the total number of days of two or more months, the function adds the remainder of the minus two months or months to the third or fourth month, and so on. For example: DATE (2000,3,35) equals the number of series on April 4, 2000.

Note:

This formula is available if you need to work with part of a date in a formula, such as year or month.

If the year, month, and day are constants in the function rather than in the function, this formula will best reflect its effect.

Example:

DATE (1978, 9, 19) equals September 19, 1978.

DATE (1211, 12, 1) equals December 1, 1211.

Datedelta

Datedelta (date, deltadays): Returns the date deltadays after a date--date.

Deltadays can be positive, negative, 0.

Example:

Datedelta ("2008-08-08",-10) equals 2008-07-29.

Datedelta ("2008-08-08", 10) equals 2008-08-18.

Datedif

Datedif (Start_date,end_date,unit): Returns the number of days, months, or years between two specified dates.

Start_date: Represents the initial date for the specified time period.

End_date: Represents the end date of the specified time period.

Unit: The type of information returned by the function.

If unit= "Y", the Datedif returns the year difference for the specified time period.

If unit= "M", the Datedif returns the month margin for the specified time period.

If unit= "D", then Datedif returns the day difference for the specified time period.

If unit= "MD", Datedif ignores the year and month, returning the day difference for the specified time period.

If unit= "YM", then Datedif ignores the year and day, returning the month margin for the specified time period.

If unit= "YD", the Datedif ignores the year and returns the day difference for the specified time period.

Example:

Datedif ("2001/2/28", "2004/3/20", "Y") equals 3, that is, between February 28, 2001 and March 20, 2004 there are 3 full year.

Datedif ("2001/2/28", "2004/3/20", "M") equals 36, i.e. there are 36 entire month between February 28, 2001 and March 20, 2004.

Datedif ("2001/2/28", "2004/3/20", "D") equals 1116, that is, between February 28, 2001 and March 20, 2004 there are 1116 days.

Datedif ("2001/2/28", "2004/3/20", "MD") equals 8, that is, ignoring months and years, the difference between February 28, 2001 and March 20, 2004 is 8 days.

Datedif ("2001/1/28", "2004/3/20", "YM") equals 2, that is, the difference between January 28, 2001 and March 20, 2004 is 2 months after ignoring the day and year.

Datedif ("2001/2/28", "2004/3/20", "YD") equals 21, that is, after years of neglect, the difference between February 28, 2001 and March 20, 2004 is 21 days.

Dateinmonth

Dateinmonth (date, number): The function returns the day of the day of the first one months.

Example:

Dateinmonth ("2008-08-08", 20) equals 2008-08-20.

Dateinquarter

Dateinquarter (date, number): The function returns the day of the first day of a particular quarter.

Example:

Dateinquarter ("2009-05-05", 20) equals 2009-04-20.

Dateinweek

Dateinweek (date, number): The function returns the day of the day of the first one weeks.

Example:

Dateinweek ("2008-08-28", 2) equals 2008-08-26.

Dateinyear

Dateinyear (date, number): The function returns the day of the day of the year.

Example:

Dateinyear ("2008/12/03", 300) equals 2008-10-26.

Datesubdate

Datesubdate (Date1, Date2, op): Returns the time difference between two dates.

The OP represents the unit of time returned:

"S", in seconds.

"M", measured in minutes.

"H", in hours.

"D", in days.

"W", in weeks.

Example:

Datesubdate ("2008-08-08", "2008-06-06", "H") equals 1512.

Datetonumber

Datetonumber (date): Returns the number of milliseconds elapsed since January 1, 1970 00:00:00 GMT.

Example:

Datetonumber ("2008-08-08") equals 1218124800000.

Day

Day: (Serial_number) returns the days in the date. Day is a number between 1 and 31.

Serial_number: Date containing the year of the request.

Note:

Finereport saves the date as a series number, and a series number represents a date that matches it to make it easier for the user to calculate the date numerically.

In the 1900 date system, the Finereport spreadsheet will be saved as series number 2 in January 1, 1900, saving January 2, 1900 as series number 3,

Save January 3, 1900 as series number 4 ... And so on As in the 1900 date system, January 1, 1998 is saved as series number 35796.

Example:

Day ("2000/1/1") equals 1.

Day ("2006/05/05") equals 5.

Day ("1997/04/20") equals 20.

Day (35796) equals 1.

DAYS360

DAYS360 (Start_date,end_date,method): According to the algorithm 360 days a year (30 days per month, a total of 12 months a year),

Returns the number of days between two days, which will be used in accounting calculations. If the financial system is based on 12 months a year, 30 days a month,

You can use this function to help calculate payments.

Start_date and end_date: is the start and end date for calculating the number of days in a period.

Method: It specifies whether the European or American approach is used in the calculation.

Method definition:

False or ignore the US method (NASD). If the starting date is one months of 31st, it is equal to the number 30th of the same month. If the termination date is one months

Number 31st, and the starting date is earlier than 30th, the end date is equal to 1th for the next one months, otherwise the end date is equal to the 30th number of this month.

True European method. Either the start date or the ending date of one months is 31st, which will be equal to the number 30th this month.

Note:

Finereport saves the date as a series number, and a series number represents a date that matches it to make it easier for the user to calculate the date numerically.

In the 1900 date system, the Finereport spreadsheet will be saved as series number 2 in January 1, 1900, saving January 2, 1900 as series number 3,

Save January 3, 1900 as series number 4 ... And so on As in the 1900 date system, January 1, 1998 is saved as series number 35796.

Example:

DAYS360 ("1998/1/30", "1998/2/1") equals 1.

Daysofmonth

Daysofmonth (date): Returns the number of days that are included in a month since January 1900. Example:

Daysofmonth ("1900-02-01") equals 28.

Daysofmonth ("2008/04/04") equals 30.

Daysofquarter

Daysofquarter (date): Returns the number of days in a quarter from January 1900.

Example:

Daysofquarter ("2009-02-01") equals 90.

Daysofquarter ("2009/05/05") equals 91.

Daysofyear

Daysofyear (year): Returns the number of days that will be included in years after 1900.

Example:

Daysofyear (2008) equals 365, equivalent to Daysofyear ("2008-01-01").

DATEVALUE

DATEVALUE (Date_text): Returns a series number that represents Date_text. This function can be used to convert a date in a text form into a series number.

Date_text: is the text format that represents the date in the spreadsheet date format. For example "2000/2/28"

Note:

Finereport saves the date as a series number, and a series number represents a date that matches it to make it easier for the user to calculate the date numerically. In 1900

In the date system, the Permanent office spreadsheet will save January 1, 1900 as series number 2, Save January 2, 1900 as series number 3, save January 3, 1900

For series number 4 ... And so on As in the 1900 date system, January 1, 1998 is saved as series number 35796.

Example:

DATEVALUE ("2000/1/1") equals 36526.

HOUR

HOUR (Serial_number): Returns the number of hours for a specified time. The hour function specifies an integer that is between 0 (0:00) and 23 (23:00).

Serial_number: Contains the time of the hour being asked.

Example:

HOUR ("11:32:40") equals 11.

MINUTE

MINUTE (Serial_number): Returns the number of minutes of a specified time, which is an integer between 0 and 59.

Serial_number: Contains the time for the number of minutes to be asked.

Example:

MINUTE ("15:36:25") equals 36.

MONTH

Month: (serial_number) Returns the months of the date. The month is a number between 1 and 12.

Serial_number: Contains the date of the month of the request.

Note:

Finereport saves the date as a series number, and a series number represents a date that matches it to make it easier for the user to calculate the date numerically.

In the 1900 date system, the Finereport spreadsheet will be saved as series number 2 in January 1, 1900, saving January 2, 1900 as series number 3,

Save January 3, 1900 as series number 4 ... And so on As in the 1900 date system, January 1, 1998 is saved as series number 35796.

Example:

MONTH ("2004/5/5") equals 5.

MONTH (35796) equals 1.

Monthdelta

Monthdelta (Date,delta): Returns the date of the delta months after a specified date.

Example:

Monthdelta ("2008-08-08", 4) equals 2008-12-08.

Now

Now (): Gets the current time.

Example:

If the system time is 15 points, 18 minutes, 38 seconds,

Then now () equals 15:18:36.

SECOND

SECOND (S Erial_number): Returns the number of seconds in a specified time, which is an integer between 0 and 59.

Serial_number: Contains the time of the number of seconds to be asked.

Example:

SECOND ("15:36:25") equals 25.

SECOND ("15:36:25", "HH:mm:ss") equals 25.

Time

Time (Hour,minute,second): Returns a decimal that represents a specified number of times. The time between 0:00:00 (12:00:00 a.m.) and 23:59:59 (11:59:59 p.m.) returns the corresponding value from 0 to 0.99999999.

Hour: A number between 0 and 23.

Minute: A number between 0 and 59.

Second: A number between 0 and 59.

Example:

Time (14,40,0) equals 2:40 PM.

Time (19,43,24) equals 7:43 PM.

TODATE

The TODATE () function converts parameters of various date forms to date types.

It has three types of parameters:

The 1 parameter is a date-type parameter, so the parameter is returned directly.

Example:

TODATE (Date (2007,12,12)) returns the date composed of December 12, 2007.

The 2 parameter returns the corresponding time in milliseconds starting from January 1, 1970 0:0 0 seconds.

Example:

TODATE (1023542354746) returns June 8, 2002.

The 3 parameter is the date format text, then returns the date that the text corresponds to.

Example:

TODATE ("2007/10/15") returns the date formed on October 5, 2007.

TODATE ("2007-6-8") returns the date formed on June 8, 2007.

4 has two parameters, the first parameter is a date-formatted text, and the second argument is the format used to parse the date.

Example:

TODATE ("1/15/07", "Mm/dd/yy") returns the date formed on January 15, 07.

Note: The month in this format must be uppercase mm, year lowercase: yy (not yyyy). Day lowercase: dd.

TODAY

Today (): Gets the current date.

Example:

If the system date is September 10, 2005

Then today () equals 2005/9/10.

WEEKDAY

WEEKDAY (Serial_number): Gets the date and returns the day of the week. The return value is an integer between 0 and 6 that represents one day of the week (from Sunday to Saturday).

Serial_number: the date entered

Note:

Finereport saves the date as a series number, and a series number represents a date that matches it to make it easier for the user to calculate the date numerically.

In the 1900 date system, the Finereport spreadsheet will be saved as series number 2 in January 1, 1900, saving January 2, 1900 as series number 3,

Save January 3, 1900 as series number 4 ... And so on As in the 1900 date system, January 1, 1998 is saved as series number 35796.

Example:

WEEKDAY ("2005/9/10") equals 6 (Saturday).

WEEKDAY ("2005/9/11") equals 0 (Sunday).

WEEKDAY (35796) equals 4 (Thursday).

WEEKNUM

WEEKNUM (Serial_num): Returns a number that represents the week ordinal of a year.

Serial_num: Represents the date entered.

Note:

Finereport saves the date as a series number, and a series number represents a date that matches it to make it easier for the user to calculate the date numerically.

In the 1900 date system, the Finereport spreadsheet will be saved as series number 2 in January 1, 1900, saving January 2, 1900 as series number 3,

Save January 3, 1900 as series number 4 ... And so on As in the 1900 date system, January 1, 1998 is saved as series number 35796.

Example:

WEEKNUM ("2005/1/1") equals 1.

WEEKNUM ("2005/1/6") equals 2.

WEEKNUM (35796) equals 1.

Year

Year: (Serial_number) returns years in the date. Year is a number between 1900 and 9999.

Serial_number: Date containing the year of the request.

Note:

Finereport saves the date as a series number, and a series number represents a date that matches it to make it easier for the user to calculate the date numerically.

In the 1900 date system, the Finereport spreadsheet will be saved as series number 2 in January 1, 1900, saving January 2, 1900 as series number 3,

Save January 3, 1900 as series number 4 ... And so on As in the 1900 date system, January 1, 1998 is saved as series number 35796.

Example:

Year ("2000/1/1") equals 2000.

Year ("2006/05/05") equals 2006.

Year ("1997/04/20") equals 1997.

Year (35796) equals 1998.

Yeardelta

Yeardelta (date, Delta): Returns the date of the delta year after the specified date.

Example:

Yeardelta ("2008-10-10", 10) equals 2018-10-10.

Java Reporting Tools Finereport usage Summary of common functions (text and date functions)