Forum Discussion

BMD's avatar
BMD
Contributor
3 years ago

Converting Number to Matching Character 1=A, 2=B, 3=C....

I have a function that returns the data from a column in an Excel file - now I want to return the data for a row.  Below is the function that returns the column - in this case column = D and starting at row 2 and ending at row 50.

I would like to convert this to return a row but have to arguments be ("C:\\Users\\TEST.xlsx", "STEP", 5, 3, 15) so this will return the data in row 5 starting at column C and ending at O.

I'm not able to find a build in function to convert the alphaNumber to the matching letter in the alphabet.   I could use the arguments ("C:\\Users\\TEST.xlsx", "STEP", 5, "C", "O") but I also don't see a next to move from C to D to E to F and so on.  

Any pointers would be of great help.

Thank you, BMD

 

function RUNgetExcelColumnCSE()
{
Get = getExcelColumnCSE("C:\\Users\\TEST.xlsx", "STEP", "D", 2, 50);
Log.Message("Get = "+(Get));
}
function getExcelColumnCSE(FilePathName, SheetName, sCol, iStart, iEnd)
{
if (iStart < iEnd)
{
sCol = aqString.ToUpper(sCol); /*Convert the column character to UPPER regardless of argument*/
var iR = iStart; /*Set a row counter equal to the starting row*/
var ColumnText = "";
var excelFile = Excel.Open(FilePathName);
var excelSheet = excelFile.SheetByTitle(SheetName);
while (iR < iEnd)
{
var Found = excelSheet.Cell(sCol, iR).Value;
if (Found == undefined)
{ /*Do Nothing*/ }
else
{
if (aqString.GetLength(ColumnText) == 0)
{ ColumnText = (Found); } /*Frist value will not add a , first*/
else
{ ColumnText = (ColumnText)+","+(Found); } /*Adding a , and then the value found to the string*/
}
iR++;
}
if (aqString.GetLength(ColumnText) == 0)
{ return ("Nothing Found To Return"); }
else
{ return (ColumnText); }
}
else
{
Log.Message("The Start row value "+(iStart)+" is grater then the End row value "+(iEnd));
}
}

  • well, you can make an array or a dictionary and work with indexes or keys, kind of like

    alphabet = [null, "A", "B", "C", "D", ... etc]

    and then reference the alphabet array with the index you are getting from your current function:

    from

    ("C:\\Users\\TEST.xlsx", "STEP", 5, 3, 15)

    to

    ("C:\\Users\\TEST.xlsx", "STEP", 5, alphabet[3], alphabet[15])

    resulting in

    ("C:\\Users\\TEST.xlsx", "STEP", 5, "C", "O")

    • BMD's avatar
      BMD
      Contributor

      Why didn't I think of this - Thank You - I think this will work and it's simple.

      THANKS to TCYKPB