Fastest Method to Remove Empty Rows and Columns from Excel Files Using Interop

  • Home
  • Languages
  • Csharp
  • Fastest Method to Remove Empty Rows and Columns from Excel Files Using Interop

Fastest method to remove Empty rows and Columns From Excel Files using Interop

I found that looping through the excel worksheet can take some time if the worksheet is large. So my solution tried to avoid any looping in the worksheet. To avoid looping through the worksheet, I made a 2 dimensional object array from the cells returned from usedRange with:

Excel.Range targetCells = worksheet.UsedRange;
object[,] allValues = (object[,])targetCells.Cells.Value;

This is the array I loop through to get the indexes of the empty rows and columns. I make 2 int lists, one keeps the row indexes to delete the other keeps the column indexes to delete.

List<int> emptyRows = GetEmptyRows(allValues, totalRows, totalCols);
List<int> emptyCols = GetEmptyCols(allValues, totalRows, totalCols);

These lists will be sorted from high to low to simplify deleting rows from the bottom up and deleting columns from right to left. Then simply loop through each list and delete the appropriate row/col. 

DeleteRows(emptyRows, worksheet);
DeleteCols(emptyCols, worksheet);

Finally after all the empty rows and columns have been deleted, I SaveAs the file to a new file name.

Hope this helps.

EDIT:

Addressed the UsedRange issue such that if there are empty rows at the top of the worksheet, those rows will now be removed. Also this will remove any empty columns to the left of the starting data. This allows for the indexing to work properly even if there are empty rows or columns before the data starts.
This was accomplished by taking the address of the first cell in UsedRange this will be an address of the form “$A$1:$D$4”. This will allow the use of an offset if the empty rows at the top and empty columns to the left are to remain and not be deleted. In this case I am simply deleting them. To get the number of rows to delete from the top can be calculated by the first “$A$4” address where the “4” is the row that the first data appears. So we need to delete the top 3 rows. The Column address is of the form “A”, “AB” or even “AAD” this required some translation and thanks to How to convert a column number (eg. 127) into an excel column (eg. AA) I was able to determine how many columns on the left need to be deleted.

class Program {
  static void Main(string[] args) {
    Excel.Application excel = new Excel.Application();
    string originalPath = @"H:\ExcelTestFolder\Book1_Test.xls";
    Excel.Workbook workbook = excel.Workbooks.Open(originalPath);
    Excel.Worksheet worksheet = workbook.Worksheets["Sheet1"];
    Excel.Range usedRange = worksheet.UsedRange;

    RemoveEmptyTopRowsAndLeftCols(worksheet, usedRange);

    DeleteEmptyRowsCols(worksheet);

    string newPath = @"H:\ExcelTestFolder\Book1_Test_Removed.xls";
    workbook.SaveAs(newPath, Excel.XlSaveAsAccessMode.xlNoChange);

    workbook.Close();
    excel.Quit();
    System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
    System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
    Console.WriteLine("Finished removing empty rows and columns - Press any key to exit");
    Console.ReadKey();
  }

  private static void DeleteEmptyRowsCols(Excel.Worksheet worksheet) {
    Excel.Range targetCells = worksheet.UsedRange;
    object[,] allValues = (object[,])targetCells.Cells.Value;
    int totalRows = targetCells.Rows.Count;
    int totalCols = targetCells.Columns.Count;

    List<int> emptyRows = GetEmptyRows(allValues, totalRows, totalCols);
    List<int> emptyCols = GetEmptyCols(allValues, totalRows, totalCols);

    // now we have a list of the empty rows and columns we need to delete
    DeleteRows(emptyRows, worksheet);
    DeleteCols(emptyCols, worksheet);
  }

  private static void DeleteRows(List<int> rowsToDelete, Excel.Worksheet worksheet) {
    // the rows are sorted high to low - so index's wont shift
    foreach (int rowIndex in rowsToDelete) {
      worksheet.Rows[rowIndex].Delete();
    }
  }

