При импорте данных из файлов Excel в 1С программисты часто сталкиваются с неприятной ситуацией: визуально в файле находится всего несколько десятков заполненных строк (например, 46), но свойство ВысотаТаблицы упорно возвращает гораздо большее число (например, 100 или даже несколько тысяч). В результате этого в базу данных начинают загружаться пустые строки, «фантомные» данные или элементы с других листов книги Excel.
В этой статье мы подробно разберем, почему возникает эта проблема, как устроена работа с многолистовыми файлами Excel в платформе 1С, и рассмотрим несколько надежных способов получения реального количества заполненных строк.
Для начала выясним причину, по которой табличный документ считает пустые ячейки заполненными. Здесь работают два основных фактора:
Прочитать() платформа 1С честно переносит всю структуру файла, включая отформатированные, но визуально пустые ячейки.Прочитать() объекта ТабличныйДокумент по умолчанию считывает все листы файла Excel и объединяет их в один длинный документ, располагая их друг за другом. Если на первом листе у вас 46 строк, а на втором — еще 54 строки, то общая высота таблицы составит ровно 100 строк.Давайте пошагово разберем, как решить каждую из этих проблем и сделать загрузку данных быстрой и безошибочной.
Если в вашем файле Excel содержится несколько листов, а загрузить нужно только первый (или какой-то конкретный), необходимо использовать особенность платформы 1С. При чтении файла Excel каждый его лист автоматически преобразуется в именованную область внутри результирующего табличного документа. Имя этой области в точности соответствует названию листа в Excel (например, "Лист1" или "Sheet1").
Рассмотрим пример кода, который позволяет получить область конкретного листа и определить его границы независимо от других листов:
// Создаем новый табличный документ и считываем файл
ТабДок = Новый ТабличныйДокумент;
ТабДок.Прочитать(ПутьКФайлу, СпособЧтенияЗначенийТабличногоДокумента.Значение);
// Задаем имя интересующего нас листа
ИмяНужногоЛиста = "Лист1";
// Проверяем наличие листа в структуре областей табличного документа
Если ТабДок.Области.Найти(ИмяНужногоЛиста) <> Неопределено Тогда
// Получаем область конкретного листа
ОбластьЛиста = ТабДок.ПолучитьОбласть(ИмяНужногоЛиста);
// Теперь границы определяются строго в рамках выбранного листа!
ВысотаЛиста = ОбластьЛиста.ВысотаТаблицы;
ШиринаЛиста = ОбластьЛиста.ШиринаТаблицы;
Сообщить("Количество строк на листе '" + ИмяНужногоЛиста + "': " + ВысотаЛиста);
// Перебираем строки только этого листа
Для НомерСтроки = 1 По ВысотаЛиста Цикл
// Обработка данных строки
Артикул = ОбластьЛиста.Область(НомерСтроки, 1).Текст;
Если ЗначениеЗаполнено(Артикул) Тогда
// Ваша логика загрузки данных
КонецЕсли;
КонецЦикла;
Иначе
Сообщить("Лист с именем '" + ИмяНужногоЛиста + "' не найден в файле!");
EndЕсли;
Этот способ полностью решает проблему «склеивания» разных листов при чтении файла через штатный механизм 1С.
Даже если мы ограничили чтение одним листом, в конце таблицы все равно могут оставаться «фантомные» пустые строки, которые когда-то форматировались пользователем. Чтобы быстро и эффективно преобразовать табличный документ в чистую таблицу значений без пустых строк, лучше всего использовать объект ПостроительЗапроса.
Этот метод считается стандартом разработки в 1С, так как он работает в десятки раз быстрее ручного обхода ячеек в цикле и автоматически игнорирует неинформативные строки в конце документа.
Разберем этот изящный алгоритм по шагам:
ТабДок = Новый ТабличныйДокумент;
ТабДок.Прочитать(ПутьКФайлу, СпособЧтенияЗначенийТабличногоДокумента.Значение);
// Выбираем область для анализа (весь документ или конкретный лист)
ИмяЛиста = "Лист1";
Если ТабДок.Области.Найти(ИмяЛиста) <> Неопределено Тогда
ОбластьДляАнализа = ТабДок.ПолучитьОбласть(ИмяЛиста);
Иначе
ОбластьДляАнализа = ТабДок.Область();
EndЕсли;
// Используем ПостроительЗапроса для трансформации табличного документа в Таблицу Значений
Построитель = Новый ПостроительЗапроса;
Построитель.ИсточникДанных = Новый ОписаниеИсточникаДанных(ОбластьДляАнализа);
Построитель.ЗаполнитьНастройки();
Построитель.Выполнить();
// Выгружаем очищенные данные
ТаблицаДанных = Построитель.Результат.Выгрузить();
// Теперь в ТаблицеДанных содержатся только строки с реальными данными!
РеальноеКоличествоСтрок = ТаблицаДанных.Количество();
Сообщить("Действительное количество заполненных строк: " + РеальноеКоличествоСтрок);
Преимущество этого метода: Вы получаете готовую индексированную таблицу значений, с которой удобно работать средствами встроенного языка 1С, а все пустые строки внизу листа отсекаются автоматически.
Если вы по каким-то причинам не хотите использовать ПостроительЗапроса и предпочитаете классический обход ячеек циклом, можно применить простой математический прием. Вместо того чтобы перебирать строки сверху вниз, мы начнем обход с самого конца (с индекса ВысотаТаблицы) и будем двигаться вверх до тех пор, пока не встретим первую непустую ячейку в ключевой колонке (например, в колонке "Артикул" или "Наименование").
Проанализируем пример такой реализации:
ТабДок = Новый ТабличныйДокумент;
ТабДок.Прочитать(ПутьКФайлу, СпособЧтенияЗначенийТабличногоДокумента.Значение);
ВысотаТаблицы = ТабДок.ВысотаТаблицы;
РеальнаяВысота = 0;
КлючеваяКолонка = 1; // Номер колонки, которая обязательно должна быть заполнена в строке
// Запускаем обратный цикл
Для Счетчик = 0 По ВысотаТаблицы - 1 Цикл
ТекущийНомерСтроки = ВысотаТаблицы - Счетчик;
// Получаем значение ключевой ячейки и очищаем ее от пробелов
ЗначениеЯчейки = СокрЛП(ТабДок.Область(ТекущийНомерСтроки, КлючеваяКолонка).Текст);
Если ЗначениеЗаполнено(ЗначениеЯчейки) Тогда
// Как только встретили заполненную ячейку - это и есть наша реальная граница таблицы
РеальнаяВысота = ТекущийНомерСтроки;
Прервать;
КонецЕсли;
КонецЦикла;
Сообщить("Реальное количество заполненных строк в файле: " + РеальнаяВысота);
// Теперь мы можем безопасно запускать цикл обработки данных до РеальнаяВысота
Для НомерСтроки = 1 По РеальнаяВысота Цикл
// Загрузка данных...
КонецЦикла;
Этот алгоритм гарантирует, что вы не потратите время на обработку сотен пустых строк, которые появились в файле из-за случайного форматирования пользователем Excel.
Если вам требуется абсолютный контроль над файлом Excel (например, нужно точно знать количество листов, их имена или использовать сложные формулы), можно прибегнуть к использованию COM-объекта Excel.Application. Стоит учитывать, что данный метод работает только на операционных системах Windows при условии, что на компьютере (или сервере 1С, если код выполняется на сервере) установлен Microsoft Excel.
Посмотрим, как выглядит получение точного количества строк через COM-соединение:
Попытка
ExcelПриложение = Новый COMОбъект("Excel.Application");
Исключение
Сообщить("Не удалось запустить Microsoft Excel. Возможно, он не установлен.");
Возврат;
КонецПопытки;
// Открываем книгу в невидимом режиме
ExcelПриложение.Visible = Ложь;
Книга = ExcelПриложение.Workbooks.Open(ПутьКФайлу);
// Обращаемся строго к первому листу
Лист = Книга.Worksheets(1);
// Используем специальное свойство Excel для поиска последней заполненной ячейки
// Параметр 11 соответствует константе xlCellTypeLastCell
ПоследняяЯчейка = Лист.Cells(1, 1).SpecialCells(11);
КоличествоСтрок = ПоследняяЯчейка.Row;
Сообщить("Количество строк на первом листе через COM: " + КоличествоСтрок);
// Не забываем закрыть файл и освободить память!
Книга.Close(Ложь);
ExcelПриложение.Quit();
ExcelПриложение = Неопределено;
Использование COM-объекта дает стопроцентную точность определения заполненных областей, однако работает медленнее стандартного чтения табличного документа и имеет платформенные ограничения.
Для быстрой, стабильной и кроссплатформенной работы (в том числе под управлением Linux-серверов) рекомендуется придерживаться следующих правил при загрузке данных из Excel: