Example #1
0
        /// <summary>
        /// Add a new pivottable
        /// </summary>
        /// <param name="sheet">The worksheet</param>
        /// <param name="address">the address of the pivottable</param>
        /// <param name="sourceAddress">The address of the Source data</param>
        /// <param name="name"></param>
        /// <param name="tblId"></param>
        internal ExcelPivotTable(ExcelWorksheet sheet, ExcelAddressBase address, ExcelRangeBase sourceAddress, string name, int tblId) :
            base(sheet.NameSpaceManager)
        {
            WorkSheet = sheet;
            Address   = address;
            var pck = sheet._package.Package;

            PivotTableXml = new XmlDocument();
            LoadXmlSafe(PivotTableXml, GetStartXml(name, tblId, address, sourceAddress));
            TopNode       = PivotTableXml.DocumentElement;
            PivotTableUri = GetNewUri(pck, "/xl/pivotTables/pivotTable{0}.xml", tblId);
            init();

            Part = pck.CreatePart(PivotTableUri, ExcelPackage.schemaPivotTable);
            PivotTableXml.Save(Part.GetStream());

            //Worksheet-Pivottable relationship
            Relationship = sheet.Part.CreateRelationship(PackUriHelper.ResolvePartUri(sheet.WorksheetUri, PivotTableUri), TargetMode.Internal, ExcelPackage.schemaRelationships + "/pivotTable");

            _cacheDefinition = new ExcelPivotCacheDefinition(sheet.NameSpaceManager, this, sourceAddress, tblId);
            _cacheDefinition.Relationship = Part.CreateRelationship(PackUriHelper.ResolvePartUri(PivotTableUri, _cacheDefinition.CacheDefinitionUri), TargetMode.Internal, ExcelPackage.schemaRelationships + "/pivotCacheDefinition");

            sheet.Workbook.AddPivotTable(CacheID.ToString(), _cacheDefinition.CacheDefinitionUri);

            LoadFields();

            using (var r = sheet.Cells[address.Address])
            {
                r.Clear();
            }
        }
Example #2
0
        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();
            LoadXmlSafe(PivotTableXml, Part.GetStream());
            init();
            TopNode = PivotTableXml.DocumentElement;
            Address = new ExcelAddressBase(GetXmlNodeString("d:location/@ref"));

            _cacheDefinition = new ExcelPivotCacheDefinition(sheet.NameSpaceManager, this);

            LoadFields();

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