如何在c#中将数值转换为Excel列名,而不使用直接从Excel中获取值的自动化。

Excel 2007的范围可能是1到16384,这是它支持的列数。结果值应以excel列名的形式出现,例如A、AA、AAA等。


当前回答

JavaScript解决方案

/**
 * Calculate the column letter abbreviation from a 1 based index
 * @param {Number} value
 * @returns {string}
 */
getColumnFromIndex = function (value) {
    var base = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'.split('');
    var remainder, result = "";
    do {
        remainder = value % 26;
        result = base[(remainder || 26) - 1] + result;
        value = Math.floor(value / 26);
    } while (value > 0);
    return result;
};

其他回答

另一种VBA方式

Public Function GetColumnName(TargetCell As Range) As String
    GetColumnName = Split(CStr(TargetCell.Cells(1, 1).Address), "$")(1)
End Function

有点晚了,但这里是我使用的代码(c#):

private static readonly string _Alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
public static int ColumnNameParse(string value)
{
    // assumes value.Length is [1,3]
    // assumes value is uppercase
    var digits = value.PadLeft(3).Select(x => _Alphabet.IndexOf(x));
    return digits.Aggregate(0, (current, index) => (current * 26) + (index + 1));
}

在perl中,对于1 (A), 27 (AA)等输入。

sub excel_colname {
  my ($idx) = @_;       # one-based column number
  --$idx;               # zero-based column index
  my $name = "";
  while ($idx >= 0) {
    $name .= chr(ord("A") + ($idx % 26));
    $idx   = int($idx / 26) - 1;
  }
  return scalar reverse $name;
}

T-sql (sql server 18)

第一页的解决方案副本

CREATE FUNCTION dbo.getExcelColumnNameByOrdinal(@RowNum int)  
RETURNS varchar(5)   
AS   
BEGIN  
    DECLARE @dividend int = @RowNum;
    DECLARE @columnName varchar(max) = '';
    DECLARE @modulo int;

    WHILE (@dividend > 0)
    BEGIN  
        SELECT @modulo = ((@dividend - 1) % 26);
        SELECT @columnName = CHAR((65 + @modulo)) + @columnName;
        SELECT @dividend = CAST(((@dividend - @modulo) / 26) as int);
    END
    RETURN 
       @columnName;

END;
 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();
    }