Загрузка данных из внешних файлов — это одна из самых частых задач, с которыми сталкивается начинающий разработчик 1С. Формат Excel (xls, xlsx) остается стандартом обмена данными в офисной среде. Существует несколько подходов к решению этой задачи, каждый из которых имеет свои преимущества и ограничения. В этой статье мы подробно разберем три основных метода, проанализируем их эффективность и научимся писать качественный код для импорта данных.
Прежде чем данные попадут в базу, пользователь должен выбрать файл на своем компьютере. Рассмотрим, как правильно организовать диалог выбора файла. Важно помнить, что в управляемом приложении работа с файловой системой на сервере ограничена, поэтому выбор происходит &НаКлиенте.
Для начала создадим реквизит формы ПутьКФайлу (тип Строка) и вынесем его на форму. Для кнопки выбора (событие НачалоВыбора) используем следующий код:
&НаКлиенте
Процедура ПутьКФайлуНачалоВыбора(Элемент, ДанныеВыбора, СтандартнаяОбработка)
СтандартнаяОбработка = Ложь;
Диалог = Новый ДиалогВыбораФайла(РежимДиалогаВыбораФайла.Открытие);
Диалог.Заголовок = "Выберите файл Excel для загрузки";
Диалог.Фильтр = "Excel файлы(*.xls; *.xlsx)|*.xls;*.xlsx";
Диалог.ИндексФильтра = 0;
Диалог.ПредварительныйПросмотр = Ложь;
Диалог.ПроверятьСуществованиеФайла = Истина;
Диалог.МножественныйВыбор = Ложь;
Если Диалог.Выбрать() Тогда
ПутьКФайлу = Диалог.ПолноеИмяФайла;
КонецЕсли;
КонецПроцедуры
Важный момент: после того как путь получен, файл необходимо передать на сервер. В современных конфигурациях рекомендуется использовать временное хранилище, чтобы не привязываться к конкретным путям на диске сервера.
Это самый рекомендуемый метод для начинающих и профессионалов. Его главное преимущество — кроссплатформенность. Этот код будет работать на Linux-серверах и не требует установленного Microsoft Excel на компьютере. Мы прочитаем файл в ТабличныйДокумент, а затем преобразуем его в ТаблицаЗначений для удобной обработки.
Рассмотрим по шагам реализацию функции чтения:
Функция ПрочитатьExcelЧерезТабДок(ПутьКФайлу) Экспорт
ТабДок = Новый ТабличныйДокумент;
Попытка
ТабДок.Прочитать(ПутьКФайлу);
Исключение
Сообщить("Не удалось прочитать файл: " + ОписаниеОшибки());
Возврат Неопределено;
КонецПопытки;
// Определяем область данных (вся заполненная часть листа)
Область = ТабДок.Область(1, 1, ТабДок.ВысотаТаблицы, ТабДок.ШиринаТаблицы);
// Используем ПостроительОтчета для быстрой выгрузки в Таблицу Значений
Построитель = Новый ПостроительОтчета;
Построитель.ИсточникДанных = Новый ОписаниеИсточникаДанных(Область);
Построитель.Выполнить();
Возврат Построитель.Результат.Выгрузить();
КонецФункции
Этот метод идеально подходит для простых таблиц. Если в файле сложная структура (объединенные ячейки, несколько шапок), ПостроительОтчета может отработать некорректно. в этом случае придется обходить ячейки ТабличногоДокумента в цикле, используя методы ПолучитьОбласть.
Этот метод основан на использовании программного интерфейса Microsoft Excel. Он работает только в операционной системе Windows и требует, чтобы Excel был физически установлен на сервере (или клиенте, в зависимости от места выполнения кода). Рассмотрим вариант создания элемента справочника Агенты на основе данных из файла.
&НаСервере
Процедура ЗагрузитьЧерезCOM(ДвоичныеДанныеФайла, Расширение)
// Сохраняем файл во временную папку сервера
ИмяФайлаНаСервере = ПолучитьИмяВременногоФайла(Расширение);
ДвоичныеДанныеФайла.Записать(ИмяФайлаНаСервере);
Попытка
Excel = Новый COMОбъект("Excel.Application");
Excel.DisplayAlerts = Ложь; // Отключаем уведомления
Excel.Visible = Ложь; // Делаем процесс невидимым
Исключение
Сообщить("Excel не установлен на сервере!");
Возврат;
КонецПопытки;
Попытка
Книга = Excel.Workbooks.Open(ИмяФайлаНаСервере);
Лист = Книга.Sheets(1); // Работаем с первым листом
// Определяем границы данных
ВсегоСтрок = Лист.Cells(1,1).SpecialCells(11).Row;
// Цикл по строкам (начинаем со 2-й, предполагая, что 1-я — заголовок)
Для НомерСтроки = 2 По ВсегоСтрок Цикл
// Читаем значения ячеек
НаименованиеОрганизации = СокрЛП(Лист.Cells(НомерСтроки, 1).Value);
ИмяАгента = СокрЛП(Лист.Cells(НомерСтроки, 2).Value);
ФИО = СокрЛП(Лист.Cells(НомерСтроки, 3).Value);
// Поиск организации (оптимизация: лучше кэшировать результаты поиска)
ОрганизацияСсылка = Справочники.Организации.НайтиПоНаименованию(НаименованиеОрганизации);
Если Не ОрганизацияСсылка.Пустая() Тогда
НовыйАгент = Справочники.Агенты.СоздатьЭлемент();
НовыйАгент.Владелец = ОрганизацияСсылка;
НовыйАгент.Наименование = ИмяАгента;
НовыйАгент.ФИО = ФИО;
НовыйАгент.Записать();
КонецЕсли;
КонецЦикла;
Книга.Close(Ложь);
Исключение
Сообщить("Ошибка при обработке: " + ОписаниеОшибки());
КонецПопытки;
Excel.Quit();
Excel = 0; // Очищаем ссылку на объект
КонецПроцедуры
Преимущества COM: высокая гибкость. Вы можете читать форматирование ячеек, работать с формулами и макросами. Недостатки: низкая скорость на больших объемах данных и зависимость от окружения (разрядность Excel должна совпадать с разрядностью сервера 1С).
Если вам нужно загрузить "простыни" данных (десятки и сотни тысяч строк), стандартный COM-объект будет работать крайне медленно. В этом случае проанализируем использование драйвера ADODB, который позволяет работать с файлом Excel как с базой данных через SQL-запросы.
&НаСервере
Процедура ЗагрузитьЧерезADODB(ПутьКФайлу)
Connection = Новый COMОбъект("ADODB.Connection");
// Строка соединения для файлов xlsx
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ПутьКФайлу + ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";";
Попытка
Connection.Open(ConnectionString);
Исключение
Сообщить("Ошибка подключения к провайдеру: " + ОписаниеОшибки());
Возврат;
КонецПопытки;
// Выбираем данные с Листа1
ТекстЗапроса = "SELECT * FROM [Лист1$]";
Recordset = Connection.Execute(ТекстЗапроса);
// Быстрая выгрузка всех данных в массив COM
ДанныеМассив = Recordset.GetRows();
// Дальнейшая обработка массива...
Connection.Close();
КонецПроцедуры
Этот метод на порядок быстрее классического перебора ячеек, так как данные считываются в память пакетом.
Проанализируем ситуацию, в которой начинающий программист допускает ошибки при записи данных. Рассмотрим основные рекомендации для профессиональной разработки:
ТаблицуЗначений. Проверьте их на корректность и только потом запускайте цикл записи в базу данных. Это позволит избежать "мусора" в базе, если файл прервется на середине.НайтиПоНаименованию вызывается на каждой итерации. Если в файле 1000 строк с одной и той же организацией, 1С сделает 1000 запросов к базе. Создайте Соответствие, где ключом будет название организации, а значением — ее ссылка. Сначала ищите в соответствии, и только если там пусто — делайте запрос к БД.Cells(Строка, Колонка).Text вместо .Value, чтобы получить строковое представление именно в том виде, в котором оно отображается в ячейке.ФоновыеЗадания.Выполнить. Это позволит пользователю продолжать работу, пока данные импортируются.Объект.ОбменДанными.Загрузка = Истина;. Это отключит лишние проверки в модуле объекта и ускорит запись, но используйте это только если вы уверены в качестве входных данных.Выясним причину, почему метод через ТабличныйДокумент считается золотым стандартом. Кроме кроссплатформенности, он избавляет администраторов от необходимости настраивать права доступа для DCOM-объектов на сервере, что часто является "головной болью" в крупных организациях. Начните свое обучение именно с этого метода, а COM и ADODB оставьте для специфических задач автоматизации офисного ПО.