Зачастую перед разработчиком 1С встает задача интеграции системы с внешними файлами, и наиболее популярным форматом обмена данными остается Microsoft Excel — для этого подойдёт загрузка номенклатуры и цен из Excel. Существует несколько подходов к решению этой задачи, каждый из которых имеет свои преимущества и недостатки в зависимости от объема данных и установленного программного обеспечения. В данной статье мы подробно разберем, как реализовать загрузку данных из Excel, проанализируем различные методы и выберем оптимальный вариант для ваших задач.
Этот метод является классическим и позволяет использовать всю мощь объектной модели Excel. Рассмотрим ситуацию, когда на сервере или клиентском компьютере установлен Microsoft Office. В этом случае мы можем напрямую управлять приложением Excel из кода 1С — есть универсальная загрузка данных из Excel в любые объекты 1С.
Разберем по шагам процесс подключения. Сначала нам необходимо создать объект Excel.Application. Важно помнить, что после завершения работы объект необходимо принудительно закрыть, чтобы не оставлять «зависших» процессов в диспетчере задач.
// Создание объекта подключения
Попытка
Эксель = Новый COMОбъект("Excel.Application");
Исключение
Сообщить("Excel не установлен на данном компьютере!");
Возврат;
КонецПопытки;
// Открытие файла
Книга = Эксель.Workbooks.Open(ПутьКФайлу);
Лист = Книга.Sheets(1); // Обращаемся к первому листу
Проанализируем процесс чтения данных. Обращение к каждой ячейке в цикле — это довольно медленная операция, так как каждый вызов Cells(r, c).Value требует времени на взаимодействие между 1С и COM-объектом. Для ускорения работы мы рекомендуем считывать данные целыми областями или использовать массивы.
Рассмотрим пример чтения данных из заполненных строк:
ВсегоСтрок = Лист.Cells.SpecialCells(11).Row; // Определение последней заполненной строки
Для НомерСтроки = 1 По ВсегоСтрок Цикл
ЗначениеКолонки1 = Лист.Cells(НомерСтроки, 1).Value;
ЗначениеКолонки2 = Лист.Cells(НомерСтроки, 2).Value;
// Обработка полученных данных
// ...
КонецЦикла;
// Обязательное закрытие
Книга.Close(Ложь);
Эксель.Quit();
Если нам требуется высокая скорость чтения больших объемов данных (десятки и сотни тысяч строк), использование COM-объекта Excel.Application может быть неэффективным. В такой ситуации проанализируем возможность использования ADO. Этот метод позволяет работать с файлом Excel как с базой данных SQL.
Выясним причину популярности этого метода: он не требует запуска самого приложения Excel, что существенно экономит системные ресурсы. Однако он требует наличия установленного драйвера Microsoft Access Database Engine.
Connection = Новый COMОбъект("ADODB.Connection");
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ПутьКФайлу + ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";";
Попытка
Connection.Open(ConnectionString);
Исключение
Сообщить("Ошибка подключения к файлу через ADO: " + ОписаниеОшибки());
Возврат;
КонецПопытки;
Recordset = Новый COMОбъект("ADODB.Recordset");
ТекстЗапроса = "SELECT * FROM [Лист1$]"; // Обратите внимание на знак $ в названии листа
Recordset.Open(ТекстЗапроса, Connection);
Пока НЕ Recordset.EOF Цикл
// Чтение данных через Recordset.Fields.Item(ИмяИлиИндекс).Value
Данные = Recordset.Fields.Item(0).Value;
Recordset.MoveNext();
КонецЦикла;
Recordset.Close();
Connection.Close();
Начиная с версии платформы 8.3.6, в 1С появилась возможность нативного чтения файлов формата XLSX без использования сторонних библиотек и установленного Excel. Посмотрим на этот способ подробнее, так как он является наиболее кроссплатформенным и безопасным — для этого подойдёт универсальный импорт данных из Excel в справочники 1С.
Рассмотрим алгоритм действий:
ТабличныйДокумент.Прочитать(), указывая путь к файлу и способ чтения.
ТабДок = Новый ТабличныйДокумент;
Попытка
ТабДок.Прочитать(ПутьКФайлу, СпособЧтенияЗначенийТабличногоДокумента.Значение);
Исключение
Сообщить("Не удалось прочитать файл: " + ОписаниеОшибки());
Возврат;
КонецПопытки;
КоличествоСтрок = ТабДок.ВысотаТаблицы;
КоличествоКолонок = ТабДок.ШиринаТаблицы;
Для НомерСтроки = 1 По КоличествоСтрок Цикл
Для НомерКолонки = 1 По КоличествоКолонок Цикл
Значение = ТабДок.Область(НомерСтроки, НомерКолонки).Текст;
// Работа со значением
КонецЦикла;
КонецЦикла;
Давайте проанализируем, какой метод лучше выбрать в конкретной ситуации:
COM-объект стоит использовать, когда вам необходимо не просто прочитать данные, а сохранить сложное форматирование, работать с формулами, макросами или создавать сводные таблицы. Это самый гибкий, но и самый медленный метод, чувствительный к разрядности системы (32/64 бит).
ADO-соединение идеально подходит для простой загрузки плоских таблиц с огромным количеством записей. Если файл содержит 100 000 строк и более, ADO покажет лучшие результаты по скорости.
Нативное чтение через ТабличныйДокумент — рекомендуемый стандарт для современной разработки на платформе 8.3. Он работает на сервере под управлением Linux, не требует лицензий Microsoft Office и обладает достаточной скоростью для большинства бизнес-задач — поможет массовая загрузка данных из XLSX для Linux.
Разберем несколько важных моментов, которые помогут избежать ошибок при работе с Excel:
Число() или Дата(), предварительно очищая строку от лишних пробелов с помощью СокрЛП().Попытка...Исключение. В блоке Исключение и в конце процедуры обязательно вызывайте Quit() для завершения процесса Excel, иначе сервер быстро переполнится неактивными процессами.ФоновыеЗадания.Выполнить), чтобы пользовательский интерфейс не «зависал» во время импорта.Выясним, как проверить корректность данных перед загрузкой. Мы рекомендуем сначала загружать данные во временную таблицу или дерево значений, показывать их пользователю для проверки и только после подтверждения записывать в информационную базу 1С.
Таким образом, мы рассмотрели основные механизмы интеграции 1С и Excel. Выбор метода зависит от ваших технических условий, но для большинства современных задач оптимальным будет использование встроенного метода Прочитать() объекта ТабличныйДокумент — решается через программная загрузка номенклатуры и реквизитов из Excel.