Obtenir la dernière colonne non vide et l'index de ligne d'excel en utilisant Interop

j'essaie de supprimer toutes les lignes et colonnes Vierges supplémentaires d'un fichier excel en utilisant la bibliothèque Interop.

j'ai suivi cette question la méthode la plus rapide pour supprimer les lignes et les colonnes vides des fichiers Excel en utilisant Interop et je le trouve utile.

mais j'ai des fichiers excel qui contiennent un petit ensemble de données mais beaucoup de lignes et de colonnes vides (de la dernière ligne non vide (ou colonne) à la fin de la feuille de travail)

j'ai essayé de boucler les rangées et les colonnes mais la boucle prend des heures.

j'essaie d'obtenir le dernier index de ligne et de colonne non vide pour que je puisse supprimer toute la plage vide dans une ligne

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

enter image description here

Note: j'essaie d'obtenir la dernière ligne contenant des données pour supprimer toutes les blancs supplémentaires (après cette ligne , ou colonne)

des suggestions?

29
demandé sur Hadi 2017-05-11 11:21:47

7 réponses

"1519110920 mise à jour" Update 1

si votre but est d'importer les données excel en utilisant c#, en supposant que vous avez identifié l'indice le plus utilisé dans votre feuille de travail (dans l'image que vous avez posté il est Col = 10, Ligne = 16) , vous pouvez convertir les indices maximum utilisés à la lettre donc il sera J16 et sélectionnez seulement la gamme utilisée en utilisant et OLEDBCommand

SELECT * FROM [Sheet1$A1:J16]

autrement, Je ne pense pas qu'il soit facile de trouver une méthode plus rapide.

vous pouvez vous référer à cet article pour convertir les index en alphabet et pour se connecter à excel en utilisant OLEDB:


Réponse Initiale

comme vous l'avez dit, vous avez commencé par la question suivante:

Et vous essayez de "obtenir de la dernière ligne contenant les données à supprimer tous des espaces supplémentaires (après cette ligne , ou colonne)"

donc en supposant que vous travaillez avec l'acceptation réponse (fournie par @JohnG ), de sorte que vous pouvez ajouter une ligne de code pour obtenir la dernière ligne utilisée et la colonne

les lignes vides sont stockées dans une liste d'entiers rowsToDelete

vous pouvez utiliser le code suivant pour obtenir les dernières lignes non vides avec un index plus petit que la dernière ligne vide

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

et si NonEmptyRows.Max() < rowsToDelete.Max() la dernière rangée non vide est NonEmptyRows.Max() sinon c'est worksheet.Rows.Count et il n'y a pas les lignes vides après la dernière utilisation.

la même chose peut être faite pour obtenir la dernière colonne non vide

le code est édité dans DeleteCols et DeleteRows fonctions:

    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();
        }
    }
12
répondu Hadi 2018-07-25 18:39:45

il y a plusieurs années, j'ai créé un exemple de code MSDN qui permet à un développeur d'obtenir la dernière ligne et colonne utilisée à partir d'une feuille de travail. Je l'ai modifié, j'ai placé tout le code nécessaire dans une bibliothèque de classe avec un formulaire windows à l'avant pour démontrer l'opération.

code sous-jacent utilise Microsoft.Bureau.Interop.Excel.

emplacement sur Microsoft One drive https://1drv.ms/u/s-oui.AtGAgKKpqdWjiEGdBzWDCSCZAMaM "151980920

ici J'obtiens la première feuille dans un fichier Excel, obtiens la dernière ligne utilisée et col et présente comme une adresse de cellule valide.

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

dans le projet de démonstration je reçois aussi toutes les feuilles pour un fichier excel, les présenter dans une boîte de liste. Sélectionnez un nom de feuille dans la boîte de liste et obtenez la dernière ligne et la colonne de cette feuille dans une adresse de cellule valide.

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

à première vue en ouvrant la solution à partir du lien ci-dessus, vous noterez qu'il y a beaucoup de code. Code est optimal et va libérer tous les objets immédiatement.

8
répondu Karen Payne 2017-05-14 15:48:10

J'utilise ClosedXml qui a des méthodes utiles 'Latusedrow' et 'Latusedcolumn'.

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

cette simple boucle supprimait 5000 lignes sur 10000 en 38 secondes. Pas vite, mais bien mieux que "Heures". Cela dépend du nombre de lignes/colonnes que vous traitez, bien sûr, ce que vous ne dites pas. Cependant, après d'autres tests avec 25000 lignes vides de 50000 il ne faudra environ 30 minutes pour supprimer les lignes vides dans une boucle. Clairement supprimer des lignes n'est pas un l'efficacité du processus.

