Ответ 1

Обновление 1

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

SELECT * FROM [Sheet1$A1:J16]

Иначе, я не думаю, что легко найти более быстрый метод.

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

• Как преобразовать номер столбца (например, 127) в столбец Excel (например, AA)
• Разрешение приложению VB.NET преобразовывать файлы Excel в Datatable

Начальный ответ

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

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

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

Предполагая, что вы работаете с ответом на принятие (предоставленным @JohnG), вы можете добавить строку кода, чтобы получить последнюю использованную строку и столбец

Пустые строки хранятся в списке целых чисел rowsToDelete

Вы можете использовать следующий код, чтобы получить последние непустые строки с индексом меньше, чем последняя пустая строка

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

И если NonEmptyRows.Max() < rowsToDelete.Max() последний непустой строка NonEmptyRows.Max() Else это 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 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 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();
        }
    }

Ответ 2

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

В базовом коде используется Microsoft.Office.Interop.Excel.

Расположение на одном диске Microsoft 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, представляя их в ListBox. Выберите имя листа из списка и получите последнюю страницу и столбец в действительном адресе ячейки.

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

На первый взгляд, открыв решение по ссылке выше, вы заметите, что есть много кода. Код является оптимальным и немедленно освободит все объекты.

Ответ 3

Я использую 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 минут, чтобы удалить пустые строки в цикле. Четкое удаление строк не является эффективным процессом.

Лучшее решение - создать новый лист, а затем скопировать строки, которые вы хотите сохранить.

Шаг 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 - это будет делать ту же операцию для столбцов.

Ответ 4

• Для получения последнего непустого индекса столбца/строки может быть использована функция Excel Find. Смотрите GetLastIndexOfNonEmptyCell.
• Затем функция листа Excel 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();

Примечание: для получения дополнительной информации посмотрите, например, на этот вопрос SO.

редактировать

Попробуйте просто очистить содержимое всех ячеек, которые находятся после последней использованной ячейки.

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

}

Ответ 5

Скажем, последняя угловая ячейка с данными - J16 - поэтому никаких данных в столбцах K дальше или в строках 17 вниз. Почему вы фактически удаляете их? Каков сценарий и чего вы пытаетесь достичь? Это очистка нашего форматирования? Is очищает наши формулы, которые показывают пустую строку?

В любом случае, цикл не подходит.

В приведенном ниже коде показан способ использования метода 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

Ответ 6

Вы должны найти последнюю непустую строку и столбец с чем-то похожим на это:

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

Это VB.NET, но он должен работать более или менее. Это вернет Row 16 и Column 10 (в зависимости от вашего изображения выше). Затем вы можете использовать это, чтобы найти диапазон, который вы хотите удалить, в одной строке.

Ответ 7

Кажется, что ваша проблема была решена Microsoft. Взгляните на свойство Range.CurrentRegion, которое возвращает диапазон, ограниченный любой комбинацией пустых строк и пустых столбцов. Есть одно неудобство: это свойство нельзя использовать на защищенном листе.

Дополнительные сведения см. В разделе " Как найти текущий регион, используемый диапазон, последнюю строку и последний столбец в Excel с помощью макроса VBA".

Некоторые члены SO упоминали о свойстве UsedRange, которое также может быть полезно, но отличие от CurrentRegion состоит в том, что UsedRange возвращает диапазон, включающий любую ячейку, которая когда-либо использовалась.
Итак, если вы хотите, чтобы LAST(row) и LAST(column) заняты данными, вам нужно использовать свойство End с XlDirection: xlToLeft и/или xlUp.

Примечание № 1:
Если ваши данные представлены в табличном формате, вы можете просто найти последнюю ячейку, используя:

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

Заметка 2:
Если ваши данные не представлены в табличном формате, вам нужно перебрать коллекцию строк и столбцов, чтобы найти последнюю непустую ячейку.

Удачи!