Импорт данных из файлов Microsoft Excel — одна из самых частых задач, с которыми сталкивается разработчик 1С. Начиная с платформы 8.3.6, фирма «1С» предоставила мощный встроенный инструмент для прямого чтения файлов xls/xlsx — объект ТабличныйДокумент. Этот подход работает гораздо быстрее и стабильнее старого COM-объекта Excel.Application, особенно на сервере под управлением операционных систем семейства Linux.
Однако при работе с этим объектом часто возникают вопросы: как правильно переключаться между листами, почему возникает ошибка доступа к свойству Значение ячейки, как преобразовать текстовую дату в полноценный тип даты 1С и как эффективно организовать построчный обход. Разберем все эти нюансы подробно по шагам.
До версии платформы 8.3.10 метод Прочитать() объединял все листы Excel-файла в одну длинную таблицу. Но в современных версиях платформы появилась возможность работать с каждым листом отдельно. Листы файла представляются в виде именованных областей табличного документа.
Давайте посмотрим, как прочитать файл из временного хранилища и получить список доступных листов:
&НаСервереБезКонтекста
Функция ПолучитьСписокЛистовИзФайла(АдресХранения)
ФайлПриемник = ПолучитьИмяВременногоФайла("xls");
ДанныеХранилища = ПолучитьИзВременногоХранилища(АдресХранения);
ДанныеХранилища.Записать(ФайлПриемник);
ТабДок = Новый ТабличныйДокумент;
ТабДок.Прочитать(ФайлПриемник);
СписокЛистов = Новый СписокЗначений;
Для Каждого ОбластьТД Из ТабДок.Области Цикл
СписокЛистов.Добавить(ОбластьТД.Имя);
КонецЦикла;
Возврат СписокЛистов;
КонецФункции
Каждая именованная область в коллекции ТабДок.Области соответствует отдельному листу исходного файла Excel. Чтобы начать работу с конкретным листом (например, с листом "Программа"), мы должны извлечь его область:
ЛистПрограмма = ТабДок.ПолучитьОбласть("Программа");
Здесь метод ПолучитьОбласть("Программа") возвращает новый подчиненный ТабличныйДокумент, содержащий только данные выбранного листа.
Выясним причину частой ошибки начинающих разработчиков: попытка обратиться к ячейке через метод ПолучитьОбласть() для чтения свойств ячейки.
Если мы напишем код следующим образом:
// НЕПРАВИЛЬНЫЙ ПОДХОД, ведущий к ошибкам!
Область = ЛистПрограмма.ПолучитьОбласть("R4C3");
ТекущаяОбласть = Область.ТекущаяОбласть;
ЗначениеЯчейки = ТекущаяОбласть.Значение; // Вызовет ошибку "Поле объекта недоступно для чтения"
Почему так происходит? Метод ПолучитьОбласть() возвращает целый новый табличный документ, который копирует структуру выделенного диапазона. Свойство ТекущаяОбласть в этом контексте ведет себя непредсказуемо, а само свойство Значение становится недоступным, так как ячейка не инициализирована как содержащая значение.
Правильный способ обращения к конкретной ячейке или диапазону — использование метода Область(), который возвращает объект типа ОбластьЯчеекТабличногоДокумента.
// ПРАВИЛЬНЫЙ ПОДХОД
Ячейка = ЛистПрограмма.Область("R4C3"); // Адресация по строкам (Row) и колонкам (Column)
// Или по классическому стилю Excel:
Ячейка = ЛистПрограмма.Область("C4");
Разберем разницу между получением текста и значения. По умолчанию ячейки табличного документа хранят данные в виде текста. Если мы хотим извлечь именно типизированные данные (число, дата, булево), нам нужно при чтении файла указать параметр СпособЧтенияЗначенийТабличногоДокумента.Значение.
ТабДок.Прочитать(ФайлПриемник, СпособЧтенияЗначенийТабличногоДокумента.Значение);
Даже в этом случае, обращаясь к ячейке, рекомендуется делать проверку на свойство СодержитЗначение:
Ячейка = ЛистПрограмма.Область("R4C3");
Если Ячейка.СодержитЗначение Тогда
ЗначениеЯчейки = Ячейка.Значение;
Иначе
ЗначениеЯчейки = Ячейка.Текст;
КонецЕсли;
Часто при импорте дат из Excel они считываются как обычный текст, например, в формате "29.02.2020г." или "29.02.2020". Если попытаться напрямую присвоить эту строку реквизиту с типом Дата, платформа выдаст ошибку.
Для решения этой проблемы напишем надежную функцию парсинга строки даты, которая удалит лишние символы (такие как "г.", точки, пробелы), приведет строку к стандартному формату 1С "ГГГГММДД" и выполнит преобразование типов. Рассмотрим по шагам этот алгоритм:
Функция ПреобразоватьТекстВДату(Знач ИсходныйТекст) Экспорт
// Убираем возможные окончания "г." или пробелы
ЧистаяСтрока = СтрЗаменить(ИсходныйТекст, "г.", "");
ЧистаяСтрока = СокрЛП(СтрЗаменить(ЧистаяСтрока, ".", "")); // На выходе получаем "29022020"
Если СтрДлина(ЧистаяСтрока) = 8 Тогда
// Формируем правильную структуру: Год (4 знака) + Месяц (2 знака) + День (2 знака)
СтрокаДляДаты = Прав(ЧистаяСтрока, 4) + Сред(ЧистаяСтрока, 3, 2) + Лев(ЧистаяСтрока, 2);
Попытка
Возврат Дата(СтрокаДляДаты);
Исключение
Возврат Неопределено;
КонецПопытки;
КонецЕсли;
Возврат Неопределено;
КонецФункции
Этот метод гораздо стабильнее простой обрезки строки, так как он защищает от внезапных изменений формата в исходном файле Excel (например, если дата записана точкой или с пробелами).
При обработке прайс-листов или каталогов товаров разработчики часто сталкиваются с объединенными ячейками, которые обычно служат заголовками групп или категорий товаров. При этом строки товаров идут ниже.
Проанализируем две важные особенности:
A1:C3) реальные данные и текст сохраняются только в самой левой верхней ячейке (в данном случае A1). Все остальные ячейки из этой области вернут пустую строку или значение Неопределено.Посмотрим на пример кода, реализующий построчный обход с учетом групп:
&НаСервере
Процедура ЗагрузитьДанныеИзТабДока(ЛистПрограмма)
ВсегоСтрок = ЛистПрограмма.ВысотаТаблицы;
ТекущаяГруппа = "";
// Начинаем обход со строки, где начинаются данные (например, со 2-й строки)
Для нСтрока = 2 По ВсегоСтрок Цикл
// Проверяем ячейку в первой колонке на предмет объединения
ЯчейкаГруппы = ЛистПрограмма.Область(нСтрока, 1);
// Если границы не совпадают, это объединенная ячейка (заголовок группы)
ЯчейкаОбъединена = НЕ (ЯчейкаГруппы.Верх = ЯчейкаГруппы.Низ И ЯчейкаГруппы.Лево = ЯчейкаГруппы.Право);
Если ЯчейкаОбъединена И ЗначениеЗаполнено(ЯчейкаГруппы.Текст) Тогда
// Запоминаем текущую группу для последующих товаров
ТекущаяГруппа = ЯчейкаГруппы.Текст;
Продолжить; // Пропускаем строку-заголовок
КонецЕсли;
// Считываем данные товара
Артикул = ЛистПрограмма.Область(нСтрока, 2).Текст;
Наименование = ЛистПрограмма.Область(нСтрока, 3).Текст;
// Пропускаем пустые строки
Если НЕ ЗначениеЗаполнено(Наименование) Тогда
Продолжить;
КонецЕсли;
// Здесь выполняем обработку полученной строки:
// Создаем или обновляем номенклатуру, записываем данные в базу
Сообщить("Группа: " + ТекущаяГруппа + ", Товар: " + Наименование + " [" + Артикул + "]");
КонецЦикла;
КонецПроцедуры
Если файл Excel содержит десятки тысяч строк, построчный обход с постоянным обращением к методу Область() может выполняться довольно долго. Платформа предоставляет скрытую, но невероятно производительную альтернативу — использование объекта ПостроительЗапроса.
С его помощью мы можем мгновенно выгрузить всю область листа в стандартную ТаблицуЗначений. 1С автоматически определит типы колонок и перенесет данные.
Проанализируем этот изящный метод:
&НаСервере
Функция БыстроВыгрузитьЛистВТаблицуЗначений(ЛистПрограмма)
Построитель = Новый ПостроительЗапроса;
// Передаем всю заполненную область листа в источник данных
Построитель.ИсточникДанных = Новый ОписаниеИсточникаДанных(ЛистПрограмма.Область());
// Инициализируем настройки построителя
Построитель.ЗаполнитьНастройки();
Построитель.Выполнить();
// Получаем готовую типизированную таблицу значений
ТаблицаДанных = Построитель.Результат.Выгрузить();
Возврат ТаблицаДанных;
EndФункции
После этого вы можете работать с обычной ТаблицейЗначений, делать по ней быстрый поиск с помощью метода НайтиСтроки() или обходить её стандартным циклом Для Каждого Строка Из ТаблицаДанных Цикл. Скорость обработки при таком подходе возрастает в несколько раз, особенно на больших массивах данных.