diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/PivotTables/IXLPivotField.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/PivotTables/IXLPivotField.cs index c186c55..4fce6d6 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/PivotTables/IXLPivotField.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/PivotTables/IXLPivotField.cs @@ -49,5 +49,6 @@ IXLPivotField SetShowBlankItems(); IXLPivotField SetShowBlankItems(Boolean value); IXLPivotField SetInsertPageBreaks(); IXLPivotField SetInsertPageBreaks(Boolean value); + List SharedStrings { get; set; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/PivotTables/IXLPivotTable.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/PivotTables/IXLPivotTable.cs index 913fdc2..d5160b1 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/PivotTables/IXLPivotTable.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/PivotTables/IXLPivotTable.cs @@ -114,6 +114,9 @@ String Title { get; set; } String Description { get; set; } + IXLCell TargetCell { get; set; } + IXLRange SourceRange { get; set; } + Boolean MergeAndCenterWithLabels { get; set; } // MergeItem Int32 RowLabelIndent { get; set; } // Indent XLFilterAreaOrder FilterAreaOrder { get; set; } // PageOverThenDown @@ -206,6 +209,6 @@ IXLPivotTable SetLayout(XLPivotLayout value); IXLPivotTable SetInsertBlankLines(); IXLPivotTable SetInsertBlankLines(Boolean value); - + void SetExcelDefaults(); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/PivotTables/IXLPivotTables.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/PivotTables/IXLPivotTables.cs index bd27bb9..1f3bb63 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/PivotTables/IXLPivotTables.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/PivotTables/IXLPivotTables.cs @@ -8,6 +8,8 @@ public interface IXLPivotTables: IEnumerable { IXLPivotTable PivotTable(String name); + void Add(String name, IXLPivotTable pivotTable); + IXLPivotTable AddNew(String name, IXLCell target, IXLRange source); void Delete(String name); void DeleteAll(); } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/PivotTables/XLPivotField.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/PivotTables/XLPivotField.cs index 2d22830..749650b 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/PivotTables/XLPivotField.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/PivotTables/XLPivotField.cs @@ -11,6 +11,7 @@ public XLPivotField(string sourceName) { SourceName = sourceName; + SharedStrings = new List(); } public String SourceName { get; private set; } @@ -26,5 +27,6 @@ public Boolean ShowBlankItems { get; set; } public IXLPivotField SetShowBlankItems() { ShowBlankItems = true; return this; } public IXLPivotField SetShowBlankItems(Boolean value) { ShowBlankItems = value; return this; } public Boolean InsertPageBreaks { get; set; } public IXLPivotField SetInsertPageBreaks() { InsertPageBreaks = true; return this; } public IXLPivotField SetInsertPageBreaks(Boolean value) { InsertPageBreaks = value; return this; } + public List SharedStrings { get; set; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/PivotTables/XLPivotTable.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/PivotTables/XLPivotTable.cs index d848d31..3f94fbd 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/PivotTables/XLPivotTable.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/PivotTables/XLPivotTable.cs @@ -17,6 +17,9 @@ Theme = XLPivotTableTheme.PivotStyleLight16; } + public IXLCell TargetCell { get; set; } + public IXLRange SourceRange { get; set; } + public IXLPivotFields Fields { get; private set; } public IXLPivotFields ReportFilters { get; private set; } public IXLPivotFields ColumnLabels { get; private set; } @@ -85,5 +88,21 @@ public IXLPivotTable SetInsertBlankLines() { InsertBlankLines = true; return this; } public IXLPivotTable SetInsertBlankLines(Boolean value) { InsertBlankLines = value; return this; } + public void SetExcelDefaults() + { + this.EmptyCellReplacement = String.Empty; + this.AutofitColumns = true; + this.PreserveCellFormatting = true; + this.ShowGrandTotalsColumns = true; + this.ShowGrandTotalsRows = true; + this.UseCustomListsForSorting = true; + this.ShowExpandCollapseButtons = true; + this.ShowContextualTooltips = true; + this.DisplayCaptionsAndDropdowns = true; + this.RepeatRowLabels = true; + this.SaveSourceData = true; + this.EnableShowDetails = true; + } + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/PivotTables/XLPivotTables.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/PivotTables/XLPivotTables.cs index a09536b..fe945b3 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/PivotTables/XLPivotTables.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/PivotTables/XLPivotTables.cs @@ -35,9 +35,17 @@ _pivotTables.Clear(); } - public void Add(String name, XLPivotTable pivotTable) + public void Add(String name, IXLPivotTable pivotTable) { - _pivotTables.Add(name, pivotTable); + _pivotTables.Add(name, (XLPivotTable)pivotTable); } + + public IXLPivotTable AddNew(string name, IXLCell target, IXLRange source) + { + var pivotTable = new XLPivotTable { Name = name, TargetCell = target, SourceRange = source }; + _pivotTables.Add(name, pivotTable); + return pivotTable; + } + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs index a174fef..8227d74 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs @@ -213,8 +213,8 @@ Boolean IsEmpty(); Boolean IsEmpty(Boolean includeFormats); - //IXLPivotTable CreatePivotTable(IXLCell targetCell); - //IXLPivotTable CreatePivotTable(IXLCell targetCell, String name); + IXLPivotTable CreatePivotTable(IXLCell targetCell); + IXLPivotTable CreatePivotTable(IXLCell targetCell, String name); //IXLChart CreateChart(Int32 firstRow, Int32 firstColumn, Int32 lastRow, Int32 lastColumn); } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs index 74fd8ed..be12fd8 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -1338,21 +1338,21 @@ // return chart; //} - //IXLPivotTable IXLRangeBase.CreatePivotTable(IXLCell targetCell) - //{ - // return CreatePivotTable(targetCell); - //} + IXLPivotTable IXLRangeBase.CreatePivotTable(IXLCell targetCell) + { + return CreatePivotTable(targetCell); + } public XLPivotTable CreatePivotTable(IXLCell targetCell) { - throw new NotImplementedException(); + return CreatePivotTable(targetCell, Guid.NewGuid().ToString()); } - //IXLPivotTable IXLRangeBase.CreatePivotTable(IXLCell targetCell, String name) - //{ - // return CreatePivotTable(targetCell, name); - //} + IXLPivotTable IXLRangeBase.CreatePivotTable(IXLCell targetCell, String name) + { + return CreatePivotTable(targetCell, name); + } public XLPivotTable CreatePivotTable(IXLCell targetCell, String name) { - throw new NotImplementedException(); + return (XLPivotTable)this.Worksheet.PivotTables.AddNew(name, targetCell, this.AsRange()); } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs index 7e66a29..27447d7 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs @@ -142,6 +142,8 @@ GenerateWorksheetPartContent(worksheetPart, worksheet, context); + //GeneratePivotTables(workbookPart, worksheetPart, worksheet, context); + //DrawingsPart drawingsPart = worksheetPart.AddNewPart("rId1"); //GenerateDrawingsPartContent(drawingsPart, worksheet); @@ -3773,5 +3775,275 @@ // return C.BarDirectionValues.Bar; //} //-- + + private static void GeneratePivotTables(WorkbookPart workbookPart, WorksheetPart worksheetPart, XLWorksheet xlWorksheet, + SaveContext context) + { + foreach (var pt in xlWorksheet.PivotTables) + { + string ptCdp = context.RelIdGenerator.GetNext(RelType.Workbook); + + var pivotTableCacheDefinitionPart = workbookPart.AddNewPart(ptCdp); + GeneratePivotTableCacheDefinitionPartContent(pivotTableCacheDefinitionPart, pt); + + var pivotCaches = new PivotCaches(); + var pivotCache = new PivotCache() { CacheId = (UInt32Value)0U, Id = ptCdp }; + + pivotCaches.Append(pivotCache); + + workbookPart.Workbook.Append(pivotCaches); + + var pivotTablePart = worksheetPart.AddNewPart(context.RelIdGenerator.GetNext(RelType.Workbook)); + GeneratePivotTablePartContent(pivotTablePart, pt); + + pivotTablePart.AddPart(pivotTableCacheDefinitionPart, "rId1"); + } + } + + // Generates content of pivotTableCacheDefinitionPart1. + private static void GeneratePivotTableCacheDefinitionPartContent(PivotTableCacheDefinitionPart pivotTableCacheDefinitionPart, IXLPivotTable pt) + { + IXLRange source = pt.SourceRange; + + PivotCacheDefinition 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 = ExcelHelper.MaxRowNumber; + + pivotCacheDefinition.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); + + CacheSource cacheSource = new CacheSource() { Type = SourceValues.Worksheet }; + cacheSource.Append(new WorksheetSource() { Name = source.ToString() }); + + CacheFields cacheFields = new CacheFields(); + + foreach (var c in source.Columns()) + { + var fieldName = c.FirstCell().Value.ToString(); + + var xlpf = pt.Fields.Add(fieldName); + + CacheField cacheField = new CacheField() { Name = fieldName }; + SharedItems sharedItems; + + if (fieldName == "Number") + { + sharedItems = new SharedItems() { ContainsSemiMixedTypes = false, ContainsString = false, ContainsNumber = true }; + } + else + { + sharedItems = new SharedItems(); + + foreach (var r in source.Rows()) + { + var cellValue = r.Cell(c.ColumnNumber()).Value.ToString(); + + if (cellValue != fieldName && !xlpf.SharedStrings.Contains(cellValue)) + { + xlpf.SharedStrings.Add(cellValue); + } + } + + foreach (var li in xlpf.SharedStrings) + { + sharedItems.Append(new StringItem() { Val = li }); + } + + } + + cacheField.Append(sharedItems); + + cacheFields.Append(cacheField); + } + + pivotCacheDefinition.Append(cacheSource); + pivotCacheDefinition.Append(cacheFields); + + pivotTableCacheDefinitionPart.PivotCacheDefinition = pivotCacheDefinition; + + PivotTableCacheRecordsPart pivotTableCacheRecordsPart = pivotTableCacheDefinitionPart.AddNewPart("rId1"); + + PivotCacheRecords pivotCacheRecords = new PivotCacheRecords(); + pivotCacheRecords.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); + pivotTableCacheRecordsPart.PivotCacheRecords = pivotCacheRecords; + + } + + // Generates content of pivotTablePart1. + private static void GeneratePivotTablePartContent(PivotTablePart pivotTablePart1, IXLPivotTable pt) + { + var pivotTableDefinition = new PivotTableDefinition() + { + Name = pt.Name, + CacheId = (UInt32Value)0U, + DataCaption = "Values", + MergeItem = pt.MergeAndCenterWithLabels, + Indent = Convert.ToUInt32(pt.RowLabelIndent), + PageOverThenDown = (pt.FilterAreaOrder == XLFilterAreaOrder.OverThenDown ? true : false), + PageWrap = Convert.ToUInt32(pt.FilterFieldsPageWrap), + ShowError = String.IsNullOrEmpty(pt.ErrorValueReplacement), + UseAutoFormatting = pt.AutofitColumns, + PreserveFormatting = pt.PreserveCellFormatting, + RowGrandTotals = pt.ShowGrandTotalsRows, + ColumnGrandTotals = pt.ShowGrandTotalsColumns, + SubtotalHiddenItems = pt.FilteredItemsInSubtotals, + MultipleFieldFilters = pt.AllowMultipleFilters, + CustomListSort = pt.UseCustomListsForSorting, + ShowDrill = pt.ShowExpandCollapseButtons, + ShowDataTips = pt.ShowContextualTooltips, + ShowMemberPropertyTips = pt.ShowPropertiesInTooltips, + ShowHeaders = pt.DisplayCaptionsAndDropdowns, + GridDropZones = pt.ClassicPivotTableLayout, + ShowEmptyRow = pt.ShowEmptyItemsOnRows, + ShowEmptyColumn = pt.ShowEmptyItemsOnColumns, + ShowItems = pt.DisplayItemLabels, + FieldListSortAscending = pt.SortFieldsAtoZ, + PrintDrill = pt.PrintExpandCollapsedButtons, + ItemPrintTitles = pt.RepeatRowLabels, + FieldPrintTitles = pt.PrintTitles, + EnableDrill = pt.EnableShowDetails + }; + + 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 = (UInt32Value)1U, FirstDataRow = (UInt32Value)1U, FirstDataColumn = (UInt32Value)0U }; + pivotTableDefinition.Append(location); + + RowFields rowFields = new RowFields(); + var rowItems = new RowItems(); + + PivotFields pivotFields = new PivotFields() { Count = Convert.ToUInt32(pt.SourceRange.ColumnCount()) }; + foreach (var xlpf in pt.Fields) + { + PivotField pf = new PivotField() { ShowAll = false }; + + if (pt.RowLabels.Where(p => p.SourceName == xlpf.SourceName).FirstOrDefault() != null) + { + pf.Axis = PivotTableAxisValues.AxisRow; + + + var f = new DocumentFormat.OpenXml.Spreadsheet.Field() { Index = 0 }; + rowFields.Append(f); + + int valIndex = 0; + foreach (var v in xlpf.SharedStrings) + { + var rowItem = new RowItem(); + rowItem.Append(new MemberPropertyIndex() { Val = valIndex }); + valIndex++; + rowItems.Append(rowItem); + } + var rowItemTotal = new RowItem() { ItemType = ItemValues.Grand }; + rowItemTotal.Append(new MemberPropertyIndex()); + rowItems.Append(rowItemTotal); + + + } + + if (pt.Values.Where(p => p.CustomName == xlpf.SourceName).FirstOrDefault() != null) + { + pf.DataField = true; + } + + if (xlpf.SharedStrings.Count > 0) + { + Items items = new Items(); + + uint i = 0; + foreach (var v in xlpf.SharedStrings) + { + items.Append(new Item() { Index = i }); + i++; + } + items.Append(new Item() { ItemType = ItemValues.Default }); + + pf.Append(items); + } + + pivotFields.Append(pf); + } + + //PivotField pivotField3 = new PivotField() { ShowAll = false }; + + ColumnItems columnItems = new ColumnItems(); + columnItems.Append(new RowItem()); + + pivotTableDefinition.Append(pivotFields); + pivotTableDefinition.Append(rowFields); + pivotTableDefinition.Append(rowItems); + pivotTableDefinition.Append(columnItems); + + var dataFields = new DataFields(); + foreach (var value in pt.Values) + { + + var df = new DataField() + { + Name = value.CustomName, + Field = (UInt32)pt.SourceRange.Columns().Where(c => c.Cell(1).Value.ToString() == value.SourceName).FirstOrDefault().ColumnNumber() - 1, + BaseField = (Int32)pt.SourceRange.Columns().Where(c => c.Cell(1).Value.ToString() == value.BaseField).FirstOrDefault().ColumnNumber() - 1, + Subtotal = value.SummaryFormula.ToOpenXml(), + ShowDataAs = value.Calculation.ToOpenXml(), + NumberFormatId = (UInt32)value.NumberFormat.NumberFormatId + }; + + if (value.CalculationItem == XLPivotCalculationItem.Previous) + df.BaseItem = 1048828U; + else if (value.CalculationItem == XLPivotCalculationItem.Next) + df.BaseItem = 1048829U; + else + df.BaseItem = (UInt32Value)0U; + + + dataFields.Append(df); + } + pivotTableDefinition.Append(dataFields); + + pivotTableDefinition.Append(new PivotTableStyle() { Name = "PivotStyleLight16", ShowRowHeaders = true, ShowColumnHeaders = true, ShowRowStripes = false, ShowColumnStripes = false, ShowLastColumn = true }); + + #region Excel 2010 Features + + PivotTableDefinitionExtensionList pivotTableDefinitionExtensionList = new PivotTableDefinitionExtensionList(); + + PivotTableDefinitionExtension pivotTableDefinitionExtension = new PivotTableDefinitionExtension() { Uri = "{962EF5D1-5CA2-4c93-8EF4-DBF5C05439D2}" }; + pivotTableDefinitionExtension.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"); + + DocumentFormat.OpenXml.Office2010.Excel.PivotTableDefinition 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.Append(pivotTableDefinition2); + + pivotTableDefinitionExtensionList.Append(pivotTableDefinitionExtension); + pivotTableDefinition.Append(pivotTableDefinitionExtensionList); + + #endregion + + pivotTablePart1.PivotTableDefinition = pivotTableDefinition; + } + } } \ No newline at end of file