Monday 16 December 2013

Convert row, column to Excel range in Axapta

Convert row, column to Excel range in Axapta 
This method is show how to convert row and column to Excel range:
rowfrom = 1,
colfrom = 1,
rowto = 5,
colto = 5;
>> A1:E5

public str convertToRange(int rowfrom, int colfrom, int rowto = 0,int colto = 0)
{
    str     strrange, srange1;

    ;
    if(rowfrom <= 0 || colfrom <= 0)
    {
        throw warning("We don`t have row <=0 or column <=0 in Excel");
    }
    if(rowto != 0 && colto != 0)
    {
        if(rowto <= 0 || colto <= 0)
        {
            throw warning("We don`t have row <=0 or column <=0 in Excel");
        }
        if(colto <= 26)
        {
            strrange = num2char(colto+64) + int2str(rowto);
        }
        else
        {
            strrange = num2char((colto-1)/26+64)+num2char(((colto-1)mod 26)+65) + int2str(rowto);
        }
    }
    if(colfrom <= 26)
    {
        srange1 = num2char(colfrom+64) + int2str(rowfrom);
    }
    else
    {
        srange1=num2char((colfrom-1)/26+64)+num2char(((colfrom-1)mod 26)+65) + int2str(rowfrom);
    }
    if(rowto != 0 && colto != 0)
    {
        return srange1 + ":" + strrange;
    }
    else return srange1;
}

No comments:

Post a Comment