I am trying to remove all extra blank rows and columns from an excel file using Interop Library.
I followed this question Fastest method to remove Empty rows and Columns From Excel Files using Interop and i find it helpful.
But i have excel files that contains a small set of data but a lot of empty rows and columns (from the last non empty row (or column) to the end of the worksheet)
I tried looping over Rows and Columns but the loop is taking hours.
I am trying to get the last non-empty row and column index so i can delete the whole empty range in one line
XlWks.Range("...").EntireRow.Delete(xlShiftUp)
Note: i am trying to get the last row containing data to remove all extra blanks (after this row , or column)
Any suggestions?
Note: The code must be compatible with SSIS Script Task environment
Best Answer
Update 1
If your goal is to import the excel data using c#, assuming that you have identified the the highest used index in your worksheet (in the image you posted it is Col = 10 , Row = 16), you can convert the maximum used indexes to letter so it will be
J16
and select only the used range using andOLEDBCommand
Else, i don't think it is easy to find a faster method.
You can refer to these article to convert indexes into alphabet and to connect to excel using OLEDB:
Initial Answer
As you said you started from the following question:
And you are trying to "get the last row containing data to remove all extra blanks (after this row , or column)"
So assuming that you are working with the accept answer (provided by @JohnG), so you can add some line of code to get the last used row and column
Empty Rows are stored in a list of integer
rowsToDelete
You can use the following code to get the last non empty rows with an index smaller than the last empty row
And if
NonEmptyRows.Max() < rowsToDelete.Max()
the last non-empty row isNonEmptyRows.Max()
Else it isworksheet.Rows.Count
and there is no empty rows after the last used one.The same thing can be done to get the last non empty column
The code is Edited in
DeleteCols
andDeleteRows
functions: