выпадающий список валидации содержимого ячейки

[Aspiring]

Добрый день форумчане!

Благодарю за проявленный интерес к этой теме!

В Calc есть одна замечательная штука как проверка(Данные->Проверка...) содержимого ячейки. Очень удобно.
Я макросом подгружаю туда необходимые данные и даю пользователю возможность выбора из выпадающего списка.

Процесс загрузки данных довольно сложен и затратен по ресурсам и времени
Все это завязано с БД, связи, зависимости данных одного выпадающего списка от другого. и таких выпадающих списков в одной колонке 45.
По событию изменений на листе обрабатываю эти изменения. Все как бы работает.
Но при таком подходе съедается огромное количество ресурсов. а обработка 10 из 45 выпадающих списков занимает пол минуты. в среднем один список 3,5 сек.

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

проблема в том что я нашел события DropDown или хотя бы setFocus у валидатора. Подскажите как можно реализовать задуманное?

Думал заменить валидатор на элемент управления "выпадающий список"(ComboBox) с ним проще будет работать, но я так понял что я не смогу получить к нему доступ через ячейку. а все элементы управления будут находиться на форме а не в ячейках рабочего листа.

Может кто то сталкивался с подобным, подскажите кто нибудь что нибудь!!!!

Еще раз спасибо!

[rami]

Процесс загрузки данных довольно сложен и затратен по ресурсам и времени

Ну, да, подковать блоху макросом, да ещё извлечь подковки и гвоздики из базы данных... Это вам не два пальца...  

Все это завязано с БД, связи, зависимости данных одного выпадающего списка от другого. и таких выпадающих списков в одной колонке 45.

Это что, в одной колонке 45 разных выпадающих списков? Да ещё зависимых один от другого? Или все одинаковые?

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

Я не понял всех этих наворотов, но что мешает создать скрытый лист с таблицей и в ней формулами всё обработать, а валидатор будет брать значения из диапазонов таблицы?

проблема в том что я нашел события DropDown или хотя бы setFocus у валидатора. Подскажите как можно реализовать задуманное?

Вы наверно пропустили "не" перед словом "нашел"? У валидатора нет событий. События есть у ячейки.

[Aspiring]

Не знаю как цитировать, поэтому отвечу списком по порядку.

1. Да действительно не два пальца.
2. Да в одной колонке "типовая ячейка"(понятие из энергетики) содержит около 45 параметров(ячеек) каждый из которых выбирается в зависимости от других. одни параметры зависят от других. чтобы стало чуть понятней: имеется таблица связи всех параметров, 45 таблиц самих параметров которые которые содержат все варианты для текущего параметра. таблица связи содержит порядка 8 тыс записей. и ссылается на 2 таблицы параметров и на идентификаторы самих параметров в этих таблицах. Знаю что связь организована идиотски. Но по другому никак. Заказчику необходима гибкость.
3. Не что не мешает. Не в этом суть. хранить и обрабатывать данные я могу и в массиве. зачем мне эти лишние чтения/записи. для меня важно чтобы список подгружался в момент перед открытием этого самого списка.
Сейчас просто реализовано так:
после выбора параметра, в систему попадает новое условие(если до этого не учитывался этот параметр, то теперь надо его учесть) из таблицы связей достаются все связи с учетом этого параметра. создаются временные таблицы и в них записываются результаты. Затем все временные таблицы объединяются по условию INNER JOIN и в результате получается список доступных вариантов выбора для параметра. этот алгоритм выполняется 45 раз. то есть для каждой ячейки (для каждого параметра). Почему временные таблицы?! - чтобы не было "out of memory" а то уже было))
4. да действительно я пропустил "не". вы правы.
ну может я и не прав. Но, xRay показывает список слушателей на validator см.вложение

пробовал я по событию щелчка мышкой в ячейку... но это просто жуть какая то. как только пощупал сразу отказался от этой идеи. Так же я не нашел события выделения ячейки. Это бы возможно тоже могло мне помочь.

[JohnSUN]

Я макросом подгружаю туда необходимые данные и даю пользователю возможность выбора из выпадающего списка.

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

и таких выпадающих списков в одной колонке 45.

