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