c# - office - Obtenga el último índice de columnas y filas no vacías de excel utilizando Interop

manejo de excel en c#

Question

Estoy tratando de eliminar todas las filas y columnas adicionales en blanco de un archivo de Excel utilizando la biblioteca de Interop.

Seguí esta pregunta El método más rápido para eliminar filas y columnas vacías de archivos de Excel usando Interop y lo encuentro útil.

Pero tengo archivos de Excel que contienen un pequeño conjunto de datos pero muchas filas y columnas vacías (desde la última fila (o columna) no vacía hasta el final de la hoja de trabajo)

Traté de pasar por encima de Filas y Columnas, pero el ciclo lleva horas.

Intento obtener el último índice de fila y columna no vacío para poder eliminar todo el rango vacío en una línea

XlWks.Range("...").EntireRow.Delete(xlShiftUp)

Nota: estoy tratando de obtener la última fila que contenga datos para eliminar todos los espacios en blanco adicionales (después de esta fila o columna)

¿Alguna sugerencia?

Answer 1

Debería poder encontrar la última fila y columna no vacía con algo similar a esto:

with m_XlWrkSheet
lastRow = .UsedRange.Rows.Count
lastCol = .UsedRange.Columns.Count
end with

Eso es VB.NET, pero debería funcionar más o menos. Eso devolverá la Fila 16 y la Columna 10 (según su imagen anterior). Luego puede usar eso para encontrar el rango que desea eliminar todo en una línea.

Answer 2

Estoy usando ClosedXml que tiene métodos útiles ''LastUsedRow'' y ''LastUsedColumn''.

var wb = new XLWorkbook(@"<path>/test.xlsx", XLEventTracking.Disabled);
var sheet = wb.Worksheet("Sheet1");

for (int i = sheet.LastRowUsed().RowNumber() - 1; i >= 1; i--)
{
    var row = sheet.Row(i);
    if (row.IsEmpty())
    {
        row.Delete();
    }
}

wb.Save();

Este simple bucle eliminó 5000 de 10000 filas en 38 segundos. No es rápido, pero es mucho mejor que ''horas''. Eso depende de la cantidad de filas / columnas con las que está tratando, por supuesto, que no dice. Sin embargo, después de más pruebas con 25000 filas vacías de 50000, toma aproximadamente 30 minutos eliminar las filas vacías en un bucle. Eliminar claramente las filas no es un proceso eficiente.

Una mejor solución es crear una nueva hoja y luego copiar las filas que desea conservar.

Paso 1: crea una hoja con 50000 filas y 20 columnas, cada dos filas y columnas están vacías.

var wb = new XLWorkbook(@"C:/Users/passp/Documents/test.xlsx");
var sheet = wb.Worksheet("Sheet1");
sheet.Clear();

for (int i = 1; i < 50000; i+=2)
{
    var row = sheet.Row(i);

    for (int j = 1; j < 20; j += 2)
    {
        row.Cell(j).Value = i * j;
    }
}

Paso 2: copie las filas con datos en una nueva hoja. Esto toma 10 segundos.

var wb = new XLWorkbook(@"C:/Users/passp/Documents/test.xlsx", XLEventTracking.Disabled);
var sheet = wb.Worksheet("Sheet1");

var sheet2 = wb.Worksheet("Sheet2");
sheet2.Clear();

sheet.RowsUsed()
    .Where(r => !r.IsEmpty())
    .Select((r, index) => new { Row = r, Index = index + 1} )
    .ForEach(r =>
    {
        var newRow = sheet2.Row(r.Index);

        r.Row.CopyTo(newRow);
    }
);

wb.Save();

Paso 3: esto sería hacer la misma operación para las columnas.

Answer 3

Hace varios años, creé un ejemplo de código de MSDN que permite que un desarrollador obtenga la última fila y columna usadas de una hoja de trabajo. Lo modifiqué, coloqué todo el código necesario en una biblioteca de clase con una interfaz de Windows para mostrar la operación.

El código subyacente usa Microsoft.Office.Interop.Excel.

Ubicación en Microsoft una unidad https://1drv.ms/u/s!AtGAgKKpqdWjiEGdBzWDCSCZAMaM

Aquí obtengo la primera hoja en un archivo de Excel, obtengo la última fila usada y col y presente como una dirección de celda válida.

