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

Автор Aspiring, 2 марта 2015, 08:46

0 Пользователи и 1 гость просматривают эту тему.

Aspiring

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

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

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

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

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

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

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

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

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

rami

Цитата: Aspiring от  2 марта 2015, 06:46Процесс загрузки данных довольно сложен и затратен по ресурсам и времени
Ну, да, подковать блоху макросом, да ещё извлечь подковки и гвоздики из базы данных... Это вам не два пальца...  ;D
Цитата: Aspiring от  2 марта 2015, 06:46Все это завязано с БД, связи, зависимости данных одного выпадающего списка от другого. и таких выпадающих списков в одной колонке 45.
Это что, в одной колонке 45 разных выпадающих списков? Да ещё зависимых один от другого? Или все одинаковые?
Цитата: Aspiring от  2 марта 2015, 06:46Для оптимизации этой работы необходимо изменить алгоритм обновления списков. Вместо того чтобы по событию изменений в одной из ячеек обновлять данные во всех остальных ячейках, нужно перед раскрытием выпадающего списка в текущей, получить данные из всех остальных ячеек, обработать, и подгрузить в данную ячейку. Это многократно ускорит работу скриптов в целом.
Я не понял всех этих наворотов, но что мешает создать скрытый лист с таблицей и в ней формулами всё обработать, а валидатор будет брать значения из диапазонов таблицы?
Цитата: Aspiring от  2 марта 2015, 06:46проблема в том что я нашел события DropDown или хотя бы setFocus у валидатора. Подскажите как можно реализовать задуманное?
Вы наверно пропустили "не" перед словом "нашел"? У валидатора нет событий. События есть у ячейки.

Aspiring

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

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

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

JohnSUN

Цитата: Aspiring от  2 марта 2015, 08:46
Я макросом подгружаю туда необходимые данные и даю пользователю возможность выбора из выпадающего списка.
В каком смысле "макросом подгружаю туда"? В смысле, используешь тип проверки "Список" и заполняешь список допустимых вариантов макросом? Или используешь "Диапазон ячеек", а в качестве источника данных указываешь макрофункцию, которая возвращает массив значений? Или?..
Цитата: Aspiring от  2 марта 2015, 08:46
и таких выпадающих списков в одной колонке 45.
То есть, "форма ввода" на листе вытянута по вертикали? А кроме выпадающих списков другие ячейки для ввода данных есть? Или всеми этими 45-ью списками вся форма и заканчивается?
Цитата: Aspiring от  2 марта 2015, 08:46
Но при таком подходе съедается огромное количество ресурсов. а обработка 10 из 45 выпадающих списков занимает пол минуты. в среднем один список 3,5 сек.
А как насчет "предварительного кэширования"? В смысле, одним махом подготовить на скрытом вспомогательном листе все возможные варианты списков и просто подсовывать нужные по ходу дела? По идее, должно получиться гораздо быстрее...
Цитата: Aspiring от  2 марта 2015, 08:46
Для оптимизации этой работы необходимо изменить алгоритм обновления списков. Вместо того чтобы по событию изменений в одной из ячеек обновлять данные во всех остальных ячейках, нужно перед раскрытием выпадающего списка в текущей, получить данные из всех остальных ячеек, обработать, и подгрузить в данную ячейку. Это многократно ускорит работу скриптов в целом.
Ну, один из подходов я только что и предложил. И, действительно, должно получиться гораздо быстрее.
Цитата: Aspiring от  2 марта 2015, 08:46
проблема в том что я нашел события DropDown или хотя бы setFocus у валидатора. Подскажите как можно реализовать задуманное?
Вот, например, очень неплохой трюк - в качестве источника данных указывается именованный диапазон. А уже сам этот диапазон (его имя) вычисляется по уже введенным данным. Подробнее рассказать пока не могу, просто не знаю, с какими значениями работают твои выпадающие списки.
Цитата: Aspiring от  2 марта 2015, 08:46
Думал заменить валидатор на элемент управления "выпадающий список"(ComboBox) с ним проще будет работать, но я так понял что я не смогу получить к нему доступ через ячейку.
Это ты не совсем правильно понял. Первое же поле в закладке "Данные" в свойствах элемента управления это и есть адрес ячейки, в которой должно появиться выбранное значение. Только мой тебе совет, используй не комбобокс, а листбокс - у него возможностей больше.
Цитата: Aspiring от  2 марта 2015, 08:46а все элементы управления будут находиться на форме а не в ячейках рабочего листа.
И это было бы самым лучшим решением! Да, это потребует кучу сил - переделать существующую форму ввода в виде ячеек на листе в отдельную диалоговую форму с полями ввода довольно утомительная работёнка. Но результат того стоит. Можно сделать настоящий "мастер заполнения", скажем, из пяти последовательных экранов с отдельными группами по 9-10 полей ввода. И у пользователя меньше шансов где-то ошибиться и что-то испортить, и выглядит такая программа более профессионально. Да и все нужные проверки собрать прямо в форме ввода будет проще...
Понадобятся уточнения - спрашивай... Только, если не трудно, какой-то образец выложи.
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне

Aspiring

#4
ЦитироватьВ каком смысле "макросом подгружаю туда"? В смысле, используешь тип проверки "Список" и заполняешь список допустимых вариантов макросом? Или используешь "Диапазон ячеек", а в качестве источника данных указываешь макрофункцию, которая возвращает массив значений? Или?..
макрос готовит строку для валидатора ""параметр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

За образцы - спасибо... Но, увы, прямо сейчас внимательно их рассматривать времени нет - понедельник, текучка...
В двух словах по твоим вопросам...
Цитата: Aspiring от  2 марта 2015, 13:06
макрос готовит строку для валидатора ""параметр1";"параметр2";"параметр3";"параметр4";"параметр5""
Я когда-то реализовал такую штуку: макрос, который готовит эту строку, превратил в функцию, возвращающую массив строк. И просто вписал её имя в Источник. То есть, в принципе, сделал именно то, о чём ты спросил с самого начала: реализовал заполнение списка значения по BeforeOpen - когда пользователь щелкал по стрелке, чтобы раскрыть список, Calc запускал на выполнение функцию, которая и готовила этот список по уже имеющимся данным.
Цитата: Aspiring от  2 марта 2015, 13:06
В идеале это нужно делать перед открытием списка и только для текущего параметра. это ускорит работу макроса в 43 раза.
Ну, на такое увеличение быстродействия я бы не сильно надеялся... Но раз в 15 - тоже хлеб: не полминуты, а две секунды...
Цитата: Aspiring от  2 марта 2015, 13:06
Так, ну а на счет этого хотелось бы поподробней.
допустим в ячейке В2 находится элемент управления привязанный к ячейке. как мне получить из него значение:

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

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

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

Aspiring

Цитата: JohnSUN от  2 марта 2015, 11:59макрос, который готовит эту строку, превратил в функцию, возвращающую массив строк
Вот теперь предыдущие слова начинают обретать понимание))). Это довольно таки интересно. попробую по колдовать в данном направлении, посмотрим что выйдет. Если получится это будет наиболее оптимальным решением.

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

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

JohnSUN

Цитата: Aspiring от  3 марта 2015, 05:24
можно ли на StarBasic создать объект с параметрами и методами?!
Смутно помнится, что лет пять-семь назад кто-то где-то на форуме показывал свои попытки реализовать нечто в этом роде... Сейчас уже не вспомню кто и где, но почему-то запомнилось, что в результате обсуждения форумчане пришли к выводу, что можно, конечно, но не стоит - возни много, а результат не оправдывает усилий. Хочешь работать с ООП - выбирай любой язык, который изначально заточен под работу с пользовательскими классами. Например, ту же Java - пишешь нормальный класс, а специальная приблуда к NetBeans превращает готовый jar в полноценный Add-On или, при желании, в Add-In. Из бэйсика удобно использовать уже существующие объекты, а описывать собственные муторно - согласись, редактор очень сильно не дотягивает до нормальной IDE
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне

Aspiring

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

Aspiring

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

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

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

spada

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

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