Сколько строк в файле Excel: считаем из кода 1С через COM, табличный документ, ADO и XML

Чтобы определить количество строк в Excel из 1С, откройте книгу через Новый COMОбъект("Excel.Application") и возьмите номер последней строки: Лист.UsedRange.Row + Лист.UsedRange.Rows.Count - 1. Без установленного Excel то же самое даёт ТабличныйДокумент.Прочитать() и свойство ВысотаТаблицы.

Частая ошибка новичков — писать Лист.Rows.Count. Это не число заполненных строк, а размер листа: 1 048 576 для xlsx и 65 536 для старого xls. Цикл до такого «конца» файла будет работать минутами и молотить пустоту.

Способов четыре, все для платформы 8.3 и не зависят от конфигурации: код одинаково работает в БП, ЗУП, УТ или в собственной внешней обработке.

1. Количество строк через COM-объект Excel.Application

Классический способ. 1С запускает Excel в фоне, открывает книгу и спрашивает у него размер данных. Нужен установленный Microsoft Excel на том компьютере, где выполняется код: на клиенте или на сервере 1С.

Функция КоличествоСтрокExcel(ПутьКФайлу, НомерЛиста = 1)

	Excel = Неопределено;
	Книга = Неопределено;

	Попытка
		Excel = Новый COMОбъект("Excel.Application");
		Excel.Visible = Ложь;
		Excel.DisplayAlerts = Ложь;

		// 0 — не обновлять связи, Истина — только чтение
		Книга = Excel.Workbooks.Open(ПутьКФайлу, 0, Истина);
		Лист = Книга.Worksheets(НомерЛиста);

		Диапазон = Лист.UsedRange;
		ПоследняяСтрока = Диапазон.Row + Диапазон.Rows.Count - 1;

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

		Возврат ПоследняяСтрока;

	Исключение
		ТекстОшибки = ОписаниеОшибки();
		Если Книга <> Неопределено Тогда
			Попытка Книга.Close(Ложь); Исключение КонецПопытки;
		КонецЕсли;
		Если Excel <> Неопределено Тогда
			Попытка Excel.Quit(); Исключение КонецПопытки;
		КонецЕсли;
		ВызватьИсключение ТекстОшибки;
	КонецПопытки;

КонецФункции

Почему не просто Диапазон.Rows.Count: UsedRange начинается не обязательно с первой строки. Если в файле три пустые строки сверху, а данные с A4 по A100, то Rows.Count вернёт 97, а последняя строка — 100. Сложение с Диапазон.Row даёт именно номер последней строки, до которой нужен цикл.

Два других запроса к Excel

• 📍 Последняя заполненная ячейка в столбце. Лист.Cells(Лист.Rows.Count, 1).End(-4162).Row — аналог нажатия Ctrl + ↑ из самого низа столбца A. Число -4162 — константа xlUp, в 1С её пишут числом.
• 📍 Последняя ячейка листа. Лист.Cells.SpecialCells(11).Row, где 11 — xlCellTypeLastCell. Это то же, что Ctrl + End в Excel.
• 📍 Число непустых ячеек столбца. Excel.WorksheetFunction.CountA(Лист.Columns(1)) считает заполненные значения, а не номер строки. Пропуски в середине уменьшают результат.

Номер листа можно заменить на имя: Книга.Worksheets("Остатки"). Сколько листов в книге, скажет Книга.Worksheets.Count.

2. Пустые строки и UsedRange: почему число строк больше реального

UsedRange — это не «где есть данные», а «чего касался пользователь». В диапазон попадают строки, где:

• 🎨 задан формат, заливка или граница, даже без значения;
• 🧹 текст удалили клавишей Delete, но формат остался;
• 📏 менялась высота строки;
• 💬 есть примечание.

Типичная картина: выгрузка из банка на 250 строк, а UsedRange говорит 1 400. Кто-то протянул заливку до 1 400-й строки и потом стёр значения.

Как получить число строк с данными

