/******************************************************************************* * You may amend and distribute as you like, but don't remove this header! * * EPPlus provides server-side generation of Excel 2007/2010 spreadsheets. * See http://www.codeplex.com/EPPlus for details. * * Copyright (C) 2011 Jan Källman * * This library is free software; you can redistribute it and/or * modify it under the terms of the GNU Lesser General Public * License as published by the Free Software Foundation; either * version 2.1 of the License, or (at your option) any later version. * This library is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. * See the GNU Lesser General Public License for more details. * * The GNU Lesser General Public License can be viewed at http://www.opensource.org/licenses/lgpl-license.php * If you unfamiliar with this license or have questions about it, here is an http://www.gnu.org/licenses/gpl-faq.html * * All code and executables are provided "as is" with no warranty either express or implied. * The author accepts no liability for any damage or loss of business that this product may cause. * * Code change notes: * * Author Change Date * ****************************************************************************** * Jan Källman Added 21-MAR-2011 * Jan Källman License changed GPL-->LGPL 2011-12-16 *******************************************************************************/ using System; using System.Collections.Generic; using System.Text; using System.Xml; using System.IO.Packaging; using System.Text.RegularExpressions; using OfficeOpenXml.Table; namespace OfficeOpenXml.Table.PivotTable { /// /// An Excel Pivottable /// public class ExcelPivotTable : XmlHelper { 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); } } } /// /// Add a new pivottable /// /// The worksheet /// the address of the pivottable /// The address of the Source data /// /// 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), Encoding.UTF8); 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(); } } private void init() { SchemaNodeOrder = new string[] { "location", "pivotFields", "rowFields", "rowItems", "colFields", "colItems", "pageFields", "pageItems", "dataFields", "dataItems", "formats", "pivotTableStyleInfo" }; } private void LoadFields() { //Fields.Clear(); //int ix=0; //foreach(XmlElement fieldNode in PivotXml.SelectNodes("//d:pivotFields/d:pivotField",NameSpaceManager)) //{ // Fields.AddInternal(new ExcelPivotTableField(NameSpaceManager, fieldNode, this, ix++)); //} int index = 0; //Add fields. foreach (XmlElement fieldElem in TopNode.SelectNodes("d:pivotFields/d:pivotField", NameSpaceManager)) { var fld = new ExcelPivotTableField(NameSpaceManager, fieldElem, this, index, 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); } } private string GetStartXml(string name, int id, ExcelAddressBase address, ExcelAddressBase sourceAddress) { string xml = string.Format("", name, id); xml += string.Format(" ", address.FirstAddress); xml += string.Format("", sourceAddress._toCol-sourceAddress._fromCol+1); for (int col = sourceAddress._fromCol; col <= sourceAddress._toCol; col++) { xml += ""; //compact=\"0\" outline=\"0\" subtotalTop=\"0\" includeNewItemsInFilter=\"1\" } xml += ""; xml += ""; xml += ""; return xml; } internal PackagePart Part { get; set; } /// /// Provides access to the XML data representing the pivottable in the package. /// public XmlDocument PivotTableXml { get; private set; } /// /// The package internal URI to the pivottable Xml Document. /// public Uri PivotTableUri { get; internal set; } internal PackageRelationship Relationship { get; set; } const string ID_PATH = "@id"; internal int Id { get { return GetXmlNodeInt(ID_PATH); } set { SetXmlNodeString(ID_PATH, value.ToString()); } } const string NAME_PATH = "@name"; const string DISPLAY_NAME_PATH = "@displayName"; /// /// Name of the pivottable object in Excel /// public string Name { get { return GetXmlNodeString(NAME_PATH); } set { if (WorkSheet.Workbook.ExistsTableName(value)) { throw (new ArgumentException("PivotTable name is not unique")); } string prevName = Name; if (WorkSheet.Tables._tableNames.ContainsKey(prevName)) { int ix = WorkSheet.Tables._tableNames[prevName]; WorkSheet.Tables._tableNames.Remove(prevName); WorkSheet.Tables._tableNames.Add(value, ix); } SetXmlNodeString(NAME_PATH, value); SetXmlNodeString(DISPLAY_NAME_PATH, cleanDisplayName(value)); } } ExcelPivotCacheDefinition _cacheDefinition = null; /// /// Reference to the pivot table cache definition object /// public ExcelPivotCacheDefinition CacheDefinition { get { if (_cacheDefinition == null) { _cacheDefinition = new ExcelPivotCacheDefinition(NameSpaceManager, this, null, 1); } return _cacheDefinition; } } private string cleanDisplayName(string name) { return Regex.Replace(name, @"[^\w\.-_]", "_"); } #region "Public Properties" /// /// The worksheet where the pivottable is located /// public ExcelWorksheet WorkSheet { get; set; } /// /// The location of the pivot table /// public ExcelAddressBase Address { get; internal set; } /// /// If multiple datafields are displayed in the row area or the column area /// public bool DataOnRows { get { return GetXmlNodeBool("@dataOnRows"); } set { SetXmlNodeBool("@dataOnRows",value); } } /// /// if true apply legacy table autoformat number format properties. /// public bool ApplyNumberFormats { get { return GetXmlNodeBool("@applyNumberFormats"); } set { SetXmlNodeBool("@applyNumberFormats",value); } } /// /// If true apply legacy table autoformat border properties /// public bool ApplyBorderFormats { get { return GetXmlNodeBool("@applyBorderFormats"); } set { SetXmlNodeBool("@applyBorderFormats",value); } } /// /// If true apply legacy table autoformat font properties /// public bool ApplyFontFormats { get { return GetXmlNodeBool("@applyFontFormats"); } set { SetXmlNodeBool("@applyFontFormats",value); } } /// /// If true apply legacy table autoformat pattern properties /// public bool ApplyPatternFormats { get { return GetXmlNodeBool("@applyPatternFormats"); } set { SetXmlNodeBool("@applyPatternFormats",value); } } /// /// If true apply legacy table autoformat width/height properties. /// public bool ApplyWidthHeightFormats { get { return GetXmlNodeBool("@applyWidthHeightFormats"); } set { SetXmlNodeBool("@applyWidthHeightFormats",value); } } /// /// Show member property information /// public bool ShowMemberPropertyTips { get { return GetXmlNodeBool("@showMemberPropertyTips"); } set { SetXmlNodeBool("@showMemberPropertyTips",value); } } /// /// Show the drill indicators /// public bool ShowCalcMember { get { return GetXmlNodeBool("@showCalcMbrs"); } set { SetXmlNodeBool("@showCalcMbrs", value); } } /// /// If the user is prevented from drilling down on a PivotItem or aggregate value /// public bool EnableDrill { get { return GetXmlNodeBool("@enableDrill", true); } set { SetXmlNodeBool("@enableDrill", value); } } /// /// Show the drill down buttons /// public bool ShowDrill { get { return GetXmlNodeBool("@showDrill", true); } set { SetXmlNodeBool("@showDrill", value); } } /// /// If the tooltips should be displayed for PivotTable data cells. /// public bool ShowDataTips { get { return GetXmlNodeBool("@showDataTips", true); } set { SetXmlNodeBool("@showDataTips", value, true); } } /// /// If the row and column titles from the PivotTable should be printed. /// public bool FieldPrintTitles { get { return GetXmlNodeBool("@fieldPrintTitles"); } set { SetXmlNodeBool("@fieldPrintTitles", value); } } /// /// If the row and column titles from the PivotTable should be printed. /// public bool ItemPrintTitles { get { return GetXmlNodeBool("@itemPrintTitles"); } set { SetXmlNodeBool("@itemPrintTitles", value); } } /// /// If the grand totals should be displayed for the PivotTable columns /// public bool ColumGrandTotals { get { return GetXmlNodeBool("@colGrandTotals"); } set { SetXmlNodeBool("@colGrandTotals", value); } } /// /// If the grand totals should be displayed for the PivotTable rows /// public bool RowGrandTotals { get { return GetXmlNodeBool("@rowGrandTotals"); } set { SetXmlNodeBool("@rowGrandTotals", value); } } /// /// If the drill indicators expand collapse buttons should be printed. /// public bool PrintDrill { get { return GetXmlNodeBool("@printDrill"); } set { SetXmlNodeBool("@printDrill", value); } } /// /// Indicates whether to show error messages in cells. /// public bool ShowError { get { return GetXmlNodeBool("@showError"); } set { SetXmlNodeBool("@showError", value); } } /// /// The string to be displayed in cells that contain errors. /// public string ErrorCaption { get { return GetXmlNodeString("@errorCaption"); } set { SetXmlNodeString("@errorCaption", value); } } /// /// Specifies the name of the value area field header in the PivotTable. /// This caption is shown when the PivotTable when two or more fields are in the values area. /// public string DataCaption { get { return GetXmlNodeString("@dataCaption"); } set { SetXmlNodeString("@dataCaption", value); } } /// /// Show field headers /// public bool ShowHeaders { get { return GetXmlNodeBool("@showHeaders"); } set { SetXmlNodeBool("@showHeaders", value); } } /// /// The number of page fields to display before starting another row or column /// public int PageWrap { get { return GetXmlNodeInt("@pageWrap"); } set { if(value<0) { throw new Exception("Value can't be negative"); } SetXmlNodeString("@pageWrap", value.ToString()); } } /// /// A boolean that indicates whether legacy auto formatting has been applied to the PivotTable view /// public bool UseAutoFormatting { get { return GetXmlNodeBool("@useAutoFormatting"); } set { SetXmlNodeBool("@useAutoFormatting",value); } } /// /// A boolean that indicates whether the in-grid drop zones should be displayed at runtime, and whether classic layout is applied /// public bool GridDropZones { get { return GetXmlNodeBool("@gridDropZones"); } set { SetXmlNodeBool("@gridDropZones",value); } } /// /// Specifies the indentation increment for compact axis and can be used to set the Report Layout to Compact Form /// public int Indent { get { return GetXmlNodeInt("@indent"); } set { SetXmlNodeString("@indent",value.ToString()); } } /// /// A boolean that indicates whether data fields in the PivotTable should be displayed in outline form /// public bool OutlineData { get { return GetXmlNodeBool("@outlineData"); } set { SetXmlNodeBool("@outlineData", value); } } /// /// a boolean that indicates whether new fields should have their outline flag set to true /// public bool Outline { get { return GetXmlNodeBool("@outline"); } set { SetXmlNodeBool("@outline", value); } } /// /// A boolean that indicates whether the fields of a PivotTable can have multiple filters set on them /// public bool MultipleFieldFilters { get { return GetXmlNodeBool("@multipleFieldFilters"); } set { SetXmlNodeBool("@multipleFieldFilters", value); } } /// /// A boolean that indicates whether new fields should have their compact flag set to true /// public bool Compact { get { return GetXmlNodeBool("@compact"); } set { SetXmlNodeBool("@compact",value); } } /// /// A boolean that indicates whether the field next to the data field in the PivotTable should be displayed in the same column of the spreadsheet /// public bool CompactData { get { return GetXmlNodeBool("@compactData"); } set { SetXmlNodeBool("@compactData",value); } } /// /// Specifies the string to be displayed for grand totals. /// public string GrandTotalCaption { get { return GetXmlNodeString("@grandTotalCaption"); } set { SetXmlNodeString("@grandTotalCaption", value); } } /// /// Specifies the string to be displayed in row header in compact mode. /// public string RowHeaderCaption { get { return GetXmlNodeString("@rowHeaderCaption"); } set { SetXmlNodeString("@rowHeaderCaption", value); } } /// /// Specifies the string to be displayed in cells with no value /// public string MissingCaption { get { return GetXmlNodeString("@missingCaption"); } set { SetXmlNodeString("@missingCaption", value); } } const string FIRSTHEADERROW_PATH="d:location/@firstHeaderRow"; /// /// Specifies the first row of the PivotTable header, relative to the top left cell in the ref value /// public int FirstHeaderRow { get { return GetXmlNodeInt(FIRSTHEADERROW_PATH); } set { SetXmlNodeString(FIRSTHEADERROW_PATH, value.ToString()); } } const string FIRSTDATAROW_PATH = "d:location/@firstDataRow"; /// /// Specifies the first column of the PivotTable data, relative to the top left cell in the ref value /// public int FirstDataRow { get { return GetXmlNodeInt(FIRSTDATAROW_PATH); } set { SetXmlNodeString(FIRSTDATAROW_PATH, value.ToString()); } } const string FIRSTDATACOL_PATH = "d:location/@firstDataCol"; /// /// Specifies the first column of the PivotTable data, relative to the top left cell in the ref value /// public int FirstDataCol { get { return GetXmlNodeInt(FIRSTDATACOL_PATH); } set { SetXmlNodeString(FIRSTDATACOL_PATH, value.ToString()); } } ExcelPivotTableFieldCollection _fields = null; /// /// The fields in the table /// public ExcelPivotTableFieldCollection Fields { get { if (_fields == null) { _fields = new ExcelPivotTableFieldCollection(this, ""); } return _fields; } } ExcelPivotTableRowColumnFieldCollection _rowFields = null; /// /// Row label fields /// public ExcelPivotTableRowColumnFieldCollection RowFields { get { if (_rowFields == null) { _rowFields = new ExcelPivotTableRowColumnFieldCollection(this, "rowFields"); } return _rowFields; } } ExcelPivotTableRowColumnFieldCollection _columnFields = null; /// /// Column label fields /// public ExcelPivotTableRowColumnFieldCollection ColumnFields { get { if (_columnFields == null) { _columnFields = new ExcelPivotTableRowColumnFieldCollection(this, "colFields"); } return _columnFields; } } ExcelPivotTableDataFieldCollection _dataFields = null; /// /// Value fields /// public ExcelPivotTableDataFieldCollection DataFields { get { if (_dataFields == null) { _dataFields = new ExcelPivotTableDataFieldCollection(this); } return _dataFields; } } ExcelPivotTableRowColumnFieldCollection _pageFields = null; /// /// Report filter fields /// public ExcelPivotTableRowColumnFieldCollection PageFields { get { if (_pageFields == null) { _pageFields = new ExcelPivotTableRowColumnFieldCollection(this, "pageFields"); } return _pageFields; } } const string STYLENAME_PATH = "d:pivotTableStyleInfo/@name"; /// /// Pivot style name. Used for custom styles /// public string StyleName { get { return GetXmlNodeString(StyleName); } set { if (value.StartsWith("PivotStyle")) { try { _tableStyle = (TableStyles)Enum.Parse(typeof(TableStyles), value.Substring(10, value.Length - 10), true); } catch { _tableStyle = TableStyles.Custom; } } else if (value == "None") { _tableStyle = TableStyles.None; value = ""; } else { _tableStyle = TableStyles.Custom; } SetXmlNodeString(STYLENAME_PATH, value, true); } } TableStyles _tableStyle = Table.TableStyles.Medium6; /// /// The table style. If this property is cusom the style from the StyleName propery is used. /// public TableStyles TableStyle { get { return _tableStyle; } set { _tableStyle=value; if (value != TableStyles.Custom) { SetXmlNodeString(STYLENAME_PATH, "PivotStyle" + value.ToString()); } } } #endregion #region "Internal Properties" internal int CacheID { get { return GetXmlNodeInt("@cacheId"); } set { SetXmlNodeString("@cacheId",value.ToString()); } } #endregion } }