Private Sub cmdAddress1_Click(sender As Object, e As EventArgs) Handles cmdAddress1.Click
    Dim ops As New GetExcelColumnLastRowInformation
    Dim info = New UsedInformation
    ExcelInformationData = info.UsedInformation(FileName, ops.GetSheets(FileName))

    Dim SheetName As String = ExcelInformationData.FirstOrDefault.SheetName

    Dim cellAddress = (
        From item In ExcelInformationData
        Where item.SheetName = ExcelInformationData.FirstOrDefault.SheetName
        Select item.LastCell).FirstOrDefault

    MessageBox.Show($"{SheetName} - {cellAddress}")

End Sub

Dentro del proyecto de demostración también recibo todas las hojas para un archivo de Excel, las presento en un ListBox. Seleccione un nombre de hoja del cuadro de lista y obtenga la última fila y columna de esa hoja en una dirección de celda válida.

Private Sub cmdAddress_Click(sender As Object, e As EventArgs) Handles cmdAddress.Click
    Dim cellAddress =
        (
            From item In ExcelInformationData
            Where item.SheetName = ListBox1.Text
            Select item.LastCell).FirstOrDefault

    If cellAddress IsNot Nothing Then
        MessageBox.Show($"{ListBox1.Text} {cellAddress}")
    End If

End Sub

A primera vista, al abrir la solución desde el enlace de arriba, notará que hay un montón de código. El código es óptimo y liberará todos los objetos inmediatamente.

Answer 4

Digamos que la última celda de esquina con datos es J16, por lo que no hay datos en las columnas K en adelante, o en las filas 17 hacia abajo. ¿Por qué los estás eliminando? ¿Cuál es el escenario y qué estás tratando de lograr? ¿Está despejando nuestro formato? ¿Está borrando nuestras fórmulas que muestran una cadena vacía?

En cualquier caso, el bucle no es el camino.

El siguiente código muestra una forma de utilizar el método Clear () del objeto Range para borrar todos los contenidos y fórmulas y el formato de un rango. Alternativamente, si realmente desea eliminarlos, puede usar el método Eliminar () para eliminar un Rango rectangular completo en un golpe. Será mucho más rápido que bucle ...

//code uses variables declared appropriately as Excel.Range & Excel.Worksheet Using Interop library
int x;
int y;
// get the row of the last value content row-wise
oRange = oSheet.Cells.Find(What: "*", 
                           After: oSheet.get_Range("A1"),
                           LookIn: XlFindLookIn.xlValues,
                           LookAt: XlLookAt.xlPart, 
                           SearchDirection: XlSearchDirection.xlPrevious,
                           SearchOrder: XlSearchOrder.xlByRows);

if (oRange == null)
{
    return;
}
x = oRange.Row;

// get the column of the last value content column-wise
oRange = oSheet.Cells.Find(What: "*",
                           After: oSheet.get_Range("A1"),
                           LookIn: XlFindLookIn.xlValues, LookAt: XlLookAt.xlPart,
                           SearchDirection: XlSearchDirection.xlPrevious,
                           SearchOrder: XlSearchOrder.xlByColumns);
y = oRange.Column;

// now we have the corner (x, y), we can delete or clear all content to the right and below
// say J16 is the cell, so x = 16, and j=10

Excel.Range clearRange;

//set clearRange to ("K1:XFD1048576")
clearRange = oSheet.Range[oSheet.Cells[1, y + 1], oSheet.Cells[oSheet.Rows.Count, oSheet.Columns.Count]];
clearRange.Clear(); //clears all content, formulas and formatting
//clearRange.Delete(); if you REALLY want to hard delete the rows

//set clearRange to ("A17:J1048576")            
clearRange = oSheet.Range[oSheet.Cells[x + 1, 1], oSheet.Cells[oSheet.Rows.Count, y]];
clearRange.Clear(); //clears all content, formulas and formatting
//clearRange.Delete();  if you REALLY want to hard delete the columns

Answer 5

Como dijiste, comenzaste de la siguiente pregunta:

• El método más rápido para eliminar filas vacías y columnas de archivos de Excel con Interop

Y está tratando de "obtener la última fila que contiene datos para eliminar todos los espacios en blanco adicionales (después de esta fila o columna)"

Asumiendo que está trabajando con la respuesta de aceptación (provista por @JohnG ), entonces puede agregar una línea de código para obtener la última fila y columna utilizada

Las filas vacías se almacenan en una lista de rowsToDelete

