Как определить начало и конец таблицы при загрузке данных из Excel в 1С?

← На главную

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

Способ 1: Использование COM-соединения и поиск по признакам

Самый гибкий метод — это прямое управление приложением Microsoft Excel через COM-объект. Разберем по шагам, как найти границы данных. Сначала нам нужно определить общее количество строк, которые Excel считает заполненными. Важно учитывать версию установленного офиса, так как методы обращения к ячейкам могут отличаться.

Проанализируем пример кода для определения границ листа:


// Создаем объект Excel
Excel = Новый COMОбъект("Excel.Application");
Книга = Excel.Workbooks.Open(ПутьКФайлу);
Лист = Книга.Sheets(1);

// Определим версию для корректного подсчета строк
Версия = Лев(Excel.Version, Найти(Excel.Version, ".") - 1);

Если Версия = "8" Тогда
    // Для старых версий Excel
    ВсегоСтрок = Лист.Cells.CurrentRegion.Rows.Count;
    ВсегоКолонок = Макс(Лист.Cells.CurrentRegion.Columns.Count, 10);
Иначе
    // Для современных версий (используем специальную ячейку конца диапазона)
    ВсегоСтрок = Лист.Cells(1, 1).SpecialCells(11).Row;
    ВсегоКолонок = Лист.Cells(1, 1).SpecialCells(11).Column;
КонецЕсли;

Однако SpecialCells(11) часто возвращает не ту строку, где заканчиваются данные, а последнюю ячейку, в которой когда-либо было форматирование. Поэтому выясним причину, по которой данные могут считываться некорректно: это «фантомные» строки. Чтобы найти реальное начало таблицы, мы применим алгоритм поиска ключевого слова в колонке «№ п/п» или «Номенклатура».

Рассмотрим подробнее логику поиска шапки:


НомерСтрокиНачала = 0;
Для НомерСтроки = 1 По ВсегоСтрок Цикл
    ЗначениеЯчейки = СокрЛП(Лист.Cells(НомерСтроки, 1).Value);
    // Ищем строку, где начинается нумерация или заголовок
    Если ЗначениеЯчейки = "1" Или Найти(ВРег(ЗначениеЯчейки), "№ П/П") > 0 Тогда
        НомерСтрокиНачала = НомерСтроки;
        Прервать;
    КонецЕсли;
КонецЦикла;

Способ 2: Определение конца таблицы «по содержанию»

Когда начало таблицы найдено, нам нужно понять, где остановиться. Проанализируем ситуацию: внизу таблицы обычно идут итоги («Итого», «Всего») или просто пустые строки. Посмотрим на пример условия прерывания цикла чтения данных:


Для СтрокаТ = НомерСтрокиНачала По ВсегоСтрок Цикл
    ТоварНаименование = СокрЛП(Лист.Cells(СтрокаТ, НомерКолонкиТовар).Value);
    
    // Если ячейка пустая — возможно, это конец таблицы
    Если ПустаяСтрока(ТоварНаименование) Тогда
        // Проверяем следующую строку, на случай случайного пропуска
        Если ПустаяСтрока(СокрЛП(Лист.Cells(СтрокаТ + 1, НомерКолонкиТовар).Value)) Тогда
            Прервать; // Таблица закончилась
        КонецЕсли;
        Продолжить;
    КонецЕсли;
    
    // Проверка на ключевые слова подвала
    Если Найти(ВРег(ТоварНаименование), "ИТОГО") > 0 Тогда
        Прервать;
    КонецЕсли;
    
    // Здесь идет обработка данных и добавление в документ 1С
КонецЦикла;

Важный момент: в некоторых накладных строки нумеруются по порядку. В этом случае удобно использовать проверку «предыдущее число + 1». Если в колонке с номером строки вместо ожидаемой цифры 5 встретилось слово Всего — значит, мы вышли за пределы товарной части.

Способ 3: Штатные средства платформы 1С (Табличный документ)

Начиная с версии 8.3.6, платформа 1С позволяет работать с Excel напрямую, без установленного Microsoft Office. Это значительно быстрее и стабильнее, особенно если код выполняется на сервере под Linux. Разберем по шагам, как загрузить файл в объект ТабличныйДокумент.

Создаем новый объект ТабличныйДокумент.
Используем метод Прочитать(), указывая тип файла Excel.
Используем свойства ВысотаТаблицы и ШиринаТаблицы для получения границ.

Пример кода для этого метода:


ТабДок = Новый ТабличныйДокумент;
Попытка
    ТабДок.Прочитать(ПутьКФайлу, СпособЧтенияТабличногоДокумента.Excel);
Исключение
    Сообщить("Ошибка чтения файла: " + ОписаниеОшибки());
    Возврат;
КонецПопытки;

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

// Теперь перебираем область как обычную таблицу 1С
Для НомерСтроки = 1 По ВсегоСтрок Цикл
    ТекстЯчейки = ТабДок.Область(НомерСтроки, 1).Текст;
    // Логика поиска аналогична COM-объекту
КонецЦикла;

Интеллектуальный поиск шапки («Бальная система»)

Если формат файлов постоянно меняется, рассмотрим более продвинутый алгоритм. Мы можем составить список «ожидаемых» слов для шапки (например: «Цена», «Количество», «Сумма», «Артикул», «НДС»). Программа сканирует первые 30 строк файла и считает, сколько ключевых слов встретилось в каждой строке. Строка, набравшая максимум «баллов», признается заголовком таблицы.

Преимущества этого подхода:

Автоматическая адаптация под разных поставщиков.
Устойчивость к изменениям (если поставщик добавил колонку или пустую строку в начало).
Возможность автоматически определить номера колонок, не привязываясь к жестким индексам (например, колонка «Цена» может быть как 4-й, так и 6-й).

Обработка проблемных файлов

Иногда Excel-файлы содержат объединенные ячейки или скрытые строки. Проанализируем, как с этим работать. При использовании COM-объекта свойство Value для объединенной ячейки вернет значение только для первой (левой верхней) ячейки области, остальные будут пустыми. При использовании ТабличныйДокумент можно проверять свойство Область().СодержитОбъединенныеЯчейки.

Если вы столкнулись с тем, что файл заблокирован или открыт в режиме «Защищенного просмотра», рекомендуем перед программным открытием копировать файл во временную директорию ОС. Это часто снимает блокировки со стороны операционной системы и Excel.

Подведем итог: для надежной загрузки данных из Excel в 1С лучше всего сочетать определение границ через ВысотаТаблицы (или SpecialCells) с логическим поиском ключевых слов начала и конца таблицы. Это обеспечит стабильную работу обработки даже при изменении структуры входящих файлов.