  private static void DeleteCols(List<int> colsToDelete, Excel.Worksheet worksheet) {
    // the cols are sorted high to low - so index's wont shift
    foreach (int colIndex in colsToDelete) {
      worksheet.Columns[colIndex].Delete();
    }
  }

  private static List<int> GetEmptyRows(object[,] allValues, int totalRows, int totalCols) {
    List<int> emptyRows = new List<int>();

    for (int i = 1; i < totalRows; i++) {
      if (IsRowEmpty(allValues, i, totalCols)) {
        emptyRows.Add(i);
      }
    }
    // sort the list from high to low
    return emptyRows.OrderByDescending(x => x).ToList();
  }

  private static List<int> GetEmptyCols(object[,] allValues, int totalRows, int totalCols) {
    List<int> emptyCols = new List<int>();

    for (int i = 1; i < totalCols; i++) {
      if (IsColumnEmpty(allValues, i, totalRows)) {
        emptyCols.Add(i);
      }
    }
    // sort the list from high to low
    return emptyCols.OrderByDescending(x => x).ToList();
  }

  private static bool IsColumnEmpty(object[,] allValues, int colIndex, int totalRows) {
    for (int i = 1; i < totalRows; i++) {
      if (allValues[i, colIndex] != null) {
        return false;
      }
    }
    return true;
  }

  private static bool IsRowEmpty(object[,] allValues, int rowIndex, int totalCols) {
    for (int i = 1; i < totalCols; i++) {
      if (allValues[rowIndex, i] != null) {
        return false;
      }
    }
    return true;
  }

  private static void RemoveEmptyTopRowsAndLeftCols(Excel.Worksheet worksheet, Excel.Range usedRange) {
    string addressString = usedRange.Address.ToString();
    int rowsToDelete = GetNumberOfTopRowsToDelete(addressString);
    DeleteTopEmptyRows(worksheet, rowsToDelete);
    int colsToDelete = GetNumberOfLeftColsToDelte(addressString);
    DeleteLeftEmptyColumns(worksheet, colsToDelete);
  }

  private static void DeleteTopEmptyRows(Excel.Worksheet worksheet, int startRow) {
    for (int i = 0; i < startRow - 1; i++) {
      worksheet.Rows[1].Delete();
    }
  }

  private static void DeleteLeftEmptyColumns(Excel.Worksheet worksheet, int colCount) {
    for (int i = 0; i < colCount - 1; i++) {
      worksheet.Columns[1].Delete();
    }
  }

  private static int GetNumberOfTopRowsToDelete(string address) {
    string[] splitArray = address.Split(':');
    string firstIndex = splitArray[0];
    splitArray = firstIndex.Split('$');
    string value = splitArray[2];
    int returnValue = -1;
    if ((int.TryParse(value, out returnValue)) && (returnValue >= 0))
      return returnValue;
    return returnValue;
  }

  private static int GetNumberOfLeftColsToDelte(string address) {
    string[] splitArray = address.Split(':');
    string firstindex = splitArray[0];
    splitArray = firstindex.Split('$');
    string value = splitArray[1];
    return ParseColHeaderToIndex(value);
  }

  private static int ParseColHeaderToIndex(string colAdress) {
    int[] digits = new int[colAdress.Length];
    for (int i = 0; i < colAdress.Length; ++i) {
      digits[i] = Convert.ToInt32(colAdress[i]) - 64;
    }
    int mul = 1; int res = 0;
    for (int pos = digits.Length - 1; pos >= 0; --pos) {
      res += digits[pos] * mul;
      mul *= 26;
    }
    return res;
  }
}

EDIT 2: For testing I made a method that loops thru the the worksheet and compared it to my code that loops thru an object array. It shows a significant difference.

Sample Image

Method to Loop thru the worksheet and delete empty rows and columns.

enum RowOrCol { Row, Column };
private static void ConventionalRemoveEmptyRowsCols(Excel.Worksheet worksheet) {
  Excel.Range usedRange = worksheet.UsedRange;
  int totalRows = usedRange.Rows.Count;
  int totalCols = usedRange.Columns.Count;

  RemoveEmpty(usedRange, RowOrCol.Row);
  RemoveEmpty(usedRange, RowOrCol.Column);
}

