Obtenir l'index de la dernière colonne et de la dernière ligne non vide à partir d'Excel en utilisant Interop.

Question

J'essaie de supprimer toutes les lignes et colonnes vides supplémentaires d'un fichier Excel à l'aide de la bibliothèque Interop.

J'ai suivi cette question Méthode la plus rapide pour supprimer les lignes et colonnes vides des fichiers Excel en utilisant Interop et je trouve ça 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 (ou colonne) non vide à la fin de la feuille de calcul)

J'ai essayé de boucler sur les lignes et les colonnes mais la boucle prend des heures.

J'essaie d'obtenir l'index de la dernière ligne et colonne non vide afin de pouvoir supprimer toute la plage vide en une seule ligne.

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

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

Des suggestions ?

---

Note : Le code doit être compatible avec l'environnement de la tâche SSIS script.

Answer 1

Mise à jour 1

Si votre objectif est d'importer les données Excel à l'aide de c#, en supposant que vous avez identifié l'indice le plus utilisé dans votre feuille de calcul (dans l'image que vous avez postée c'est Col = 10 , Row = 16) vous pouvez convertir le maximum d'index utilisés en lettres de façon à ce que ce soit J16 et sélectionnez uniquement la plage utilisée en utilisant et OLEDBCommand

SELECT * FROM [Sheet1$A1:J16]

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

Vous pouvez vous référer à ces articles pour convertir les index en alphabet et pour vous connecter à Excel en utilisant OLEDB :

• Comment convertir un numéro de colonne (par exemple 127) en une colonne Excel (par exemple AA) ?
• Permettre à l'application VB.NET de convertir des fichiers Excel en tables de données

---

Réponse initiale

Comme vous l'avez dit, vous êtes parti de la question suivante :

• Méthode la plus rapide pour supprimer les lignes et colonnes vides des fichiers Excel en utilisant Interop

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

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

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 ligne non vide est NonEmptyRows.Max() Sinon, c'est worksheet.Rows.Count et il n'y a pas de ligne vide après la dernière ligne utilisée.

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

Answer 2

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 d'une feuille de calcul. Je l'ai modifié, j'ai placé tout le code nécessaire dans une bibliothèque de classes avec un formulaire frontal Windows pour faire la démonstration de l'opération.

Le code sous-jacent utilise Microsoft.Office.Interop.Excel.

Emplacement sur le lecteur Microsoft one https://1drv.ms/u/s!AtGAgKKpqdWjiEGdBzWDCSCZAMaM

Ici, je récupère la première feuille d'un fichier Excel, je récupère la dernière ligne et la dernière colonne utilisées et je les 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 récupère également toutes les feuilles d'un fichier Excel et je les présente dans une ListBox. Sélectionnez un nom de feuille dans la liste et obtenez la dernière ligne et 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, lorsque vous ouvrez la solution à partir du lien ci-dessus, vous remarquerez qu'il y a beaucoup de code. Le code est optimal et libère tous les objets immédiatement.

Answer 3

J'utilise ClosedXml qui possède des méthodes utiles 'LastUsedRow' et '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();

Cette simple boucle a supprimé 5000 des 10000 lignes en 38 secondes. Ce n'est pas rapide, mais c'est beaucoup mieux que des "heures". Cela dépend bien sûr du nombre de lignes/colonnes que vous traitez, ce que vous ne dites pas. Cependant, après d'autres tests avec 25000 lignes vides sur 50000, il faut environ 30 minutes pour supprimer les lignes vides dans une boucle. Il est clair que la suppression des lignes n'est pas un processus efficace.

Une meilleure solution consiste à créer une nouvelle feuille, puis à copier les lignes que vous souhaitez conserver.

Etape 1 - créer une feuille avec 50000 lignes et 20 colonnes, une ligne et une colonne sur deux 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 - copier les lignes avec les données dans une nouvelle feuille. Cette opération 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 - il s'agit de faire la même opération pour les colonnes.

Answer 4

• Pour obtenir l'index de la dernière colonne/ligne non vide, la fonction Excel Find peut être utilisé. Voir GetLastIndexOfNonEmptyCell .
• Ensuite, le Fonction de la feuille de calcul Excel CountA est utilisé pour déterminer si les cellules sont vides et syndicat l'ensemble des lignes/colonnes à une plage de lignes/colonnes.
• Ces plages sont définitivement supprimées d'un coup.

---

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 permettant d'obtenir des plages vides de lignes/colonnes pourraient être refactorisées en une seule 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 ensuite, appelez-le :

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, consultez par exemple le site suivant cette question SO .

Modifier

Essayez d'effacer simplement le contenu de toutes les cellules qui se trouvent 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();

}

Answer 5

Disons que la dernière cellule d'angle contenant des données est J16 - donc aucune donnée dans les colonnes K et suivantes, ou dans les lignes 17 et suivantes. Pourquoi les supprimez-vous réellement ? Quel est le scénario et qu'essayez-vous d'obtenir ? Efface-t-il notre mise en forme ? Efface-t-il nos formules qui affichent une chaîne vide ?

Dans tous les cas, le bouclage 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 tout le contenu, les formules et le formatage d'une plage. Alternativement, si vous voulez vraiment les supprimer, vous pouvez utiliser la méthode Delete() pour supprimer une plage rectangulaire entière en une seule fois. Ce sera beaucoup plus rapide que de faire des boucles...

//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