使用 Interop 从 Excel 文件中删除空行和列的最快方法 [英] Fastest method to remove Empty rows and Columns From Excel Files using Interop

问题描述

我有很多包含数据的 excel 文件，其中包含空行和空列.如下图

我正在尝试使用互操作从 excel 中删除空行和列.我创建了一个简单的 winform 应用程序并使用了以下代码，它运行良好.

将 lstFiles 作为新列表(字符串)lstFiles.AddRange(IO.Directory.GetFiles(m_strFolderPath, "*.xls", IO.SearchOption.AllDirectories))Dim m_XlApp = 新建 Excel.ApplicationDim m_xlWrkbs As Excel.Workbooks = m_XlApp.WorkbooksDim m_xlWrkb 作为 Excel.Workbook对于每个 strFile 作为 lstFiles 中的字符串m_xlWrkb = m_xlWrkbs.Open(strFile)Dim m_XlWrkSheet As Excel.Worksheet = m_xlWrkb.Worksheets(1)Dim intRow 作为整数 = 1虽然 intRow <= m_XlWrkSheet.UsedRange.Rows.Count如果 m_XlApp.WorksheetFunction.CountA(m_XlWrkSheet.Cells(intRow, 1).EntireRow) = 0 那么m_XlWrkSheet.Cells(intRow, 1).EntireRow.Delete(Excel.XlDeleteShiftDirection.xlShiftUp)别的整数行 += 1万一结束时间将 intCol 调暗为整数 = 1虽然 intCol <= m_XlWrkSheet.UsedRange.Columns.Count如果 m_XlApp.WorksheetFunction.CountA(m_XlWrkSheet.Cells(1, intCol).EntireColumn) = 0 那么m_XlWrkSheet.Cells(1, intCol).EntireColumn.Delete(Excel.XlDeleteShiftDirection.xlShiftToLeft)别的整数 += 1万一结束时间下一个m_xlWrkb.Save()m_xlWrkb.Close(SaveChanges:=True)Marshal.ReleaseComObject(m_xlWrkb)Marshal.ReleaseComObject(m_xlWrkbs)m_XlApp.Quit()Marshal.ReleaseComObject(m_XlApp)

但是在清理大型 Excel 文件时需要花费大量时间.任何优化此代码的建议?或另一种更快地清理这个excel文件的方法?有没有一键删除空行的功能?

如果答案使用 C#，我没有问题

我上传了一个示例文件

遍历工作表并删除空行和列的方法.

