Question
Cognizant Technology Solutions
IN
Last activity: 6 Nov 2018 20:02 EST
Excel Row Count Column Count
Here is what I am trying to get a simple integer output of the used rows and columns in excel sheet. How can this be achieved using Open Span Excel connector?
**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
Pegasystems Inc.
US
Excel has a UsedRange property that returns a list of all the cells ever touched. It isn't super accurate and can be easily fooled. The best way is to use that and then check to make sure that the last row is actually "used (not empty). If it is iterate backwards through the rows until you reach a row that has a value by calling GetCellValue.
Attached is the automation that returns the Used Range (thank you to JeffBadger for this). I have attached the automation, a screenshot showing how to select the input parameter of _Worksheet, and how to call it in your automation.
Cognizant
IN
Thank you very much sasnt for detailed explanation and images.
Tata Consultancy Services
US
I'm able to get the range by following this. Thank you.
The Royal Bank of Scotland Group PLC
IN
Hi, I'm new to Openspan. Is there any C# script to identify the last row in a specified excel sheet. I have tried the below but did not work
Imports : Microsoft.Office.Interop.Excel
Parameters: String sheetname, Workbook excelWkBook
Script
Worksheet sh;
long lastRow;
long fullRow;
sh = excelWkBook.Worksheets.get_Item(sheetname);
fullRow = sh.Rows.Count;
lastRow = sh.Cells[fullRow, 1].End(Microsoft.Office.Interop.Excel.XlDirection.xlUp).Row;
tata
MX
I did not get Excel/_Worksheet in the Pickup dialog, could you tell me how can I get it, please?
Pegasystems Inc.
US
I am not sure what you mean in the Pick Up dialog. I assume you mean the dialog shown in my first screenshot. You need to select the assembly as shown there.