gifts2017

Нестандартная загрузка из Excel

Опубликовал Яков Коган (Yashazz) в раздел Обмен - Загрузка и выгрузка в Excel

Взгляд на привычную задачу под неожиданным углом - пусть пользователь прямо в Excel решает, что ему надо, и грузит в 1С только нужное. Установите любой отбор, выделите фрагмент, и вот он уже в 1С. Для обычных и УФ 8.3

Достаточно хорошо известны различные способы загрузки данных из MS Excel 97-2010, а именно - COM-соединение, ADO, COMSafeArray и прочие извраты; но почти все их реализации для 1С совпадают в одном. Из 1С указывается, какой файл и лист(ы) интересуют пользователя, после чего выполняется некий явный или скрытый вызов, обращение, читающее данные в 1С. В ряде случаев уже на стороне 1С предполагается разбирательство с прочитанными данными, их дальнейшая обработка и анализ.

Но что, если пользователю сложно/неинтересно копаться в наворотах обработки-загрузчика на стороне 1С? Или если у вас нет времени делать такие навороты с прочитанными данными, а пользователь прекрасно владеет экселем, умеет сам ставить там нужные отборы? Если пользователь в принципе хочет определить множество данных, подлежащих загрузке в 1С, на стороне экселя и чисто его средствами?

Оговорюсь, речь не идёт о копипасте простой области в mxl, откуда уже можно прочитать. Там, знаете ли, и формулы могут оказаться, и жуткие объединённые ячейки, и вообще размер области иной раз такой, что копипастится десятки минут.

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

Важно: вы можете использовать любой отбор, отфильтровать данные и просто выделить их. Отбор по значению, цвету, шрифту - всё к вашим услугам. Потом выделяете получившееся, мышкой или клавиатурой, и нажимаете спецкнопку - и все данные уже в 1С именно согласно отбору. См. картинки с 4 по 7.

Обновление: добавлена возможность указать конкретные колонки-приёмники в 1С, и сопоставить им столбцы в экселе. Если задействован этот режим (указана хоть одна колонка), читаются только данные из сопоставленных столбцов (т.е. Select F1..FN, а не Select *). См. картинки 8 и 9.

 

Техническая реализация:

1. Обработка 1С запускает com-соединение экселя с указанным файлом, добавляя в код проекта свой модуль и процедуры обработки событий, в инструментальную панель свои кнопки, и цепляя подписки на штатные события экселя;

2. Нажатие кнопки чтения выделенного фрагмента вызывает событие, перехватываемое обработкой 1С, где и происходит считывание средствами ADO. Считываются все ячейки выделенного фрагмента, их значения приводятся к строковым (IMEX=1), хотя можно и более внимательно рассмативать типы. Условий в запросе ADO нет.

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

4. В коде в закомментированном виде есть возможность кнопки чтения всех данных текущего листа целиком.

5. Попытка сохранения изменений заблокирована, чтобы всё добавленное в п.1 не сохранилось навечно (принудительную удалялку добавленных фич я не писал, но это несложно сделать). Попытка закрытия перехватывается 1С.

6. Всё вышеперечисленное возможно при наличии доступа к VB IDE, что проверяется в реестре и,  при необходимости и наличии прав у пользователя, может включаться/выключаться.

Далее заполнения таблицы значений ничего не делал - т.к. публикация рассчитана на разработчиков, то думаю, коллеги, все сами смогут распорядиться прочитанными данными по своему усмотрению.

Собственно, можно обойтись и без ADO, это мне захотелось так сделать; на идее реализации способ чтения не сказывается.

UPD: сделана версия для УФ 8.3 с учётом модной нынче немодальности.

Кто найдёт баги - сообщайте, буду оперативно дорабатывать.

p.s. А всё просто потому, что мне поставили задачу "загрузить из экселя", брать готовую (свою или чужую) выходило громоздко, а писать в сотый раз одни и те же баяны уже не было сил. Вот и захотелось извратиться)))

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

