what is the best way to loop through alphabet in C# to output to Excel?

Question

I have code that is exporting to excel and i have an array of columns . .

  var colheaders = new string[] {"Name", "Age", "Total", "Date"}

right now i have code that looks like this to setup the headers

            excelExport.SetCell("A", 1, "Name");
            excelExport.SetCell("B", 1, "Age");
            excelExport.SetCell("C", 1, "Total");
            excelExport.SetCell("D", 1, "Date");

the issue is that if i have 50 columns and i want to add one at the beginnging, i have to go and updated the letter in each column "A", "B", "C", etc . .

since i have an array already of string headers i would like something like this:

  foreach (string colheader in colheaders)
  {
     excelExport.SetCell("A", 1, colheader);
  }

but i need to dynamically set the letter "A" in this case. Something like this:

  int i = 0;
  foreach (string colheader in colheaders)
  {
     excelExport.SetCell(GetCol(i), 1, colheader);
     i++;
  }

NOTE:

Also, after Z, i need to go to AA, then AB, then AC, etc . . to match the Excel columns so the logic would have to go beyond 26 columns

take a look at here http://stackoverflow.com/questions/1011732/iterating-through-the-alphabet-c-sharp-a-caz it might be help you ... — Glory Raj, Dec 28 '11 at 18:36
http://stackoverflow.com/questions/181596/how-to-convert-a-column-number-eg-127-into-an-excel-column-eg-aa — Jimmy, Dec 28 '11 at 18:53
I don't know what that `SetCell` function is doing, but one little-known fact about Excel is that you can use numbers to refer to columns rather than letters: `Cells(1, "AA")` is the same as `Cells(1, 27)`. — phoog, Dec 28 '11 at 18:57
I found a working solution [here][1]: [1]: http://stackoverflow.com/questions/837155/fastest-function-to-generate-excel-column-letters-in-c-sharp — leora, Dec 28 '11 at 20:54
@leora I suggest you close your own question as a duplicate (rather than have other people flag it for closure). — Matthew Strawbridge, Dec 28 '11 at 22:53
@Matthew Strawbridge - I did but i think it still needs a few more close votes — leora, Dec 28 '11 at 23:21

score 5 · Answer 1 · answered Dec 28 '11 at 19:09

This isn't the most elegant solution but it does yield the result you are looking for.

const int maxAlpha = 26;
int charNum;
var timesMaxed = 0;
var counter = 0;
var asciiStartPoint = 65;

foreach (string colheader in colheaders)
{
    var result = String.Empty;

    if (counter == maxAlpha)
    {
        timesMaxed++;
        counter = 0;
    }

    if (timesMaxed > 0)
    {
        charNum = asciiStartPoint + (timesMaxed - 1);
        result += ((char)charNum).ToString();
    }

    charNum = asciiStartPoint + counter;
    result += ((char)charNum).ToString();

    excelExport.SetCell(result, 1, colheader);
    counter++;
}

score 4 · Accepted Answer · edited May 23 '17 at 12:20

4

I was able to find another question on stackoverflow that had a working solution:

Here is the question: Fastest function to generate Excel column letters in C#

and the answer is the: ExcelColumnFromNumber() method

edited May 23 '17 at 12:20

Community

1
1

answered Dec 28 '11 at 21:14

leora

188,729
360
878
1,366

Reza ArabQaeni · Answer 3 · 2011-12-28T20:17:35.643

1

    private List<string> GetCol()
    {
        var x = Enumerable.Range(65, 26).Select(p => (char)p).ToList();
        List<string> result = new List<string>();
        foreach (var first in x)
        {
            result.Add(first.ToString());
        }

        foreach (var first in x)
        {
            foreach (var second in x)
            {
                result.Add(first.ToString() + second.ToString());
            }
        }
        return result;
    }

edited Dec 28 '11 at 20:17

answered Dec 28 '11 at 18:49

Reza ArabQaeni

4,848
27
46

Clever! But there are 26 letters in the alphabet, not 31. – phoog Dec 28 '11 at 19:03
Thanks, I confused it with persian alphabet. – Reza ArabQaeni Dec 28 '11 at 20:17
Hm, perhaps Excel would use the Persian alphabet for column headings when running under Persian regional settings? If so, then the correct answer would be much more complicated! – phoog Dec 28 '11 at 20:22
@RedHat - I don't see you passing in an argument anywhere . . – leora Dec 28 '11 at 20:31
@leora- This is because of my poor English. – Reza ArabQaeni Dec 28 '11 at 20:39

score 0 · Answer 4 · answered Dec 28 '11 at 19:00

Depending on the definition of SetCell, you may be able to do this:

int i = 0; 
foreach (string colheader in colheaders) 
{ 
    excelExport.SetCell(++i, 1, colheader);
}

This would work, for example, if SetCell were defined thus:

void SetCell(object col, object row, object value)
{
    _excelApp.Cells(row, col).set_Value(value);
}

what is the best way to loop through alphabet in C# to output to Excel?

NOTE:

4 Answers4