在2020年时,我在《自加载宏让你的Excel支持正则处理函数》一文中演示了给office的Excel增加正则处理函数,文本处理瞬间变得记得简单。VBA的宏要在WPS中使用,也只需要安装wps对应的vba库即可,上面的文章中有下载链接。
JS宏开发正则处理函数
正则表达式的参考资料:
小小明Python正则表达式速查表与实操手册
https://xxmdmst.blog.csdn.net/article/details/112691043
JS宏API文档直达页面:https://qn.cache.wpscdn.cn/encs/doc/office_v19/index.htm
新建Excel,打开开发工具->wps宏编辑器,编写如下代码:
functionreadValue(target){returntypeof(target)=="string"?target:target.Item(1).Value2}//正则替换functionre_replace(text,pattern,repl){text=readValue(text),pattern=readValue(pattern),repl=readValue(repl);letregexp=newRegExp(pattern,"g");returntext.replace(regexp,repl);}//正则查找functionre_findall(text,pattern){text=readValue(text),pattern=readValue(pattern);letregexp=newRegExp(pattern,"g");letresult=[];while(a=regexp.exec(text)){result.push(a[0])}returnresult;}//正则抽取functionre_draw(text,pattern){text=readValue(text),pattern=readValue(pattern);letregexp=newRegExp(pattern,"g");letarr=regexp.exec(text);arr.shift();returnarr.map(v=>v||"");}//正则判断,搜索是否包含指定的模式字符串functionre_test(text,pattern){text=readValue(text),pattern=readValue(pattern);letregexp=newRegExp(pattern,"g");returnregexp.test(text);}//正则切割functionre_split(text,pattern){text=readValue(text),pattern=readValue(pattern);letrule=newRegExp(pattern,"g");returntext.split(rule);}
相对于vba而言,JS宏开发正则函数实在是过于简单。因此,相对之前的vba代码而言,我们还增加了正则匹配判断和正则切割的实现。
保存Excel文件为re.xlam
,即自加载的宏文件。将其放到C:\Users\{userName}\AppData\Roaming\Microsoft\Excel\XLSTART\
注意:{userName}表示你当前的用户名。
我个人是将之前的vba宏和现在写的js宏都保存到了这个文件里:
对于WPS而已,Excel宏放在上述目录下能自动加载需要开启WPS的office兼容模式。在开始菜单的配置工具中:
个人开启的对2007的兼容模式,应该对2010的兼容模式也是该目录。
若不想开启兼容模式还可以通过everything搜索XLSTART
获取其他的加载项目录,将文件放到其中。例如,我搜索到C:\Users\ASUS\AppData\Roaming\kingsoft\wps\XLSTART
。
JS正则函数测试
对于之前已经测试过的正则,这次也全部测试通过。
正则抽取:
=re_draw(A1,"([^|(])(?:\(共(\d)层\))?(?:\|(\d{4})年建\|)?(\d室\d厅)\|([\d.])平米\|([东南西北])")=re_draw(A1,"(.?)(\d)")
注意:对于数组公式(CtrlshiftEnter),WPS无法直接拖动填充柄。需要复制公式后,选中要填充的区域,再以公式形式选择性粘贴。
先复制数组公式,再直接在剩余区域粘贴也行,直接粘贴后也可以切换到纯公式的形式。
正则搜索:
=re_findall(A1,"[\u4e00-\u9fa5]|^\w$")=re_findall(A1,"[-_\d]")
正则替换:
=re_replace(A1,"[A-Z0-9_\-]|\(.*\)","")
下面我们测试一下正则匹配测试,需求是判断一组地址字符串中是否包含指定的关键字(任意一个都行):
=re_test(A1,"(?:葵涌|港岛|元朗|沙咀)")
某些情况下使用正则切割进行分列会很方便,例如字符串的分隔符是长度不确定的空白字符:
=re_split(A1,"\s")