Нестандартная загрузка из 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. А всё просто потому, что мне поставили задачу «загрузить из экселя», брать готовую (свою или чужую) выходило громоздко, а писать в сотый раз одни и те же баяны уже не было сил. Вот и захотелось извратиться)))

45 Comments

Yashazz 12.11.2014 at 17:41

Да, ещё что забыл: работа под Excel 2003 и древнее предполагается, но не гарантируется (не тестировал).

Reply ↓
stanru1 12.11.2014 at 18:06

Идея понравилась, спасибо!

Reply ↓
Pasha1st 12.11.2014 at 23:10

Очень интересное решение! + за оригинальный подход.

Reply ↓
TrinitronOTV 13.11.2014 at 05:54

Это всё хорощо конечно, но вот у меня в некоторых случаях есть необходимость при загрузке данных определять ещё цвет шрифта для решения вопроса загружать эти данные или нет, так что практически все методы отпадают…

Reply ↓
Yashazz 13.11.2014 at 08:41

(4) TrinitronOTV, идею это не отменяет — можно ДО обмена в экселе поймать строки с нужным шрифтом, скриптом выделить их в selection и уж его грузануть. В том и фишка, что это гораздо проще выяснить средствами самого экселя, а не при обмене. Можно в скрипте прописать вызов события, на которое подписана 1С, тогда вообще не кнопку жамкать, а любые нужные области сразу перекидывать.

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

Reply ↓
TrinitronOTV 13.11.2014 at 14:03

(5) согласен с вами

Reply ↓
Platon777 14.11.2014 at 11:06

Отличная штука! Сделаю в ней ещё себе ещё поле с выполнением произвольного кода 1С в «пользвательском» — вообще тогда ничего для Excel не нужно …

Reply ↓
the1 15.11.2014 at 00:08

Ну что могу сказать… Грандиозно!

Reply ↓
Yashazz 18.11.2014 at 02:14

Управляемая форма этой поделки кому-нибудь нужна? А то скоро сделаю, могу выложить.

Reply ↓
buganov 18.11.2014 at 05:26

(9) выкладывайте, наверняка понадобится

Reply ↓
UncleVader 19.11.2014 at 09:37

(9) обязательно!

Reply ↓
Famza 19.11.2014 at 11:04

Плюсую однозначно

Reply ↓
Famza 19.11.2014 at 11:17

Ошибка при определении доступности обмена; возможно, у Вас нет права даже на чтение системного реестра!

ОпределитьДоступностьОбмена: {Форма.ОсновнаяФорма.Форма(194)}: Ошибка при вызове метода контекста (RegRead): Произошла исключительная ситуация (WshShell.RegRead): Не удается открыть для чтения раздел реестра «HKEY_CURRENT_USERSoftwareMicrosoftOffice12.0ExcelSecurityAccessVBOM».

Обратитесь к Вашему системному администратору!

Ось ХР СП3, 1С 8.3.5.1248, права админа. Ось не поддерживается?

Reply ↓
Goruch 19.11.2014 at 11:30

Даа, круто. Однозначно плюс.

Reply ↓
PrinzOfMunchen 19.11.2014 at 12:16

Да, тоже когда-то так делал, но чисто для себя. ))

Reply ↓
Yashazz 19.11.2014 at 14:43

(13) Famza, проверьте, а правильно ли определилась версия. У меня вызывает подозрение HKEY_CURRENT_USERSoftwareMicrosoftOffice12.0ExcelSecurity в пути ключа. Может, у Вас 8.0 или что-то подобное?

Я это определяю динамически, но вдруг определялка лажает или там не прямая связь с версией офиса?

Reply ↓
eugeniezheludkov 26.11.2014 at 09:25

Ошибка при определении доступности обмена; возможно, у Вас нет права даже на чтение системного реестра!

ОпределитьДоступностьОбмена: {Форма.ОсновнаяФорма.Форма(194)}: Ошибка при вызове метода контекста (RegRead): Произошла исключительная ситуация (WshShell.RegRead): Не удается открыть для чтения раздел реестра «HKEY_CURRENT_USERSoftwareMicrosoftOffice14.0ExcelSecurityAccessVBOM».