private static void RemoveEmpty(Excel.Range usedRange, RowOrCol rowOrCol) {
  int count;
  Excel.Range curRange;
  if (rowOrCol == RowOrCol.Column)
    count = usedRange.Columns.Count;
  else
    count = usedRange.Rows.Count;

  for (int i = count; i > 0; i--) {
    bool isEmpty = true;
    if (rowOrCol == RowOrCol.Column)
      curRange = usedRange.Columns[i];
    else
      curRange = usedRange.Rows[i];

    foreach (Excel.Range cell in curRange.Cells) {
      if (cell.Value != null) {
        isEmpty = false;
        break; // we can exit this loop since the range is not empty
      }
      else {
        // Cell value is null contiue checking
      }
    } // end loop thru each cell in this range (row or column)

    if (isEmpty) {
      curRange.Delete();
    }
  }
}

Then a Main for testing/timing the two methods.

enum RowOrCol { Row, Column };

static void Main(string[] args)
{
  Excel.Application excel = new Excel.Application();
  string originalPath = @"H:\ExcelTestFolder\Book1_Test.xls";
  Excel.Workbook workbook = excel.Workbooks.Open(originalPath);
  Excel.Worksheet worksheet = workbook.Worksheets["Sheet1"];
  Excel.Range usedRange = worksheet.UsedRange;

  // Start test for looping thru each excel worksheet
  Stopwatch sw = new Stopwatch();
  Console.WriteLine("Start stopwatch to loop thru WORKSHEET...");
  sw.Start();
  ConventionalRemoveEmptyRowsCols(worksheet);
  sw.Stop();
  Console.WriteLine("It took a total of: " + sw.Elapsed.Milliseconds + " Miliseconds to remove empty rows and columns...");

  string newPath = @"H:\ExcelTestFolder\Book1_Test_RemovedLoopThruWorksheet.xls";
  workbook.SaveAs(newPath, Excel.XlSaveAsAccessMode.xlNoChange);
  workbook.Close();
  Console.WriteLine("");

  // Start test for looping thru object array
  workbook = excel.Workbooks.Open(originalPath);
  worksheet = workbook.Worksheets["Sheet1"];
  usedRange = worksheet.UsedRange;
  Console.WriteLine("Start stopwatch to loop thru object array...");
  sw = new Stopwatch();
  sw.Start();
  DeleteEmptyRowsCols(worksheet);
  sw.Stop();

  // display results from second test
  Console.WriteLine("It took a total of: " + sw.Elapsed.Milliseconds + " Miliseconds to remove empty rows and columns...");
  string newPath2 = @"H:\ExcelTestFolder\Book1_Test_RemovedLoopThruArray.xls";
  workbook.SaveAs(newPath2, Excel.XlSaveAsAccessMode.xlNoChange);
  workbook.Close();
  excel.Quit();
  System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
  System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
  Console.WriteLine("");
  Console.WriteLine("Finished testing methods - Press any key to exit");
  Console.ReadKey();
}

EDIT 3 As per OP request...
I updated and changed the code to match the OP code. With this I found some interesting results. See below.

I changed the code to match the functions you are using ie… EntireRow and CountA. The code below I found that it preforms terribly. Running some tests I found the code below was in the 800+ milliseconds execution time. However one subtle change made a huge difference.

On the line:

while (rowIndex <= worksheet.UsedRange.Rows.Count)

This is slowing things down a lot. If you create a range variable for UsedRang and not keep regrabbibg it with each iteration of the while loop will make a huge difference. So… when I change the while loop to…

Excel.Range usedRange = worksheet.UsedRange;
int rowIndex = 1;

while (rowIndex <= usedRange.Rows.Count)
and
while (colIndex <= usedRange.Columns.Count)

This performed very close to my object array solution. I did not post the results, as you can use the code below and change the while loop to grab the UsedRange with each iteration or use the variable usedRange to test this.

