Разработка механизмов импорта данных из электронных таблиц Excel в информационные базы 1С — одна из самых частых задач, с которой сталкивается любой программист 1С. Существует несколько принципиально разных подходов к решению этой задачи. Исторически наиболее популярным был метод прямого обращения к приложению MS Excel через COM-соединение, однако современные реалии (переход на Linux-серверы, требования к производительности, отсутствие лицензий MS Office на серверах) заставляют разработчиков искать более гибкие и быстрые альтернативы.
В этой статье мы подробно разберем все доступные способы работы с Excel в 1С: от классического COM-соединения до сверхбыстрого разбора XML-структуры файла, проанализируем их достоинства, недостатки и ограничения.
Это классический метод, который использует установленное в операционной системе Windows приложение Microsoft Excel. Главная ошибка начинающих разработчиков при работе с COM — это построчный обход ячеек в цикле 1С. Каждое обращение к ячейке вида Excel.Cells(Строка, Колонка).Value совершает вызов внешней библиотеки, что при работе с файлами более чем в 1000 строк превращает загрузку в бесконечный процесс.
Для оптимизации процесса необходимо прочитать всю нужную область данных за один раз в двумерный массив 1С (тип COMSafeArray), после чего передать его на сервер для дальнейшей обработки. Рассмотрим готовый шаблон кода, реализующий эту схему.
Для начала разберем клиентскую процедуру, которая инициализирует COM-соединение, открывает файл, определяет границы заполненных данных и выгружает всю область данных в память:
&НаКлиенте
Процедура ВыполнитьИмпортИзExcel(Команда)
// Проверим заполнение пути к файлу
Если ПустаяСтрока(ПутьКФайлу) Тогда
ПоказатьПредупреждение(, "Укажите путь к файлу Excel!");
Возврат;
КонецЕсли;
// Инициализируем COM-объект MS Excel
Попытка
Excel = Новый COMОбъект("Excel.Application");
Книга = Excel.Workbooks.Open(ПутьКФайлу);
Лист = Книга.Sheets(1);
Исключение
Сообщить("Не удалось запустить MS Excel или открыть файл! Возможно, приложение не установлено.");
Если Excel <> Неопределено Тогда
Excel.Quit();
КонецЕсли;
Возврат;
КонецПопытки;
Попытка
// Определяем границы используемой области на листе
ВсегоСтрок = Лист.UsedRange.Rows.Count;
ВсегоКолонок = Лист.UsedRange.Columns.Count;
// Загружаем всю таблицу данных в COMSafeArray за один вызов
// Чтение начинаем со второй строки (пропуская шапку)
НачальнаяЯчейка = Лист.Cells(2, 1);
КонечнаяЯчейка = Лист.Cells(ВсегоСтрок, ВсегоКолонок);
ДанныеЭксель = Лист.Range(НачальнаяЯчейка, КонечнаяЯчейка).Value.Выгрузить();
// Закрываем книгу и приложение Excel, чтобы не оставлять процессы в памяти
Книга.Close(Ложь);
Excel.Quit();
Excel = Неопределено;
Исключение
// В случае ошибки обязательно закрываем процесс Excel
Если Excel <> Неопределено Тогда
Книга.Close(Ложь);
Excel.Quit();
Excel = Неопределено;
КонецЕсли;
Сообщить("Ошибка при чтении данных: " + ОписаниеОшибки());
Возврат;
КонецПопытки;
// Передаем полученный массив данных на сервер для обработки
ОбработатьЗагруженныеДанныеНаСервере(ДанныеЭксель);
КонецПроцедуры
Теперь рассмотрим серверную процедуру обработки полученного массива. Важно понимать, что при выгрузке из COMSafeArray мы получаем двумерный массив, где первое измерение — это колонки, а второе — строки:
&НаСервере
Процедура ОбработатьЗагруженныеДанныеНаСервере(МассивДанных)
// Получаем количество строк в загруженных данных
КоличествоСтрок = МассивДанных[0].Количество();
Для ИндексСтроки = 0 По КоличествоСтрок - 1 Цикл
// Извлекаем данные по колонкам (индексы соответствуют колонкам Excel с 0)
Артикул = СокрЛП(МассивДанных[0][ИндексСтроки]);
Название = СокрЛП(МассивДанных[1][ИндексСтроки]);
Количество = МассивДанных[2][ИндексСтроки];
Цена = МассивДанных[3][ИндексСтроки];
// Проверяем заполненность ключевых полей
Если ПустаяСтрока(Артикул) И ПустаяСтрока(Название) Тогда
Продолжить; // Пропускаем пустые строки
КонецЕсли;
// Алгоритм поиска номенклатуры в базе 1С
НайденнаяСсылка = Справочники.Номенклатура.ПустаяСсылка();
Если Не ПустаяСтрока(Артикул) Тогда
НайденнаяСсылка = Справочники.Номенклатура.НайтиПоРеквизиту("Артикул", Артикул);
КонецЕсли;
Если НайденнаяСсылка.Пустая() И Не ПустаяСтрока(Название) Тогда
НайденнаяСсылка = Справочники.Номенклатура.НайтиПоНаименованию(Название);
КонецЕсли;
// Если товар не найден, мы можем его создать или вывести предупреждение
Если НайденнаяСсылка.Пустая() Тогда
Сообщить("Товар не найден в базе: " + Название + " (Артикул: " + Артикул + ")");
Продолжить;
КонецЕсли;
// Здесь выполняется логика создания документов или заполнения табличных частей
// Например, добавление строк в документ "Поступление товаров и услуг"
КонецЦикла;
КонецПроцедуры
Преимущества метода: Работает стабильно со сложными форматами ячеек, формулами и защищенными листами. Быстро выгружает большие области данных за счет использования COMSafeArray.
Недостатки метода: Требует обязательного наличия установленного приложения MS Excel на компьютере пользователя или сервере. Работает только под управлением ОС Windows. Процессы excel.exe часто зависают в оперативной памяти при некорректном завершении сеанса 1С.
Начиная со средних версий платформы 1С:Предприятие 8.3, встроенный объект ТабличныйДокумент научился читать файлы форматов XLS и XLSX напрямую, без привлечения стороннего программного обеспечения. Это позволяет организовать загрузку файлов на стороне сервера, в том числе под управлением ОС Linux.
Выясним причину, по которой у многих разработчиков возникают сложности с этим методом. Основная проблема — некорректное преобразование типов данных (числа и даты считываются как пустые строки или текст с неразрывными пробелами). Для решения этой проблемы критически важно правильно выставлять параметр чтения табличного документа.
Рассмотрим пример реализации загрузки через ТабличныйДокумент на сервере:
&НаСервере
Процедура ПрочитатьExcelЧерезТабДокНаСервере(ПутьКФайлуНаСервере)
ТабДок = Новый ТабличныйДокумент;
// Чтение файла с указанием способа получения значений
Попытка
ТабДок.Прочитать(ПутьКФайлуНаСервере, СпособЧтенияЗначенийТабличногоДокумента.Значение);
Исключение
Сообщить("Не удалось прочитать файл! Ошибка: " + ОписаниеОшибки());
Возврат;
КонецПопытки;
КоличествоСтрок = ТабДок.ВысотаТаблицы;
КоличествоКолонок = ТабДок.ШиринаТаблицы;
// Обходим строки табличного документа
Для НомерСтроки = 2 По КоличествоСтрок Цикл // Начинаем со 2-й строки
// Получаем значения ячеек напрямую с сохранением типов
АртикулЗначение = ТабДок.Область(НомерСтроки, 1).ТекущийОбъект.Значение;
НазваниеЗначение = ТабДок.Область(НомерСтроки, 2).Текст; // Для строк лучше брать Текст
КоличествоЗначение = ТабДок.Область(НомерСтроки, 3).ТекущийОбъект.Значение;
ДатаЗначение = ТабДок.Область(НомерСтроки, 4).ТекущийОбъект.Значение;
// Теперь КоличествоЗначение гарантированно будет числом, а ДатаЗначение - датой
Если ТипЗнч(КоличествоЗначение) = Тип("Число") Тогда
// Обрабатываем числовые данные
КонецЕсли;
КонецЦикла;
КонецПроцедуры
Использование свойства ТекущийОбъект.Значение вместо свойства Текст позволяет разработчику получать чистые типы данных платформы 1С (числа, даты, булево) без необходимости ручного парсинга текстовых строк.
Преимущества метода: Полная кроссплатформенность (работает на серверах Linux и Windows). Высокая скорость работы. Не требует приобретения лицензий и установки MS Office.
Недостатки метода: Если в исходном файле Excel присутствуют сложные объединенные ячейки в шапке таблицы или скрытые строки, структура колонок в табличном документе может сместиться, что приведет к ошибкам считывания.
Проанализируем продвинутую методику ускорения работы с ТабличнымДокументом. Если файл содержит классическую плоскую таблицу с понятной структурой колонок, мы можем полностью отказаться от медленного обхода строк в цикле 1С. Для этого мы задействуем системный объект ПостроительЗапроса, передав ему в качестве источника данных область табличного документа.
Разберем по шагам этот элегантный и производительный алгоритм:
&НаСервере
Функция ПрочитатьExcelВТаблицуЗначений(ПутьКФайлу) Экспорт
ТабДок = Новый ТабличныйДокумент;
Попытка
ТабДок.Прочитать(ПутьКФайлу, СпособЧтенияЗначенийТабличногоДокумента.Значение);
Исключение
ВызватьИсключение "Не удалось загрузить файл: " + ОписаниеОшибки();
КонецПопытки;
// Выбираем всю заполненную область таблицы
ОбластьДанных = ТабДок.Область(1, 1, ТабДок.ВысотаТаблицы, ТабДок.ШиринаТаблицы);
// Создаем описание источника данных для Построителя Запроса
ИсточникДанных = Новый ОписаниеИсточникаДанных(ОбластьДанных);
ИсточникДанных.Колонки[0].Имя = "Артикул";
ИсточникДанных.Колонки[1].Имя = "Наименование";
ИсточникДанных.Колонки[2].Имя = "Количество";
ИсточникДанных.Колонки[3].Имя = "Цена";
// Инициализируем построитель запроса
Построитель = Новый ПостроительЗапроса;
Построитель.ИсточникДанных = ИсточникДанных;
// Выполняем построение запроса и моментально выгружаем в Таблицу Значений
Построитель.Выполнить();
ТаблицаРезультат = Построитель.Результат.Выгрузить();
// На выходе получаем готовую Таблицу Значений со всеми типами данных!
Возврат ТаблицаРезультат;
КонецФункции
Этот способ позволяет прочитать файл размером в десятки тысяч строк буквально за долю секунды, так как весь процесс переноса данных из документа в таблицу значений происходит на уровне низкоскоростного платформенного C++ кода без выполнения медленных интерпретируемых циклов на встроенном языке 1С.
Если перед вами стоит задача регулярного импорта огромных каталогов товаров (от 100 000 строк и более) на сервере под управлением Linux, то все стандартные методы 1С могут столкнуться с ограничениями оперативной памяти или нехваткой производительности. В этом случае на помощь приходит архитектура самого формата XLSX.
Как известно, файл с расширением .xlsx — это обычный упакованный ZIP-архив. Если распаковать его программно средствами 1С с помощью объекта ЧтениеZipФайла, мы обнаружим внутреннюю структуру папок и файлов XML:
xl/sharedStrings.xml.xl/worksheets/sheet1.xml.Используя системные объекты платформы 1С ЧтениеXML или ПостроительDOM, мы можем последовательно прочитать эти файлы XML. Этот способ является самым сложным в реализации, так как программисту приходится самостоятельно обрабатывать форматирование дат, пропуски пустых строк и структуры формул, но по скорости работы на сверхбольших массивах данных у него нет конкурентов.
Выберем наиболее подходящий инструмент под конкретные бизнес-требования и архитектурные условия вашей системы:
СпособЧтенияЗначенийТабличногоДокумента.Значение.Рассмотренные подходы позволяют разработчику гибко лавировать между простотой реализации алгоритма и производительностью готового программного решения, обеспечивая надежный и бесперебойный импорт данных в информационные базы 1С.