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);
        }
Esempio n. 2
0
        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();
        }
Esempio n. 3
0
        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();
        }
Esempio n. 4
0
        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);
        }