private static void RemoveEmptyRowsCols3(Excel.Worksheet worksheet) {
  //Excel.Range usedRange = worksheet.UsedRange;     // <- using this variable makes the while loop much faster 
  int rowIndex = 1;

  // delete empty rows
  //while (rowIndex <= usedRange.Rows.Count)     // <- changing this one line makes a huge difference - not grabbibg the UsedRange with each iteration...
  while (rowIndex <= worksheet.UsedRange.Rows.Count) {
    if (excel.WorksheetFunction.CountA(worksheet.Cells[rowIndex, 1].EntireRow) == 0) {
      worksheet.Cells[rowIndex, 1].EntireRow.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
    }
    else {
      rowIndex++;
    }
  }

  // delete empty columns
  int colIndex = 1;
  // while (colIndex <= usedRange.Columns.Count) // <- change here also

  while (colIndex <= worksheet.UsedRange.Columns.Count) {
    if (excel.WorksheetFunction.CountA(worksheet.Cells[1, colIndex].EntireColumn) == 0) {
      worksheet.Cells[1, colIndex].EntireColumn.Delete(Excel.XlDeleteShiftDirection.xlShiftToLeft);
    }
    else {
      colIndex++;
    }
  }
}

UPDATE by @Hadi

You can alter DeleteCols and DeleteRows function to get better performance if excel contains extra blank rows and columns after the last used ones:

private static void DeleteRows(List<int> rowsToDelete, Microsoft.Office.Interop.Excel.Worksheet worksheet)
{
    // the rows are sorted high to low - so index's wont shift

    List<int> NonEmptyRows = Enumerable.Range(1, rowsToDelete.Max()).ToList().Except(rowsToDelete).ToList();

    if (NonEmptyRows.Max() < rowsToDelete.Max())
    {

        // there are empty rows after the last non empty row

        Microsoft.Office.Interop.Excel.Range cell1 = worksheet.Cells[NonEmptyRows.Max() + 1,1];
        Microsoft.Office.Interop.Excel.Range cell2 = worksheet.Cells[rowsToDelete.Max(), 1];

        //Delete all empty rows after the last used row
        worksheet.Range[cell1, cell2].EntireRow.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp);

    }    //else last non empty row = worksheet.Rows.Count

    foreach (int rowIndex in rowsToDelete.Where(x => x < NonEmptyRows.Max()))
    {
        worksheet.Rows[rowIndex].Delete();
    }
}

private static void DeleteCols(List<int> colsToDelete, Microsoft.Office.Interop.Excel.Worksheet worksheet)
{
    // the cols are sorted high to low - so index's wont shift

    //Get non Empty Cols
    List<int> NonEmptyCols = Enumerable.Range(1, colsToDelete.Max()).ToList().Except(colsToDelete).ToList();

    if (NonEmptyCols.Max() < colsToDelete.Max())
    {

        // there are empty rows after the last non empty row

        Microsoft.Office.Interop.Excel.Range cell1 = worksheet.Cells[1,NonEmptyCols.Max() + 1];
        Microsoft.Office.Interop.Excel.Range cell2 = worksheet.Cells[1,NonEmptyCols.Max()];

        //Delete all empty rows after the last used row
        worksheet.Range[cell1, cell2].EntireColumn.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftToLeft);

    }            //else last non empty column = worksheet.Columns.Count

    foreach (int colIndex in colsToDelete.Where(x => x < NonEmptyCols.Max()))
    {
        worksheet.Columns[colIndex].Delete();
    }
}

check my answer at Get Last non empty column and row index from excel using Interop

Microsoft Office Interop: Detemine empty columns in excel

Something like:

public bool CheckIfEmptyCell(xlsSheet, row, column) {
    var cell = (Excel.Range)xlsSheet.Cells[row, column]);
    return (cell.Value2 == null || String.IsNullOrEmpty(cell.Value2.ToString())
}

....

var isEmpty = CheckIfEmptyCell(xlsSheet, 5, "D"); // D5 is empty?

Get Last non empty column and row index from excel using Interop


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 and OLEDBCommand

