Regular Expressions match Excel address strings

This article from: http://www.cnblogs.com/CodeBlove/archive/2011/08/09/2132748.html

 

 

In order to import and export databases and Excel files, you need to write an Excel worksheet class to address the unit range. Several days later, we decided to use a regular expression to determine the row and column address strings of cells. I have not found detailed descriptions on the Internet for a long time. I can only do it myself.

The same rule of books this day is really frustrating, but considering that the future is boundless, many strings are used for parsing in the past.ProgramIt is easy to use regular expressions to implement the method of loop logic judgment. It took two days to study Regular Expressions and finally met the requirements. During the study, we also learned the role of parentheses in regular expressions. The purpose of writing the study results is to review the results to enhance the impression and make a memo.

First, the regular expression of the Excel unit and range address is given:

Single address:

 (  ^  (\ $  ?  ) [  -  Za  - Z]  +  \  2  [  0  -  9  ]  *  $)  |  (  ^  \ $  ?  [  0  - 9  ]  +  $) 

Range address:

 ((  ^  (\ $  ?  ) [  -  Za  -  Z]  +  \  3  [  0 -  9  ]  *  )  |  (  ^  (\ $  ?  )[  0  -  9  ]  +  )):(((\  3  ) [ -  Za  -  Z]  +  \  3  [  0  -  9  ]  *  $)  |  (\  5  [  0 -  9  ]  +  $ )) 

And write the corresponding method. In C #, note the escape of the "\" symbol and write it as "\".

The following is the C # implementation method.

Using system. Text. regularexpressions;

View sourceprint?

// OthersCodeOmitted

View sourceprint?

// Match a single address

Public Static Bool Isxlscell (String Input)

{

Return RegEx. ismatch (input,"(^ (\\$ ?) [A-Za-Z] + \ 2 [0-9] * $) | (^ \ $? [0-9] + $ )");

}

// Match the range address

Public Static Bool Isxlsrange (String Input)

{

Return RegEx. ismatch (input,"(^ (\\$ ?) [A-Za-Z] + \ 3 [0-9] *) | (^ ?) [0-9] +) (\ 3) [A-Za-Z] + \ 3 [0-9] * $) | (\ 5 [0-9] + $ ))");

}

Analyze and create basic parts:

Take a single address as an example. In Excel, the format of a single cell address is composed of row and column parameters. The format is "$ column number $ row number", for example, "$ B $10". You can also omit "$ ", for example, "B10" indicates the cells in two columns and 10 rows. Both formats are supported in Excel. Analyze the other two cases of a single cell string. In Excel, two special cases are single and single columns in the format of "$ column number" and "$ row number", such as "$ A" and "$10 ", or "A" and "10" () respectively indicate the entire column with column number 2 and the row with row number 10.

(The method parameter order of the objects in Excel is the opposite: "cell [int row, int Col]. Value", which is the first row after. The address description isA letter always represents a column number, a number always represents a row number, and a letter is converted into a numerical value.ArticleDescription.)

After this analysis, we can see that there are three scenarios for a single unit address, plus six combinations of the "$" symbol combination. That is, "[$] [column] [$] [row]". This is the model, and the column can only be a letter (case-insensitive), and the row can only be a number, square brackets indicate that they can be omitted. Although this writing method is not very accurate, we can see the shadow of some regular expressions. We can also clearly see that in addition to the modifier of the "$" symbol, the Unit Address is composed of two parts, column and row.

The following is a step-by-step assembly of basic regular expressions. In order to be clear, the results of each item are given first, and each item can be understood as a component.

• Regular Expression of the "$" symbol"\ $?"

The "$" symbol is dispensable, and it cannot be left alone for integrity, but also for the appearance of the string, and to test the caching Effect of parentheses in the regular expression later. I have made such a rule, once the first match includes "$", the following match must exist. Otherwise, it is regarded as invalid, that is, non-match (this is simply an extra action. Haha, in fact, in actual application, replace before matching ). Matching a single dispensable symbol is"\ $?", Because" $ "in the regular expression Chinese end qualifier, so must escape, Escape Character and C-LIKE language, are" \ "character, followed by"?" It indicates that the previous match matches 0 or 1, that is, it is dispensable.

• The basic format of the column regular expression"[A-Za-Z]"

"[A-Za-Z]" indicates matching a letter in upper or lower case. Note that it only matches a letter,Square bracketsThere is a range in it. The range of matching letters is limited here, that is, the lowercase letters A to Z and the uppercase letters A to Z, and Z and a cannot contain other characters, including spaces. Now, we can extend it. The column number may consist of multiple letters. Therefore, add a qualifier after square brackets. If one or more operators need to be matched, use the "+" symbol, that is, "[A-Za-Z] +". To match 0 or more, use the "*" symbol, that is, "[A-Za-Z] *".

• The basic format of the row regular expression"[0-9]"

Needless to say, like above, 0-9 is a limited range, which means matching a single digit. Similarly, to match one or more numeric symbols is "[0-9] +". To match 0 or more numeric symbols, use "[0-9] *".

To sum up, both "+" and "*" are used to limit the previous matching items. You can understand that "+" has at least one, it can also be understood that "*" can be multiple consecutive or not.

Assembly:

