Загрузка данных из внешних источников — одна из самых частых задач при автоматизации учета. Часто данные приходят не в одном файле, а в виде набора однотипных отчетов (например, ежедневные данные с датчиков или ведомости из разных подразделений). В этой статье мы разберем, как автоматизировать процесс перебора всех файлов в папке и последовательно загрузить их содержимое в одну табличную часть документа.
Предположим, у нас есть каталог, в который выгружаются файлы формата .xls или .xlsx. Структура файлов идентична: данные начинаются с определенной строки, а названия колонок всегда одинаковы. Нам необходимо реализовать алгоритм, который:
Рассмотрим несколько способов реализации, начиная от классического COM-соединения и заканчивая более современными и производительными методами.
Этот способ является наиболее гибким, так как позволяет использовать все возможности объектной модели Microsoft Excel. Мы воспользуемся функцией НайтиФайлы, чтобы получить список всех книг в каталоге, а затем в цикле обработаем каждую из них.
Проанализируем пример кода, который можно разместить в модуле формы документа:
&НаСервере
Процедура ЗагрузитьДанныеИзПапкиНаСервере()
ПутьКПапке = "C:\Exchange\ImportExcel\"; // Путь к каталогу с файлами
Маска = "*.xls*"; // Ищем и xls, и xlsx
// Получаем массив файлов в каталоге
МассивФайлов = НайтиФайлы(ПутьКПапке, Маска, Ложь);
Если МассивФайлов.Количество() = 0 Тогда
Сообщить("Файлы для загрузки не найдены.");
Возврат;
КонецЕсли;
// Очищаем таблицу перед загрузкой, если это необходимо
Объект.Надой.Очистить();
Попытка
Excel = Новый COMОбъект("Excel.Application");
Excel.DisplayAlerts = Ложь; // Отключаем лишние уведомления Excel
Исключение
Сообщить("Ошибка при запуске Excel! Возможно, программа не установлена.");
Возврат;
КонецПопытки;
Для каждого ФайлИзСписка Из МассивФайлов Цикл
Попытка
Книга = Excel.WorkBooks.Open(ФайлИзСписка.ПолноеИмя);
Лист = Книга.Sheets(1); // Работаем с первым листом
Исключение
Сообщить("Не удалось открыть файл: " + ФайлИзСписка.Имя);
Продолжить;
КонецПопытки;
// Определяем границы данных
КоличествоСтрок = Лист.Cells(1, 1).SpecialCells(11).Row;
// Перебираем строки данных (например, со 3-й строки, если сверху шапка)
Для НомерСтроки = 3 По КоличествоСтрок Цикл
КодЖивотного = Лист.Cells(НомерСтроки, 2).Text; // Допустим, код во второй колонке
Если ПустаяСтрока(КодЖивотного) Тогда
Продолжить;
КонецЕсли;
НоваяСтрока = Объект.Надой.Добавить();
// Ищем ссылку в справочнике по коду из Excel
НоваяСтрока.Животное = Справочники.Животные.НайтиПоКоду(КодЖивотного);
// Заполняем остальные реквизиты
НоваяСтрока.Станок = Лист.Cells(НомерСтроки, 3).Value;
НоваяСтрока.Количество = Лист.Cells(НомерСтроки, 4).Value;
НоваяСтрока.Время = Лист.Cells(НомерСтроки, 5).Value;
КонецЦикла;
Книга.Close(Ложь); // Закрываем книгу без сохранения изменений
КонецЦикла;
Excel.Quit();
Excel = Неопределено;
Сообщить("Загрузка успешно завершена!");
КонецПроцедуры
Важный нюанс: при работе на стороне сервера 1С необходимо убедиться, что Microsoft Excel установлен на сервере и у пользователя, под которым запущен агент службы 1С, есть права на запуск COM-объектов. Если сервер 64-битный, могут возникнуть сложности с 32-битным Excel, что потребует настройки через dcomcnfg.
Обращение к каждой ячейке через Cells(Row, Col).Value в цикле — это очень медленная операция. Каждое такое обращение инициирует вызов через медленный интерфейс COM. Если в ваших файлах много строк, загрузка может занять минуты.
Рассмотрим, как ускорить этот процесс. Вместо чтения ячеек по одной, мы можем считать всю используемую область данных в массив 1С за один вызов:
// Считываем данные всего листа в двумерный массив
ДанныеЛиста = Лист.UsedRange.Value.Выгрузить();
// Теперь работаем с массивом в памяти 1С, что в десятки раз быстрее
Для СтрокаМассива = 3 По ДанныеЛиста.ВГраница(1) Цикл
// Обратите внимание: индексы массива в данном случае могут зависеть от версии Excel
// Обычно доступ идет как ДанныеЛиста[Колонка][Строка]
КонецЦикла;
Использование этого метода позволяет сократить время обработки одного файла с нескольких секунд до долей секунды.
Если на сервере не установлен Microsoft Excel или вы хотите избежать проблем с COM-объектами, воспользуемся встроенным методом платформы 1С. Начиная с версии 8.3.6, объект ТабличныйДокумент умеет напрямую читать файлы Excel.
Разберем этот алгоритм по шагам:
&НаСервере
Процедура ЗагрузкаЧерезТабДок()
Каталог = "C:\Import\";
Файлы = НайтиФайлы(Каталог, "*.xlsx");
Для каждого ТекФайл Из Файлы Цикл
ТабДок = Новый ТабличныйДокумент;
Попытка
ТабДок.Прочитать(ТекФайл.ПолноеИмя, СпособЧтенияТабличныхДокументов.Excel);
Исключение
Сообщить("Ошибка чтения файла: " + ТекФайл.Имя);
Продолжить;
КонецПопытки;
// Определяем количество строк в прочитанном документе
ВсегоСтрок = ТабДок.ВысотаТаблицы;
Для НомСтроки = 3 По ВсегоСтрок Цикл
// Получаем текст из ячейки (Колонка 1 - Код, Колонка 2 - Значение)
Код = ТабДок.Область(НомСтроки, 1).Текст;
Если ПустаяСтрока(Код) Тогда Продолжить; КонецЕсли;
НоваяСтрока = Объект.ТЧ_Данные.Добавить();
НоваяСтрока.Номенклатура = Справочники.Номенклатура.НайтиПоКоду(Код);
НоваяСтрока.Сумма = Число(ТабДок.Область(НомСтроки, 2).Текст);
КонецЦикла;
КонецЦикла;
КонецПроцедуры
Преимущества данного метода:
excel.exe в диспетчере задач).В приведенных выше примерах мы использовали метод НайтиПоКоду внутри цикла. Если у вас 10 файлов по 1000 строк, система сделает 10 000 отдельных запросов к базе данных. Это создает колоссальную нагрузку.
Проанализируем ситуацию: как сделать правильно? Правильнее будет сначала собрать все уникальные коды из файлов в одну Таблицу Значений, а затем одним запросом получить все ссылки на справочник. После этого останется лишь сопоставить данные в памяти.
При использовании COM-соединения важно гарантировать закрытие приложения Excel, даже если в процессе чтения произошла ошибка. Всегда используйте конструкцию Попытка...Исключение...КонецПопытки. В блоке исключения обязательно вызывайте Excel.Quit(), чтобы не "повесить" сервер из-за утечки памяти от открытых процессов.
Также хорошей практикой считается перемещение уже обработанных файлов в отдельную папку (архив). Это предотвратит повторную загрузку одних и тех же данных при следующем запуске обработки. Для этого в 1С используется команда ПереместитьФайл(СтароеИмя, НовоеИмя).
Выяснив причину медленной работы или ошибок при загрузке, и применив один из предложенных подходов (COM или ТабличныйДокумент), вы сможете построить надежную систему импорта данных из любого количества внешних книг Excel.