Question
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.
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;
I did not get Excel/_Worksheet in the Pickup dialog, could you tell me how can I get it, please?
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.
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.