コード例 #1
0
        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);
        }
コード例 #2
0
        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);
        }
コード例 #3
0
        // <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);
        }
コード例 #4
0
        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);
        }
コード例 #5
0
ファイル: ExcelPivotTable.cs プロジェクト: luerli622/DataPie
        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);
                }
            }
        }
コード例 #6
0
        // 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;
        }
コード例 #7
0
        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());
        }
コード例 #8
0
        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);
        }