Самый быстрый способ удаления пустых строк и столбцов из файлов Excel с помощью Interop

Ответ 1

Я обнаружил, что цикл через лист excel может занять некоторое время, если рабочий лист большой. Поэтому мое решение попыталось избежать цикла на листе. Чтобы избежать прокрутки листа, я сделал 2-мерный массив объектов из ячеек, возвращаемых из usedRange, с помощью:

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

Это массив, через который я прохожу, чтобы получить индексы пустых строк и столбцов. Я делаю 2 списка int, один держит индексы строк, чтобы удалить другую, чтобы индексы столбцов удалялись.

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

Эти списки будут отсортированы с высокой до низкой, чтобы упростить удаление строк снизу вверх и удаление столбцов справа налево. Затем просто проведите цикл по каждому списку и удалите соответствующую строку/столбец.

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

Наконец, после того, как все пустые строки и столбцы были удалены, я сохранил файл в новом имени файла.

Надеюсь, что это поможет.

EDIT:

Относится к проблеме UsedRange, так что если в верхней части листа есть пустые строки, эти строки будут удалены. Также это приведет к удалению всех пустых столбцов слева от исходных данных. Это позволяет правильно индексировать работу, даже если перед запуском данных есть пустые строки или столбцы. Это было достигнуто с помощью адреса первой ячейки в UsedRange, это будет адрес формы "$ A $1: $D $4". Это позволит использовать смещение, если пустые строки в верхнем и пустом столбцах слева должны оставаться и не удаляться. В этом случае я просто удаляю их. Чтобы получить количество строк для удаления сверху, можно вычислить по первому адресу "$ A $4", где "4" - это строка, в которой появляются первые данные. Поэтому нам нужно удалить верхние 3 строки. Адрес столбца имеет форму "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 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 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:. Для тестирования я создал метод, который пересекает рабочий лист и сравнивает его с моим кодом, который проходит через массив объектов. Это показывает существенную разницу.

Метод Loop через рабочий лист и удаление пустых строк и столбцов.

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)

Это замедляет многое. Если вы создаете переменную диапазона для UsedRang и не сохраняете regrabbibg, то каждая итерация цикла while будет иметь огромное значение. Итак... когда я меняю цикл while на...

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

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

Это было очень близко к решению моего массива объектов. Я не опубликовал результаты, так как вы можете использовать приведенный ниже код и изменить цикл while, чтобы захватить UseRange с каждой итерацией или использовать переменную 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

Вы можете изменить функции DeleteCols и DeleteRows, чтобы получить лучшую производительность, если excel содержит дополнительные пустые строки и столбцы после последних использованных:

private static void DeleteRows(List<int> rowsToDelete, Microsoft.Office.Interop.Excel.Worksheet worksheet)
{
    // the rows are sorted high to low - so index 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 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();
    }
}

проверить мой ответ на Получить последний не пустой столбец и индекс строки из excel с помощью Interop

Ответ 2

Может быть, что-то рассмотреть:

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

Я думаю, что есть две эффективности по сравнению с эквивалентными функциями в исходном коде. Во-первых, вместо использования ненадежного свойства WeekRange Excel мы находим последнее значение и проверяем только строки и столбцы в пределах используемого диапазона.

Во-вторых, функция подсчета листов снова работает только в пределах используемого диапазона - например, при поиске пустых строк мы рассматриваем только диапазон используемых столбцов (а не .EntireRow).

Циклы For работают назад, потому что, например, каждый раз, когда строка удаляется, изменяется адрес строки следующих данных. Работа назад означает, что адреса строк "данные, которые будут обработаны" не изменяются.

Ответ 3

По моему мнению, самая трудоемкая часть может перечислить и найти пустые строки и столбцы.

Как насчет: http://www.howtogeek.com/206696/how-to-quickly-and-easily-delete-blank-rows-and-columns-in-excel-2013/

EDIT:

Как насчет:

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

Протестированный результат выборки данных выглядит нормально, производительность лучше (тестируется с VBA, но разница огромна).

UPDATE:

Протестировано на примере Excel с 14k строк (из исходных данных) исходный код ~ 30 с, эта версия < 1s

Ответ 4

Самый простой способ, которым я знаю, - скрыть непустые ячейки и удалить видимые:

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;

Ответ 5

Вы можете открыть соединение ADO с рабочим листом, получить список полей, выдать инструкцию SQL, которая включает только известные поля, а также исключить записи без значений в известных полях.