这正则表达式是能够在C＃中的公式中选择Excel列名称？ [英] Which regular expression is able to select excel column names in a formula in C#?
问题描述
我nedding实现在C＃中的Excel公式自动填充
I'm nedding to implement excel formula autofill in C#.
让我们假设这个公式是位于B100：
Let's suppose this formula is located at B100:
=SUM($B$99:B99)
我想使这个公式的不同在C100：
I want to make this formula different at C100:
=SUM($B$99:C99)
此公式仅是一个例子。一些真实的例子是：
This formula is only an example. Some real examples are:
=(SUM($B${0}:B{0})/SUM({1}!$B${0}:{1}!B{0}) -1)
=SUM(B{0}:B{1})
=B{0} + B{1}
=C{0}+ B{1}
=$B${0}+ AC{1}
（考虑{0}和{1}，其实，数字）
(consider {0} and {1} are, in fact, numbers)
我需要做的什么，一般是挑这些列名和增量他们。列名由$公式不应该更新包围。
What I need to do, generically, is to pick these column names and "increment" them. Column names surrounded by $ in formulas should not be updated.
如何用正则表达式来识别这些领域？
How to identify these fields with regex?
推荐答案
下面是一个正则表达式的解决方案，只涉及公式。我将离开Excel的东西给你。 。只要你有一个代表公式中的字符串的集合，你可以通过这个运行它们来增加你的列名
Here's a regex solution that solely deals with formulas. I'll leave the Excel stuff to you. Provided you have a collection of strings representing your formulas, you can run them through this to increment your column names.
一些评论：
- **测试此彻底！**也许手工做一个表，并与生成的结果比较你的努力。
- 这是不应该意外地改变了适应单元格命名模式函数名。如果你知道你的公式让Excel函数名，包括数字，留意他们和 - 再 - **验证结果**
- 正则表达式不验证你在做什么。喂养它是一个公式 - 我把它你只使用公式。换句话说，我没能检查字符串以=号。如果你打算通过这个其他单元格的值来养活非公式，再加入其中，IsMatch在If分支使用formula.StartsWith（=）使用的检查。要明白我指的是，一个额外的测试字符串添加到我的样本，如退房T4代 - 如果没有StartsWith（=）作出检验，将匹配和T4将成为U4
正则表达式模式实际上是比较容易的部分。它只是将匹配任何字母数字序列，而忽略$ A $ 1和$ A1类型的细胞。最棘手的部分是递增列中的逻辑。我已经添加评论，以澄清一点，所以抓住一些咖啡，读了一下：）
The regex pattern was actually the easy part. It will just match any letter-number sequence, and ignores $A$1 and $A1 types of cells. The tricky part was the logic to increment the column. I've added comments to clarify that bit so grab some coffee and read it over :)
我敢肯定，这可以加强，但是这是我有时间
I'm sure this could be enhanced but this is what I had time for.
using System.Text.RegularExpressions;
static void Main(string[] args)
{
string[] formulas = { "Z1", "ZZ1", "AZ1", "AZB1", "BZZ2",
"=SUM($B$99:B99)","=SUM($F99:F99)", "=(SUM($B$0:B0)/SUM(1!$B$11:22!B33) -1)",
"=SUM(X80:Z1)", "=A0 + B1 - C2 + Z5", "=C0+ B1",
"=$B$0+ AC1", "=AA12-ZZ34 + AZ1 - BZ2 - BX3 + BZX4",
"=SUMX2MY2(A2:A8,B2:B8)", // ensure function SUMX2MY2 isn't mistakenly incremented
"=$B$40 + 50 - 20" // no match
//,"Check out T4 generation!" // not a formula but it'll still increment T4, use formula.StartsWith("=")
};
// use this if you don't want to include regex comments
//Regex rxCell = new Regex(@"(?<![$])\b(?<col>[A-Z]+)(?<row>\d+)\b");
// regex comments in this style requires RegexOptions.IgnorePatternWhitespace
string rxCellPattern = @"(?<![$]) # match if prefix is absent: $ symbol (prevents matching $A1 type of cells)
# (if all you have is $A$1 type of references, and not $A1 types, this negative look-behind isn't needed)
\b # word boundary (prevents matching Excel functions with a similar pattern to a cell)
(?<col>[A-Z]+) # named capture group, match uppercase letter at least once
# (change to [A-Za-z] if you have lowercase cells)
(?<row>\d+) # named capture group, match a number at least once
\b # word boundary
";
Regex rxCell = new Regex(rxCellPattern, RegexOptions.IgnorePatternWhitespace);
foreach (string formula in formulas)
{
if (rxCell.IsMatch(formula))
{
Console.WriteLine("Formula: {0}", formula);
foreach (Match cell in rxCell.Matches(formula))
Console.WriteLine("Cell: {0}, Col: {1}", cell.Value, cell.Groups["col"].Value);
// the magic happens here
string newFormula = rxCell.Replace(formula, IncrementColumn);
Console.WriteLine("Modified: {0}", newFormula);
}
else
{
Console.WriteLine("Not a match: {0}", formula);
}
Console.WriteLine();
}
}
private static string IncrementColumn(Match m)
{
string col = m.Groups["col"].Value;
char c;
// single character column name (ie. A1)
if (col.Length == 1)
{
c = Convert.ToChar(col);
if (c == 'Z')
{
// roll over
col = "AA";
}
else
{
// advance to next char
c = (char)((int)c + 1);
col = c.ToString();
}
}
else
{
// multi-character column name (ie. AB1)
// in this case work backwards to do some column name "arithmetic"
c = Convert.ToChar(col.Substring(col.Length - 1, 1)); // grab last letter of col
if (c == 'Z')
{
string temp = "";
for (int i = col.Length - 1; i >= 0; i--)
{
// roll over should occur
if (col[i] == 'Z')
{
// prepend AA if current char is not the last char in column and its next neighbor was also a Z
// ie. column BZZ: if current char is 1st Z, it's neighbor Z (2nd Z) just got incremented, so 1st Z becomes AA
if (i != col.Length - 1 && col[i + 1] == 'Z')
{
temp = "AA" + temp;
}
else
{
// last char in column is Z, becomes A (this will happen first, before the above if branch ever happens)
temp = "A" + temp;
}
}
else
{
temp = ((char)((int)col[i] + 1)).ToString() + temp;
}
}
col = temp;
}
else
{
// advance char
c = (char)((int)c + 1);
// chop off final char in original column, append advanced char
col = col.Remove(col.Length - 1) + c.ToString();
}
}
// updated column and original row (from regex match)
return col + m.Groups["row"].Value;
}
结果应该是这样的（我删除了简洁的细胞破裂）：
The results should look like this (I removed the cell breakdown for brevity):
Formula: Z1
Modified: AA1
Formula: ZZ1
Modified: AAA1
Formula: AZ1
Modified: BA1
Formula: AZB1
Modified: AZC1
Formula: BZZ2
Modified: CAAA2
Formula: =SUM($B$99:B99)
Modified: =SUM($B$99:C99)
Formula: =SUM($F99:F99)
Modified: =SUM($F99:G99)
Formula: =(SUM($B$0:B0)/SUM(1!$B$11:22!B33) -1)
Modified: =(SUM($B$0:C0)/SUM(1!$B$11:22!C33) -1)
Formula: =SUM(X80:Z1)
Modified: =SUM(Y80:AA1)
Formula: =A0 + B1 - C2 + Z5
Modified: =B0 + C1 - D2 + AA5
Formula: =C0+ B1
Modified: =D0+ C1
Formula: =$B$0+ AC1
Modified: =$B$0+ AD1
Formula: =AA12-ZZ34 + AZ1 - BZ2 - BX3 + BZX4
Modified: =AB12-AAA34 + BA1 - CA2 - BY3 + BZY4
Formula: =SUMX2MY2(A2:A8,B2:B8)
Modified: =SUMX2MY2(B2:B8,C2:C8)
Not a match: =$B$40 + 50 - 20
这篇关于这正则表达式是能够在C＃中的公式中选择Excel列名称？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
