带字母计数扩展

• 下载源代码 - 1.52 KB
• 下载 ExcelUtils 库源代码 - 4.15 KB

引言

如果您需要像 Excel 那样按字母计数列，请查看 Zeltera 的 "使用字母计数"。
他的例程可以扩展为将整数转换为 Excel 列，或将 Excel 列转换为整数。很久以前，我编写了两个非常简单、效率低的循环包装器，围绕 Zeltera 的核心代码来完成这项工作。在这里，我提供了一种计算结果的方法，以将循环次数减少到不超过 26 次。

背景

Zeltera 的例程很好地从 "a" 计数到 "z"，然后跳到 "aa" 到 "xdf"（Excel 的最大值）。由于我只想“完成这项工作”，所以我编写了以下小包装器。（此处已最小化）。这些循环只是从 "a" 到任何字母。计算机速度很快，我不需要处理大量的列，因此计算时间并不重要。（我相信你也写过你知道效率很低的代码。）

string IntToExcelColumn(int value)
    string str = "a";
    for (int inx = 1; inx < value; inx++) { str = GetNext(str); }
    return str;

int ExcelColumnToInt(string column)
    for (int inx = 1; inx < 16384; inx++)
    {
        string str = IntToExcelColumn(inx).ToLower();
        if (String.Compare(col, str) == 0) { return inx; }
    }

代码讨论

Excel 将列数限制为 XDF，即 16384 列。显然，使用单个字母，可以从 1 到 26，列 A 到 Z。因此，可以推断出要到达 AA，需要经过 26 两次。因此，由于 G 是字母表中的第 7 个字母，AG 的计算结果为 (26 * 2) + 7 = 59。按照这个推理，AAA 的数值必须是 676 (26 x 26 = 676)。然后，你只需要进行推广即可。

我提出了这些规则（示例： "G", "GG", "GGG"）来获得一个整数

1)  For one alpha: The numeric value within the alphabet.
    Thus:  (G = 7)

2)  For two alphas: the numeric value of the most significant times 26 
    plus the numeric value of the lest significant.
    Thus:  (G# = 26 x 7 = 182) + (#G = 7) = 189

3)  For three alphas: the numeric value of the most significant time 676 
    plus 26 times the next most significant plus the numeric value of the lest significant.
    Thus (G## = 676 x 7 = 4732) + (#G# = 182) + (##G = 7) = 4921

4)  No further as Excel is limited to three alphas (XFD, 16384).

这些也可以用来生成字母列。如果将结果除以 676，则得到最高位字母的值，然后将余数除以 26，则得到下一位字母的值；剩下的就是最低位字母。

代码

const int MAXINT = 16384;
const int TWOALPHAS = 26;
const int THREEALPHAS = 676;

// Input must be greater than 0 and less than or equal to 16384
// On error: returns an empty string
public string IntToExcelColumn(int value)
{
    // Excel is limited to the integer 16384, XFD
    if ((value < 1) || (value > MAXINT)) return String.Empty;

    // Discussion on converting from a column to an integer:
    //1) For one alpha: The numeric value within the alphabet
    //    Thus:  G = 7
    //2) For two alphas: the numeric value of the most significant times 26 plus
    //   the numeric value of the lest significant.
    //    Thus:  (G = 26 x 7 = 182) + (G = 7) = 189
    //3) For three alphas: the numeric value of the most significant time 676 plus
    //   26 times the next most significant plus the numeric value of the lest significant
    //    Thus (G## = 676 x 7 = 4732) + (#G# = 182) + (##G = 7) = 4921
    //4) No further as Excel is limited to three alphas (XFD, 16384).

    // Discussion on converting from an integer to a column
    // The numbers above apply.  If one divides by 676, one gets the value of the third alpha
    // Then the remainder by 26 one gets the value of the second alpha; and what is
    // left over is the final alpha.

    int remainder = value;
    int[] aryInt = new int[3];
    string[] aryStr = new string[3];
  
    if (remainder > THREEALPHAS)
    {
        aryInt[2] = remainder / THREEALPHAS;
        remainder = remainder % THREEALPHAS;
        aryStr[2] = CountUpToValue(aryInt[2]);

        aryInt[1] = remainder / TWOALPHAS;
        remainder = remainder % TWOALPHAS;
        aryStr[1] = CountUpToValue(aryInt[1]);
    }
    else if (value > TWOALPHAS)
    {
        aryInt[1] = remainder / TWOALPHAS;
        remainder = remainder % TWOALPHAS;
        aryStr[1] = CountUpToValue(aryInt[1]);
    }
    aryInt[0] = remainder;
    aryStr[0] = CountUpToValue(aryInt[0]);

    string rtn = aryStr[2] + aryStr[1] + aryStr[0];
    return rtn.ToUpper();
}

