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();
}