Puede usar el siguiente código para obtener las últimas filas no vacías con un índice más pequeño que la última fila vacía

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

Y si NonEmptyRows.Max() < rowsToDelete.Max() la última fila no vacía es NonEmptyRows.Max() contrario, es worksheet.Rows.Count y no hay filas vacías después de la última utilizada.

Lo mismo se puede hacer para obtener la última columna no vacía

El código se edita en las funciones DeleteCols y 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();
        }
    }

Answer 6

• Para obtener el último índice de columna / fila no vacío, se puede utilizar la función de Excel Find . Ver GetLastIndexOfNonEmptyCell .
• A CountA se utiliza el CountA función de la CountA cálculo de Excel para determinar si las celdas están vacías y CountA las filas / columnas enteras con un rango de filas / columnas.
• Estos rangos se eliminan finalmente de una vez.

public void Yahfoufi(string excelFile)
{
    var exapp = new Microsoft.Office.Interop.Excel.Application {Visible = true};
    var wrb = exapp.Workbooks.Open(excelFile);
    var sh = wrb.Sheets["Sheet1"];
    var lastRow = GetLastIndexOfNonEmptyCell(exapp, sh, XlSearchOrder.xlByRows);
    var lastCol = GetLastIndexOfNonEmptyCell(exapp, sh, XlSearchOrder.xlByColumns);
    var target = sh.Range[sh.Range["A1"], sh.Cells[lastRow, lastCol]];
    Range deleteRows = GetEmptyRows(exapp, target);
    Range deleteColumns = GetEmptyColumns(exapp, target);
    deleteColumns?.Delete();
    deleteRows?.Delete();
}

private static int GetLastIndexOfNonEmptyCell(
    Microsoft.Office.Interop.Excel.Application app,
    Worksheet sheet,
    XlSearchOrder searchOrder)
{
    Range rng = sheet.Cells.Find(
        What: "*",
        After: sheet.Range["A1"],
        LookIn: XlFindLookIn.xlFormulas,
        LookAt: XlLookAt.xlPart,
        SearchOrder: searchOrder,
        SearchDirection: XlSearchDirection.xlPrevious,
        MatchCase: false);
    if (rng == null)
        return 1;
    return searchOrder == XlSearchOrder.xlByRows
        ? rng.Row
        : rng.Column;
}

private static Range GetEmptyRows(
    Microsoft.Office.Interop.Excel.Application app,
    Range target)
{
    Range result = null;
    foreach (Range r in target.Rows)
    {
        if (app.WorksheetFunction.CountA(r.Cells) >= 1)
            continue;
        result = result == null
            ? r.EntireRow
            : app.Union(result, r.EntireRow);
    }
    return result;
}

private static Range GetEmptyColumns(
    Microsoft.Office.Interop.Excel.Application app,
    Range target)
{
    Range result = null;
    foreach (Range c in target.Columns)
    {
        if (app.WorksheetFunction.CountA(c.Cells) >= 1)
            continue;
        result = result == null
            ? c.EntireColumn
            : app.Union(result, c.EntireColumn);
    }
    return result;
}

Las dos funciones para obtener rangos vacíos de filas / columnas podrían refactorizarse a una función, algo como esto:

private static Range GetEntireEmptyRowsOrColumns(
    Microsoft.Office.Interop.Excel.Application app,
    Range target,
    Func<Range, Range> rowsOrColumns,
    Func<Range, Range> entireRowOrColumn)
{
    Range result = null;
    foreach (Range c in rowsOrColumns(target))
    {
        if (app.WorksheetFunction.CountA(c.Cells) >= 1)
            continue;
        result = result == null
            ? entireRowOrColumn(c)
            : app.Union(result, entireRowOrColumn(c));
    }
    return result;
}

Y luego solo llámalo:

Range deleteColumns = GetEntireEmptyRowsOrColumns(exapp, target, (Func<Range, Range>)(r1 => r1.Columns), (Func<Range, Range>)(r2 => r2.EntireColumn));
Range deleteRows = GetEntireEmptyRowsOrColumns(exapp, target, (Func<Range, Range>)(r1 => r1.Rows), (Func<Range, Range>)(r2 => r2.EntireRow));
deleteColumns?.Delete();
deleteRows?.Delete();

Nota: para obtener más información, eche un vistazo, por ejemplo, a esta pregunta SO .