c# - teclado - eliminar filas en blanco excel macro

El método más rápido para eliminar filas y columnas vacías de archivos de Excel usando interoperabilidad

Question

Tengo muchos archivos de Excel que contienen datos y filas y columnas vacías. como se muestra abajo

Estoy tratando de eliminar filas y columnas vacías de Excel usando interoperabilidad. Creo una aplicación simple para Winform y utilicé el siguiente código y funciona bien.

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)

Pero cuando se limpian archivos de Excel grandes, lleva mucho tiempo. ¿Alguna sugerencia para optimizar este código? u otra forma de limpiar este excel archivos mas rapido? ¿Hay una función que puede eliminar filas vacías en un clic?

No tengo problema si las respuestas están usando C #

EDITAR:

Subí un archivo de muestra . Pero no todos los archivos tienen la misma estructura.

Answer 1

Descubrí que recorrer la hoja de cálculo de Excel puede llevar algún tiempo si la hoja de cálculo es grande. Así que mi solución trató de evitar cualquier bucle en la hoja de trabajo. Para evitar recorrer la hoja de trabajo, hice una matriz de objetos bidimensionales a partir de las celdas devueltas de usedRange con:

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

Esta es la matriz que recorro para obtener los índices de las filas y columnas vacías. Hago 2 listas int, una mantiene los índices de fila para eliminar la otra mantiene los índices de columna para eliminar.

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

Estas listas se ordenarán de alto a bajo para simplificar la eliminación de filas de abajo hacia arriba y la eliminación de columnas de derecha a izquierda. Luego simplemente recorra cada lista y elimine la fila / col apropiada.

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

Finalmente, después de que se hayan eliminado todas las filas y columnas vacías, guardaré el archivo en un nuevo nombre de archivo.

Espero que esto ayude.

EDITAR:

Se solucionó el problema de UsedRange, de modo que si hay filas vacías en la parte superior de la hoja de trabajo, esas filas ahora se eliminarán. También esto eliminará cualquier columna vacía a la izquierda de los datos de inicio. Esto permite que la indexación funcione correctamente incluso si hay filas o columnas vacías antes de que empiecen los datos. Esto se logró al tomar la dirección de la primera celda en UsedRange; esta será una dirección del formulario “$ A $ 1: $ D $ 4”. Esto permitirá el uso de un desplazamiento si las filas vacías en la parte superior y las columnas vacías a la izquierda deben permanecer y no ser eliminadas. En este caso simplemente los estoy eliminando. Para obtener el número de filas que se deben eliminar desde la parte superior, se puede calcular por la primera dirección "$ A $ 4" donde "4" es la fila en la que aparecen los primeros datos. Así que necesitamos eliminar las 3 filas superiores. La dirección de la columna es de la forma "A", "AB" o incluso "AAD", esto requirió una traducción y gracias a Cómo convertir un número de columna (por ejemplo, 127) en una columna de Excel (por ejemplo, AA) pude determine cuántas columnas a la izquierda necesitan ser eliminadas.

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

EDITAR 2: Para las pruebas hice un método que recorre la hoja de trabajo y lo comparo con mi código que recorre una matriz de objetos. Muestra una diferencia significativa.

Método para recorrer la hoja de trabajo y eliminar filas y columnas vacías.

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

Luego un Main para probar / cronometrar los dos métodos.

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

EDITAR 3 Según la solicitud OP ... Actualicé y cambié el código para que coincida con el código OP. Con esto encontré algunos resultados interesantes. Vea abajo.

Cambié el código para que coincida con las funciones que está utilizando, es decir, ... EntireRow y CountA. El código de abajo encontré que se preforma terriblemente. Al ejecutar algunas pruebas, encontré que el código siguiente estaba en el tiempo de ejecución de más de 800 milisegundos. Sin embargo, un cambio sutil hizo una gran diferencia.

En la línea:

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

Esto está ralentizando mucho las cosas. Si crea una variable de rango para UsedRang y no mantiene regrabbibg con cada iteración del bucle while hará una gran diferencia. Entonces ... cuando cambio el bucle while a ...

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

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

Esto se realizó muy cerca de mi solución de matriz de objetos. No publiqué los resultados, ya que puedes usar el código a continuación y cambiar el bucle while para tomar el Rango Utilizado con cada iteración o usar la variable usedRange para probar esto.

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

ACTUALIZACIÓN por

Puede modificar las funciones DeleteCols y DeleteRows para obtener un mejor rendimiento si Excel contiene filas y columnas adicionales en blanco después de las últimas utilizadas:

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

verifique mi respuesta en Obtener último índice de filas y filas no vacías de Excel usando Interop

Answer 2

En mi opinión, la parte que consume más tiempo podría ser enumerar y encontrar filas y columnas vacías.

Qué hay de: http://www.howtogeek.com/206696/how-to-quickly-and-easily-delete-blank-rows-and-columns-in-excel-2013/

EDITAR:

Qué pasa:

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

El resultado de prueba de datos de muestra se ve bien, el rendimiento es mejor (probado de VBA pero la diferencia es enorme).

ACTUALIZAR:

Probado en Excel de muestra con 14k filas (hechas de datos de muestra) código original ~ 30 s, esta versión <1s

Answer 3

La forma más fácil que conozco es ocultar las celdas que no están en blanco y eliminar las visibles:

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

Los métodos más rápidos son no eliminar nada en absoluto, sino mover (cortar + pegar) las áreas que no están en blanco.

La forma más rápida de interoperabilidad (hay métodos más rápidos y complicados sin abrir el archivo) es obtener todos los valores en la matriz, mover los valores en la matriz y devolver los valores:

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;

Answer 4

Podría abrir una conexión ADO a la hoja de trabajo, obtener una lista de campos, emitir una declaración SQL que incluya solo campos conocidos y también excluir registros sin valores en los campos conocidos.

Answer 5

Quizás algo a considerar:

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

Creo que hay dos eficiencias en comparación con las funciones equivalentes en el código original. En primer lugar, en lugar de utilizar la propiedad poco confiable de UsedRange, encontramos el último valor y solo escaneamos filas y columnas dentro del rango original utilizado.

En segundo lugar, la función de conteo de la hoja de trabajo nuevamente solo funciona dentro del rango utilizado genuino, por ejemplo, cuando buscamos filas en blanco, solo buscamos en el rango de columnas usadas (en lugar de.

Los bucles For funcionan hacia atrás porque, por ejemplo, cada vez que se elimina una fila, la dirección de la fila de los siguientes datos cambia. Trabajar hacia atrás significa que las direcciones de fila de "datos en los que se trabajará" no cambian.