То есть, "форма ввода" на листе вытянута по вертикали? А кроме выпадающих списков другие ячейки для ввода данных есть? Или всеми этими 45-ью списками вся форма и заканчивается?

Но при таком подходе съедается огромное количество ресурсов. а обработка 10 из 45 выпадающих списков занимает пол минуты. в среднем один список 3,5 сек.

А как насчет "предварительного кэширования"? В смысле, одним махом подготовить на скрытом вспомогательном листе все возможные варианты списков и просто подсовывать нужные по ходу дела? По идее, должно получиться гораздо быстрее...

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

Ну, один из подходов я только что и предложил. И, действительно, должно получиться гораздо быстрее.

проблема в том что я нашел события DropDown или хотя бы setFocus у валидатора. Подскажите как можно реализовать задуманное?

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

Думал заменить валидатор на элемент управления "выпадающий список"(ComboBox) с ним проще будет работать, но я так понял что я не смогу получить к нему доступ через ячейку.

Это ты не совсем правильно понял. Первое же поле в закладке "Данные" в свойствах элемента управления это и есть адрес ячейки, в которой должно появиться выбранное значение. Только мой тебе совет, используй не комбобокс, а листбокс - у него возможностей больше.

а все элементы управления будут находиться на форме а не в ячейках рабочего листа.

И это было бы самым лучшим решением! Да, это потребует кучу сил - переделать существующую форму ввода в виде ячеек на листе в отдельную диалоговую форму с полями ввода довольно утомительная работёнка. Но результат того стоит. Можно сделать настоящий "мастер заполнения", скажем, из пяти последовательных экранов с отдельными группами по 9-10 полей ввода. И у пользователя меньше шансов где-то ошибиться и что-то испортить, и выглядит такая программа более профессионально. Да и все нужные проверки собрать прямо в форме ввода будет проще...
Понадобятся уточнения - спрашивай... Только, если не трудно, какой-то образец выложи.

[Aspiring]

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

макрос готовит строку для валидатора ""параметр1";"параметр2";"параметр3";"параметр4";"параметр5""

Код Выделить Развернуть

oValidation=oCell.Validation
oValidation.Type=com.sun.star.sheet.ValidationType.LIST
oValidation.setOperator(com.sun.star.sheet.ConditionOperator.EQUAL)
oValidation.setFormula1(sParametrValueList) ' <--- список параметров
oCell.Validation=oValidation

То есть, "форма ввода" на листе вытянута по вертикали? А кроме выпадающих списков другие ячейки для ввода данных есть? Или всеми этими 45-ью списками вся форма и заканчивается?

да форма вытянута по вертикали. дело в том что в итоге будет неопределенное кол-во таких столбцов. "типовая ячейка 1" 2,3,4 и т.д.
по сути весь лист это сплошная форма ввода. а столбец из 45 (если быть точным то из 43) ячеек это "Типовая ячейка" таких типовых ячеек может быть много. Одна типовая ячейка параметрируется только этими выпадающими списками.

А как насчет "предварительного кэширования"? В смысле, одним махом подготовить на скрытом вспомогательном листе все возможные варианты списков и просто подсовывать нужные по ходу дела? По идее, должно получиться гораздо быстрее...

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

Это ты не совсем правильно понял. Первое же поле в закладке "Данные" в свойствах элемента управления это и есть адрес ячейки, в которой должно появиться выбранное значение. Только мой тебе совет, используй не комбобокс, а листбокс - у него возможностей больше.

Так, ну а на счет этого хотелось бы поподробней.
допустим в ячейке В2 находится элемент управления привязанный к ячейке. как мне получить из него значение:

Код Выделить Развернуть


oCell = ThisComponent.CurrentController.ActiveSheet.getCellByPosition(1, 1)
что нужно сделать дальше?


И это было бы самым лучшим решением! Да, это потребует кучу сил - переделать существующую форму ввода в виде ячеек на листе в отдельную диалоговую форму с полями ввода довольно утомительная работёнка. Но результат того стоит. Можно сделать настоящий "мастер заполнения", скажем, из пяти последовательных экранов с отдельными группами по 9-10 полей ввода. И у пользователя меньше шансов где-то ошибиться и что-то испортить, и выглядит такая программа более профессионально. Да и все нужные проверки собрать прямо в форме ввода будет проще...
Понадобятся уточнения - спрашивай... Только, если не трудно, какой-то образец выложи.

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

