Ejemplo n.º 1
0
 private static void BuildHeader(PivotTable pivotTable, StringBuilder builder)
 {
     builder.Append("<thead><tr>");
     foreach (string groupTitle in pivotTable.GroupTitles)
         builder.AppendFormat(@"<th class=""group-title"">{0}</th>", groupTitle);
     foreach (string valueTitle in pivotTable.ValueTitles)
         builder.AppendFormat(@"<th class=""value-title"">{0}</th>", valueTitle);
     builder.Append("</tr></thead>");
 }
Ejemplo n.º 2
0
 /// <summary>
 /// Changes the name of the pivot field custom.
 /// </summary>
 /// <param name="pivotTable">The pivot table.</param>
 /// <param name="dataFieldName">Name of the data field.</param>
 /// <param name="dataFieldCustomName">Name of the data field custom.</param>
 public static void ChangePivotFieldCustomName(PivotTable pivotTable, string dataFieldName, string dataFieldCustomName)
 {
     for (int i = 0; i < pivotTable.DataFields.Count; i++)
     {
         if (pivotTable.DataFields[i].Name == dataFieldName)
         {
             pivotTable.DataFields[i].DisplayName = dataFieldCustomName;
         }
     }
 }
Ejemplo n.º 3
0
 private static void BuildBody(PivotTable pivotTable, StringBuilder builder)
 {
     builder.Append("<tbody>");
     if (pivotTable.ShowTotal == ShowTotal.Top)
         BuildTotal(pivotTable, builder);
     var startRow = new Stack<bool>();
     BuildGroups(pivotTable.Groups, 0, pivotTable.GroupTitles.Count(), startRow, builder);
     if (pivotTable.ShowTotal == ShowTotal.Bottom)
         BuildTotal(pivotTable, builder);
     builder.Append("</tbody>");
 }
        static void MoveFieldToAxis(IWorkbook workbook)
        {
            #region #MoveToAxis
            Worksheet worksheet = workbook.Worksheets["Report1"];
            workbook.Worksheets.ActiveWorksheet = worksheet;

            // Access the pivot table by its name in the collection.
            PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];

            // Move the "Region" field to the column axis area.
            pivotTable.ColumnFields.Add(pivotTable.Fields["Region"]);
            #endregion #MoveToAxis
        }
        static void MoveFieldUp(IWorkbook workbook)
        {
            #region #MoveUp
            Worksheet worksheet = workbook.Worksheets["Report3"];
            workbook.Worksheets.ActiveWorksheet = worksheet;

            // Access the pivot table by its name in the collection.
            PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];

            // Move the "Category" field one position up in the row area.
            pivotTable.RowFields["Category"].MoveUp();
            #endregion #MoveUp
        }
        static void InsertFieldToAxis(IWorkbook workbook)
        {
            #region #InsertAtTop
            Worksheet worksheet = workbook.Worksheets["Report1"];
            workbook.Worksheets.ActiveWorksheet = worksheet;

            // Access the pivot table by its name in the collection.
            PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];

            // Insert the "Region" field at the top of the row axis area.
            pivotTable.RowFields.Insert(0, pivotTable.Fields["Region"]);
            #endregion #InsertAtTop
        }
Ejemplo n.º 7
0
        public PivotTable GenerateAverageVector(PivotTable tableWithMoreThanOneRow)
        {
            if (tableWithMoreThanOneRow.Count < 2)
            {
                throw new Exception("Must Have more than one row");
            }

            Dictionary <String, List <Double> > data = new Dictionary <String, List <Double> >();

            //consolidate all the values for each column of each entry in the pivot table
            foreach (PivotTableEntry entry in tableWithMoreThanOneRow)
            {
                foreach (String key in entry.Data.Keys)
                {
                    if (data.ContainsKey(key))
                    {
                        data[key].Add(entry.Data[key]);
                    }
                    else
                    {
                        List <Double> newList = new List <Double>();
                        newList.Add(entry.Data[key]);
                        data.Add(key, newList);
                    }
                }
            }
            //now average each and produce a new data dictionary
            Dictionary <String, Double> averages = new Dictionary <String, Double>();

            foreach (String key in data.Keys)
            {
                double avg = 0d;
                foreach (Double val in data[key])
                {
                    avg += val;
                }
                avg = avg / data[key].Count;
                averages.Add(key, avg);
            }
            PivotTableEntry newEntry = new PivotTableEntry()
            {
                Data    = averages,
                Context = tableWithMoreThanOneRow[0].Context,
                RowKey  = tableWithMoreThanOneRow[0].RowKey
            };

            PivotTable pt = new PivotTable();

            pt.Add(newEntry);
            return(pt);
        }
Ejemplo n.º 8
0
        static void ChangeBehaviorOptions(IWorkbook workbook)
        {
            #region #Change Behavior Options
            Worksheet worksheet = workbook.Worksheets["Report1"];
            workbook.Worksheets.ActiveWorksheet      = worksheet;
            worksheet.Columns["B"].WidthInCharacters = 40;
            PivotTable           pivotTable      = worksheet.PivotTables["PivotTable1"];
            PivotBehaviorOptions behaviorOptions = pivotTable.Behavior;
            behaviorOptions.AutoFitColumns  = false;
            behaviorOptions.EnableFieldList = false;
            pivotTable.Cache.Refresh();

            #endregion #Change Behavior Options
        }
Ejemplo n.º 9
0
        static void ChangePivotTableDataSource(IWorkbook workbook)
        {
            #region #Change DataSource
            Worksheet worksheet = workbook.Worksheets["Report1"];
            workbook.Worksheets.ActiveWorksheet = worksheet;
            PivotTable pivotTable      = worksheet.PivotTables["PivotTable1"];
            Worksheet  sourceWorksheet = workbook.Worksheets["Data2"];
            pivotTable.ChangeDataSource(sourceWorksheet["A1:H6367"]);
            pivotTable.RowFields.Add(pivotTable.Fields["State"]);
            PivotDataField dataField = pivotTable.DataFields.Add(pivotTable.Fields["Yearly Earnings"]);
            dataField.SummarizeValuesBy = PivotDataConsolidationFunction.Average;

            #endregion #Change DataSource
        }
Ejemplo n.º 10
0
        static void CreatePivotTableFromRange(IWorkbook workbook)
        {
            #region #Create from Range
            Worksheet sourceWorksheet = workbook.Worksheets["Data1"];
            Worksheet worksheet       = workbook.Worksheets.Add();
            workbook.Worksheets.ActiveWorksheet = worksheet;
            PivotTable pivotTable = worksheet.PivotTables.Add(sourceWorksheet["A1:D41"], worksheet["B2"]);
            pivotTable.RowFields.Add(pivotTable.Fields["Category"]);
            pivotTable.RowFields.Add(pivotTable.Fields["Product"]);
            pivotTable.DataFields.Add(pivotTable.Fields["Sales"]);
            pivotTable.Style = workbook.TableStyles.DefaultPivotStyle;

            #endregion #Create from Range
        }
Ejemplo n.º 11
0
        static void CreatePivotTableFromCache(IWorkbook workbook)
        {
            #region #Create from PivotCache
            Worksheet worksheet = workbook.Worksheets.Add();
            workbook.Worksheets.ActiveWorksheet = worksheet;
            PivotCache cache      = workbook.Worksheets["Report1"].PivotTables["PivotTable1"].Cache;
            PivotTable pivotTable = worksheet.PivotTables.Add(cache, worksheet["B2"]);
            pivotTable.RowFields.Add(pivotTable.Fields["Category"]);
            pivotTable.RowFields.Add(pivotTable.Fields["Product"]);
            pivotTable.DataFields.Add(pivotTable.Fields["Sales"]);
            pivotTable.Style = workbook.TableStyles.DefaultPivotStyle;

            #endregion #Create from PivotCache
        }
Ejemplo n.º 12
0
        static void MultipleSubtotals(IWorkbook workbook)
        {
            #region #MultipleSubtotals
            Worksheet worksheet = workbook.Worksheets["Report1"];
            workbook.Worksheets.ActiveWorksheet = worksheet;

            // Access the pivot table by its name in the collection.
            PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
            // Access the pivot field by its name in the collection.
            PivotField field = pivotTable.Fields["Category"];
            // Display multiple subtotals for the field.
            field.SetSubtotal(PivotSubtotalFunctions.Sum | PivotSubtotalFunctions.Average);
            #endregion #MultipleSubtotals
        }
        public static PivotTable getPivotTable(Worksheet sh, string Name)
        {
            PivotTable ret = null;

            foreach (PivotTable pt in sh.PivotTables())
            {
                if (pt.Name == Name)
                {
                    ret = pt;
                }
            }

            return(ret);
        }
Ejemplo n.º 14
0
        static void SortFieldItemsByDataField(IWorkbook workbook)
        {
            #region #SortFieldItemsByDataField
            Worksheet worksheet = workbook.Worksheets["Report1"];
            workbook.Worksheets.ActiveWorksheet = worksheet;

            // Access the pivot table by its name in the collection.
            PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
            // Access the pivot field by its name in the collection.
            PivotField field = pivotTable.Fields["Product"];
            // Sort items in the "Product" field by values of the "Sum of Sales" data field.
            field.SortItems(PivotFieldSortType.Ascending, 0);
            #endregion #SortFieldItemsByDataField
        }
        static void SetMultipleFilter(IWorkbook workbook)
        {
            #region #Multiple Filters
            Worksheet worksheet = workbook.Worksheets["Report6"];
            workbook.Worksheets.ActiveWorksheet = worksheet;
            PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
            pivotTable.Behavior.AllowMultipleFieldFilters = true;
            PivotField field1 = pivotTable.Fields[0];
            pivotTable.Filters.Add(field1, PivotFilterType.SecondQuarter);
            PivotFilter filter = pivotTable.Filters.Add(field1, pivotTable.DataFields[0], PivotFilterType.Count, 2);
            filter.Top10Type = PivotFilterTop10Type.Bottom;

            #endregion #Multiple Filters
        }
        static void SetLabelFilter(IWorkbook workbook)
        {
            #region #LabelFilter
            Worksheet worksheet = workbook.Worksheets["Report4"];
            workbook.Worksheets.ActiveWorksheet = worksheet;

            // Access the pivot table by its name in the collection.
            PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
            // Access the "Region" field.
            PivotField field = pivotTable.Fields[0];
            // Filter the "Region" field by text to display sales data for the "South" region.
            pivotTable.Filters.Add(field, PivotFilterType.CaptionEqual, "South");
            #endregion #LabelFilter
        }
Ejemplo n.º 17
0
        static void MergeTitles(IWorkbook workbook)
        {
            #region #MergeTitles
            Worksheet worksheet = workbook.Worksheets["Report4"];
            workbook.Worksheets.ActiveWorksheet = worksheet;

            // Access the pivot table by its name in the collection.
            PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
            // Display the pivot table in the tabular form.
            pivotTable.Layout.SetReportLayout(PivotReportLayout.Tabular);
            // Merge and center cells with labels.
            pivotTable.Layout.MergeTitles = true;
            #endregion #MergeTitles
        }
        static void SetValueFilter(IWorkbook workbook)
        {
            #region #ValueFilter
            Worksheet worksheet = workbook.Worksheets["Report4"];
            workbook.Worksheets.ActiveWorksheet = worksheet;

            // Access the pivot table by its name in the collection.
            PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
            // Access the "Product" field.
            PivotField field = pivotTable.Fields[1];
            // Filter the "Product" field to display products with grand total sales between $6000 and $13000.
            pivotTable.Filters.Add(field, pivotTable.DataFields[0], PivotFilterType.ValueBetween, 6000, 13000);
            #endregion #ValueFilter
        }
        static void GroupFieldByDates(IWorkbook workbook)
        {
            #region #GroupFieldByDates
            Worksheet worksheet = workbook.Worksheets["Report8"];
            workbook.Worksheets.ActiveWorksheet = worksheet;

            // Access the pivot table by its name in the collection.
            PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
            // Access the "DATE" field by its name in the collection.
            PivotField field = pivotTable.Fields["DATE"];
            // Group field items by quarters and months.
            field.GroupItems(PivotFieldGroupByType.Quarters | PivotFieldGroupByType.Months);
            #endregion #GroupFieldByDates
        }
        static void GroupFieldByNumericRanges(IWorkbook workbook)
        {
            #region #GroupFieldByNumericRanges
            Worksheet worksheet = workbook.Worksheets["Report12"];
            workbook.Worksheets.ActiveWorksheet = worksheet;

            // Access the pivot table by its name in the collection.
            PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
            // Access the "Usual Hours Worked" field by its name in the collection.
            PivotField field = pivotTable.Fields["Sales"];
            // Group field items from 1000 to 4000 by 1000.
            field.GroupItems(1000, 4000, 1000, PivotFieldGroupByType.NumericRanges);
            #endregion #GroupFieldByNumericRanges
        }
Ejemplo n.º 21
0
        static void SortFieldItems(IWorkbook workbook)
        {
            #region #SortFieldItems
            Worksheet worksheet = workbook.Worksheets["Report1"];
            workbook.Worksheets.ActiveWorksheet = worksheet;

            // Access the pivot table by its name in the collection.
            PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
            // Access the pivot field by its name in the collection.
            PivotField field = pivotTable.Fields["Product"];
            // Sort items in the "Product" field.
            field.SortType = PivotFieldSortType.Ascending;
            #endregion #SortFieldItems
        }
Ejemplo n.º 22
0
        static void ShowRowHeaders(IWorkbook workbook)
        {
            #region #RowHeaders
            Worksheet worksheet = workbook.Worksheets["Report1"];
            workbook.Worksheets.ActiveWorksheet = worksheet;

            // Access the pivot table by its name in the collection.
            PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
            // Add the "Region" field to the column axis area.
            pivotTable.ColumnFields.Add(pivotTable.Fields["Region"]);
            // Remove formatting from row headers.
            pivotTable.ShowRowHeaders = false;
            #endregion #RowHeaders
        }
        static void SetDateFilter(IWorkbook workbook)
        {
            #region #DateFilter
            Worksheet worksheet = workbook.Worksheets["Report6"];
            workbook.Worksheets.ActiveWorksheet = worksheet;

            // Access the pivot table by its name in the collection.
            PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
            // Access the "Date" field.
            PivotField field = pivotTable.Fields[0];
            // Filter the "Date" field to display sales for the second quarter.
            pivotTable.Filters.Add(field, PivotFilterType.SecondQuarter);
            #endregion #DateFilter
        }
Ejemplo n.º 24
0
        public void PivotTable_Simple()
        {
            var pvtData = getSamplePivotData();
            var pvtTbl  = new PivotTable(new string[] { "B" }, new string[] { "A" }, pvtData);

            Assert.Equal(3, pvtTbl.ColumnKeys.Length);
            Assert.Equal(3, pvtTbl.RowKeys.Length);
            Assert.Equal("A1", pvtTbl.ColumnKeys[0].DimKeys[0]);
            Assert.Equal("B1", pvtTbl.RowKeys[0].DimKeys[0]);

            Assert.Equal(54, Convert.ToInt32(pvtTbl[null, null].Value));               // global totals
            Assert.Equal(6, Convert.ToInt32(pvtTbl[0, 0].Value));
            Assert.Equal(18, Convert.ToInt32(pvtTbl[0, null].Value));

            var pvtData2 = getSamplePivotData(true);
            var pvtTbl2  = new PivotTable(new string[] { "A", "B" }, new string[] { "C" }, pvtData2);

            Assert.Equal(6, pvtTbl2.RowKeys.Length);
            Assert.Equal("A1", pvtTbl2.RowKeys[0].DimKeys[0]);
            Assert.Equal("B1", pvtTbl2.RowKeys[0].DimKeys[1]);
            Assert.Equal(20, Convert.ToInt32(pvtTbl2[null, null].Value));               // global totals
            Assert.Equal(2, Convert.ToInt32(pvtTbl2[5, null].Value));

            var pvtTbl3 = new PivotTable(new string[] { "A", "B", "C" }, null, pvtData);

            Assert.Equal(6, Convert.ToInt32(
                             pvtTbl3.GetValue(new ValueKey(new object[] { "A1", "B1", Key.Empty }), null).Value));
            Assert.Equal(18, Convert.ToInt32(
                             pvtTbl3.GetValue(new ValueKey(new object[] { Key.Empty, "B1", Key.Empty }), null).Value));
            Assert.Equal(54, Convert.ToInt32(
                             pvtTbl3[null, null].Value));

            var diagPvtTbl = new PivotTable(new [] { "B" }, new [] { "B" }, pvtData);

            for (int i = 0; i < diagPvtTbl.RowKeys.Length; i++)
            {
                for (int j = 0; j < diagPvtTbl.ColumnKeys.Length; j++)
                {
                    if (i == j)
                    {
                        Assert.NotEqual(0, (int)diagPvtTbl[i, j].Count);                        // "Incorrect diagonal pivot table"
                    }
                    else
                    {
                        Assert.Equal(0, (int)diagPvtTbl[i, j].Count);                        // "Incorrect diagonal pivot table"
                    }
                }
            }
        }
Ejemplo n.º 25
0
        public static void Run()
        {
            //Source directory
            string sourceDir = RunExamples.Get_SourceDirectory();

            //Output directory
            string outputDir = RunExamples.Get_OutputDirectory();

            // Create workbook object from source excel file
            Workbook workbook = new Workbook(sourceDir + "sampleChangingLayoutOfPivotTable.xlsx");

            // Access first worksheet
            Worksheet worksheet = workbook.Worksheets[0];

            // Access first pivot table
            PivotTable pivotTable = worksheet.PivotTables[0];

            // 1 - Show the pivot table in compact form
            pivotTable.ShowInCompactForm();

            // Refresh the pivot table
            pivotTable.RefreshData();
            pivotTable.CalculateData();

            // Save the output
            workbook.Save(outputDir + "outputChangingLayoutOfPivotTable_CompactForm.xlsx");

            // 2 - Show the pivot table in outline form
            pivotTable.ShowInOutlineForm();

            // Refresh the pivot table
            pivotTable.RefreshData();
            pivotTable.CalculateData();

            // Save the output
            workbook.Save(outputDir + "outputChangingLayoutOfPivotTable_OutlineForm.xlsx");

            // 3 - Show the pivot table in tabular form
            pivotTable.ShowInTabularForm();

            // Refresh the pivot table
            pivotTable.RefreshData();
            pivotTable.CalculateData();

            // Save the output
            workbook.Save(outputDir + "outputChangingLayoutOfPivotTable_TabularForm.xlsx");

            Console.WriteLine("ChangingLayoutOfPivotTable executed successfully.");
        }
        static void SetItemVisibilityFilter(IWorkbook workbook)
        {
            #region #ItemVisibility
            Worksheet worksheet = workbook.Worksheets["Report4"];
            workbook.Worksheets.ActiveWorksheet = worksheet;

            // Access the pivot table by its name in the collection.
            PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
            // Access items of the "Product" field.
            PivotItemCollection pivotFieldItems = pivotTable.Fields[1].Items;

            // Hide the first item in the "Product" field.
            pivotFieldItems[0].Visible = false;
            #endregion #ItemVisibility
        }
        static void RankLargestToSmallest(IWorkbook workbook)
        {
            #region #RankLargestToSmallest
            Worksheet worksheet = workbook.Worksheets["Report13"];
            workbook.Worksheets.ActiveWorksheet = worksheet;

            // Access the pivot table by its name in the collection
            PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];

            // Add the "Amount" field to the data area for the second time and assign the custom name to the field.
            PivotDataField dataField = pivotTable.DataFields.Add(pivotTable.Fields["Amount"], "Rank");
            // Display the rank of sales values for the "Customer" field, listing the largest item in the field as 1.
            dataField.ShowValuesWithCalculation(PivotShowValuesAsType.RankDescending, pivotTable.Fields["Customer"]);
            #endregion #RankLargestToSmallest
        }
Ejemplo n.º 28
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();
        }
Ejemplo n.º 29
0
        static void AddFieldToAxis(IWorkbook workbook)
        {
            #region #Add to Axis
            Worksheet sourceWorksheet = workbook.Worksheets["Data1"];
            Worksheet worksheet       = workbook.Worksheets.Add();
            workbook.Worksheets.ActiveWorksheet = worksheet;
            PivotTable pivotTable = worksheet.PivotTables.Add(sourceWorksheet["A1:D41"], worksheet["B2"]);
            pivotTable.RowFields.Add(pivotTable.Fields["Product"]);
            pivotTable.ColumnFields.Add(pivotTable.Fields["Category"]);
            PivotDataField dataField = pivotTable.DataFields.Add(pivotTable.Fields["Sales"], "Sales(Sum)");
            dataField.NumberFormat = @"_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]* "" - ""??_);_(@_)";
            pivotTable.PageFields.Add(pivotTable.Fields["Region"]);

            #endregion #Add to Axis
        }
Ejemplo n.º 30
0
        static void ColumnGrandTotals(IWorkbook workbook)
        {
            #region #ColumnGrandTotals
            Worksheet worksheet = workbook.Worksheets["Report1"];
            workbook.Worksheets.ActiveWorksheet = worksheet;

            // Access the pivot table by its name in the collection.
            PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
            // Add the "Region" field to the column axis area.
            pivotTable.ColumnFields.Add(pivotTable.Fields["Region"]);

            // Hide grand totals for columns.
            pivotTable.Layout.ShowColumnGrandTotals = false;
            #endregion #ColumnGrandTotals
        }
Ejemplo n.º 31
0
        static void RemoveBlankRows(IWorkbook workbook)
        {
            #region #RemoveBlankRows
            Worksheet worksheet = workbook.Worksheets["Report1"];
            workbook.Worksheets.ActiveWorksheet = worksheet;

            // Access the pivot table by its name in the collection.
            PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
            // Insert a blank row after each group of items.
            pivotTable.Layout.InsertBlankRows();

            // Remove a blank row after each group of items.
            pivotTable.Layout.RemoveBlankRows();
            #endregion #RemoveBlankRows
        }
Ejemplo n.º 32
0
         public static PivotTable<TRow, TColumn, TValue> ToPivot<TItem, TRow, TColumn, TValue>(
             this IEnumerable<TItem> source,
             Func<TItem, TRow> rowSelector,
             Func<TItem, TColumn> colSelector,
             Func<IEnumerable<TItem>, TValue> aggregatFunc
         )
             where TRow : IComparable, IEquatable<TRow>
             where TColumn : IComparable, IEquatable<TColumn>
         {
             var dic = source
                 .GroupBy(rowSelector)
                 .ToDictionary(x => x.Key, x => x.GroupBy(colSelector).ToDictionary(y => y.Key, y => aggregatFunc(y)));
 
             return PivotTable.Create(dic);
         }
        static void SetTop10Filter(IWorkbook workbook)
        {
            #region #Top10Filter
            Worksheet worksheet = workbook.Worksheets["Report4"];
            workbook.Worksheets.ActiveWorksheet = worksheet;

            // Access the pivot table by its name in the collection.
            PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
            // Access the "Product" field.
            PivotField field = pivotTable.Fields[1];
            // Filter the "Product" field to display two products with the lowest sales.
            PivotFilter filter = pivotTable.Filters.Add(field, pivotTable.DataFields[0], PivotFilterType.Count, 2);
            filter.Top10Type = PivotFilterTop10Type.Bottom;
            #endregion #Top10Filter
        }
