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));
}
}