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