Наименование Файл Версия Размер Кол. Скачив.
ExcelExp
.epf 1,69Mb
04.01.15
243
.epf 1,69Mb 243 Скачать

См. также

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

Комментарии

1. Яков Коган (Yashazz) 12.11.14 17:41
Да, ещё что забыл: работа под Excel 2003 и древнее предполагается, но не гарантируется (не тестировал).
2. Станислав Раташнюк (stanru1) 12.11.14 18:06
Идея понравилась, спасибо!
3. Pavel Fomin (Pasha1st) 12.11.14 23:10
Очень интересное решение! + за оригинальный подход.
4. Александр Лыткин (TrinitronOTV) 13.11.14 05:54
Это всё хорощо конечно, но вот у меня в некоторых случаях есть необходимость при загрузке данных определять ещё цвет шрифта для решения вопроса загружать эти данные или нет, так что практически все методы отпадают...
5. Яков Коган (Yashazz) 13.11.14 08:41
(4) TrinitronOTV, идею это не отменяет - можно ДО обмена в экселе поймать строки с нужным шрифтом, скриптом выделить их в selection и уж его грузануть. В том и фишка, что это гораздо проще выяснить средствами самого экселя, а не при обмене. Можно в скрипте прописать вызов события, на которое подписана 1С, тогда вообще не кнопку жамкать, а любые нужные области сразу перекидывать.

Вообще, можно ведь добавить и другие штуки - служебные элементы интерфейса, которые помогут юзеру лучше определить множество нужных данных, разметить его (например, ассоциировать колонки экселя с данными 1С прямо в экселе, выбрать режимы, итд) - словом, простор интеграции.
TrinitronOTV; +1 Ответить 1
6. Александр Лыткин (TrinitronOTV) 13.11.14 14:03
7. фокс малдер (Platon777) 14.11.14 11:06
Отличная штука! Сделаю в ней ещё себе ещё поле с выполнением произвольного кода 1С в "пользвательском" - вообще тогда ничего для Excel не нужно ...
8. Maxim Kolkin (the1) 15.11.14 00:08
Ну что могу сказать... Грандиозно!
9. Яков Коган (Yashazz) 18.11.14 02:14
Управляемая форма этой поделки кому-нибудь нужна? А то скоро сделаю, могу выложить.
10. Александр Буганов (buganov) 18.11.14 05:26
(9) Yashazz, выкладывайте, наверняка понадобится
11. UncleVader (UncleVader) 19.11.14 09:37
12. Иван Иванов (Famza) 19.11.14 11:04
13. Иван Иванов (Famza) 19.11.14 11:17
Ошибка при определении доступности обмена; возможно, у Вас нет права даже на чтение системного реестра!
ОпределитьДоступностьОбмена: {Форма.ОсновнаяФорма.Форма(194)}: Ошибка при вызове метода контекста (RegRead): Произошла исключительная ситуация (WshShell.RegRead): Не удается открыть для чтения раздел реестра "HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Security\AccessVBOM".
Обратитесь к Вашему системному администратору!

Ось ХР СП3, 1С 8.3.5.1248, права админа. Ось не поддерживается?
14. Дима (Goruch) 19.11.14 11:30
Даа, круто. Однозначно плюс.
15. Вадим Миляев (PrinzOfMunchen) 19.11.14 12:16
Да, тоже когда-то так делал, но чисто для себя. ))
16. Яков Коган (Yashazz) 19.11.14 14:43
(13) Famza, проверьте, а правильно ли определилась версия. У меня вызывает подозрение HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Security\ в пути ключа. Может, у Вас 8.0 или что-то подобное?
Я это определяю динамически, но вдруг определялка лажает или там не прямая связь с версией офиса?
kostyaomsk; +1 Ответить
17. eugenie zheludkov (eugeniezheludkov) 26.11.14 09:25
Ошибка при определении доступности обмена; возможно, у Вас нет права даже на чтение системного реестра!
ОпределитьДоступностьОбмена: {Форма.ОсновнаяФорма.Форма(194)}: Ошибка при вызове метода контекста (RegRead): Произошла исключительная ситуация (WshShell.RegRead): Не удается открыть для чтения раздел реестра "HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Security\AccessVBOM".
Обратитесь к Вашему системному администратору!