Обратитесь к Вашему системному администратору!

у меня в реестре ветка не так выглядит: есть HKEY_CURRENT_USERSoftwareMicrosoftOffice14.0ExcelSecurityTrusted Documents, но нет HKEY_CURRENT_USERSoftwareMicrosoftOffice14.0ExcelSecurityAccessVBO.

Полный офис, лицензионный. Бил Гейтс лично приезжал ставил. Так понимаю без этого ключа, данная обработка, не будет работать у всех 9000 или 10000 пользователей, при таком количестве лучше юзать типовые ?

ПС во всех компаниях где работал в домене, права у пользователя на компьютере совсем не админские, что так же накладывает ограничение на «установка низкого уровня безопасности (применится после перезапуска Excel!)» в коде 🙁

Reply ↓
ValeriTim 27.11.2014 at 10:40

Знаете что меня раздражает в подобных постах?

Человек сделал хорошую разработку и пытается ее продемонстрировать другим, однако сделать нормальные скриншоты религия, видимо, не позволяет … Ну вот зачем делать изображение каких то данных и потом замазывать из них 90%? Неужели нельзя взять файлик, налупить туда абсолютно сторонней информации и демонстрировать?

Reply ↓
Yashazz 27.11.2014 at 14:57

(17) eugeniezheludkov, то, что «установка низкого уровня безопасности» — поосторожничал я, обычно и без этого работает. Насчёт отсутствия ключа разрешения работы с VB, надо ещё курить мануалы, навскидку не отвечу. Но это довольно частый вопрос и распространённая проблема, мелкомягкие до сих пор боятся «страшных макровирусов» и запрещают всё на свете.

(18) Упрёк про замазывание в общем-то справедлив, но вот поверьте — не могу «взять и налупить»; либо конфиденциалка, либо времени совсем йок, либо недостоверно выходит. Не умею я высасывать из пальца красивые демо-данные.

Какие ещё претензии к скриншотам, кроме замазывания?

Reply ↓
Yashazz 27.11.2014 at 14:58

Таки сделал настройку чтения по колонкам.

(10), (11) — УФ будет не раньше середины декабря, я совсем закопался в текучке, прошу извинить.

Reply ↓
CratosX 27.11.2014 at 20:03

(20) подпишусь и скачаю при апдейте, круто выглядит

Reply ↓
WhiteOwl 28.11.2014 at 10:33

Интересная идея!

Reply ↓
fomix 02.12.2014 at 11:43

(21) Наверняка хорошая штука. Просьба к автору — сделай обработку 2in1 для обычных форм и под УФ, чтобы не плодить клоны. Заранее спасибо!

Reply ↓
Yashazz 02.12.2014 at 13:38

(23) fomix, только после 10 декабря. Собсно, так и собирался, 2 в 1.

Reply ↓
SemenovaMarinaV 09.12.2014 at 15:46

++ за оригинальность

Reply ↓
SemenovaMarinaV 09.12.2014 at 15:47

Жду под управляемые формы

Reply ↓
qwinter 10.12.2014 at 21:06

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

И как все эти поднятые проблемы решает данная обработка? Баян.

Reply ↓
Yashazz 11.12.2014 at 20:07

(28) Очень просто: половина головной боли решается средствами экселя на стороне экселя БЕЗ какого-либо com-соединения, ДО того, как начинать игры с обменом. А размер области, учитывая, что у меня ADO-чтение, не сильно играет роль.

Но я соглашусь с Вами в части баянности, если вы в обоснование своей оценки подкинете ссылку на именно такой подход, чтобы с оператором «ДобавитьОбработчик» и автовстраиванием функционала подготовки чтения в сам эксель. Жду с нетерпением)))

Reply ↓
qwinter 12.12.2014 at 10:08

