Как определить количество строк в Excel из 1С

Задача узнать количество строк в Excel-файле из 1С возникает при загрузке прайсов, выгрузках из сторонних систем и любой интеграции, где нужно заранее оценить объём данных: вывести прогресс обработки, проверить файл на пустоту или ограничить цикл чтения. Универсальной функции «посчитать строки» в платформе 1С нет — способ зависит от того, как именно вы работаете с файлом.

Основных подхода три: подключение к Excel через COM-объект Excel.Application, чтение файла в табличный документ средствами платформы и запрос к файлу через ADO. Каждый вариант даёт свой способ определения границ данных и имеет ограничения по скорости, лицензиям и работе на сервере.

Выбор способа: что влияет на решение

Прежде чем писать код, определите три условия. Первое — где выполняется код: на клиенте или на сервере 1С. COM-подключение к Excel возможно только там, где установлен сам Microsoft Excel, а на серверах его обычно не ставят по соображениям лицензирования и стабильности. Второе — формат файла: старый XLS, современный XLSX или файл, сохранённый как табличный документ MXL. Третье — что именно считать строкой: любую строку с данными или только заполненные строки в конкретной колонке.

Если Excel установлен на рабочем месте пользователя и обработка клиентская, COM-объект даёт максимум возможностей. Если код серверный или Excel отсутствует, файл XLSX можно прочитать в табличный документ методом Прочитать() и посчитать строки уже средствами платформы. ADO-подход подходит как промежуточный вариант, когда Excel не установлен, но нужен SQL-подобный запрос к данным.

Способ 1: количество строк через COM-объект Excel

Классический вариант для клиентских обработок. Создаётся объект Excel.Application, открывается книга, и у нужного листа запрашивается используемый диапазон. Свойство UsedRange.Rows.Count возвращает число строк в прямоугольнике, который Excel считает заполненным.

Попытка
Excel = Новый COMОбъект("Excel.Application");
Исключение
Сообщить("Excel не установлен: " + ОписаниеОшибки());
Возврат;
КонецПопытки;

Excel.Visible = Ложь;
Excel.DisplayAlerts = Ложь;

Книга = Excel.Workbooks.Open("C:\Данные\Прайс.xlsx");
Лист = Книга.Worksheets(1);

ВсегоСтрок = Лист.UsedRange.Rows.Count;
Сообщить("Строк с данными: " + ВсегоСтрок);

Книга.Close(Ложь);
Excel.Quit();
Excel = Неопределено;

У UsedRange есть известная особенность: Excel запоминает область, которая когда-либо содержала данные. Если пользователь вводил значения в тысячной строке, а потом удалил их клавишей Delete, UsedRange может по-прежнему включать эти строки, и счётчик окажется завышенным. Для более точной границы используют поиск последней заполненной ячейки.

ПоследняяСтрока = Лист.Cells.Find("*", Лист.Cells(1,1), , , 1, 2).Row;  // поиск последней непустой ячейки снизу вверх

Подсчёт строк по конкретной колонке

Если строки могут быть заполнены частично и нужно считать только записи с данными в ключевой колонке (например, с артикулом или наименованием), используйте функцию листа CountA — аналог экселевской СЧЁТЗ.

// Число непустых ячеек в колонке A (включая заголовок)
НепустыхЯчеек = Excel.WorksheetFunction.CountA(Лист.Columns(1));

// Строк данных без строки заголовка
СтрокДанных = НепустыхЯчеек - 1;

Метод корректно работает, только если в колонке нет пропусков внутри данных. Если пропуски возможны, надёжнее определить последнюю строку через Find или Cells(Rows.Count, 1).End(xlUp).Row и затем пройтись циклом с проверкой каждой строки.

Способ 2: чтение файла в табличный документ

Когда Excel на машине нет или код выполняется на сервере, файл XLSX можно открыть как табличный документ. Платформа умеет читать форматы XLSX, XLS (с ограничениями), ODS и собственный MXL без внешних программ.

