如何在c#中将数值转换为Excel列名,而不使用直接从Excel中获取值的自动化。
Excel 2007的范围可能是1到16384,这是它支持的列数。结果值应以excel列名的形式出现,例如A、AA、AAA等。
如何在c#中将数值转换为Excel列名,而不使用直接从Excel中获取值的自动化。
Excel 2007的范围可能是1到16384,这是它支持的列数。结果值应以excel列名的形式出现,例如A、AA、AAA等。
当前回答
static string[] ExcelColumnAlphabetIdentifiers = new string[] { "", "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N",
"O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };
public static string ExcelColumnAlphabetIdentifier( int ColumnNumber)
{
StringBuilder sb = new StringBuilder();
int remainder = ColumnNumber;
do
{
sb.Append(ExcelColumnAlphabetIdentifiers[remainder % 26]);
remainder = remainder / 26;
}
while (remainder > 0);
return sb.ToString();
}
其他回答
到目前为止,所有的解决方案都包含迭代或递归,这让我感到惊讶。
这是我的解,在常数时间内运行(没有循环)。此解决方案适用于所有可能的Excel列,并检查输入是否可以转换为Excel列。可能的列在[A, XFD]或[1,16384]范围内。(这取决于你的Excel版本)
private static string Turn(uint col)
{
if (col < 1 || col > 16384) //Excel columns are one-based (one = 'A')
throw new ArgumentException("col must be >= 1 and <= 16384");
if (col <= 26) //one character
return ((char)(col + 'A' - 1)).ToString();
else if (col <= 702) //two characters
{
char firstChar = (char)((int)((col - 1) / 26) + 'A' - 1);
char secondChar = (char)(col % 26 + 'A' - 1);
if (secondChar == '@') //Excel is one-based, but modulo operations are zero-based
secondChar = 'Z'; //convert one-based to zero-based
return string.Format("{0}{1}", firstChar, secondChar);
}
else //three characters
{
char firstChar = (char)((int)((col - 1) / 702) + 'A' - 1);
char secondChar = (char)((col - 1) / 26 % 26 + 'A' - 1);
char thirdChar = (char)(col % 26 + 'A' - 1);
if (thirdChar == '@') //Excel is one-based, but modulo operations are zero-based
thirdChar = 'Z'; //convert one-based to zero-based
return string.Format("{0}{1}{2}", firstChar, secondChar, thirdChar);
}
}
..并转换为php:
function GetExcelColumnName($columnNumber) {
$columnName = '';
while ($columnNumber > 0) {
$modulo = ($columnNumber - 1) % 26;
$columnName = chr(65 + $modulo) . $columnName;
$columnNumber = (int)(($columnNumber - $modulo) / 26);
}
return $columnName;
}
精炼原始的解决方案(在c#中):
public static class ExcelHelper
{
private static Dictionary<UInt16, String> l_DictionaryOfColumns;
public static ExcelHelper() {
l_DictionaryOfColumns = new Dictionary<ushort, string>(256);
}
public static String GetExcelColumnName(UInt16 l_Column)
{
UInt16 l_ColumnCopy = l_Column;
String l_Chars = "0ABCDEFGHIJKLMNOPQRSTUVWXYZ";
String l_rVal = "";
UInt16 l_Char;
if (l_DictionaryOfColumns.ContainsKey(l_Column) == true)
{
l_rVal = l_DictionaryOfColumns[l_Column];
}
else
{
while (l_ColumnCopy > 26)
{
l_Char = l_ColumnCopy % 26;
if (l_Char == 0)
l_Char = 26;
l_ColumnCopy = (l_ColumnCopy - l_Char) / 26;
l_rVal = l_Chars[l_Char] + l_rVal;
}
if (l_ColumnCopy != 0)
l_rVal = l_Chars[l_ColumnCopy] + l_rVal;
l_DictionaryOfColumns.ContainsKey(l_Column) = l_rVal;
}
return l_rVal;
}
}
我正在尝试在Java中做同样的事情… 我写了以下代码:
private String getExcelColumnName(int columnNumber) {
int dividend = columnNumber;
String columnName = "";
int modulo;
while (dividend > 0)
{
modulo = (dividend - 1) % 26;
char val = Character.valueOf((char)(65 + modulo));
columnName += val;
dividend = (int)((dividend - modulo) / 26);
}
return columnName;
}
现在,一旦我用columnNumber = 29运行它,它给我的结果=“CA”(而不是“AC”) 有什么意见吗? 我知道我可以通过StringBuilder....反转它但看着格雷厄姆的回答,我有点困惑....
微软Excel微型,快速和肮脏的公式。
Hi,
下面是一种从数字....获取Excel字符-列-头的方法
我为Excel单元格创建了一个公式。
(即我采取了不使用VBA编程的方法。)
这个公式查看一个有数字的单元格,然后告诉你这个列是什么——用字母表示。
如图所示:
I put 1,2,3 etc in the top row all the way out to column ABS. I pasted my formula in the second row all the way out to ABS. My formula looks at row 1 and converts the number to Excel's column header id. My formula works for all numbers out to 702 (zz). I did it in this manner to prove that the formula works so you can look at the output from the formula and look at the column header above and easily visually verify that the formula works. :-) =CONCATENATE(MID("_abcdefghijklmnopqrstuvwxyz",(IF(MOD(K1,26)>0,INT(K1/26)+1,(INT(K1/26)))),1),MID("abcdefghijklmnopqrstuvwxyz",IF(MOD(K1,26)=0,26,MOD(K1,26)),1))
下划线的存在是为了调试目的——让您知道有一个实际的空格,并且它正在正确地工作。
用上面的公式——不管你在K1里放什么——这个公式会告诉你列的标题是什么。
目前的公式只有2位数字(ZZ),但可以修改为添加第3个字母(ZZZ)。