(29) а какой смысл в встраивании, если все это уже много лет решалось копированием в область табличного документа в 1С? Причем никаких тормазов с копированием нет. Все копируется очень быстро, и все хорошо, как со скрытыми строками, так и с формулами, то есть Вы своей статьей вводите в заблуждение читателей, которые теперь считают, что при копировании области в 1С есть какие то проблемы с формулами и скрытыми ячейками. Причем копирование, а затем чтение построителем происходит даже чуть быстрее, чем чтение ADO, разница там очень минимальная и появляется на очень больших объемах данных (документ 12 колонок, 500000 строк — копирование — 20 секунд, чтение построителем — 127с, чтение ADO — 177с). Безусловно метод копирования тоже имеет изъяны. У 1С есть проблемы с отображением очень больших табличных документов (более 6.5 миллионов ячеек) и она валится с ошибкой памяти. Но куда будет загружать пользователь такой объем данных? Для сравнения это полностью заполненная табличная часть документа с 66 колонками. Сможете назвать хоть один документ в типовых или отраслевых с таким количеством колонок?

Reply ↓
Yashazz 15.12.2014 at 19:38

(30) qwinter, не найдя аналогов, позволяющих упрекнуть меня в баянизме, вы решили перейти к тональности наезда) В заблуждение я ввожу, ага))) Какой я нехороший.

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

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

Кстати, спасибо, что напомнили. В лохматые времена я опубликовал действительно баян, как раз на эту тему: http://infostart.ru/public/78817/ — так что плюсы и минусы способа копирования знаю не понаслышке.

Reply ↓
qwinter 15.12.2014 at 21:45

qwinter, не найдя аналогов, позволяющих упрекнуть меня в баянизме, вы решили перейти к тональности наезда) В заблуждение я ввожу, ага))) Какой я нехороший.

Мой вопрос предельно точно был задан в моем первом сообщении. Но Вы вместо ответа прицепились, к слову «баян». Ваша обработки никаким образом не решает, Вами же самим озвученные проблемы.

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

Что в данном случае меняет использование Вашей обработки?

Аналогично, проблема с формулами не в том даже, что их данные как-то криво попадут, а в том, что работа с ними будет идти на стороне 1С, в отрыве от контекста.

Вы заблуждаетесь, и продолжаете вводить в заблуждение тех кто Вас читает. При копировании читаются уже рассчитанные данные ячейки, для этого используется то же самое ADO-соединение, которое используете Вы.

Reply ↓
AlexO 16.12.2014 at 09:30

(0) Вот сразу резануло — «нестандартная загрузка в Excel».

А какая стандартная? А почему эта — нестандартная? Не средствами 1С? А если средствами 1С — то какое там может быть принципиальное отличие от «стандартных»?

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

Каким образом Excel «рааскажет» 1С, какие типы данных использовать? Кто создаст нужную типизацию? Не говоря уже о контроле за корректностью передачи.

Согласен с qwinter — обработка не решает ни одну из поднятых проблем лучше, чем прямая «тупая» копи-паста в MXL.

Да плюс к этому, н асамом деле — чуть ли не открытым текстом ввод в заблуждение:

«Человек сделал хорошую разработку»

«Наверняка хорошая штука»…

Reply ↓
AlexO 16.12.2014 at 09:40

(32) qwinter,

Ваша обработки никаким образом не решает, Вами же самим озвученные проблемы.

Согласен полностью.

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

Копи-паста работает по ADO? Я что-то сомневаюсь ))

Reply ↓
KKelena 16.12.2014 at 10:21

под УФ нужна такая обработка

Reply ↓
Yashazz 16.12.2014 at 21:06

(33) AlexO, обработка — это пример. А концепция поднятые вопросы позволяет решить проще, нежели через COM, и это очевидный факт. Типизацию можно рулить, в т.ч. из настроек. Контроль корректности на совести АДО-запроса. Почему «нестандартная» — я уже сказал, читайте внимательно; или покажите мне аналогичные разработки) Концепция позволяет решать вопросы качественно иначе, нежели копипаста.

