Question
Synechron Inc.
US
Last activity: 4 Oct 2018 13:54 EDT
Exporting the Excel sheet data into Table view component
Hi,
I need to export the excel sheet data with multiple columns into Table view component. I am using the Pega-Robotic studio 8.0 the only properties that are related to data-Tableview are ApplyDefaultSort, Filter, RowFilter and sort and method that is relevent to this is Addnew.
Kindly help me to get bunch of records with multiple columns into TableView from ExcelSheet at a time without looping.
Thanks and Best Regards,
Madhukar Ganesh Chatra
**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.
Pegasystems Inc.
US
Madhukar,
You can achieve the same with the use of the ExportData method on the excel connector. The method allows you to specify the start and ending cell range that should be extracted. The returned value is a .NET datatable. So you wouldnt need to push this into a tableview. You can directly push the datatable result into the datagrid component using the datasource property. Below is a screenshot of such steps.
Escobar Rueda
Hi, how are you?
What if I would like to populate a DataGrid with just a few columns and not the entire table? Is it possible?
Thanks in advance
Pegasystems Inc.
US
Most datagrid components allow you to hide columns that you do not want to display. When you attach a data source, the data will be there. You just don't have to display it.
Synechron Inc.
US
Hi Thomz,
Thanks for your quick response.
I want to filter the data extracted from Excel sheet, so I need Table view to do that task.
Kindly go over the above document for detailed explanation.
Thanks and Best Regards,
Madhukar Ganesh Chatra
Pegasystems Inc.
US
Madhukar,
As Jeff mentioned in the other post, our excel connector does not contain a filter method that can be applied to a workbook.
If you wish to accomplish this strictly with the available methods found in Studio, you will need to apply the logic that was outlined in your initial post where you iterate through the excel sheet while updating the table view with the desired data and then exporting the table view data to the grid view.
If your use case allows for you to apply filters to the workbook, you can call an autofilter through referencing the Microsoft.Office.Tools.Excel assembly in a script component and calling the autofilter method. Below is a link that goes into the method use and an example of using such a method in C#.
http://www.c-sharpcorner.com/uploadfile/cubs610/using-C-Sharp-and-the-excel-autofilter/
Once the filter has been applied, you export the data to the gridview in the same manner that was mentioned before.
Synechron Inc.
US
Hi Thomz,
Thanks for the help and It would be great if you can give me a example of using ExcelSheet ExportData to a DataGrid and then iterating over the it and adding it to Tableview component because right now I am facing difficulty in using AddNew method of TableView since it's not taking any data input.
Thanks and Best Regards,
Madhukar Ganesh Chatra