Надёжный способ — опорный столбец. Выберите колонку, которая заполнена всегда: артикул, ИНН, дата документа. Затем:

1. Возьмите End(-4162).Row по этому столбцу — это последняя строка с данными.
2. Если опорного столбца нет, идите снизу вверх от UsedRange и проверяйте строку целиком: Excel.WorksheetFunction.CountA(Лист.Rows(НомерСтроки)) > 0.
3. Вычтите строки заголовка, если нужен именно счётчик записей, а не номер последней строки.

Скрытые строки UsedRange тоже считает. Если нужны только видимые, используйте Лист.UsedRange.Columns(1).SpecialCells(12).Count, где 12 — xlCellTypeVisible. Когда видимых ячеек нет, Excel выбрасывает ошибку, поэтому вызов оборачивают в Попытка.

3. Подсчёт строк Excel без запуска Excel: табличный документ

С платформы 8.3.6 табличный документ умеет читать xlsx сам. Excel на компьютере не нужен, COM не нужен, код работает и на сервере под Linux.

ТабДок = Новый ТабличныйДокумент;
ТабДок.Прочитать(ПутьКФайлу, СпособЧтенияЗначенийТабличногоДокумента.Значение);
Высота = ТабДок.ВысотаТаблицы;
Ширина = ТабДок.ШиринаТаблицы;

ВысотаТаблицы ведёт себя как UsedRange: строки с одним лишь форматом тоже считаются. Отрезаем хвост пустых строк снизу:

Стр = ТабДок.ВысотаТаблицы;
Ширина = ТабДок.ШиринаТаблицы;

Пока Стр > 0 Цикл
	ЕстьДанные = Ложь;
	Для Кол = 1 По Ширина Цикл
		Если ЗначениеЗаполнено(СокрЛП(ТабДок.Область(Стр, Кол, Стр, Кол).Текст)) Тогда
			ЕстьДанные = Истина;
			Прервать;
		КонецЕсли;
	КонецЦикла;
	Если ЕстьДанные Тогда
		Прервать;
	КонецЕсли;
	Стр = Стр - 1;
КонецЦикла;

Сообщить("Строк с данными: " + Формат(Стр, "ЧГ=0; ЧН=0"));

Где выполнять код в управляемой форме

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

1. На клиенте файл передают в хранилище: НачатьПомещениеФайлаНаСервер или НачатьПомещениеФайла — зависит от версии платформы.
2. На сервере получают ДвоичныеДанные из временного хранилища по адресу.
3. Записывают их во временный файл: ДД.Записать(ПолучитьИмяВременногоФайла("xlsx")).
4. Читают табличным документом и удаляют временный файл через УдалитьФайлы.

4. Количество строк из xlsx как из архива: dimension и row

Файл xlsx — это zip-архив с XML внутри. Лист лежит в xl/worksheets/sheet1.xml, а в его начале есть тег <dimension ref="A1:H250"/>. Цифра после двоеточия и есть последняя строка.

Метод самый быстрый: файл не загружается в табличный документ целиком, работают только штатные объекты ЧтениеZipФайла и ЧтениеXML.