enum RowOrCol { Row, Column };私有静态无效常规RemoveEmptyRowsCols(Excel.Worksheet工作表){Excel.Range usedRange = worksheet.UsedRange;int totalRows = usedRange.Rows.Count;int totalCols = usedRange.Columns.Count;RemoveEmpty(usedRange, RowOrCol.Row);RemoveEmpty(usedRange, RowOrCol.Column);}私有静态无效 RemoveEmpty(Excel.Range usedRange, RowOrCol rowOrCol) {整数计数；Excel.Range curRange;如果(rowOrCol == RowOrCol.Column)计数 = usedRange.Columns.Count;别的计数 = usedRange.Rows.Count;for (int i = count; i > 0; i--) {bool isEmpty = true;如果(rowOrCol == RowOrCol.Column)curRange = usedRange.Columns[i];别的curRange = usedRange.Rows[i];foreach(curRange.Cells 中的 Excel.Range 单元格){如果(单元格值！= null){isEmpty = 假；休息;//我们可以退出这个循环，因为范围不为空}别的 {//单元格值为空 contiue 检查}}//通过此范围(行或列)中的每个单元格结束循环如果(isEmpty){curRange.Delete();}}}

然后是用于测试/计时这两种方法的 Main.

enum RowOrCol { Row, Column };静态无效主(字符串 [] args){Excel.Application excel = new Excel.Application();string originalPath = @"H:ExcelTestFolderBook1_Test.xls";Excel.Workbook 工作簿 = excel.Workbooks.Open(originalPath);Excel.Worksheet worksheet = workbook.Worksheets["Sheet1"];Excel.Range usedRange = worksheet.UsedRange;//开始测试循环遍历每个 excel 工作表秒表 sw = 新秒表();Console.WriteLine("开始秒表循环遍历工作表...");sw.开始()；常规RemoveEmptyRowsCols(工作表)；sw.停止()；Console.WriteLine("总共花费了:" + sw.Elapsed.Milliseconds + " 删除空行和列的毫秒数...");string newPath = @"H:ExcelTestFolderBook1_Test_RemovedLoopThruWorksheet.xls";workbook.SaveAs(newPath, Excel.XlSaveAsAccessMode.xlNoChange);工作簿.关闭()；Console.WriteLine("");//开始测试循环遍历对象数组工作簿 = excel.Workbooks.Open(originalPath);worksheet = workbook.Worksheets["Sheet1"];usedRange = 工作表.UsedRange;Console.WriteLine("开始秒表循环遍历对象数组...");sw = 新秒表();sw.开始()；DeleteEmptyRowsCols(工作表)；sw.停止()；//显示第二次测试的结果Console.WriteLine("总共花费了:" + sw.Elapsed.Milliseconds + " 删除空行和列的毫秒数...");string newPath2 = @"H:ExcelTestFolderBook1_Test_RemovedLoopThruArray.xls";workbook.SaveAs(newPath2, Excel.XlSaveAsAccessMode.xlNoChange);工作簿.关闭()；退出();System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);Console.WriteLine("");Console.WriteLine("完成的测试方法 - 按任意键退出");Console.ReadKey();}

EDIT 3 根据 OP 要求...我更新并更改了代码以匹配 OP 代码.有了这个，我发现了一些有趣的结果.见下文.

我更改了代码以匹配您正在使用的函数，即… EntireRow 和 CountA.下面的代码我发现它执行得非常糟糕.运行一些测试我发现下面的代码在 800+ 毫秒的执行时间内.然而，一个细微的变化却带来了巨大的不同.

上线:

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

这大大减慢了速度.如果您为 UsedRang 创建一个范围变量，并且没有在 while 循环的每次迭代中保持重新抓取它，将会产生巨大的差异.所以......当我将while循环更改为......

Excel.Range usedRange = worksheet.UsedRange;int rowIndex = 1;while (rowIndex <= usedRange.Rows.Count)和while (colIndex <= usedRange.Columns.Count)

这与我的对象数组解决方案非常接近.我没有发布结果，因为您可以使用下面的代码并更改 while 循环以在每次迭代时获取 UsedRange 或使用变量 usedRange 来测试这一点.

private static void RemoveEmptyRowsCols3(Excel.Worksheet worksheet) {//Excel.Range usedRange = worksheet.UsedRange;//<- 使用这个变量使while循环更快int rowIndex = 1;//删除空行//while (rowIndex <= usedRange.Rows.Count)//<- 改变这一行会产生巨大的不同 - 不要在每次迭代时抓取 UsedRange...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);}别的 {行索引++；}}//删除空列int colIndex = 1;//while (colIndex <= usedRange.Columns.Count)//<- 也在这里更改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);}别的 {colIndex++;}}}

更新来自 @Hadi

如果excel在最后使用的行和列之后包含额外的空白行和列，您可以更改DeleteCols和DeleteRows函数以获得更好的性能:

private static void DeleteRows(ListrowsToDelete, Microsoft.Office.Interop.Excel.Worksheet worksheet){//行从高到低排序 - 所以索引不会移动列表NonEmptyRows = Enumerable.Range(1, rowsToDelete.Max()).ToList().Except(rowsToDelete).ToList();if (NonEmptyRows.Max()  x  colsToDelete, Microsoft.Office.Interop.Excel.Worksheet worksheet){//列从高到低排序 - 所以索引不会移动//获取非空列列表NonEmptyCols = Enumerable.Range(1, colsToDelete.Max()).ToList().Except(colsToDelete).ToList();if (NonEmptyCols.Max() < colsToDelete.Max()){//在最后一个非空行之后有空行Microsoft.Office.Interop.Excel.Range cell1 = worksheet.Cells[1,NonEmptyCols.Max() + 1];Microsoft.Office.Interop.Excel.Range cell2 = worksheet.Cells[1,NonEmptyCols.Max()];//删除最后使用的行之后的所有空行worksheet.Range[cell1, cell2].EntireColumn.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftToLeft);}//else 最后一个非空列 = worksheet.Columns.Countforeach (int colIndex in colsToDelete.Where(x => x 

在 使用Interop从excel中获取最后一个非空列和行索引

I have a lot of excel files that contains data and it contains empty rows and empty columns. like shown bellow

I am trying to remove Empty rows and columns from excel using interop. I create a simple winform application and used the following code and it works fine.

Dim lstFiles As New List(Of String)
lstFiles.AddRange(IO.Directory.GetFiles(m_strFolderPath, "*.xls", IO.SearchOption.AllDirectories))

Dim m_XlApp = New Excel.Application
Dim m_xlWrkbs As Excel.Workbooks = m_XlApp.Workbooks
Dim m_xlWrkb As Excel.Workbook

For Each strFile As String In lstFiles
    m_xlWrkb = m_xlWrkbs.Open(strFile)
    Dim m_XlWrkSheet As Excel.Worksheet = m_xlWrkb.Worksheets(1)
    Dim intRow As Integer = 1

    While intRow <= m_XlWrkSheet.UsedRange.Rows.Count
        If m_XlApp.WorksheetFunction.CountA(m_XlWrkSheet.Cells(intRow, 1).EntireRow) = 0 Then
            m_XlWrkSheet.Cells(intRow, 1).EntireRow.Delete(Excel.XlDeleteShiftDirection.xlShiftUp)
        Else
            intRow += 1
        End If
    End While

    Dim intCol As Integer = 1
    While intCol <= m_XlWrkSheet.UsedRange.Columns.Count
        If m_XlApp.WorksheetFunction.CountA(m_XlWrkSheet.Cells(1, intCol).EntireColumn) = 0 Then
            m_XlWrkSheet.Cells(1, intCol).EntireColumn.Delete(Excel.XlDeleteShiftDirection.xlShiftToLeft)
        Else
            intCol += 1
        End If
    End While
Next

m_xlWrkb.Save()
m_xlWrkb.Close(SaveChanges:=True)

Marshal.ReleaseComObject(m_xlWrkb)
Marshal.ReleaseComObject(m_xlWrkbs)
m_XlApp.Quit()
Marshal.ReleaseComObject(m_XlApp)

But when cleaning big excel files it takes a lot of time. Any suggestions for optimizing this code? or another way to clean this excel files faster? Is there a function that can delete empty rows in one click?

I don't have problem if answers are using C#

EDIT:

I uploaded a sample file Sample File. But not all files have same structure.

解决方案

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:ExcelTestFolderBook1_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:ExcelTestFolderBook1_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.

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:ExcelTestFolderBook1_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:ExcelTestFolderBook1_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:ExcelTestFolderBook1_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

这篇关于使用 Interop 从 Excel 文件中删除空行和列的最快方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！