Интеллектуальная загрузка в 1С счетов, накладных из файлов электронных таблиц (типа XLS), не требующая настроек, с указанием номеров строк и колонок. Для примера обработка загрузки в документ "Поступление товаров и услуг" для "Управление Торговлей 10.3"

Опубликовал Alexandr Kuritsyn (hibico) в раздел Обработки - Обработка документов

Существует большое количество разработок для загрузки документов из электронных таблиц, в том числе и на ИС. Для загрузки необходимо для каждого вида документа производить настройку с указанием номеров строк и колонок для сопоставления реквизитов документа и ячеек в таблице. Предлагаю отказаться от различных настроек с указанием колонок и строк. Сопоставления данных производить автоматически на основе анализа структуры табличного документа.

Основная идея в следующем.
1. Производится загрузка файла в табличный документ.
2. Далее построчный анализ документа (заодно ищем ИНН для определения поставщика).
3. При нахождениии в одной строке ячеек, содержащих текст "Наименование (Товар)", "Цена" и "Кол" определяемся с окончанием шапки документа и началом табличной части. Тут же по тексту в ячейках определяем номера колонок и для других данных (код, штрихкод, артикул).
4. Загружаем данные из найденных колонок в табличную часть обработки.

Конечно, электронный документ в этом случае должен соответствовать определенным требованиям: 

1) в табличной части счета должны присутствовать, как минимум, колонки, содержащие наименование товара, количество и цену;
2) в название колонки с наименованием товара должно содержаться сочетание «товар» или «наименование»;
3) в названии колонки с количеством должно быть сочетание «кол»;
4) в названии колонки с ценой - сочетание «цена»;
5) в названии колонки с штрихкодом товара — сочетание «штрих»;
6) в названии колонки с кодом товара — сочетание «код»;
7) в название колонки с артикулом товара — сочетание «артикул»;
8) следует также иметь ввиду, что при наличии в счете нескольких колонок с количеством или ценой будут использоваться первые найденные.
 
Ну, а теперь попорядку.

Загрука файла в табличный документ 1С.
В интернете существует большое количество вариантов для этого. Ниже представлен вариант с использованием "OpenOffice". 

// возращает Табличный Документ со считанной информацией
Функция СчитатьФайл(Знач ПутьКФайлу,текЛист) //OpenOffice Calc//

   
Попытка
       
ServiceManager = Новый COMОбъект("com.sun.star.ServiceManager");
    Исключение
       
Предупреждение(ОписаниеОшибки() + Символы.ПС + "программа OpenOffice\LibreOffice не установлена на данном компьютере!");
        Возврат Неопределено;
    КонецПопытки;

   
текЛист = ?(текЛист=0, 1, текЛист);
   
ПутьКФайлу = СтрЗаменить(ПутьКФайлу," ","%20");
   