Ejemplo n.º 34
0
        /// <summary>
        /// Renames the pivot reforecast columns.
        /// </summary>
        /// <param name="pivotTable">The pivot table.</param>
        /// <param name="reforecastQuarterName">Name of the reforecast quarter.</param>
        public static void RenamePivotReforecastColumns(PivotTable pivotTable, string reforecastQuarterName)
        {
            if (reforecastQuarterName == "Q0")
            {
                ChangePivotFieldCustomName(pivotTable, "MtdOriginalBudget/Reforecast", "MTD Original Budget");
                ChangePivotFieldCustomName(pivotTable, "YtdOriginalBudget/Reforecast", "YTD Original Budget");
            }
            else
            {
                ChangePivotFieldCustomName(pivotTable, "MtdOriginalBudget/Reforecast", "MTD " + reforecastQuarterName + " Reforecast");
                ChangePivotFieldCustomName(pivotTable, "YtdOriginalBudget/Reforecast", "YTD " + reforecastQuarterName + " Reforecast");

                ChangePivotFieldCustomName(pivotTable, "Current Budget / Reforecast Allocated FTE", reforecastQuarterName + " Reforecast");
                ChangePivotFieldCustomName(pivotTable, "Full Year Budget/Reforecast (HELPER)", "Full Year Average " + reforecastQuarterName + " Reforecast");
                ChangePivotFieldCustomName(pivotTable, "YTD Average Budget (HELPER)", "YTD Average " + reforecastQuarterName + " RF");
                ChangePivotFieldCustomName(pivotTable, "Current Month Act vs. Current Month Buget (HELPER)", "Current Month Act vs. Current Month " + reforecastQuarterName + " RF");
                ChangePivotFieldCustomName(pivotTable, "YTD Actual vs. YTD Budget (HELPER)", "YTD Actual vs. YTD " + reforecastQuarterName + " RF");
            }
        }
        public PivotTable GetSparseSimilarites(PivotTableEntry baseVector, PivotTable vectors, bool logarithm, bool onlyBase)
        {
            this.pbarUpdate(vectors.Count, 0, 0);

              PivotTable outMap = new PivotTable();
              int i = 0;
              foreach (PivotTableEntry b in vectors) {
            PivotTableAnalysisResult similarity = GetSparseSimilarity(baseVector, b, logarithm, onlyBase);
            similarity.Data.Add("cos_sim", similarity.prob);
            Dictionary<String, double> diffData = CalculateDiffs(baseVector, b);
            foreach (String key in diffData.Keys) {
              if (!similarity.Data.ContainsKey(key)) {
            similarity.Data.Add(key, diffData[key]);
              }
            }
            outMap.Add(similarity);
            this.pbarValueUpdate(i);
            i++;
              }
              return outMap;
        }
        private void ButtMultiAnalyzeDiffClick(object sender, RoutedEventArgs e)
        {
            try
            {
                var layerNames = new List<string>();
                var allFieldNames = new List<string>();
                foreach (string s in this.lbChangeLayers.SelectedItems)
                {
                    if (!layerNames.Contains(s))
                    {
                        layerNames.Add(s);
                    }
                }
                if (this.lbChangeLayers.SelectedItems.Count < 2)
                {
                    MessageBox.Show("Please select at least two layers");
                    return;
                }
                var colsMappingForMsgBox = new Dictionary<string, string>();
                var uniqueFieldNames = new Dictionary<string, string>();
                var multiResult = new PivotTable();
                var analyzer = new PivotTableAnalyzer(this.UpdatePBar, this.SetPBarProperties);

                for (var i = 0; i < layerNames.Count; i++)
                {
                    for (var x = i + 1; x < layerNames.Count; x++)
                    {
                        var layerA = layerNames[i];
                        var layerB = layerNames[x];
                        colsMappingForMsgBox.Add("L" + i + "_" + x + "L", layerA + " --> " + layerB);

                        if (layerA == null || layerB == null)
                        {
                            MessageBox.Show("no layers available");
                        }

                        var layers = this.GetFeatureLayersFromToc(this.GetActiveViewFromArcMap(ArcMap.Application));
                        //  getSelectedFeatureFromLayerByName(layers, "somename");
                        var flayerA = this.GetLayerByName(layers, layerA);
                        var flayerB = this.GetLayerByName(layers, layerB);
                        this.pbarChangeDet.Value = 0;

                        this.UpdateStatusLabel("Preparing " + layerA);
                        Application.DoEvents();
                        var ignoreCols = new List<string> { "OBJECTID", "SHAPE", "SHAPE_Length", "SHAPE_Area" };
                        var ptA = this.FeatureLayerToPivotTable(flayerA, "GeoHash", ignoreCols);
                        this.UpdateStatusLabel("Preparing " + layerB);
                        Application.DoEvents();
                        var ptB = this.FeatureLayerToPivotTable(flayerB, "GeoHash", ignoreCols);
                        this.UpdateStatusLabel("Calculating change");
                        Application.DoEvents();

                        var res = analyzer.DetectChange(ptA, ptB, "L" + i + "_" + x + "L", false);

                        foreach (var entry in res)
                        {
                            if (!entry.Data.ContainsKey("layerAIndex"))
                            {
                                entry.Data.Add("layerAIndex", i);
                            }
                            if (!entry.Data.ContainsKey("layerBIndex"))
                            {
                                entry.Data.Add("layerBIndex", i);
                            }

                            entry.Label = "L" + i + "_" + x + "L";

                            foreach (var name in entry.Data.Keys)
                            {
                                if (!allFieldNames.Contains(name))
                                {
                                    allFieldNames.Add(name);
                                }

                                if (!uniqueFieldNames.ContainsKey(name))
                                {
                                    uniqueFieldNames.Add(name, name);
                                }
                            }
                            break;
                        }
                        multiResult.AddRange(res);
                    }
                }
                var flat = analyzer.flattenAndSimplify(multiResult, "percent_change");
                uniqueFieldNames = new Dictionary<string, string>();
                foreach (var pte in flat)
                {
                    var vals = new List<double>();
                    foreach (var field in pte.Data.Keys)
                    {
                        vals.Add(pte.Data[field]);
                    }
                    pte.Data.Add("stdev_", analyzer.stdev(vals));
                    pte.Data.Add("avg_", analyzer.avg(vals));
                    pte.Data.Add("min_", analyzer.min(vals));
                    pte.Data.Add("max_", analyzer.max(vals));

                    foreach (var field in pte.Data.Keys)
                    {
                        if (!uniqueFieldNames.ContainsKey(field))
                        {
                            uniqueFieldNames.Add(field, field);
                        }
                    }
                }
                var ws = Jarvis.OpenWorkspace(Settings.Default.geoDatabase);
                var fcName = "multi_change_" + DateTime.Now.Millisecond;
                //  uniqueFieldNames.Add("context_str", "context_str");
                var featureClass = Jarvis.CreateStandaloneFeatureClass(ws, fcName, uniqueFieldNames, false, 0);

                var insertCur = featureClass.Insert(true);
                this.UpdateStatusLabel("Loading Output Feature Class");
                Application.DoEvents();

                this.InsertPivoTableRowsToFeatureClass(featureClass, flat, uniqueFieldNames);
                this.AddLayerToArcMap(fcName);
                this.pbarChangeDet.Value = 0;

                this.lblPbarStatus.Content = "Done";
                var message = "FYI:\n";
                foreach (var colName in colsMappingForMsgBox.Keys)
                {
                    message += "Column " + colName + " represents change for " + colsMappingForMsgBox[colName] + "\n";
                }
                message += "\n*This is required because of field length limitations";
                MessageBox.Show(message);

                Application.DoEvents();
                // now I need to create a feature class and feature layer from this object
            }
            catch (Exception ex)
            {
                MessageBox.Show("An error occured calculating change" + ex.Message);
            }
        }
        public PivotTable FeaturesToPivotTable(
            List<IFeature> layers,
            string rowKeyColName,
            List<string> columnsToIgnore)
        {
            SendAnInt sai = this.UpdatePBar;
            this.pbarChangeDet.Minimum = 0;
            this.pbarChangeDet.Maximum = layers.Count;
            this.pbarChangeDet.Value = 0;

            if (columnsToIgnore == null)
            {
                columnsToIgnore = new List<string>();
            }
            if (!columnsToIgnore.Contains("OBJECTID"))
            {
                columnsToIgnore.Add("OBJECTID");
            }
            var pt = new PivotTable();

            // IFeature feature = featureCursor.NextFeature();
            // loop through the returned features and get the value for the field
            var x = 0;
            foreach (var feature in layers)
            {
                var entry = new PivotTableEntry();
                //do something with each feature(ie update geometry or attribute)
                //  Console.WriteLine("The {0} field contains a value of {1}", nameOfField, feature.get_Value(fieldIndexValue));
                this.pbarChangeDet.Value++;
                sai.Invoke(x);
                x++;
                for (var i = 0; i < feature.Fields.FieldCount; i++)
                {
                    if (this.pbarChangeDet.Value == this.pbarChangeDet.Maximum)
                    {
                        this.pbarChangeDet.Maximum = this.pbarChangeDet.Maximum + 10;
                    }

                    var fname = feature.Fields.get_Field(i).Name;
                    var val = feature.get_Value(i).ToString();

                    if (columnsToIgnore.Contains(fname))
                    {
                        continue;
                    }

                    if (fname.Equals(rowKeyColName))
                    {
                        entry.RowKey = Convert.ToString(val);
                    }
                    else
                    {
                        try
                        {
                            entry.Data.Add(fname, int.Parse(val));
                        }
                        catch
                        {
                        }
                    }
                }
                pt.Add(entry);
            }
            sai.Invoke(Convert.ToInt32(this.pbarChangeDet.Maximum));
            return pt;
        }
        public PivotTable flattenAndSimplify(PivotTable withMultipleCompares, String pivCol)
        {
            PivotTable output = new PivotTable();
              Dictionary<String, Dictionary<String, Double>> outMap = new Dictionary<String, Dictionary<String, Double>>();

              foreach (PivotTableEntry pte in withMultipleCompares) {
            if (outMap.ContainsKey(pte.RowKey)) {
              if (pte.Data.ContainsKey(pivCol)) {
            outMap[pte.RowKey].Add(pte.Label, pte.Data[pivCol]);
              }
             else {
            outMap[pte.RowKey].Add(pte.Label, 0d);
             }
            }
            else {
              Dictionary<String, double> newMap = new Dictionary<string, double>();
              if (pte.Data.ContainsKey(pivCol)) {
            newMap.Add(pte.Label, pte.Data[pivCol]);
            outMap.Add(pte.RowKey, newMap);
              }
             // else {
             //   newMap.Add(pte.Label, 0d);
               // outMap.Add(pte.RowKey, newMap);
              //}
            }
              }
              foreach (String key in outMap.Keys) {
            output.Add(new PivotTableEntry() { RowKey = key, Data = outMap[key] });

              }

              return output;
        }
