The {Code} Cookbook

C# Excel Column Number to Name and Vice Versa

By gilbitron in C# on 27/04/2009 at 07:49

Views: 1312
Tagged: c#, excel, conversion
URL: http://www.mail-archive.com/poi-user@jakarta.apache.org/msg08811.html

Starter:

Convert a column number to a column name and vice versa in an Excel spreadsheet.

Main Course:

private int colNameToNum(final String colName)
{
        int result  = 0;
        String  lcColName   = colName.toLowerCase();
        for (int ctr = 0; ctr < lcColName.length(); ++ ctr)
            result  = (result * 26) + (lcColName.charAt(ctr) - 'a' + 1);
        --result;
        return (result);
}
 
private String colNumToName(int colNum)
{
        StringBuffer    sb  = new StringBuffer();
        int cycleNum    = colNum / 26;
        int withinNum   = colNum - (cycleNum * 26);
        if (cycleNum > 0)
            sb.append((char) ((cycleNum - 1) + 'a'));
        sb.append((char) (withinNum + 'a'));
        return (sb.toString());
} 
 

Expand Report Code | Install Coda Clip

Please log in to vote.

Compliments to the Chef

1. Comment by Greg Aluise on 30/09/2009 at 09:16

   It appears the colNumToName function only works for 2 character column names i.e. (out to ZZ). Also, the colNameToNum was one shy so I removed the --result; line.
   
   The code below has been tested with Excel 2007.
   
   Here is a corrected version:
   
   private int colNameToNum(String colName)
   {
   int result = 0;
   String lcColName = colName.ToUpper();
   for (int ctr = 0; ctr < lcColName.Length; ++ctr)
   result = (result * 26) + (lcColName.ToCharArray()[ctr] - 'A' + 1);
   return (result);
   }
   
   private String colNumToName(int colNum)
   {
   StringBuilder sb = new StringBuilder();
   int cycle = colNum;
   
   while (cycle > 0)
   {
   int letter = cycle % 26;
   sb.Insert(0, (char)((letter - 1) + 'A'));
   cycle /= 26;
   }
   return sb.ToString();
   }

How was your Meal?