SELECT * FROM [Sheet1$A1:J16]

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:

  • How to convert a column number (eg. 127) into an excel column (eg. AA)
  • Allowing VB.NET app to convert Excel Files to Datatable

Initial Answer

As you said you started from the following question:

  • Fastest method to remove Empty rows and Columns From Excel Files using Interop

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

List<int> NonEmptyRows = Enumerable.Range(1, rowsToDelete.Max()).ToList().Except(rowsToDelete).ToList();

And if NonEmptyRows.Max() < rowsToDelete.Max() the last non-empty row is NonEmptyRows.Max() Else it is worksheet.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 and DeleteRows functions:

    private static void DeleteRows(List<int> rowsToDelete, Microsoft.Office.Interop.Excel.Worksheet worksheet)
    {
        // the rows are sorted high to low - so index's wont shift

        List<int> NonEmptyRows = Enumerable.Range(1, rowsToDelete.Max()).ToList().Except(rowsToDelete).ToList();

        if (NonEmptyRows.Max() < rowsToDelete.Max())
        {

            // there are empty rows after the last non empty row

            Microsoft.Office.Interop.Excel.Range cell1 = worksheet.Cells[NonEmptyRows.Max() + 1,1];
            Microsoft.Office.Interop.Excel.Range cell2 = worksheet.Cells[rowsToDelete.Max(), 1];

            //Delete all empty rows after the last used row
            worksheet.Range[cell1, cell2].EntireRow.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp);

        }    //else last non empty row = worksheet.Rows.Count

        foreach (int rowIndex in rowsToDelete.Where(x => x < NonEmptyRows.Max()))
        {
            worksheet.Rows[rowIndex].Delete();
        }
    }

    private static void DeleteCols(List<int> colsToDelete, Microsoft.Office.Interop.Excel.Worksheet worksheet)
    {
        // the cols are sorted high to low - so index's wont shift

        //Get non Empty Cols
        List<int> NonEmptyCols = Enumerable.Range(1, colsToDelete.Max()).ToList().Except(colsToDelete).ToList();

        if (NonEmptyCols.Max() < colsToDelete.Max())
        {

            // there are empty rows after the last non empty row

            Microsoft.Office.Interop.Excel.Range cell1 = worksheet.Cells[1,NonEmptyCols.Max() + 1];
            Microsoft.Office.Interop.Excel.Range cell2 = worksheet.Cells[1,NonEmptyCols.Max()];

            //Delete all empty rows after the last used row
            worksheet.Range[cell1, cell2].EntireColumn.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftToLeft);

        }            //else last non empty column = worksheet.Columns.Count

        foreach (int colIndex in colsToDelete.Where(x => x < NonEmptyCols.Max()))
        {
            worksheet.Columns[colIndex].Delete();
        }
    }

How to delete multiple blank lines in a WPF DataGrid imported from an Excel file

Below, as commented, is an example of removing the extra “empty” rows from the DataTable.

There are a couple of ways to approach this. One is to clean the Excel file of the extras rows as I am aware that using Excel’s UsedRange property has a nasty habit of flagging rows that have no apparent data as NOT empty. This may be from formatting or other issues. I have a solution for that if you want to go down that rabbit hole. Fastest method to remove Empty rows and Columns From Excel Files using Interop

However, this solution was heavily based on LARGE Excel files with many rows and columns. If the files are not large, then the solution below should work.

Even though your posted code has some much-needed range checking (more below), using the posted code, I was able to read an Excel file that produced extra “empty” rows at the end. It is these rows we want to remove from the DataTable.

I am sure there are other ways to do this, however, a basic approach would be to simply loop through the DataTable rows, and check each cell… and, if ALL the cells on that row are “empty” then remove that row. This is the approach I used below.

To help get this done quickly, keeping this to one loop through the table is a goal. In other words, we want to loop through the table and remove rows from that SAME table. This will mean that extra care is needed. Obviously a foreach loop through the rows will not work.

However, a simple for loop will work, as long as we start at the bottom and work up. AND we need to make sure and NOT use dt.Rows.Count as an “ending” condition in the for loop through the rows. As this could possibly cause some problems. This is easily avoided by simply fixing the row count to a variable and use it as an ending condition. This will allow the code to delete the rows from the bottom up and not have to worry about getting the row and loop indexes mixed up.

