2.3.4 use npoi to Operate Excel-string functions

In this section, we start to learn another very common function-string function in Excel. It provides a rich array of string functions in Excel and is also well supported in npoi.
I. case-insensitive conversion functions
Lower (string): converts all uppercase letters in a string to lowercase letters.
Upper (string): converts text into a large write format.
Proper (string): converts the first letter of a text string and the first letter after any non-letter character into uppercase. Convert other letters to lowercase letters.
Corresponding C #CodeThe code for setting the formula is similar to that described in the previous sections:

Hssfrow row1 = Sheet1.createrow ( 0 );
Row1.createcell ( 0 ). Setcellvalue ( " String to be operated " );
Row1.createcell ( 1 ). Setcellvalue ( " Operation Functions " );
Row1.createcell ( 2 ). Setcellvalue ( " Operation Result " );

Hssfrow row2 = Sheet1.createrow ( 1 );
Row2.createcell ( 0 ). Setcellvalue ( " This is a npoi example! " );
Row2.createcell ( 1 ). Setcellvalue ( " Lower (A2) " );
// Replace "lower (A2)" in this sentence with upper (A2) and proper (A2) to see different effects.
Row2.createcell ( 2 ). Setcellformula ( " Lower (A2) " );

2. extract some characters in the string
Left (text, num_chars): Left (text, num_chars) where text is the string containing the characters to be extracted. Num_chars specifies the number of characters to be extracted from left.

Mid (text, start_num, num_chars): Mid (text, start_num, num_chars). Text is the text string containing the characters to extract. Start_num is the position of the first character to be extracted in the text. num_chars indicates the number of characters to be extracted.

Right (text, num_chars): Right (text, num_chars) where text is the string containing the characters to extract. Num_chars specifies the number of characters to be extracted by right.
The code is similar to the above and will not be written.

3. Remove blank strings
Trim (text): text indicates the text in which spaces need to be cleared. Note that, unlike trim in C #, the trim function in Excel not only deletes the character at the beginning and end of the string, but also removes extra characters in the string, and only one space is reserved between words.

Iv. String comparison
Exact (text1, text2): Compares whether two strings are equal and case sensitive.

The execution result is as follows:

Here, I only briefly talk about common functions. There are many character string functions in Excel, which can be tested one by one.

Function Name	Function Description	Syntax
ASC	Change the full-width (double-byte) English letters in the string to half-width (single-byte) characters.	ASC (text)
Char	Returns the character corresponding to the numeric code. The function char can convert the code in other types of computer files into characters.	Char (number)
Clean	Delete characters that cannot be printed in text. For other applicationsProgramUse the clean function to delete the characters that cannot be printed by the operating system. For example, you can delete low-level computer code that usually appears in the header or tail of a data file and cannot be printed.	Clean (text)
Code	Returns the numerical code of the first character in a text string. The Returned Code corresponds to the character set currently used by the computer.	Code (text)
Concatenate	Merge several text strings into one text string.	Concatenate (text1, text2 ,...)
Dollar	Rounds the decimal number to the specified number of digits according to the currency format and converts it to text.	Dollar or RMB (number, decimals)
Exact	This function tests whether the two strings are identical. If they are identical, true is returned; otherwise, false is returned. The function exact can be case sensitive, but the format difference is ignored. Use the function exact to test the text in the input document.	Exact (text1, text2)
Find	Find is used to find the find_text in other text strings (within_text), and returns the start position number of find_text starting from the first character of within_text.	Find (find_text, within_text, start_num)
Fixed	Rounds the number of decimal places by a specified number of decimal places. The period and comma are used to set the format of the number in decimal format and return the result in the form of a text string.	Fixed (number, decimals, no_commas)
JIS	Change the half-width (single-byte) English letters or katakana to full-width (double-byte) characters.	JIS (text)
Left	Left returns the first or first few characters in a text string based on the specified number of characters. Leftb returns the first or first few characters in a text string based on the specified number of bytes. This function is used for double-byte characters.	Left (text, num_chars) Leftb (text, num_bytes)
Len	Len returns the number of characters in a text string. Lenb returns the number of bytes used to represent characters in a text string. This function is used for double-byte characters.	Len (text) Lenb (text)
Lower	Converts all uppercase letters in a string to lowercase letters.	Lower (text)
Mid	Mid Returns the number of characters starting from the specified position in the text string. The number is specified by the user. The number of characters in the text string returned by midb starting from the specified position. The number is specified by the user. This function is used for double-byte characters.	Mid (text, start_num, num_chars) Midb (text, start_num, num_bytes)
Phonetic	Extract the pinyin (furigana) characters from the text string.	Phonetic (reference)
Proper	Converts the first letter of a string and the first letter after any non-letter character into a uppercase letter. Convert other letters to lowercase letters.	Proper (text)
Replace	Replace uses other text strings and replaces some text in a text string with the specified number of characters. Replaceb uses other text strings and replaces some text in a text string with the specified number of characters. This function is designed for dubyte characters.	Replace (old_text, start_num, num_chars, new_text) Replaceb (old_text, start_num, num_bytes, new_text)
Rept	The text is repeatedly displayed based on the given number of times. You can use the rept function to repeatedly display a text string and fill the cell.	Rept (text, number_times)
Right	Right returns the last or multiple characters in the text string based on the specified number of characters. Rightb returns the last or multiple characters in the text string based on the specified number of characters. This function is used for double-byte characters.	Right (text, num_chars) Rightb (text, num_bytes)
Search	Search returns the number of a specific character at the position of a specific character or text string for the first time starting from start_num. Use search to determine the position of a character or text string in another text string, so that you can use the mid or replace function to change the text. Searchb can also find the find_text in other text strings (within_text) and return the start position number of find_text. The result is based on the number of bytes used by each character and starts from start_num. This function is used for double-byte characters. In addition, you can use findb to search for text strings in other text strings.	Search (find_text, within_text, start_num) Searchb (find_text, within_text, start_num)
Substitute	Replace old_text with new_text in the text string. To replace the specified text in a string, use the Substitute function. To replace any text at the specified position in a string, use the replace function.	Substitute (text, old_text, new_text, instance_num)
T	Converts a value to a text value.	T (value)
Text	Converts a value to text in the specified numeric format.	Text (value, format_text)
Trim	In addition to a single space between words, clear all spaces in the text. You can use the trim function to retrieve text with irregular spaces from other applications.	Trim (text)
Upper	Converts text into a large write format.	Upper (text)
Value	Converts a string representing a number to a number.	Value (text)
Widechar	Converts a single-byte character to a double-byte character.	Widechar (text)
Yen	Convert the number into text in the format of $ (YEN) and round the number at the specified position.	Yen (number, decimals)

 

Returned directory