Получить последний непустой индекс столбца и строки из excel с помощью Interop


Я пытаюсь удалить все лишние пустые строки и столбцы из файла excel с помощью библиотеки Interop.

Я следовал этому вопросу Самый быстрый метод удаления пустых строк и столбцов из файлов Excel с помощью Interop, и я нахожу его полезным.

Но у меня есть файлы excel, которые содержат небольшой набор данных, но много пустых строк и столбцов (от последней непустой строки (или столбца) до конца листа)

Я попробовал перебирать строки и столбцы, но цикл занимает несколько часов.

Я пытаюсь получить последний непустой индекс строки и столбца, чтобы я мог удалить весь пустой диапазон в одной строке

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

Введите описание изображения здесь

примечание: Я пытаюсь получить последнюю строку, содержащую данные, чтобы удалить все лишние пробелы (после этой строки или столбца)

Есть предложения?

7 29

7 ответов:

Обновление 1

Если ваша цель состоит в импорте данных excel с помощью c#, предполагая, что вы определили самый высокий используемый индекс в вашем листе (на опубликованном изображении он Col = 10, Row = 16) , вы можете преобразовать максимально используемые индексы в буквы, так что это будет J16 и выбрать только используемый диапазон с помощью и OLEDBCommand

SELECT * FROM [Sheet1$A1:J16]
Кроме того, я не думаю, что легко найти более быстрый метод.

вы можете обратиться к этой статье, чтобы преобразовать индексы в алфавит и подключиться к excel с помощью OLEDB:


Первоначальный Ответ

Как вы сказали, вы начали со следующего вопроса:

И вы пытаетесь это сделать. " получить последнюю строку, содержащую данные, чтобы удалить все лишние пробелы (после этой строки или столбца)"

Итак, предполагая, что вы работаете с ответом 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 столбцами, все остальные строки и столбцы пусты.
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;
    }
}
Шаг 2-скопируйте строки с данными на новый лист. Это займет 10 секунд.
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();
Шаг 3-это будет делать ту же операцию для столбцов.
  • для получения последнего непустого индекса столбца/строки можно использовать функцию 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:
Если ваши данные не являются в табличном формате, вам нужно перебирать коллекцию строк и столбцов, чтобы найти последнюю непустую ячейку.

Удачи!