private static void ChangePivotCells(PivotTable pt, List <PivotCell> cells, List <string> values)
{
Worksheet sh = pt.TableRange1.Worksheet;
string sourceString = pt.PivotCache().SourceData;
ListObject dt = ListObjectHelper.getListObject(Globals.ThisAddIn.Application.ActiveWorkbook, sourceString);
if (dt == null)
{
MessageBox.Show("Datas source of the pivot table must be a Table");
pt.PivotCache().Refresh();
return;
// Range sourceRange = SheetRangeHelper.getRange(Globals.ThisAddIn.Application.ActiveWorkbook, sourceString);
}
// loop through changed cells, apply changes
for (int i = 0; i <= cells.Count - 1; i++)
{
if (!GeneralSettings.IsNumeric(values[i]) && !string.IsNullOrEmpty(values[i]))
{
System.Windows.Forms.MessageBox.Show("Only numeric values are allowed.");
}
else
{
ChangePivotCell(cells[i], values[i], dt);
}
}
ListObjectHelper.ResetTable(dt);
}
public override void RunCommand(object sender)
{
var engine = (IAutomationEngineInstance)sender;
string vSheet = v_SheetName.ConvertUserVariableToString(engine);
var vPivotTable = v_PivotTable.ConvertUserVariableToString(engine);
var excelObject = v_InstanceName.GetAppInstance(engine);
var excelInstance = (Application)excelObject;
var workSheet = excelInstance.Sheets[vSheet] as Worksheet;
PivotTable pivotTable = (PivotTable)workSheet.PivotTables(vPivotTable);
pivotTable.PivotCache().Refresh();
}
public async override Task RunCommand(object sender)
{
var engine = (IAutomationEngineInstance)sender;
string vSheet = (string)await v_SheetName.EvaluateCode(engine);
var vPivotTable = (string)await v_PivotTable.EvaluateCode(engine);
var excelObject = ((OBAppInstance)await v_InstanceName.EvaluateCode(engine)).Value;
var excelInstance = (Application)excelObject;
var workSheet = excelInstance.Sheets[vSheet] as Worksheet;
PivotTable pivotTable = (PivotTable)workSheet.PivotTables(vPivotTable);
pivotTable.PivotCache().Refresh();
}
void AddPivotSummary(WorkItemActionRequest request)
{
long startTicks = Log.EVENT_HANDLER("Enter", Common.PROJECT_NAME);
Workbook wb = Globals.ThisAddIn.Application.ActiveWorkbook;
Worksheet ws = Globals.ThisAddIn.Application.ActiveSheet;
Microsoft.Office.Interop.Excel.Range activeCell = Globals.ThisAddIn.Application.ActiveCell;
Options_AZDO_TFS options = new Options_AZDO_TFS();
int workItemID = int.Parse(request.WorkItemID);
XlHlp.XlLocation insertAt = CreateNewWorksheet(string.Format("P_WI_{0}", workItemID), GetOptions());
// TODO(crhodes)
// Figure out how to get the table name from the active cell.
var tableName = activeCell.ListObject.Name;
PivotCache pc = wb.PivotCaches().Create(
SourceType: XlPivotTableSourceType.xlDatabase,
SourceData: tableName,
Version: 6);
string insertRange = $"{insertAt.workSheet.Name}!R{options.StartingRow}C{options.StartingColumn}";
PivotTable pt = pc.CreatePivotTable(TableDestination: insertRange);
// this is from the macro recording. Not all may be needed or desired.
pt.ColumnGrand = true;
pt.HasAutoFormat = true;
pt.DisplayErrorString = false;
pt.DisplayNullString = true;
pt.EnableDrilldown = true;
pt.ErrorString = "";
pt.MergeLabels = false;
pt.NullString = "";
pt.PageFieldOrder = 2;
pt.PageFieldWrapCount = 0;
pt.PreserveFormatting = true;
pt.RowGrand = true;
pt.SaveData = true;
pt.PrintTitles = false;
pt.RepeatItemsOnEachPrintedPage = true;
pt.TotalsAnnotation = false;
pt.CompactRowIndent = 1;
pt.InGridDropZones = false;
pt.DisplayFieldCaptions = true;
pt.DisplayMemberPropertyTooltips = false;
pt.DisplayContextTooltips = true;
pt.ShowDrillIndicators = true;
pt.PrintDrillIndicators = false;
pt.AllowMultipleFilters = false;
pt.SortUsingCustomLists = true;
pt.FieldListSortAscending = false;
pt.ShowValuesRow = false;
pt.CalculatedMembersInFilters = false;
pt.RowAxisLayout(XlLayoutRowType.xlCompactRow);
pt.PivotCache().RefreshOnFileOpen = false;
pt.PivotCache().MissingItemsLimit = XlPivotTableMissingItems.xlMissingItemsDefault;
PivotField pf1 = pt.PivotFields("Source.Type");
PivotField pf2 = pt.PivotFields("Target.Type");
pf1.Orientation = XlPivotFieldOrientation.xlRowField;
pf1.Position = 1;
pf2.Orientation = XlPivotFieldOrientation.xlRowField;
pf2.Position = 2;
//pt.AddDataField(pf1, "Count", XlConsolidationFunction.xlCount);
//pf2.Orientation = XlPivotFieldOrientation.xlRowField;
//pf2.Position = 2;
//With ActiveSheet.PivotTables("PivotTable1").PivotFields("Target.Type")
// .Orientation = xlRowField
// .Position = 1
//End With
//ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables(_
// "PivotTable1").PivotFields("Target.Type"), "Count of Target.Type", xlCount
//With ActiveSheet.PivotTables("PivotTable1").PivotFields("SourceId")
// .Orientation = xlRowField
// .Position = 2
//End With
insertAt.workSheet.Select();
Log.EVENT_HANDLER("Exit", Common.PROJECT_NAME, startTicks);
}