у меня в реестре ветка не так выглядит: есть HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Security\Trusted Documents, но нет HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Security\AccessVBO.
Полный офис, лицензионный. Бил Гейтс лично приезжал ставил. Так понимаю без этого ключа, данная обработка, не будет работать у всех 9000 или 10000 пользователей, при таком количестве лучше юзать типовые ?
ПС во всех компаниях где работал в домене, права у пользователя на компьютере совсем не админские, что так же накладывает ограничение на "установка низкого уровня безопасности (применится после перезапуска Excel!)" в коде :(
kostyaomsk; +1 Ответить 1
18. ValeriTim (ValeriTim) 27.11.14 10:40
Знаете что меня раздражает в подобных постах?

Человек сделал хорошую разработку и пытается ее продемонстрировать другим, однако сделать нормальные скриншоты религия, видимо, не позволяет ... Ну вот зачем делать изображение каких то данных и потом замазывать из них 90%? Неужели нельзя взять файлик, налупить туда абсолютно сторонней информации и демонстрировать?
19. Яков Коган (Yashazz) 27.11.14 14:57
(17) eugeniezheludkov, то, что "установка низкого уровня безопасности" - поосторожничал я, обычно и без этого работает. Насчёт отсутствия ключа разрешения работы с VB, надо ещё курить мануалы, навскидку не отвечу. Но это довольно частый вопрос и распространённая проблема, мелкомягкие до сих пор боятся "страшных макровирусов" и запрещают всё на свете.

(18) Упрёк про замазывание в общем-то справедлив, но вот поверьте - не могу "взять и налупить"; либо конфиденциалка, либо времени совсем йок, либо недостоверно выходит. Не умею я высасывать из пальца красивые демо-данные.
Какие ещё претензии к скриншотам, кроме замазывания?
20. Яков Коган (Yashazz) 27.11.14 14:58
Таки сделал настройку чтения по колонкам.
(10), (11) - УФ будет не раньше середины декабря, я совсем закопался в текучке, прошу извинить.
21. Алексей Т. (CratosX) 27.11.14 20:03
(20) Yashazz, подпишусь и скачаю при апдейте, круто выглядит
22. Римма Герасименко (WhiteOwl) 28.11.14 10:33
23. Fomix (fomix) 02.12.14 11:43
(21) Наверняка хорошая штука. Просьба к автору - сделай обработку 2in1 для обычных форм и под УФ, чтобы не плодить клоны. Заранее спасибо!
24. Яков Коган (Yashazz) 02.12.14 13:38
(23) fomix, только после 10 декабря. Собсно, так и собирался, 2 в 1.
26. Марина Семёнова (SemenovaMarinaV) 09.12.14 15:46
27. Марина Семёнова (SemenovaMarinaV) 09.12.14 15:47
Жду под управляемые формы
28. Павел Алексеенко (qwinter) 10.12.14 21:06
Там, знаете ли, и формулы могут оказаться, и жуткие объединённые ячейки, и вообще размер области иной раз такой, что копипастится десятки минут
И как все эти поднятые проблемы решает данная обработка? Баян.
29. Яков Коган (Yashazz) 11.12.14 20:07
(28) Очень просто: половина головной боли решается средствами экселя на стороне экселя БЕЗ какого-либо com-соединения, ДО того, как начинать игры с обменом. А размер области, учитывая, что у меня ADO-чтение, не сильно играет роль.

Но я соглашусь с Вами в части баянности, если вы в обоснование своей оценки подкинете ссылку на именно такой подход, чтобы с оператором "ДобавитьОбработчик" и автовстраиванием функционала подготовки чтения в сам эксель. Жду с нетерпением)))
30. Павел Алексеенко (qwinter) 12.12.14 10:08
(29) Yashazz, а какой смысл в встраивании, если все это уже много лет решалось копированием в область табличного документа в 1С? Причем никаких тормазов с копированием нет. Все копируется очень быстро, и все хорошо, как со скрытыми строками, так и с формулами, то есть Вы своей статьей вводите в заблуждение читателей, которые теперь считают, что при копировании области в 1С есть какие то проблемы с формулами и скрытыми ячейками. Причем копирование, а затем чтение построителем происходит даже чуть быстрее, чем чтение ADO, разница там очень минимальная и появляется на очень больших объемах данных (документ 12 колонок, 500000 строк - копирование - 20 секунд, чтение построителем - 127с, чтение ADO - 177с). Безусловно метод копирования тоже имеет изъяны. У 1С есть проблемы с отображением очень больших табличных документов (более 6.5 миллионов ячеек) и она валится с ошибкой памяти. Но куда будет загружать пользователь такой объем данных? Для сравнения это полностью заполненная табличная часть документа с 66 колонками. Сможете назвать хоть один документ в типовых или отраслевых с таким количеством колонок?
31. Яков Коган (Yashazz) 15.12.14 19:38
(30) qwinter, не найдя аналогов, позволяющих упрекнуть меня в баянизме, вы решили перейти к тональности наезда) В заблуждение я ввожу, ага))) Какой я нехороший.

