Довольно часто при разработке на платформе 1С возникает необходимость загрузить данные из файлов формата Excel. При использовании технологии COM-объектов многие разработчики сталкиваются с неочевидным поведением: мы вроде бы программно обращаемся ко второму или третьему листу, но данные все равно считываются с первого. Давайте подробно разберем эту ситуацию, проанализируем частую ошибку в коде и выясним причину такого поведения.
Рассмотрим классический пример. У нас есть файл Excel, содержащий несколько листов. Наша задача — прочитать данные, например, со второго листа. Разработчик пишет код для создания COM-объекта, открытия книги и позиционирования на листе, но в результате получает данные с первой страницы.
Посмотрим на пример кода, в котором кроется эта коварная ошибка:
// Создание COM-объекта
Excel = Новый COMОбъект("Excel.Application");
// Открытие книги
Книга = Excel.Workbooks.Open(ИмяФайла);
// Позиционирование на нужном листе (например, 2-ом)
Лист = Книга.Worksheets(НомерСтраницы);
// Получим количество строк и колонок
ФайлСтрок = Excel.Cells(1,1).SpecialCells(11).Row;
ФайлКолонок = Excel.Cells(1,1).SpecialCells(11).Column;
// Считываем первую строку (заголовки)
Сч = 1;
Пока ЗначениеЗаполнено(Excel.Cells(1, Сч).Text) Цикл
ИмяКолонки = Excel.Cells(1, Сч).Text;
Сч = Сч + 1;
КонецЦикла;
// Считываем тело файла эксель построчно
Для НС = 2 по ФайлСтрок Цикл
Для НомерКолонки = 1 по ФайлКолонок Цикл
ТекущееЗначение = Excel.Cells(НС, НомерКолонки).Text;
// Обработка значения...
КонецЦикла;
КонецЦикла;
На первый взгляд все логично: мы создали объект Книга, выбрали из него конкретный лист в переменную Лист (например, с индексом 2). Но почему же данные продолжают читаться с первого листа?
Проанализируем строки, где происходит чтение значений ячеек: Excel.Cells(НС, НомерКолонки).Text. В этом месте мы обращаемся к глобальному объекту Excel.Application.
Важно понимать архитектуру COM-объекта MS Excel. Объект Application (в нашем коде переменная Excel) при обращении к свойствам вроде Cells или Range по умолчанию маршрутизирует вызов к активному листу активной книги. При открытии файла активным листом, как правило, является первый лист (или тот, на котором файл был сохранен). Поэтому, даже получив ссылку на нужный лист в переменную Лист, мы эту ссылку никак не используем для чтения!
Решение: чтобы прочитать данные именно со второго (выбранного) листа, необходимо обращаться к ячейкам через созданную переменную листа, а не через глобальный объект приложения.
Исправленный вариант кода для чтения будет выглядеть так:
// Правильное обращение к ячейкам конкретного листа
Для НС = 2 по ФайлСтрок Цикл
Для НомерКолонки = 1 по ФайлКолонок Цикл
ТекущееЗначение = Лист.Cells(НС, НомерКолонки).Text; // Используем переменную Лист!
КонецЦикла;
КонецЦикла;
Также не забываем использовать Лист при определении количества строк и колонок: Лист.Cells(1,1).SpecialCells(11).Row.
Рассмотрим подробнее альтернативные подходы к чтению файлов Excel, которые могут значительно упростить и ускорить вашу работу.
Индексы листов (1, 2, 3) — не самый надежный ориентир. Пользователи могут поменять листы местами, добавить пустой лист в начало и т.д. Если вы точно знаете название нужного листа, лучше позиционироваться по его имени: Лист = Книга.Worksheets("НазваниеЛиста");. Для проверки имени листа во время отладки можно использовать конструкцию Книга.Worksheets(2).Name.
Использование COM-объекта Excel.Application требует обязательной установки пакета MS Office на компьютере или сервере, где выполняется код. В современных версиях платформы лучше использовать штатный объект ТабличныйДокумент. Платформа 1С умеет напрямую читать файлы .xls и .xlsx методом ТабличныйДокумент.Прочитать(ИмяФайла). Листы исходного файла попадают в коллекцию ТабличныйДокумент.Области, что позволяет программно перебирать их и считывать нужные области без установки самого Excel.
Поячеечное чтение в цикле (как в исходном примере) работает крайне медленно, если в файле тысячи строк. Разберем по шагам, как это ускорить:
Использование COMSafeArray: Данные с листа считываются не по одной ячейке, а целиком за один вызов с помощью Лист.Range(...).Value. Лист выгружается в память 1С в виде вариантного массива (SafeArray). Обход такого массива внутри 1С происходит в десятки раз быстрее, чем постоянное дерганье COM-интерфейса.
Использование ADO (ADODB.Connection): Позволяет подключаться к файлу Excel как к базе данных и считывать листы с помощью обычных SQL-запросов (где лист выступает в роли таблицы). Этот метод считается одним из самых быстрых и также не требует визуального запуска тяжелого процесса Excel.
В конце исходного кода мы видим Excel.Quit();. Однако на практике в 1С этого часто бывает недостаточно. В фоновых процессах сервера могут оставаться "зависшие" сеансы EXCEL.EXE, которые со временем исчерпают всю оперативную память. Чтобы процесс гарантированно завершился, необходимо очистить ссылки на все созданные COM-объекты, присвоив им значение Неопределено.
Посмотрим на пример правильного завершения работы:
Excel.DisplayAlerts = 0;
Книга.Close();
Excel.Quit();
// Очистка ссылок для сборщика мусора
Лист = Неопределено;
Книга = Неопределено;
Excel = Неопределено;
Используя эти правила, вы сможете надежно и быстро обрабатывать нужные листы Excel в ваших обработках 1С, избегая зависаний процессов и ошибок с чтением неверных страниц.