如何在c#中将数值转换为Excel列名,而不使用直接从Excel中获取值的自动化。
Excel 2007的范围可能是1到16384,这是它支持的列数。结果值应以excel列名的形式出现,例如A、AA、AAA等。
如何在c#中将数值转换为Excel列名,而不使用直接从Excel中获取值的自动化。
Excel 2007的范围可能是1到16384,这是它支持的列数。结果值应以excel列名的形式出现,例如A、AA、AAA等。
当前回答
public string ToBase26(int number)
{
if (number < 0) return String.Empty;
int remainder = number % 26;
int value = number / 26;
return value == 0 ?
String.Format("{0}", Convert.ToChar(65 + remainder)) :
String.Format("{0}{1}", ToBase26(value - 1), Convert.ToChar(65 + remainder));
}
其他回答
抱歉,这是Python而不是c#,但至少结果是正确的:
def ColIdxToXlName(idx):
if idx < 1:
raise ValueError("Index is too small")
result = ""
while True:
if idx > 26:
idx, r = divmod(idx - 1, 26)
result = chr(r + ord('A')) + result
else:
return chr(idx + ord('A') - 1) + result
for i in xrange(1, 1024):
print "%4d : %s" % (i, ColIdxToXlName(i))
f#版本的各种方式
let rec getExcelColumnName x = if x<26 then int 'A'+x|>char|>string else (x/26-1|>c)+ c(x%26)
对不起,最小化,正在开发一个更好的https://stackoverflow.com/a/4500043/57883版本
相反的方向:
// return values start at 0
let getIndexFromExcelColumnName (x:string) =
let a = int 'A'
let fPow len i =
Math.Pow(26., len - 1 - i |> float)
|> int
let getValue len i c =
int c - a + 1 * fPow len i
let f i = getValue x.Length i x.[i]
[0 .. x.Length - 1]
|> Seq.map f
|> Seq.sum
|> fun x -> x - 1
这些我的代码转换特定的数字(索引从1开始)到Excel列。
public static string NumberToExcelColumn(uint number)
{
uint originalNumber = number;
uint numChars = 1;
while (Math.Pow(26, numChars) < number)
{
numChars++;
if (Math.Pow(26, numChars) + 26 >= number)
{
break;
}
}
string toRet = "";
uint lastValue = 0;
do
{
number -= lastValue;
double powerVal = Math.Pow(26, numChars - 1);
byte thisCharIdx = (byte)Math.Truncate((columnNumber - 1) / powerVal);
lastValue = (int)powerVal * thisCharIdx;
if (numChars - 2 >= 0)
{
double powerVal_next = Math.Pow(26, numChars - 2);
byte thisCharIdx_next = (byte)Math.Truncate((columnNumber - lastValue - 1) / powerVal_next);
int lastValue_next = (int)Math.Pow(26, numChars - 2) * thisCharIdx_next;
if (thisCharIdx_next == 0 && lastValue_next == 0 && powerVal_next == 26)
{
thisCharIdx--;
lastValue = (int)powerVal * thisCharIdx;
}
}
toRet += (char)((byte)'A' + thisCharIdx + ((numChars > 1) ? -1 : 0));
numChars--;
} while (numChars > 0);
return toRet;
}
我的单元测试:
[TestMethod]
public void Test()
{
Assert.AreEqual("A", NumberToExcelColumn(1));
Assert.AreEqual("Z", NumberToExcelColumn(26));
Assert.AreEqual("AA", NumberToExcelColumn(27));
Assert.AreEqual("AO", NumberToExcelColumn(41));
Assert.AreEqual("AZ", NumberToExcelColumn(52));
Assert.AreEqual("BA", NumberToExcelColumn(53));
Assert.AreEqual("ZZ", NumberToExcelColumn(702));
Assert.AreEqual("AAA", NumberToExcelColumn(703));
Assert.AreEqual("ABC", NumberToExcelColumn(731));
Assert.AreEqual("ACQ", NumberToExcelColumn(771));
Assert.AreEqual("AYZ", NumberToExcelColumn(1352));
Assert.AreEqual("AZA", NumberToExcelColumn(1353));
Assert.AreEqual("AZB", NumberToExcelColumn(1354));
Assert.AreEqual("BAA", NumberToExcelColumn(1379));
Assert.AreEqual("CNU", NumberToExcelColumn(2413));
Assert.AreEqual("GCM", NumberToExcelColumn(4823));
Assert.AreEqual("MSR", NumberToExcelColumn(9300));
Assert.AreEqual("OMB", NumberToExcelColumn(10480));
Assert.AreEqual("ULV", NumberToExcelColumn(14530));
Assert.AreEqual("XFD", NumberToExcelColumn(16384));
}
如果你想以实用的方式引用单元格,那么如果你使用工作表的Cells方法,你会得到更可读的代码。它接受行和列索引,而不是传统的单元格引用。它与Offset方法非常相似。
我今天必须做这个工作,我的实现使用递归:
private static string GetColumnLetter(string colNumber)
{
if (string.IsNullOrEmpty(colNumber))
{
throw new ArgumentNullException(colNumber);
}
string colName = String.Empty;
try
{
var colNum = Convert.ToInt32(colNumber);
var mod = colNum % 26;
var div = Math.Floor((double)(colNum)/26);
colName = ((div > 0) ? GetColumnLetter((div - 1).ToString()) : String.Empty) + Convert.ToChar(mod + 65);
}
finally
{
colName = colName == String.Empty ? "A" : colName;
}
return colName;
}
该方法将数字视为字符串,而以“0”开头的数字(A = 0)