Ejemplo n.º 39
0
 private static void BuildTotal(PivotTable pivotTable, StringBuilder builder)
 {
     builder.Append(@"<tr class=""total"">");
     builder.AppendFormat(@"<td class=""group"">{0}</td>", pivotTable.TotalName);
     BuildPrefixCells(0, pivotTable.GroupTitles.Count(), builder);
     BuildValues(pivotTable.FormatValues, builder);
     builder.Append("</tr>");
 }
Ejemplo n.º 40
0
        public void Export3ItemGroupsToExcel(Stream stream, int year)
        {
            using (var package = new ExcelPackage(stream))
            {
                var query = from item in _dataContext.ReportDatas
                            where item.ChannelName.ToLower() == "direct"
                            && (item.ItemGroupName.ToLower() == "prepenem" || item.ItemGroupName.ToLower() == "maxpenepm" || item.ItemGroupName.ToLower() == "combilipid")
                            && (item.Year == year || item.Year == year - 1)
                            select item;

                DataTable table = LinqExtension.LinqResultToDataTable(query);

                var pivotData = new PivotData(new[] { "ChannelName", "ItemGroupName", "ClientName", "Year", "Month" },
                        new SumAggregatorFactory("CifAmount")
                    );

                pivotData.ProcessData(new DataTableReader(table));

                package.Compression = CompressionLevel.Default;
                var pivot = package.Workbook.Worksheets.Add("PivotTable");
                var data = package.Workbook.Worksheets.Add("Data");

                var pivotTable = new PivotTable(
                        new[] { "ItemGroupName", "ClientName" }, //rows
                        new[] { "Year", "Month" }, // columns
                        pivotData
                    );

                var excelPivotTableWriter = new ExcelPivotTableWriter(pivot, data);

                excelPivotTableWriter.Write(pivotTable);

                //pkg.Workbook.Worksheets.Delete(wsData);

                package.Save();
            }
        }