A walkthrough of the code would go like… First a bool variable allEmpty is created to indicate if ALL the cells in a row are “empty.” For each row, we will set this variable to true to indicate that the row is empty. Then a loop through each cell of that row and check if each cell is NOT empty. If at least one of the cells in that row is NOT empty, then, we set allEmpty to false and break out of the columns loop. After the columns loop is exited, the code simply checks to see if that row is empty and if so, deletes that row.

It should be noted in the last if statement that checks for the empty row. When the FIRST non-empty row is found, then in this context where we are only wanting to delete the last “empty” rows, then, we are done and can break out of the rows loop and exit since we are only looking for the LAST empty rows.

If you comment out the else portion of the bottom if code, then, the code will remove ALL the empty rows.

bool allEmpty;
int rowCount = dt.Rows.Count - 1;
for (int dtRowIndex = rowCount; dtRowIndex >= 0; dtRowIndex--) {
  allEmpty = true;
  for (int dtColIndex = 0; dtColIndex < dt.Columns.Count; dtColIndex++) {
    if (dt.Rows[dtRowIndex].ItemArray[dtColIndex].ToString() != "") {
      allEmpty = false;
      break;
    }
  }
  if (allEmpty) {
    dt.Rows.RemoveAt(dtRowIndex);
  }
  else {
    break;
  }
}

Eye brow raiser for the posted code…

The current posted code makes some dangerous assumptions in relation to what is returned from UsedRange and the dt column indexes. Example, the code starts by grabbing the worksheets UsedRange.

range = worksheet.UsedRange;

We obviously NEED this info, however, at this point in the code, we have NO clue how many rows or columns have been returned. Therefore, when the code gets to the second for loop through the columns... The code uses this column index as an index into the data row dr…

dr[column - 1] = …

Since the data table dt only has 6 columns, this is a risky assignment without checking the index range. Since used range grabs the used cells, what if a user added some text into column 7, 8 or ANY cell greater than 6, then this code will crash and burn. The code MUST check the number of columns returned from UsedRange to avoid an index out of range exception.

There are a couple of ways you could fix this. One would be to set the column loop ending condition to the number of columns in the data table. Unfortunately, this still leads to checking the number of columns returned by the used range considering it may return less columns than the data table has and the code will crash on the same line above only on the right side of the “=” equation.

 = Convert.ToString((range.Cells[row, column] as Excel.Range).Value);

In both cases it is clear your code needs to check these ranges BEFORE you start the looping through the used range.

Lastly, if you must use Excel Interop, which is usually a last option case, then you need to minimize the possibility of leaking the COM objects (leaking resources), such that when something goes wrong your code still releases the COM objects the code creates. When using Interop, I suggest you wrap all the Excel code in a try/catch/finally statement. In the try portion you have the code. And the Finally portion is where you close the excel workbook, quit the excel application and release the COM objects.

You will need to decide what to do in the catch portion of code. A simple message box displayed to the user may suffice to tell the user there was an error, the user clicks OK, and the code executes the finally code. Point being, that you want to display something instead of simply swallowing the error.

This approach may look something like…

Microsoft.Office.Interop.Excel.Application ExcelApp = null;
Microsoft.Office.Interop.Excel.Workbook Workbook = null;
Microsoft.Office.Interop.Excel.Worksheet Worksheet = null;

try {
  // code that works with excel interop
}
catch (Exception e) {
  MessageBox.Show("Error Excel: " + e.Message);
}
finally {
  if (Worksheet != null) {
    Marshal.ReleaseComObject(Worksheet);
  }
  if (Workbook != null) {
    //Workbook.Save();
    Workbook.Close();
    Marshal.ReleaseComObject(Workbook);
  }
  if (ExcelApp != null) {
    ExcelApp.Quit();
    Marshal.ReleaseComObject(ExcelApp);
  }
}

I hope this makes sense and helps.

Removing empty rows in excel using C#

