【原】必藏神技！Power BI读取带密码Excel数据！ | PBI实战技能

大海_Power 2021-09-06

展开全文

- 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里以输入数据的方式，新建一个查询，存放该代码：