Ejemplo n.º 41
0
        public PivotTable FeatureLayerToPivotTable(
            IFeatureLayer layer,
            string rowKeyColName,
            List<string> columnsToIgnore)
        {
            SendAnInt sai = this.UpdatePBar;
            this.analysisProgressBar.Minimum = 0;
            this.analysisProgressBar.Maximum = layer.FeatureClass.FeatureCount(null);
            this.analysisProgressBar.Value = 0;

            if (columnsToIgnore == null)
            {
                columnsToIgnore = new List<string>();
            }
            if (!columnsToIgnore.Contains("OBJECTID"))
            {
                columnsToIgnore.Add("OBJECTID");
            }
            var pt = new PivotTable();
            if (PivotTableCache.Cache.ContainsKey(layer.Name))
            {
                pt = PivotTableCache.Cache[layer.Name];
                return pt;
            }

            var featureCursor = layer.FeatureClass.Search(null, false);
            var feature = featureCursor.NextFeature();
            // loop through the returned features and get the value for the field
            var x = 0;
            while (feature != null)
            {
                var entry = new PivotTableEntry();
                //do something with each feature(ie update geometry or attribute)
                this.analysisProgressBar.Value++;
                sai.Invoke(x);
                x++;
                for (var i = 0; i < feature.Fields.FieldCount; i++)
                {
                    if (this.analysisProgressBar.Value == this.analysisProgressBar.Maximum)
                    {
                        this.analysisProgressBar.Maximum = this.analysisProgressBar.Maximum + 10;
                    }

                    var f = feature.Fields.get_Field(i).Name;
                    var val = feature.get_Value(i).ToString();

                    if (columnsToIgnore.Contains(f))
                    {
                        continue;
                    }

                    if (f.Equals(rowKeyColName))
                    {
                        entry.RowKey = Convert.ToString(val);
                    }
                    else
                    {
                        try
                        {
                            entry.Data.Add(f, int.Parse(val));
                        }
                        catch
                        {
                        }
                    }
                }
                pt.Add(entry);
                feature = featureCursor.NextFeature();
            }

            sai.Invoke(Convert.ToInt32(this.analysisProgressBar.Maximum));
            //add to the cache
            if (!PivotTableCache.Cache.ContainsKey(layer.Name))
            {
                PivotTableCache.Cache.Add(layer.Name, pt);
            }
            return pt;
        }
 public void Dummy12(PivotTable p1, PivotTable p2)
 {
     throw new NotImplementedException();
 }
