Согласитесь, было бы неплохо получить инструмент, который принимал бы запросы к данным со слабовыраженной и нестабильной структурой в том виде, в котором человек объяснял бы другому человеку правила по которым он глазами находит колонку "цена" в УПД. Ну, например:
о цене мы точно знаем, что это число, оно находится в колонке с названием содержащим слово "цена", и оно располагается между наименованием и ставкой НДС
Сформулированный "запрос" содержит основные критерии (тип, название колонки) и "подстраховку" (расположение относительно других данных), которые не позволят выбрать что-то лишнее. При этом, как вы могли заметить, такой "запрос" не привязан к какому-то одному формату, с помощью него можно успешно найти цену и в УПД и в Накладной и в ТОРГ-12. И уж тем более, там ничего не написано ни про номер колонки в которой стоит цена ни про номер строки с которой начинаются товары.
Но если так может человек, почему так не может 1С?
Если обобщить, мы хотим получить возможность писать "запрос", атрибутирующий любые данные, имеющие непостоянную структуру, через непосредственно само значение и его окружение.
Фактически, такой "запрос" можно получить в виде фразы ГДЕ языка запросов 1С, построив нужным (и автоматическим) образом фразу ИЗ. Фраза ВЫБРАТЬ особого интереса не представляет и будет статичной. Если "перевести" описание колонки Цена, данное выше, с человеческого на язык запросов 1С, получится примерно такое:
ГДЕ ТипЗначения(Слово) = Тип(Число) И СловаВыше ПОДОБНО ""%ЦЕНА%"" И СловаСлева ПОДОБНО ""%[А-Я]%"" И СловаСлева.Длина > 3 И ТипЗначения(СловаСправа) = Тип(Число)
Разберемся, в том, что тут понаписано.
Основные параметры
Во-первых надо понять, что мы имеем дело с обычным запросом, ну т.е. с его фразой "ГДЕ". Соответственно, для описания условий нам доступны все возможности языка запросов: сравнения, равенства, манипуляции периодами, математические выражения, оператор "ПОДОБНО", и все остальное, что только можно написать в обычном запросе.
Во-вторых, для рассмотрения доступно само поле. О нем доступны следующие сведения:
- в реквизите Слово.Значение хранится "чистое" значение поля.
- в реквизите Слово.ОригинальноеЗначение хранится значение поля в том виде, в котором оно было в изначальном экселе, без очистки.
- в реквизитах Слово.НомерСтроки и Слово.НомерКолонки хранятся координаты позиции слова в исходной таблице.
- Слово.Длина содержит длину строки (для не строк - длину строкового представления соответствующего значения).
Для наглядности и простоты написания, выражения "Слово.Значение" и "Слово" - эквивалентны, т.е. когда вы не уточняете какое имеете ввиду поле у таблицы "Слово" - будет подставлено поле по-умолчанию "Значение".
В-третьих, доступно все окружение каждого поля. Например, если мы хотим описать какое-то поле через его предшественника - ставку НДС, мы можем написать "СловаСлева ПОДОБНО "18\%". Другой пример - описание поля через заголовок столбца: "СловаСверху ПОДОБНО "%ЦЕНА%". Ну и так далее, всего возможно 8 вариантов обращения к окружению, все описаны в справке. Каждое из окружающих слов содержит туже структуру его описания, что и само Слово (Значение, ОригинальноеЗначение, НомерСтроки, НомерКолонки, Длина). И также, при опускании ".Значение" - оно будет поставлено автоматически (т.е. записи "СловаСлева" и "СловаСлева.Значение" эквивалентны).
В-четвертых, требуется осознать следующую вещь: "Слева" - это не значит "непосредственно в соседней слева ячейке". Это значит где-то слева. Такой подход помогает нам легче обращаться к наименованиям колонок (они же могли быть и 3 и 10 строк назад), а также не иметь проблем при объединениях ячеек. Но, если есть уверенность в непосредственной близости каких-то данных, всегда можно написать "СловаСлева.НомерКолонки + 1 = Слово.НомерКолонки"
В-пятых, в каждом направлении (влево, вверх...) мы можем анализировать не одно поле. В таком случае мы можем обзывать поля как хотим, главное - оставить префикс из того набора, который у нас есть. Например, при поиске количества хотим чтобы оно следовало за "шт" и "796": "СловаСлеваИмяЕдИзм = "ШТ" И СловаСлеваКодЕдИзм = 796". Естественно, если написать еще какое-то условие для синонима СловаСлеваКодЕдИзм - будет понятно, что это требование к тому же самому полю, а не к третьему.
Углубимся в технику
Технически, весь объем данных (Excel, табличный документ, Word, не важно) помещается в таблицу значений с колонками Значение, ОригинальноеЗначение, НомерСтроки, НомерКолонки, Длина, где каждая запись соответствует одной ячейке. Далее, таблица значений помещается в запрос и она соединяется сама с собой по правилам, в результате которых каждому слову (синоним Слово) получаются сопоставлены слова стоящие в той же строке слева от него (синоним СловаСлева), в той же строке справа от него (СловаСправа) в той же колонке выше него (СловаВыше) и в той же колонке ниже (СловаНиже). Области этих четырех соединений схематично показаны ниже, на первой схеме.
Кроме того, есть еще 4 таблицы: представляющие все ячейки справа (ВсеСловаСправа) и все ячейки слева (ВсеСловаСлева), независимо от строки - вторая схема. И все ячейки сверху (ВсеСловаВыше) и все ячейки снизу (ВсеСловаНиже) независимо от колонки - третья схема. Т.е. если предыдущие таблицы представляли собой "линию" из ячеек, то эти таблицы - это поле ячеек. Соединения всех таблиц происходит по координатам ячейки в реквизитах НомерСтроки и НомерКолонки. И, естественно, реально объявляются во фразе ИЗ только те таблицы, которые используются во фразе ГДЕ.
Есть также дополнительный вариант, для "условно-не-табличных" данных (например, требуется вычленить из шапки документа номер, дату) - хорошо работает принцип когда каждое слово по одному помещаются в таблицу (получается, что каждое конкретное слово становится доступно для анализа отдельно от остального окружения в ячейке). За это отвечает реквизит РежимРаботы функции ТаблицаСловПоМассиву. Нумерация строк и колонок при этом остается как в оригинале. Так очень эффективно бороться, например, с датами (их пишут все кому как в голову взбредет: кто-то в одной ячейке, кто-то в трех, кто-то еще псевдо-печатное оформление оставляет в стиле "___")
Также отдельное внимание стоит обратить на разборщика входящих данных (функция ОкультуритьТекст). Эта функция строит т.н. "чистое" значение по оригинальному значению, без нее весь банкет был бы невозможен. Она работает так:
- переводит все в верхний регистр
- определяет соответствует ли значение одному из шаблонов даты, если да - пытается привести значение к типу дата.
- определяет можно ли привести значение к числу. если да - приводит. при этом разные написания вроде "1 234.50", "1`234,50", "1.234,50" и т.п. будут обработаны успешно.
- для строк - все символы кроме букв, цифр и символов "-", "/", "(", ")", заменяет на пробелы, удаляет пробелы в начале и конце строки, удаляет повторяющиеся пробелы.
В результате ее работы мы получаем т.н. "чистое" значение, однако, если нам все-же захочется проанализировать оригинальное значение - оно сохраняется в реквизите ОригинальноеЗначение.
Визуальная консоль запросов
Для проверки запросов в процессе разработки функциональности (ну и для поиграться) предназначена визуальная консоль, которую вы видите в гифках и можете скачать ниже. Пользоваться консолью проще простого:
- в табличный документ консоли копи-пастим эксель
- пишем текст своего запроса
- жмем выполнить - соответствующие запросу ячейки будут подсвечены
В консоли есть справка и простейшие примеры. В модуле объекта изолированы процедуры, которые вы должны переместить в свой общий серверный модуль для программного использования.
Методика программного использования
При разборе документов у меня методика использования получилась следующая:
- Думаем, какой столбец будет опорным. Это должен быть столбец, определить элементы которого можно с наименьшей погрешностью. Я выбрал цену. (многообещающе выглядела ставка НДС, но ее нет в некоторых форматах)
- Делаем запрос ко всем значениям опорного столбца. Для цены у меня такой запрос: "ГДЕ СловаСлева ПОДОБНО ""%[А-Я]%"" И СловаСлева.Длина > 3 И ТипЗначения(СловаСправа) = Тип(Число) И СловаВыше ПОДОБНО ""%ЦЕНА%"" И ТипЗначения(Слово) = Тип(Число)"
- Далее, пишем по одному запросу к каждому типу значений (один запрос - количество, второй - наименование, третий - сумма и т.д.), используя в них конструкции типа Слово.НомерСтроки В (&НомераСтрокОпорныхЗначений).
- Если нужно получить одно значение, а получить четко одно невозможно - можно приоритезировать результаты поиска по каким-то критериям, например, по номеру колонки или длине строки (при программном использовании доступна также установка фразы УПОРЯДОЧИТЬ ПО) Вроде как не совсем красиво выбирать наименование между "шт" и "Туфли женские" опираясь на длину строки, но мне не встречалась ситуация, когда такой критерий подводил.
Есть и более простой вариант: написать по одному запросу, каждый из которых вернет значения всех соответствующих областей независимо ни от какого другого, но при таком подходе существенно возрастает риск, что запрос "зацепит" какие-то данные, которые вы не планировали получать, хотя если в консоли такие запросы успешно обкатаются - можно и так.
Код полностью открыт, запароленных и/или обфусцированных участков кода нет. Писал на платформе 8.3.9.2233. Удачи!
