使用Interop从Excel文件中删除空行和空列的最快方法

Question

使用Interop从Excel文件中删除空行和空列的最快方法

22

我有许多包含数据、空行和空列的 Excel 文件，如下所示：

我试图使用 interop 从 Excel 中删除空行和空列。我创建了一个简单的 winform 应用程序，并使用了以下代码，它可以正常工作。

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)

在清理大型Excel文件时，需要花费很长时间。有没有优化该代码的建议？或者有没有更快的方法来清理这些Excel文件？是否有一种函数可以通过单击删除空行？

如果答案使用C#也没有问题。

编辑：

我上传了一个示例文件样例文件。但并非所有文件结构都相同。

- Hadi

1

看起来对我来说很理想。盯着水壶看是不会烧开的，不要等待它。你可以考虑跟踪你已经处理过的文件和它们的最后修改日期，这样你就不会重复修复它们了。 - Hans Passant

1

你有一个我们可以用来测试的样本 xls 文件吗？ - Simon Mourier

我添加了一个示例文件，请查看我上传的示例文件。 - Hadi

5个回答

5

也许有些需要考虑的事情：

Sub usedRangeDeleteRowsCols()
    Dim LastRow, LastCol, i As Long

    LastRow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    LastCol = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column

    For i = LastRow To 1 Step -1
        If WorksheetFunction.CountA(Range(Cells(i, 1), Cells(i, LastCol))) = 0 Then
            Cells(i, 1).EntireRow.Delete
        End If
    Next

    For i = LastCol To 1 Step -1
        If WorksheetFunction.CountA(Range(Cells(1, i), Cells(LastRow, i))) = 0 Then
            Cells(1, i).EntireColumn.Delete
        End If
    Next
End Sub

我认为与原始代码中等效函数相比有两个效率更高的地方。首先，我们不使用Excel不可靠的UsedRange属性，而是找到最后一个值，并仅在真正使用的范围内扫描行和列。

其次，工作表计数函数再次仅适用于真正使用的范围 - 例如，在搜索空白行时，我们仅查看已使用列的范围（而不是.EntireRow）。

“For”循环向后工作，因为，例如，每次删除行后，下面数据的行地址会改变。向后工作意味着“要处理的数据”的行地址不会改变。

- David

同样的性能！！但被投票支持，因为我喜欢你的思考方式。 - Hadi

1

啊，非常感谢！真遗憾它没有加速。也许代码的另一部分会导致最大的延迟，但我不确定是什么…… - David

4

在我看来，最耗时的部分可能是枚举和查找空行和列。关于这个问题，可以参考以下链接：http://www.howtogeek.com/206696/how-to-quickly-and-easily-delete-blank-rows-and-columns-in-excel-2013/ 编辑： 那么，以下内容怎么样？

m_XlWrkSheet.Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
m_XlWrkSheet.Rows("1:1").SpecialCells(xlCellTypeBlanks).EntireColumn.Delete

在样本数据上测试结果看起来不错，性能更好（从VBA测试，但差异巨大）。

更新：

在包含14k行的样本Excel上进行测试（由样本数据生成），原始代码约为30秒，此版本小于1秒。

- smartobelix

不错，但它并没有帮助。因为存在包含空列的非空行。 - Hadi

@H.Fadlallah：这个修正版本是否符合您的标准？ - smartobelix

这个方法真是太棒了...但只适用于非空列（主键的情况）或行中包含空单元格。在我的情况下，它将删除许多包含数据的行。 - Hadi

2

我所知道的最简单方法是隐藏非空单元格并删除可见的单元格：

var range = m_XlWrkSheet.UsedRange;
range.SpecialCells(XlCellType.xlCellTypeConstants).EntireRow.Hidden = true;
range.SpecialCells(XlCellType.xlCellTypeVisible).Delete(XlDeleteShiftDirection.xlShiftUp);
range.EntireRow.Hidden = false;

更快的方法是根本不删除任何内容，而是移动（剪切+粘贴）非空白区域。

最快的Interop方式（还有更快、更复杂的方法，不需要打开文件）是获取数组中的所有值，移动数组中的值，然后将值放回。

object[,] values = m_XlWrkSheet.UsedRange.Value2 as object[,];

// some code here (the values start from values[1, 1] not values[0, 0])

m_XlWrkSheet.UsedRange.Value2 = values;

- Slai

0

您可以打开一个 ADO 连接到工作表，获取字段列表，发出仅包含已知字段的 SQL 语句，并排除在已知字段中没有值的记录。

- Zev Spitz

Ado连接不是与Excel一起工作的最佳方式，因为Excel不是一个非常结构化的表格。您可能会有没有标题的列，混合数据类型的列以及许多其他问题... - Hadi

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- JohnG · Accepted Answer

我发现如果工作表很大，循环遍历Excel工作表可能需要一些时间。因此，我的解决方案试图避免在工作表中进行任何循环。为了避免在工作表中进行循环，我使用usedRange返回的单元格创建了一个二维对象数组：

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

这是我循环的数组，以获取空行和列的索引。我创建了两个int列表，一个用于保留要删除的行索引，另一个用于保留要删除的列索引。

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

为了方便从底部向上删除行和从右向左删除列，这些列表将按从高到低的顺序排序。然后只需循环遍历每个列表并删除相应的行/列。

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

最后删除所有空行和空列后，将文件另存为新文件名。

希望这可以帮到您。

编辑：

解决了UsedRange问题，使得如果工作表顶部有空行，则这些行现在将被删除。此外，这将删除数据开始左侧的所有空列。即使数据开始之前存在空行或列，这也允许索引正常工作。这是通过获取UsedRange第一个单元格的地址来实现的，它将是一个形如“$A$1:$D$4”的地址。如果要保留顶部的空行和左侧的空列而不删除它们，则可以使用偏移量。在这种情况下，我只是删除它们。从顶部删除的行数可以通过第一个“$A$4”地址计算，其中“4”是第一个数据出现的行。列地址的形式为“A”、“AB”甚至“AAD”，这需要一些转换，感谢如何将列号（例如127）转换为Excel列（例如AA），我能够确定需要删除左侧的多少列。

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;
  }
}

编辑 2：为了测试，我编写了一个方法来遍历工作表并将其与我的代码进行比较，该代码遍历对象数组。它显示了显着的差异。

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

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();
    }
  }
}

然后是用于测试/计时这两种方法的主要函数。

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();
}

编辑3 根据OP的要求，我更新并更改了代码以匹配OP的代码。通过这个过程，我发现了一些有趣的结果，请看下面。

我更改了代码以匹配您正在使用的函数，即EntireRow和CountA。我发现以下代码执行效率非常低。运行一些测试后，我发现以下代码的执行时间超过了800毫秒。然而，一个微小的改变产生了巨大的影响。

在这行代码上：

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

这会严重拖慢速度。如果你为UsedRange创建一个范围变量，而不是在每次while循环迭代时重新获取它，将会产生巨大的差异。因此...当我将while循环更改为...

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

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

这个方法的表现与我的对象数组解决方案非常接近。我没有发布结果，因为您可以使用下面的代码，并将 while 循环更改为在每次迭代中抓取 UsedRange 或使用变量 usedRange 进行测试。

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++;
    }
  }
}

由@Hadi更新

如果在Excel中存在最后使用的行和列之后的额外空白行和列，您可以修改DeleteCols和DeleteRows函数以获得更好的性能：

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();
    }
}

请查看我的答案：使用Interop从Excel获取最后一个非空列和行索引