C#中最快生成Excel列字母的函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/837155/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Fastest function to generate Excel column letters in C#
提问by
What is the fastest c# function that takes and int and returns a string containing a letter or letters for use in an Excel function? For example, 1 returns "A", 26 returns "Z", 27 returns "AA", etc.
什么是最快的 c# 函数，它接受和 int 并返回一个包含一个或多个字母的字符串，以便在 Excel 函数中使用？例如，1 返回“A”，26 返回“Z”，27 返回“AA”等。
This is called tens of thousands of times and is taking 25% of the time needed to generate a large spreadsheet with many formulas.
这被称为数万次，并且占用了生成包含许多公式的大型电子表格所需时间的 25%。
public string Letter(int intCol) {
int intFirstLetter = ((intCol) / 676) + 64;
int intSecondLetter = ((intCol % 676) / 26) + 64;
int intThirdLetter = (intCol % 26) + 65;
char FirstLetter = (intFirstLetter > 64) ? (char)intFirstLetter : ' ';
char SecondLetter = (intSecondLetter > 64) ? (char)intSecondLetter : ' ';
char ThirdLetter = (char)intThirdLetter;
return string.Concat(FirstLetter, SecondLetter, ThirdLetter).Trim();
}
回答by JDunkerley
You could pre-generate all the values into an array of strings. This would take very little memory and could be calculated on the first call.
您可以将所有值预先生成为字符串数组。这将占用很少的内存，并且可以在第一次调用时计算。
回答by Doug
The absolute FASTEST, would be capitalizing that the Excel spreadsheet only a fixed number of columns, so you would do a lookup table. Declare a constant string array of 256 entries, and prepopulate it with the strings from "A" to "IV". Then you simply do a straight index lookup.
绝对最快，将大写 Excel 电子表格只有固定数量的列，所以你会做一个查找表。声明一个包含 256 个条目的常量字符串数组，并用从“A”到“IV”的字符串预填充它。然后您只需进行直接索引查找。
回答by womp
I can tell you that the fastest function will not be the prettiest function. Here it is:
我可以告诉你，最快的功能不会是最漂亮的功能。这里是：
private string[] map = new string[]
{
"A", "B", "C", "D", "E" .............
};
public string getColumn(int number)
{
return map[number];
}
回答by Noldorin
Try this function.
试试这个功能。
// Returns name of column for specified 0-based index.
public static string GetColumnName(int index)
{
var name = new char[3]; // Assumes 3-letter column name max.
int rem = index;
int div = 17576; // 26 ^ 3
for (int i = 2; i >= 0; i++)
{
name[i] = alphabet[rem / div];
rem %= div;
div /= 26;
}
if (index >= 676)
return new string(name, 3);
else if (index >= 26)
return new string(name, 2);
else
return new string(name, 1);
}
Now it shouldn't take up thatmuch memory to pre-generate each column name for every index and store them in a single huge array, so you shouldn't need to look up the name for any column twice.
现在，为每个索引预先生成每个列名并将它们存储在一个巨大的数组中不应该占用那么多内存，因此您不需要两次查找任何列的名称。
If I can think of any further optimisations, I'll add them later, but I believe this function should be pretty quick, and I doubt you even need this sort of speed if you do the pre-generation.
如果我能想到任何进一步的优化，我会在以后添加它们，但我相信这个功能应该很快，而且我怀疑如果你进行预生成，你甚至需要这种速度。
回答by shahkalpesh
Once your function has run, let it cache the results into a dictionary. So that, it won't have to do the calculation again.
函数运行后，让它将结果缓存到字典中。这样，它就不必再次进行计算。
e.g. Convert(27) will check if 27 is mapped/stored in dictionary. If not, do the calculation and store "AA" against 27 in the dictionary.
例如 Convert(27) 将检查 27 是否被映射/存储在字典中。如果不是，请进行计算并将“AA”对 27 存储在字典中。
回答by barrowc
Don't convert it at all. Excel can work in R1C1 notation just as well as in A1 notation.
根本不要转换它。Excel 可以使用 R1C1 表示法，就像使用 A1 表示法一样。
So (apologies for using VBA rather than C#):
所以（对于使用 VBA 而不是 C# 表示歉意）：
Application.Worksheets("Sheet1").Range("B1").Font.Bold = True
can just as easily be written as:
可以很容易地写成：
Application.Worksheets("Sheet1").Cells(1, 2).Font.Bold = True
The Rangeproperty takes A1 notation whereas the Cellsproperty takes (row number, column number).
该Range属性采用 A1 表示法，而该Cells属性采用（行号、列号）。
To select multiple cells: Range(Cells(1, 1), Cells(4, 6))(NB would need some kind of object qualifier if not using the active worksheet) rather than Range("A1:F4")
要选择多个单元格：（Range(Cells(1, 1), Cells(4, 6))如果不使用活动工作表，NB 将需要某种对象限定符）而不是Range("A1:F4")
The Columnsproperty can take either a letter (e.g. F) or a number (e.g. 6)
所述Columns属性可以是字母（如F）或数目（例如6）
回答by Neil N
Your first problem is that you are declaring 6 variables in the method. If a methd is going to be called thousands of times, just moving those to class scope instead of function scope will probably cut your processing time by more than half right off the bat.
您的第一个问题是您在方法中声明了 6 个变量。如果一个方法将被调用数千次，只需将它们移动到类范围而不是函数范围可能会立即将您的处理时间减少一半以上。
回答by foson
Caching really does cut the runtime of 10,000,000 random calls to 1/3 its value though:
缓存确实确实将 10,000,000 次随机调用的运行时间减少到其价值的 1/3：
static Dictionary<int, string> LetterDict = new Dictionary<int, string>(676);
public static string LetterWithCaching(int index)
{
int intCol = index - 1;
if (LetterDict.ContainsKey(intCol)) return LetterDict[intCol];
int intFirstLetter = ((intCol) / 676) + 64;
int intSecondLetter = ((intCol % 676) / 26) + 64;
int intThirdLetter = (intCol % 26) + 65;
char FirstLetter = (intFirstLetter > 64) ? (char)intFirstLetter : ' ';
char SecondLetter = (intSecondLetter > 64) ? (char)intSecondLetter : ' ';
char ThirdLetter = (char)intThirdLetter;
String s = string.Concat(FirstLetter, SecondLetter, ThirdLetter).Trim();
LetterDict.Add(intCol, s);
return s;
}
I think caching in the worst-case (hit every value) couldn't take up more than 250kb (17576 possible values * (sizeof(int)=4 + sizeof(char)*3 + string overhead=2)
我认为在最坏的情况下（命中每个值）缓存不能超过 250kb（17576 个可能的值 * (sizeof(int)=4 + sizeof(char)*3 + 字符串开销 = 2）
回答by foson
It is recursive. Fast, and right :
它是递归的。快速，正确：
class ToolSheet
{
//Not the prettyest but surely the fastest :
static string[] ColName = new string[676];
public ToolSheet()
{
ColName[0] = "A";
for (int index = 1; index < 676; ++index) Recurse(index, index);
}
private int Recurse(int i, int index)
{
if (i < 1) return 0;
ColName[index] = ((char)(65 + i % 26)).ToString() + ColName[index];
return Recurse(i / 26, index);
}
public string GetColName(int i)
{
return ColName[i - 1];
}
}
回答by foson
sorry there was a shift. corrected.
对不起有一个转变。更正。
class ToolSheet
{
//Not the prettyest but surely the fastest :
static string[] ColName = new string[676];
public ToolSheet()
{
for (int index = 0; index < 676; ++index)
{
Recurse(index, index);
}
}
private int Recurse(int i, int index)
{
if (i < 1)
{
if (index % 26 == 0 && index > 0) ColName[index] = ColName[index - 1].Substring(0, ColName[index - 1].Length - 1) + "Z";
return 0;
}
ColName[index] = ((char)(64 + i % 26)).ToString() + ColName[index];
return Recurse(i / 26, index);
}
public string GetColName(int i)
{
return ColName[i - 1];
}
}
