Программное получение последней заполненной строки Excel с использованием C #

Это распространенная проблема в Excel.

Вот некоторый код C #:

// Find the last real row
nInLastRow = oSheet.Cells.Find("*",System.Reflection.Missing.Value, 
System.Reflection.Missing.Value, System.Reflection.Missing.Value,    Excel.XlSearchOrder.xlByRows,Excel.XlSearchDirection.xlPrevious, false,System.Reflection.Missing.Value,System.Reflection.Missing.Value).Row;

// Find the last real column
nInLastCol = oSheet.Cells.Find("*", System.Reflection.Missing.Value,     System.Reflection.Missing.Value,System.Reflection.Missing.Value, Excel.XlSearchOrder.xlByColumns,Excel.XlSearchDirection.xlPrevious,    false,System.Reflection.Missing.Value,System.Reflection.Missing.Value).Column;

найден здесь

или с использованием SpecialCells

Excel.Range last = sheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
Excel.Range range = sheet.get_Range("A1", last);

[EDIT] Подобный threads:

• VB.NET - Чтение ВСЕГО содержимого файла excel
• Как получить диапазон занятых ячеек в таблице Excel ]

Ответ Прянка - это то, что сработало для меня ближе всего. Я добавил немного ближе к концу (.Row), так что я не просто возвращаю range, но integer.

int lastRow = wkSheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing).Row;

Единственный способ заставить его работать во ВСЕХ сценариях (кроме Защищенных листов):

Он поддерживает:

• Сканирование скрытых строк / столбцов

• Игнорирует отформатированные ячейки без данных / формулы

Код:

// Unhide All Cells and clear formats
sheet.Columns.ClearFormats();
sheet.Rows.ClearFormats();

// Detect Last used Row - Ignore cells that contains formulas that result in blank values
int lastRowIgnoreFormulas = sheet.Cells.Find(
                "*",
                System.Reflection.Missing.Value,
                InteropExcel.XlFindLookIn.xlValues,
                InteropExcel.XlLookAt.xlWhole,
                InteropExcel.XlSearchOrder.xlByRows,
                InteropExcel.XlSearchDirection.xlPrevious,
                false,
                System.Reflection.Missing.Value,
                System.Reflection.Missing.Value).Row;
// Detect Last Used Column  - Ignore cells that contains formulas that result in blank values
int lastColIgnoreFormulas = sheet.Cells.Find(
                "*",
                System.Reflection.Missing.Value,
                System.Reflection.Missing.Value,
                System.Reflection.Missing.Value,
                InteropExcel.XlSearchOrder.xlByColumns,
                InteropExcel.XlSearchDirection.xlPrevious,
                false,
                System.Reflection.Missing.Value,
                System.Reflection.Missing.Value).Column;

// Detect Last used Row / Column - Including cells that contains formulas that result in blank values
int lastColIncludeFormulas = sheet.UsedRange.Columns.Count;
int lastColIncludeFormulas = sheet.UsedRange.Rows.Count;

Для вопросов, связанных с объектной моделью Excel, часто сначала проще попробовать ее в VBA, а затем перевод на C # довольно тривиален.

В этом случае один из способов сделать это в VBA:

Worksheet.UsedRange.Row + Worksheet.UsedRange.Rows.Count - 1

ActiveSheet.UsedRange.Value возвращает массив двумерных объектов [строка, столбец]. Проверка длины обоих измерений предоставит индекс LastRow и индекс LastColumn. В приведенном ниже примере используется C #.

       Excel.Worksheet activeSheet;
		Excel.Range activeRange;


        public virtual object[,] RangeArray {
			get { return ActiveRange.Value; }
		}

		public virtual int ColumnCount {
			get { return RangeArray.GetLength(1); }
		}


		public virtual int RowCount {
			get { return RangeArray.GetLength(0); }
		}

		public virtual int LastRow {
			get { return RowCount; }
		}

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

Мое решение игнорирует полностью пустые строки и возвращает счетчик строк самого длинного столбца:

private static int GetLastRow(Worksheet worksheet)
    {
        int lastUsedRow = 1;
        Range range = worksheet.UsedRange;
        for (int i = 1; i < range.Columns.Count; i++)
        {
            int lastRow = range.Rows.Count;
            for (int j = range.Rows.Count; j > 0; j--)
            {
                if (lastUsedRow < lastRow)
                {
                    lastRow = j;
                    if (!String.IsNullOrWhiteSpace(Convert.ToString((worksheet.Cells[j, i] as Range).Value)))
                    {
                        if (lastUsedRow < lastRow)
                            lastUsedRow = lastRow;
                        if (lastUsedRow == range.Rows.Count)
                            return lastUsedRow - 1;
                        break;
                    }
                }
                else
                    break;
            }
        }
        return lastUsedRow;
    }

Для тех, кто использует метод SpecialCells (я не уверен насчет других), обратите внимание: если ваша последняя ячейка объединена , вы не сможете получить номер последней строки и столбца, используя Range.Row и Range.Column для получения последней строки и столбца в виде чисел. Вам нужно сначала Снять свой диапазон, а затем снова получить последнюю ячейку. Это стоило мне много.

private int[] GetLastRowCol(Ex.Worksheet ws)
    {
        Ex.Range last = ws.Cells.SpecialCells(Ex.XlCellType.xlCellTypeLastCell, Type.Missing);
        bool isMerged = (bool)last.MergeCells;
        if (isMerged)
        {
            last.UnMerge();
            last = ws.Cells.SpecialCells(Ex.XlCellType.xlCellTypeLastCell, Type.Missing);
        }
        return new int[2] { last.Row, last.Column };
    }

Как обсуждалось ранее, описанные выше методы (xlCellTypeLastCell и т. Д.) Не всегда обеспечивают ожидаемые результаты. Хотя нетрудно выполнить итерацию вниз по столбцу, проверяя значения, иногда вы можете обнаружить, что есть пустые ячейки или строки с данными, которые вы хотите рассмотреть в последующих строках. При непосредственном использовании Excel хороший способ найти последнюю строку - нажать пару раз клавишу CTRL + стрелка вниз (в результате вы получите строку 1048576 для листа XLSX), а затем нажать клавишу CTRL + стрелка вверх, которая выберет последнюю населенный пункт. Если вы сделаете это в Excel во время записи макроса, вы получите код для его репликации, а затем это всего лишь случай настройки его для C # с использованием библиотек Microsoft.Office.Interop.Excel. Например:

    private int GetLastRow()
    {
        Excel.Application ExcelApp;
        ExcelApp = new Excel.Application();

        ExcelApp.Selection.End(Excel.XlDirection.xlDown).Select();
        ExcelApp.Selection.End(Excel.XlDirection.xlDown).Select();
        ExcelApp.Selection.End(Excel.XlDirection.xlDown).Select();

        ExcelApp.Selection.End(Excel.XlDirection.xlUp).Select();

        return ExcelApp.ActiveCell.Row;
    }

Возможно, это не самое элегантное решение (я полагаю, вместо этого вы могли бы перейти непосредственно к последней строке таблицы непосредственно перед использованием XlUp), но оно кажется более надежным.