📢作者: 小小明-代码实体
📢博客主页:https://blog.csdn.net/as604049322
📢欢迎点赞 👍 收藏 ⭐留言 📝 欢迎讨论!
需求描述
原始数据如下:
共1.2万多条数据,现在我们需要给来源网址添加可以点击到目标网址的链接,并通过HS编码生成详情页链接。
最终生成效果如下:
这个案例用Python实现会很简单,但是我们可以通过这个简单的例子练习js宏的使用,由易到难。
WPS JS宏入门
视频学习资料:
- WPS JS宏入门视频教程:https://www.wps.cn/learning/course/detail/id/330734.html
API文档:
- WPS JS宏API文档:https://open.wps.cn/docs/client/wpsLoad
- Excel VBA 参考文档:https://learn.microsoft.com/zh-cn/office/vba/api/overview/excel
另外是必须懂JavaScript语言的语法,本人对JavaScript语言的熟练程度远高于VB,所以WPS宏的js宏也相对写的非常顺手舒服。
JavaScript文档教程推荐:
- 廖雪峰:https://www.liaoxuefeng.com/wiki/1022910821149312
- w3school:https://www.w3school.com.cn/js/index.asp
- 菜鸟教程:https://www.runoob.com/js/
有宏录制功能,即使在不查API文档的情况下,也能清楚知道一些操作代码。比如我们不知道如何添加链接,可以先点击开发工具的录制新宏功能:
此时我们打开WPS宏编辑器,对照WPS主窗口,可以看到WPS的任何操作都会在对应的方法中生成代码:
此时我们可以点击停止录制,之后我们的操作将不会在Macro1方法中自动生成代码。
通过生成的代码可以很清楚的知道,js宏向一个单元格添加超链接的示例代码就是:
Range("H2").Select();
Selection.Hyperlinks.Add(Selection, "https://www.i5a6.com/hstree/01", "", "", "/hstree/01");
下面我们根据以前编写VBA的经验,测试一下对应的JS宏代码。首先测试通过JS宏获取表格的行数:
function test()
{
Console.log(Range("A2").End(xlDown).Row);
}
结果在立即窗口中显示:
12624
可以看到顺利获取了最后一行的行号。
注意:菜单中的插入->过程 可以创建函数。
VB宏的调试方法是
Debug.Print
,但是JS宏也可以使用JavaScript语言的调试方法Console.log
。
其他常用调试方法:
alert("调试信息3") // 弹出警告框
Console.clear() //清空所有日志
WPS JS宏批量添加链接
有了前面的测试,我们可以很轻松的编写添加链接的代码:
function 添加来源链接()
{
let max_row=Range("H2").End(xlDown).Row;
for(let i=2;i<=max_row;i++) {
let rng = Range("H"+i),v=rng.Value();
rng.Select();
Selection.Hyperlinks.Add(rng, "https://www.i5a6.com"+v, undefined, undefined, v);
}
}
然后再编写批量生成详情页链接的代码:
function 生成详情页代码()
{
let max_row=Range("A2").End(xlDown).Row;
// Application.ScreenUpdating=false;
for(let i=2;i<=max_row;i++){
let v = "/hscode/detail/"+Range("A"+i).Value().replace(".","");
let rng=Range("I"+i);
rng.Select();
Selection.Hyperlinks.Add(rng, "https://www.i5a6.com"+v, undefined, undefined, v);
}
// Application.ScreenUpdating=true;
}
上面代码执行耗时还是比较长的,可以通过Application.ScreenUpdating
不进行屏幕刷新进行加速,即解除注释即可,但这样也会导致无法看到当前的执行进度。
经过上述代码,就顺利解决了需求。
Python批量添加链接
我们试试直接用Python实现,完整代码:
import openpyxl as oxl
from openpyxl.styles import Side, Border, Alignment
side = Side(style="thin", color="000000")
border = Border(left=side, right=side, top=side, bottom=side)
alignment = Alignment(horizontal="left", vertical="center")
wb = oxl.load_workbook("海关进出口编码_带链接.xlsx")
sht = wb.active
for i in range(2, sht.max_row+1):
# 添加来源链接
cell = sht[f"H{i}"]
cell.hyperlink = "https://www.i5a6.com"+cell.value
cell.style = "Hyperlink"
cell.border = border
cell.alignment = alignment
# 生成详情页代码
v = "/hscode/detail/"+sht[f"A{i}"].value.replace(".", "")
cell = sht[f"I{i}"]
cell.value = v
cell.hyperlink = "https://www.i5a6.com"+v
cell.style = "Hyperlink"
cell.border = border
cell.alignment = alignment
wb.save("text.xlsx")
可惜速度有点不尽人意,保存时耗时居然达到一分钟。看来用Python还是js宏解决这种问题,效率上Python和宏 都差不多,Python并没有体现出明显的快的优势。
Python控制WPS调用COM组件实现
既然使用跨平台的Python库没那么快,试试调用COM组件来实现吧。
完整代码:
import xlwings as xw
app = xw.App(add_book=False)
wb = app.books.open("海关进出口编码_带链接.xlsx")
sht = wb.sheets.active
max_row = sht.range("A2").end('down').row
app.screen_updating = False
for i in range(2, max_row+1):
cell = sht.range(f"H{i}")
sht.api.Hyperlinks.Add(cell.api, "https://www.i5a6.com"+cell.value)
v = "/hscode/detail/"+sht.range(f"A{i}").value.replace(".", "")
cell = sht.range(f"I{i}")
sht.api.Hyperlinks.Add(cell.api, "https://www.i5a6.com"+v, None, None, v)
app.screen_updating = True
运行近7分钟依然没运行完,我已经无法忍受,直接强制中断了程序:
此时我再执行app.screen_updating = True
后查看wps后发现,才跑9千多条数据:
所以对于这种大批量的改样式的需求,直接使用宏,或完全跨平台的Python库更佳。
for循环补充
对于批量添加链接,遍历单元格的另外一种写法是:
for cell in sht.range(sht.range("H2"), sht.range("H2").end('down')):
sht.api.Hyperlinks.Add(cell.api, "https://www.i5a6.com"+cell.value)
对于JS宏则需要这样写:
function 添加来源链接()
{
for(cell of Range(Range("H2"),Range("H2").End(xlDown))){
cell.Select();
Selection.Hyperlinks.Add(cell, "https://www.i5a6.com"+cell.Value());
}
}
经测试,通过宏,假如不选择单元格,直接添加链接,经常报错中断。这也是JS宏使用Selection添加超链接的原因。文章来源:https://www.toymoban.com/news/detail-447222.html
报错示例:文章来源地址https://www.toymoban.com/news/detail-447222.html
for(cell of Range(Range("H2"),Range("H2").End(xlDown))){
ActiveSheet.Hyperlinks.Add(cell, "https://www.i5a6.com"+cell.Value());
}
到了这里,关于WPS JS宏示例-批量添加链接的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!