diff --git a/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML.csproj
index 238169d..a4b4662 100644
--- a/ClosedXML/ClosedXML.csproj
+++ b/ClosedXML/ClosedXML.csproj
@@ -51,6 +51,10 @@
..\packages\DocumentFormat.OpenXml.2.7.2\lib\net40\DocumentFormat.OpenXml.dll
True
+
+ ..\packages\ExcelNumberFormat.1.0.3\lib\net20\ExcelNumberFormat.dll
+ True
+
..\packages\FastMember.Signed.1.1.0\lib\net40\FastMember.Signed.dll
True
@@ -101,6 +105,7 @@
+
diff --git a/ClosedXML/Excel/CalcEngine/Expression.cs b/ClosedXML/Excel/CalcEngine/Expression.cs
index b9a29ca..ff1288c 100644
--- a/ClosedXML/Excel/CalcEngine/Expression.cs
+++ b/ClosedXML/Excel/CalcEngine/Expression.cs
@@ -113,6 +113,11 @@
return ((DateTime)v).ToOADate();
}
+ if (v is TimeSpan)
+ {
+ return ((TimeSpan)v).TotalDays;
+ }
+
// handle nulls
if (v == null || v is string)
{
diff --git a/ClosedXML/Excel/CalcEngine/Functions/Text.cs b/ClosedXML/Excel/CalcEngine/Functions/Text.cs
index bbf58ca..552c79d 100644
--- a/ClosedXML/Excel/CalcEngine/Functions/Text.cs
+++ b/ClosedXML/Excel/CalcEngine/Functions/Text.cs
@@ -1,4 +1,5 @@
using ClosedXML.Excel.CalcEngine.Exceptions;
+using ExcelNumberFormat;
using System;
using System.Collections.Generic;
using System.Globalization;
@@ -267,14 +268,12 @@
var format = (string)p[1];
if (string.IsNullOrEmpty(format.Trim())) return "";
- // We'll have to guess as to whether the format represents a date and/or time.
- // Not sure whether there's a better way to detect this.
- bool isDateFormat = new string[] { "y", "m", "d", "h", "s" }.Any(part => format.ToLower().Contains(part.ToLower()));
+ var nf = new NumberFormat(format);
- if (isDateFormat)
- return DateTime.FromOADate(number).ToString(format, CultureInfo.CurrentCulture);
+ if (nf.IsDateTimeFormat)
+ return nf.Format(DateTime.FromOADate(number), CultureInfo.InvariantCulture);
else
- return number.ToString(format, CultureInfo.CurrentCulture);
+ return nf.Format(number, CultureInfo.InvariantCulture);
}
private static object Trim(List p)
diff --git a/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/Excel/Cells/XLCell.cs
index 39e00c1..911910b 100644
--- a/ClosedXML/Excel/Cells/XLCell.cs
+++ b/ClosedXML/Excel/Cells/XLCell.cs
@@ -320,36 +320,36 @@
cValue = _cellValue;
}
+ var format = GetFormat();
+
if (_dataType == XLDataType.Boolean)
- return (cValue != "0").ToString();
- if (_dataType == XLDataType.TimeSpan)
- return cValue;
- if (_dataType == XLDataType.DateTime || IsDateFormat())
+ return (cValue != "0").ToExcelFormat(format);
+
+ else if (_dataType == XLDataType.TimeSpan || _dataType == XLDataType.DateTime || IsDateFormat())
{
double dTest;
if (Double.TryParse(cValue, XLHelper.NumberStyle, XLHelper.ParseCulture, out dTest)
&& dTest.IsValidOADateNumber())
{
- var format = GetFormat();
- return DateTime.FromOADate(dTest).ToString(format);
+ return DateTime.FromOADate(dTest).ToExcelFormat(format);
}
return cValue;
}
- if (_dataType == XLDataType.Number)
+ else if (_dataType == XLDataType.Number)
{
double dTest;
if (Double.TryParse(cValue, XLHelper.NumberStyle, XLHelper.ParseCulture, out dTest))
{
- var format = GetFormat();
- return dTest.ToString(format);
+ return dTest.ToExcelFormat(format);
}
return cValue;
}
- return cValue;
+ else
+ return cValue;
}
public object Value
@@ -1748,7 +1748,7 @@
Worksheet.Cell(
Address.RowNumber + sourceCell.Address.RowNumber - minRow,
Address.ColumnNumber + sourceCell.Address.ColumnNumber - minColumn
- ).CopyFrom(sourceCell, true);
+ ).CopyFromInternal(sourceCell as XLCell, true);
}
var rangesToMerge = (from mergedRange in (asRange.Worksheet).Internals.MergedRanges
@@ -1765,7 +1765,9 @@
Worksheet.Range(initialRo, initialCo, initialRo + mergedRange.RowCount() - 1,
initialCo + mergedRange.ColumnCount() - 1)).Cast().
ToList();
- rangesToMerge.ForEach(r => r.Merge());
+ rangesToMerge.ForEach(r => r.Merge(false));
+
+ CopyConditionalFormatsFrom(asRange);
return true;
}
@@ -1773,6 +1775,72 @@
return false;
}
+ private void CopyConditionalFormatsFrom(XLRangeBase fromRange)
+ {
+ var srcSheet = fromRange.Worksheet;
+ int minRo = fromRange.RangeAddress.FirstAddress.RowNumber;
+ int minCo = fromRange.RangeAddress.FirstAddress.ColumnNumber;
+ if (srcSheet.ConditionalFormats.Any(r => r.Range.Intersects(fromRange)))
+ {
+ var fs = srcSheet.ConditionalFormats.Where(r => r.Range.Intersects(fromRange)).ToArray();
+ if (fs.Any())
+ {
+ minRo = fs.Max(r => r.Range.RangeAddress.LastAddress.RowNumber);
+ minCo = fs.Max(r => r.Range.RangeAddress.LastAddress.ColumnNumber);
+ }
+ }
+ int rCnt = minRo - fromRange.RangeAddress.FirstAddress.RowNumber + 1;
+ int cCnt = minCo - fromRange.RangeAddress.FirstAddress.ColumnNumber + 1;
+ rCnt = Math.Min(rCnt, fromRange.RowCount());
+ cCnt = Math.Min(cCnt, fromRange.ColumnCount());
+ var toRange = Worksheet.Range(this, Worksheet.Cell(Address.RowNumber + rCnt - 1, Address.ColumnNumber + cCnt - 1));
+ var formats = srcSheet.ConditionalFormats.Where(f => f.Range.Intersects(fromRange));
+ foreach (var cf in formats.ToList())
+ {
+ var fmtRange = Relative(Intersection(cf.Range, fromRange), fromRange, toRange);
+ var c = new XLConditionalFormat((XLRange) fmtRange, true);
+ c.CopyFrom(cf);
+ foreach (var v in c.Values.ToList())
+ {
+ var f = v.Value.Value;
+ if (v.Value.IsFormula)
+ {
+ var r1c1 = ((XLCell) cf.Range.FirstCell()).GetFormulaR1C1(f);
+ f = ((XLCell)fmtRange.FirstCell()).GetFormulaA1(r1c1);
+ }
+
+ c.Values[v.Key] = new XLFormula {_value = f, IsFormula = v.Value.IsFormula};
+ }
+
+ _worksheet.ConditionalFormats.Add(c);
+ }
+ }
+
+ private static IXLRangeBase Intersection(IXLRangeBase range, IXLRangeBase crop)
+ {
+ var sheet = range.Worksheet;
+ using (var xlRange = sheet.Range(
+ Math.Max(range.RangeAddress.FirstAddress.RowNumber, crop.RangeAddress.FirstAddress.RowNumber),
+ Math.Max(range.RangeAddress.FirstAddress.ColumnNumber, crop.RangeAddress.FirstAddress.ColumnNumber),
+ Math.Min(range.RangeAddress.LastAddress.RowNumber, crop.RangeAddress.LastAddress.RowNumber),
+ Math.Min(range.RangeAddress.LastAddress.ColumnNumber, crop.RangeAddress.LastAddress.ColumnNumber)))
+ {
+ return sheet.Range(xlRange.RangeAddress);
+ }
+ }
+
+ private static IXLRange Relative(IXLRangeBase range, IXLRangeBase baseRange, IXLRangeBase targetBase)
+ {
+ using (var xlRange = targetBase.Worksheet.Range(
+ range.RangeAddress.FirstAddress.RowNumber - baseRange.RangeAddress.FirstAddress.RowNumber + 1,
+ range.RangeAddress.FirstAddress.ColumnNumber - baseRange.RangeAddress.FirstAddress.ColumnNumber + 1,
+ range.RangeAddress.LastAddress.RowNumber - baseRange.RangeAddress.FirstAddress.RowNumber + 1,
+ range.RangeAddress.LastAddress.ColumnNumber - baseRange.RangeAddress.FirstAddress.ColumnNumber + 1))
+ {
+ return ((XLRangeBase)targetBase).Range(xlRange.RangeAddress);
+ }
+ }
+
private bool SetDataTable(object o)
{
var dataTable = o as DataTable;
@@ -1882,7 +1950,7 @@
_cellValue = val;
}
- private string GetFormulaR1C1(string value)
+ internal string GetFormulaR1C1(string value)
{
return GetFormula(value, FormulaConversionType.A1ToR1C1, 0, 0);
}
@@ -2106,14 +2174,35 @@
return defaultWorksheet.Workbook.Worksheet(wsName).Cell(pair[1]);
}
+ internal IXLCell CopyFromInternal(XLCell otherCell, Boolean copyDataValidations)
+ {
+ CopyValuesFrom(otherCell);
+
+ if (otherCell._styleCacheId.HasValue)
+ SetStyle(otherCell._style ?? otherCell.Worksheet.Workbook.GetStyleById(otherCell._styleCacheId.Value));
+
+ if (copyDataValidations)
+ {
+ var eventTracking = Worksheet.EventTrackingEnabled;
+ Worksheet.EventTrackingEnabled = false;
+ if (otherCell.HasDataValidation)
+ CopyDataValidation(otherCell, otherCell.DataValidation);
+ else if (HasDataValidation)
+ {
+ using (var asRange = AsRange())
+ Worksheet.DataValidations.Delete(asRange);
+ }
+ Worksheet.EventTrackingEnabled = eventTracking;
+ }
+
+ return this;
+ }
+
public IXLCell CopyFrom(IXLCell otherCell, Boolean copyDataValidations)
{
var source = otherCell as XLCell; // To expose GetFormulaR1C1, etc
- CopyValuesFrom(source);
-
- if (source._styleCacheId.HasValue)
- SetStyle(source._style ?? source.Worksheet.Workbook.GetStyleById(source._styleCacheId.Value));
+ CopyFromInternal(source, copyDataValidations);
var conditionalFormats = source.Worksheet.ConditionalFormats.Where(c => c.Range.Contains(source)).ToList();
foreach (var cf in conditionalFormats)
@@ -2136,20 +2225,6 @@
_worksheet.ConditionalFormats.Add(c);
}
- if (copyDataValidations)
- {
- var eventTracking = Worksheet.EventTrackingEnabled;
- Worksheet.EventTrackingEnabled = false;
- if (source.HasDataValidation)
- CopyDataValidation(source, source.DataValidation);
- else if (HasDataValidation)
- {
- using (var asRange = AsRange())
- Worksheet.DataValidations.Delete(asRange);
- }
- Worksheet.EventTrackingEnabled = eventTracking;
- }
-
return this;
}
diff --git a/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs b/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs
index 03aa8ca..5023880 100644
--- a/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs
+++ b/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs
@@ -1,11 +1,108 @@
using System;
using System.Collections.Generic;
+using System.Linq;
using ClosedXML.Utils;
namespace ClosedXML.Excel
{
internal class XLConditionalFormat : IXLConditionalFormat, IXLStylized
{
+ private sealed class FullEqualityComparer : IEqualityComparer
+ {
+ private readonly bool _compareRange;
+ private readonly DictionaryComparer _colorsComparer = new DictionaryComparer();
+ private readonly EnumerableComparer _listComparer = new EnumerableComparer();
+ private readonly DictionaryComparer _contentsTypeComparer = new DictionaryComparer();
+ private readonly DictionaryComparer _iconSetTypeComparer = new DictionaryComparer();
+
+ public FullEqualityComparer(bool compareRange)
+ {
+ _compareRange = compareRange;
+ }
+
+ public bool Equals(IXLConditionalFormat x, IXLConditionalFormat y)
+ {
+ var xx = (XLConditionalFormat) x;
+ var yy = (XLConditionalFormat) y;
+ if (ReferenceEquals(xx, yy)) return true;
+ if (ReferenceEquals(xx, null)) return false;
+ if (ReferenceEquals(yy, null)) return false;
+ if (xx.GetType() != yy.GetType()) return false;
+
+ var xxValues = xx.Values.Values.Where(v => !v.IsFormula).Select(v=>v.Value);
+ var yyValues = yy.Values.Values.Where(v => !v.IsFormula).Select(v => v.Value);
+ var xxFormulas = xx.Values.Values.Where(v => v.IsFormula).Select(f => ((XLCell)x.Range.FirstCell()).GetFormulaR1C1(f.Value));
+ var yyFormulas = yy.Values.Values.Where(v => v.IsFormula).Select(f => ((XLCell)y.Range.FirstCell()).GetFormulaR1C1(f.Value));
+
+ var xStyle = xx._style ?? xx.Range.Worksheet.Workbook.GetStyleById(xx._styleCacheId);
+ var yStyle = yy._style ?? yy.Range.Worksheet.Workbook.GetStyleById(yy._styleCacheId);
+
+ return Equals(xStyle, yStyle)
+ && xx.CopyDefaultModify == yy.CopyDefaultModify
+ && xx.UpdatingStyle == yy.UpdatingStyle
+ && xx.ConditionalFormatType == yy.ConditionalFormatType
+ && xx.TimePeriod == yy.TimePeriod
+ && xx.IconSetStyle == yy.IconSetStyle
+ && xx.Operator == yy.Operator
+ && xx.Bottom == yy.Bottom
+ && xx.Percent == yy.Percent
+ && xx.ReverseIconOrder == yy.ReverseIconOrder
+ && xx.StopIfTrueInternal == yy.StopIfTrueInternal
+ && xx.ShowIconOnly == yy.ShowIconOnly
+ && xx.ShowBarOnly == yy.ShowBarOnly
+ && _listComparer.Equals(xxValues, yyValues)
+ && _listComparer.Equals(xxFormulas, yyFormulas)
+ && _colorsComparer.Equals(xx.Colors, yy.Colors)
+ && _contentsTypeComparer.Equals(xx.ContentTypes, yy.ContentTypes)
+ && _iconSetTypeComparer.Equals(xx.IconSetOperators, yy.IconSetOperators)
+ && (!_compareRange || Equals(xx.Range.RangeAddress, yy.Range.RangeAddress)) ;
+ }
+
+ public int GetHashCode(IXLConditionalFormat obj)
+ {
+ var xx = (XLConditionalFormat)obj;
+ var xStyle = xx._style ?? xx.Range.Worksheet.Workbook.GetStyleById(xx._styleCacheId);
+ var xValues = xx.Values.Values.Where(v => !v.IsFormula).Select(v => v.Value)
+ .Union(xx.Values.Values.Where(v => v.IsFormula).Select(f => ((XLCell)obj.Range.FirstCell()).GetFormulaR1C1(f.Value)));
+
+ unchecked
+ {
+ var hashCode = xStyle.GetHashCode();
+ hashCode = (hashCode * 397) ^ xx._styleCacheId;
+ hashCode = (hashCode * 397) ^ xx.CopyDefaultModify.GetHashCode();
+ hashCode = (hashCode * 397) ^ xx.UpdatingStyle.GetHashCode();
+ hashCode = (hashCode * 397) ^ xValues.GetHashCode();
+ hashCode = (hashCode * 397) ^ (xx.Colors != null ? xx.Colors.GetHashCode() : 0);
+ hashCode = (hashCode * 397) ^ (xx.ContentTypes != null ? xx.ContentTypes.GetHashCode() : 0);
+ hashCode = (hashCode * 397) ^ (xx.IconSetOperators != null ? xx.IconSetOperators.GetHashCode() : 0);
+ hashCode = (hashCode * 397) ^ (_compareRange && xx.Range != null ? xx.Range.GetHashCode() : 0);
+ hashCode = (hashCode * 397) ^ (int)xx.ConditionalFormatType;
+ hashCode = (hashCode * 397) ^ (int)xx.TimePeriod;
+ hashCode = (hashCode * 397) ^ (int)xx.IconSetStyle;
+ hashCode = (hashCode * 397) ^ (int)xx.Operator;
+ hashCode = (hashCode * 397) ^ xx.Bottom.GetHashCode();
+ hashCode = (hashCode * 397) ^ xx.Percent.GetHashCode();
+ hashCode = (hashCode * 397) ^ xx.ReverseIconOrder.GetHashCode();
+ hashCode = (hashCode * 397) ^ xx.ShowIconOnly.GetHashCode();
+ hashCode = (hashCode * 397) ^ xx.ShowBarOnly.GetHashCode();
+ hashCode = (hashCode * 397) ^ xx.StopIfTrueInternal.GetHashCode();
+ return hashCode;
+ }
+ }
+ }
+
+ private static readonly IEqualityComparer FullComparerInstance = new FullEqualityComparer(true);
+ public static IEqualityComparer FullComparer
+ {
+ get { return FullComparerInstance; }
+ }
+
+ private static readonly IEqualityComparer NoRangeComparerInstance = new FullEqualityComparer(false);
+ public static IEqualityComparer NoRangeComparer
+ {
+ get { return NoRangeComparerInstance; }
+ }
+
public XLConditionalFormat(XLRange range, Boolean copyDefaultModify = false)
{
Id = Guid.NewGuid();
@@ -376,5 +473,59 @@
return new XLCFIconSet(this);
}
}
+
+ internal class DictionaryComparer :
+ IEqualityComparer>
+ {
+ private readonly IEqualityComparer _valueComparer;
+ public DictionaryComparer(IEqualityComparer valueComparer = null)
+ {
+ this._valueComparer = valueComparer ?? EqualityComparer.Default;
+ }
+ public bool Equals(Dictionary x, Dictionary y)
+ {
+ if (x.Count != y.Count)
+ return false;
+ if (x.Keys.Except(y.Keys).Any())
+ return false;
+ if (y.Keys.Except(x.Keys).Any())
+ return false;
+ foreach (var pair in x)
+ if (!_valueComparer.Equals(pair.Value, y[pair.Key]))
+ return false;
+ return true;
+ }
+
+ public int GetHashCode(Dictionary obj)
+ {
+ throw new NotImplementedException();
+ }
+ }
+
+ internal class EnumerableComparer : IEqualityComparer>
+ {
+ private readonly IEqualityComparer _valueComparer;
+ public EnumerableComparer(IEqualityComparer valueComparer = null)
+ {
+ this._valueComparer = valueComparer ?? EqualityComparer.Default;
+ }
+
+ public bool Equals(IEnumerable x, IEnumerable y)
+ {
+ return SetEquals(x, y, _valueComparer);
+ }
+
+ public int GetHashCode(IEnumerable obj)
+ {
+ throw new NotImplementedException();
+ }
+
+ public static bool SetEquals(IEnumerable first, IEnumerable second,
+ IEqualityComparer comparer)
+ {
+ return new HashSet(second, comparer ?? EqualityComparer.Default)
+ .SetEquals(first);
+ }
+ }
}
diff --git a/ClosedXML/Excel/ConditionalFormats/XLConditionalFormats.cs b/ClosedXML/Excel/ConditionalFormats/XLConditionalFormats.cs
index c968484..940bd22 100644
--- a/ClosedXML/Excel/ConditionalFormats/XLConditionalFormats.cs
+++ b/ClosedXML/Excel/ConditionalFormats/XLConditionalFormats.cs
@@ -13,6 +13,22 @@
_conditionalFormats.Add(conditionalFormat);
}
+ private bool IsRangeAbove(IXLRangeAddress newAddr, IXLRangeAddress addr)
+ {
+ return newAddr.FirstAddress.ColumnNumber == addr.FirstAddress.ColumnNumber
+ && newAddr.LastAddress.ColumnNumber == addr.LastAddress.ColumnNumber
+ && newAddr.FirstAddress.RowNumber < addr.FirstAddress.RowNumber
+ && (newAddr.LastAddress.RowNumber+1).Between(addr.FirstAddress.RowNumber, addr.LastAddress.RowNumber);
+ }
+
+ private bool IsRangeToLeft(IXLRangeAddress newAddr, IXLRangeAddress addr)
+ {
+ return newAddr.FirstAddress.RowNumber == addr.FirstAddress.RowNumber
+ && newAddr.LastAddress.RowNumber == addr.LastAddress.RowNumber
+ && newAddr.FirstAddress.ColumnNumber < addr.FirstAddress.ColumnNumber
+ && (newAddr.LastAddress.ColumnNumber+1).Between(addr.FirstAddress.ColumnNumber, addr.LastAddress.ColumnNumber);
+ }
+
public IEnumerator GetEnumerator()
{
return _conditionalFormats.GetEnumerator();
@@ -29,6 +45,66 @@
_conditionalFormats.RemoveAll(predicate);
}
+ ///
+ /// The method consolidate the same conditional formats, which are located in adjacent ranges.
+ ///
+ internal void Consolidate()
+ {
+ var formats = _conditionalFormats
+ .OrderByDescending(x => x.Range.RangeAddress.FirstAddress.RowNumber)
+ .ThenByDescending(x => x.Range.RangeAddress.FirstAddress.ColumnNumber);
+
+ var orderedFormats = formats.ToList();
+
+ foreach (var item in formats)
+ {
+ var itemAddr = item.Range.RangeAddress;
+ var itemRowNum = itemAddr.FirstAddress.RowNumber;
+
+ Func IsSameFormat = f => f != item && f.Range.Worksheet.Position == item.Range.Worksheet.Position &&
+ XLConditionalFormat.NoRangeComparer.Equals(f, item);
+
+ // search for an adjacent range
+ var format = orderedFormats
+ .TakeWhile(f => f.Range.RangeAddress.FirstAddress.RowNumber >= itemRowNum)
+ .FirstOrDefault(f => (IsRangeAbove(itemAddr, f.Range.RangeAddress) || IsRangeToLeft(itemAddr, f.Range.RangeAddress)) && IsSameFormat(f));
+ if (format != null)
+ {
+ Merge(format, item);
+ _conditionalFormats.Remove(item);
+ orderedFormats.Remove(item);
+ // compress with bottom range
+ var newaddr = format.Range.RangeAddress;
+ var newRowNum = newaddr.FirstAddress.RowNumber;
+ var bottom = orderedFormats
+ .TakeWhile(f => f.Range.RangeAddress.FirstAddress.RowNumber >= newRowNum)
+ .FirstOrDefault(f => IsRangeAbove(newaddr, f.Range.RangeAddress) && IsSameFormat(f));
+ if (bottom != null)
+ {
+ Merge(bottom, format);
+ _conditionalFormats.Remove(format);
+ orderedFormats.Remove(format);
+ }
+ continue;
+ }
+
+ // search for an encompassable range
+ format = _conditionalFormats.FirstOrDefault(f => f.Range.Contains(item.Range) && IsSameFormat(f));
+ if (format != null)
+ {
+ _conditionalFormats.Remove(item);
+ orderedFormats.Remove(item);
+ }
+ }
+ }
+
+ private static void Merge(IXLConditionalFormat format, IXLConditionalFormat item)
+ {
+ foreach (var v in format.Values.ToList())
+ format.Values[v.Key] = item.Values[v.Key];
+ format.Range.RangeAddress.FirstAddress = item.Range.RangeAddress.FirstAddress;
+ }
+
public void RemoveAll()
{
_conditionalFormats.ForEach(cf => cf.Range.Dispose());
diff --git a/ClosedXML/Excel/PivotTables/XLPivotTable.cs b/ClosedXML/Excel/PivotTables/XLPivotTable.cs
index 6832af3..1d7895d 100644
--- a/ClosedXML/Excel/PivotTables/XLPivotTable.cs
+++ b/ClosedXML/Excel/PivotTables/XLPivotTable.cs
@@ -295,19 +295,35 @@
private void SetExcelDefaults()
{
EmptyCellReplacement = String.Empty;
- AutofitColumns = true;
- PreserveCellFormatting = true;
- ShowGrandTotalsColumns = true;
- ShowGrandTotalsRows = true;
- UseCustomListsForSorting = true;
- ShowExpandCollapseButtons = true;
- ShowContextualTooltips = true;
- DisplayCaptionsAndDropdowns = true;
- RepeatRowLabels = true;
SaveSourceData = true;
- EnableShowDetails = true;
ShowColumnHeaders = true;
ShowRowHeaders = true;
+
+ // source http://www.datypic.com/sc/ooxml/e-ssml_pivotTableDefinition.html
+ DisplayItemLabels = true; // Show Item Names
+ ShowExpandCollapseButtons = true; // Show Expand Collapse
+ PrintExpandCollapsedButtons = false; // Print Drill Indicators
+ ShowPropertiesInTooltips = true; // Show Member Property ToolTips
+ ShowContextualTooltips = true; // Show ToolTips on Data
+ EnableShowDetails = true; // Enable Drill Down
+ PreserveCellFormatting = true; // Preserve Formatting
+ AutofitColumns = false; // Auto Formatting
+ FilterAreaOrder = XLFilterAreaOrder.DownThenOver; // Page Over Then Down
+ FilteredItemsInSubtotals = false; // Subtotal Hidden Items
+ ShowGrandTotalsRows = true; // Row Grand Totals
+ ShowGrandTotalsColumns = true; // Grand Totals On Columns
+ PrintTitles = false; // Field Print Titles
+ RepeatRowLabels = false; // Item Print Titles
+ MergeAndCenterWithLabels = false; // Merge Titles
+ RowLabelIndent = 1; // Indentation for Compact Axis
+ ShowEmptyItemsOnRows = false; // Show Empty Row
+ ShowEmptyItemsOnColumns = false; // Show Empty Column
+ DisplayCaptionsAndDropdowns = true; // Show Field Headers
+ ClassicPivotTableLayout = false; // Enable Drop Zones
+ AllowMultipleFilters = true; // Multiple Field Filters
+ SortFieldsAtoZ = false; // Default Sort Order
+ UseCustomListsForSorting = true; // Custom List AutoSort
+
}
}
}
diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs
index 2faaf4a..15696ad 100644
--- a/ClosedXML/Excel/XLWorkbook_Load.cs
+++ b/ClosedXML/Excel/XLWorkbook_Load.cs
@@ -68,7 +68,7 @@
SetProperties(dSpreadsheet);
SharedStringItem[] sharedStrings = null;
- if (dSpreadsheet.WorkbookPart.GetPartsOfType().Count() > 0)
+ if (dSpreadsheet.WorkbookPart.GetPartsOfType().Any())
{
var shareStringPart = dSpreadsheet.WorkbookPart.GetPartsOfType().First();
sharedStrings = shareStringPart.SharedStringTable.Elements().ToArray();
@@ -513,6 +513,15 @@
if (pivotTableDefinition.ItemPrintTitles != null) pt.RepeatRowLabels = pivotTableDefinition.ItemPrintTitles.Value;
if (pivotTableDefinition.FieldPrintTitles != null) pt.PrintTitles = pivotTableDefinition.FieldPrintTitles.Value;
if (pivotTableDefinition.EnableDrill != null) pt.EnableShowDetails = pivotTableDefinition.EnableDrill.Value;
+ if (pivotTableCacheDefinitionPart.PivotCacheDefinition.SaveData != null) pt.SaveSourceData = pivotTableCacheDefinitionPart.PivotCacheDefinition.SaveData.Value;
+
+ if (pivotTableCacheDefinitionPart.PivotCacheDefinition.MissingItemsLimit != null)
+ {
+ if (pivotTableCacheDefinitionPart.PivotCacheDefinition.MissingItemsLimit == 0U)
+ pt.ItemsToRetainPerField = XLItemsToRetain.None;
+ else if (pivotTableCacheDefinitionPart.PivotCacheDefinition.MissingItemsLimit == XLHelper.MaxRowNumber)
+ pt.ItemsToRetainPerField = XLItemsToRetain.Max;
+ }
if (pivotTableDefinition.ShowMissing != null && pivotTableDefinition.MissingCaption != null)
pt.EmptyCellReplacement = pivotTableDefinition.MissingCaption.Value;
@@ -520,6 +529,25 @@
if (pivotTableDefinition.ShowError != null && pivotTableDefinition.ErrorCaption != null)
pt.ErrorValueReplacement = pivotTableDefinition.ErrorCaption.Value;
+ var pivotTableDefinitionExtensionList = pivotTableDefinition.GetFirstChild();
+ var pivotTableDefinitionExtension = pivotTableDefinitionExtensionList?.GetFirstChild();
+ var pivotTableDefinition2 = pivotTableDefinitionExtension?.GetFirstChild();
+ if (pivotTableDefinition2 != null)
+ {
+ if (pivotTableDefinition2.EnableEdit != null) pt.EnableCellEditing = pivotTableDefinition2.EnableEdit.Value;
+ if (pivotTableDefinition2.HideValuesRow != null) pt.ShowValuesRow = !pivotTableDefinition2.HideValuesRow.Value;
+ }
+
+ var pivotTableStyle = pivotTableDefinition.GetFirstChild();
+ if (pivotTableStyle != null)
+ {
+ pt.Theme = (XLPivotTableTheme) Enum.Parse(typeof(XLPivotTableTheme), pivotTableStyle.Name);
+ pt.ShowRowHeaders = pivotTableStyle.ShowRowHeaders;
+ pt.ShowColumnHeaders = pivotTableStyle.ShowColumnHeaders;
+ pt.ShowRowStripes = pivotTableStyle.ShowRowStripes;
+ pt.ShowColumnStripes = pivotTableStyle.ShowColumnStripes;
+ }
+
// Subtotal configuration
if (pivotTableDefinition.PivotFields.Cast().All(pf => pf.SubtotalTop != null && pf.SubtotalTop.HasValue && pf.SubtotalTop.Value))
pt.SetSubtotals(XLPivotSubtotals.AtTop);
@@ -727,7 +755,7 @@
else
throw new NotImplementedException();
}
- else if (BooleanValue.ToBoolean(pf.MultipleItemSelectionAllowed))
+ else if (OpenXmlHelper.GetBooleanValueAsBool(pf.MultipleItemSelectionAllowed, false))
{
foreach (var item in pf.Items.Cast- ())
{
diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs
index a0e3fdc..4c0831a 100644
--- a/ClosedXML/Excel/XLWorkbook_Save.cs
+++ b/ClosedXML/Excel/XLWorkbook_Save.cs
@@ -2149,16 +2149,16 @@
Name = pt.Name,
CacheId = cacheId,
DataCaption = "Values",
- MergeItem = OpenXmlHelper.GetBooleanValue(pt.MergeAndCenterWithLabels, true),
+ MergeItem = OpenXmlHelper.GetBooleanValue(pt.MergeAndCenterWithLabels, false),
Indent = Convert.ToUInt32(pt.RowLabelIndent),
PageOverThenDown = (pt.FilterAreaOrder == XLFilterAreaOrder.OverThenDown),
PageWrap = Convert.ToUInt32(pt.FilterFieldsPageWrap),
ShowError = String.IsNullOrEmpty(pt.ErrorValueReplacement),
- UseAutoFormatting = OpenXmlHelper.GetBooleanValue(pt.AutofitColumns, true),
+ UseAutoFormatting = OpenXmlHelper.GetBooleanValue(pt.AutofitColumns, false),
PreserveFormatting = OpenXmlHelper.GetBooleanValue(pt.PreserveCellFormatting, true),
RowGrandTotals = OpenXmlHelper.GetBooleanValue(pt.ShowGrandTotalsRows, true),
ColumnGrandTotals = OpenXmlHelper.GetBooleanValue(pt.ShowGrandTotalsColumns, true),
- SubtotalHiddenItems = OpenXmlHelper.GetBooleanValue(pt.FilteredItemsInSubtotals, true),
+ SubtotalHiddenItems = OpenXmlHelper.GetBooleanValue(pt.FilteredItemsInSubtotals, false),
MultipleFieldFilters = OpenXmlHelper.GetBooleanValue(pt.AllowMultipleFilters, true),
CustomListSort = OpenXmlHelper.GetBooleanValue(pt.UseCustomListsForSorting, true),
ShowDrill = OpenXmlHelper.GetBooleanValue(pt.ShowExpandCollapseButtons, true),
@@ -2166,13 +2166,13 @@
ShowMemberPropertyTips = OpenXmlHelper.GetBooleanValue(pt.ShowPropertiesInTooltips, true),
ShowHeaders = OpenXmlHelper.GetBooleanValue(pt.DisplayCaptionsAndDropdowns, true),
GridDropZones = OpenXmlHelper.GetBooleanValue(pt.ClassicPivotTableLayout, false),
- ShowEmptyRow = OpenXmlHelper.GetBooleanValue(pt.ShowEmptyItemsOnRows, true),
- ShowEmptyColumn = OpenXmlHelper.GetBooleanValue(pt.ShowEmptyItemsOnColumns, true),
+ ShowEmptyRow = OpenXmlHelper.GetBooleanValue(pt.ShowEmptyItemsOnRows, false),
+ ShowEmptyColumn = OpenXmlHelper.GetBooleanValue(pt.ShowEmptyItemsOnColumns, false),
ShowItems = OpenXmlHelper.GetBooleanValue(pt.DisplayItemLabels, true),
- FieldListSortAscending = OpenXmlHelper.GetBooleanValue(pt.SortFieldsAtoZ, true),
- PrintDrill = OpenXmlHelper.GetBooleanValue(pt.PrintExpandCollapsedButtons, true),
- ItemPrintTitles = OpenXmlHelper.GetBooleanValue(pt.RepeatRowLabels, true),
- FieldPrintTitles = OpenXmlHelper.GetBooleanValue(pt.PrintTitles, true),
+ FieldListSortAscending = OpenXmlHelper.GetBooleanValue(pt.SortFieldsAtoZ, false),
+ PrintDrill = OpenXmlHelper.GetBooleanValue(pt.PrintExpandCollapsedButtons, false),
+ ItemPrintTitles = OpenXmlHelper.GetBooleanValue(pt.RepeatRowLabels, false),
+ FieldPrintTitles = OpenXmlHelper.GetBooleanValue(pt.PrintTitles, false),
EnableDrill = OpenXmlHelper.GetBooleanValue(pt.EnableShowDetails, true)
};
@@ -2547,7 +2547,7 @@
var df = new DataField
{
Name = value.CustomName,
- Field = (UInt32)sourceColumn.ColumnNumber() - 1,
+ Field = (UInt32)(sourceColumn.ColumnNumber() - pt.SourceRange.RangeAddress.FirstAddress.ColumnNumber),
Subtotal = value.SummaryFormula.ToOpenXml(),
ShowDataAs = value.Calculation.ToOpenXml(),
NumberFormatId = numberFormatId
@@ -4102,6 +4102,8 @@
private static void GenerateWorksheetPartContent(
WorksheetPart worksheetPart, XLWorksheet xlWorksheet, bool evaluateFormulae, SaveContext context)
{
+ ((XLConditionalFormats)xlWorksheet.ConditionalFormats).Consolidate();
+
#region Worksheet
if (worksheetPart.Worksheet == null)
diff --git a/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/Excel/XLWorksheet.cs
index fbf309b..f5e3a0d 100644
--- a/ClosedXML/Excel/XLWorksheet.cs
+++ b/ClosedXML/Excel/XLWorksheet.cs
@@ -1360,6 +1360,7 @@
conditionalFormat.Range.Dispose();
}
ResumeEvents();
+ newConditionalFormats.Consolidate();
ConditionalFormats = newConditionalFormats;
}
diff --git a/ClosedXML/Extensions.cs b/ClosedXML/Extensions.cs
index c2d1067..19d108d 100644
--- a/ClosedXML/Extensions.cs
+++ b/ClosedXML/Extensions.cs
@@ -176,6 +176,11 @@
{
return value.ToString(CultureInfo.InvariantCulture.NumberFormat);
}
+
+ public static bool Between(this int val, int from, int to)
+ {
+ return val >= from && val <= to;
+ }
}
public static class DecimalExtensions
diff --git a/ClosedXML/Extensions/FormatExtensions.cs b/ClosedXML/Extensions/FormatExtensions.cs
new file mode 100644
index 0000000..4a183bb
--- /dev/null
+++ b/ClosedXML/Extensions/FormatExtensions.cs
@@ -0,0 +1,17 @@
+using ExcelNumberFormat;
+using System.Globalization;
+
+namespace ClosedXML.Extensions
+{
+ internal static class FormatExtensions
+ {
+ public static string ToExcelFormat(this object o, string format)
+ {
+ var nf = new NumberFormat(format);
+ if (!nf.IsValid)
+ return format;
+
+ return nf.Format(o, CultureInfo.InvariantCulture);
+ }
+ }
+}
diff --git a/ClosedXML/Utils/XmlEncoder.cs b/ClosedXML/Utils/XmlEncoder.cs
index 4219901..3177a12 100644
--- a/ClosedXML/Utils/XmlEncoder.cs
+++ b/ClosedXML/Utils/XmlEncoder.cs
@@ -1,10 +1,13 @@
using System.Text;
+using System.Text.RegularExpressions;
using System.Xml;
namespace ClosedXML.Utils
{
public static class XmlEncoder
{
+ private static readonly Regex xHHHHRegex = new Regex("_(x[\\dA-F]{4})_", RegexOptions.Compiled);
+
///
/// Checks if a character is not allowed to the XML Spec http://www.w3.org/TR/REC-xml/#charsets
///
@@ -23,20 +26,23 @@
{
if (encodeStr == null) return null;
- var newString = new StringBuilder();
+ encodeStr = xHHHHRegex.Replace(encodeStr, "_x005F_$1_");
+
+ var sb = new StringBuilder(encodeStr.Length);
foreach (var ch in encodeStr)
{
if (IsXmlChar(ch)) //this method is new in .NET 4
{
- newString.Append(ch);
+ sb.Append(ch);
}
else
{
- newString.Append(XmlConvert.EncodeName(ch.ToString()));
+ sb.Append(XmlConvert.EncodeName(ch.ToString()));
}
}
- return newString.ToString();
+
+ return sb.ToString();
}
public static string DecodeString(string decodeStr)
diff --git a/ClosedXML/packages.config b/ClosedXML/packages.config
index bed0dd7..344713c 100644
--- a/ClosedXML/packages.config
+++ b/ClosedXML/packages.config
@@ -1,5 +1,6 @@
+
\ No newline at end of file
diff --git a/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML_Tests/ClosedXML_Tests.csproj
index cf928a7..b559252 100644
--- a/ClosedXML_Tests/ClosedXML_Tests.csproj
+++ b/ClosedXML_Tests/ClosedXML_Tests.csproj
@@ -87,6 +87,7 @@
+
diff --git a/ClosedXML_Tests/Excel/CalcEngine/TextTests.cs b/ClosedXML_Tests/Excel/CalcEngine/TextTests.cs
index 59d0a59..b4cf9b6 100644
--- a/ClosedXML_Tests/Excel/CalcEngine/TextTests.cs
+++ b/ClosedXML_Tests/Excel/CalcEngine/TextTests.cs
@@ -27,7 +27,7 @@
[Test]
public void Char_Input_Too_Large()
{
- Assert.Throws< CellValueException>(() => XLWorkbook.EvaluateExpr(@"Char(9797)"));
+ Assert.Throws(() => XLWorkbook.EvaluateExpr(@"Char(9797)"));
}
[Test]
@@ -459,7 +459,8 @@
[Test]
public void Text_Value()
{
- Object actual = XLWorkbook.EvaluateExpr(@"Text(Date(2010, 1, 1), ""yyyy-MM-dd"")");
+ Object actual;
+ actual = XLWorkbook.EvaluateExpr(@"Text(Date(2010, 1, 1), ""yyyy-MM-dd"")");
Assert.AreEqual("2010-01-01", actual);
actual = XLWorkbook.EvaluateExpr(@"Text(1469.07, ""0,000,000.00"")");
@@ -488,6 +489,18 @@
Assert.AreEqual("211x", actual);
}
+ [TestCase(2020, 11, 1, 9, 23, 11, "m/d/yyyy h:mm:ss", "11/1/2020 9:23:11")]
+ [TestCase(2023, 7, 14, 2, 12, 3, "m/d/yyyy h:mm:ss", "7/14/2023 2:12:03")]
+ [TestCase(2025, 10, 14, 2, 48, 55, "m/d/yyyy h:mm:ss", "10/14/2025 2:48:55")]
+ [TestCase(2023, 2, 19, 22, 1, 38, "m/d/yyyy h:mm:ss", "2/19/2023 22:01:38")]
+ [TestCase(2025, 12, 19, 19, 43, 58, "m/d/yyyy h:mm:ss", "12/19/2025 19:43:58")]
+ [TestCase(2034, 11, 16, 1, 48, 9, "m/d/yyyy h:mm:ss", "11/16/2034 1:48:09")]
+ [TestCase(2018, 12, 10, 11, 22, 42, "m/d/yyyy h:mm:ss", "12/10/2018 11:22:42")]
+ public void Text_DateFormats(int year, int months, int days, int hour, int minutes, int seconds, string format, string expected)
+ {
+ Assert.AreEqual(expected, XLWorkbook.EvaluateExpr($@"TEXT(DATE({year}, {months}, {days}) + TIME({hour}, {minutes}, {seconds}), ""{format}"")"));
+ }
+
[Test]
public void Trim_EmptyInput_Striong()
{
diff --git a/ClosedXML_Tests/Excel/ConditionalFormats/ConditionalFormatsConsolidateTests.cs b/ClosedXML_Tests/Excel/ConditionalFormats/ConditionalFormatsConsolidateTests.cs
new file mode 100644
index 0000000..222fbeb
--- /dev/null
+++ b/ClosedXML_Tests/Excel/ConditionalFormats/ConditionalFormatsConsolidateTests.cs
@@ -0,0 +1,136 @@
+using System.Linq;
+using ClosedXML.Excel;
+using NUnit.Framework;
+
+namespace ClosedXML_Tests.Excel.ConditionalFormats
+{
+ [TestFixture]
+ public class ConditionalFormatsConsolidateTests
+ {
+ [Test]
+ public void ConsecutivelyRowsConsolidateTest()
+ {
+ var wb = new XLWorkbook();
+ IXLWorksheet ws = wb.Worksheets.Add("Sheet");
+
+ SetFormat1(ws.Range("B2:C2").AddConditionalFormat());
+ SetFormat1(ws.Range("B4:C4").AddConditionalFormat());
+ SetFormat1(ws.Range("B3:C3").AddConditionalFormat());
+
+ ((XLConditionalFormats)ws.ConditionalFormats).Consolidate();
+
+ Assert.AreEqual(1, ws.ConditionalFormats.Count());
+ var format = ws.ConditionalFormats.First();
+ Assert.AreEqual("B2:C4", format.Range.RangeAddress.ToStringRelative());
+ Assert.AreEqual("F2", format.Values.Values.First().Value);
+ }
+
+ [Test]
+ public void ConsecutivelyColumnsConsolidateTest()
+ {
+ var wb = new XLWorkbook();
+ IXLWorksheet ws = wb.Worksheets.Add("Sheet");
+
+ SetFormat1(ws.Range("D2:D3").AddConditionalFormat());
+ SetFormat1(ws.Range("B2:B3").AddConditionalFormat());
+ SetFormat1(ws.Range("C2:C3").AddConditionalFormat());
+
+ ((XLConditionalFormats)ws.ConditionalFormats).Consolidate();
+
+ Assert.AreEqual(1, ws.ConditionalFormats.Count());
+ var format = ws.ConditionalFormats.First();
+ Assert.AreEqual("B2:D3", format.Range.RangeAddress.ToStringRelative());
+ Assert.AreEqual("F2", format.Values.Values.First().Value);
+ }
+
+ [Test]
+ public void Contains1ConsolidateTest()
+ {
+ var wb = new XLWorkbook();
+ IXLWorksheet ws = wb.Worksheets.Add("Sheet");
+
+ SetFormat1(ws.Range("B11:D12").AddConditionalFormat());
+ SetFormat1(ws.Range("C12:D12").AddConditionalFormat());
+
+ ((XLConditionalFormats)ws.ConditionalFormats).Consolidate();
+
+ Assert.AreEqual(1, ws.ConditionalFormats.Count());
+ var format = ws.ConditionalFormats.First();
+ Assert.AreEqual("B11:D12", format.Range.RangeAddress.ToStringRelative());
+ Assert.AreEqual("F11", format.Values.Values.First().Value);
+ }
+
+ [Test]
+ public void Contains2ConsolidateTest()
+ {
+ var wb = new XLWorkbook();
+ IXLWorksheet ws = wb.Worksheets.Add("Sheet");
+
+ SetFormat1(ws.Range("B14:C14").AddConditionalFormat());
+ SetFormat1(ws.Range("B14:B14").AddConditionalFormat());
+
+ ((XLConditionalFormats)ws.ConditionalFormats).Consolidate();
+
+ Assert.AreEqual(1, ws.ConditionalFormats.Count());
+ var format = ws.ConditionalFormats.First();
+ Assert.AreEqual("B14:C14", format.Range.RangeAddress.ToStringRelative());
+ Assert.AreEqual("F14", format.Values.Values.First().Value);
+ }
+
+ [Test]
+ public void SuperimposedConsolidateTest()
+ {
+ var wb = new XLWorkbook();
+ IXLWorksheet ws = wb.Worksheets.Add("Sheet");
+
+ SetFormat1(ws.Range("B16:D18").AddConditionalFormat());
+ SetFormat1(ws.Range("B18:D19").AddConditionalFormat());
+
+ ((XLConditionalFormats)ws.ConditionalFormats).Consolidate();
+
+ Assert.AreEqual(1, ws.ConditionalFormats.Count());
+ var format = ws.ConditionalFormats.First();
+ Assert.AreEqual("B16:D19", format.Range.RangeAddress.ToStringRelative());
+ Assert.AreEqual("F16", format.Values.Values.First().Value);
+ }
+
+ [Test]
+ public void DifferentRangesNoConsolidateTest()
+ {
+ var wb = new XLWorkbook();
+ IXLWorksheet ws = wb.Worksheets.Add("Sheet");
+
+ SetFormat1(ws.Range("B7:C7").AddConditionalFormat());
+ SetFormat1(ws.Range("B8:B8").AddConditionalFormat());
+ SetFormat1(ws.Range("B9:C9").AddConditionalFormat());
+
+ ((XLConditionalFormats)ws.ConditionalFormats).Consolidate();
+
+ Assert.AreEqual(3, ws.ConditionalFormats.Count());
+ }
+
+ [Test]
+ public void DifferentFormatNoConsolidateTest()
+ {
+ var wb = new XLWorkbook();
+ IXLWorksheet ws = wb.Worksheets.Add("Sheet");
+
+ SetFormat1(ws.Range("B11:D12").AddConditionalFormat());
+ SetFormat2(ws.Range("C12:D12").AddConditionalFormat());
+
+ ((XLConditionalFormats)ws.ConditionalFormats).Consolidate();
+
+ Assert.AreEqual(2, ws.ConditionalFormats.Count());
+ }
+
+ private static void SetFormat1(IXLConditionalFormat format)
+ {
+ format.WhenEquals("="+format.Range.FirstCell().CellRight(4).Address.ToStringRelative()).Fill.SetBackgroundColor(XLColor.Blue);
+ }
+
+ private static void SetFormat2(IXLConditionalFormat format)
+ {
+ format.WhenEquals(5).Fill.SetBackgroundColor(XLColor.AliceBlue);
+ }
+ }
+}
diff --git a/ClosedXML_Tests/Excel/Loading/LoadingTests.cs b/ClosedXML_Tests/Excel/Loading/LoadingTests.cs
index 64ab73c..6efca95 100644
--- a/ClosedXML_Tests/Excel/Loading/LoadingTests.cs
+++ b/ClosedXML_Tests/Excel/Loading/LoadingTests.cs
@@ -122,6 +122,23 @@
}
}
+ [Test]
+ public void CanLoadPivotTableSubtotals()
+ {
+ using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Misc\LoadPivotTables.xlsx")))
+ using (var wb = new XLWorkbook(stream))
+ {
+ var ws = wb.Worksheet("PivotTableSubtotals");
+ var pt = ws.PivotTable("PivotTableSubtotals");
+
+ var subtotals = pt.RowLabels.Get("Group").Subtotals.ToArray();
+ Assert.AreEqual(3, subtotals.Length);
+ Assert.AreEqual(XLSubtotalFunction.Average, subtotals[0]);
+ Assert.AreEqual(XLSubtotalFunction.Count, subtotals[1]);
+ Assert.AreEqual(XLSubtotalFunction.Sum, subtotals[2]);
+ }
+ }
+
///
/// For non-English locales, the default style ("Normal" in English) can be
/// another piece of text (e.g. ??????? in Russian).
diff --git a/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs b/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs
index f61b84b..8ee4606 100644
--- a/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs
+++ b/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs
@@ -29,5 +29,120 @@
}
}
}
+
+ [Test]
+ public void PivotTableOptionsSaveTest()
+ {
+ using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Examples\PivotTables\PivotTables.xlsx")))
+ using (var wb = new XLWorkbook(stream))
+ {
+ var ws = wb.Worksheet("PastrySalesData");
+ var table = ws.Table("PastrySalesData");
+
+ var range = table.DataRange;
+ var header = ws.Range(1, 1, 1, 3);
+ var dataRange = ws.Range(header.FirstCell(), range.LastCell());
+
+ var ptSheet = wb.Worksheets.Add("BlankPivotTable");
+ var pt = ptSheet.PivotTables.AddNew("pvtOptionsTest", ptSheet.Cell(1, 1), dataRange);
+
+ pt.ColumnHeaderCaption = "clmn header";
+ pt.RowHeaderCaption = "row header";
+
+ pt.AutofitColumns = true;
+ pt.PreserveCellFormatting = false;
+ pt.ShowGrandTotalsColumns = true;
+ pt.ShowGrandTotalsRows = true;
+ pt.UseCustomListsForSorting = false;
+ pt.ShowExpandCollapseButtons = false;
+ pt.ShowContextualTooltips = false;
+ pt.DisplayCaptionsAndDropdowns = false;
+ pt.RepeatRowLabels = true;
+ pt.SaveSourceData = false;
+ pt.EnableShowDetails = false;
+ pt.ShowColumnHeaders = false;
+ pt.ShowRowHeaders = false;
+
+ pt.MergeAndCenterWithLabels = true; // MergeItem
+ pt.RowLabelIndent = 12; // Indent
+ pt.FilterAreaOrder = XLFilterAreaOrder.OverThenDown; // PageOverThenDown
+ pt.FilterFieldsPageWrap = 14; // PageWrap
+ pt.ErrorValueReplacement = "error test"; // ErrorCaption
+ pt.EmptyCellReplacement = "empty test"; // MissingCaption
+
+ pt.FilteredItemsInSubtotals = true; // Subtotal filtered page items
+ pt.AllowMultipleFilters = false; // MultipleFieldFilters
+
+ pt.ShowPropertiesInTooltips = false;
+ pt.ClassicPivotTableLayout = true;
+ pt.ShowEmptyItemsOnRows = true;
+ pt.ShowEmptyItemsOnColumns = true;
+ pt.DisplayItemLabels = false;
+ pt.SortFieldsAtoZ = true;
+
+ pt.PrintExpandCollapsedButtons = true;
+ pt.PrintTitles = true;
+
+ // TODO pt.RefreshDataOnOpen = false;
+ pt.ItemsToRetainPerField = XLItemsToRetain.Max;
+ pt.EnableCellEditing = true;
+ pt.ShowValuesRow = true;
+ pt.ShowRowStripes = true;
+ pt.ShowColumnStripes = true;
+ pt.Theme = XLPivotTableTheme.PivotStyleDark13;
+
+ using (var ms = new MemoryStream())
+ {
+ wb.SaveAs(ms, true);
+
+ ms.Position = 0;
+
+ using (var wbassert = new XLWorkbook(ms))
+ {
+ var wsassert = wbassert.Worksheet("BlankPivotTable");
+ var ptassert = wsassert.PivotTable("pvtOptionsTest");
+ Assert.AreNotEqual(null, ptassert, "name save failure");
+ Assert.AreEqual("clmn header", ptassert.ColumnHeaderCaption, "ColumnHeaderCaption save failure");
+ Assert.AreEqual("row header", ptassert.RowHeaderCaption, "RowHeaderCaption save failure");
+ Assert.AreEqual(true, ptassert.MergeAndCenterWithLabels, "MergeAndCenterWithLabels save failure");
+ Assert.AreEqual(12, ptassert.RowLabelIndent, "RowLabelIndent save failure");
+ Assert.AreEqual(XLFilterAreaOrder.OverThenDown, ptassert.FilterAreaOrder, "FilterAreaOrder save failure");
+ Assert.AreEqual(14, ptassert.FilterFieldsPageWrap, "FilterFieldsPageWrap save failure");
+ Assert.AreEqual("error test", ptassert.ErrorValueReplacement, "ErrorValueReplacement save failure");
+ Assert.AreEqual("empty test", ptassert.EmptyCellReplacement, "EmptyCellReplacement save failure");
+ Assert.AreEqual(true, ptassert.AutofitColumns, "AutofitColumns save failure");
+ Assert.AreEqual(false, ptassert.PreserveCellFormatting, "PreserveCellFormatting save failure");
+ Assert.AreEqual(true, ptassert.ShowGrandTotalsRows, "ShowGrandTotalsRows save failure");
+ Assert.AreEqual(true, ptassert.ShowGrandTotalsColumns, "ShowGrandTotalsColumns save failure");
+ Assert.AreEqual(true, ptassert.FilteredItemsInSubtotals, "FilteredItemsInSubtotals save failure");
+ Assert.AreEqual(false, ptassert.AllowMultipleFilters, "AllowMultipleFilters save failure");
+ Assert.AreEqual(false, ptassert.UseCustomListsForSorting, "UseCustomListsForSorting save failure");
+ Assert.AreEqual(false, ptassert.ShowExpandCollapseButtons, "ShowExpandCollapseButtons save failure");
+ Assert.AreEqual(false, ptassert.ShowContextualTooltips, "ShowContextualTooltips save failure");
+ Assert.AreEqual(false, ptassert.ShowPropertiesInTooltips, "ShowPropertiesInTooltips save failure");
+ Assert.AreEqual(false, ptassert.DisplayCaptionsAndDropdowns, "DisplayCaptionsAndDropdowns save failure");
+ Assert.AreEqual(true, ptassert.ClassicPivotTableLayout, "ClassicPivotTableLayout save failure");
+ Assert.AreEqual(true, ptassert.ShowEmptyItemsOnRows, "ShowEmptyItemsOnRows save failure");
+ Assert.AreEqual(true, ptassert.ShowEmptyItemsOnColumns, "ShowEmptyItemsOnColumns save failure");
+ Assert.AreEqual(false, ptassert.DisplayItemLabels, "DisplayItemLabels save failure");
+ Assert.AreEqual(true, ptassert.SortFieldsAtoZ, "SortFieldsAtoZ save failure");
+ Assert.AreEqual(true, ptassert.PrintExpandCollapsedButtons, "PrintExpandCollapsedButtons save failure");
+ Assert.AreEqual(true, ptassert.RepeatRowLabels, "RepeatRowLabels save failure");
+ Assert.AreEqual(true, ptassert.PrintTitles, "PrintTitles save failure");
+ Assert.AreEqual(false, ptassert.SaveSourceData, "SaveSourceData save failure");
+ Assert.AreEqual(false, ptassert.EnableShowDetails, "EnableShowDetails save failure");
+ // TODO Assert.AreEqual(false, ptassert.RefreshDataOnOpen, "RefreshDataOnOpen save failure");
+ Assert.AreEqual(XLItemsToRetain.Max, ptassert.ItemsToRetainPerField, "ItemsToRetainPerField save failure");
+ Assert.AreEqual(true, ptassert.EnableCellEditing, "EnableCellEditing save failure");
+ Assert.AreEqual(XLPivotTableTheme.PivotStyleDark13, ptassert.Theme, "Theme save failure");
+ Assert.AreEqual(true, ptassert.ShowValuesRow, "ShowValuesRow save failure");
+ Assert.AreEqual(false, ptassert.ShowRowHeaders, "ShowRowHeaders save failure");
+ Assert.AreEqual(false, ptassert.ShowColumnHeaders, "ShowColumnHeaders save failure");
+ Assert.AreEqual(true, ptassert.ShowRowStripes, "ShowRowStripes save failure");
+ Assert.AreEqual(true, ptassert.ShowColumnStripes, "ShowColumnStripes save failure");
+ }
+ }
+ }
+ }
}
}
diff --git a/ClosedXML_Tests/Excel/Ranges/CopyingRangesTests.cs b/ClosedXML_Tests/Excel/Ranges/CopyingRangesTests.cs
index 426daf5..f2b8058 100644
--- a/ClosedXML_Tests/Excel/Ranges/CopyingRangesTests.cs
+++ b/ClosedXML_Tests/Excel/Ranges/CopyingRangesTests.cs
@@ -1,4 +1,5 @@
using System.Drawing;
+using System.Linq;
using ClosedXML.Excel;
using NUnit.Framework;
@@ -53,13 +54,7 @@
IXLWorksheet ws = wb.Worksheets.Add("Sheet");
IXLRow row1 = ws.Row(1);
- row1.Cell(1).Style.Fill.SetBackgroundColor(XLColor.Red);
- row1.Cell(2).Style.Fill.SetBackgroundColor(XLColor.FromArgb(1, 1, 1));
- row1.Cell(3).Style.Fill.SetBackgroundColor(XLColor.FromHtml("#CCCCCC"));
- row1.Cell(4).Style.Fill.SetBackgroundColor(XLColor.FromIndex(26));
- row1.Cell(5).Style.Fill.SetBackgroundColor(XLColor.FromKnownColor(KnownColor.MediumSeaGreen));
- row1.Cell(6).Style.Fill.SetBackgroundColor(XLColor.FromName("Blue"));
- row1.Cell(7).Style.Fill.SetBackgroundColor(XLColor.FromTheme(XLThemeColor.Accent3));
+ FillRow(row1);
ws.Cell(2, 1).Value = row1;
ws.Cell(3, 1).Value = row1.Row(1, 7);
@@ -81,6 +76,43 @@
Assert.AreEqual(XLColor.FromKnownColor(KnownColor.MediumSeaGreen), row3.Cell(5).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromName("Blue"), row3.Cell(6).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromTheme(XLThemeColor.Accent3), row3.Cell(7).Style.Fill.BackgroundColor);
+
+ Assert.AreEqual(3, ws.ConditionalFormats.Count());
+ Assert.IsTrue(ws.ConditionalFormats.Single(x => x.Range.RangeAddress.ToStringRelative() == "B1:B1").Values.Any(v => v.Value.Value == "G1" && v.Value.IsFormula));
+ Assert.IsTrue(ws.ConditionalFormats.Single(x => x.Range.RangeAddress.ToStringRelative() == "B2:B2").Values.Any(v => v.Value.Value == "G2" && v.Value.IsFormula));
+ Assert.IsTrue(ws.ConditionalFormats.Single(x => x.Range.RangeAddress.ToStringRelative() == "B3:B3").Values.Any(v => v.Value.Value == "G3" && v.Value.IsFormula));
+ }
+
+ [Test]
+ public void CopyingConditionalFormats()
+ {
+ var wb = new XLWorkbook();
+ IXLWorksheet ws = wb.Worksheets.Add("Sheet");
+
+ FillRow(ws.Row(1));
+ FillRow(ws.Row(2));
+ FillRow(ws.Row(3));
+
+ ((XLConditionalFormats)ws.ConditionalFormats).Consolidate();
+
+ ws.Cell(5, 2).Value = ws.Row(2).Row(1, 7);
+
+ Assert.AreEqual(2, ws.ConditionalFormats.Count());
+ Assert.IsTrue(ws.ConditionalFormats.Single(x => x.Range.RangeAddress.ToStringRelative() == "B1:B3").Values.Any(v => v.Value.Value == "G1" && v.Value.IsFormula));
+ Assert.IsTrue(ws.ConditionalFormats.Single(x => x.Range.RangeAddress.ToStringRelative() == "C5:C5").Values.Any(v => v.Value.Value == "H5" && v.Value.IsFormula));
+ }
+
+ private static void FillRow(IXLRow row1)
+ {
+ row1.Cell(1).Style.Fill.SetBackgroundColor(XLColor.Red);
+ row1.Cell(2).Style.Fill.SetBackgroundColor(XLColor.FromArgb(1, 1, 1));
+ row1.Cell(3).Style.Fill.SetBackgroundColor(XLColor.FromHtml("#CCCCCC"));
+ row1.Cell(4).Style.Fill.SetBackgroundColor(XLColor.FromIndex(26));
+ row1.Cell(5).Style.Fill.SetBackgroundColor(XLColor.FromKnownColor(KnownColor.MediumSeaGreen));
+ row1.Cell(6).Style.Fill.SetBackgroundColor(XLColor.FromName("Blue"));
+ row1.Cell(7).Style.Fill.SetBackgroundColor(XLColor.FromTheme(XLThemeColor.Accent3));
+
+ row1.Cell(2).AddConditionalFormat().WhenEquals("=" + row1.FirstCell().CellRight(6).Address.ToStringRelative()).Fill.SetBackgroundColor(XLColor.Blue);
}
}
-}
\ No newline at end of file
+}
diff --git a/ClosedXML_Tests/Excel/Saving/SavingTests.cs b/ClosedXML_Tests/Excel/Saving/SavingTests.cs
index 60a1d7a..70b4b3d 100644
--- a/ClosedXML_Tests/Excel/Saving/SavingTests.cs
+++ b/ClosedXML_Tests/Excel/Saving/SavingTests.cs
@@ -2,6 +2,7 @@
using NUnit.Framework;
using System.Globalization;
using System.IO;
+using System.Linq;
using System.Threading;
namespace ClosedXML_Tests.Excel.Saving
@@ -34,11 +35,32 @@
}
[Test]
+ public void CanEscape_xHHHH_Correctly()
+ {
+ using (var ms = new MemoryStream())
+ {
+ using (var wb = new XLWorkbook())
+ {
+ var ws = wb.AddWorksheet("Sheet1");
+ ws.FirstCell().Value = "Reserve_TT_A_BLOCAGE_CAG_x6904_2";
+ wb.SaveAs(ms);
+ }
+
+ ms.Seek(0, SeekOrigin.Begin);
+
+ using (var wb = new XLWorkbook(ms))
+ {
+ var ws = wb.Worksheets.First();
+ Assert.AreEqual("Reserve_TT_A_BLOCAGE_CAG_x6904_2", ws.FirstCell().Value);
+ }
+ }
+ }
+
+ [Test]
public void CanSaveFileMultipleTimesAfterDeletingWorksheet()
{
// https://github.com/ClosedXML/ClosedXML/issues/435
-
using (var ms = new MemoryStream())
{
using (XLWorkbook book1 = new XLWorkbook())
@@ -61,7 +83,6 @@
}
}
-
[Test]
public void CanSaveAndValidateFileInAnotherCulture()
{
diff --git a/ClosedXML_Tests/Excel/Styles/NumberFormatTests.cs b/ClosedXML_Tests/Excel/Styles/NumberFormatTests.cs
index 87ab21a..ed012b3 100644
--- a/ClosedXML_Tests/Excel/Styles/NumberFormatTests.cs
+++ b/ClosedXML_Tests/Excel/Styles/NumberFormatTests.cs
@@ -31,5 +31,21 @@
Assert.AreEqual("yy-MM-dd", ws.Cell("E1").Style.DateFormat.Format);
}
}
+
+ [Test]
+ public void TestExcelNumberFormats()
+ {
+ using (var wb = new XLWorkbook())
+ {
+ var ws = wb.AddWorksheet("Sheet1");
+ var c = ws.FirstCell()
+ .SetValue(41573.875)
+ .SetDataType(XLDataType.DateTime);
+
+ c.Style.NumberFormat.SetFormat("m/d/yy\\ h:mm;@");
+
+ Assert.AreEqual("10/26/13 21:00", c.GetFormattedString());
+ }
+ }
}
}
diff --git a/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx
index d6f65c7..c0f266b 100644
--- a/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx
+++ b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx
Binary files differ
diff --git a/ClosedXML_Tests/Resource/Misc/LoadPivotTables.xlsx b/ClosedXML_Tests/Resource/Misc/LoadPivotTables.xlsx
index 6fad7fe..b4c5b1c 100644
--- a/ClosedXML_Tests/Resource/Misc/LoadPivotTables.xlsx
+++ b/ClosedXML_Tests/Resource/Misc/LoadPivotTables.xlsx
Binary files differ