Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Мост для использования в Excel возможностей регулярных выражений .Net
 
#1
04.02.2015 16:23:44
Наверно не все, но некоторые знают, что в VBA регулярные выражения поддерживаются за счет библиотеки Microsoft VBScript Regular Expressions 5.5 коротая, в свою очередь по сравнению с регулярными выражениями .Net (System.Text.RegularExpressions) имеет не такой большой набор возможностей.
Учитывая что по Microsoft VBScript Regular Expressions 5.5 сложно найти пособия и информацию Я решил разнообразить Excel новыми функциями работающими с регулярными выражениями .Net.

Функции включены в надстройку C# Regex.xll (загружается как обычная надстройка xls)
Всю информацию по написанию шаблонов (патернов) поиска можно найти в  библиотеке msdn
Надстройка содержит следующие UDF:
RegexMatch(Текст, Шаблон поиска) - выполняет поиск в тексте и возвращает первое найденное соответствие шаблону поиска
RegexMatches(Текст, Шаблон поиска, Разделитель) - выполняет поиск в тексте и возвращает все найденные соответствия шаблону поиска, разделенные Разделителем
RegexReplace(Текст, Шаблон поиска, Шаблон замены) - выполняет поиск и замену частей текста соответствующих шаблону поиска на шаблон замены (или просто на текст)
RegexReplacePlus(Текст, Шаблон поиска, Шаблон замены) - аналогичен RegexReplace, но в качестве Шаблонов Поиска и Замены выступают диапазоны ячеек, имеющих одинаковую размерность, при этом функция выполняет последовательную обработку текста по принципу: из диапазонов читаются (парно) ячейки с шаблонами Поиска и Замены соответствующие друг другу по позиции в массиве, выполняется обработка по типу RegexReplace полученное значение переходит к следующей итерации и все повторяется пока не закончатся значения из диапазонов.

Если кому интересно могу выложить более подробную информацию и примеры.

П.С.
1. в функциях не ограничены диапазоны по-этому вписывать в значения функций диапазоны D  :D  , E:E  и т.п. не стоит т.к. функция все равно пройдет все ячейки, а их свыше 1 млн. для одной итерации.
2. В шаблонах для замены на пустоту необходимо использовать знак апострофа '

Ссылка на надстройку http://1drv.ms/1zJgCPu
Изменено: amarf - 02.11.2016 16:01:16
Необходимо не только знать, но и уметь использовать это знание, ведь самые гениальные решения наиболее просты
 
 
 
#2
04.02.2015 16:27:18
Не получается загрузить файл в сообщения (454 КБ разделенных в архиве по 90 КБ)
Необходимо не только знать, но и уметь использовать это знание, ведь самые гениальные решения наиболее просты
 
 
 
#3
04.02.2015 16:37:53
Чтоб можно было вписывать столбцы целиком - обрабатывайте

Код
intersect(range.parent.usedrange,range)
 
 
 
#4
04.02.2015 16:43:53
Hugo При создании надстройки используется C# а также специальный мост который напичкан API функциями и COM объектом Excel, а они мало общего имеют с VBA и интерфейсами .Net, поскольку я только начал осваивать надстройки xll, я особо не волнуюсь за диапазоны.

Должен Вас поблагодарить, благодаря Вам я нашел направление движения по данному вопросу, выглядит это страшновато +)

Код
(ExcelDnaUtil.Application as dynamic).Intersect(FirstRange, AvailableRange).Rows.Count;
Изменено: amarf - 04.02.2015 16:56:44
Необходимо не только знать, но и уметь использовать это знание, ведь самые гениальные решения наиболее просты
 
 
 
#5
04.02.2015 16:54:25
интересно.
установленный .NET Framework нужен? какой версии?
в каких версиях Excel может работать? есть ограничения?
примеры "плюшек" по сравнению со "стандартным" RegExp есть?
и что там со скоростью работы?
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#6
04.02.2015 17:08:25
Работает с версии 2002 и выше (Но надо проверить), Net Framework по идее не ниже 4 версии т.к. при компиляции библиотеки не интегрируются.
Скорость работы выше чем у RegExp где то в 1,5-2 раза (это чисто субъективная оценка - проверял одну из моих работ с .Net и RegExp и большим количеством условий)  и плюс используются параллельные вычисления Excel.

По сравнению с RegExp в .Net есть очень удобные просмотры вперед и назад, именованные группы, больше привязок. При этом можно реализовать более сложные вычисления в других функциях - тут я представил стандартные с немножко расширенным функционалом под Excel.

