Почему в 1С отображается больше строк при открытии файла Excel и как это исправить?

← На главную

При загрузке прайс-листов, накладных или отчетов из файлов Excel в 1С разработчики часто сталкиваются со странным поведением: в исходном файле .xls или .xlsx находится, например, 10 038 строк, а при открытии файла в 1С или выгрузке в ТабличныйДокумент получается 20 515 строк. Разберем, с чем связано такое расхождение и как правильно получать данные из многолистовых файлов Excel.

Выясняем причину: особенности объекта ТабличныйДокумент

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

Когда вы открываете файл Excel через меню Файл — Открыть или загружаете его программно вызовом метода Прочитать(), платформа читает весь файл целиком. Если файл Excel состоит из нескольких листов, 1С последовательно склеивает содержимое всех вкладок друг под другом в один табличный документ. В результате общее количество строк (свойство ВысотаТаблицы) оказывается равным сумме строк всех листов книги.

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

Решение 1: Получение данных конкретного листа через коллекцию Области

Начиная с версии платформы 1С:Предприятие 8.3.10 (при работе без режима совместимости с более ранними версиями), при прочтении файла каждый лист книги Excel автоматически превращается в именованную область. Имена листов преобразуются по правилам именования идентификаторов 1С — пробелы и спецсимволы заменяются на знаки подчеркивания.

Рассмотрим, как выделить данные только первого (или любого другого) листа из загруженной книги. Для этого обратимся к коллекции Области прочитанного табличного документа.

Посмотрим на пример кода:


ИмяФайла = "C:\Data\PriceList.xls";

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

// Проверяем, есть ли области (листы) в прочитанном документе
Если ТабличныйДокумент.Области.Количество() > 0 Тогда
    // Получаем имя первой области (первого листа, нумерация с 0)
    ИмяПервогоЛиста = ТабличныйДокумент.Области[0].Имя;
    
    // Выделяем область первого листа в отдельный ТабличныйДокумент
    ТабличныйДокументПервогоЛиста = ТабличныйДокумент.ПолучитьОбласть(ИмяПервогоЛиста);
    
    // Теперь ВысотаТаблицы вернет количество строк только первого листа
    Сообщить("Количество строк на первом листе: " + ТабличныйДокументПервогоЛиста.ВысотаТаблицы);
КонецЕсли;

Этот простой подход позволяет отсечь лишние листы книги, не создавая новых файлов и не копируя данные вручную через Excel.

Решение 2: Фильтрация пустых строк через ПостроительЗапроса

Если в файле присутствуют пустые строки со сбитым форматированием, из-за чего ТабличныйДокумент считывает лишние строки в конце листа, проанализируем способ их быстрой фильтрации с помощью объекта ПостроительЗапроса.

Передаем прочитанный ТабличныйДокумент в ОписаниеИсточникаДанных.
Выгружаем данные во внутренний объект ТаблицаЗначений.
Построитель запроса при выгрузке автоматически проигнорирует форматированные строки, не содержащие фактических данных.

Альтернативные варианты чтения файлов Excel

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

Прямой разбор XLSX через ZIP и XML: Файл формата .xlsx представляет собой ZIP-архив. С помощью объектов ЧтениеZipФайла и ЧтениеXML можно прочитать файл xl/worksheets/sheet1.xml. Это позволяет мгновенно загрузить только первый лист без загрузки всей книги в память и без установки стороннего ПО.
Чтение через ADODB (OLEDB): Обращение к файлу Excel как к базе данных с помощью SQL-запроса вида SELECT * FROM [Лист1$]. Метод работает быстро и обращается строго к указанному листу.
COM-соединение с Excel (Excel.Application): Позволяет программно открывать книгу и обращаться к конкретному листу через коллекцию Worksheets(1). Учитывайте, что данный вариант требует установленного Microsoft Excel на сервере или клиенте и не рекомендуется для использования в фоновых заданиях.

Подведем итог: Причина удваивания или увеличения количества строк в 1С заключается в том, что ТабличныйДокумент.Прочитать() считывает все листы файла Excel в единую таблицу. Используйте метод ТабличныйДокумент.ПолучитьОбласть(ТабличныйДокумент.Области[0].Имя), чтобы изолировать данные нужного листа и получить корректное количество строк.