//--------------------------------------------------------------
// Computes the excel column integer
// Input is limited to one three alphas with the maximum "XDF"
// On error: returns 0
public int ExcelColumnToInt(string column)
{
    // Discussion:
    // 1) For one alpha: The numeric value within the alphabet
    //      Thus:  G = 7
    // 2) For two alphas: the numeric value of the most significant times 26 plus
    //    the numeric value of the lest significant.
    //      Thus:  (G = 26 x 7 = 182) + (G = 7) = 189
    // 3) For three alphas: the numeric value of the most significant time 676 plus
    //    26 times the next most significant plus the numeric value of the lest significant
    //      Thus (G## = 676 x 7 = 4732) + (#G# = 182) + (##G = 7) = 4921
    // 4) No further as Excel is limited to three alphas (XFD, 16384).

    string col = column.ToLower();

    // Verify a valid input
    if (col.Length > 3) return 0;

    int[] aryInt = new int[3];
    int[] aryCalc = new int[3];
    for (int inx = 0; inx < col.Length; inx++)
    {
        aryInt[inx] = CountToAlpha(col.Substring(inx, 1));
        if (aryInt[inx] == 0) return 0;

        if (col.Length == 1)
        {
            aryCalc[inx] = aryInt[inx];
            if (aryInt[inx] == 0) return 0;
        }
        else if (col.Length == 2)
        {
            switch (inx)
            {
                case 0: aryCalc[inx] = aryInt[inx] * TWOALPHAS; break;
                case 1: aryCalc[inx] = aryInt[inx]; break;
            }
        }
        else
        {
            switch (inx)
            {
                case 0: aryCalc[inx] = aryInt[inx] * THREEALPHAS; break;
                case 1: aryCalc[inx] = aryInt[inx] * TWOALPHAS; break;
                case 2: aryCalc[inx] = aryInt[inx]; break;
            }
        }
    }

    int rtn = aryCalc[0] + aryCalc[1] + aryCalc[2];
    if (rtn > MAXINT) return 0;
    return rtn;
}

你需要这两个小包装器

//--------------------------------------------------------------
// For internal call to verify valid input
// Expect lower case input
// Feed values 1 through 26 (a-z) 
private string CountUpToValue(int value)
{
    string str = "a";
    if (value == 1) return str;

    for (int inx = 1; inx < value; inx++)
    {
        str = GetNext(str);
    }
    return str;
}
    
//--------------------------------------------------------------
// For internal call to verify valid input
// Expect lower case input
// Feed only one apha letter "a" through "z"
private int CountToAlpha(string column)
{
    string str;
    for (int inx = 1; inx < (MAXINT + 1); inx++)
    {
        str = CountUpToValue(inx);
        if (String.Compare(column, str) == 0)
        {
            return inx;
        }
    }
    return 0;
}

最后，你需要 Zeltera 的这段代码

// From Code Project: "Count with letters" by Zeltera 17 Jun 2007 
private String GetNext(String currentString)
{
    currentString = currentString.ToLower();
    if (currentString.Length == 0)
        return "a";

    Char lastCharacter = currentString[currentString.Length - 1];
    String subString = currentString.Substring(0, currentString.Length - 1);

    if (lastCharacter == 'z')
        return GetNext(subString) + 'a';
    else
        return subString + (char)((int)lastCharacter + 1);
}

关注点

由于从效率低的循环到少量计算，处理速度得到了极大的提高，从...

public string IntToExcelColumn(int value)

...和...

public int ExcelColumnToInt(string column)

...到编写使用它们的附加范围型包装器，就很容易了

public List<string> IntToExcelColumnsList(string start, int count)
public List<string> IntToExcelColumnsList(int first, int count)
public List<string> ColumnToExcelColumnList(string start, int count)
public List<string> ColumnToExcelColumnList(int start, int end)
public List<string> ColumnToExcelColumnList(string start, string end)

如果您需要它们，请下载 ExcelUtils 库的源代码。其中还有其他东西。我不保证其他代码。请在使用前对其进行测试，因为我很久没有使用过它了。

历史

• 2011年6月21日：初始版本