Загрузка данных из внешних файлов Excel — одна из самых частых задач в практике разработчика 1С. Однако она таит в себе множество подводных камней: пользователь может изменить порядок столбцов, удалить нужную колонку или добавить лишние строки сверху. Если начать загрузку такого файла «как есть», это может привести к критическим ошибкам в базе или порче данных. В этой статье мы подробно разберем, как реализовать надежный механизм предварительной проверки Excel-файла перед его обработкой.
Прежде всего, проанализируем, где именно должна происходить проверка. Согласно стандартам разработки на платформе 1С:Предприятие 8.3, работа с файловыми потоками и тяжелые вычисления должны выноситься на сервер. На клиенте мы лишь инициируем выбор файла и передаем его во временное хранилище. Рассмотрим типовую схему взаимодействия:
ТабличныйДокумент, преобразуется в ТаблицаЗначений, и выполняется детальный анализ структуры колонок.Для начала разберем процедуру выбора файла. Мы будем использовать асинхронный метод НачатьПомещениеФайла, который является предпочтительным в современных конфигурациях. Посмотрим на пример кода:
&НаКлиенте
Процедура ЗаполнитьИзExcel(Команда)
ОписаниеОповещения = Новый ОписаниеОповещения("ЗагрузитьФайлЗавершение", ЭтотОбъект);
Диалог = Новый ДиалогВыбораФайла(РежимДиалогаВыбораФайла.Открытие);
Диалог.Фильтр = НСтр("ru='Выберите файл Excel(*.xls, *.xlsx)|*.xls*'");
НачатьПомещениеФайла(ОписаниеОповещения, , Диалог, Истина, УникальныйИдентификатор);
КонецПроцедуры
После того как пользователь выбрал файл, управление передается в процедуру завершения. Здесь мы вызываем серверную функцию, которая вернет нам результат проверки или текст ошибки.
&НаКлиенте
Процедура ЗагрузитьФайлЗавершение(Результат, Адрес, ВыбранноеИмяФайла, ДополнительныеПараметры) Экспорт
Если Результат Тогда
// Вызываем серверную функцию для анализа файла
ТекстОшибки = "";
СписокСообщений = ЗаполнитьФормуИзФайлаExcelНаСервере(Адрес, ТекстОшибки);
Если ЗначениеЗаполнено(ТекстОшибки) Тогда
ОбщегоНазначенияКлиент.СообщитьПользователю(ТекстОшибки);
Иначе
// Продолжаем загрузку...
КонецЕсли;
КонецЕсли;
КонецПроцедуры
Теперь переходим к самому ответственному моменту — разберем, как прочитать файл без использования COM-объектов (что важно для серверов на Linux) и проверить его структуру. Мы воспользуемся объектом ТабличныйДокумент и ПостроительЗапроса. Проанализируем ситуацию: ПостроительЗапроса позволяет превратить область табличного документа в полноценную таблицу значений, где именами колонок станут заголовки из первой строки файла.
&НаСервере
Функция ЗаполнитьФормуИзФайлаExcelНаСервере(Адрес, ТекстОшибки)
Данные = ПолучитьИзВременногоХранилища(Адрес);
// Создаем временное имя с нужным расширением
ИмяВременногоФайла = ПолучитьИмяВременногоФайла("xlsx");
Данные.Записать(ИмяВременногоФайла);
Документ = Новый ТабличныйДокумент;
Попытка
Документ.Прочитать(ИмяВременногоФайла, СпособЧтенияЗначенийТабличногоДокумента.Значение);
Исключение
ТекстОшибки = "Не удалось прочитать файл. Возможно, он поврежден или открыт в другой программе.";
Возврат Неопределено;
КонецПопытки;
// Определяем область данных. В данном примере берем первые 6 колонок.
// Важно: ВысотаТаблицы может быть больше реальных данных из-за форматирования!
Область = Документ.Область(1, 1, Документ.ВысотаТаблицы, 6);
ПЗ = Новый ПостроительЗапроса;
ПЗ.ИсточникДанных = Новый ОписаниеИсточникаДанных(Область);
ПЗ.ЗаполнитьНастройки();
ПЗ.Выполнить();
ТЗ_Excel = ПЗ.Результат.Выгрузить();
// Теперь проверяем, все ли нужные колонки на месте
МассивОшибок = ПроверитьНаличиеКолонок(ТЗ_Excel);
Если МассивОшибок.Количество() > 0 Тогда
ТекстОшибки = "В файле отсутствуют необходимые колонки: " + СтрСоединить(МассивОшибок, ", ");
Попытка УдалитьФайлы(ИмяВременногоФайла); Исключение КонецПопытки;
Возврат Неопределено;
КонецЕсли;
// Если проверка пройдена, возвращаем данные для дальнейшей обработки
Возврат ТЗ_Excel;
КонецФункции
Для гибкости вынесем проверку имен в отдельную функцию. Выясним причину, почему это удобно: так мы сможем легко дополнять список обязательных полей, не меняя основной алгоритм загрузки. Проанализируем код функции:
&НаСервере
Функция ПроверитьНаличиеКолонок(ТЗ_Excel)
МассивОтсутствующихКолонок = Новый Массив;
МассивНужныхКолонок = Новый Массив;
МассивНужныхКолонок.Добавить("ФИО");
МассивНужныхКолонок.Добавить("ИНН");
МассивНужныхКолонок.Добавить("Должность");
МассивНужныхКолонок.Добавить("Грейд");
Для Каждого ИмяКолонки Из МассивНужныхКолонок Цикл
Если ТЗ_Excel.Колонки.Найти(ИмяКолонки) = Неопределено Тогда
МассивОтсутствующихКолонок.Добавить(ИмяКолонки);
КонецЕсли;
КонецЦикла;
Возврат МассивОтсутствующихКолонок;
КонецФункции
Рассмотрим подробнее проблемы, которые часто возникают при реальной эксплуатации подобных загрузок, и способы их решения:
ПостроительЗапроса примет его за имя колонки. Решение: Перед загрузкой программно сканируйте первые несколько строк ТабличногоДокумента поиском ключевых слов (например, «ИНН», «ФИО»). Начинайте область ОписаниеИсточникаДанных с той строки, где найдено совпадение.Документ.ВысотаТаблицы возвращает 10 000 строк вместо 100. Решение: При обходе полученной таблицы значений всегда проверяйте ключевое поле на ЗначениеЗаполнено(). Если ФИО пустое — пропускайте строку.СпособЧтенияЗначенийТабличногоДокумента.Значение платформа попытается сохранить тип из Excel. Будьте готовы приводить ИНН к строке и дополнять нулями до 10 или 12 знаков.ТабличныйДокумент при чтении получает результат вычисления формулы, а не саму формулу, что в большинстве случаев является именно тем, что нам нужно.Если вы работаете в современной конфигурации, мы рекомендуем обратить внимание на подсистему «Загрузка данных из файла». Она позволяет не писать код проверки вручную, а предоставляет пользователю удобный интерфейс «мэппинга» (сопоставления) колонок файла с реквизитами системы. Это избавляет программиста от необходимости жестко задавать имена колонок, так как пользователь сам сможет указать, что колонка «Налоговый номер» в его файле — это «ИНН» в 1С.
Рассмотрим еще один критический аспект. Даже если структура колонок верна, данные внутри могут быть некорректными (например, неверное контрольное число ИНН). Правильнее всего выполнять загрузку в два этапа:
НачатьТранзакцию(), записываем объекты и выполняем ЗафиксироватьТранзакцию().Такой подход гарантирует, что база данных останется в консистентном состоянии, а пользователь получит подробный отчет о том, что именно нужно исправить в Excel-файле для успешной загрузки.