Функция ПоследняяСтрокаXLSX(ПутьКФайлу)

	Каталог = ПолучитьИмяВременногоФайла("");
	СоздатьКаталог(Каталог);

	Zip = Новый ЧтениеZipФайла(ПутьКФайлу);
	ЭлементЛиста = Неопределено;
	Для Каждого Элемент Из Zip.Элементы Цикл
		Если НРег(СтрЗаменить(Элемент.ПолноеИмя, "\", "/")) = "xl/worksheets/sheet1.xml" Тогда
			ЭлементЛиста = Элемент;
			Прервать;
		КонецЕсли;
	КонецЦикла;

	Если ЭлементЛиста = Неопределено Тогда
		Zip.Закрыть();
		УдалитьФайлы(Каталог);
		ВызватьИсключение "В книге не найден лист sheet1.xml";
	КонецЕсли;

	Zip.Извлечь(ЭлементЛиста, Каталог, РежимВосстановленияПутейФайловZIP.НеВосстанавливать);
	Zip.Закрыть();

	Чтение = Новый ЧтениеXML;
	Чтение.ОткрытьФайл(Каталог + ПолучитьРазделительПути() + ЭлементЛиста.Имя);

	ПоследняяСтрока = 0;
	Пока Чтение.Прочитать() Цикл
		Если Чтение.ТипУзла = ТипУзлаXML.НачалоЭлемента
			И Чтение.ЛокальноеИмя = "row" Тогда
			ПоследняяСтрока = Число(Чтение.ЗначениеАтрибута("r"));
		КонецЕсли;
	КонецЦикла;
	Чтение.Закрыть();

	УдалитьФайлы(Каталог);
	Возврат ПоследняяСтрока;

КонецФункции

Код берёт номер последнего тега <row>, а не dimension. Причина: dimension пишет программа, создавшая файл, и некоторые генераторы отчётов оставляют его пустым или неверным. Атрибут r у <row> надёжнее.

Две оговорки:

• 📄 sheet1.xml — первый лист по порядку создания, а не обязательно первый по вкладкам. Соответствие имён и файлов хранится в xl/workbook.xml и xl/_rels/workbook.xml.rels.
• 🧱 Тег <row> пишется и для отформатированной пустой строки. Совсем пустых строк в XML нет вообще, поэтому число тегов <row> может быть меньше номера последней строки.

Для xls (формат 97–2003) этот способ не подходит: там двоичный файл, не архив.

5. Подсчёт строк запросом COUNT через ADO

Если на машине стоит Microsoft Access Database Engine (провайдер Microsoft.ACE.OLEDB.12.0), лист Excel можно опросить как таблицу базы данных. Excel при этом не запускается.

Соединение = Новый COMОбъект("ADODB.Connection");
Соединение.Open("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ПутьКФайлу
	+ ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";");

Набор = Соединение.Execute("SELECT COUNT(*) FROM [Остатки$]");
КоличествоЗаписей = Набор.Fields(0).Value;

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

Что важно знать:

• 🔢 При HDR=YES первая строка считается заголовком и в COUNT(*) не входит.
• 🔢 COUNT(*) считает все строки диапазона, включая пустые в середине. COUNT([Артикул]) — только строки, где колонка заполнена. При HDR=NO колонки называются F1, F2 и так далее.
• 📑 Имя листа пишут в квадратных скобках со знаком $ на конце. Список листов отдаёт Соединение.OpenSchema(20).
• 🧩 Для xls в строке подключения вместо Excel 12.0 Xml пишут Excel 8.0.

6. Сравнение методов: какой выбрать под задачу

7. Ошибки при подсчёте строк Excel из 1С

8. Как ускорить подсчёт строк в сотнях файлов

Массовый анализ — например, проверка папки с выгрузками перед загрузкой — упирается не в подсчёт, а в накладные расходы.

1. Один экземпляр Excel на всю пачку. Создайте COMОбъект один раз, в цикле открывайте и закрывайте книги, Quit() — после последнего файла. Запуск Excel стоит дороже, чем открытие книги.
2. Отключите лишнее. Excel.ScreenUpdating = Ложь, Excel.EnableEvents = Ложь, книга только на чтение и без обновления связей.
3. Не читайте ячейки по одной. Каждое обращение к Cells — это вызов через COM. Если после подсчёта нужны данные, заберите диапазон целиком: Лист.Range("A1:H" + Формат(ПоследняяСтрока, "ЧГ=0")).Value вернёт массив COMSafeArray, а его метод Выгрузить() даст обычный массив 1С.
4. Для «только посчитать» уходите от Excel. Разбор архива из раздела 4 не загружает значения и не тратит память на табличный документ.
5. Длинные проверки — в фоновое задание. Тогда форма не замирает, а сервер не обрывает вызов по таймауту.

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