Что, неужели вы думаете, все пользователи экселя освоили дзен выделения только тех строк, что удовлетворяют отбору? Большинство выделит прямоугольный фрагмент как обычно ("что вижу, то беру"), и в него попадут строки, которых юзеру было не надо. Аналогично, проблема с формулами не в том даже, что их данные как-то криво попадут, а в том, что работа с ними будет идти на стороне 1С, в отрыве от контекста. Проблемы, о которых я говорю, не программно-технические, а операционно-технологические, вероятность ошибки выше, а средств диагностики меньше - при копировании все связи с исходной книгой обрываются, контекст теряется.

Ну и повторюсь - нет смысла купить в магазине всё, надрываясь тащить домой, и дома разбираться, что надо и что лишнее.

Кстати, спасибо, что напомнили. В лохматые времена я опубликовал действительно баян, как раз на эту тему: http://infostart.ru/public/78817/ - так что плюсы и минусы способа копирования знаю не понаслышке.
32. Павел Алексеенко (qwinter) 15.12.14 21:45
qwinter, не найдя аналогов, позволяющих упрекнуть меня в баянизме, вы решили перейти к тональности наезда) В заблуждение я ввожу, ага))) Какой я нехороший.
Мой вопрос предельно точно был задан в моем первом сообщении. Но Вы вместо ответа прицепились, к слову "баян". Ваша обработки никаким образом не решает, Вами же самим озвученные проблемы.
Что, неужели вы думаете, все пользователи экселя освоили дзен выделения только тех строк, что удовлетворяют отбору? Большинство выделит прямоугольный фрагмент как обычно ("что вижу, то беру"), и в него попадут строки, которых юзеру было не надо.
Что в данном случае меняет использование Вашей обработки?
Аналогично, проблема с формулами не в том даже, что их данные как-то криво попадут, а в том, что работа с ними будет идти на стороне 1С, в отрыве от контекста.
Вы заблуждаетесь, и продолжаете вводить в заблуждение тех кто Вас читает. При копировании читаются уже рассчитанные данные ячейки, для этого используется то же самое ADO-соединение, которое используете Вы.
33. Алекс Ю (AlexO) 16.12.14 09:30
(0) Вот сразу резануло - "нестандартная загрузка в Excel".
А какая стандартная? А почему эта - нестандартная? Не средствами 1С? А если средствами 1С - то какое там может быть принципиальное отличие от "стандартных"?
Важно: вы можете использовать любой отбор, отфильтровать данные и просто выделить их. Отбор по значению, цвету, шрифту - всё к вашим услугам. Потом выделяете получившееся, мышкой или клавиатурой, и нажимаете спецкнопку - и все данные уже в 1С именно согласно отбору.