une meilleure solution est de créer une nouvelle feuille et ensuite de copier les lignes que vous voulez garder.

Étape 1-Créer feuille avec 50000 lignes et 20 colonnes, chaque autre ligne et colonne est vide.

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

Étape 2-Copiez les lignes avec les données sur une nouvelle feuille. Ça prend 10 Secondes.

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

Étape 3 - ce serait de faire la même opération pour les colonnes.

7
répondu Phil 2017-05-14 14:20:48
  • pour obtenir la dernière colonne non vide/index de ligne la fonction Excel Find peut être utilisée. Voir GetLastIndexOfNonEmptyCell .
  • puis la Excel Worksheet Function CountA est utilisé pour déterminer si les cellules sont vides et union les lignes/colonnes entières à une rangée/colonnes gamme.
  • cette fourchette est supprimée immédiatement.

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

les deux fonctions pour obtenir des gammes vides de lignes/colonnes pourraient être refactorisées à une fonction, quelque chose comme ceci:

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

et puis il suffit de l'appeler:

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

Note: pour plus d'informations ont un regard par exemple sur cette question SO .

Modifier

Essayez simplement d'effacer le contenu de toutes les cellules qui sont après la dernière cellule utilisée.

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

    // Clear the columns
    sh.Range(sh.Cells(1, lastCol + 1), sh.Cells(1, Columns.Count)).EntireColumn.Clear();

    // Clear the remaining cells
    sh.Range(sh.Cells(lastRow + 1, 1), sh.Cells(Rows.Count, lastCol)).Clear();

}
7
répondu dee 2018-07-23 14:49:22

disons que la dernière cellule de coin avec des données est J16 - donc pas de données dans les colonnes K suivantes, ou dans les lignes 17 vers le bas. Pourquoi êtes-vous vraiment supprimer? Quel est le scénario et qu'essayez-vous de réaliser? Est-ce que ça clarifie notre formatage? Est est la compensation de nos formules qui montrent une chaîne vide?

dans tous les cas, boucler n'est pas la solution.

le code ci-dessous montre une façon d'utiliser la méthode Clear() de L'objet Range pour effacer tous les contenus et les formules et mise en forme d'un éventail. Alternativement si vous voulez vraiment les supprimer, vous pouvez utiliser la méthode Delete() pour supprimer une gamme rectangulaire entière en une seule touche. Sera beaucoup plus rapide que la boucle...

//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
4
répondu MacroMarc 2017-05-19 22:52:33

vous devriez être en mesure de trouver la dernière ligne et colonne non vide avec quelque chose de similaire à ceci:

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

C'est VB.NET mais ça devrait plus ou moins marcher. Qui retournera la ligne 16 et la colonne 10 (basé sur votre image ci-dessus). Ensuite, vous pouvez utiliser cela pour trouver la gamme que vous voulez supprimer tous dans une ligne.

2
répondu garroad_ran 2017-05-13 14:36:25

Semble que votre problème a été résolu par Microsoft. Regardez la gamme .Propriété CurrentRegion , qui retourne une gamme limitée par n'importe quelle combinaison de lignes et de colonnes en blanc. Il y a un inconvénient: cette propriété ne peut pas être utilisée sur une feuille de travail protégée .

pour plus de détails, s'il vous plaît voir: Comment trouver la région actuelle, gamme utilisée, dernière ligne et dernière colonne dans Excel avec la Macro VBA

certains de ses membres ont mentionné environ usedrange property , qui pourrait être utile aussi, mais la différence à CurrentRegion est que UsedRange retourne une gamme inclut n'importe quelle cellule qui a été utilisée.

Ainsi, si vous souhaitez obtenir un LAST(row) et LAST(column) occupé par des données, vous devez utiliser propriété finale avec XlDirection : xlToLeft et/ou xlUp .

Note #1:

Si vos données sont dans un format tabulaire, vous pouvez simplement trouver la dernière cellule, en utilisant:

lastCell = yourWorkseet.UsedRange.End(xlUp)
firstEmtyRow = lastCell.Offset(RowOffset:=1).EntireRow

Note #2:

Si vos données ne sont pas dans un format tabulaire, vous devez boucler la boucle à travers la collection de lignes et de colonnes pour trouver la dernière cellule non vide.

bon la chance!

1
répondu Maciej Los 2018-07-27 06:48:41