You can do it using a Range Object. I assume here that you are using Excel interop.

Let say you have your book open, then set the range then delete it It should look something like this

ApplicationClass excel = new ApplicationClass();
//...

Microsoft.Office.Interop.Excel.Range cel = (Range)excel.Cells[rowIndex, columnIndex];
cel.Delete();

You can also try using:

for(int i = 1; i <=20; i++)
{
   excelRange = (Excel.Range)excelWorkSheet.Cells[i, 1];
   if (!string.IsNullOrEmpty(excelRange.Text.ToString()))
   {
       ((Range)excelWorkSheet.Rows[i]).Delete(excelRange);
   }
}

Check out the link below

https://social.msdn.microsoft.com/Forums/office/en-US/469fdf10-35cc-46b2-a875-7b974deb5659/how-to-delete-all-empty-rows-from-a-excel-sheet-using-microsoftofficeinteropexcel?forum=exceldev

and

https://stackoverflow.com/a/9952004/4373895
Here "Something" is your null value.

Hope this helps.

Quickest excel interop way to copy a big range, sort it and remove unnecessay columns

Firstly, as good practice, you should catch the exception thrown & find any additional information - which will be the HRESULT for this exception.

In this case though, the information obtained is not particularly helpful - Error code 0x800a03ec, which is used for multiple faults.

Looking at the documentation for the Sort method, I found your 4th parameter is incorrect - it should only be used when sorting PivotTable reports & should otherwise be passed as Type.Missing.

rData.Sort(rData.Columns[2, Type.Missing], Excel.XlSortOrder.xlDescending,
            Type.Missing, Type.Missing,  Excel.XlSortOrder.xlAscending,
            Type.Missing, Excel.XlSortOrder.xlAscending,
            Excel.XlYesNoGuess.xlGuess, Type.Missing, Type.Missing,
            Excel.XlSortOrientation.xlSortColumns, Excel.XlSortMethod.xlPinYin,
            Excel.XlSortDataOption.xlSortNormal,
            Excel.XlSortDataOption.xlSortNormal,
            Excel.XlSortDataOption.xlSortNormal);

You could also simplify the method call by using named parameters, it will then use appropriate default values for the ones you omit:

        rData.Sort(rData.Columns[2, Type.Missing],
             XlSortOrder.xlDescending, 
             Header: XlYesNoGuess.xlGuess, 
             Orientation: XlSortOrientation.xlSortColumns);

See also https://learn.microsoft.com/en-us/visualstudio/vsto/how-to-programmatically-sort-data-in-worksheets?view=vs-2019

Also, you should quit the application - excel.Quit(); at the end of your application. By default, an instance of Excel is opened up but not displayed - if you do not quit the application then it will remain open until you shut your PC down - try running your application several times & then open up TaskManager & you will see an equal number of Excel processes.

If your application is relatively small like your code above, then you should not have any issue regarding tidying up the Excel COM objects, if your code is more complex & you use many objects (Ranges, Worksheets etc are all COM objects) then you should ensure they are released from memory by use of Marshal.ReleaseComObject & setting the reference to null - calling GC.Collect twice is also recommended by some people - lots of discussion here : How do I properly clean up Excel interop objects?


Related Topics

Data Binding Dynamic Data

Get the Value of Displayname Attribute

Linq Grouping Dynamically

Custom Collection Initializers

Thread.Sleep for Less Than 1 Millisecond

File I/O with Streams - Best Memory Buffer Size

How to Convert String to Double with Proper Cultureinfo

Escape Command Line Arguments in C#

Does Parallel.Foreach Limit the Number of Active Threads

Best Way in .Net to Manage Queue of Tasks on a Separate (Single) Thread

.Net: Determine the Type of "This" Class in Its Static Method

How to Hide Tabpage from Tabcontrol

JSON Deserialization to C# with Dynamic Keys

Parsing JSON Object with Variable Properties into Strongly Typed Object

Re-Entrant Locks in C#

Why Can't Class Fields Be Var

Escape Special Character in Regex

How to Filter a Datatable


Leave a reply



Submit