Excel催化剂开源第47波-Excel与PowerBIDeskTop互通互联之第一篇

当国外都在追求软件开源，并且在GitHub等平台上产生了大量优质的开源代码时，但在国内却在刮着一股收割小白智商税的知识付费热潮，实在可悲。

互联网的精神乃是分享，让分享带来更多人的受益。

在PowerBI领域，出现了十分优秀的DAXStudio和Tabular Editor等开源工具，影响深远。借此，Excel催化剂也决定将最核心的、也是PowerBI群体中热切盼望到功能点进行开源。

但愿从中受益的群体，不要将其视为其有，并且利用信息不对称继续进行收割智商税的延续，并且最好能够在引用时按开源的原则，署名上代码出处。

此篇对应功能实现出自：第3波-与PowerbiDesktop互通互联（Excel透视表连接PowerbiDesktop数据模型）https://www.jianshu.com/p/e05460ad407d

如何识别到当前电脑打开的PowerBIDeskTop所开启的SSAS服务实例？

因PowerBIDeskTop运行的时候，是开启了SSAS的实例，和Sqlserver上开启的类似，只是功能上受限于本机访问，具体表现形式为在任务管理器上可查看到有msmdsrv.exe进程。

打开Pbix文件后出现的msmdsrv.exe进程

当打开多个pbix文件时，会出现多个msmdsrv.exe进程，而Excel连接PowerBIDeskTop的核心就变为识别到msmdsrv.exe所开启的端口号。

而就算识别到端口号时，如果有多个msmdsrv.exe同时运行，还需要将不同的msmdsrv.exe所开启的端口号，对应回原来的PowerBIDeskTop打开的Pbix文件。

只有将文件名关联进来，在用户查看时，才能分辨出具体哪个msmdsrv.exe端口对应的连接属于哪个模型，最终通过连接所需要的端口号，实现连接到所需要的相应的pbix文件对应的数据模型中来。

具体代码

Excel催化剂实现了以上的技术难点，使用的是DAXStudio开源代码里的代码片段。

老规则，先建立一个实体类，用于存储一些关键信息。

 class PbidFileInfo
 {
 public string FileName { get; set; }
 public int Port { get; set; }
 public string DbName { get; set; }
 public string ModelName { get; set; }
 }

通过以下的代码，对PowerBIDeskTop所开启的SSAS实例的端口号及对应的pbix文件名等信息进行获取，返回List清单。

public static List GetPbidPortTittleMappings()
 {
 List pbidPortTittleMappings = new List();
 ManagementClass mgmtClass = new ManagementClass("Win32_Process");
 foreach (ManagementObject process in mgmtClass.GetInstances())
 {
 string processName = process["Name"].ToString().ToLower();
 if (processName == "msmdsrv.exe")
 {
 // get the process pid
 System.UInt32 pid = (System.UInt32)process["ProcessId"];
 var parentPid = int.Parse(process["ParentProcessId"].ToString());
 var parentTitle = "";
 if (parentPid > 0)
 {
 var parentProcess = Process.GetProcessById(parentPid);
 //if (parentProcess.ProcessName == "devenv") _icon = EmbeddedSSASIcon.Devenv;
 parentTitle = parentProcess.MainWindowTitle;
 if (parentTitle.Length == 0)
 {
 // for minimized windows we need to use some Win32 api calls to get the title
 parentTitle = GetWindowTitle(parentPid);
 }
 }
 // Get the command line - can be null if we don't have permissions
 // but should have permission for PowerBI msmdsrv as it will have been
 // launched by the current user.
 string cmdLine = null;
 if (process["CommandLine"] != null)
 {
 cmdLine = process["CommandLine"].ToString();
 try
 {
 var rex = new System.Text.RegularExpressions.Regex("-ss"(?.*)"");
 var m = rex.Matches(cmdLine);
 if (m.Count == 0) continue;
 string msmdsrvPath = m[0].Groups["path"].Captures[0].Value;
 var portFile = string.Format("{0}msmdsrv.port.txt", msmdsrvPath);
 if (System.IO.File.Exists(portFile))
 {
 string sPort = System.IO.File.ReadAllText(portFile, Encoding.Unicode);
 string cnnString = cnnString = $"localhost:{sPort}";
 using (AMO.Server server = new AMO.Server())
 {
 server.Connect(cnnString);
 string dbName = server.Databases[0].Name;
 string modelName = server.Databases[0].Model.Name;
 pbidPortTittleMappings.Add(new Entity.PbidFileInfo()
 {
 FileName = parentTitle.Replace(" - Power BI Desktop", ""),
 Port = int.Parse(sPort),
 DbName = dbName,
 ModelName = modelName
 });
 }
 }
 }
 catch (Exception)
 {
 }
 }
 }
 }
 return pbidPortTittleMappings;
 }
 //#region PInvoke calls to get the window title of a minimize window
 delegate bool EnumThreadDelegate(IntPtr hWnd, IntPtr lParam);
 [DllImport("user32.dll")]
 static extern bool IsWindowVisible(IntPtr hWnd);
 [DllImport("user32.dll")]
 static extern bool EnumThreadWindows(int dwThreadId, EnumThreadDelegate lpfn,
 IntPtr lParam);
 private static IEnumerable EnumerateProcessWindowHandles(int processId)
 {
 var handles = new List();
 foreach (ProcessThread thread in Process.GetProcessById(processId).Threads)
 EnumThreadWindows(thread.Id,
 (hWnd, lParam) => { handles.Add(hWnd); return true; }, IntPtr.Zero);
 return handles;
 }
 private const uint WM_GETTEXT = 0x000D;
 [DllImport("user32.dll", CharSet = CharSet.Auto)]
 static extern IntPtr SendMessage(IntPtr hWnd, uint Msg, int wParam,
 StringBuilder lParam);
 private static string GetWindowTitle(int procId)
 {
 foreach (var handle in EnumerateProcessWindowHandles(procId))
 {
 StringBuilder message = new StringBuilder(1000);
 if (IsWindowVisible(handle))
 {
 SendMessage(handle, WM_GETTEXT, message.Capacity, message);
 if (message.Length > 0) return message.ToString();
 }
 }
 return "";
 }

