gifts2017

Интеллектуальная загрузка в 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) 14
.epf 38,44Kb
30.09.16
14
.epf 2 38,44Kb Скачать

См. также

Подписаться Добавить вознаграждение

Комментарии

1. Дмитрий Жиляков (Zhilyakovdr) 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) 21.09.16 15:36
(1) Zhilyakovdr,
Идея понятна, возьму на вооружение. Единственно, как быть, если бывает ИНН с 12-ю символами.

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

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

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