Сколько строк в файле Excel: считаем средствами 1С 8.3

Быстрее всего узнать количество строк в Excel из 1С через COM: Лист.UsedRange.Row + Лист.UsedRange.Rows.Count - 1. Если Excel на компьютере не установлен, прочитайте файл в ТабличныйДокумент и возьмите свойство ВысотаТаблицы. Оба числа иногда завышены: Excel и 1С учитывают пустые строки, у которых когда-то было форматирование.

Если файл грузится в базу, одного числа мало: нужно отсечь пустой хвост, не оставить висящий EXCEL.EXE и не ждать минутами на выгрузке в сотни тысяч строк. Для каждой задачи есть свой приём.

Какой способ подсчёта строк выбрать

Выбор зависит от того, где выполняется код и есть ли там Microsoft Excel.

💡 Для обработки, которую запускает бухгалтер на своём компьютере, обычно хватает COM. Для фонового задания на сервере 1С выбирайте ТабличныйДокумент или XML: на сервере Excel чаще всего нет, а автоматизацию Office из служб Microsoft не поддерживает.

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

Схема простая: запускаем Excel невидимым, открываем книгу только для чтения, берём используемый диапазон листа и закрываем всё за собой.

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

	Excel = Неопределено;
	Книга = Неопределено;
	Результат = 0;

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

		// Open(Filename, UpdateLinks, ReadOnly)
		Книга = Excel.Workbooks.Open(ИмяФайла, 0, Истина);
		Лист = Книга.Worksheets(НомерЛиста);

		Диапазон = Лист.UsedRange;
		Результат = Диапазон.Row + Диапазон.Rows.Count - 1;

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

	Возврат Результат;

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

Почему не просто UsedRange.Rows.Count. Если данные начинаются с пятой строки, а первые четыре пустые, Rows.Count вернёт число строк диапазона, а не номер последней строки. Прибавка Row - 1 даёт именно номер последней строки листа.

Ещё несколько деталей, которые экономят время при отладке:

• 📌 Worksheets(1) — первый лист по порядку вкладок. По имени лист берут так: Книга.Worksheets("Остатки").
• 📌 Третий параметр Open = Истина открывает книгу только для чтения. Тогда файл, открытый у коллеги, не вызовет запрос «файл занят».
• 📌 DisplayAlerts = Ложь гасит диалоги Excel. Без этого невидимый Excel может ждать ответа на вопрос, которого никто не видит.
• 📌 Количество листов в книге — Книга.Worksheets.Count. По нему удобно пройти циклом и посчитать строки на каждом листе.

Последняя заполненная строка вместо «использованного диапазона»

UsedRange помнит всё, что когда-либо трогали: залитые цветом строки, границы, удалённые значения. В выгрузке из банк-клиента на 300 строк он легко вернёт 1 200. Поэтому для загрузки данных нужна последняя строка со значением.

Способ 1. Подъём снизу по ключевой колонке

Аналог Ctrl+↑ в Excel: встаём в самую нижнюю ячейку колонки и прыгаем вверх до первого значения.

xlUp = -4162;
ПоследняяСтрока = Лист.Cells(Лист.Rows.Count, 1).End(xlUp).Row;

Лист.Rows.Count равен 1 048 576 для xlsx и 65 536 для старого xls, константу писать не нужно. Минус способа: смотрит одну колонку. Если в колонке A есть пропуски в конце, а данные в колонке C идут дальше, строка будет занижена. Берите колонку, которая заполнена всегда: номер, артикул, ИНН.

Способ 2. Поиск последней непустой ячейки на всём листе

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

// Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection)
xlValues   = -4163;
xlPart     = 2;
xlByRows   = 1;
xlPrevious = 2;

Найдено = Лист.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious);
Если Найдено = Неопределено Тогда
	ПоследняяСтрока = 0; // лист пустой
Иначе
	ПоследняяСтрока = Найдено.Row;
КонецЕсли;

Если ничего не найдено, Excel возвращает Nothing, а в 1С оно приходит как Неопределено. Проверка обязательна, иначе обращение к .Row упадёт.

Способ 3. Последняя ячейка листа

Лист.Cells.SpecialCells(11).Row, где 11 — константа xlCellTypeLastCell. Это то же, что Ctrl+End. Результат совпадает с UsedRange и так же завышен форматированием, так что для загрузки он подходит хуже первых двух.

Подсчёт строк без установленного Excel: ТабличныйДокумент

Платформа 8.3 читает xls, xlsx и ods сама. Метод Прочитать доступен на сервере и в толстом клиенте, поэтому в управляемой форме файл сначала передают на сервер через временное хранилище, а уже там записывают во временный файл.

&НаСервереБезКонтекста
Функция КоличествоСтрокТабличнымДокументом(АдресВХранилище, Расширение)

	ИмяВременного = ПолучитьИмяВременногоФайла(Расширение); // "xlsx" без точки
	ДвоичныеДанные = ПолучитьИзВременногоХранилища(АдресВХранилище);
	ДвоичныеДанные.Записать(ИмяВременного);

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

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

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

	Возврат Высота;

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