ПутьКФайлу = СтрЗаменить(ПутьКФайлу,"\","/");
   
ПутьКФайлу = "file:/" + "/localhost/" + ПутьКФайлу;

   
Desktop = ServiceManager.createInstance("com.sun.star.frame.Desktop");
   
Args = Новый COMSafeArray("VT_VARIANT", 3);
   
Свойства = ServiceManager.Bridge_GetStruct("com.sun.star.beans.PropertyValue");
   
Свойства.Name = "AsTemplate";
   
Свойства.Value = Истина;
   
Args.SetValue(0, Свойства);
   
Свойства1 = ServiceManager.Bridge_GetStruct("com.sun.star.beans.PropertyValue");
   
Свойства1.Name = "Hidden";
   
Свойства1.Value = Истина;
   
Args.SetValue(1,Свойства1);
   
Свойства2 = ServiceManager.Bridge_GetStruct("com.sun.star.beans.PropertyValue");
   
Свойства2.Name = "Password";
   
Свойства2.Value = ПарольНаФайл;
   
Args.SetValue(2,Свойства2);
   
Document = Desktop.LoadComponentFromURL(ПутьКФайлу, "_blank", 0, Args);
    Если
Document = Неопределено Тогда
       
Desktop.terminate();
       
Предупреждение("Ошибка чтения файла! Возможно необходимо указать пароль.");
        Возврат Неопределено;
    КонецЕсли;
   
Sheets = Document.getSheets();
   
Sheet = Sheets.getByIndex(текЛист-1);

   
Cursor = Sheet.CreateCursor();
   
Cursor.GoToStartOfUsedArea(Ложь);
   
Cursor.GoToEndOfUsedArea(Истина);
   
КоличествоЗаполненныхКолонок        = Cursor.Columns.Count;
   
КоличествоЗаполненныхСтрок        = Cursor.Rows.Count;
   
RangeAddress = Cursor.GetRangeAddress();
   
ПерваяЗаполненнаяКолонка        = RangeAddress.StartColumn;
   
ПерваяЗаполненнаяСтрока            = RangeAddress.StartRow;
   
ПоследняяЗаполненнаяКолонка        = RangeAddress.EndColumn;
   
ПоследняяЗаполненнаяСтрока        = RangeAddress.EndRow;

   
Range = Sheet.getCellRangeByPosition(ПерваяЗаполненнаяКолонка, ПерваяЗаполненнаяСтрока, ПоследняяЗаполненнаяКолонка, ПоследняяЗаполненнаяСтрока);
   
МассивКом = Range.getDataArray();
   
тмпПростойМассив = МассивКом.Выгрузить();

   
ТабДокумент = Новый ТабличныйДокумент;

   
НомСтр = 0;
    Для каждого
массив из тмпПростойМассив Цикл
       
НомСтр = НомСтр+1;
       
НКол = 0;
        Для каждого
ЭлементМассива из массив цикл
           
НКол = НКол+1;
           
ЗапОбласть = ТабДокумент.Область(НомСтр,НКол);
           
ЗапОбласть.Текст = СокрЛП(Формат(ЭлементМассива,"ЧГ=0"));
        КонецЦикла;
    КонецЦикла;

   
Document.close(True);
   
Desktop.terminate();

    Возврат
ТабДокумент;

КонецФункции

Далее анализируем табличный документ и заполняем таблицу значений.


Процедура ЗаполнитьТаблицуПоДокументу(ТабДокумент)

    Перем
ТоварНК, КоличествоНК, ЦенаНК, АртикулНК, КодНК, ШКодНК; // номера колонок
   
Шапка = Истина; ТабличнаяЧасть = Ложь; НашлиИНН = Ложь;

   
ВысотаТаблицы = ТабДокумент.ВысотаТаблицы;
   
ШиринаТаблицы = ТабДокумент.ШиринаТаблицы;

    Для
ТекСтрока = 1 По ВысотаТаблицы Цикл // цикл по строкам

       
Если ТабличнаяЧасть Тогда // разбираем строку табличной части
            // проверим на служебные строки и подвал
           
Попытка
               
// товар
               
ТекОбласть = ТабДокумент.Область(ТекСтрока,ТоварНК);
               
Товар = ТекОбласть.Текст;
               
// Количество
               
ТекОбласть = ТабДокумент.Область(ТекСтрока,КоличествоНК);
               
Количество = Число(ТекОбласть.Текст);
               
// Цена
               
ТекОбласть = ТабДокумент.Область(ТекСтрока,ЦенаНК);
               
Цена = Число(ТекОбласть.Текст);
                Если
СтрДлина(Товар)<4 ИЛИ Количество=0 ИЛИ Цена=0 Тогда
                    Продолжить;
                КонецЕсли;
            Исключение
                Продолжить;
            КонецПопытки;
           
// Добавляем строку в заполняемую таблицу
           
НовСтрока = ТаблицаЗагрузки.Добавить();
           
НовСтрока.Наименование = Товар;
           
НовСтрока.Количество = Количество;
           
НовСтрока.Цена = Цена;
           
НовСтрока.Сумма = Цена*Количество;
            Если
ЕстьКод Тогда // Код
               
ТекОбласть = ТабДокумент.Область(ТекСтрока,КодНК);
               
НовСтрока.Код = ТекОбласть.Текст;
            КонецЕсли;
            Если
ЕстьШтрихкод Тогда // Штрихкод
               
ТекОбласть = ТабДокумент.Область(ТекСтрока,ШКодНК);
               
НовСтрока.Штрихкод = ТекОбласть.Текст;
            КонецЕсли;
            Если
ЕстьАртикул Тогда // Артикул
               
ТекОбласть = ТабДокумент.Область(ТекСтрока,АртикулНК);
               
НовСтрока.Артикул = ТекОбласть.Текст;
            КонецЕсли;
        КонецЕсли;

        Если
Шапка Тогда  // разбираем шапку документа
           
ТоварНК = 0; КоличествоНК = 0; ЦенаНК = 0;
           
АртикулНК = 0; КодНК = 0; ШКодНК = 0;
            Для
ТекКолонка = 1 По ШиринаТаблицы Цикл // цикл по колонкам
               
ТекОбласть = ТабДокумент.Область(ТекСтрока,ТекКолонка);
               
ТекТекст = ТекОбласть.Текст;
                Если
ПустаяСтрока(ТекТекст) Тогда
                    Продолжить;
                КонецЕсли;
                Если  Не
НашлиИНН И Найти(ТекТекст,"ИНН")>0 Тогда   // ищем ИНН
                   
Для нк = ТекКолонка По ТабДокумент.ШиринаТаблицы Цикл
                       
ОбластьИНН = ТабДокумент.Область(ТекСтрока,нк);
                       
ИНН = НайтиИНН(ОбластьИНН.Текст);
                        Если Не
ПустаяСтрока(ИНН) Тогда
                           
НашлиИНН = Истина;
                           
НайтиПоставщика(ИНН);
                            Прервать;
                        КонецЕсли;
                    КонецЦикла;
                ИначеЕсли
ТоварНК=0 И Найти(ВРег(ТекТекст),"ТОВАР")>0 ИЛИ Найти(ВРег(ТекТекст),"НАИМЕНОВАНИЕ")>0 Тогда
                   
ТоварНК = ТекКолонка;
                ИначеЕсли
КоличествоНК=0 И Найти(ВРег(ТекТекст),"КОЛ")>0 Тогда
                   
КоличествоНК = ТекКолонка;
                ИначеЕсли
ЦенаНК=0 И Найти(ВРег(ТекТекст),"ЦЕНА")>0 Тогда
                   
ЦенаНК = ТекКолонка;
                ИначеЕсли
АртикулНК=0 И Найти(ВРег(ТекТекст),"АРТИКУЛ") Тогда
                   
АртикулНК = ТекКолонка;
                ИначеЕсли
ШКодНК=0 И Найти(ВРег(ТекТекст),"ШТРИХ")>0 Тогда
                   
ШКодНК = ТекКолонка;
                ИначеЕсли
КодНК=0 И Найти(ВРег(ТекТекст),"КОД")>0 Тогда
                   
КодНК = ТекКолонка;
                КонецЕсли;
            КонецЦикла; 
// по колонкам в шапке

           
Если ТоварНК>0 И КоличествоНК>0 И ЦенаНК>0 Тогда // найден заголовок ТЧ
               
Шапка = Ложь;
               
ТабличнаяЧасть = Истина;
               
ЕстьКод = КодНК>0;
               
ЕстьШтрихкод = ШКодНК>0;
               
ЕстьАртикул = АртикулНК>0;
               
ТаблицаЗагрузки.Очистить();
            КонецЕсли;
        КонецЕсли;

    КонецЦикла;
// по строкам

   
Если Шапка Тогда
       
Предупреждение("Не найдена табличная часть в документе!");
    КонецЕсли;

КонецПроцедуры
 

Используемые тут функции "НайтиИНН" и "НайтиПоставщика" можно посмотреть в прилагаемой обработке.

Конечно, ничего идеального не существует. Так некоторые документы (например Торг-12) со сложными шапками без редактирования нормально не загрузятся. Но идея имеет право на жизнь

Для примера выкладываю обработку загрузки файлов в документ "Поступление товаров и услуг" для "Управление торговли 10.3".
Обработка подключается в качестве "Внешней обработки загрузки табличных частей" (Сервис - Внешнии печатные форму и обработки - Внешнии обработки загрузки табличных частей).
В прилагаемой обработке для сопоставления номенклатуры используется регистр сведений "Номенклатура контрагентов", что в принципе не очень удобно и не совсем правильно. Но тем не менее работает и не требует изменения конфигурации. У меня используется для сопоставления собственный регистр сведений "
СоответствиеТоваровПоставщика".

Информация для тех, кто тоже решит использовать такой же регистр:

Регистр независимый, непереодический. 

Измерения: "Поставщик"(СправочникСсылка.Контрагент, ведущее), "ПолеПоиска"(Строка, 15), "СтрокаПоиска"(Строка, 150). 

Ресурс: "Номенклатура"(СправочникСсылка.Номенклатура). 

Для работы обработки с данным регистром в модуле обработки в процедурах "ЗаполнениеСоответствий" и "СохранениеСоответствий" нужно раскоментировать закоментированное и закоментировать незакоментированное. 

Краткая инструкция к обработке.

Загрузка счета производится из документа «Поступление товаров и услуг» через кнопку меню табличной части «Заполнить» -> «Заполнить из электронного документа».

В форме обработке необходимо указать путь к файлу со счетом, при наличии в файле нескольких листов можно указать порядковый номер загружаемого листа из файла (по умолчанию 1). Если файл запоролен, указать пароль.

При нажатии «Загрузить файл» производится считывание и заполнение табличной части обработки. При этом, если в счете присутствуют поля «Код», «Штрихкод» и «Артикул», в табличной части появятся аналогичные колонки. Информационная строка под табличной частью служит для контроля правильности загрузки. Поле «НДС» служит только для формирования данной строки.

В момент загрузки файла производится автоматический поиск соответствий в определенном регистре сведений согласно установленного «Поставщика» и «Метода поиска» (по наименованию, по коду, по штрхкоду или по артикулу). При нахождение определенного ранее соответствия будет заполнено поле «Ссылка в базе». Для строк с неопределенными ранее соответствиями поиск будет продолжен в справочнике "Номенклатура" по полям «Полное наименование», «Штрихкод» и «Артикул». Строки с найденными совпадениями в данном случае будут помечены голубым фоном. Автоматический поиск соответствий также можно запустить вручную кнопкой «Найти соответствия». Позиции с ненайденными соответствиями будут подсвечены розовым цветом.

Для остальных позиций без совпадений необходимо в табличной части указать соответствующую данной позиции номенклатуру из базы. Строки с указанными вручную соответсвиями будут подсвечены голубым цветом. После указания соответствий для автоматического сопоставления в дальнейшем, необходимо произвести запись с помощью кнопки «Записать соответствия». При этом будут сохранены в базе данные сопоставления для данного «Поставщика» согласно указанному «Методу поиска». Предпочтительней для метода поиска использовать поиск «по коду» , «по артикулу» или «по штрихкоду».

Загрузка позиций в документ производится после указания всех соответствий кнопкой «Заполнить документ и закрыть». 

Во второй версии обработки появилась возможность настройки сочетаний для поиска колонок (типа "Цена", "Кол" и т. п.). Для настройки нужно нажать кнопку со значком "Настройка" над кнопкой "Закрыть".

Скачать файлы

Наименование Файл Версия Размер
Обработка заполнения табличной части "Поступление товаров и услуг" (в. 2)
.epf 38,44Kb
30.09.16
23
.epf 2 38,44Kb 23 Скачать

См. также

Комментарии
1. Дмитрий Жиляков (Zhilyakovdr) 79 21.09.16 11:40 Сейчас в теме
Добрый день!
Сейчас решаю подобную задачу, только исходный материал документы на бумаге.
Документы сканируются поточным сканером, по заданию распознаются и кладутся в формате xlsx (его проще обрабатывать), далее в 1с регламентное задание при помощи регулярных выражений (по вхождению контрольной строки) распознает тип документа и таким же образом значимые данные типа инн и кпп, как организации так и контрагента.
Даже при такой степени распознания от настроек под конкретный загружаемый вид документа уйти не удалось.
Не знаю каким образом вы ищите ИНН и КПП, но как вариант:

........................
ОрганизацияИНН	= ДанныеДляЗаполнения.Организация.ИНН; 
Для каждого стр Из МассивSharedStrings Цикл
	
	МассивИНН	= грт_НаКлиентеИНаСервере.ВыполнитьРегулярноеВыражение(стр, "[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]");
	Если не МассивИНН.найти(ОрганизацияИНН) = Неопределено Тогда МассивИНН.удалить(МассивИНН.найти(ОрганизацияИНН)); КонецЕсли; 
	Если 		МассивИНН.количество() = 1 Тогда КонтрагентИНН	= МассивИНН[0];
	ИначеЕсли	МассивИНН.количество() > 1 Тогда КонтрагентИНН	= МассивИНН[0];
	КонецЕсли; 
		
КонецЦикла; 
......................
...Показать Скрыть


Функция ВыполнитьРегулярноеВыражение(ИсходнаяСтрока, Шаблон) Экспорт
	
	RegExp 				= грт_НаКлиентеИНаСервереПовтИсп.RegExp();
	RegExp.Pattern 		= Шаблон;
	RegExp.IgnoreCase	= истина;
	Результат = RegExp.Execute(ИсходнаяСтрока); 
	МассивСовпадений	= новый массив;
	Для Счетчик = 0 По Результат.Count - 1 Цикл 
		Совпадение = Результат.Item(Счетчик);
		МассивСовпадений.Добавить(Совпадение.Value);
	КонецЦикла;
	
	возврат	МассивСовпадений;
	
КонецФункции // ВыполнитьРегулярноеВыражение()
...Показать Скрыть


Функция RegExp() Экспорт
	
	Возврат	Новый COMОбъект("VBScript.RegExp");
	
КонецФункции
...Показать Скрыть


Надеюсь эти выдержки из кода помогут оптимизировать ваше решение.
2. Alexandr Kuritsyn (hibico) 216 21.09.16 15:36 Сейчас в теме
(1) Zhilyakovdr,
Идея понятна, возьму на вооружение. Единственно, как быть, если бывает ИНН с 12-ю символами.

У меня в данной обработке всё проще:
// ищет в переданной строке ИНН
Функция НайтиИНН(Текст)
	
	ОбрТекст = СтрЗаменить(Текст," ",Символы.ПС);
	Для н = 1 По СтрЧислоСтрок(ОбрТекст) Цикл
		Стр = СтрПолучитьСтроку(ОбрТекст,н);
		ДлинаСтр = СтрДлина(Стр);
		Если ДлинаСтр <> 10 И ДлинаСтр <> 12 Тогда
			Продолжить;
		КонецЕсли;
		Если Не ЭтоЦифры(Стр) Тогда
			Продолжить;
		КонецЕсли;
		Если ПравильныйИНН(Стр) Тогда
			Возврат Стр;
		КонецЕсли;
	КонецЦикла;
	Возврат "";
	
КонецФункции

// возвращает Истина, если строка состоит из цифр
Функция ЭтоЦифры(Текст)
	
	Для н = 1 По СтрДлина(Текст) Цикл
		Если Найти("0123456789",Сред(Текст,н,1))=0 Тогда
			Возврат Ложь;
		КонецЕсли;
	КонецЦикла;
	Возврат Истина;
	
КонецФункции
...Показать Скрыть

Функция ПравильныйИНН - больше для баловства, проверяет на правильность ИНН по контрольным числам.
3. Alexandr Kuritsyn (hibico) 216 30.09.16 12:21 Сейчас в теме
Обновлена процедура "ЗаполнитьТаблицуПоДокументу()" в тексте статьи на более правильную.
Также обновлен файл обработки. Новая версия лучше обрабатывает файлы со сложной структурой. Также появилась возможность настройки списка сочетаний для поиска колонок.
Оставьте свое сообщение