А вообще, господа, выглядите вы как два толстых завистливых тролля. Нелицеприятно то бишь. А посему я лучше буду тратить время на доводку своей поделки под УФ, нежели с вами общаться, и просьба своим антиконструктивом тут не мусорить. Вы наверняка сейчас попробуете, чтобы последнее словцо осталось за вами, ну так вот я отвечать вам и не стану)

Reply ↓
galich 24.12.2014 at 14:18

(9) УФ нужна. Спасибо. Интересная разработка.

Reply ↓
Yashazz 04.01.2015 at 15:06

Сделал УФ, наконец-то дошли руки. Обычная форма переделана идеологически под работу с конкретным документом, с привязкой к его табчастям и их реквизитам; для УФ было влом. Кроме того, обычная форма теперь может цепляться как «заполнение». Если кому сильно надо, могу сделать такое же под УФ БСП 8.3

Reply ↓
jobkostya1c8 08.01.2015 at 14:55

Попробую под что-нибудь переделать обработку загрузки из Екселя.

Reply ↓

jobkostya1c8 08.01.2015 at 18:13

(38) довольно интересная обработка. По ней много вопросов.

1. Это что за системный параметр «VBIDE»? Используется как ключ реестра для определения пути

рКлюч=»HKEY_CURRENT_USERSoftwareMicrosoftOffice»+мВерсияЭкселя+»ExcelSecurityAccessVBOM»;

Может как-то в винде включать 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 только сделать доступ, но увидел что для него тоже есть поддержка.

Reply ↓

jobkostya1c8 08.01.2015 at 18:14

Много компактных технологии по связи с Екселем. Вот если придется что-то подправить?

Reply ↓
jobkostya1c8 08.01.2015 at 18:24
Все-таки цели таких обработок — чтоб пользователь где угодно что угодно зажевал. Дальше что сам ексель сделал половину работы.

Как-то была самая простая. По тому же древнему «Excel.Application» все «проглотить» в 1С. Указать с каких колонок перед этим по номерам и с каких строк что куда (формат если приходится подгонять).

За вот этот фильтр отдельный плюс
```
рЗапретные=»?,=|:;&»»@#$#k8SjZc9Dxk!~`'[]{}№+-/*%()<>»+Символы.ВК+Символы.ВТаб+Символы.НПП+Символы.ПС+Символы.ПФ+Символы.Таб;
```
Сколько с ним бились все время. Каждый раз заново.

Все-таки цель повторюсь хоть как-то зажевать файл-источник. В моем случае кучу прайсов поставщиков и потом руками сопоставлять. Благо не тысячи величин.

Короче…Веселая обработка. Автору большой плюс. Прям она с этими перехватами событий (которые еще нужно запустить) и VBS который полезно знать теперь (про скрипты уже молчу).

Сначала помучиться запускать-исследовать и только потом увидеть подробное описание всех действий и технологий 🙂

Reply ↓
jobkostya1c8 08.01.2015 at 19:13

(19) тут насчет этого самого

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

Тут точно даже библиотека BBide.dll не регистрируется. Кстати сам мануал в обработке в каком формате?

Reply ↓
Yashazz 08.01.2015 at 19:53

(40) Не очень понял, надо ли что-то отвечать))

(43) Вообще есть, говорят, интерфейсный способ всё включить, без реестра. Доступен любому юзверю и вроде даже не с админскими правами. Могу узнать подробнее. Единственно что, под восьмую винду не знаю, в силе ли это. Сам на Win2008 Server работаю или на семёрке.

Насчёт формата мануала — да вроде обычный текст или пакетник-chm. Если CHM, то сохранить куда угодно и запустить, как исполняемую прогу.

Reply ↓
jobkostya1c8 08.01.2015 at 21:17

(44) елки-палки CHM. Когда сложный материал уже забываешь что смотрел 🙂 Отдыхать надо на праздниках, а не 1С мучить 🙂

Reply ↓
German_Tagil 14.08.2019 at 06:55

подумаю — надо потестировать

Reply ↓

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

Related Posts

45 Comments

Leave a Comment Отменить ответ