ТабДок = Новый ТабличныйДокумент;
ТабДок.Прочитать("C:\Данные\Прайс.xlsx", СпособЧтенияЗначенийТабличногоДокумента.Значение);

Высота = ТабДок.ВысотаТаблицы();
Ширина = ТабДок.ШиринаТаблицы();
Сообщить("Строк: " + Высота + ", колонок: " + Ширина);

Метод ВысотаТаблицы() возвращает номер последней строки табличного документа, в которой есть данные или оформление. Как и UsedRange, он может завышать результат, если в файле есть отформатированные, но пустые строки. Для точного подсчёта пройдитесь циклом и проверяйте содержимое ключевой области:

РеальныхСтрок = 0;
Для НомерСтроки = 2 По ТабДок.ВысотаТаблицы() Цикл
ЗначениеЯчейки = ТабДок.Область(НомерСтроки, 1).Текст;
Если Не ПустаяСтрока(ЗначениеЯчейки) Тогда
РеальныхСтрок = РеальныхСтрок + 1;
КонецЕсли;
КонецЦикла;

Способ 3: подсчёт через ADO без установленного Excel

Третий вариант — обратиться к файлу как к источнику данных через ADO с провайдером Microsoft ACE OLEDB. Это позволяет выполнить SQL-запрос SELECT COUNT(*) и получить число строк, не открывая файл целиком. Провайдер должен быть установлен на машине, где выполняется код, и разрядность провайдера должна совпадать с разрядностью клиента или сервера 1С.

СтрокаПодключения = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Данные\Прайс.xlsx;"
+ "Extended Properties=""Excel 12.0 Xml;HDR=YES""";

Соединение = Новый COMОбъект("ADODB.Connection");
Соединение.Open(СтрокаПодключения);

Набор = Новый COMОбъект("ADODB.Recordset");
Набор.Open("SELECT COUNT(*) AS Количество FROM [Лист1$]", Соединение);

ВсегоСтрок = Набор.Fields("Количество").Value;
Сообщить("Строк данных: " + ВсегоСтрок);

Набор.Close();
Соединение.Close();

Параметр HDR=YES указывает, что первая строка — заголовок, поэтому COUNT(*) вернёт именно число строк данных. Имя листа в запросе пишется с символом доллара в квадратных скобках. Учтите, что ADO определяет тип колонки по первым строкам и может некорректно трактовать смешанные данные — для подсчёта это обычно не критично, но для последующего чтения значений стоит проверить результат на тестовом файле.

Проверка результата и типичные ошибки

После получения числа строк сверьте его с ожиданиями: откройте файл в Excel вручную, нажмите Ctrl+End — курсор встанет на последнюю ячейку используемого диапазона, и её номер строки должен совпасть с результатом UsedRange. Если цифры расходятся, вероятно, в файле есть «мусорные» области: бывшие данные, форматирование или скрытые объекты.

Ещё одна проверка — сравнить результат разных методов на одном файле. Если UsedRange.Rows.Count даёт 5000, а CountA по ключевой колонке — 4800, значит в файле есть 200 пустых или частично заполненных строк, и для загрузки надёжнее опираться на второе число.

Отдельно предупредим про освобождение COM-объектов. Каждый открытый через COM Excel остаётся в памяти до явного вызова Quit(). В тонком клиенте это приводит к накоплению процессов EXCEL.EXE у пользователя, на сервере — к утечке памяти серверного процесса. Всегда оборачивайте работу в попытку с гарантированным закрытием книги и приложения в ветке исключения.

Ограничения и нюансы разных сценариев

Несколько практических ограничений, о которых стоит помнить. Во веб-клиенте COM-объекты недоступны — там остаётся только передача файла на сервер и чтение в табличный документ. Файлы формата XLS (бинарный формат старых версий Excel) платформа читает в табличный документ с ограничениями, поэтому для них надёжнее COM или ADO. Защищённые паролем книги через COM открываются с передачей пароля в Workbooks.Open, а табличным документом не читаются вовсе.

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

Частые вопросы