Question
Synechron Technologies Pvt. Ltd.
IN
Last activity: 16 Oct 2018 12:03 EDT
Loop through Excel data to fill forms on web page and create records in database - Openspan - Pega Robotic Automation
I have one excel(xlsx) with some data with column headers. I want to
Loop through the excel data -> take a row at a time -> fill web form for each row -> performClick on submit button to save data on website.
I used MicrosoftExcel connector to open the file. But I am facing some problems -
1. ExportData method take fromCell to toCell to export the data in datatable. How can I get the cell range as there may be any number of rows?
2. How can I loop through the DataTable returned from ExportData method? Tried forLoop, but not getting fit with the DataTable returned by ExportData function.
3. While looping through, how can I get the individual column values so that I can set values in web form fields one by one?
I would be thankful if anyone can help.
Thanks,
Pravin
**Moderation Team has archived post**
This post has been archived for educational purposes. Contents and links will no longer be updated. If you have the same/similar question, please write a new post.
Accepted Solution
Synechron Technologies Pvt. Ltd.
IN
Found the solution.
Version of Microsoft.Office.Interop.Excel.dll in install folder is
Version of Microsoft.Office.Interop.Excel.dll in C:\Program Files (x86)\Microsoft Visual Studio 12.0\Visual Studio Tools for Office\PIA\Office15 is
I copied dll from C:\Program Files (x86)\Microsoft Visual Studio 12.0\Visual Studio Tools for Office\PIA\Office15 to C:\Program Files (x86)\OpenSpan\OpenSpan Studio for Microsoft Visual Studio 2015, and then Pick Type window showing 'Office' in tree.
Pegasystems Inc.
US
Take a look at my post Excel How To - it will show you how to export data. Once you have that datatable you just need to use proxies to walk through the hierarchy of the datatable. Here is an example doing that - adapt to your own needs.
Synechron Technologies Pvt. Ltd.
IN
Thank you for quick response!! Your post is really great and helpful!!
I was trying to add automation EL_F_Cast_ToWorkbook but I can't see Microsoft.Office.Interop.Excel._Workbook, am I missing any assembly reference?
Accenture Solutions Pvt Ltd
IN
Hi Jeff,
For ExportData method here the CellStart & CellEnd is not specified, how it will recognize or detect the column & cell range automatically?
Regards,
Pravallika.
Pegasystems Inc.
US
This picture was for demo purposes on how to take the output of the Export command and loop through the datatable. You will need to properly fill in those values to export the data.
L & T Infotech
IN
Hi jeffbadger,
I am new to openspan. I am not able to find the GetItem(columnName) in datarowcollectionproxy on methods. Found one method with GetItem(Index). Could you please provide the snapshot of Getitem with columnname as input?
Regards,
Genet J
Clover Infotech
IN
Hi jeffbadger,
I want to display data from excel to windows form, i have used above flow that you describe but i do not understand how to display that data into the windows form.
Thanks,
Deepak
Infosys
IN
Hi Jeff,
Thank you for the automation diagram. I was able to fetch the data from the excel and display it on message box cell by cell. Instead of message box, I'm trying to input the values to form having text fields and drop-downs but the complete for loop is executing only in 1 text field and does not proceed to the next text field. Appreciate, If you could you help me, as to how, this next item can be handled? I'm looking something like below : parameterizing the text field as "getItem(columnIndex)" Please find the screenshot of my automation diagram.
Thanks,
Karthik
Coforge BPM
US
Jeff,
I was going through your above post in order to get cell value of each cell on a table. I did exactly the same as your example shows just to test to get values but at the last block (dataRow, GetItem method), i am not getting columnName as a parameter instead i am getting columnIndex. I checked several times, everything looks good. Am i missing or doing something wrong?
I have attached the error i am getting and also the automation.
Dipesh
Pegasystems Inc.
US
Did you enabled Office support when you installed Pega Robotics Studio? Additionally, you must have Office installed on the machine. You can enable Office support after install by changing a value in the StudioConfig.xml file. This file is found in the %appdata%\Roaming\OpenSpan folder. Change the Office key to match your installed version of Office.
Synechron Technologies Pvt. Ltd.
IN
Yes, I had selected office2016 support while installation. Also office2016 is installed on my machine, so value of OfficeVersion entry in StudioConfig.xml is matching to 2016. Am I missing anything?
installed word2016 from the below subscription.
Pegasystems Inc.
US
Check your Pega Robotics Studio install folder. It should contain these files:
If they are not present, you should copy the contents of the Office2016 folder (it is in the Pega Robotics Studio folder) to the install folder.
Accepted Solution
Synechron Technologies Pvt. Ltd.
IN
Found the solution.
Version of Microsoft.Office.Interop.Excel.dll in install folder is
Version of Microsoft.Office.Interop.Excel.dll in C:\Program Files (x86)\Microsoft Visual Studio 12.0\Visual Studio Tools for Office\PIA\Office15 is
I copied dll from C:\Program Files (x86)\Microsoft Visual Studio 12.0\Visual Studio Tools for Office\PIA\Office15 to C:\Program Files (x86)\OpenSpan\OpenSpan Studio for Microsoft Visual Studio 2015, and then Pick Type window showing 'Office' in tree.
Patil
IN
Hi Jeff,
Im fetching data from excel and displaying it on the input text fields of webpage form cell by cell.
The for loop is executing only in 1st text field and does not proceed to the next text field.
Please find the screenshot of my automation diagram and web form.
Thanks,
Swati P