public void RefreshPivotRable(string name)
{
PivotTables sheetPivotTables = _sheet.PivotTables(Type.Missing);
PivotTable pivotTable = sheetPivotTables.Item(name);
pivotTable.RefreshTable();
}
public override void RunCommand(object sender)
{
var engine = (IAutomationEngineInstance)sender;
string vSheetExcelTable = v_SheetNameExcelTable.ConvertUserVariableToString(engine);
string vSheetPivotTable = v_SheetNamePivotTable.ConvertUserVariableToString(engine);
var vTableName = v_TableName.ConvertUserVariableToString(engine);
var vCellLocation = v_CellLocation.ConvertUserVariableToString(engine);
var vPivotTable = v_PivotTable.ConvertUserVariableToString(engine);
var excelObject = v_InstanceName.GetAppInstance(engine);
var excelInstance = (Application)excelObject;
var workBook = excelInstance.ActiveWorkbook;
var workSheetExcelTable = excelInstance.Sheets[vSheetExcelTable] as Worksheet;
var workSheetPivotTable = excelInstance.Sheets[vSheetPivotTable] as Worksheet;
var excelTable = workSheetExcelTable.ListObjects[vTableName];
object useDefault = Type.Missing;
Range pivotDestination = workSheetPivotTable.Range[vCellLocation, useDefault];
var pivotCache = workBook.PivotCaches().Create(XlPivotTableSourceType.xlDatabase, vTableName, Type.Missing);
PivotTable pivotTable = pivotCache.CreatePivotTable(pivotDestination, vPivotTable, Type.Missing, Type.Missing);
PivotField pivotField;
for (int i = 1; i <= excelTable.ListColumns.Count; i++)
{
pivotField = pivotTable.PivotFields(i);
if (XlPivotFieldDataType.xlText != pivotField.DataType)
{
pivotTable.AddDataField(pivotTable.PivotFields(i), Type.Missing, Type.Missing);
}
else
{
pivotTable.PivotFields(i).Orientation = XlPivotFieldOrientation.xlRowField;
}
}
pivotTable.RefreshTable();
}