Excel Connector
Hi,
I would like to know whether there is a way to get the records of a particular column( depending on the column name like customer ID, place etc rather than A1, D3 etc).This is because additional columns would be inserted into the sheet.
Also if I use ExportData, the cellStart and cellEnd has to be specified. Is there a way to read the entire set of records without specifying the cell name?
Thanks in advance.
Accepted Solution
Pegasystems Inc.
US
ANZ
IN
Hi,
We can create a .net component using excel.introp to read the excel and keep all the values in a table.
Then we can use query with a column name to retrieve the values.
it give us more flexibility and usability then a excel connector.
And with a .net component it will make your code more reusable.
Accepted Solution
Pegasystems Inc.
US
Please see the post below. I discuss how you can get the UsedRange of an Excel sheet. It isn't 100% accurate however as Excel will report cells that are empty, although I believe it will always report 100% of the cells (it just may have extras if those cells were ever used). You could then filter the DataTable object in a script to exclude the empty rows.
https://collaborate.pega.com/question/export-data-method-ms-excel-component
Please see the post below. I discuss how you can get the UsedRange of an Excel sheet. It isn't 100% accurate however as Excel will report cells that are empty, although I believe it will always report 100% of the cells (it just may have extras if those cells were ever used). You could then filter the DataTable object in a script to exclude the empty rows.
https://collaborate.pega.com/question/export-data-method-ms-excel-component