Вот файл http://1drv.ms/1zJgCPu с примером работы - там выполнялся поиск адресов из одной таблицы с 6500 адресов в другой в которой >20000 адресов - проблема была в том, что синтаксис написания адресов был похож, но точно не соответствовал, что не давало использовать ПОИСПОЗ
Необходимо не только знать, но и уметь использовать это знание, ведь самые гениальные решения наиболее просты
 
 
 
#7
04.02.2015 17:10:48
А вот с

Код
Intersect(FirstRange, AvailableRange).Rows.Count
осторожно, если
Код
AvailableRange
может быть не с первой строки. Хотя конечно смотря где это число использовать...
 
 
 
#8
04.02.2015 17:12:44
А не проще написать для Excel надстройку ExcelAddIn и не мучиться с мостами и прочим.
 
 
 
#9
04.02.2015 17:13:01
Hugo Это как раз то что необходимо, т.к. в диапазонах (массивах) можно отслеживать с какой строки начинать вычисления и до какой выполнять итерации +)
Цитата
Doober пишет: А не проще написать для Excel надстройку ExcelAddIn и не мучиться с мостами и прочим.
ExcelAddIn средствами VBA писать не хочется (я слишком разбалован C# и .Net) а Com надстройки имеют ограничения по использованию в разных версиях + производительность ниже чем у XLL
Изменено: amarf - 04.02.2015 21:21:49
Необходимо не только знать, но и уметь использовать это знание, ведь самые гениальные решения наиболее просты
 
 
 
#10
23.04.2016 01:03:57
А можете обновить ссылки? Нет сейчас к ним доступа.
 
 
 
#11
23.04.2016 09:34:55
Цитата
ikki написал:
примеры "плюшек" по сравнению со "стандартным" RegExp есть?
Отсутствуют:
1) [a-z-[aeiou]] - Character class subtraction
2) \G - finds only consecutive matches
3) (?<=expr) - Zero-width positive look-behind assertion
4) (?<!expr>) - Zero-width negative look-behind assertion
5) (?>expr) - Nonbacktracking subexpression
6) \k<name> - Named back reference
7) (?(expr)yes|no) и (?(name)yes|no) - Matches the "yes" part if the expression matches at this point; otherwise, matches the "no" part и Matches the "yes" part if the named capture string has a match; otherwise, matches the "no" part.
8) Регулирование настроек поиска в паттерне: (?imnsx-imnsx)
9) Regex.Split() - разбивка строки по регулярке

Regex.Replace позволяет указать свою функцию для замены. Пример:
Код
Sub TestReplaceWithCallback()
   Dim re As New Regex("\d+\s*\+\s*\d+")
   Dim source As String = "a = 100 + 234: b = 200+345"
   ' Заменяем сумму их результатом
   Console.WriteLine(re.Replace(source, AddressOf DoSum))
   'Получаем: a = 334: b = 545
End Sub

' Сallback function
Private Function DoSum(ByVal m As Match) As String
   Dim args() As String = m.Value.Split("+"c)
   Dim n1 As Long = CLng(args(0))
   Dim n2 As Long = CLng(args(1))
   Return (n1 + n2).ToString()
End Function
Изменено: SuperCat - 23.04.2016 12:54:55
There is no knowledge that is not power
 
 
 
#12
02.11.2016 15:56:54
Добрый день! Присоединяюсь к последней просьбе - можете, пожалуйста, обновить ссылки на файл?
 
 
 
#13
03.11.2016 20:08:31
Оу ! не был тут столько времени, а тут оказывается оживили тему ! Чуть позже залью надстройку, как только откопаю ее у себя на компьютере
Необходимо не только знать, но и уметь использовать это знание, ведь самые гениальные решения наиболее просты
 
 
 
