Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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

like image 752
leora Avatar asked Dec 28 '11 18:12

leora


2 Answers

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++;
}
like image 52
Scott Boettger Avatar answered Oct 03 '22 00:10

Scott Boettger


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

like image 34
leora Avatar answered Oct 03 '22 00:10

leora