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)
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?
7 réponses
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:
- comment convertir un numéro de colonne (par ex. 127) dans une colonne excel (p. ex. AA)
- permettant VB.NET application pour convertir des fichiers Excel à Datatable
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();
}
}
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.
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.
- pour obtenir la dernière colonne non vide/index de ligne la fonction Excel
Find
peut être utilisée. VoirGetLastIndexOfNonEmptyCell
. - 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();
}
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
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.
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!