#14
03.11.2016 20:37:52
Выложил версию 3 для работы с регулярными выражениями (C# Regex v3.xll), а также надстройку (XlsFunctionLibrary v1.xll) добавляющую некоторые очень полезные формулы, например получить часть строки из ячейкт соответствующую шаблону, посчитать кол-во символов в ячейке, найти файл в директории, узнать содержит ли ячейка шаблон текста, заполнить ячейку из текстового файла.
Все формулы можно посмотреть, вызвав диалог для вставки формулы и выбрав соответствующие разделы (А.M4rf C# Regex Functions и AMarf Xls FunctionLibrary) для всех формул включена документация, если кому то нудны будут более подробные разъяснения то пишите.

Ссылка на скачивание:  OneDrive
Распространение запрещено. Это означает, что скачивать и пользоваться можно, но размещать на других ресурсах для скачивания нельзя.
Необходимо не только знать, но и уметь использовать это знание, ведь самые гениальные решения наиболее просты
 
 
 
#15
07.11.2016 17:29:27
Спасибо! Полезная вещь, пробую использовать.
Сначала была ошибка при подключении XLL, потом вспомнил, что проверяю на 64-битной версии Excel, попробовал подключить на 32-битной - всё заработало.

Есть идеи по улучшению:
Явно просится булева функция IsMatch, проверяющая на соответствие выбранному шаблону, для участия в условиях.
Можно также добавить функцию Split с какими-нибудь доп. параметрами.
Изменено: medvalex - 07.11.2016 17:29:49
 
 
 
#16
08.11.2016 22:29:17
Цитата
medvalex написал:
Явно просится булева функция IsMatch
Она есть, собственно Содержит(A1;"[a-z]";1)
Для 64-битной версии тоже есть надстройка, это не проблема.
Развитие данной библиотеки пока не ведется ибо нет необходимости и мотивации - в принципе можно написать, что угодно и даже встроить полноценное приложение которое хоть по инету шарит хоть с удаленными сервисами работает.... в обшем ограничений почти нет - только апи экселя
Необходимо не только знать, но и уметь использовать это знание, ведь самые гениальные решения наиболее просты
 
 
 
#17
09.11.2016 10:12:24
Цитата
amarf написал: Она есть, собственно Содержит(A1;"[a-z]";1)
Не очень понял, что за функция "Содержит". В стандартных функциях Эксель её нет, в группе функций "A.M4rf C# Regex Functions" её тоже нет. Уточните?

Я-то, собственно, про вот этот конкретный метод:
https://msdn.microsoft.com/ru-ru/library/sdx2bds0(v=vs.110).aspx
Мне нужна функция проверки всей строки на соответствие шаблону, а не возможность найти где-нибудь внутри строки часть, соответствующую шаблону.

Что характерно, нашёл похожую настройку, только на C++:
http://xllregex.codeplex.com/
Там есть функция REGEX.MATCH, которая делает как раз проверку всей строки шаблону, но диалект регулярных выражений в C++ не поддерживает опцию игнорирования регистра букв для части шаблона (?i), а вот в .Net Ваша надстройка поддерживает.. Не хватает только Regex.IsMatch.

Нашёл, конечно, обходной путь:
=ЕСЛИ(RegexMatch(string;pattern)="";ЛОЖЬ;ИСТИНА)
Но он:
  1. Возвращает Истину для пустых строк, даже если они не удовлетворяют выражению
  2. Возвращает Истину, когда найдено соответствие только части строки, а не всей строки, шаблону.
 
 
 
#18
10.11.2016 22:07:00
Ещё есть такой вопрос: можно ли как-то с помощью регулярных выражений поменять регистр определенных частей строки? Например, "НАЧИНАТЬ С ПРОПИСНЫХ" в "Начинать С Прописных". Гуглил, однако нашёл только для Perl что-то... есть ли такая возможность в регулярных выражениях C# (.Net)?
 
 
 
#19
11.11.2016 13:04:26
Для этого проще формулу написать она, что на VBA что на .Net это очень простая задача. В Регулярных выражениях, если память не изменяет такой возможности нет.

Цитата
medvalex написал: Возвращает Истину для пустых строк, даже если они не удовлетворяют выражению
Возвращает Истину, когда найдено соответствие только части строки, а не всей строки, шаблону.
Я же писал, что библиотека не поддерживается, а значит предоставляется как есть, ну нет у меня пока желания ее развивать.
Необходимо не только знать, но и уметь использовать это знание, ведь самые гениальные решения наиболее просты
 
 
 
#20
21.11.2016 19:38:07
Цитата
amarf написал: библиотека не поддерживается, а значит предоставляется как есть
А исходный код где-нибудь можете выложить?
 
 
 
#21
21.11.2016 19:48:17
Согласен с medvalex. Исходный код - в студию!
There is no knowledge that is not power
 
 
 
#22
21.11.2016 20:36:10
Цитата
medvalex написал:
можно ли как-то с помощью регулярных выражений поменять регистр определенных частей строки? Например, "НАЧИНАТЬ С ПРОПИСНЫХ" в "Начинать С Прописных"
Код
?strconv("НАЧИНАТЬ С ПРОПИСНЫХ",vbProperCase)
Начинать С Прописных
 
 
 
#23
20.12.2016 00:33:24
Цитата
Казанский написал:
?strconv("НАЧИНАТЬ С ПРОПИСНЫХ",vbProperCase)
Про эту функцию я в курсе)) для себя сделал пользовательскую функцию немного поуниверсальнее. Может, кому-нибудь пригодится:
Скрытый текст
Изменено: medvalex - 20.12.2016 00:35:10
 
 
 
Страницы: 1
Читают тему
Наверх