При автоматизации складского учета и закупок одной из наиболее частых задач является загрузка накладных, прайс-листов и приходов из файлов Excel (форматы .xls, .xlsx). Главная сложность этой задачи заключается в отсутствии единого стандарта: разные поставщики присылают файлы с различным оформлением. В одних документах табличная часть начинается с 5-й строки, в других — с 20-й, шапка таблицы может содержать синонимы («Товар», «Наименование», «Номенклатура»), а артикул и наименование часто склеены в одну строку через разделитель.
В этой статье мы подробно разберем, как уйти от жесткой привязки к номерам строк, реализовать гибкий эвристический поиск начала таблицы, эффективно распарсить сложные текстовые поля и рассмотримы современные альтернативы медленному COM-соединению.
Самый простой способ, который часто применяется на практике — это предоставление пользователю возможности самостоятельно указать начальные параметры на форме обработки загрузки. Мы можем расположить на форме следующие реквизиты:
Этот способ надежен, когда структура файлов от конкретного поставщика статична. Однако он требует постоянного внимания со стороны оператора, если файлы приходят от разных контрагентов. Любое изменение в структуре файла (например, добавление рекламной строки сверху) приведет к сдвигу данных и некорректной загрузке.
Чтобы сделать процесс полностью автоматическим, мы можем реализовать алгоритм, который самостоятельно сканирует файл и определяет, где начинается табличная часть. Рассмотрим подробнее этот алгоритм.
Идея заключается в том, что в любой таблице прихода обязательно должны присутствовать ключевые столбцы: наименование товара и его цена (или количество). Мы можем перебирать строки табличного документа сверху вниз и искать ячейки, содержащие ключевые слова-маркеры.
Рассмотрим по шагам программную реализацию этого поиска:
ТабличныйДокумент.Посмотрим на пример реализации этой логики на встроенном языке 1С:
Функция ОпределитьГраницыТаблицы(ТабДок) Экспорт
Результат = Новый Структура("СтартоваяСтрока, КолонкаНоменклатуры, КолонкаЦены", 0, 0, 0);
ВсегоСтрок = ТабДок.ВысотаТаблицы;
ВсегоКолонок = ТабДок.ШиринаТаблицы;
// Ограничиваем область поиска шапки первыми 50 строками
ПределПоиска = Мин(ВсегоСтрок, 50);
Для НомСтроки = 1 По ПределПоиска Цикл
КолонкаТовара = 0;
КолонкаЦены = 0;
Для НомКолонки = 1 По ВсегоКолонок Цикл
ЗначениеЯчейки = СокрЛП(ТабДок.Область(НомСтроки, НомКолонки).Текст);
ЗначениеЯчекиВРегистре = ВРег(ЗначениеЯчейки);
// Проверяем маркеры номенклатуры
Если Найти(ЗначениеЯчекиВРегистре, "НАИМЕНОВАНИЕ") > 0
Или Найти(ЗначениеЯчекиВРегистре, "ТОВАР") > 0
Или Найти(ЗначениеЯчекиВРегистре, "НОМЕНКЛАТУРА") > 0 Тогда
КолонкаТовара = НомКолонки;
КонецЕсли;
// Проверяем маркеры цены
Если Найти(ЗначениеЯчекиВРегистре, "ЦЕНА") > 0
Или Найти(ЗначениеЯчекиВРегистре, "СТОИМОСТЬ") > 0 Тогда
КолонкаЦены = НомКолонки;
КонецЕсли;
КонецЦикла;
// Если в одной строке нашли и товар, и цену — шапка определена
Если КолонкаТовара > 0 И КолонкаЦены > 0 Тогда
Результат.СтартоваяСтрока = НомСтроки + 1; // Данные начинаются со следующей строки
Результат.КолонкаНоменклатуры = КолонкаТовара;
Результат.КолонкаЦены = КолонкаЦены;
Возврат Результат;
КонецЕсли;
КонецЦикла;
Возврат Неопределено;
КонецФункции
Что делать, если в файле вообще нет явной шапки (например, поставщик прислал простой список без заголовков колонок)? В таком случае мы можем применить метод анализа заполненности строк и типов данных.
Проанализируем структуру типичного прайс-листа. Строки с товарами характеризуются высокой плотностью заполнения: в них почти всегда заполнены колонки с названием, количеством и ценой. При этом типы данных в колонках ниже шапки становятся однородными (в колонке количества идут строго числа, в наименовании — строки).
Алгоритм работает следующим образом:
Часто поставщики присылают объединенную информацию, например: НаименованиеТовара_АртикулПроизводителя. Для поиска в справочнике «Номенклатура» по реквизиту «Артикул» нам необходимо разделить эту строку.
Разберем, как сделать это эффективно. Если в качестве разделителя используется символ нижнего подчеркивания «_», мы можем использовать стандартные функции работы со строками. Однако более надежным и современным способом является использование регулярных выражений (RegEx).
Рассмотрим пример разделения строки с помощью регулярных выражений в 1С:
// Функция разделяет исходную строку на Наименование и Артикул
Функция РазобратьСтрокуТовара(ВходнаяСтрока) Экспорт
Результат = Новый Структура("Наименование, Артикул", "", "");
// Используем RegExp для поиска разделителя "_"
RegExp = Новый COMОбъект("VBScript.RegExp");
RegExp.IgnoreCase = Истина;
RegExp.Global = Ложь;
// Шаблон ищет текст до последнего символа подчеркивания и после него
RegExp.Pattern = "^(.+)_([^_]+)$";
Если RegExp.Test(ВходнаяСтрока) Тогда
Matches = RegExp.Execute(ВходнаяСтрока);
Match = Matches.Item(0);
Результат.Наименование = СокрЛП(Match.SubMatches.Item(0));
Результат.Артикул = СокрЛП(Match.SubMatches.Item(1));
Иначе
// Если разделитель не найден, считаем всю строку наименованием
Результат.Наименование = СокрЛП(ВходнаяСтрока);
КонецЕсли;
Возврат Результат;
КонецФункции
Использование COM-объекта Excel.Application считается устаревшим подходом по нескольким причинам: он требует обязательного наличия установленного Microsoft Office на сервере или клиенте, работает крайне медленно на больших файлах и абсолютно несовместим с серверами 1С под управлением операционной системы Linux.
Современный стандарт разработки в 1С предписывает использовать метод Прочитать() объекта ТабличныйДокумент. Этот метод работает нативно, быстро, напрямую на сервере и не требует стороннего ПО.
Выполним чтение файла и выгрузку данных в ТаблицуЗначений с помощью платформенных механизмов:
Функция ПрочитатьExcelВТаблицуЗначений(ПутьКФайлу) Экспорт
ТабДок = Новый ТабличныйДокумент;
Попытка
ТабДок.Прочитать(ПутьКФайлу, СпособФорматированияТабличногоДокумента.Авто);
Исключение
ВызватьИсключение "Не удалось прочитать файл Excel: " + ОписаниеОшибки();
КонецПопытки;
// Определяем границы таблицы через эвристический поиск
ПараметрыТаблицы = ОпределитьГраницыТаблицы(ТабДок);
Если ПараметрыТаблицы = Неопределено Тогда
ВызватьИсключение "Не удалось автоматически определить структуру таблицы!";
КонецЕсли;
// Создаем результирующую таблицу значений
ТЗ = Новый ТаблицаЗначений;
ТЗ.Колонки.Добавить("НоменклатураТекст", Новый ОписаниеТипов("Строка"));
ТЗ.Колонки.Добавить("Артикул", Новый ОписаниеТипов("Строка"));
ТЗ.Колонки.Добавить("Цена", Новый ОписаниеТипов("Число", Новый КвалификаторыЧисла(15, 2)));
ВсегоСтрок = ТабДок.ВысотаТаблицы;
Для НомСтроки = ПараметрыТаблицы.СтартоваяСтрока По ВсегоСтрок Цикл
СтрокаТовараRaw = ТабДок.Область(НомСтроки, ПараметрыТаблицы.КолонкаНоменклатуры).Текст;
СтрокаЦеныRaw = ТабДок.Область(НомСтроки, ПараметрыТаблицы.КолонкаЦены).Текст;
// Пропускаем пустые строки
Если ПустаяСтрока(СтрокаТовараRaw) Тогда
Продолжить;
КонецЕсли;
// Разбираем строку номенклатуры на наименование и артикул
ДанныеНоменклатуры = РазобратьСтрокуТовара(СтрокаТовараRaw);
НоваяСтрока = ТЗ.Добавить();
НоваяСтрока.НоменклатураТекст = ДанныеНоменклатуры.Наименование;
НоваяСтрока.Артикул = ДанныеНоменклатуры.Артикул;
// Приводим цену к числу, очищая от лишних символов и пробелов
ЧистаяСтрокаЦены = СтрЗаменить(СтрокаЦеныRaw, Символы.НПП, ""); // неразрывный пробел
ЧистаяСтрокаЦены = СтрЗаменить(ЧистаяСтрокаЦены, " ", "");
ЧистаяСтрокаЦены = СтрЗаменить(ЧистаяСтрокаЦены, ",", ".");
НоваяСтрока.Цена = Число(ЧистаяСтрокаЦены);
КонецЦикла;
Возврат ТЗ;
КонецФункции
Если задача распознавания документов от различных поставщиков становится массовой, ручное написание парсеров под каждый чих становится нерентабельным. В таких сценариях рекомендуется использовать современные инструменты автоматизации:
Использование комбинации эвристического поиска шапки по ключевым словам и встроенных средств чтения ТабличныйДокумент позволяет создавать быстрые, надежные и кроссплатформенные механизмы загрузки данных, не зависящие от версий установленного Microsoft Office и готовые к работе в облачных средах и под управлением Linux.