Каким образом Excel "рааскажет" 1С, какие типы данных использовать? Кто создаст нужную типизацию? Не говоря уже о контроле за корректностью передачи.
Согласен с qwinter - обработка не решает ни одну из поднятых проблем лучше, чем прямая "тупая" копи-паста в MXL.
Да плюс к этому, н асамом деле - чуть ли не открытым текстом ввод в заблуждение:
"Человек сделал хорошую разработку"
"Наверняка хорошая штука"...
34. Алекс Ю (AlexO) 16.12.14 09:40
(32) qwinter,
Ваша обработки никаким образом не решает, Вами же самим озвученные проблемы.

Согласен полностью.
для этого используется то же самое ADO-соединение

Копи-паста работает по ADO? Я что-то сомневаюсь ))
35. Елена Каткова (KKelena) 16.12.14 10:21
под УФ нужна такая обработка
36. Яков Коган (Yashazz) 16.12.14 21:06
(33) AlexO, обработка - это пример. А концепция поднятые вопросы позволяет решить проще, нежели через COM, и это очевидный факт. Типизацию можно рулить, в т.ч. из настроек. Контроль корректности на совести АДО-запроса. Почему "нестандартная" - я уже сказал, читайте внимательно; или покажите мне аналогичные разработки) Концепция позволяет решать вопросы качественно иначе, нежели копипаста.

А вообще, господа, выглядите вы как два толстых завистливых тролля. Нелицеприятно то бишь. А посему я лучше буду тратить время на доводку своей поделки под УФ, нежели с вами общаться, и просьба своим антиконструктивом тут не мусорить. Вы наверняка сейчас попробуете, чтобы последнее словцо осталось за вами, ну так вот я отвечать вам и не стану)
kostyaomsk; +1 Ответить
37. Илья Галицков (galich) 24.12.14 14:18
(9) Yashazz, УФ нужна. Спасибо. Интересная разработка.
38. Яков Коган (Yashazz) 04.01.15 15:06
Сделал УФ, наконец-то дошли руки. Обычная форма переделана идеологически под работу с конкретным документом, с привязкой к его табчастям и их реквизитам; для УФ было влом. Кроме того, обычная форма теперь может цепляться как "заполнение". Если кому сильно надо, могу сделать такое же под УФ БСП 8.3
39. Константин Юрин (kostyaomsk) 08.01.15 14:55
Попробую под что-нибудь переделать обработку загрузки из Екселя.
40. Константин Юрин (kostyaomsk) 08.01.15 18:13
(38) Yashazz, довольно интересная обработка. По ней много вопросов.
1. Это что за системный параметр "VBIDE"? Используется как ключ реестра для определения пути
рКлюч="HKEY_CURRENT_USER\Software\Microsoft\Office\"+мВерсияЭкселя+"\Excel\Security\AccessVBOM";
Может как-то в винде включать VB. Обработка то для простых пользователей нужна. Я про то, что используется объект винды, который придется изучать
шелл=Новый COMОбъект("WScript.Shell");
. Настала необходимость. Видимо, версия Windows 8 у нее свой ключ.
Дальше вначале ввело в ступор использование зарезервированных слов без кода:
ДобавитьОбработчик рКнигаЭксель.BeforeClose, ПередЗакрытиемКнигиЭкселя;	
	ДобавитьОбработчик рКнигаЭксель.BeforeSave, ПередЗаписьюКнигиЭкселя;
	ДобавитьОбработчик рЛист.Change, ПриИзмененииЛиста;
