必藏神技!Power BI读取带密码Excel数据! | PBI实战技能

- 1 -

前段时间,我在文章《工作簿有密码,自动刷新数据,没问题!| PQ重要技巧》里,写了关于用VBA先解除工作簿密码,刷新Power Query查询,然后再重新设置工作簿密码的方法:

但是,这个方法因为用到VBA,可是,在Power BI里,根本没有VBA啊!!!

所以,我们只能想其他办法。

- 2 -

讲具体实现方法之前,我们先来讲讲Power Query识别网页内容的问题。

前面我们讲过很多Power Query爬取网络数据的例子:

实际上,爬取网络数据,主要就是识别网页上的编码信息,然后提取其中的数据,而网页上的编码信息,通常由HTML及Java Script编写,最终显示为我们看到的内容,也就是说,Power Query其实就是识别了HTML或Java Script的编码而取得了相应的数据。

举个简单的例子,比如我们可以用HTML语言(想了解HTML或Java Script语言的朋友可以参考免费网站https://www.w3school.com.cn/),在记事本里写段简单的代码,然后另存为网页文件:

然后,用浏览器打开刚刚保存的html文件,对比一下其中的编码和显示结果:

在Power BI里,也可以用获取Web数据的方式直接读取。

首先,复制链接(等同于网爬时的网页链接):

在Power BI中“获取数据/Web”:

输入(粘贴)刚复制的html文件链接:

确定后就可以获得网页上的内容:

- 3 -

通过上面的内容,我们发现,可以通过Power Query获取网页脚本的输出结果,那么,如果我们可以通过Java Script脚本先读取带密码的Excel工作簿的内容(相当于生成网页数据),是不是就可以通过调用这个脚本来得到Excel工作簿的结果呢?

于是,赶紧搜索一下关于Java Script打开带密码Excel工作簿的方法,嘿,还真不少!

同时还可以看看其中的方法参数细节:

有了大佬的代码,复制改改就是了(关键变量及方法见代码注释,为使代码简洁,代码仅读取excel内容并通过“~”、“^”等特殊符号将各行每个单元格内容进行简单连接),代码如下:

<html><body><script type="text/javascript"> var filePath="D:/数据源-带密码.xlsx"; //要读取的excel文件 var sheet_id=1; //读取第1个表 var row_start=1; //从第1行开始读取 var col_count = 3; //读取的列数 var tempStr="";
try{ var oXL = new ActiveXObject("Excel.application"); //创建Excel.Application对象 }
catch(err) { alert(err); }
var oWB = oXL.Workbooks.open(filePath,0,false,1,"123456"); // 用密码(123456)打开 oWB.worksheets(sheet_id).select(); var oSheet = oWB.ActiveSheet; var row_count=oXL.Worksheets(sheet_id).UsedRange.Cells.Rows.Count ;
for(var i=row_start;i<=row_count;i++){ // 遍历每一行 for(var j=1;j<=col_count;j++){ // 遍历每一个单元格(列) tempStr+=oSheet.Cells(i,j).value+"^"; // 用 “^” 分开不同列数据 } tempStr+="~" // 用 “~” 分开不同行数据 }
document.write(tempStr); //返回
oXL.Quit();</script></body></html>

通过看代码,我们知道该JavaScript代码通过调用ActiveXObject对象打开Excel工作簿,但是,网页解析ActiveXObject内容时,会有安全问题,所以,先要设置一下IE的安全选项(启用未标记的ActiveX控件),方法如下:

为方便调用,在Power BI里以输入数据的方式,新建一个查询,存放该代码:

然后,在接入数据到查询中用Web.Page函数直接调用该代码:

然后,逐层进入相应的table:

最后即可得到读取的内容:

再按需要筛选出相应的内容,拆分到行、列:

最后即可得到完整的数据表:

- 4 -

总的来说,通过调用JavaScript脚本,我们可以实现加密Excel文件的数据读取,但是,整个过程还是有点儿周折,所以,如果不是十分必要,需要被Power BI引用进行分析的Excel文件,建议不要加密。

当然,很多时候,Excel的加密是企业的一些管理需要,万一遇到了,这时,大家可以参考本文的方法。

(0)

相关推荐