public MatrixBuilder(DataClasses.ReportSettings settings)
{
_settings = settings;
foreach (DataClasses.ReportColumn column in settings.MatrixRows)
{
RowFields.Add(column.Name.RemoveAccent());
}
foreach (DataClasses.ReportColumn column in settings.Columns)
{
if (column.ReportDataTypeId != DataClasses.ReportDataType.Number)
{
ColumnFields.Add(column.Name);
}
else
{
SummarizedFields.Add(column.Name);
}
}
if (SummarizedFields.Count == 0)
{
SummarizedFields.Add("Qtd");
}
//SummarizedFields.Add(settings.MatrixRows[0].Name);
}
public static DataTable PivotData(DataTable _SourceTable, string RowField, string DataField, AggregateFunction Aggregate, params string[] ColumnFields)
{
DataTable dt = new DataTable();
string Separator = ".";
var RowList = (from x in _SourceTable.AsEnumerable()
select new { Name = x.Field <object>(RowField) })
.Distinct().OrderBy(x => x.Name);
var ColList = (from x in _SourceTable.AsEnumerable()
select new
{
Name = ColumnFields.Select(n => x.Field <object>(n))
.Aggregate((a, b) => a += Separator + b.ToString())
})
.Distinct()
.OrderBy(m => m.Name);
dt.Columns.Add(RowField);
foreach (var col in ColList)
{
dt.Columns.Add(string.IsNullOrEmpty(col.Name.ToString()) ? "[Empty]" : col.Name.ToString(), typeof(float));
}
foreach (var RowName in RowList)
{
DataRow row = dt.NewRow();
row[RowField] = RowName.Name.ToString();
foreach (var col in ColList)
{
string strFilter = RowField + " = '" + RowName.Name + "'";
string[] strColValues =
col.Name.ToString().Split(Separator.ToCharArray(),
StringSplitOptions.None);
for (int i = 0; i < ColumnFields.Length; i++)
{
strFilter += " and " + ColumnFields[i] +
" = '" + strColValues[i] + "'";
}
row[string.IsNullOrEmpty(col.Name.ToString()) ? "[Empty]" : col.Name.ToString()]
= GetData(_SourceTable, strFilter, DataField, Aggregate);
}
dt.Rows.Add(row);
}
return(dt);
}
// <summary>
/// Pivots the DataTable based on provided RowField, DataField, Aggregate Function and ColumnFields.//
/// </summary>
/// <param name="RowField">The column name of the Source Table which you want to spread into rows</param>
/// <param name="DataField">The column name of the Source Table which you want to spread into Data Part</param>
/// <param name="Aggregate">The Aggregate function which you want to apply in case matching data found more than once</param>
/// <param name="ColumnFields">The List of column names which you want to spread as columns</param>
/// <returns>A DataTable containing the Pivoted Data</returns>
public static DataTable Pivot(this DataTable dtable, string RowField, string DataField, AggregateFunction Aggregate, params string[] ColumnFields)
{
DataTable dt = new DataTable();
string Separator = ".";
var RowList = (from x in dtable.AsEnumerable() select new { Name = x.Field <object>(RowField) }).Distinct();
// Gets the list of columns .(dot) separated.
var ColList = (from x in dtable.AsEnumerable()
select new
{
Name = ColumnFields.Select(n => x.Field <object>(n))
.Aggregate((a, b) => a += Separator + b.ToString())
})
.Distinct()
.OrderBy(m => m.Name);
dt.Columns.Add(RowField);
foreach (var col in ColList)
{
dt.Columns.Add(col.Name.ToString()); // Cretes the result columns.//
}
foreach (var RowName in RowList)
{
DataRow row = dt.NewRow();
row[RowField] = RowName.Name.ToString();
foreach (var col in ColList)
{
string strFilter = RowField + " = '" + RowName.Name + "'";
string[] strColValues = col.Name.ToString().Split(Separator.ToCharArray(), StringSplitOptions.None);
for (int i = 0; i < ColumnFields.Length; i++)
{
strFilter += " and " + ColumnFields[i] + " = '" + strColValues[i] + "'";
}
row[col.Name.ToString()] = dtable.GetData(strFilter, DataField, Aggregate);
}
dt.Rows.Add(row);
}
return(dt);
}
public override int GetHashCode()
{
var result = 37;
unchecked
{
result = result * 17 + base.GetHashCode();
result = result * 17 + SeqNumber;
result = result * 17 + (ColumnFields?.Select(f => f.GetHashCode()).UncheckedSum() ?? 0);
result = result * 17 + (int)FieldType;
result = result * 17 + FormatTypeId;
result = result * 17 + (CurrencyCode?.GetHashCode() ?? 0);
result = result * 17 + HasSubtotals.GetHashCode();
result = result * 17 + HasTotals.GetHashCode();
result = result * 17 + (int)TotalsAggregation;
result = result * 17 + IsVisible.GetHashCode();
if (ColumnGroupId != null)
{
result = result * 17 + ColumnGroupId.Value;
}
}
return(result);
}
internal ExcelPivotTable(PackageRelationship rel, ExcelWorksheet sheet) :
base(sheet.NameSpaceManager)
{
WorkSheet = sheet;
PivotTableUri = PackUriHelper.ResolvePartUri(rel.SourceUri, rel.TargetUri);
Relationship = rel;
var pck = sheet._package.Package;
Part = pck.GetPart(PivotTableUri);
PivotTableXml = new XmlDocument();
PivotTableXml.Load(Part.GetStream());
init();
TopNode = PivotTableXml.DocumentElement;
Address = new ExcelAddressBase(GetXmlNodeString("d:location/@ref"));
_cacheDefinition = new ExcelPivotCacheDefinition(sheet.NameSpaceManager, this);
LoadFields();
//int index=0;
////Add fields.
//foreach (XmlElement fieldElem in TopNode.SelectNodes("d:pivotFields/d:pivotField", NameSpaceManager))
//{
// var fld=new ExcelPivotTableField(NameSpaceManager, fieldElem, this, index++);
// Fields.AddInternal(fld);
//}
////Add fields.
//index = 0;
//foreach (XmlElement fieldElem in _cacheDefinition.TopNode.SelectNodes("d:cacheFields/d:cacheField", NameSpaceManager))
//{
// var fld = Fields[index++];
// fld.SetCacheFieldNode(fieldElem);
//}
//Add row fields.
foreach (XmlElement rowElem in TopNode.SelectNodes("d:rowFields/d:field", NameSpaceManager))
{
int x;
if (int.TryParse(rowElem.GetAttribute("x"), out x) && x >= 0)
{
RowFields.AddInternal(Fields[x]);
}
else
{
rowElem.ParentNode.RemoveChild(rowElem);
}
}
////Add column fields.
foreach (XmlElement colElem in TopNode.SelectNodes("d:colFields/d:field", NameSpaceManager))
{
int x;
if (int.TryParse(colElem.GetAttribute("x"), out x) && x >= 0)
{
ColumnFields.AddInternal(Fields[x]);
}
else
{
colElem.ParentNode.RemoveChild(colElem);
}
}
//Add Page elements
//int index = 0;
foreach (XmlElement pageElem in TopNode.SelectNodes("d:pageFields/d:pageField", NameSpaceManager))
{
int fld;
if (int.TryParse(pageElem.GetAttribute("fld"), out fld) && fld >= 0)
{
var field = Fields[fld];
field._pageFieldSettings = new ExcelPivotTablePageFieldSettings(NameSpaceManager, pageElem, field, fld);
PageFields.AddInternal(field);
}
}
//Add data elements
//index = 0;
foreach (XmlElement dataElem in TopNode.SelectNodes("d:dataFields/d:dataField", NameSpaceManager))
{
int fld;
if (int.TryParse(dataElem.GetAttribute("fld"), out fld) && fld >= 0)
{
var field = Fields[fld];
var dataField = new ExcelPivotTableDataField(NameSpaceManager, dataElem, field);
DataFields.AddInternal(dataField);
}
}
}
// 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;
}
private IList <ColumnModel> GetColumns()
{
var lstProperties = typeof(TModel).GetProperties();
var lstFields = new List <PropertyInfo>();
var lstColumns = new List <ColumnModel>();
if (!ColumnFields.IsNullOrEmpty())
{
lstProperties = ColumnFields.Split(',')
.Where(s => lstProperties.Any(x => x.Name.Equals(s)))
.Select(x => lstProperties.SingleOrDefault(f => f.Name.Equals(x))).ToArray();
}
var _order = 100;
foreach (var prop in lstProperties)
{
var pType = prop.PropertyType;
var pName = prop.Name;
var _field = new ColumnModel
{
Field = prop.Name,
Width = "150",
IsResizeble = false,
Caption = prop.Name,
IsPrimaryKey = false,
IsEditable = true,
Format = "",
Order = _order++
};
if (pName.Equals("Id"))
{
_field.IsEditable = false;
_field.Order = 1;
_field.IsPrimaryKey = true;
_field.Width = "60";
}
if (prop.Name.Equals("Nome"))
{
//_field.Width = "250";
_field.IsResizeble = true;
_field.Order = 2;
}
if (prop.PropertyType.IsCollection())
{
_field.Ignore = true;
}
var attr = prop.GetAttribute <ModelFieldAttribute>();
if (attr != null)
{
if (attr.Order > 0)
{
_field.Order = attr.Order;
}
_field.Ignore = attr.Ignore;
_field.IsResizeble = attr.AutoFit;
_field.IsEditable = attr.AllowEdit;
_field.Caption = attr.Display.IsEmpty() ? prop.Name : attr.Display;
_field.Field = attr.FieldMapping.IsEmpty() ? prop.Name : attr.FieldMapping;
}
if (pType == typeof(bool))
{
_field.Width = "60";
_field.Type = ColumnType.Boolean;
_field.IsCheckBox = true;
}
else if (pType == typeof(decimal?) || pType == typeof(decimal))
{
_field.Width = "120";
_field.Format = "n2";
_field.Type = ColumnType.Number;
}
else if (pType == typeof(int?) || pType == typeof(int))
{
_field.Width = "60";
_field.Type = ColumnType.Number;
}
else if (pType == typeof(DateTime?) || pType == typeof(DateTime))
{
_field.Width = "130";
_field.Format = "dd/MM/yyyy";
_field.Type = ColumnType.DateTime;
}
if (!AllowResize)
{
_field.IsResizeble = false;
}
lstColumns.Add(_field);
}
return(lstColumns.Where(f => !f.Ignore).OrderBy(f => f.Order).ToList());
}
public override bool Equals(object obj)
{
var result = base.Equals(obj);
if (result)
{
var column = obj as ColumnInfo;
if (column != null)
{
result = SeqNumber == column.SeqNumber && FieldType == column.FieldType &&
FormatTypeId == column.FormatTypeId && string.Equals(CurrencyCode, column.CurrencyCode) &&
HasSubtotals == column.HasSubtotals && HasTotals == column.HasTotals &&
TotalsAggregation == column.TotalsAggregation && ColumnGroupId == column.ColumnGroupId && IsVisible == column.IsVisible;
result = result &&
((ColumnFields == null && column.ColumnFields == null) ||
(ColumnFields != null && column.ColumnFields != null && ColumnFields.OrderBy(f => f.FieldId).SequenceEqual(column.ColumnFields.OrderBy(f => f.FieldId))));
}
else
{
result = false;
}
}
return(result);
}