базу надо зарегистрировать. запускать нужно ods
и полностью все прогнать не выйдет, там еще не все параметры заполнены и не все связи описаны. на деле работают первые 10 помоему параметров. затем из-за отсутствия связей выпадающие списки будут пустые

[JohnSUN]

За образцы - спасибо... Но, увы, прямо сейчас внимательно их рассматривать времени нет - понедельник, текучка...
В двух словах по твоим вопросам...

макрос готовит строку для валидатора ""параметр1";"параметр2";"параметр3";"параметр4";"параметр5""

Я когда-то реализовал такую штуку: макрос, который готовит эту строку, превратил в функцию, возвращающую массив строк. И просто вписал её имя в Источник. То есть, в принципе, сделал именно то, о чём ты спросил с самого начала: реализовал заполнение списка значения по BeforeOpen - когда пользователь щелкал по стрелке, чтобы раскрыть список, Calc запускал на выполнение функцию, которая и готовила этот список по уже имеющимся данным.

В идеале это нужно делать перед открытием списка и только для текущего параметра. это ускорит работу макроса в 43 раза.

Ну, на такое увеличение быстродействия я бы не сильно надеялся... Но раз в 15 - тоже хлеб: не полминуты, а две секунды...

Так, ну а на счет этого хотелось бы поподробней.
допустим в ячейке В2 находится элемент управления привязанный к ячейке. как мне получить из него значение:

Код Выделить Развернуть


oCell = ThisComponent.CurrentController.ActiveSheet.getCellByPosition(1, 1)
что нужно сделать дальше?


Всё верно, нужно взять значение из B2. А вот как его туда затолкать - см. на прилагаемой картинке

Об остальном - чуть позже, действительно нужно по офису кой-чего порешать...

[Aspiring]

макрос, который готовит эту строку, превратил в функцию, возвращающую массив строк

Вот теперь предыдущие слова начинают обретать понимание))). Это довольно таки интересно. попробую по колдовать в данном направлении, посмотрим что выйдет. Если получится это будет наиболее оптимальным решением.

JohnSUN - ты в моих исходниках найдешь попытки использования типа определенного пользователем. Это были отчаянные попытки привнести ооп в бейсик. Меня очень интересует данный вопрос, можно ли на StarBasic создать объект с параметрами и методами?! Вопрос не по теме, но может подкинешь ссылочку для чтения. Где то находил информацию что язык объектно ориентирован, но как то продвинуться в этом направлении я так и не смог. Спасибо!

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

[JohnSUN]

можно ли на StarBasic создать объект с параметрами и методами?!

Смутно помнится, что лет пять-семь назад кто-то где-то на форуме показывал свои попытки реализовать нечто в этом роде... Сейчас уже не вспомню кто и где, но почему-то запомнилось, что в результате обсуждения форумчане пришли к выводу, что можно, конечно, но не стоит - возни много, а результат не оправдывает усилий. Хочешь работать с ООП - выбирай любой язык, который изначально заточен под работу с пользовательскими классами. Например, ту же Java - пишешь нормальный класс, а специальная приблуда к NetBeans превращает готовый jar в полноценный Add-On или, при желании, в Add-In. Из бэйсика удобно использовать уже существующие объекты, а описывать собственные муторно - согласись, редактор очень сильно не дотягивает до нормальной IDE

[Aspiring]

Ну да, ну да.. Исчерпывающе. Вот и я попытался да бросил эту затею. То что осталось просто лень переписывать было.

[Aspiring]

Ну в общем все получилось!
Выделяем ячейку и заходим меню "Данные"->"Проверка..."->вкладка "Условия"
в поле "Разрешить:" указываем "Диапазон ячеек", в поле "Источник" вместо диапазона ячеек пишем "=function_name()" .
Функция должна быть описана в любом из модулей и возвращать массив строк.

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

Большое спасибо за помощь JohnSUN! Ты меня уже не первый раз выручаешь советом!

[spada]

вот нашел статью про функции, но  мне она не помогла. http://www.firststeps.ru/vba/vbahelp/r.php?23

Можете написать как функция выглядит.