最快的函数生成在C＃中的Excel列字母 [英] Fastest function to generate Excel column letters in C#

问题描述

什么是最快的C＃函数，它和int和返回包含在Excel函数中使用的字母或字母串？例如，1返回A，26返回Z，27返回AA，等等。

这就是所谓的数万次，并正在采取的产生大S preadsheet有很多公式需要25％的时间。

 公共字符串信（INT intCol）{    INT intFirstLetter =（（intCol）/ 676）+ 64;
    INT intSecondLetter =（（intCol％676）/ 26）+ 64;
    INT intThirdLetter =（intCol 26％）+ 65;    焦炭FirstLetter =（intFirstLetter＆GT; 64）？ （char）的intFirstLetter：'';
    焦炭SecondLetter =（intSecondLetter＆GT; 64）？ （char）的intSecondLetter：'';
    焦炭ThirdLetter =（char）的intThirdLetter;    返回string.Concat（FirstLetter，SecondLetter，ThirdLetter）.Trim（）;
}
 

解决方案

我目前使用这个，用Excel 2007

 公共静态字符串ExcelColumnFromNumber（INT列）
        {
            字符串columnString =;
            小数columnNumber =列;
            而（columnNumber大于0）
            {
                小数currentLetterNumber =（columnNumber  -  1）％26;
                焦炭currentLetter =（char）的（currentLetterNumber + 65）;
                columnString = currentLetter + columnString;
                columnNumber =（columnNumber  - （currentLetterNumber + 1））/ 26;
            }
            返回columnString;
        }
 

和

 公共静态INT NumberFromExcelColumn（字符串列）
        {
            INT retVal的= 0;
            字符串COL = column.ToUpper（）;
            对于（INT ICHAR = col.Length  -  1; ICHAR＆GT; = 0; iChar--）
            {
                焦炭colPiece = COL [ICHAR]
                INT colNum = colPiece  -  64;
                retVal的= retVal的+ colNum *（int）的Math.Pow（26，col.Length  - （ICHAR + 1））;
            }
            返回retVal的;
        }
 

正如在其他职位所提到的，其结果可以被缓存。

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.

This is called tens of thousands of times and is taking 25% of the time needed to generate a large spreadsheet with many formulas.

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

解决方案

I currently use this, with Excel 2007

public static string ExcelColumnFromNumber(int column)
        {
            string columnString = "";
            decimal columnNumber = column;
            while (columnNumber > 0)
            {
                decimal currentLetterNumber = (columnNumber - 1) % 26;
                char currentLetter = (char)(currentLetterNumber + 65);
                columnString = currentLetter + columnString;
                columnNumber = (columnNumber - (currentLetterNumber + 1)) / 26;
            }
            return columnString;
        }

and

public static int NumberFromExcelColumn(string column)
        {
            int retVal = 0;
            string col = column.ToUpper();
            for (int iChar = col.Length - 1; iChar >= 0; iChar--)
            {
                char colPiece = col[iChar];
                int colNum = colPiece - 64;
                retVal = retVal + colNum * (int)Math.Pow(26, col.Length - (iChar + 1));
            }
            return retVal;
        }

As mentioned in other posts, the results can be cached.

这篇关于最快的函数生成在C＃中的Excel列字母的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！