x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 697

convert integers columns & rows into CString Excel cell format.

Hi all,
I am searching for an algorithm which converts integers Columns and Rows, in an Excel cell.

E.g. if I have nCol=0, nRow=0 => algo returns CString "A1"
if I have nCol=28, nRow=1  => algo must return "AB2" (2 chars wide for Col description)

Thanks a lot !

Regards,
David.
0
• 2
1 Solution

Commented:
I think that nCol = 28, nRow = 1 should be "AC2".  If that is the case, the following is a C program that has a function to do what you want.

You would have to change the character array stuff to a CString, but the algorithm is the same.  If the column is greater than 25 (since we are using 0 as the base), then there are two letters to describe the column.

#include <stdio.h>

void toExcel(int i, int j, char *s)
{
if ( i > 25 )
{
sprintf(s, "%c%c%1i", 'A' + (i/26) - 1, 'A' + (i%26), j + 1);
}
else
sprintf(s, "%c%1i", 'A' + i, j + 1 );
}

int main(void)
{
int x, y;
char a[5];

x = 0;
y = 0;
toExcel(x, y, (char*)a);
printf("x: %i  y: %i  a: %s\n", x, y, a);

x = 28;
y = 1;
toExcel(x, y, (char*)a);
printf("x: %i  y: %i  a: %s\n", x, y, a);

return 0;
}

0

Author Commented:
Cool code.
Here it is translated in MsVC++ :

void C_Results::ConvCell (long nCol, long nRow, CString& strCell)
{
CString strCol = "";
if (nCol>25)
{      strCol = ((char)('A' + (nCol/26) - 1));
strCol += ((char)('A' + (nCol%26)) );
}
else
strCol = (char)('A' + nCol);

strCell.Format("%s%d", strCol, nRow + 1 );
}

Thanks.
0

Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Featured Post

• 2
Tackle projects and never again get stuck behind a technical roadblock.