Хорошо в 1С справка есть :)
И, конечно код VBA для екселя непонятен, если не знаешь особо.
|Public Sub SetColumnRoleOf1C()
	|	On Error Resume Next
	|	'taking neigther action nor event in 1C, just tuning
	|	Set CurBtn=Application.CommandBars.ActionControl
	|	Set colCell=Application.Selection.Cells(1, 1)
	|	oldColNum=Trim(CurBtn.Parameter)
	|	newColNum=CStr(colCell.Column)
	|	If oldColNum<>"""" and oldColNum<>newColNum Then
	|		If msgbox(""Колонке <""+Trim(CurBtn.Tag)+""> уже сопоставлен столбец ""+oldColNum+"", переприсвоить?"",4,""Уже указано!"")=7 Then Exit Sub 'no
	|	End If
	|	CurBtn.Parameter=newColNum
	|	CurBtn.Caption=Trim(CurBtn.Tag)+"" (кол.""+newColNum+"")""
	|End Sub
...Показать Скрыть
Хорошо хоть догадаться можно :)
Тоже интересна технология работы с обработчикам уже внешнего объекта Екселя:
Попытка
	Если СокрЛП(мЭксель.CommandBars("LoadDataTo1C").Controls(1).Tag)<>"ToDo:Close" Тогда
		Отказ=Истина; // "спасибо" уродам из 1С, лишившим нас возможности передачи прямого контекста в обработчик завершения НЕ-модального вызова
		ОтключитьРаботуСЭксель(Команды.Найти("ОтключитьРаботуСЭксель")); // и ещё "спасибо" за невозможность написать Команды.<ИмяКоманды>
	КонецЕсли;
Исключение
	// ничего не будем делать
КонецПопытки;
...Показать Скрыть

Это уже сами события Екселя.
Так в целом понятно, что есть фильтр на недопустимые знаки, динамическое считывание. Хотел уже через ADO только сделать доступ, но увидел что для него тоже есть поддержка.
41. Константин Юрин (kostyaomsk) 08.01.15 18:14
Много компактных технологии по связи с Екселем. Вот если придется что-то подправить?
42. Константин Юрин (kostyaomsk) 08.01.15 18:24
Все-таки цели таких обработок - чтоб пользователь где угодно что угодно зажевал. Дальше что сам ексель сделал половину работы.
Как-то была самая простая. По тому же древнему "Excel.Application" все "проглотить" в 1С. Указать с каких колонок перед этим по номерам и с каких строк что куда (формат если приходится подгонять).
За вот этот фильтр отдельный плюс
рЗапретные="?,=\|:;&""@#$^!~`'[]{}№+-/*%()<>"+Символы.ВК+Символы.ВТаб+Символы.НПП+Символы.ПС+Символы.ПФ+Символы.Таб;
Сколько с ним бились все время. Каждый раз заново.
Все-таки цель повторюсь хоть как-то зажевать файл-источник. В моем случае кучу прайсов поставщиков и потом руками сопоставлять. Благо не тысячи величин.
Короче...Веселая обработка. Автору большой плюс. Прям она с этими перехватами событий (которые еще нужно запустить) и VBS который полезно знать теперь (про скрипты уже молчу).
Сначала помучиться запускать-исследовать и только потом увидеть подробное описание всех действий и технологий :)
43. Константин Юрин (kostyaomsk) 08.01.15 19:13
(19) Yashazz, тут насчет этого самого
Насчёт отсутствия ключа разрешения работы с VB, надо ещё курить мануалы
Тут точно даже библиотека BBide.dll не регистрируется. Кстати сам мануал в обработке в каком формате?
Прикрепленные файлы:
44. Яков Коган (Yashazz) 08.01.15 19:53
(40) Не очень понял, надо ли что-то отвечать))
(43) Вообще есть, говорят, интерфейсный способ всё включить, без реестра. Доступен любому юзверю и вроде даже не с админскими правами. Могу узнать подробнее. Единственно что, под восьмую винду не знаю, в силе ли это. Сам на Win2008 Server работаю или на семёрке.
Насчёт формата мануала - да вроде обычный текст или пакетник-chm. Если CHM, то сохранить куда угодно и запустить, как исполняемую прогу.
kostyaomsk; +1 Ответить 1
45. Константин Юрин (kostyaomsk) 08.01.15 21:17
(44) Yashazz, елки-палки CHM. Когда сложный материал уже забываешь что смотрел :) Отдыхать надо на праздниках, а не 1С мучить :)
Для написания сообщения необходимо авторизоваться
Прикрепить файл
Дополнительные параметры ответа