private static WorkbookConnection FindPowerPivotConnection(Workbook wb) { WorkbookConnection wbc = null; foreach (WorkbookConnection c in wb.Connections) { Debug.WriteLine("WorkbookConnection: " + c.Name); if (!c.InModel) { continue; } if (c.Type == XlConnectionType.xlConnectionTypeMODEL) { continue; } //if (c.Name == "ThisWorkbookDataModel") continue; if (c.ModelTables == null) { continue; } if (c.ModelTables.Count == 0) { continue; } // otherwise wbc = c; break; } return(wbc); }
public static void DaxQueryTable2013(Worksheet excelSheet, string daxQuery)//, IOutputWindow output) { Worksheet ws = excelSheet; Workbook wb = excelSheet.Parent; WorkbookConnection wbc = FindPowerPivotConnection(wb); if (wbc == null) { throw new Exception("Workbook table connection not found"); } var listObjs = ws.ListObjects; Range r = ws.Cells[1, 1]; var lo = listObjs.Add(SourceType: XlListObjectSourceType.xlSrcModel , Source: wbc , Destination: r); var to = lo.TableObject; to.RowNumbers = false; to.PreserveFormatting = true; to.RefreshStyle = XlCellInsertionMode.xlInsertEntireRows; to.AdjustColumnWidth = true; //to.ListObject.DisplayName = "DAX query"; var oleCnn = to.WorkbookConnection.OLEDBConnection; oleCnn.CommandType = XlCmdType.xlCmdDAX; string[] qryArray = daxQuery.Split(new char[] { '\r' }, StringSplitOptions.RemoveEmptyEntries); oleCnn.CommandText = qryArray; oleCnn.Refresh(); WriteQueryToExcelComment(excelSheet, daxQuery); }
public void EnsurePowerPivotDataIsLoaded2013() { WorkbookConnection wbc = FindPowerPivotConnection(_app.ActiveWorkbook); if (wbc != null) { _app.ActiveWorkbook.Model.Initialize(); } //wbc.Refresh(); }