Wednesday, July 11, 2012

Getting ColumnName for excel from ColumnIndex


I was working on creating excel at runtime and come across a very intersting point where I have find the columnName of excel based on ColumnIndex like

if ColumnIndex is 1 it should give me A and if It is 26 it should give Z while for 27 it should return AA like that. And finally I created a function which will give you the correct columnName based on passed ColumnIndex which not zero based. The column should start from 1.

 private static string GetExcelColumnName(int columnIndex, int rowIndex)
        {
            char[] alpha = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".ToCharArray();
            int no = (columnIndex - 1) / 26;
            int nextColumn = (columnIndex - 1) % 26;
            char resepectiveCharacter = alpha[nextColumn];
            string columnName = string.Empty;
            for (int i = 0; i < no + 1; i++)
            {
                columnName = string.Format("{0}{1}", resepectiveCharacter.ToString(), columnName);
            }
            return string.Format("{0}{1}", columnName, rowIndex);
        }

Let me know if it works for you.

Cheers!
Vinod

No comments:

Post a Comment

Send us your comment related to the topic mentioned on the blog