The above three parts are the main parts required for unit address matching. For the convenience of description, these parts are called separately in order.X, Y, ZAs the code name, the Assembly is started below.

From the previous analysis, there are three scenarios for a single cell address: column (single cell), single column, single row, and below.

• The regular expression"^ \ $? [A-Za-Z] + \ $? [0-9] +$"

In this case, only "$" or no other characters are allowed before the column letter, so you need to use the "^" Start qualifier, which indicates that the matching item on the right is the string start, there are no other symbols, that is, ^ X, that is, add "^" before the first part, and expand to "^ \ $? ". Note the "?" . The next step is the column letter sequence, which is ^ xy. In a single cell address, the column must have one letter and may contain multiple letters. Therefore, the column must be followed by a "+ ", that is, ^ xy +. ^\ $?[-Za-Z] + ". Then assemble the expression part of the line. The code is Z. Okay, that is ^ xy + Z. Wait, it seems that something is missing. By the way, there may be a "$" in front of the number to match, insert ^ xy + xz. The number symbol in the row must also have one or more numeric symbols. Therefore, add "+", that is, ^ xy + xz +, then, by the way, there cannot be any other non-digit symbol behind the number. Add the end qualifier "$" (as mentioned earlier), and add it as"^ Xy + xz + $" After expansion, it is" ^\ $?[-Za-Z] + \ $? [0-9] + $ ".

• Regular Expression in a single column"^ \ $? [A-Za-Z] + $"

This is simple. According to the above instructions, it is easy to assemble,"^ Xy + $", Expand "^ \ $? [A-Za-Z] + $ ", that's simple.

• The regular expression"^ \ $? [0-9] + $"

Needless to say,"^ Xz + $", Expand "^ \ $? [0-9] + $ ".

Note that X, Y, and Z represent the above three basic parts, which must be substituted into expressions like algebra. The "^" symbol indicates the start, and the "$" symbol indicates the end. If it is not added, illegal characters such as "12ab34" cannot be blocked, it will also think that the cell address matches, so it is not allowed.

So far, it seems to have been completed, but in this case, we have to write three methods to match the three cases respectively, and we have to call them three times at the time of judgment, which is not perfect and troublesome, integration and optimization are required.

Optimization:

In order to facilitate understanding, we still use an algebraic description. We already have three expressions for different situations. They are"^ Xy + xz + $","^ Xy + $","^ Xz + $",We can see that the last two are subsets of the previous one.

Intersection

"^ Xy + xz + $" then "^ xy + $" = "^ xy + ". The intersection of a single column and a cell.

"^ Xy + xz + $" then "^ xz + $" = "xz + $ ". The intersection of a single row and a cell.

Makeup (if you forget any symbols, use minus signs instead)

"^ Xy + xz + $"-"^ xy + $" = "xz + ". Single Column and cell population.

"^ Xy + xz + $"-"^ xz + $" = "xy + ". Set a single row and a cell.

Rule: Align left. first look at the relationship between a single column and a cell. We can see a part of the intersection between a single column and a cell.       ^  XY   +". The set is " xz +  ", the difference is the row part. For a single column, the row number is missing and may not exist.               therefore,   Replace" + "with" * ", indicating that there are no rows.  

                       as:  " ^ xy + xz * $ "to match cells or Single Column.    

Two cases are solved at once, and one case of single rows can only be listed separately, that is, "^ xz + $ ".
 

    Then combine "(^XY+ Xz*$)|(^ Xz+$) ", Well, it's good. After such optimization, it should be the best. Well, we can do algebra and extend it into the following: 

 
"(^ \ $? [A-Za-Z] + \ $? [0-9] * $) | (^ \ $? [0-9] + $)"
 

 
I felt like it was, and finally forced the "$" symbol consistency (fever ). Because "? "Indicates dispensable. Therefore, the address of a single cell of" $ A10 "can also be matched. However, to test the cache mechanism of parentheses, you must use the" $ "symbol in a uniform way, or do not include it, either.
 

Enclose the matching items matching the "$" symbol in parentheses (where single columns and single rows are not involved). For details, see the highlighted Red Section:
 

 

 
"(^(\ $ ?)[A-Za-Z] +(\ $ ?)[0-9] * $) | (^ \ $? [0-9] + $ )"
 

 

 
No matter whether the previous match is successful or not, it must be consistent with the latter. According to the regular expression rules, parentheses will cache the matching results. "\ n" can reference the cached results, N indicates the cache number. The cache number ranges from 1 to 99 and is numbered by hierarchy. It is estimated that it is the principle of stack. Split the brackets to see the number of the first matching item.
 

 

View sourceprint?

(// Cache 1 #

^

(\ $ ?)// Cache 2 #

[A-Za-Z] +

(\ $ ?)// Cache 3 #

[0-9] * $

)

|

(// Cache 4 #

^ \ $? [0-9] + $

)

This is clear, the first "$" Match is 2 # cache, OK, a small operation, see the red letter, put "\ $ ?" Change to "\ 2" and add the Escape Character "\" of C #. This is the regular expression of the final single address.

"(^ (\\$ ?) [A-Za-Z] + \ 2 [0-9] * $) | (^ \ $? [0-9] + $)"
 

 

The range address is similar to this, which is more complicated. The above expression is successfully debugged in C # Of vs2008 without any bugs.