Ejemplo n.º 43
0
 private static string BuildTable(PivotTable pivotTable)
 {
     var builder = new StringBuilder();
     builder.Append(@"<table border=""1"">");
     BuildHeader(pivotTable, builder);
     BuildBody(pivotTable, builder);
     builder.Append("</table>");
     return builder.ToString();
 }
 /// <summary>
 /// Converts a csv file to a pivot table.
 /// </summary>
 /// <param name="file"></param>
 /// <param name="rowkeyColumnName"></param>
 /// <returns></returns>
 public PivotTable FileToPivotTable(String file, string rowkeyColumnName)
 {
     string[] fields = null;
       PivotTable vectors = new PivotTable();
       using (var reader = System.IO.File.OpenText(file)) {
     String line = null;
     int i = 0;
     while ((line = reader.ReadLine()) != null) {
       if (i == 0) {
     fields = Regex.Split(line, ",");
     i++;
     continue;
       }
       Dictionary<string, double> vector = new Dictionary<string, double>();
       string[] vals = Regex.Split(line, ",");
       String geohash = "";
       for (int x = 0; x < fields.Length; x++) {
     //  Console.WriteLine(fields[x]);
     string key = fields[x];
     String valStr = vals[x];
     if (Regex.IsMatch(key, "(SHAPE_Length|SHAPE_Area)", RegexOptions.IgnoreCase)) { continue; }
     if (Regex.IsMatch(key, rowkeyColumnName, RegexOptions.IgnoreCase)) {
       geohash = vals[x];
       continue;
     }
     double val = Double.Parse(vals[x]);
     vector.Add(key, val);
       }
       vectors.Add(new PivotTableEntry() { Data = new Dictionary<string, double>(vector), RowKey = geohash });
       //Console.WriteLine(line);
       i++;
     }
       }
       return vectors;
 }
        //@wurfless :^)
        public Dictionary<String, Dictionary<String, Double>> GetSimilarityGraph(PivotTable signature)
        {
            Dictionary<String, Dictionary<String, Double>> outputGraph = new Dictionary<string, Dictionary<string, double>>();
              if (signature.Count < 2) { return outputGraph; }

              for (int i = 0; i < signature.Count; i++) {
            ///tricky... creates an acyclic undirected, non repeating graph
            for (int x = i +1; x < signature.Count; x++) {
              PivotTableEntry a = signature[i];
              PivotTableEntry b = signature[x];
              double sim = GetSparseSimilarity(a, b, true, false).prob;
              if (outputGraph.ContainsKey(a.RowKey)) {
            outputGraph[a.RowKey].Add(b.RowKey, sim);
              }
              else {
            Dictionary<String, double> newInnerMap = new Dictionary<string, double>();
            newInnerMap.Add(b.RowKey, sim);
            outputGraph.Add(a.RowKey, newInnerMap);
              }
            }
              }

              return outputGraph;
        }
        public PivotTable GenerateAverageVector(PivotTable tableWithMoreThanOneRow)
        {
            if (tableWithMoreThanOneRow.Count < 2) {
            throw new Exception("Must Have more than one row");
              }

              Dictionary<String, List<Double>> data = new Dictionary<String, List<Double>>();
              //consolidate all the values for each column of each entry in the pivot table
              foreach (PivotTableEntry entry in tableWithMoreThanOneRow) {
            foreach (String key in entry.Data.Keys) {
              if (data.ContainsKey(key)) {
            data[key].Add(entry.Data[key]);
              }
              else {
            List<Double> newList = new List<Double>();
            newList.Add(entry.Data[key]);
            data.Add(key,newList);
              }
            }
              }
              //now average each and produce a new data dictionary
              Dictionary<String, Double> averages = new Dictionary<String, Double>();
              foreach (String key in data.Keys) {
            double avg = 0d;
            foreach (Double val in data[key]) {
              avg += val;
            }
            avg = avg / data[key].Count;
            averages.Add(key, avg);
              }
              PivotTableEntry newEntry = new PivotTableEntry() {
            Data = averages,
            Context = tableWithMoreThanOneRow[0].Context,
            RowKey = tableWithMoreThanOneRow[0].RowKey
              };

              PivotTable pt = new PivotTable();
              pt.Add(newEntry);
              return pt;
        }
        private void InsertPivoTableRowsToFeatureClass(
            IFeatureClass featureClass,
            PivotTable ptable,
            Dictionary<string, string> uniqueFieldNames)
        {
            // get the polygon of the geohash
            var insertCur = featureClass.Insert(true);
            this.pbarChangeDet.Maximum = ptable.Count;
            this.pbarChangeDet.Minimum = 0;
            this.pbarChangeDet.Value = 0;
            var fieldsInFc = new List<string>();
            for (var y = 0; y < featureClass.Fields.FieldCount; y++)
            {
                fieldsInFc.Add(featureClass.Fields.Field[y].Name);
            }

            var i = 0;
            foreach (var entry in ptable)
            {
                i++;
                this.UpdatePBar(i);

                var poly = this.GetGeoHashPoly(entry.RowKey);
                var buffer = featureClass.CreateFeatureBuffer();

                // Setup the features geometry.
                buffer.Shape = (IGeometry)poly;

                buffer.Value[featureClass.FindField("Geohash")] = entry.RowKey;
                foreach (var val in entry.Data.Keys)
                {
                    if (uniqueFieldNames.ContainsKey(val))
                    {
                        try
                        {
                            if (val.EndsWith("_str"))
                            {
                                var fieldName = "DG_" + uniqueFieldNames[val];
                                var field = featureClass.FindField(fieldName);
                                var value = entry.Label;

                                buffer.Value[field] = value;
                            }
                            else
                            {
                                var fieldName = "DG_" + uniqueFieldNames[val];
                                var field = featureClass.FindField(fieldName);
                                var value = entry.Data[val];

                                buffer.Value[field] = value;
                            }
                        }
                        catch (Exception error)
                        {
                            Jarvis.Logger.Error(error);
                        }
                    }
                }
                // Feature has been created so add to the feature class.
                insertCur.InsertFeature(buffer);
            }
            insertCur.Flush();
        }
Ejemplo n.º 48
0
 public static MvcHtmlString PivotTable(this HtmlHelper html, PivotTable pivotTable, string title = "")
 {
     return new MvcHtmlString(pivotTable.RenderHtmlTable(title));
 }
        /// <summary>
        /// Compares two pivot tables. Do not pass in columns that don't make sense to compare. This method encapsulates a cosine similarity
        /// calculation on geohash cell pairs, and subsequently, each pair also calculates a diff between each col pair as a quasi percentage diff.
        /// 
        /// </summary>
        /// <param name="timeA"></param>
        /// <param name="timeB"></param>A PivotTable that is full
        /// <returns></returns>
        public PivotTable DetectChange(PivotTable ptA, PivotTable ptB, string label, bool diffs)
        {
            PivotTable outList = new PivotTable();
              //each dictionary below is a geohash agg layer, key=aGeoHashPrefix,value=anAggVectorOfThatBox
              Dictionary<string, PivotTableEntry> a = new Dictionary<string, PivotTableEntry>();
              Dictionary<string, PivotTableEntry> b = new Dictionary<string, PivotTableEntry>();
              HashSet<string> hashset = new HashSet<string>();
              //union the key sets into hashset variable
              foreach (PivotTableEntry av in ptA) {
            a.Add(av.RowKey, av);
            hashset.Add(av.RowKey);
              }
              foreach (PivotTableEntry av in ptB) {
            b.Add(av.RowKey, av);
            hashset.Add(av.RowKey);
              }

              this.pbarUpdate.Invoke(hashset.Count, 0, 0);
              //now hashset variable is a unique list of strings
              Dictionary<string,double> empty = new Dictionary<string, double>();
              foreach(String s in hashset){
            empty.Add(s, 0d);
              }
             int x =0;
              foreach (string geohash in hashset) {
            this.pbarValueUpdate.Invoke(x);
            x++;
            PivotTableEntry ava = null;
            PivotTableEntry avb = null;
            if (a.ContainsKey(geohash)) { ava = a[geohash]; }
            if (b.ContainsKey(geohash)) { avb = b[geohash]; }
            if (ava == null || avb == null) {
              outList.Add(new PivotTableAnalysisResult() { RowKey = geohash, prob = 0d, Data = empty, Label = label });
            }
            else {
              PivotTableAnalysisResult p = GetSparseSimilarity(ava, avb, true, false);
              p.RowKey = geohash;
              p.Label = label;
              if (diffs) {
              p.Data = CalculateDiffs(ava, avb);
              }
              else {
            p.Data = new Dictionary<string, double>();
              }
              p.Data.Add("cos_sim", p.prob);
              p.Data.Add("percent_change", Math.Abs(p.prob - 1) * 100);
              outList.Add(p);
            }
              }
              return outList;
        }