diff --git a/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/Excel/Cells/XLCell.cs
index a634817..cc1cb2f 100644
--- a/ClosedXML/Excel/Cells/XLCell.cs
+++ b/ClosedXML/Excel/Cells/XLCell.cs
@@ -1476,7 +1476,6 @@
private IXLStyle GetStyle()
{
- //return _style ?? (_style = new XLStyle(this, Worksheet.Workbook.GetStyleById(_styleCacheId)));
if (_style != null)
return _style;
diff --git a/ClosedXML/Excel/EnumConverter.cs b/ClosedXML/Excel/EnumConverter.cs
index e1384ca..f35d89b 100644
--- a/ClosedXML/Excel/EnumConverter.cs
+++ b/ClosedXML/Excel/EnumConverter.cs
@@ -463,12 +463,12 @@
{
case XLPivotCalculation.Normal: return ShowDataAsValues.Normal;
case XLPivotCalculation.DifferenceFrom: return ShowDataAsValues.Difference;
- case XLPivotCalculation.PctOf: return ShowDataAsValues.Percent;
- case XLPivotCalculation.PctDifferenceFrom: return ShowDataAsValues.PercentageDifference;
+ case XLPivotCalculation.PercentageOf: return ShowDataAsValues.Percent;
+ case XLPivotCalculation.PercentageDifferenceFrom: return ShowDataAsValues.PercentageDifference;
case XLPivotCalculation.RunningTotal: return ShowDataAsValues.RunTotal;
- case XLPivotCalculation.PctOfRow: return ShowDataAsValues.PercentOfRaw; // There's a typo in the OpenXML SDK =)
- case XLPivotCalculation.PctOfColumn: return ShowDataAsValues.PercentOfColumn;
- case XLPivotCalculation.PctOfTotal: return ShowDataAsValues.PercentOfTotal;
+ case XLPivotCalculation.PercentageOfRow: return ShowDataAsValues.PercentOfRaw; // There's a typo in the OpenXML SDK =)
+ case XLPivotCalculation.PercentageOfColumn: return ShowDataAsValues.PercentOfColumn;
+ case XLPivotCalculation.PercentageOfTotal: return ShowDataAsValues.PercentOfTotal;
case XLPivotCalculation.Index: return ShowDataAsValues.Index;
#region default
@@ -1085,12 +1085,12 @@
{
case ShowDataAsValues.Normal: return XLPivotCalculation.Normal;
case ShowDataAsValues.Difference: return XLPivotCalculation.DifferenceFrom;
- case ShowDataAsValues.Percent: return XLPivotCalculation.PctOf;
- case ShowDataAsValues.PercentageDifference: return XLPivotCalculation.PctDifferenceFrom;
+ case ShowDataAsValues.Percent: return XLPivotCalculation.PercentageOf;
+ case ShowDataAsValues.PercentageDifference: return XLPivotCalculation.PercentageDifferenceFrom;
case ShowDataAsValues.RunTotal: return XLPivotCalculation.RunningTotal;
- case ShowDataAsValues.PercentOfRaw: return XLPivotCalculation.PctOfRow; // There's a typo in the OpenXML SDK =)
- case ShowDataAsValues.PercentOfColumn: return XLPivotCalculation.PctOfColumn;
- case ShowDataAsValues.PercentOfTotal: return XLPivotCalculation.PctOfTotal;
+ case ShowDataAsValues.PercentOfRaw: return XLPivotCalculation.PercentageOfRow; // There's a typo in the OpenXML SDK =)
+ case ShowDataAsValues.PercentOfColumn: return XLPivotCalculation.PercentageOfColumn;
+ case ShowDataAsValues.PercentOfTotal: return XLPivotCalculation.PercentageOfTotal;
case ShowDataAsValues.Index: return XLPivotCalculation.Index;
#region default
@@ -1259,4 +1259,4 @@
}
#endregion
}
-}
\ No newline at end of file
+}
diff --git a/ClosedXML/Excel/PageSetup/IXLPageSetup.cs b/ClosedXML/Excel/PageSetup/IXLPageSetup.cs
index 463c0ee..5376f9a 100644
--- a/ClosedXML/Excel/PageSetup/IXLPageSetup.cs
+++ b/ClosedXML/Excel/PageSetup/IXLPageSetup.cs
@@ -76,7 +76,7 @@
public enum XLPageOrderValues { DownThenOver, OverThenDown }
public enum XLShowCommentsValues { None, AtEnd, AsDisplayed }
public enum XLPrintErrorValues { Blank, Dash, Displayed, NA }
-
+
public interface IXLPageSetup
{
///
@@ -178,8 +178,8 @@
/// The worksheet will be printed on as many pages as necessary to print at the given scale.
/// Setting this value will override the PagesWide and PagesTall values.
///
- /// The scale at which the worksheet will be printed.
- void AdjustTo(Int32 pctOfNormalSize);
+ /// The scale at which the worksheet will be printed.
+ void AdjustTo(Int32 percentageOfNormalSize);
///
/// Gets or sets the number of pages the worksheet will be printed on.
/// This is equivalent to setting both PagesWide and PagesTall properties.
diff --git a/ClosedXML/Excel/PageSetup/XLPageSetup.cs b/ClosedXML/Excel/PageSetup/XLPageSetup.cs
index 5eaa80a..9aca46d 100644
--- a/ClosedXML/Excel/PageSetup/XLPageSetup.cs
+++ b/ClosedXML/Excel/PageSetup/XLPageSetup.cs
@@ -8,7 +8,7 @@
{
public XLPageSetup(XLPageSetup defaultPageOptions, XLWorksheet worksheet)
{
-
+
if (defaultPageOptions != null)
{
PrintAreas = new XLPrintAreas(defaultPageOptions.PrintAreas as XLPrintAreas, worksheet);
@@ -23,7 +23,7 @@
_pagesTall = defaultPageOptions.PagesTall;
_pagesWide = defaultPageOptions.PagesWide;
_scale = defaultPageOptions.Scale;
-
+
if (defaultPageOptions.Margins != null)
{
@@ -113,7 +113,7 @@
public IXLMargins Margins { get; set; }
private Int32 _pagesWide;
- public Int32 PagesWide
+ public Int32 PagesWide
{
get
{
@@ -126,7 +126,7 @@
_scale = 0;
}
}
-
+
private Int32 _pagesTall;
public Int32 PagesTall
{
@@ -158,9 +158,9 @@
}
}
- public void AdjustTo(Int32 pctOfNormalSize)
+ public void AdjustTo(Int32 percentageOfNormalSize)
{
- Scale = pctOfNormalSize;
+ Scale = percentageOfNormalSize;
_pagesWide = 0;
_pagesTall = 0;
}
diff --git a/ClosedXML/Excel/PivotTables/PivotValues/IXLPivotValue.cs b/ClosedXML/Excel/PivotTables/PivotValues/IXLPivotValue.cs
index 48c6190..1784044 100644
--- a/ClosedXML/Excel/PivotTables/PivotValues/IXLPivotValue.cs
+++ b/ClosedXML/Excel/PivotTables/PivotValues/IXLPivotValue.cs
@@ -24,12 +24,12 @@
{
Normal,
DifferenceFrom,
- PctOf,
- PctDifferenceFrom,
+ PercentageOf,
+ PercentageDifferenceFrom,
RunningTotal,
- PctOfRow,
- PctOfColumn,
- PctOfTotal,
+ PercentageOfRow,
+ PercentageOfColumn,
+ PercentageOfTotal,
Index
}
public enum XLPivotCalculationItem
@@ -59,12 +59,12 @@
IXLPivotValue ShowAsNormal();
IXLPivotValueCombination ShowAsDifferenceFrom(String fieldSourceName);
- IXLPivotValueCombination ShowAsPctFrom(String fieldSourceName);
- IXLPivotValueCombination ShowAsPctDifferenceFrom(String fieldSourceName);
+ IXLPivotValueCombination ShowAsPercentageFrom(String fieldSourceName);
+ IXLPivotValueCombination ShowAsPercentageDifferenceFrom(String fieldSourceName);
IXLPivotValue ShowAsRunningTotalIn(String fieldSourceName);
- IXLPivotValue ShowAsPctOfRow();
- IXLPivotValue ShowAsPctOfColumn();
- IXLPivotValue ShowAsPctOfTotal();
+ IXLPivotValue ShowAsPercentageOfRow();
+ IXLPivotValue ShowAsPercentageOfColumn();
+ IXLPivotValue ShowAsPercentageOfTotal();
IXLPivotValue ShowAsIndex();
}
diff --git a/ClosedXML/Excel/PivotTables/PivotValues/IXLPivotValueFormat.cs b/ClosedXML/Excel/PivotTables/PivotValues/IXLPivotValueFormat.cs
index 6ccaedf..3c6391c 100644
--- a/ClosedXML/Excel/PivotTables/PivotValues/IXLPivotValueFormat.cs
+++ b/ClosedXML/Excel/PivotTables/PivotValues/IXLPivotValueFormat.cs
@@ -1,13 +1,11 @@
using System;
-using System.Collections.Generic;
-using System.Linq;
-using System.Text;
namespace ClosedXML.Excel
{
- public interface IXLPivotValueFormat: IXLNumberFormatBase
+ public interface IXLPivotValueFormat : IXLNumberFormatBase, IEquatable
{
IXLPivotValue SetNumberFormatId(Int32 value);
+
IXLPivotValue SetFormat(String value);
}
}
diff --git a/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValue.cs b/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValue.cs
index 37ec344..50753cb 100644
--- a/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValue.cs
+++ b/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValue.cs
@@ -14,7 +14,7 @@
}
public IXLPivotValueFormat NumberFormat { get; private set; }
- public String SourceName { get; private set; }
+ public String SourceName { get; private set; }
public String CustomName { get; set; } public IXLPivotValue SetCustomName(String value) { CustomName = value; return this; }
public XLPivotSummary SummaryFormula { get; set; } public IXLPivotValue SetSummaryFormula(XLPivotSummary value) { SummaryFormula = value; return this; }
@@ -34,16 +34,16 @@
SetCalculation(XLPivotCalculation.DifferenceFrom);
return new XLPivotValueCombination(this);
}
- public IXLPivotValueCombination ShowAsPctFrom(String fieldSourceName)
+ public IXLPivotValueCombination ShowAsPercentageFrom(String fieldSourceName)
{
BaseField = fieldSourceName;
- SetCalculation(XLPivotCalculation.PctOf);
+ SetCalculation(XLPivotCalculation.PercentageOf);
return new XLPivotValueCombination(this);
}
- public IXLPivotValueCombination ShowAsPctDifferenceFrom(String fieldSourceName)
+ public IXLPivotValueCombination ShowAsPercentageDifferenceFrom(String fieldSourceName)
{
BaseField = fieldSourceName;
- SetCalculation(XLPivotCalculation.PctDifferenceFrom);
+ SetCalculation(XLPivotCalculation.PercentageDifferenceFrom);
return new XLPivotValueCombination(this);
}
public IXLPivotValue ShowAsRunningTotalIn(String fieldSourceName)
@@ -51,19 +51,19 @@
BaseField = fieldSourceName;
return SetCalculation(XLPivotCalculation.RunningTotal);
}
- public IXLPivotValue ShowAsPctOfRow()
+ public IXLPivotValue ShowAsPercentageOfRow()
{
- return SetCalculation(XLPivotCalculation.PctOfRow);
+ return SetCalculation(XLPivotCalculation.PercentageOfRow);
}
- public IXLPivotValue ShowAsPctOfColumn()
+ public IXLPivotValue ShowAsPercentageOfColumn()
{
- return SetCalculation(XLPivotCalculation.PctOfColumn);
+ return SetCalculation(XLPivotCalculation.PercentageOfColumn);
}
- public IXLPivotValue ShowAsPctOfTotal()
+ public IXLPivotValue ShowAsPercentageOfTotal()
{
- return SetCalculation(XLPivotCalculation.PctOfTotal);
+ return SetCalculation(XLPivotCalculation.PercentageOfTotal);
}
public IXLPivotValue ShowAsIndex()
diff --git a/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValueFormat.cs b/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValueFormat.cs
index 191d46c..7e5b918 100644
--- a/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValueFormat.cs
+++ b/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValueFormat.cs
@@ -78,5 +78,27 @@
return _pivotValue;
}
+ #region Overrides
+ public bool Equals(IXLNumberFormatBase other)
+ {
+ return
+ _numberFormatId == other.NumberFormatId
+ && _format == other.Format
+ ;
+ }
+
+ public override bool Equals(object obj)
+ {
+ return Equals((IXLNumberFormatBase)obj);
+ }
+
+ public override int GetHashCode()
+ {
+ return NumberFormatId
+ ^ Format.GetHashCode();
+ }
+
+ #endregion
+
}
}
diff --git a/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValues.cs b/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValues.cs
index b855923..a7542ba 100644
--- a/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValues.cs
+++ b/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValues.cs
@@ -8,6 +8,14 @@
internal class XLPivotValues: IXLPivotValues
{
private readonly Dictionary _pivotValues = new Dictionary();
+
+ private readonly IXLPivotTable _pivotTable;
+
+ internal XLPivotValues(IXLPivotTable pivotTable)
+ {
+ this._pivotTable = pivotTable;
+ }
+
public IEnumerator GetEnumerator()
{
return _pivotValues.Values.GetEnumerator();
@@ -25,7 +33,11 @@
public IXLPivotValue Add(String sourceName, String customName)
{
var pivotValue = new XLPivotValue(sourceName) { CustomName = customName };
- _pivotValues.Add(sourceName, pivotValue);
+ _pivotValues.Add(customName, pivotValue);
+
+ if (_pivotValues.Count > 1 && !this._pivotTable.ColumnLabels.Any(cl => cl.SourceName == XLConstants.PivotTableValuesSentinalLabel) && !this._pivotTable.RowLabels.Any(rl => rl.SourceName == XLConstants.PivotTableValuesSentinalLabel))
+ _pivotTable.ColumnLabels.Add(XLConstants.PivotTableValuesSentinalLabel);
+
return pivotValue;
}
diff --git a/ClosedXML/Excel/PivotTables/XLPivotTable.cs b/ClosedXML/Excel/PivotTables/XLPivotTable.cs
index dc2b6ee..b6020cd 100644
--- a/ClosedXML/Excel/PivotTables/XLPivotTable.cs
+++ b/ClosedXML/Excel/PivotTables/XLPivotTable.cs
@@ -7,13 +7,14 @@
{
internal class XLPivotTable: IXLPivotTable
{
+
public XLPivotTable()
{
Fields = new XLPivotFields();
ReportFilters = new XLPivotFields();
ColumnLabels=new XLPivotFields();
RowLabels = new XLPivotFields();
- Values = new XLPivotValues();
+ Values = new XLPivotValues(this);
Theme = XLPivotTableTheme.PivotStyleLight16;
SetExcelDefaults();
diff --git a/ClosedXML/Excel/Style/IXLNumberFormat.cs b/ClosedXML/Excel/Style/IXLNumberFormat.cs
index 1490413..2a8ef5e 100644
--- a/ClosedXML/Excel/Style/IXLNumberFormat.cs
+++ b/ClosedXML/Excel/Style/IXLNumberFormat.cs
@@ -2,9 +2,10 @@
namespace ClosedXML.Excel
{
- public interface IXLNumberFormat: IXLNumberFormatBase, IEquatable
+ public interface IXLNumberFormat : IXLNumberFormatBase, IEquatable
{
IXLStyle SetNumberFormatId(Int32 value);
+
IXLStyle SetFormat(String value);
}
}
diff --git a/ClosedXML/Excel/Style/XLNumberFormat.cs b/ClosedXML/Excel/Style/XLNumberFormat.cs
index 1b6620a..7a15cc7 100644
--- a/ClosedXML/Excel/Style/XLNumberFormat.cs
+++ b/ClosedXML/Excel/Style/XLNumberFormat.cs
@@ -6,13 +6,11 @@
{
#region IXLNumberFormat Members
- public bool Equals(IXLNumberFormat other)
+ public bool Equals(IXLNumberFormatBase other)
{
- var otherNf = other as XLNumberFormat;
-
return
- _numberFormatId == otherNf._numberFormatId
- && _format == otherNf._format
+ _numberFormatId == other.NumberFormatId
+ && _format == other.Format
;
}
@@ -25,7 +23,7 @@
public override bool Equals(object obj)
{
- return Equals((XLNumberFormat)obj);
+ return Equals((IXLNumberFormatBase)obj);
}
public override int GetHashCode()
@@ -114,4 +112,4 @@
#endregion
}
-}
\ No newline at end of file
+}
diff --git a/ClosedXML/Excel/XLConstants.cs b/ClosedXML/Excel/XLConstants.cs
index f01f06e..d1cbe58 100644
--- a/ClosedXML/Excel/XLConstants.cs
+++ b/ClosedXML/Excel/XLConstants.cs
@@ -1,9 +1,10 @@
-
-namespace ClosedXML.Excel
+namespace ClosedXML.Excel
{
//Use the class to store magic strings or variables.
- internal static class XLConstants
+ public static class XLConstants
{
+ public const string PivotTableValuesSentinalLabel = "{{Values}}";
+
internal static class Comment
{
internal const string ShapeTypeId = "#_x0000_t202";
diff --git a/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs b/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs
index 356424c..9bcb8a2 100644
--- a/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs
+++ b/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs
@@ -16,6 +16,8 @@
[DebuggerBrowsable(DebuggerBrowsableState.Never)]
private readonly Dictionary _sharedStyles;
[DebuggerBrowsable(DebuggerBrowsableState.Never)]
+ private readonly Dictionary _sharedNumberFormats;
+ [DebuggerBrowsable(DebuggerBrowsableState.Never)]
private readonly Dictionary _sharedFonts;
[DebuggerBrowsable(DebuggerBrowsableState.Never)]
private readonly HashSet _tableNames;
@@ -27,6 +29,7 @@
{
_relIdGenerator = new RelIdGenerator();
_sharedStyles = new Dictionary();
+ _sharedNumberFormats = new Dictionary();
_sharedFonts = new Dictionary();
_tableNames = new HashSet();
_tableId = 0;
@@ -43,6 +46,11 @@
[DebuggerStepThrough]
get { return _sharedStyles; }
}
+ public Dictionary SharedNumberFormats
+ {
+ [DebuggerStepThrough]
+ get { return _sharedNumberFormats; }
+ }
public Dictionary SharedFonts
{
[DebuggerStepThrough]
@@ -133,7 +141,7 @@
internal struct NumberFormatInfo
{
public Int32 NumberFormatId;
- public IXLNumberFormat NumberFormat;
+ public IXLNumberFormatBase NumberFormat;
}
#endregion
#region Nested type: StyleInfo
diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs
index 1a4f3bc..0e08ae1 100644
--- a/ClosedXML/Excel/XLWorkbook_Save.cs
+++ b/ClosedXML/Excel/XLWorkbook_Save.cs
@@ -1832,7 +1832,7 @@
var pivotTablePart =
worksheetPart.AddNewPart(context.RelIdGenerator.GetNext(RelType.Workbook));
- GeneratePivotTablePartContent(pivotTablePart, pt, pivotCache.CacheId);
+ GeneratePivotTablePartContent(pivotTablePart, pt, pivotCache.CacheId, context);
pivotTablePart.AddPart(pivotTableCacheDefinitionPart, context.RelIdGenerator.GetNext(RelType.Workbook));
}
@@ -1926,7 +1926,7 @@
}
// Generates content of pivotTablePart
- private static void GeneratePivotTablePartContent(PivotTablePart pivotTablePart1, IXLPivotTable pt, uint cacheId)
+ private static void GeneratePivotTablePartContent(PivotTablePart pivotTablePart, IXLPivotTable pt, uint cacheId, SaveContext context)
{
var pivotTableDefinition = new PivotTableDefinition
{
@@ -1993,12 +1993,12 @@
var columnFields = new ColumnFields();
var rowItems = new RowItems();
var columnItems = new ColumnItems();
- var pageFields = new PageFields {Count = (uint)pt.ReportFilters.Count()};
-
+ 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.OrderBy(f => pt.RowLabels.Any(p => p.SourceName == f.SourceName) ? pt.RowLabels.IndexOf(f) : Int32.MaxValue ))
{
- if (pt.RowLabels.FirstOrDefault(p => p.SourceName == xlpf.SourceName) != null)
+ if (pt.RowLabels.Any(p => p.SourceName == xlpf.SourceName))
{
var f = new Field {Index = pt.Fields.IndexOf(xlpf)};
rowFields.AppendChild(f);
@@ -2014,9 +2014,9 @@
rowItemTotal.AppendChild(new MemberPropertyIndex());
rowItems.AppendChild(rowItemTotal);
}
- else if (pt.ColumnLabels.FirstOrDefault(p => p.SourceName == xlpf.SourceName) != null)
+ else if (pt.ColumnLabels.Any(p => p.SourceName == xlpf.SourceName))
{
- var f = new Field {Index = pt.Fields.IndexOf(xlpf)};
+ var f = new Field { Index = pt.Fields.IndexOf(xlpf) };
columnFields.AppendChild(f);
for (var i = 0; i < xlpf.SharedStrings.Count; i++)
@@ -2032,26 +2032,38 @@
}
}
+ if (pt.Values.Count() > 1)
+ {
+ // -2 is the sentinal value for "Values"
+ if (pt.ColumnLabels.Any(cl => cl.SourceName == XLConstants.PivotTableValuesSentinalLabel))
+ columnFields.AppendChild(new Field { Index = -2 });
+ else if (pt.RowLabels.Any(rl => rl.SourceName == XLConstants.PivotTableValuesSentinalLabel))
+ {
+ pivotTableDefinition.DataOnRows = true;
+ rowFields.AppendChild(new Field { Index = -2 });
+ }
+ }
+
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)
+ if (pt.RowLabels.Any(p => p.SourceName == xlpf.SourceName))
{
pf.Axis = PivotTableAxisValues.AxisRow;
}
- else if (pt.ColumnLabels.FirstOrDefault(p => p.SourceName == xlpf.SourceName) != null)
+ else if (pt.ColumnLabels.Any(p => p.SourceName == xlpf.SourceName))
{
pf.Axis = PivotTableAxisValues.AxisColumn;
}
- else if (pt.ReportFilters.FirstOrDefault(p => p.SourceName == xlpf.SourceName) != null)
+ else if (pt.ReportFilters.Any(p => p.SourceName == xlpf.SourceName))
{
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)
+ else if (pt.Values.Any(p => p.SourceName == xlpf.SourceName))
{
pf.DataField = true;
}
@@ -2126,6 +2138,7 @@
fieldItems.AppendChild(new Item {ItemType = ItemValues.Default});
}
+ fieldItems.Count = Convert.ToUInt32(fieldItems.Count());
pf.AppendChild(fieldItems);
pivotFields.AppendChild(pf);
}
@@ -2135,27 +2148,43 @@
if (pt.RowLabels.Any())
{
+ rowFields.Count = Convert.ToUInt32(rowFields.Count());
pivotTableDefinition.AppendChild(rowFields);
}
else
{
rowItems.AppendChild(new RowItem());
}
+
+ rowItems.Count = Convert.ToUInt32(rowItems.Count());
pivotTableDefinition.AppendChild(rowItems);
- if (!pt.ColumnLabels.Any())
+ if (!pt.ColumnLabels.Any(cl => cl.CustomName != XLConstants.PivotTableValuesSentinalLabel))
{
- columnItems.AppendChild(new RowItem());
- pivotTableDefinition.AppendChild(columnItems);
+ for (int i = 0; i < pt.Values.Count(); i++)
+ {
+ var rowItem = new RowItem();
+ rowItem.Index = Convert.ToUInt32(i);
+ rowItem.AppendChild(new MemberPropertyIndex() { Val = i });
+ columnItems.AppendChild(rowItem);
+ }
}
- else
+
+ if (columnFields.Any())
{
+ columnFields.Count = Convert.ToUInt32(columnFields.Count());
pivotTableDefinition.AppendChild(columnFields);
+ }
+
+ if (columnItems.Any())
+ {
+ columnItems.Count = Convert.ToUInt32(columnItems.Count());
pivotTableDefinition.AppendChild(columnItems);
}
if (pt.ReportFilters.Any())
{
+ pageFields.Count = Convert.ToUInt32(pageFields.Count());
pivotTableDefinition.AppendChild(pageFields);
}
@@ -2167,21 +2196,36 @@
pt.SourceRange.Columns().FirstOrDefault(c => c.Cell(1).Value.ToString() == value.SourceName);
if (sourceColumn == null) continue;
+ UInt32 numberFormatId = 0;
+ if (value.NumberFormat.NumberFormatId != -1 || context.SharedNumberFormats.ContainsKey(value.NumberFormat.NumberFormatId))
+ numberFormatId = (UInt32)value.NumberFormat.NumberFormatId;
+ else if (context.SharedNumberFormats.Any(snf => snf.Value.NumberFormat.Format == value.NumberFormat.Format))
+ numberFormatId = (UInt32)context.SharedNumberFormats.First(snf => snf.Value.NumberFormat.Format == value.NumberFormat.Format).Key;
+
var df = new DataField
{
- Name = value.SourceName,
+ Name = value.CustomName,
Field = (UInt32)sourceColumn.ColumnNumber() - 1,
Subtotal = value.SummaryFormula.ToOpenXml(),
ShowDataAs = value.Calculation.ToOpenXml(),
- NumberFormatId = (UInt32)value.NumberFormat.NumberFormatId
+ NumberFormatId = numberFormatId
};
if (!String.IsNullOrEmpty(value.BaseField))
{
- var baseField =
- pt.SourceRange.Columns().FirstOrDefault(c => c.Cell(1).Value.ToString() == value.BaseField);
+ var baseField = pt.SourceRange.Columns().FirstOrDefault(c => c.Cell(1).Value.ToString() == value.BaseField);
if (baseField != null)
+ {
df.BaseField = baseField.ColumnNumber() - 1;
+
+ var items = baseField.CellsUsed()
+ .Select(c => c.Value)
+ .Skip(1) // Skip header column
+ .Distinct().ToList();
+
+ if (items.Any(i => i.Equals(value.BaseItem)))
+ df.BaseItem = Convert.ToUInt32(items.IndexOf(value.BaseItem));
+ }
}
else
{
@@ -2192,12 +2236,13 @@
df.BaseItem = 1048828U;
else if (value.CalculationItem == XLPivotCalculationItem.Next)
df.BaseItem = 1048829U;
- else
+ else if (df.BaseItem == null || !df.BaseItem.HasValue)
df.BaseItem = 0U;
-
dataFields.AppendChild(df);
}
+
+ dataFields.Count = Convert.ToUInt32(dataFields.Count());
pivotTableDefinition.AppendChild(dataFields);
pivotTableDefinition.AppendChild(new PivotTableStyle
@@ -2229,7 +2274,7 @@
#endregion
- pivotTablePart1.PivotTableDefinition = pivotTableDefinition;
+ pivotTablePart.PivotTableDefinition = pivotTableDefinition;
}
@@ -2505,7 +2550,7 @@
var sharedBorders = new Dictionary
{{defaultStyle.Border, new BorderInfo {BorderId = 0, Border = defaultStyle.Border as XLBorder}}};
- var sharedNumberFormats = new Dictionary
+ var sharedNumberFormats = new Dictionary
{
{
defaultStyle.NumberFormat,
@@ -2556,6 +2601,7 @@
UInt32 borderCount = 1;
var numberFormatCount = 1;
var xlStyles = new HashSet();
+ var pivotTableNumberFormats = new HashSet();
foreach (var worksheet in WorksheetsInternal)
{
@@ -2574,6 +2620,24 @@
s => !xlStyles.Contains(s))
)
xlStyles.Add(s);
+
+ foreach (var ptnf in worksheet.PivotTables.SelectMany(pt => pt.Values.Select(ptv => ptv.NumberFormat)).Distinct().Where(nf => !pivotTableNumberFormats.Contains(nf)))
+ pivotTableNumberFormats.Add(ptnf);
+ }
+
+ foreach (var numberFormat in pivotTableNumberFormats)
+ {
+ if (numberFormat.NumberFormatId != -1
+ || sharedNumberFormats.ContainsKey(numberFormat))
+ continue;
+
+ sharedNumberFormats.Add(numberFormat,
+ new NumberFormatInfo
+ {
+ NumberFormatId = numberFormatCount + 164,
+ NumberFormat = numberFormat
+ });
+ numberFormatCount++;
}
foreach (var xlStyle in xlStyles.Select(GetStyleById))
@@ -2603,6 +2667,11 @@
}
var allSharedNumberFormats = ResolveNumberFormats(workbookStylesPart, sharedNumberFormats, defaultFormatId);
+ foreach (var nf in allSharedNumberFormats)
+ {
+ context.SharedNumberFormats.Add(nf.Value.NumberFormatId, nf.Value);
+ }
+
ResolveFonts(workbookStylesPart, context);
var allSharedFills = ResolveFills(workbookStylesPart, sharedFills);
var allSharedBorders = ResolveBorders(workbookStylesPart, sharedBorders);
@@ -3300,9 +3369,9 @@
return nf.Equals(xlFont);
}
- private static Dictionary ResolveNumberFormats(
+ private static Dictionary ResolveNumberFormats(
WorkbookStylesPart workbookStylesPart,
- Dictionary sharedNumberFormats,
+ Dictionary sharedNumberFormats,
UInt32 defaultFormatId)
{
if (workbookStylesPart.Stylesheet.NumberingFormats == null)
@@ -3315,7 +3384,7 @@
});
}
- var allSharedNumberFormats = new Dictionary();
+ var allSharedNumberFormats = new Dictionary();
foreach (var numberFormatInfo in sharedNumberFormats.Values.Where(nf => nf.NumberFormatId != defaultFormatId))
{
var numberingFormatId = 164;
@@ -3351,7 +3420,7 @@
return allSharedNumberFormats;
}
- private static bool NumberFormatsAreEqual(NumberingFormat nf, IXLNumberFormat xlNumberFormat)
+ private static bool NumberFormatsAreEqual(NumberingFormat nf, IXLNumberFormatBase xlNumberFormat)
{
var newXLNumberFormat = new XLNumberFormat();
@@ -3755,7 +3824,7 @@
}
var distinctRows = xlWorksheet.Internals.CellsCollection.RowsCollection.Keys.Union(xlWorksheet.Internals.RowsCollection.Keys);
- var noRows = (sheetData.Elements().FirstOrDefault() == null);
+ var noRows = !sheetData.Elements().Any();
foreach (var distinctRow in distinctRows.OrderBy(r => r))
{
Row row;
diff --git a/ClosedXML_Examples/PivotTables/PivotTables.cs b/ClosedXML_Examples/PivotTables/PivotTables.cs
index 4649a17..5678228 100644
--- a/ClosedXML_Examples/PivotTables/PivotTables.cs
+++ b/ClosedXML_Examples/PivotTables/PivotTables.cs
@@ -1,37 +1,110 @@
using ClosedXML.Excel;
using System;
+using System.Collections.Generic;
namespace ClosedXML_Examples
{
public class PivotTables : IXLExample
{
- public void Create(String filePath)
+ private class Pastry
{
- var wb = new XLWorkbook();
-
- var wsData = wb.Worksheets.Add("Data");
- wsData.Cell("A1").Value = "Category";
- wsData.Cell("A2").Value = "A";
- wsData.Cell("A3").Value = "B";
- wsData.Cell("A4").Value = "B";
- wsData.Cell("B1").Value = "Number";
- wsData.Cell("B2").Value = 100;
- wsData.Cell("B3").Value = 150;
- wsData.Cell("B4").Value = 75;
- var source = wsData.Range("A1:B4");
-
- for (int i = 1; i <= 3; i++)
+ public Pastry(string name, int numberOfOrders, double quality, string month)
{
- var name = "PT" + i;
- var wsPT = wb.Worksheets.Add(name);
- var pt = wsPT.PivotTables.AddNew(name, wsPT.Cell("A1"), source);
- pt.RowLabels.Add("Category");
- pt.Values.Add("Number")
- .ShowAsPctFrom("Category").And("A")
- .NumberFormat.Format = "0%";
+ Name = name;
+ NumberOfOrders = numberOfOrders;
+ Quality = quality;
+ Month = month;
}
- wb.SaveAs(filePath);
+ public string Name { get; set; }
+ public int NumberOfOrders { get; set; }
+ public double Quality { get; set; }
+ public string Month { get; set; }
+ }
+
+ public void Create(String filePath)
+ {
+ var pastries = new List
+ {
+ new Pastry("Croissant", 150, 60.2, "Apr"),
+ new Pastry("Croissant", 250, 50.42, "May"),
+ new Pastry("Croissant", 134, 22.12, "June"),
+ new Pastry("Doughnut", 250, 89.99, "Apr"),
+ new Pastry("Doughnut", 225, 70, "May"),
+ new Pastry("Doughnut", 210, 75.33, "June"),
+ new Pastry("Bearclaw", 134, 10.24, "Apr"),
+ new Pastry("Bearclaw", 184, 33.33, "May"),
+ new Pastry("Bearclaw", 124, 25, "June"),
+ new Pastry("Danish", 394, -20.24, "Apr"),
+ new Pastry("Danish", 190, 60, "May"),
+ new Pastry("Danish", 221, 24.76, "June"),
+ new Pastry("Scone", 135, 0, "Apr"),
+ new Pastry("Scone", 122, 5.19, "May"),
+ new Pastry("Scone", 243, 44.2, "June")
+ };
+
+ using (var wb = new XLWorkbook())
+ {
+ var sheet = wb.Worksheets.Add("PastrySalesData");
+ // Insert our list of pastry data into the "PastrySalesData" sheet at cell 1,1
+ var source = sheet.Cell(1, 1).InsertTable(pastries, "PastrySalesData", true);
+ sheet.Columns().AdjustToContents();
+
+ // Create a range that includes our table, including the header row
+ var range = source.DataRange;
+ var header = sheet.Range(1, 1, 1, 3);
+ var dataRange = sheet.Range(header.FirstCell(), range.LastCell());
+
+ IXLWorksheet ptSheet;
+ IXLPivotTable pt;
+
+ for (int i = 1; i <= 3; i++)
+ {
+ // Add a new sheet for our pivot table
+ ptSheet = wb.Worksheets.Add("PivotTable" + i);
+
+ // Create the pivot table, using the data from the "PastrySalesData" table
+ pt = ptSheet.PivotTables.AddNew("PivotTable", ptSheet.Cell(1, 1), dataRange);
+
+ // The rows in our pivot table will be the names of the pastries
+ pt.RowLabels.Add("Name");
+ if (i == 2) pt.RowLabels.Add(XLConstants.PivotTableValuesSentinalLabel);
+
+ // The columns will be the months
+ pt.ColumnLabels.Add("Month");
+ if (i == 3) pt.ColumnLabels.Add(XLConstants.PivotTableValuesSentinalLabel);
+
+ // The values in our table will come from the "NumberOfOrders" field
+ // The default calculation setting is a total of each row/column
+ pt.Values.Add("NumberOfOrders", "NumberOfOrdersPercentageOfBearclaw")
+ .ShowAsPercentageFrom("Name").And("Bearclaw")
+ .NumberFormat.Format = "0%";
+
+ if (i > 1)
+ {
+ pt.Values.Add("Quality", "Sum of Quality")
+ .NumberFormat.SetFormat("#,##0.00");
+ }
+ if (i > 2)
+ {
+ pt.Values.Add("NumberOfOrders", "Sum of NumberOfOrders");
+ }
+
+ ptSheet.Columns().AdjustToContents();
+ }
+
+ // Different kind of pivot
+ ptSheet = wb.Worksheets.Add("PivotTableNoColumnLabels");
+ pt = ptSheet.PivotTables.AddNew("PivotTableNoColumnLabels", ptSheet.Cell(1, 1), dataRange);
+
+ pt.RowLabels.Add("Name");
+ pt.RowLabels.Add("Month");
+
+ pt.Values.Add("NumberOfOrders").SetSummaryFormula(XLPivotSummary.Sum);//.NumberFormat.Format = "#0.00";
+ pt.Values.Add("Quality").SetSummaryFormula(XLPivotSummary.Sum);//.NumberFormat.Format = "#0.00";
+
+ wb.SaveAs(filePath);
+ }
}
}
}
diff --git a/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx
index 43136a8..c4025e5 100644
--- a/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx
+++ b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx
Binary files differ