ВысотаТаблицы — это номер последней строки, в которой есть хоть что-то, включая оформление. Цикл снизу вверх убирает такой хвост. Обычно он короткий, и цикл отрабатывает за доли секунды.

⚠️ Прочитать загружает только первый лист книги. Если нужные данные лежат на втором листе, используйте COM или разбор XML.

Число строк xlsx напрямую из XML

Файл xlsx — это ZIP-архив с XML внутри. Строки листа лежат в xl/worksheets/sheetN.xml, и посчитать их можно без Excel и без загрузки книги целиком. Для xls и xlsb способ не подходит: это двоичные форматы.

Внутри файла листа есть два источника ответа:

• 🔹 тег <dimension ref="A1:H1250"/> в начале — готовый диапазон, число после двоеточия и есть последняя строка;
• 🔹 теги <row r="1250"> внутри <sheetData> — по одному на каждую строку, где что-то есть.

dimension читается мгновенно, но ему не всегда можно верить: часть сторонних генераторов xlsx пишет туда A1 или не пишет тег вообще. Надёжнее взять наибольший атрибут r у <row>.

Функция КоличествоСтрокXLSX(ИмяФайла, ИмяЛистаXML = "sheet1.xml")

	Каталог = КаталогВременныхФайлов() + Строка(Новый УникальныйИдентификатор)
		+ ПолучитьРазделительПути();
	СоздатьКаталог(Каталог);

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

	Если НужныйЭлемент = Неопределено Тогда
		Zip.Закрыть();
		ВызватьИсключение "В файле нет листа " + ИмяЛистаXML;
	КонецЕсли;

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

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

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

	Чтение.Закрыть();
	УдалитьФайлы(Каталог);

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

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

Две ловушки, о которых узнают только на живых файлах:

1. sheet1.xml — не обязательно первая вкладка. Порядок вкладок задан в xl/workbook.xml (теги <sheet name="…" r:id="…">), а какой файл соответствует какому r:id, написано в xl/_rels/workbook.xml.rels. Если пользователь переставлял листы, первая вкладка может храниться в sheet3.xml.
2. <row> бывает у пустой строки. Excel сохраняет тег, если у строки задана высота или стиль, даже без ячеек <c>. Нужен точный результат — считайте только строки, внутри которых встретился хотя бы один <v> или <is> с текстом.

Подсчёт через ADO и провайдер ACE

Ещё один путь без Excel — драйвер Microsoft Access Database Engine. Он позволяет выполнить к листу SQL-запрос.

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

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

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

Для xls в строке подключения вместо Excel 12.0 Xml указывают Excel 8.0. Имя листа пишется со знаком $ в квадратных скобках. HDR=NO значит, что первая строка тоже считается данными; с HDR=YES шапка в подсчёт не попадёт.

Разрядность провайдера ACE должна совпадать с разрядностью процесса 1С: 64-битному серверу 1С нужен 64-битный ACE. Это самая частая причина, по которой код работает у разработчика и падает у клиента.

Ошибки при подсчёте и как их обработать

Любое обращение к внешнему файлу заворачивайте в Попытка … Исключение, а ресурсы закрывайте в обеих ветках, как в примере с COM выше.

Сообщение об ошибке пользователю лучше давать своё, понятное: «Не удалось открыть файл, закройте его в Excel и повторите». Исходный текст ОписаниеОшибки() пишите в журнал регистрации через ЗаписьЖурналаРегистрации, чтобы было что разбирать.

Большие файлы: как считать строки быстро

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

• ⚡ Не перебирайте ячейки через COM. Каждое обращение Лист.Cells(i, j).Value — межпроцессный вызов. Если после подсчёта нужны и сами данные, заберите диапазон одним вызовом: Массив = Лист.Range("A1:H" + Формат(ПоследняяСтрока, "ЧГ=0")).Value.Выгрузить();. Получится обычный массив 1С.
• ⚡ Склеивайте номер строки через Формат(…, "ЧГ=0"). Без этого число 12500 превратится в строку с неразрывным пробелом, и адрес диапазона будет ошибочным.
• ⚡ Для xlsx читайте XML потоково. ЧтениеXML идёт по файлу узел за узлом и не держит его в памяти. ПостроительDOM грузит документ целиком, и на листе в сотни мегабайт рабочий процесс упрётся в память.
• ⚡ ТабличныйДокумент — для средних файлов. Он хранит в памяти все ячейки с оформлением. Для фонового задания на сервере это сотни мегабайт на один файл; на очень больших книгах выбирайте XML или ADO.
• ⚡ Если нужен только размер, а не данные, ADO с COUNT(*) и тег dimension дают ответ без чтения ячеек.

💡 Посчитайте строки до загрузки и покажите пользователю: «В файле 48 320 строк, загрузка займёт несколько минут». Так человек не закроет форму на середине, решив, что программа зависла.

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