Получить последний непустой индекс столбца и строки из excel с помощью Interop
Я пытаюсь удалить все лишние пустые строки и столбцы из файла excel с помощью библиотеки Interop.
Я следовал этому вопросу Самый быстрый метод удаления пустых строк и столбцов из файлов Excel с помощью Interop, и я нахожу его полезным.
Но у меня есть файлы excel, которые содержат небольшой набор данных, но много пустых строк и столбцов (от последней непустой строки (или столбца) до конца листа)
Я попробовал перебирать строки и столбцы, но цикл занимает несколько часов.
Я пытаюсь получить последний непустой индекс строки и столбца, чтобы я мог удалить весь пустой диапазон в одной строке
XlWks.Range("...").EntireRow.Delete(xlShiftUp)
примечание: Я пытаюсь получить последнюю строку, содержащую данные, чтобы удалить все лишние пробелы (после этой строки или столбца)
Есть предложения?
7 ответов:
Обновление 1
Если ваша цель состоит в импорте данных excel с помощью c#, предполагая, что вы определили самый высокий используемый индекс в вашем листе (на опубликованном изображении он Col = 10, Row = 16) , вы можете преобразовать максимально используемые индексы в буквы, так что это будет
J16
и выбрать только используемый диапазон с помощью иOLEDBCommand
Кроме того, я не думаю, что легко найти более быстрый метод.SELECT * FROM [Sheet1$A1:J16]
вы можете обратиться к этой статье, чтобы преобразовать индексы в алфавит и подключиться к excel с помощью OLEDB:
- Как преобразовать номер столбца (напр. 127) в колонку excel (напр. АА)
- позволяя VB.NET приложение для преобразования файлов Excel в Datatable
Первоначальный Ответ
Как вы сказали, вы начали со следующего вопроса:И вы пытаетесь это сделать. " получить последнюю строку, содержащую данные, чтобы удалить все лишние пробелы (после этой строки или столбца)"
Итак, предполагая, что вы работаете с ответом accept (предоставленным @JohnG ), поэтому вы можете добавить некоторую строку кода, чтобы получить последнюю используемую строку и столбец
Пустые строки хранятся в списке целых чисел
rowsToDelete
Вы можете использовать следующий код, чтобы получить последние непустые строки с индексом меньше, чем последняя пустая строка
List<int> NonEmptyRows = Enumerable.Range(1, rowsToDelete.Max()).ToList().Except(rowsToDelete).ToList();
И если
NonEmptyRows.Max() < rowsToDelete.Max()
последняя непустая строка-этоNonEmptyRows.Max()
, иначе этоworksheet.Rows.Count
, и после последней используемой строки нет пустых строк.то же самое можно сделать, чтобы получить последний непустой столбец
Код редактируется в функциях
DeleteCols
и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(); } }
Несколько лет назад я создал пример кода MSDN, который позволяет разработчику получить последнюю используемую строку и столбец из рабочего листа. Я изменил его, поместил весь необходимый код в библиотеку классов с Windows form front end для демонстрации операции.
Базовый код использует Microsoft.Офис.Взаимодействие.Превосходить.
Расположение на диске Microsoft one https://1drv.ms/u/s! AtGAgKKpqdWjiEGdBzWDCSCZAMaM
Здесь я получаю первый лист в файле Excel, получаю последнюю использованную строку и col и представить в качестве действительного адреса ячейки.
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
В рамках демонстрационного проекта я также получаю все листы для файла excel, представляю их в виде списка. Выберите имя листа из списка и получите последнюю строку и столбец этого листа в допустимом адресе ячейки.
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
На первый взгляд при открытии решения по ссылке выше вы заметите, что там много кода. Код является оптимальным и немедленно освободит все объекты.
Я использую ClosedXml, который имеет полезные методы "LastUsedRow" и "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();
Этот простой цикл удалил 5000 из 10000 строк за 38 секунд. Не быстро, но намного лучше, чем "часы". Это зависит от того, с каким количеством строк/столбцов вы имеете дело, конечно, что вы не говорите. Однако после дальнейших тестов с 25000 пустыми строками из 50000 требуется около 30 минут, чтобы удалить пустые строки в цикле. Ясно, что удаление строк не является эффективным процессом.
A лучшее решение-создать новый лист, а затем скопировать строки, которые вы хотите сохранить.
Шаг 1-Создайте лист с 50000 строками и 20 столбцами, все остальные строки и столбцы пусты.Шаг 2-скопируйте строки с данными на новый лист. Это займет 10 секунд.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; } }
Шаг 3-это будет делать ту же операцию для столбцов.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();
- для получения последнего непустого индекса столбца/строки можно использовать функцию Excel
Find
. СмотритеGetLastIndexOfNonEmptyCell
.- затем функция листа Excel
CountA
используется для определения пустоты ячеек и объединения всех строк / столбцов в один диапазон строк/столбцов.- эти диапазоны удаляются окончательно сразу.
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; }
Две функции для получения пустых диапазонов строк / столбцов могут быть преобразованы в одну функцию, что-то вроде это:
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; }
А потом просто назовите его:
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();
Примечание: Для получения дополнительной информации посмотрите, например, на этот так вопрос .
Edit
Попробуйте просто очистить содержимое всех ячеек, которые находятся после последней используемой ячейки.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(); }
Предположим, что последняя угловая ячейка с данными - это J16, поэтому нет данных в Столбцах K вперед или в строках 17 вниз. Почему вы на самом деле удаляете их? Каков сценарий и чего вы пытаетесь достичь? Это очищает наше форматирование? Является ли очистка наших формул, которые показывают пустую строку?
В любом случае, зацикливание-это не путь.
Приведенный ниже код показывает способ использования метода Clear() объекта Range для очистки всего содержимого, формул и форматирования из диапазона. Кроме того, если вы действительно хотите удалить их, вы можете использовать метод Delete (), чтобы удалить весь прямоугольный диапазон одним ударом. Будет намного быстрее, чем Петля...
//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
Вы должны быть в состоянии найти последнюю непустую строку и столбец с чем-то подобным этому:
with m_XlWrkSheet lastRow = .UsedRange.Rows.Count lastCol = .UsedRange.Columns.Count end with
Это VB.NET, но это должно более или менее работать. Это вернет строку 16 и столбец 10 (на основе вашего изображения выше). Затем вы можете использовать это, чтобы найти диапазон, который вы хотите удалить все в одной строке.
Похоже, что ваша проблема была решена корпорацией Майкрософт. Взгляните на диапазон .CurrentRegion свойство , которое возвращает диапазон, ограниченный любой комбинацией пустых строк и пустых столбцов. Есть одно неудобство: это свойство нельзя использовать на защищенном листе.
Дополнительные сведения см. В разделе: Как найти текущую область, используемый диапазон, последнюю строку и последний столбец в Excel с помощью макроса VBA
Некоторые члены отметили о свойстве UsedRange, которое тоже может быть полезно, но отличие от
CurrentRegion
состоит в том, чтоUsedRange
возвращает диапазон, включающий любую ячейку, которая когда-либо использовалась.
Итак, если вы хотите получитьLAST(row)
иLAST(column)
занятые данными, вы должны использовать End свойство сXlDirection
:xlToLeft
и/илиxlUp
.Примечание #1:
Если ваши данные представлены в табличном формате, вы можете просто найти последнюю ячейку, используя:lastCell = yourWorkseet.UsedRange.End(xlUp) firstEmtyRow = lastCell.Offset(RowOffset:=1).EntireRow
Примечание #2:
Если ваши данные не являются в табличном формате, вам нужно перебирать коллекцию строк и столбцов, чтобы найти последнюю непустую ячейку.Удачи!