internal override IEnumerable <Int32> Match(XLWorkbook.PivotTableInfo pti, IXLPivotTable pt)
{
return(new Int32[]
{
pt.Values.IndexOf(Value.ToString())
});
}
public XLPivotField(IXLPivotTable pivotTable, string sourceName)
{
this._pivotTable = pivotTable;
SourceName = sourceName;
Subtotals = new List <XLSubtotalFunction>();
SelectedValues = new List <Object>();
SortType = XLPivotSortType.Default;
SetExcelDefaults();
StyleFormats = new XLPivotFieldStyleFormats(this);
}
private void dataTableCreate(Expression <Func <dbMain_9, bool> > value, string type)
{
var data = dc.dbMain_9.Where(value);
DataTable dataTableSource = LINQToDataTable(data);
//DataSet dataSet = ToDataTable(dc, data);
//DataTable dataTableSource = dataSet.Tables[0];
if (dc.DatabaseExists())
{
DataGrid.DataContext = null;
DataGrid.DataContext = data.ToList();
}
if (type == "save")
{
XLWorkbook workbook = new XLWorkbook();
var sourceTableSheet = workbook.Worksheets.Add("DataSource");
var SourceTable = sourceTableSheet.Cell(1, 1).InsertTable(dataTableSource, "DataSource", true);
//IXLTable x;
//var sTable = x.AppendData(dataTableSource);
var pivotTableSheet = workbook.Worksheets.Add("PivotTable");
IXLPivotTable PivotTable = pivotTableSheet.PivotTables.Add("PivotTable", pivotTableSheet.Cell(1, 1), SourceTable.AsRange());
PivotTable.RowLabels.Add("brand");
PivotTable.RowLabels.Add("brandOwner");
PivotTable.RowLabels.Add("secondaryBrand");
PivotTable.RowLabels.Add("secondaryBrandOwner");
PivotTable.RowLabels.Add("product");
PivotTable.RowLabels.Add("industry");
PivotTable.RowLabels.Add("subIndustry");
PivotTable.RowLabels.Add("subIndustry2");
PivotTable.RowLabels.Add("subIndustry3");
PivotTable.RowLabels.Add("subIndustry4");
PivotTable.RowLabels.Add("publisher");
PivotTable.RowLabels.Add("website");
PivotTable.RowLabels.Add("subWebsite");
PivotTable.RowLabels.Add("contentType");
PivotTable.RowLabels.Add("adType");
PivotTable.RowLabels.Add("adTypeGroup");
PivotTable.RowLabels.Add("isAutopromotion");
PivotTable.RowLabels.Add("adUrl");
PivotTable.RowLabels.Add("adSize");
PivotTable.RowLabels.Add("impressionType");
PivotTable.RowLabels.Add("xYear");
PivotTable.RowLabels.Add("xMonth");
PivotTable.RowLabels.Add("xWeek");
PivotTable.RowLabels.Add("xDate");
PivotTable.Values.Add("impressions");
PivotTable.Values.Add("cost");
PivotTable.Values.Add("cost_gross");
string path = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "/" + FileName + ".xlsx";
workbook.Worksheet("DataSource").Delete();
workbook.SaveAs(path);
}
}
/// <summary>
/// ''' can't touch already created pivot-table
/// ''' https://github.com/ClosedXML/ClosedXML/pull/124
/// ''' </summary>
/// ''' <param name="sheetSource"></param>
/// ''' <param name="sheetDest"></param>
/// ''' <param name="rows"></param>
/// ''' <param name="cols"></param>
/// ''' <returns></returns>
public ExcelHelper DoPivotTable(string sheetSource, string sheetDest, string[] rows = null, string[] cols = null, string[] datafields = null)
{
try
{
IXLWorksheet worksheetSource = null;
IXLTable sourceTable = null;
if (_workbook.Worksheets.Count < 1)
{
_workbook = new XLWorkbook(this._inputFile.FullName);
}
worksheetSource = _workbook.Worksheets.Worksheet(sheetSource);
sourceTable = worksheetSource.Table(0);
// TODO PRIMA CONTROLLARE SE ESISTE E nel caso cancella
this.RemoveSheet(sheetDest); // <<TODO invece di remove che in questo caso crea un errore cercare se è possibile chiamare REFRESH
IXLWorksheet pivotTableSheet = _workbook.Worksheets.Add(sheetDest);
IXLPivotTable pivoTable = pivotTableSheet.PivotTables.Add("PivotTable", pivotTableSheet.Cell(1, 1), sourceTable.AsRange());
foreach (string r in rows)
{
if (r.Trim() != "")
{
pivoTable.RowLabels.Add(r);
}
}
foreach (string c in cols)
{
if (c.Trim() != "")
{
pivoTable.ColumnLabels.Add(c);
}
}
foreach (string d in datafields)
{
if (d.Trim() != "")
{
pivoTable.Values.Add(d);
}
}
}
// i filtri non sono al momento supportati https://github.com/ClosedXML/ClosedXML/issues/218
catch (Exception ex)
{
//SD.Log(ex.Message, SD.LogLevel.Error, ex.StackTrace);
return(this);
}
return(this);
}
internal override IEnumerable <Int32> Match(XLWorkbook.PivotTableInfo pti, IXLPivotTable pt)
{
var values = pti.Fields[PivotField.SourceName].DistinctValues.ToList();
if (predicate == null)
{
return new Int32[] { }
}
;
return(values.Select((Value, Index) => new { Value, Index })
.Where(v => predicate.Invoke(v.Value))
.Select(v => v.Index)
.ToList());
}
}
public void Add(String name, IXLPivotTable pivotTable)
{
_pivotTables.Add(name, (XLPivotTable)pivotTable);
}
// Generates content of pivotTablePart
private static void GeneratePivotTablePartContent(PivotTablePart pivotTablePart1, IXLPivotTable pt)
{
var pivotTableDefinition = new PivotTableDefinition
{
Name = pt.Name,
CacheId = 0U,
DataCaption = "Values",
MergeItem = GetBooleanValue(pt.MergeAndCenterWithLabels, true),
Indent = Convert.ToUInt32(pt.RowLabelIndent),
PageOverThenDown = (pt.FilterAreaOrder == XLFilterAreaOrder.OverThenDown),
PageWrap = Convert.ToUInt32(pt.FilterFieldsPageWrap),
ShowError = String.IsNullOrEmpty(pt.ErrorValueReplacement),
UseAutoFormatting = GetBooleanValue(pt.AutofitColumns, true),
PreserveFormatting = GetBooleanValue(pt.PreserveCellFormatting, true),
RowGrandTotals = GetBooleanValue(pt.ShowGrandTotalsRows, true),
ColumnGrandTotals = GetBooleanValue(pt.ShowGrandTotalsColumns, true),
SubtotalHiddenItems = GetBooleanValue(pt.FilteredItemsInSubtotals, true),
MultipleFieldFilters = GetBooleanValue(pt.AllowMultipleFilters, true),
CustomListSort = GetBooleanValue(pt.UseCustomListsForSorting, true),
ShowDrill = GetBooleanValue(pt.ShowExpandCollapseButtons, true),
ShowDataTips = GetBooleanValue(pt.ShowContextualTooltips, true),
ShowMemberPropertyTips = GetBooleanValue(pt.ShowPropertiesInTooltips, true),
ShowHeaders = GetBooleanValue(pt.DisplayCaptionsAndDropdowns, true),
GridDropZones = GetBooleanValue(pt.ClassicPivotTableLayout, true),
ShowEmptyRow = GetBooleanValue(pt.ShowEmptyItemsOnRows, true),
ShowEmptyColumn = GetBooleanValue(pt.ShowEmptyItemsOnColumns, true),
ShowItems = GetBooleanValue(pt.DisplayItemLabels, true),
FieldListSortAscending = GetBooleanValue(pt.SortFieldsAtoZ, true),
PrintDrill = GetBooleanValue(pt.PrintExpandCollapsedButtons, true),
ItemPrintTitles = GetBooleanValue(pt.RepeatRowLabels, true),
FieldPrintTitles = GetBooleanValue(pt.PrintTitles, true),
EnableDrill = GetBooleanValue(pt.EnableShowDetails, true)
};
if (pt.EmptyCellReplacement != null)
{
pivotTableDefinition.ShowMissing = true;
pivotTableDefinition.MissingCaption = pt.EmptyCellReplacement;
}
else
{
pivotTableDefinition.ShowMissing = false;
}
if (pt.ErrorValueReplacement != null)
{
pivotTableDefinition.ShowError = true;
pivotTableDefinition.ErrorCaption = pt.ErrorValueReplacement;
}
else
{
pivotTableDefinition.ShowError = false;
}
var location = new Location
{
Reference = pt.TargetCell.Address.ToString(),
FirstHeaderRow = 1U,
FirstDataRow = 1U,
FirstDataColumn = 1U
};
var rowFields = new RowFields();
var columnFields = new ColumnFields();
var rowItems = new RowItems();
var columnItems = new ColumnItems();
var pageFields = new PageFields {Count = (uint)pt.ReportFilters.Count()};
var pivotFields = new PivotFields {Count = Convert.ToUInt32(pt.SourceRange.ColumnCount())};
foreach (var xlpf in pt.Fields)
{
var pf = new PivotField {ShowAll = false, Name = xlpf.CustomName};
if (pt.RowLabels.FirstOrDefault(p => p.SourceName == xlpf.SourceName) != null)
{
pf.Axis = PivotTableAxisValues.AxisRow;
var f = new Field {Index = pt.Fields.IndexOf(xlpf)};
rowFields.AppendChild(f);
for (var i = 0; i < xlpf.SharedStrings.Count; i++)
{
var rowItem = new RowItem();
rowItem.AppendChild(new MemberPropertyIndex {Val = i});
rowItems.AppendChild(rowItem);
}
var rowItemTotal = new RowItem {ItemType = ItemValues.Grand};
rowItemTotal.AppendChild(new MemberPropertyIndex());
rowItems.AppendChild(rowItemTotal);
}
else if (pt.ColumnLabels.FirstOrDefault(p => p.SourceName == xlpf.SourceName) != null)
{
pf.Axis = PivotTableAxisValues.AxisColumn;
var f = new Field {Index = pt.Fields.IndexOf(xlpf)};
columnFields.AppendChild(f);
for (var i = 0; i < xlpf.SharedStrings.Count; i++)
{
var rowItem = new RowItem();
rowItem.AppendChild(new MemberPropertyIndex {Val = i});
columnItems.AppendChild(rowItem);
}
var rowItemTotal = new RowItem {ItemType = ItemValues.Grand};
rowItemTotal.AppendChild(new MemberPropertyIndex());
columnItems.AppendChild(rowItemTotal);
}
else if (pt.ReportFilters.FirstOrDefault(p => p.SourceName == xlpf.SourceName) != null)
{
location.ColumnsPerPage = 1;
location.RowPageCount = 1;
pf.Axis = PivotTableAxisValues.AxisPage;
pageFields.AppendChild(new PageField {Hierarchy = -1, Field = pt.Fields.IndexOf(xlpf)});
}
else if (pt.Values.FirstOrDefault(p => p.CustomName == xlpf.SourceName) != null)
{
pf.DataField = true;
}
var fieldItems = new Items();
if (xlpf.SharedStrings.Count > 0)
{
for (uint i = 0; i < xlpf.SharedStrings.Count; i++)
{
fieldItems.AppendChild(new Item {Index = i});
}
}
if (xlpf.Subtotals.Count > 0)
{
foreach (var subtotal in xlpf.Subtotals)
{
var itemSubtotal = new Item();
switch (subtotal)
{
case XLSubtotalFunction.Average:
pf.AverageSubTotal = true;
itemSubtotal.ItemType = ItemValues.Average;
break;
case XLSubtotalFunction.Count:
pf.CountASubtotal = true;
itemSubtotal.ItemType = ItemValues.CountA;
break;
case XLSubtotalFunction.CountNumbers:
pf.CountSubtotal = true;
itemSubtotal.ItemType = ItemValues.Count;
break;
case XLSubtotalFunction.Maximum:
pf.MaxSubtotal = true;
itemSubtotal.ItemType = ItemValues.Maximum;
break;
case XLSubtotalFunction.Minimum:
pf.MinSubtotal = true;
itemSubtotal.ItemType = ItemValues.Minimum;
break;
case XLSubtotalFunction.PopulationStandardDeviation:
pf.ApplyStandardDeviationPInSubtotal = true;
itemSubtotal.ItemType = ItemValues.StandardDeviationP;
break;
case XLSubtotalFunction.PopulationVariance:
pf.ApplyVariancePInSubtotal = true;
itemSubtotal.ItemType = ItemValues.VarianceP;
break;
case XLSubtotalFunction.Product:
pf.ApplyProductInSubtotal = true;
itemSubtotal.ItemType = ItemValues.Product;
break;
case XLSubtotalFunction.StandardDeviation:
pf.ApplyStandardDeviationInSubtotal = true;
itemSubtotal.ItemType = ItemValues.StandardDeviation;
break;
case XLSubtotalFunction.Sum:
pf.SumSubtotal = true;
itemSubtotal.ItemType = ItemValues.Sum;
break;
case XLSubtotalFunction.Variance:
pf.ApplyVarianceInSubtotal = true;
itemSubtotal.ItemType = ItemValues.Variance;
break;
}
fieldItems.AppendChild(itemSubtotal);
}
}
else
{
fieldItems.AppendChild(new Item {ItemType = ItemValues.Default});
}
pf.AppendChild(fieldItems);
pivotFields.AppendChild(pf);
}
pivotTableDefinition.AppendChild(location);
pivotTableDefinition.AppendChild(pivotFields);
if (pt.RowLabels.Any())
{
pivotTableDefinition.AppendChild(rowFields);
}
else
{
rowItems.AppendChild(new RowItem());
}
pivotTableDefinition.AppendChild(rowItems);
if (!pt.ColumnLabels.Any())
{
columnItems.AppendChild(new RowItem());
pivotTableDefinition.AppendChild(columnItems);
}
else
{
pivotTableDefinition.AppendChild(columnFields);
pivotTableDefinition.AppendChild(columnItems);
}
if (pt.ReportFilters.Any())
{
pivotTableDefinition.AppendChild(pageFields);
}
var dataFields = new DataFields();
foreach (var value in pt.Values)
{
var sourceColumn =
pt.SourceRange.Columns().FirstOrDefault(c => c.Cell(1).Value.ToString() == value.SourceName);
if (sourceColumn == null) continue;
var df = new DataField
{
Name = value.SourceName,
Field = (UInt32)sourceColumn.ColumnNumber() - 1,
Subtotal = value.SummaryFormula.ToOpenXml(),
ShowDataAs = value.Calculation.ToOpenXml(),
NumberFormatId = (UInt32)value.NumberFormat.NumberFormatId
};
if (!String.IsNullOrEmpty(value.BaseField))
{
var baseField =
pt.SourceRange.Columns().FirstOrDefault(c => c.Cell(1).Value.ToString() == value.BaseField);
if (baseField != null)
df.BaseField = baseField.ColumnNumber() - 1;
}
else
{
df.BaseField = 0;
}
if (value.CalculationItem == XLPivotCalculationItem.Previous)
df.BaseItem = 1048828U;
else if (value.CalculationItem == XLPivotCalculationItem.Next)
df.BaseItem = 1048829U;
else
df.BaseItem = 0U;
dataFields.AppendChild(df);
}
pivotTableDefinition.AppendChild(dataFields);
pivotTableDefinition.AppendChild(new PivotTableStyle
{
Name = Enum.GetName(typeof (XLPivotTableTheme), pt.Theme),
ShowRowHeaders = pt.ShowRowHeaders,
ShowColumnHeaders = pt.ShowColumnHeaders,
ShowRowStripes = pt.ShowRowStripes,
ShowColumnStripes = pt.ShowColumnStripes
});
#region Excel 2010 Features
var pivotTableDefinitionExtensionList = new PivotTableDefinitionExtensionList();
var pivotTableDefinitionExtension = new PivotTableDefinitionExtension
{Uri = "{962EF5D1-5CA2-4c93-8EF4-DBF5C05439D2}"};
pivotTableDefinitionExtension.AddNamespaceDeclaration("x14",
"http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
var pivotTableDefinition2 = new DocumentFormat.OpenXml.Office2010.Excel.PivotTableDefinition
{EnableEdit = pt.EnableCellEditing, HideValuesRow = !pt.ShowValuesRow};
pivotTableDefinition2.AddNamespaceDeclaration("xm", "http://schemas.microsoft.com/office/excel/2006/main");
pivotTableDefinitionExtension.AppendChild(pivotTableDefinition2);
pivotTableDefinitionExtensionList.AppendChild(pivotTableDefinitionExtension);
pivotTableDefinition.AppendChild(pivotTableDefinitionExtensionList);
#endregion
pivotTablePart1.PivotTableDefinition = pivotTableDefinition;
}
// Generates content of pivotTableCacheDefinitionPart
private static void GeneratePivotTableCacheDefinitionPartContent(
PivotTableCacheDefinitionPart pivotTableCacheDefinitionPart, IXLPivotTable pt)
{
var source = pt.SourceRange;
var pivotCacheDefinition = new PivotCacheDefinition
{
Id = "rId1",
SaveData = pt.SaveSourceData,
RefreshOnLoad = true //pt.RefreshDataOnOpen
};
if (pt.ItemsToRetainPerField == XLItemsToRetain.None)
pivotCacheDefinition.MissingItemsLimit = 0U;
else if (pt.ItemsToRetainPerField == XLItemsToRetain.Max)
pivotCacheDefinition.MissingItemsLimit = XLHelper.MaxRowNumber;
pivotCacheDefinition.AddNamespaceDeclaration("r",
"http://schemas.openxmlformats.org/officeDocument/2006/relationships");
var cacheSource = new CacheSource {Type = SourceValues.Worksheet};
cacheSource.AppendChild(new WorksheetSource {Name = source.ToString()});
var cacheFields = new CacheFields();
foreach (var c in source.Columns())
{
var columnNumber = c.ColumnNumber();
var columnName = c.FirstCell().Value.ToString();
var xlpf = pt.Fields.Add(columnName);
var field =
pt.RowLabels.Union(pt.ColumnLabels).Union(pt.ReportFilters).FirstOrDefault(f => f.SourceName == columnName);
if (field != null)
{
xlpf.CustomName = field.CustomName;
xlpf.Subtotals.AddRange(field.Subtotals);
}
var sharedItems = new SharedItems();
var onlyNumbers =
!source.Cells().Any(
cell =>
cell.Address.ColumnNumber == columnNumber &&
cell.Address.RowNumber > source.FirstRow().RowNumber() && cell.DataType != XLCellValues.Number);
if (onlyNumbers)
{
sharedItems = new SharedItems
{ContainsSemiMixedTypes = false, ContainsString = false, ContainsNumber = true};
}
else
{
foreach (var cellValue in source.Cells().Where(cell =>
cell.Address.ColumnNumber == columnNumber &&
cell.Address.RowNumber >
source.FirstRow().RowNumber()).Select(
cell => cell.Value.ToString())
.Where(cellValue => !xlpf.SharedStrings.Contains(cellValue)))
{
xlpf.SharedStrings.Add(cellValue);
}
foreach (var li in xlpf.SharedStrings)
{
sharedItems.AppendChild(new StringItem {Val = li});
}
}
var cacheField = new CacheField {Name = xlpf.SourceName};
cacheField.AppendChild(sharedItems);
cacheFields.AppendChild(cacheField);
}
pivotCacheDefinition.AppendChild(cacheSource);
pivotCacheDefinition.AppendChild(cacheFields);
pivotTableCacheDefinitionPart.PivotCacheDefinition = pivotCacheDefinition;
var pivotTableCacheRecordsPart = pivotTableCacheDefinitionPart.AddNewPart<PivotTableCacheRecordsPart>("rId1");
var pivotCacheRecords = new PivotCacheRecords();
pivotCacheRecords.AddNamespaceDeclaration("r",
"http://schemas.openxmlformats.org/officeDocument/2006/relationships");
pivotTableCacheRecordsPart.PivotCacheRecords = pivotCacheRecords;
}
internal XLPivotFields(IXLPivotTable pivotTable)
{
this._pivotTable = pivotTable;
}
internal XLPivotValues(IXLPivotTable pivotTable)
{
this._pivotTable = pivotTable;
}
public void Add(String name, IXLPivotTable pivotTable)
{
_pivotTables.Add(name, (XLPivotTable)pivotTable);
}
/// <summary> /// <P>Helper function used during saving to calculate the indices of the filtered values</P> /// </summary> /// <returns>Indices of the filtered values</returns> internal abstract IEnumerable <Int32> Match(XLWorkbook.PivotTableInfo pti, IXLPivotTable pt);
internal void Add(String name, IXLPivotTable pivotTable)
{
_pivotTables.Add(name, (XLPivotTable)pivotTable);
}
