I dont think we have any API that can help you do a kind of auto looping ! How are you looping thru the excel ? Are you loading the excel in the applicaiton first and store it and then looping thru or directly looping thru in the excel ?
I don't know of a good way to do this easily. I might ask why you are storing this data in Excel. You might find it easier to have it in a local data storage or data table (depending on your version of the Pega Platform). Then you could convert the data on the list with a data transform.
Can you please share a screenshot of the autx that you are currently using? Also, what are you referring to when you state "code & name". Are you referencing worksheet code function or an associated name for a worksheet?
Unfortunately, there is not a "smarter" way of traversing an excel sheet. There is the option of using the script component and coding the traversal with C# code which may be a cleaner solution but both functions will be doing the same thing.
If your codes are unique, you can use the Excel connector to export the data to an OpenSpan lookup table. Assuming your codes are in column A and your names are in column B, create a lookup table with 2 columns - Code and Name. Then export the region as shown below:
Once the data is in the lookup table, use the code to lookup the name.
To do this you need an automation to convert a number to a column letter. Here is an automation that does that that I call EL_F_Column_GetLetter. It takes a 1-based number and converts it to an Excel column.
Now you create an automation that uses that to convert a ForLoop index to a column. In this example it will read column A - Z.
Thanks Jeff for the reply,but actually am facing issue when connecting forloop with Excel connector method Getcellvalue,it is not returning any record or value.the loop itself is not running.Could you please show me the example for Excel connector Getcellvalue & setcellvalue method using forloop?
GetCellValue needs a cell as input, for instance B1. In the example above the stringUtils.Concat method concatenates the Column (for example B) with the row (for example 1) to create B1. In the example above EL_F_Column_GetLetter converts a number to a column letter.
Yes the xlsx file is the database here.
Excel also storing values in a table format isn't it... Hence you can use the excel workbook as database and sheets as tables.
Try google like "oledb connection with excel using c#" .. A site named csharpcorner has tutorials wgich will give you more idea..
By default all methods are not displayed in object explorer so selected required component in object explorer and click on icon as shown in the diagram and select all method from the lookup window or search required method and select then OK.