以上代码的思路为：使用Win32_Process下的ManagementClass，获取到msmdsrv.exe对应的pid。

再通过一些系统API函数，获取到其对应的标题。

再利用CommandLine的特性，取到msmdsrv.exe对应的路径配合正则处理和路径拼接得到最终的msmdsrv.port.txt文件全路径。如此次笔者机器的路径为

C:UsersAdministratorMicrosoftPower BI Desktop Store AppAnalysisServicesWorkspacesAnalysisServicesWorkspace115089896Datamsmdsrv.port.txt

C:UsersAdministratorMicrosoftPower BI Desktop Store AppAnalysisServicesWorkspacesAnalysisServicesWorkspace1855860078Datamsmdsrv.port.txt

再利用此文件下存储着端口号信息的特性，最终将不同的msmdsrv.exe对应的端口号拿到手。

再利用AMO对象模型，将此端口号下的数据库名和Model名也读取到。

最终拿齐了所有信息后，可以回到Excel客户端去发起访问连接。

而在用户层面，使用窗体直观呈现关键可读性信息供用户选择不同的模型。

在Excel客户端上展示关键信息供用户选择

 private void formPbidNewConnect_Load(object sender, EventArgs e)
 {
 this.dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
 List pbidPortTittleMappings = PbidConnection.GetPbidPortTittleMappings();
 foreach (var item in pbidPortTittleMappings)
 {
 this.dataGridView1.Rows.Add(new object[] { item.Port, item.FileName,item.DbName,item.ModelName });
 }
 this.dataGridView1.Rows[0].Selected = true;
 }

在数据透视表层面，只需构造出一条Oledb连接，用MSOLAP的provider即可。

具体代码如下：

 private void btnEnter_Click(object sender, EventArgs e)
 {
 DataGridViewRow row = this.dataGridView1.SelectedRows[0];
 Entity.PbidFileInfo pbidFileInfo = Common.GetPbidFileInfo(row);
 Excel.WorkbookConnection wkbcnn;
 if (Common.ExcelApp.ActiveWorkbook.Connections.Cast().Any(s=>s.Name==pbidFileInfo.FileName))
 {
 Common.ExcelApp.ActiveWorkbook.Connections[pbidFileInfo.FileName].Delete();
 }
 string wkbCnnString = $"OLEDB;Provider=MSOLAP;Integrated Security=SSPI;Persist Security Info=True;Data Source=localhost:{pbidFileInfo.Port};Initial Catalog={pbidFileInfo.DbName};";
 Common.ExcelApp.ActiveWorkbook.Connections.Add(pbidFileInfo.FileName, "pbidConnection", wkbCnnString, pbidFileInfo.ModelName, 1);
 Excel.Worksheet wht = Common.ExcelApp.Worksheets.Add();
 wkbcnn = Common.ExcelApp.ActiveWorkbook.Connections[pbidFileInfo.FileName];
 Excel.PivotCache pvtCache = Common.ExcelApp.ActiveWorkbook.PivotCaches().Create(Excel.XlPivotTableSourceType.xlExternal, wkbcnn);
 pvtCache.CreatePivotTable(wht.Range["A1"]);
 this.Close();
 }

结语

没有什么是不能分享的，为了社区的健康繁荣，Excel催化剂将最精华的最具商业价值的代码贡献给社区，也让中国的社区的声音能够更加的响亮，带出国际性的影响力。