Question
How to read non-blank values from a sheet in Excel file upon button click
Hi,
I'm new in Pega and find it hard to create a simple automation that will read the non-blank values in Excel. Saw this post, but guess my design is different:
https://pdn.pega.com/community/product-support/question/pega-robotics-excel-how
My UI is there is a Browse button for the user to locate the excel file. After that, automation will start upon clicking Proceed button which should then read all the non-blank values (rows and columns) from the Excel File specified. The excel file contains multiple sheets so it would be better to define the sheet name somewhere in the script. For testing purposes, can display the row/column value in a message box.
I'm stuck and would appreciate if someone can provide help. Sample code would be highly appreciated.
Thanks in advance
Attached is my current workflow.
It's using ExportData and cells are hard-coded. It runs slow as the number of row increases.
Read about UsedRange property from Microsoft.Office.Interop.Excel._Worksheet which can be useful here.
The sample in the link above uses parameter in entry point. Not sure how to access _Worksheet if this will be done via click event. Any suggestion?