diff --git a/ClosedXML/Excel/AutoFilters/IXLBaseAutoFilter.cs b/ClosedXML/Excel/AutoFilters/IXLBaseAutoFilter.cs index 88ca907..f13ce40 100644 --- a/ClosedXML/Excel/AutoFilters/IXLBaseAutoFilter.cs +++ b/ClosedXML/Excel/AutoFilters/IXLBaseAutoFilter.cs @@ -1,25 +1,30 @@ using System; + namespace ClosedXML.Excel { - using System.Collections.Generic; public enum XLFilterType { Regular, Custom, TopBottom, Dynamic } + public enum XLFilterDynamicType { AboveAverage, BelowAverage } - public enum XLTopBottomPart { Top, Bottom} + + public enum XLTopBottomPart { Top, Bottom } + public interface IXLBaseAutoFilter { Boolean Enabled { get; set; } IXLRange Range { get; set; } + IXLBaseAutoFilter Set(IXLRangeBase range); + IXLBaseAutoFilter Clear(); IXLFilterColumn Column(String column); + IXLFilterColumn Column(Int32 column); IXLBaseAutoFilter Sort(Int32 columnToSortBy, XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true); + Boolean Sorted { get; set; } XLSortOrder SortOrder { get; set; } Int32 SortColumn { get; set; } - - } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs b/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs index 12e0995..2d1368d 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs @@ -1,3 +1,4 @@ +using ClosedXML.Excel.CalcEngine.Exceptions; using ClosedXML.Excel.CalcEngine.Functions; using System; using System.Collections; @@ -64,7 +65,7 @@ ce.RegisterFunction("SUBTOTAL", 2, 255, Subtotal); ce.RegisterFunction("SUM", 1, int.MaxValue, Sum); ce.RegisterFunction("SUMIF", 2, 3, SumIf); - //ce.RegisterFunction("SUMPRODUCT", 1, SumProduct); + ce.RegisterFunction("SUMPRODUCT", 1, 30, SumProduct); ce.RegisterFunction("SUMSQ", 1, 255, SumSq); //ce.RegisterFunction("SUMX2MY2", SumX2MY2, 1); //ce.RegisterFunction("SUMX2PY2", SumX2PY2, 1); @@ -261,6 +262,37 @@ return tally.Sum(); } + private static object SumProduct(List p) + { + // all parameters should be IEnumerable + if (p.Any(param => !(param is IEnumerable))) + throw new NoValueAvailableException(); + + var counts = p.Cast().Select(param => + { + int i = 0; + foreach (var item in param) + i++; + return i; + }) + .Distinct(); + + // All parameters should have the same length + if (counts.Count() > 1) + throw new NoValueAvailableException(); + + var values = p + .Cast() + .Select(range => range.Cast().ToList()); + + return Enumerable.Range(0, counts.Single()) + .Aggregate(0d, (t, i) => + t + values.Aggregate(1d, + (product, list) => product * list[i] + ) + ); + } + private static object Tan(List p) { return Math.Tan(p[0]); diff --git a/ClosedXML/Excel/Cells/IXLCell.cs b/ClosedXML/Excel/Cells/IXLCell.cs index fc3724e..3212d2e 100644 --- a/ClosedXML/Excel/Cells/IXLCell.cs +++ b/ClosedXML/Excel/Cells/IXLCell.cs @@ -35,6 +35,14 @@ IXLAddress Address { get; } /// + /// Returns the current region. The current region is a range bounded by any combination of blank rows and blank columns + /// + /// + /// The current region. + /// + IXLRange CurrentRegion { get; } + + /// /// Gets or sets the type of this cell's data. /// Changing the data type will cause ClosedXML to covert the current value to the new data type. /// An exception will be thrown if the current value cannot be converted to the new data type. diff --git a/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/Excel/Cells/XLCell.cs index 09d313d..ef28e44 100644 --- a/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/Excel/Cells/XLCell.cs @@ -691,7 +691,8 @@ public IXLTable InsertTable(DataTable data, string tableName, bool createTable) { - if (data == null) return null; + if (data == null || data.Columns.Count == 0) + return null; if (createTable && this.Worksheet.Tables.Any(t => t.Contains(this))) throw new InvalidOperationException(String.Format("This cell '{0}' is already part of a table.", this.Address.ToString())); @@ -2746,5 +2747,32 @@ public Boolean HasArrayFormula { get { return FormulaA1.StartsWith("{"); } } public IXLRangeAddress FormulaReference { get; set; } + + public IXLRange CurrentRegion + { + get + { + return this.Worksheet.Range(FindCurrentRegion(this.AsRange())); + } + } + + internal IXLRangeAddress FindCurrentRegion(IXLRangeBase range) + { + var rangeAddress = range.RangeAddress; + + var filledCells = range + .SurroundingCells(c => !(c as XLCell).IsEmpty(false, false)) + .Concat(this.Worksheet.Range(rangeAddress).Cells()); + + var grownRangeAddress = new XLRangeAddress( + new XLAddress(this.Worksheet, filledCells.Min(c => c.Address.RowNumber), filledCells.Min(c => c.Address.ColumnNumber), false, false), + new XLAddress(this.Worksheet, filledCells.Max(c => c.Address.RowNumber), filledCells.Max(c => c.Address.ColumnNumber), false, false) + ); + + if (rangeAddress.Equals(grownRangeAddress)) + return this.Worksheet.Range(grownRangeAddress).RangeAddress; + else + return FindCurrentRegion(this.Worksheet.Range(grownRangeAddress)); + } } -} +} \ No newline at end of file diff --git a/ClosedXML/Excel/ConditionalFormats/IXLConditionalFormat.cs b/ClosedXML/Excel/ConditionalFormats/IXLConditionalFormat.cs index 962cc21..8428f7e 100644 --- a/ClosedXML/Excel/ConditionalFormats/IXLConditionalFormat.cs +++ b/ClosedXML/Excel/ConditionalFormats/IXLConditionalFormat.cs @@ -68,7 +68,7 @@ IXLStyle WhenNotBlank(); IXLStyle WhenIsError(); IXLStyle WhenNotError(); - IXLStyle WhenDateIs(XLTimePeriod timePeriod ); + IXLStyle WhenDateIs(XLTimePeriod timePeriod); IXLStyle WhenContains(String value); IXLStyle WhenNotContains(String value); IXLStyle WhenStartsWith(String value); @@ -99,6 +99,7 @@ IXLCFColorScaleMin ColorScale(); IXLCFDataBarMin DataBar(XLColor color, Boolean showBarOnly = false); + IXLCFDataBarMin DataBar(XLColor positiveColor, XLColor negativeColor, Boolean showBarOnly = false); IXLCFIconSet IconSet(XLIconSetStyle iconSetStyle, Boolean reverseIconOrder = false, Boolean showIconOnly = false); XLConditionalFormatType ConditionalFormatType { get; } @@ -113,12 +114,11 @@ XLDictionary Colors { get; } XLDictionary ContentTypes { get; } XLDictionary IconSetOperators { get; } - + XLCFOperator Operator { get; } Boolean Bottom { get; } Boolean Percent { get; } - - + + } } - \ No newline at end of file diff --git a/ClosedXML/Excel/ConditionalFormats/Save/IXLCFConverterExtension.cs b/ClosedXML/Excel/ConditionalFormats/Save/IXLCFConverterExtension.cs new file mode 100644 index 0000000..82b11a0 --- /dev/null +++ b/ClosedXML/Excel/ConditionalFormats/Save/IXLCFConverterExtension.cs @@ -0,0 +1,13 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using DocumentFormat.OpenXml.Office2010.Excel; + +namespace ClosedXML.Excel +{ + internal interface IXLCFConverterExtension + { + ConditionalFormattingRule Convert(IXLConditionalFormat cf, XLWorkbook.SaveContext context); + } +} diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFConvertersExtension.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFConvertersExtension.cs new file mode 100644 index 0000000..7b85951 --- /dev/null +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFConvertersExtension.cs @@ -0,0 +1,28 @@ +using DocumentFormat.OpenXml.Office2010.Excel; +using System; +using System.Collections.Generic; + +namespace ClosedXML.Excel +{ + internal class XLCFConvertersExtension + { + private readonly static Dictionary Converters; + + static XLCFConvertersExtension() + { + XLCFConvertersExtension.Converters = new Dictionary() + { + { XLConditionalFormatType.DataBar, new XLCFDataBarConverterExtension() } + }; + } + + public XLCFConvertersExtension() + { + } + + public static ConditionalFormattingRule Convert(IXLConditionalFormat conditionalFormat, XLWorkbook.SaveContext context) + { + return XLCFConvertersExtension.Converters[conditionalFormat.ConditionalFormatType].Convert(conditionalFormat, context); + } + } +} \ No newline at end of file diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFDataBarConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFDataBarConverter.cs index 3feddcc..3f46e38 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFDataBarConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFDataBarConverter.cs @@ -1,3 +1,4 @@ +using ClosedXML.Extensions; using DocumentFormat.OpenXml.Spreadsheet; using System; using System.Linq; @@ -11,6 +12,7 @@ var conditionalFormattingRule = new ConditionalFormattingRule { Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; var dataBar = new DataBar { ShowValue = !cf.ShowBarOnly }; + var conditionalFormatValueObject1 = new ConditionalFormatValueObject { Type = cf.ContentTypes[1].ToOpenXml() }; if (cf.Values.Any() && cf.Values[1]?.Value != null) conditionalFormatValueObject1.Val = cf.Values[1].Value; @@ -37,8 +39,25 @@ dataBar.Append(conditionalFormatValueObject2); dataBar.Append(color); + conditionalFormattingRule.Append(dataBar); + if (cf.Colors.Count > 1) + { + ConditionalFormattingRuleExtensionList conditionalFormattingRuleExtensionList = new ConditionalFormattingRuleExtensionList(); + + ConditionalFormattingRuleExtension conditionalFormattingRuleExtension = new ConditionalFormattingRuleExtension { Uri = "{B025F937-C7B1-47D3-B67F-A62EFF666E3E}" }; + conditionalFormattingRuleExtension.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"); + DocumentFormat.OpenXml.Office2010.Excel.Id id = new DocumentFormat.OpenXml.Office2010.Excel.Id + { + Text = (cf as XLConditionalFormat).Id.WrapInBraces() + }; + conditionalFormattingRuleExtension.Append(id); + + conditionalFormattingRuleExtensionList.Append(conditionalFormattingRuleExtension); + + conditionalFormattingRule.Append(conditionalFormattingRuleExtensionList); + } return conditionalFormattingRule; } } diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFDataBarConverterExtension.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFDataBarConverterExtension.cs new file mode 100644 index 0000000..3b172f8 --- /dev/null +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFDataBarConverterExtension.cs @@ -0,0 +1,88 @@ +using ClosedXML.Extensions; +using DocumentFormat.OpenXml.Office.Excel; +using DocumentFormat.OpenXml.Office2010.Excel; +using System; +using System.Linq; + +namespace ClosedXML.Excel +{ + internal class XLCFDataBarConverterExtension : IXLCFConverterExtension + { + public XLCFDataBarConverterExtension() + { + } + + public ConditionalFormattingRule Convert(IXLConditionalFormat cf, XLWorkbook.SaveContext context) + { + ConditionalFormattingRule conditionalFormattingRule = new ConditionalFormattingRule() + { + Type = DocumentFormat.OpenXml.Spreadsheet.ConditionalFormatValues.DataBar, + Id = (cf as XLConditionalFormat).Id.WrapInBraces() + }; + + DataBar dataBar = new DataBar() + { + MinLength = 0, + MaxLength = 100, + Gradient = false, + AxisPosition = DataBarAxisPositionValues.Middle, + ShowValue = !cf.ShowBarOnly + }; + + ConditionalFormattingValueObjectTypeValues cfMinType = Convert(cf.ContentTypes[1].ToOpenXml()); + var cfMin = new ConditionalFormattingValueObject { Type = cfMinType }; + if (cf.Values.Any() && cf.Values[1]?.Value != null) + { + cfMin.Type = ConditionalFormattingValueObjectTypeValues.Numeric; + cfMin.Append(new Formula() { Text = cf.Values[1].Value }); + } + + ConditionalFormattingValueObjectTypeValues cfMaxType = Convert(cf.ContentTypes[2].ToOpenXml()); + var cfMax = new ConditionalFormattingValueObject { Type = cfMaxType }; + if (cf.Values.Count >= 2 && cf.Values[2]?.Value != null) + { + cfMax.Type = ConditionalFormattingValueObjectTypeValues.Numeric; + cfMax.Append(new Formula() { Text = cf.Values[2].Value }); + } + + var barAxisColor = new BarAxisColor { Rgb = XLColor.Black.Color.ToHex() }; + + var negativeFillColor = new NegativeFillColor { Rgb = cf.Colors[1].Color.ToHex() }; + if (cf.Colors.Count == 2) + { + negativeFillColor = new NegativeFillColor { Rgb = cf.Colors[2].Color.ToHex() }; + } + + dataBar.Append(cfMin); + dataBar.Append(cfMax); + + dataBar.Append(negativeFillColor); + dataBar.Append(barAxisColor); + + conditionalFormattingRule.Append(dataBar); + + return conditionalFormattingRule; + } + + private ConditionalFormattingValueObjectTypeValues Convert(DocumentFormat.OpenXml.Spreadsheet.ConditionalFormatValueObjectValues obj) + { + switch (obj) + { + case DocumentFormat.OpenXml.Spreadsheet.ConditionalFormatValueObjectValues.Max: + return ConditionalFormattingValueObjectTypeValues.AutoMax; + case DocumentFormat.OpenXml.Spreadsheet.ConditionalFormatValueObjectValues.Min: + return ConditionalFormattingValueObjectTypeValues.AutoMin; + case DocumentFormat.OpenXml.Spreadsheet.ConditionalFormatValueObjectValues.Number: + return ConditionalFormattingValueObjectTypeValues.Numeric; + case DocumentFormat.OpenXml.Spreadsheet.ConditionalFormatValueObjectValues.Percent: + return ConditionalFormattingValueObjectTypeValues.Percent; + case DocumentFormat.OpenXml.Spreadsheet.ConditionalFormatValueObjectValues.Percentile: + return ConditionalFormattingValueObjectTypeValues.Percentile; + case DocumentFormat.OpenXml.Spreadsheet.ConditionalFormatValueObjectValues.Formula: + return ConditionalFormattingValueObjectTypeValues.Formula; + default: + throw new NotImplementedException(); + } + } + } +} diff --git a/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs b/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs index 96847d6..b42905d 100644 --- a/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs +++ b/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs @@ -5,11 +5,11 @@ namespace ClosedXML.Excel { - internal class XLConditionalFormat: IXLConditionalFormat, IXLStylized + internal class XLConditionalFormat : IXLConditionalFormat, IXLStylized { - public XLConditionalFormat(XLRange range, Boolean copyDefaultModify = false) { + Id = Guid.NewGuid(); Range = range; Style = new XLStyle(this, range.Worksheet.Style); Values = new XLDictionary(); @@ -18,31 +18,34 @@ IconSetOperators = new XLDictionary(); CopyDefaultModify = copyDefaultModify; } - public XLConditionalFormat(XLConditionalFormat other) + + public XLConditionalFormat(XLConditionalFormat conditionalFormat) { - Range = other.Range; - Style = new XLStyle(this, other.Style); - Values = new XLDictionary(other.Values); - Colors = new XLDictionary(other.Colors); - ContentTypes = new XLDictionary(other.ContentTypes); - IconSetOperators = new XLDictionary(other.IconSetOperators); + Id = Guid.NewGuid(); + Range = conditionalFormat.Range; + Style = new XLStyle(this, conditionalFormat.Style); + Values = new XLDictionary(conditionalFormat.Values); + Colors = new XLDictionary(conditionalFormat.Colors); + ContentTypes = new XLDictionary(conditionalFormat.ContentTypes); + IconSetOperators = new XLDictionary(conditionalFormat.IconSetOperators); - ConditionalFormatType = other.ConditionalFormatType; - TimePeriod = other.TimePeriod; - IconSetStyle = other.IconSetStyle; - Operator = other.Operator; - Bottom = other.Bottom; - Percent = other.Percent; - ReverseIconOrder = other.ReverseIconOrder; - ShowIconOnly = other.ShowIconOnly; - ShowBarOnly = other.ShowBarOnly; + ConditionalFormatType = conditionalFormat.ConditionalFormatType; + TimePeriod = conditionalFormat.TimePeriod; + IconSetStyle = conditionalFormat.IconSetStyle; + Operator = conditionalFormat.Operator; + Bottom = conditionalFormat.Bottom; + Percent = conditionalFormat.Percent; + ReverseIconOrder = conditionalFormat.ReverseIconOrder; + ShowIconOnly = conditionalFormat.ShowIconOnly; + ShowBarOnly = conditionalFormat.ShowBarOnly; } + public Guid Id { get; internal set; } public Boolean CopyDefaultModify { get; set; } private IXLStyle _style; private Int32 _styleCacheId; - public IXLStyle Style{ get { return GetStyle(); } set { SetStyle(value); } } + public IXLStyle Style { get { return GetStyle(); } set { SetStyle(value); } } private IXLStyle GetStyle() { //return _style; @@ -88,12 +91,12 @@ public XLConditionalFormatType ConditionalFormatType { get; set; } public XLTimePeriod TimePeriod { get; set; } public XLIconSetStyle IconSetStyle { get; set; } - public XLCFOperator Operator { get; set; } - public Boolean Bottom { get; set; } - public Boolean Percent { get; set; } - public Boolean ReverseIconOrder { get; set; } - public Boolean ShowIconOnly { get; set; } - public Boolean ShowBarOnly { get; set; } + public XLCFOperator Operator { get; set; } + public Boolean Bottom { get; set; } + public Boolean Percent { get; set; } + public Boolean ReverseIconOrder { get; set; } + public Boolean ShowIconOnly { get; set; } + public Boolean ShowBarOnly { get; set; } public void CopyFrom(IXLConditionalFormat other) { @@ -279,8 +282,8 @@ return Style; } public IXLStyle WhenBetween(Double minValue, Double maxValue) - { - Values.Initialize(new XLFormula (minValue)); + { + Values.Initialize(new XLFormula(minValue)); Values.Add(new XLFormula(maxValue)); Operator = XLCFOperator.Between; ConditionalFormatType = XLConditionalFormatType.CellIs; @@ -308,7 +311,7 @@ public IXLStyle WhenIsTrue(String formula) { String f = formula.TrimStart()[0] == '=' ? formula : "=" + formula; - Values.Initialize(new XLFormula {Value = f}); + Values.Initialize(new XLFormula { Value = f }); ConditionalFormatType = XLConditionalFormatType.Expression; return Style; } @@ -328,7 +331,7 @@ Bottom = true; return Style; } - + public IXLCFColorScaleMin ColorScale() { ConditionalFormatType = XLConditionalFormatType.ColorScale; @@ -341,6 +344,14 @@ ConditionalFormatType = XLConditionalFormatType.DataBar; return new XLCFDataBarMin(this); } + public IXLCFDataBarMin DataBar(XLColor positiveColor, XLColor negativeColor, Boolean showBarOnly = false) + { + Colors.Initialize(positiveColor); + Colors.Add(negativeColor); + ShowBarOnly = showBarOnly; + ConditionalFormatType = XLConditionalFormatType.DataBar; + return new XLCFDataBarMin(this); + } public IXLCFIconSet IconSet(XLIconSetStyle iconSetStyle, Boolean reverseIconOrder = false, Boolean showIconOnly = false) { IconSetOperators.Clear(); diff --git a/ClosedXML/Excel/ConditionalFormats/XLConditionalFormats.cs b/ClosedXML/Excel/ConditionalFormats/XLConditionalFormats.cs index 5d1ea42..dc00b56 100644 --- a/ClosedXML/Excel/ConditionalFormats/XLConditionalFormats.cs +++ b/ClosedXML/Excel/ConditionalFormats/XLConditionalFormats.cs @@ -5,7 +5,7 @@ namespace ClosedXML.Excel { - internal class XLConditionalFormats: IXLConditionalFormats + internal class XLConditionalFormats : IXLConditionalFormats { private readonly List _conditionalFormats = new List(); public void Add(IXLConditionalFormat conditionalFormat) diff --git a/ClosedXML/Excel/Drawings/Style/IXLDrawingFont.cs b/ClosedXML/Excel/Drawings/Style/IXLDrawingFont.cs index 94b6850..878f23b 100644 --- a/ClosedXML/Excel/Drawings/Style/IXLDrawingFont.cs +++ b/ClosedXML/Excel/Drawings/Style/IXLDrawingFont.cs @@ -17,6 +17,6 @@ IXLDrawingStyle SetFontColor(XLColor value); IXLDrawingStyle SetFontName(String value); IXLDrawingStyle SetFontFamilyNumbering(XLFontFamilyNumberingValues value); - + IXLDrawingStyle SetFontCharSet(XLFontCharSet value); } } diff --git a/ClosedXML/Excel/Drawings/Style/XLDrawingFont.cs b/ClosedXML/Excel/Drawings/Style/XLDrawingFont.cs index 886e249..744d781 100644 --- a/ClosedXML/Excel/Drawings/Style/XLDrawingFont.cs +++ b/ClosedXML/Excel/Drawings/Style/XLDrawingFont.cs @@ -1,7 +1,4 @@ using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; namespace ClosedXML.Excel { @@ -29,6 +26,7 @@ public String FontName { get; set; } public XLFontFamilyNumberingValues FontFamilyNumbering { get; set; } + public XLFontCharSet FontCharSet { get; set; } public IXLDrawingStyle SetBold() { @@ -120,5 +118,10 @@ return _style; } + public IXLDrawingStyle SetFontCharSet(XLFontCharSet value) + { + FontCharSet = value; + return _style; + } } } diff --git a/ClosedXML/Excel/Drawings/XLPicture.cs b/ClosedXML/Excel/Drawings/XLPicture.cs index 7628b3b..df02007 100644 --- a/ClosedXML/Excel/Drawings/XLPicture.cs +++ b/ClosedXML/Excel/Drawings/XLPicture.cs @@ -140,6 +140,8 @@ { if ((_worksheet.Pictures.FirstOrDefault(p => p.Id.Equals(value)) ?? this) != this) throw new ArgumentException($"The picture ID '{value}' already exists."); + + id = value; } } diff --git a/ClosedXML/Excel/PivotTables/IXLPivotTable.cs b/ClosedXML/Excel/PivotTables/IXLPivotTable.cs index 91bcafd..03f76d1 100644 --- a/ClosedXML/Excel/PivotTables/IXLPivotTable.cs +++ b/ClosedXML/Excel/PivotTables/IXLPivotTable.cs @@ -1,4 +1,5 @@ using System; +using System.Collections.Generic; namespace ClosedXML.Excel { @@ -117,20 +118,22 @@ IXLCell TargetCell { get; set; } IXLRange SourceRange { get; set; } + IEnumerable SourceRangeFieldsAvailable { get; } + Boolean MergeAndCenterWithLabels { get; set; } // MergeItem Int32 RowLabelIndent { get; set; } // Indent - XLFilterAreaOrder FilterAreaOrder { get; set; } // PageOverThenDown + XLFilterAreaOrder FilterAreaOrder { get; set; } // PageOverThenDown Int32 FilterFieldsPageWrap { get; set; } // PageWrap String ErrorValueReplacement { get; set; } // ErrorCaption String EmptyCellReplacement { get; set; } // MissingCaption - Boolean AutofitColumns { get; set; } //UseAutoFormatting - Boolean PreserveCellFormatting { get; set; } // PreserveFormatting - - Boolean ShowGrandTotalsRows { get; set; } // RowGrandTotals - Boolean ShowGrandTotalsColumns { get; set; } // ColumnGrandTotals + Boolean AutofitColumns { get; set; } //UseAutoFormatting + Boolean PreserveCellFormatting { get; set; } // PreserveFormatting + + Boolean ShowGrandTotalsRows { get; set; } // RowGrandTotals + Boolean ShowGrandTotalsColumns { get; set; } // ColumnGrandTotals Boolean FilteredItemsInSubtotals { get; set; } // Subtotal filtered page items - Boolean AllowMultipleFilters { get; set; } // MultipleFieldFilters - Boolean UseCustomListsForSorting { get; set; } // CustomListSort + Boolean AllowMultipleFilters { get; set; } // MultipleFieldFilters + Boolean UseCustomListsForSorting { get; set; } // CustomListSort Boolean ShowExpandCollapseButtons { get; set; } Boolean ShowContextualTooltips { get; set; } diff --git a/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValues.cs b/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValues.cs index 4e09f16..ef16caf 100644 --- a/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValues.cs +++ b/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValues.cs @@ -32,6 +32,9 @@ } public IXLPivotValue Add(String sourceName, String customName) { + if (sourceName != XLConstants.PivotTableValuesSentinalLabel && !this._pivotTable.SourceRangeFieldsAvailable.Contains(sourceName, StringComparer.OrdinalIgnoreCase)) + throw new ArgumentOutOfRangeException(nameof(sourceName), String.Format("The column '{0}' does not appear in the source range.", sourceName)); + var pivotValue = new XLPivotValue(sourceName) { CustomName = customName }; _pivotValues.Add(customName, pivotValue); diff --git a/ClosedXML/Excel/PivotTables/XLPivotFields.cs b/ClosedXML/Excel/PivotTables/XLPivotFields.cs index 19accb7..d35e0e8 100644 --- a/ClosedXML/Excel/PivotTables/XLPivotFields.cs +++ b/ClosedXML/Excel/PivotTables/XLPivotFields.cs @@ -4,9 +4,16 @@ namespace ClosedXML.Excel { - public class XLPivotFields : IXLPivotFields + internal class XLPivotFields : IXLPivotFields { + private readonly Dictionary _pivotFields = new Dictionary(); + private readonly IXLPivotTable _pivotTable; + + internal XLPivotFields(IXLPivotTable pivotTable) + { + this._pivotTable = pivotTable; + } public IXLPivotField Add(String sourceName) { @@ -15,6 +22,9 @@ public IXLPivotField Add(String sourceName, String customName) { + if (sourceName != XLConstants.PivotTableValuesSentinalLabel && !this._pivotTable.SourceRangeFieldsAvailable.Contains(sourceName, StringComparer.OrdinalIgnoreCase)) + throw new ArgumentOutOfRangeException(nameof(sourceName), String.Format("The column '{0}' does not appear in the source range.", sourceName)); + var pivotField = new XLPivotField(sourceName) { CustomName = customName }; _pivotFields.Add(sourceName, pivotField); return pivotField; @@ -49,7 +59,8 @@ { var selectedItem = _pivotFields.Select((item, index) => new { Item = item, Position = index }).FirstOrDefault(i => i.Item.Key == pf.SourceName); if (selectedItem == null) - throw new IndexOutOfRangeException("Invalid field name."); + throw new ArgumentNullException(nameof(pf), "Invalid field name."); + return selectedItem.Position; } diff --git a/ClosedXML/Excel/PivotTables/XLPivotTable.cs b/ClosedXML/Excel/PivotTables/XLPivotTable.cs index acb4a84..e1232cb 100644 --- a/ClosedXML/Excel/PivotTables/XLPivotTable.cs +++ b/ClosedXML/Excel/PivotTables/XLPivotTable.cs @@ -10,10 +10,10 @@ public XLPivotTable() { - Fields = new XLPivotFields(); - ReportFilters = new XLPivotFields(); - ColumnLabels=new XLPivotFields(); - RowLabels = new XLPivotFields(); + Fields = new XLPivotFields(this); + ReportFilters = new XLPivotFields(this); + ColumnLabels=new XLPivotFields(this); + RowLabels = new XLPivotFields(this); Values = new XLPivotValues(this); Theme = XLPivotTableTheme.PivotStyleLight16; @@ -22,6 +22,10 @@ public IXLCell TargetCell { get; set; } public IXLRange SourceRange { get; set; } + public IEnumerable SourceRangeFieldsAvailable + { + get { return this.SourceRange.FirstRow().Cells().Select(c => c.GetString()); } + } public IXLPivotFields Fields { get; private set; } public IXLPivotFields ReportFilters { get; private set; } diff --git a/ClosedXML/Excel/Ranges/IXLRangeBase.cs b/ClosedXML/Excel/Ranges/IXLRangeBase.cs index 064d975..b0dd238 100644 --- a/ClosedXML/Excel/Ranges/IXLRangeBase.cs +++ b/ClosedXML/Excel/Ranges/IXLRangeBase.cs @@ -262,10 +262,71 @@ IXLAutoFilter SetAutoFilter(); + IXLAutoFilter SetAutoFilter(Boolean value); + IXLDataValidation SetDataValidation(); IXLConditionalFormat AddConditionalFormat(); void Select(); + + /// + /// Grows this the current range by one cell to each side + /// + IXLRangeBase Grow(); + + /// + /// Grows this the current range by the specified number of cells to each side. + /// + /// The grow count. + /// + IXLRangeBase Grow(Int32 growCount); + + /// + /// Shrinks this current range by one cell. + /// + IXLRangeBase Shrink(); + + /// + /// Shrinks the current range by the specified number of cells from each side. + /// + /// The shrink count. + /// + IXLRangeBase Shrink(Int32 shrinkCount); + + /// + /// Returns the intersection of this range with another range on the same worksheet. + /// + /// The other range. + /// Predicate applied to this range's cells. + /// Predicate applied to the other range's cells. + /// + IXLRangeBase Intersection(IXLRangeBase otherRange, Func thisRangePredicate = null, Func otherRangePredicate = null); + + /// + /// Returns the set of cells surrounding the current range. + /// + /// The predicate to apply on the resulting set of cells. + IXLCells SurroundingCells(Func predicate = null); + + /// + /// Calculates the union of two ranges on the same worksheet. + /// + /// The other range. + /// Predicate applied to this range's cells. + /// Predicate applied to the other range's cells. + /// + /// The union + /// + IXLCells Union(IXLRangeBase otherRange, Func thisRangePredicate = null, Func otherRangePredicate = null); + + /// + /// Returns all cells in the current range that are not in the other range. + /// + /// The other range. + /// Predicate applied to this range's cells. + /// Predicate applied to the other range's cells. + /// + IXLCells Difference(IXLRangeBase otherRange, Func thisRangePredicate = null, Func otherRangePredicate = null); } } diff --git a/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/Excel/Ranges/XLRangeBase.cs index cb0d324..fceca26 100644 --- a/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -803,14 +803,18 @@ if (absRow <= 0 || absRow > XLHelper.MaxRowNumber) { - throw new IndexOutOfRangeException(String.Format("Row number must be between 1 and {0}", - XLHelper.MaxRowNumber)); + throw new ArgumentOutOfRangeException( + nameof(cellAddressInRange), + String.Format("Row number must be between 1 and {0}", XLHelper.MaxRowNumber) + ); } if (absColumn <= 0 || absColumn > XLHelper.MaxColumnNumber) { - throw new IndexOutOfRangeException(String.Format("Column number must be between 1 and {0}", - XLHelper.MaxColumnNumber)); + throw new ArgumentOutOfRangeException( + nameof(cellAddressInRange), + String.Format("Column number must be between 1 and {0}", XLHelper.MaxColumnNumber) + ); } var cell = Worksheet.Internals.CellsCollection.GetCell(absRow, @@ -1757,8 +1761,16 @@ public IXLAutoFilter SetAutoFilter() { - using (var asRange = AsRange()) - return Worksheet.AutoFilter.Set(asRange); + return SetAutoFilter(true); + } + + public IXLAutoFilter SetAutoFilter(Boolean value) + { + if (value) + using (var asRange = AsRange()) + return Worksheet.AutoFilter.Set(asRange); + else + return Worksheet.AutoFilter.Clear(); } #region Sort @@ -2067,5 +2079,104 @@ { Worksheet.SelectedRanges.Add(AsRange()); } + + public IXLRangeBase Grow() + { + return Grow(1); + } + + public IXLRangeBase Grow(int growCount) + { + var firstRow = Math.Max(1, this.RangeAddress.FirstAddress.RowNumber - growCount); + var firstColumn = Math.Max(1, this.RangeAddress.FirstAddress.ColumnNumber - growCount); + + var lastRow = Math.Min(XLHelper.MaxRowNumber, this.RangeAddress.LastAddress.RowNumber + growCount); + var lastColumn = Math.Min(XLHelper.MaxColumnNumber, this.RangeAddress.LastAddress.ColumnNumber + growCount); + + return this.Worksheet.Range(firstRow, firstColumn, lastRow, lastColumn); + } + + public IXLRangeBase Shrink() + { + return Shrink(1); + } + + public IXLRangeBase Shrink(int shrinkCount) + { + var firstRow = this.RangeAddress.FirstAddress.RowNumber + shrinkCount; + var firstColumn = this.RangeAddress.FirstAddress.ColumnNumber + shrinkCount; + + var lastRow = this.RangeAddress.LastAddress.RowNumber - shrinkCount; + var lastColumn = this.RangeAddress.LastAddress.ColumnNumber - shrinkCount; + + if (firstRow > lastRow || firstColumn > lastColumn) + return null; + + return this.Worksheet.Range(firstRow, firstColumn, lastRow, lastColumn); + } + + public IXLRangeBase Intersection(IXLRangeBase otherRange, Func thisRangePredicate = null, Func otherRangePredicate = null) + { + if (otherRange == null) + return null; + + if (!this.Worksheet.Equals(otherRange.Worksheet)) + return null; + + if (thisRangePredicate == null) thisRangePredicate = c => true; + if (otherRangePredicate == null) otherRangePredicate = c => true; + + var intersectionCells = this.Cells(c => thisRangePredicate(c) && otherRange.Cells(otherRangePredicate).Contains(c)); + + if (!intersectionCells.Any()) + return null; + + var firstRow = intersectionCells.Min(c => c.Address.RowNumber); + var firstColumn = intersectionCells.Min(c => c.Address.ColumnNumber); + + var lastRow = intersectionCells.Max(c => c.Address.RowNumber); + var lastColumn = intersectionCells.Max(c => c.Address.ColumnNumber); + + return this.Worksheet.Range(firstRow, firstColumn, lastRow, lastColumn); + } + + public IXLCells SurroundingCells(Func predicate = null) + { + var cells = new XLCells(false, false, predicate); + this.Grow().Cells(c => !this.Contains(c)).ForEach(c => cells.Add(c as XLCell)); + return cells; + } + + public IXLCells Union(IXLRangeBase otherRange, Func thisRangePredicate = null, Func otherRangePredicate = null) + { + if (otherRange == null) + return this.Cells(thisRangePredicate); + + var cells = new XLCells(false, false); + if (!this.Worksheet.Equals(otherRange.Worksheet)) + return cells; + + if (thisRangePredicate == null) thisRangePredicate = c => true; + if (otherRangePredicate == null) otherRangePredicate = c => true; + + this.Cells(thisRangePredicate).Concat(otherRange.Cells(otherRangePredicate)).Distinct().ForEach(c => cells.Add(c as XLCell)); + return cells; + } + + public IXLCells Difference(IXLRangeBase otherRange, Func thisRangePredicate = null, Func otherRangePredicate = null) + { + if (otherRange == null) + return this.Cells(thisRangePredicate); + + var cells = new XLCells(false, false); + if (!this.Worksheet.Equals(otherRange.Worksheet)) + return cells; + + if (thisRangePredicate == null) thisRangePredicate = c => true; + if (otherRangePredicate == null) otherRangePredicate = c => true; + + this.Cells(c => thisRangePredicate(c) && !otherRange.Cells(otherRangePredicate).Contains(c)).ForEach(c => cells.Add(c as XLCell)); + return cells; + } } -} +} \ No newline at end of file diff --git a/ClosedXML/Excel/RichText/IXLPhonetics.cs b/ClosedXML/Excel/RichText/IXLPhonetics.cs index 0330b61..67cd96d 100644 --- a/ClosedXML/Excel/RichText/IXLPhonetics.cs +++ b/ClosedXML/Excel/RichText/IXLPhonetics.cs @@ -17,6 +17,7 @@ IXLPhonetics SetFontColor(XLColor value); IXLPhonetics SetFontName(String value); IXLPhonetics SetFontFamilyNumbering(XLFontFamilyNumberingValues value); + IXLPhonetics SetFontCharSet(XLFontCharSet value); IXLPhonetics Add(String text, Int32 start, Int32 end); IXLPhonetics ClearText(); diff --git a/ClosedXML/Excel/RichText/IXLRichString.cs b/ClosedXML/Excel/RichText/IXLRichString.cs index b41b537..a090460 100644 --- a/ClosedXML/Excel/RichText/IXLRichString.cs +++ b/ClosedXML/Excel/RichText/IXLRichString.cs @@ -10,7 +10,7 @@ public interface IXLRichString: IXLFontBase, IEquatable, IXLWithRichString { String Text { get; set; } - + IXLRichString SetBold(); IXLRichString SetBold(Boolean value); IXLRichString SetItalic(); IXLRichString SetItalic(Boolean value); @@ -22,5 +22,6 @@ IXLRichString SetFontColor(XLColor value); IXLRichString SetFontName(String value); IXLRichString SetFontFamilyNumbering(XLFontFamilyNumberingValues value); + IXLRichString SetFontCharSet(XLFontCharSet value); } } diff --git a/ClosedXML/Excel/RichText/XLPhonetics.cs b/ClosedXML/Excel/RichText/XLPhonetics.cs index 2cf1e02..b048267 100644 --- a/ClosedXML/Excel/RichText/XLPhonetics.cs +++ b/ClosedXML/Excel/RichText/XLPhonetics.cs @@ -1,6 +1,6 @@ using System; -using System.Linq; using System.Collections.Generic; +using System.Linq; namespace ClosedXML.Excel { @@ -8,7 +8,8 @@ { private readonly List _phonetics = new List(); - readonly IXLFontBase _defaultFont; + private readonly IXLFontBase _defaultFont; + public XLPhonetics(IXLFontBase defaultFont) { _defaultFont = defaultFont; @@ -22,7 +23,7 @@ _defaultFont = defaultFont; Type = defaultPhonetics.Type; Alignment = defaultPhonetics.Alignment; - + this.CopyFont(defaultPhonetics); } @@ -36,28 +37,52 @@ public XLColor FontColor { get; set; } public String FontName { get; set; } public XLFontFamilyNumberingValues FontFamilyNumbering { get; set; } + public XLFontCharSet FontCharSet { get; set; } - public IXLPhonetics SetBold() { Bold = true; return this; } public IXLPhonetics SetBold(Boolean value) { Bold = value; return this; } - public IXLPhonetics SetItalic() { Italic = true; return this; } public IXLPhonetics SetItalic(Boolean value) { Italic = value; return this; } - public IXLPhonetics SetUnderline() { Underline = XLFontUnderlineValues.Single; return this; } public IXLPhonetics SetUnderline(XLFontUnderlineValues value) { Underline = value; return this; } - public IXLPhonetics SetStrikethrough() { Strikethrough = true; return this; } public IXLPhonetics SetStrikethrough(Boolean value) { Strikethrough = value; return this; } + public IXLPhonetics SetBold() { Bold = true; return this; } + + public IXLPhonetics SetBold(Boolean value) { Bold = value; return this; } + + public IXLPhonetics SetItalic() { Italic = true; return this; } + + public IXLPhonetics SetItalic(Boolean value) { Italic = value; return this; } + + public IXLPhonetics SetUnderline() { Underline = XLFontUnderlineValues.Single; return this; } + + public IXLPhonetics SetUnderline(XLFontUnderlineValues value) { Underline = value; return this; } + + public IXLPhonetics SetStrikethrough() { Strikethrough = true; return this; } + + public IXLPhonetics SetStrikethrough(Boolean value) { Strikethrough = value; return this; } + public IXLPhonetics SetVerticalAlignment(XLFontVerticalTextAlignmentValues value) { VerticalAlignment = value; return this; } - public IXLPhonetics SetShadow() { Shadow = true; return this; } public IXLPhonetics SetShadow(Boolean value) { Shadow = value; return this; } + + public IXLPhonetics SetShadow() { Shadow = true; return this; } + + public IXLPhonetics SetShadow(Boolean value) { Shadow = value; return this; } + public IXLPhonetics SetFontSize(Double value) { FontSize = value; return this; } + public IXLPhonetics SetFontColor(XLColor value) { FontColor = value; return this; } + public IXLPhonetics SetFontName(String value) { FontName = value; return this; } + public IXLPhonetics SetFontFamilyNumbering(XLFontFamilyNumberingValues value) { FontFamilyNumbering = value; return this; } + public IXLPhonetics SetFontCharSet(XLFontCharSet value) { FontCharSet = value; return this; } + public IXLPhonetics Add(String text, Int32 start, Int32 end) { _phonetics.Add(new XLPhonetic(text, start, end)); return this; } + public IXLPhonetics ClearText() { _phonetics.Clear(); return this; } + public IXLPhonetics ClearFont() { this.CopyFont(_defaultFont); @@ -70,6 +95,7 @@ public XLPhoneticType Type { get; set; } public IXLPhonetics SetAlignment(XLPhoneticAlignment phoneticAlignment) { Alignment = phoneticAlignment; return this; } + public IXLPhonetics SetType(XLPhoneticType phoneticType) { Type = phoneticType; return this; } public IEnumerator GetEnumerator() @@ -104,8 +130,7 @@ && FontSize == other.FontSize && FontColor.Equals(other.FontColor) && FontName == other.FontName - && FontFamilyNumbering == other.FontFamilyNumbering - ; + && FontFamilyNumbering == other.FontFamilyNumbering; } } } diff --git a/ClosedXML/Excel/RichText/XLRichString.cs b/ClosedXML/Excel/RichText/XLRichString.cs index b9e9cdd..9837fdb 100644 --- a/ClosedXML/Excel/RichText/XLRichString.cs +++ b/ClosedXML/Excel/RichText/XLRichString.cs @@ -33,6 +33,7 @@ public XLColor FontColor { get; set; } public String FontName { get; set; } public XLFontFamilyNumberingValues FontFamilyNumbering { get; set; } + public XLFontCharSet FontCharSet { get; set; } public IXLRichString SetBold() { Bold = true; return this; } public IXLRichString SetBold(Boolean value) { Bold = value; return this; } public IXLRichString SetItalic() { Italic = true; return this; } public IXLRichString SetItalic(Boolean value) { Italic = value; return this; } @@ -44,6 +45,7 @@ public IXLRichString SetFontColor(XLColor value) { FontColor = value; return this; } public IXLRichString SetFontName(String value) { FontName = value; return this; } public IXLRichString SetFontFamilyNumbering(XLFontFamilyNumberingValues value) { FontFamilyNumbering = value; return this; } + public IXLRichString SetFontCharSet(XLFontCharSet value) { FontCharSet = value; return this; } public Boolean Equals(IXLRichString other) { diff --git a/ClosedXML/Excel/Style/IXLFont.cs b/ClosedXML/Excel/Style/IXLFont.cs index 2262d35..a996b62 100644 --- a/ClosedXML/Excel/Style/IXLFont.cs +++ b/ClosedXML/Excel/Style/IXLFont.cs @@ -19,7 +19,7 @@ } public enum XLFontFamilyNumberingValues - { + { NotApplicable = 0, Roman = 1, Swiss = 2, @@ -28,18 +28,131 @@ Decorative = 5 } + public enum XLFontCharSet + { + /// + /// ASCII character set. + /// + Ansi = 0, + + /// + /// System default character set. + /// + Default = 1, + + /// + /// Symbol character set. + /// + Symbol = 2, + + /// + /// Characters used by Macintosh. + /// + Mac = 77, + + /// + /// Japanese character set. + /// + ShiftJIS = 128, + + /// + /// Korean character set. + /// + Hangul = 129, + + /// + /// Another common spelling of the Korean character set. + /// + Hangeul = 129, + + /// + /// Korean character set. + /// + Johab = 130, + + /// + /// Chinese character set used in mainland China. + /// + GB2312 = 134, + + /// + /// Chinese character set used mostly in Hong Kong SAR and Taiwan. + /// + ChineseBig5 = 136, + + /// + /// Greek character set. + /// + Greek = 161, + + /// + /// Turkish character set. + /// + Turkish = 162, + + /// + /// Vietnamese character set. + /// + Vietnamese = 163, + + /// + /// Hebrew character set. + /// + Hebrew = 177, + + /// + /// Arabic character set. + /// + Arabic = 178, + + /// + /// Baltic character set. + /// + Baltic = 186, + + /// + /// Russian character set. + /// + Russian = 204, + + /// + /// Thai character set. + /// + Thai = 222, + + /// + /// Eastern European character set. + /// + EastEurope = 238, + + /// + /// Extended ASCII character set used with disk operating system (DOS) and some Microsoft Windows fonts. + /// + Oem = 255 + } + public interface IXLFont : IXLFontBase, IEquatable { IXLStyle SetBold(); IXLStyle SetBold(Boolean value); + IXLStyle SetItalic(); IXLStyle SetItalic(Boolean value); + IXLStyle SetUnderline(); IXLStyle SetUnderline(XLFontUnderlineValues value); + IXLStyle SetStrikethrough(); IXLStyle SetStrikethrough(Boolean value); + IXLStyle SetVerticalAlignment(XLFontVerticalTextAlignmentValues value); + IXLStyle SetShadow(); IXLStyle SetShadow(Boolean value); + IXLStyle SetFontSize(Double value); + IXLStyle SetFontColor(XLColor value); + IXLStyle SetFontName(String value); + IXLStyle SetFontFamilyNumbering(XLFontFamilyNumberingValues value); + IXLStyle SetFontCharSet(XLFontCharSet value); } } diff --git a/ClosedXML/Excel/Style/IXLFontBase.cs b/ClosedXML/Excel/Style/IXLFontBase.cs index e23deb0..77ac7a1 100644 --- a/ClosedXML/Excel/Style/IXLFontBase.cs +++ b/ClosedXML/Excel/Style/IXLFontBase.cs @@ -14,5 +14,6 @@ XLColor FontColor { get; set; } String FontName { get; set; } XLFontFamilyNumberingValues FontFamilyNumbering { get; set; } + XLFontCharSet FontCharSet { get; set; } } } diff --git a/ClosedXML/Excel/Style/XLFont.cs b/ClosedXML/Excel/Style/XLFont.cs index 3c52c20..87915b4 100644 --- a/ClosedXML/Excel/Style/XLFont.cs +++ b/ClosedXML/Excel/Style/XLFont.cs @@ -9,6 +9,7 @@ private Boolean _bold; private XLColor _fontColor; private XLFontFamilyNumberingValues _fontFamilyNumbering; + private XLFontCharSet _fontCharSet; private String _fontName; private Double _fontSize; private Boolean _italic; @@ -37,6 +38,7 @@ _fontColor = defaultFont.FontColor; _fontName = defaultFont.FontName; _fontFamilyNumbering = defaultFont.FontFamilyNumbering; + _fontCharSet = defaultFont.FontCharSet; if (useDefaultModify) { @@ -52,6 +54,7 @@ FontColorModified = d.FontColorModified; FontNameModified = d.FontNameModified; FontFamilyNumberingModified = d.FontFamilyNumberingModified; + FontCharSetModified = d.FontCharSetModified; } } @@ -105,7 +108,7 @@ _underline = value; UnderlineModified = true; } - + } } @@ -236,6 +239,23 @@ } } + public Boolean FontCharSetModified { get; set; } + public XLFontCharSet FontCharSet + { + get { return _fontCharSet; } + set + { + SetStyleChanged(); + if (_container != null && !_container.UpdatingStyle) + _container.Styles.ForEach(s => s.Font.FontCharSet = value); + else + { + _fontCharSet = value; + FontCharSetModified = true; + } + } + } + public IXLStyle SetBold() { Bold = true; @@ -326,6 +346,12 @@ return _container.Style; } + public IXLStyle SetFontCharSet(XLFontCharSet value) + { + FontCharSet = value; + return _container.Style; + } + public Boolean Equals(IXLFont other) { var otherF = other as XLFont; @@ -397,4 +423,4 @@ ^ (Int32)FontFamilyNumbering; } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/Style/XLPredefinedFormat.cs b/ClosedXML/Excel/Style/XLPredefinedFormat.cs new file mode 100644 index 0000000..32c6e1a --- /dev/null +++ b/ClosedXML/Excel/Style/XLPredefinedFormat.cs @@ -0,0 +1,165 @@ +namespace ClosedXML.Excel +{ + /// + /// Reference point of date/number formats available. + /// See more at: https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformat.aspx + /// + public static class XLPredefinedFormat + { + public enum Number + { + /// + /// General + /// + General = 0, + + /// + /// 0 + /// + Integer = 1, + + /// + /// 0.00 + /// + Precision2 = 2, + + /// + /// #,##0 + /// + IntegerWithSeparator = 3, + + /// + /// #,##0.00 + /// + Precision2WithSeparator = 4, + + /// + /// 0% + /// + PercentInteger = 9, + + /// + /// 0.00% + /// + PercentPrecision2 = 10, + + /// + /// 0.00E+00 + /// + ScientificPrecision2 = 11, + + /// + /// # ?/? + /// + FractionPrecision1 = 12, + + /// + /// # ??/?? + /// + FractionPrecision2 = 13, + + /// + /// #,##0 ,(#,##0) + /// + IntegerWithSeparatorAndParens = 37, + + /// + /// #,##0 ,[Red](#,##0) + /// + IntegerWithSeparatorAndParensRed = 38, + + /// + /// #,##0.00,(#,##0.00) + /// + Precision2WithSeparatorAndParens = 39, + + /// + /// #,##0.00,[Red](#,##0.00) + /// + Precision2WithSeparatorAndParensRed = 40, + + /// + /// ##0.0E+0 + /// + ScientificUpToHundredsAndPrecision1 = 48, + + /// + /// @ + /// + Text = 49 + } + + public enum DateTime + { + /// + /// General + /// + General = 0, + + /// + /// d/m/yyyy + /// + DayMonthYear4WithSlashes = 14, + + /// + /// d-mmm-yy + /// + DayMonthAbbrYear2WithDashes = 15, + + /// + /// d-mmm + /// + DayMonthAbbrWithDash = 16, + + /// + /// mmm-yy + /// + MonthAbbrYear2WithDash = 17, + + /// + /// h:mm tt + /// + Hour12MinutesAmPm = 18, + + /// + /// h:mm:ss tt + /// + Hour12MinutesSecondsAmPm = 19, + + /// + /// H:mm + /// + Hour24Minutes = 20, + + /// + /// H:mm:ss + /// + Hour24MinutesSeconds = 21, + + /// + /// m/d/yyyy H:mm + /// + MonthDayYear4WithDashesHour24Minutes = 22, + + /// + /// mm:ss + /// + MinutesSeconds = 45, + + /// + /// [h]:mm:ss + /// + Hour12MinutesSeconds = 46, + + /// + /// mmss.0 + /// + MinutesSecondsMillis1 = 47, + + /// + /// @ + /// + Text = 49 + } + } +} diff --git a/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/Excel/XLWorkbook.cs index c72fb39..4b004d3 100644 --- a/ClosedXML/Excel/XLWorkbook.cs +++ b/ClosedXML/Excel/XLWorkbook.cs @@ -51,60 +51,62 @@ { get { - return _defaultStyle ?? (_defaultStyle = new XLStyle(null) - { - Font = new XLFont(null, null) - { - Bold = false, - Italic = false, - Underline = XLFontUnderlineValues.None, - Strikethrough = false, - VerticalAlignment = XLFontVerticalTextAlignmentValues.Baseline, - FontSize = 11, - FontColor = XLColor.FromArgb(0, 0, 0), - FontName = "Calibri", - FontFamilyNumbering = XLFontFamilyNumberingValues.Swiss - }, - Fill = new XLFill(null) - { - BackgroundColor = XLColor.FromIndex(64), - PatternType = XLFillPatternValues.None, - PatternColor = XLColor.FromIndex(64) - }, - Border = new XLBorder(null, null) - { - BottomBorder = XLBorderStyleValues.None, - DiagonalBorder = XLBorderStyleValues.None, - DiagonalDown = false, - DiagonalUp = false, - LeftBorder = XLBorderStyleValues.None, - RightBorder = XLBorderStyleValues.None, - TopBorder = XLBorderStyleValues.None, - BottomBorderColor = XLColor.Black, - DiagonalBorderColor = XLColor.Black, - LeftBorderColor = XLColor.Black, - RightBorderColor = XLColor.Black, - TopBorderColor = XLColor.Black - }, - NumberFormat = new XLNumberFormat(null, null) { NumberFormatId = 0 }, - Alignment = new XLAlignment(null) - { - Indent = 0, - Horizontal = XLAlignmentHorizontalValues.General, - JustifyLastLine = false, - ReadingOrder = XLAlignmentReadingOrderValues.ContextDependent, - RelativeIndent = 0, - ShrinkToFit = false, - TextRotation = 0, - Vertical = XLAlignmentVerticalValues.Bottom, - WrapText = false - }, - Protection = new XLProtection(null) - { - Locked = true, - Hidden = false - } - }); + return _defaultStyle + ?? (_defaultStyle = new XLStyle(null) + { + Font = new XLFont(null, null) + { + Bold = false, + Italic = false, + Underline = XLFontUnderlineValues.None, + Strikethrough = false, + VerticalAlignment = XLFontVerticalTextAlignmentValues.Baseline, + FontSize = 11, + FontColor = XLColor.FromArgb(0, 0, 0), + FontName = "Calibri", + FontFamilyNumbering = XLFontFamilyNumberingValues.Swiss, + FontCharSet = XLFontCharSet.Default + }, + Fill = new XLFill(null) + { + BackgroundColor = XLColor.FromIndex(64), + PatternType = XLFillPatternValues.None, + PatternColor = XLColor.FromIndex(64) + }, + Border = new XLBorder(null, null) + { + BottomBorder = XLBorderStyleValues.None, + DiagonalBorder = XLBorderStyleValues.None, + DiagonalDown = false, + DiagonalUp = false, + LeftBorder = XLBorderStyleValues.None, + RightBorder = XLBorderStyleValues.None, + TopBorder = XLBorderStyleValues.None, + BottomBorderColor = XLColor.Black, + DiagonalBorderColor = XLColor.Black, + LeftBorderColor = XLColor.Black, + RightBorderColor = XLColor.Black, + TopBorderColor = XLColor.Black + }, + NumberFormat = new XLNumberFormat(null, null) { NumberFormatId = 0 }, + Alignment = new XLAlignment(null) + { + Indent = 0, + Horizontal = XLAlignmentHorizontalValues.General, + JustifyLastLine = false, + ReadingOrder = XLAlignmentReadingOrderValues.ContextDependent, + RelativeIndent = 0, + ShrinkToFit = false, + TextRotation = 0, + Vertical = XLAlignmentVerticalValues.Bottom, + WrapText = false + }, + Protection = new XLProtection(null) + { + Locked = true, + Hidden = false + } + }); } } @@ -116,24 +118,24 @@ get { var defaultPageOptions = new XLPageSetup(null, null) - { - PageOrientation = XLPageOrientation.Default, - Scale = 100, - PaperSize = XLPaperSize.LetterPaper, - Margins = new XLMargins - { - Top = 0.75, - Bottom = 0.5, - Left = 0.75, - Right = 0.75, - Header = 0.5, - Footer = 0.75 - }, - ScaleHFWithDocument = true, - AlignHFWithMargins = true, - PrintErrorValue = XLPrintErrorValues.Displayed, - ShowComments = XLShowCommentsValues.None - }; + { + PageOrientation = XLPageOrientation.Default, + Scale = 100, + PaperSize = XLPaperSize.LetterPaper, + Margins = new XLMargins + { + Top = 0.75, + Bottom = 0.5, + Left = 0.75, + Right = 0.75, + Header = 0.5, + Footer = 0.75 + }, + ScaleHFWithDocument = true, + AlignHFWithMargins = true, + PrintErrorValue = XLPrintErrorValues.Displayed, + ShowComments = XLShowCommentsValues.None + }; return defaultPageOptions; } } @@ -143,10 +145,10 @@ get { return new XLOutline(null) - { - SummaryHLocation = XLOutlineSummaryHLocation.Right, - SummaryVLocation = XLOutlineSummaryVLocation.Bottom - }; + { + SummaryHLocation = XLOutlineSummaryHLocation.Right, + SummaryVLocation = XLOutlineSummaryVLocation.Bottom + }; } } @@ -323,20 +325,20 @@ private void InitializeTheme() { Theme = new XLTheme - { - Text1 = XLColor.FromHtml("#FF000000"), - Background1 = XLColor.FromHtml("#FFFFFFFF"), - Text2 = XLColor.FromHtml("#FF1F497D"), - Background2 = XLColor.FromHtml("#FFEEECE1"), - Accent1 = XLColor.FromHtml("#FF4F81BD"), - Accent2 = XLColor.FromHtml("#FFC0504D"), - Accent3 = XLColor.FromHtml("#FF9BBB59"), - Accent4 = XLColor.FromHtml("#FF8064A2"), - Accent5 = XLColor.FromHtml("#FF4BACC6"), - Accent6 = XLColor.FromHtml("#FFF79646"), - Hyperlink = XLColor.FromHtml("#FF0000FF"), - FollowedHyperlink = XLColor.FromHtml("#FF800080") - }; + { + Text1 = XLColor.FromHtml("#FF000000"), + Background1 = XLColor.FromHtml("#FFFFFFFF"), + Text2 = XLColor.FromHtml("#FF1F497D"), + Background2 = XLColor.FromHtml("#FFEEECE1"), + Accent1 = XLColor.FromHtml("#FF4F81BD"), + Accent2 = XLColor.FromHtml("#FFC0504D"), + Accent3 = XLColor.FromHtml("#FF9BBB59"), + Accent4 = XLColor.FromHtml("#FF8064A2"), + Accent5 = XLColor.FromHtml("#FF4BACC6"), + Accent6 = XLColor.FromHtml("#FFF79646"), + Hyperlink = XLColor.FromHtml("#FF0000FF"), + FollowedHyperlink = XLColor.FromHtml("#FF800080") + }; } internal XLColor GetXLColor(XLThemeColor themeColor) diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs index 13daf1e..b66fba5 100644 --- a/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/Excel/XLWorkbook_Load.cs @@ -1,3 +1,4 @@ +using ClosedXML.Extensions; using ClosedXML.Utils; using DocumentFormat.OpenXml; using DocumentFormat.OpenXml.Packaging; @@ -260,6 +261,8 @@ LoadRowBreaks((RowBreaks)reader.LoadCurrentElement(), ws); else if (reader.ElementType == typeof(ColumnBreaks)) LoadColumnBreaks((ColumnBreaks)reader.LoadCurrentElement(), ws); + else if (reader.ElementType == typeof(WorksheetExtensionList)) + LoadExtensions((WorksheetExtensionList)reader.LoadCurrentElement(), ws); else if (reader.ElementType == typeof(LegacyDrawing)) ws.LegacyDrawingId = (reader.LoadCurrentElement() as LegacyDrawing).Id.Value; } @@ -1436,11 +1439,14 @@ if (fontColor.HasValue) fontBase.FontColor = fontColor; - var fontFamilyNumbering = - fontSource.Elements().FirstOrDefault(); + var fontFamilyNumbering = fontSource.Elements().FirstOrDefault(); if (fontFamilyNumbering != null && fontFamilyNumbering.Val != null) - fontBase.FontFamilyNumbering = - (XLFontFamilyNumberingValues)Int32.Parse(fontFamilyNumbering.Val.ToString()); + fontBase.FontFamilyNumbering = (XLFontFamilyNumberingValues)Int32.Parse(fontFamilyNumbering.Val.ToString()); + + var fontCharSet = fontSource.Elements().FirstOrDefault(); + if (fontCharSet != null && fontCharSet.Val != null) + fontBase.FontCharSet = (XLFontCharSet)Int32.Parse(fontCharSet.Val.ToString()); + var runFont = fontSource.Elements().FirstOrDefault(); if (runFont != null) { @@ -1872,6 +1878,11 @@ var dataBar = fr.Elements().First(); if (dataBar.ShowValue != null) conditionalFormat.ShowBarOnly = !dataBar.ShowValue.Value; + + var id = fr.Descendants().FirstOrDefault(); + if (id != null && id.Text != null && !String.IsNullOrWhiteSpace(id.Text)) + conditionalFormat.Id = Guid.Parse(id.Text.Substring(1, id.Text.Length - 2)); + ExtractConditionalFormatValueObjects(conditionalFormat, dataBar); } else if (fr.Elements().Any()) @@ -1906,6 +1917,32 @@ } } + private void LoadExtensions(WorksheetExtensionList extensions, XLWorksheet ws) + { + if (extensions == null) + { + return; + } + + foreach (var conditionalFormattingRule in extensions + .Descendants() + .Where(cf => + cf.Type != null + && cf.Type.HasValue + && cf.Type.Value == ConditionalFormatValues.DataBar)) + { + var xlConditionalFormat = ws.ConditionalFormats + .Cast() + .SingleOrDefault(cf => cf.Id.WrapInBraces() == conditionalFormattingRule.Id); + if (xlConditionalFormat != null) + { + var negativeFillColor = conditionalFormattingRule.Descendants().SingleOrDefault(); + var color = new DocumentFormat.OpenXml.Spreadsheet.Color { Rgb = negativeFillColor.Rgb }; + xlConditionalFormat.Colors.Add(this.GetColor(color)); + } + } + } + private static XLFormula GetFormula(String value) { var formula = new XLFormula(); diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index e7f2213..d50c1d1 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -2082,6 +2082,12 @@ EnableDrill = GetBooleanValue(pt.EnableShowDetails, true) }; + if (pt.ClassicPivotTableLayout) + { + pivotTableDefinition.Compact = false; + pivotTableDefinition.CompactData = false; + } + if (pt.EmptyCellReplacement != null) { pivotTableDefinition.ShowMissing = true; @@ -2170,6 +2176,12 @@ IXLPivotField labelField = null; var pf = new PivotField { ShowAll = false, Name = xlpf.CustomName }; + if (pt.ClassicPivotTableLayout) + { + pf.Outline = false; + pf.Compact = false; + } + switch (pt.Subtotals) { case XLPivotSubtotals.DoNotShow: @@ -3634,6 +3646,10 @@ ? new FontFamilyNumbering { Val = (Int32)fontInfo.Font.FontFamilyNumbering } : null; + var fontCharSet = (fontInfo.Font.FontCharSetModified || ignoreMod) && fontInfo.Font.FontCharSet != XLFontCharSet.Default + ? new FontCharSet { Val = (Int32)fontInfo.Font.FontCharSet } + : null; + if (bold != null) font.AppendChild(bold); if (italic != null) @@ -3654,6 +3670,8 @@ font.AppendChild(fontName); if (fontFamilyNumbering != null) font.AppendChild(fontFamilyNumbering); + if (fontCharSet != null) + font.AppendChild(fontCharSet); return font; } @@ -4530,6 +4548,60 @@ } } + + var exlst = from c in xlWorksheet.ConditionalFormats where c.ConditionalFormatType == XLConditionalFormatType.DataBar && c.Colors.Count > 1 && typeof(IXLConditionalFormat).IsAssignableFrom(c.GetType()) select c; + if (exlst != null && exlst.Count() > 0) + { + if (!worksheetPart.Worksheet.Elements().Any()) + { + var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.WorksheetExtensionList); + worksheetPart.Worksheet.InsertAfter(new WorksheetExtensionList(), previousElement); + } + + WorksheetExtensionList worksheetExtensionList = worksheetPart.Worksheet.Elements().First(); + cm.SetElement(XLWSContentManager.XLWSContents.WorksheetExtensionList, worksheetExtensionList); + + var conditionalFormattings = worksheetExtensionList.Descendants().SingleOrDefault(); + if (conditionalFormattings == null || !conditionalFormattings.Any()) + { + WorksheetExtension worksheetExtension1 = new WorksheetExtension { Uri = "{78C0D931-6437-407d-A8EE-F0AAD7539E65}" }; + worksheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"); + worksheetExtensionList.Append(worksheetExtension1); + + conditionalFormattings = new DocumentFormat.OpenXml.Office2010.Excel.ConditionalFormattings(); + worksheetExtension1.Append(conditionalFormattings); + } + + foreach (var cfGroup in exlst + .GroupBy( + c => c.Range.RangeAddress.ToStringRelative(false), + c => c, + (key, g) => new { RangeId = key, CfList = g.ToList() } + ) + ) + { + foreach (var xlConditionalFormat in cfGroup.CfList.Cast()) + { + var conditionalFormattingRule = conditionalFormattings.Descendants() + .SingleOrDefault(r => r.Id == xlConditionalFormat.Id.WrapInBraces()); + if (conditionalFormattingRule != null) + { + var conditionalFormat = conditionalFormattingRule.Ancestors().SingleOrDefault(); + conditionalFormattings.RemoveChild(conditionalFormat); + } + + var conditionalFormatting = new DocumentFormat.OpenXml.Office2010.Excel.ConditionalFormatting(); + conditionalFormatting.AddNamespaceDeclaration("xm", "http://schemas.microsoft.com/office/excel/2006/main"); + conditionalFormatting.Append(XLCFConvertersExtension.Convert(xlConditionalFormat, context)); + var referenceSequence = new DocumentFormat.OpenXml.Office.Excel.ReferenceSequence { Text = cfGroup.RangeId }; + conditionalFormatting.Append(referenceSequence); + + conditionalFormattings.Append(conditionalFormatting); + } + } + } + + #endregion Conditional Formatting #region DataValidations diff --git a/ClosedXML/Extensions.cs b/ClosedXML/Extensions.cs index bc42b67..8c0111b 100644 --- a/ClosedXML/Extensions.cs +++ b/ClosedXML/Extensions.cs @@ -241,6 +241,7 @@ font.FontColor = sourceFont.FontColor; font.FontName = sourceFont.FontName; font.FontFamilyNumbering = sourceFont.FontFamilyNumbering; + font.FontCharSet = sourceFont.FontCharSet; } private static Font GetCachedFont(IXLFontBase fontBase, Dictionary fontCache) diff --git a/ClosedXML/Extensions/GuidExtensions.cs b/ClosedXML/Extensions/GuidExtensions.cs new file mode 100644 index 0000000..c9d2eba --- /dev/null +++ b/ClosedXML/Extensions/GuidExtensions.cs @@ -0,0 +1,12 @@ +using System; + +namespace ClosedXML.Extensions +{ + internal static class GuidExtensions + { + internal static String WrapInBraces(this Guid guid) + { + return string.Concat('{', guid.ToString(), '}'); + } + } +} diff --git a/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs b/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs index a87bf0c..27656bf 100644 --- a/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs +++ b/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs @@ -1,6 +1,5 @@ -using System; using ClosedXML.Excel; - +using System; namespace ClosedXML_Examples { @@ -27,7 +26,6 @@ public class CFColorScaleLowHigh : IXLExample { - public void Create(String filePath) { var workbook = new XLWorkbook(); @@ -221,7 +219,6 @@ } } - public class CFEqualsString : IXLExample { public void Create(String filePath) @@ -527,6 +524,39 @@ } } + public class CFDataBarNegative : IXLExample + { + public void Create(String filePath) + { + var workbook = new XLWorkbook(); + var ws = workbook.AddWorksheet("Sheet1"); + + ws.Cell(1, 1).SetValue(-1) + .CellBelow().SetValue(1) + .CellBelow().SetValue(2) + .CellBelow().SetValue(3); + + ws.Range(ws.Cell(1, 1), ws.Cell(4, 1)) + .AddConditionalFormat() + .DataBar(XLColor.Green, XLColor.Red, showBarOnly: false) + .LowestValue() + .HighestValue(); + + ws.Cell(1,3).SetValue(-20) + .CellBelow().SetValue(40) + .CellBelow().SetValue(-60) + .CellBelow().SetValue(30); + + ws.Range(ws.Cell(1, 3), ws.Cell(4, 3)) + .AddConditionalFormat() + .DataBar(XLColor.Green, XLColor.Red, showBarOnly: true) + .Minimum(XLCFContentType.Number, -100) + .Maximum(XLCFContentType.Number, 100); + + workbook.SaveAs(filePath); + } + } + public class CFIconSet : IXLExample { public void Create(String filePath) @@ -579,7 +609,7 @@ var workbook = new XLWorkbook(); var ws = workbook.AddWorksheet("Sheet1"); - ws.Cell(2,1).SetValue(1) + ws.Cell(2, 1).SetValue(1) .CellRight().SetValue(1) .CellRight().SetValue(2) .CellRight().SetValue(3); @@ -588,7 +618,6 @@ range.AddConditionalFormat().WhenEquals("1").Font.SetBold(); range.InsertRowsAbove(1); - workbook.SaveAs(filePath); } } @@ -606,7 +635,7 @@ .CellBelow().SetValue(3) .CellBelow().SetValue(4); - ws.RangeUsed().AddConditionalFormat().DataBar(XLColor.Red) + ws.RangeUsed().AddConditionalFormat().DataBar(XLColor.Red, XLColor.Green) .LowestValue() .HighestValue(); @@ -621,7 +650,7 @@ var workbook = new XLWorkbook(); var ws = workbook.AddWorksheet("Sheet1"); - using(var range = ws.Range("A1:A10")) + using (var range = ws.Range("A1:A10")) { range.AddConditionalFormat().WhenEquals("3") .Fill.SetBackgroundColor(XLColor.Blue); diff --git a/ClosedXML_Examples/Misc/AutoFilter.cs b/ClosedXML_Examples/Misc/AutoFilter.cs index 21b8402..bb119b2 100644 --- a/ClosedXML_Examples/Misc/AutoFilter.cs +++ b/ClosedXML_Examples/Misc/AutoFilter.cs @@ -50,11 +50,11 @@ ws.Cell("A4").Value = "Dagny"; ws.RangeUsed().SetAutoFilter(); - - // Your can turn off the autofilter in three ways: - // 1) worksheet.AutoFilterRange.SetAutoFilter(false) - // 2) worksheet.AutoFilterRange = null - // 3) Pick any range in the worksheet and call range.SetAutoFilter(false); + + // Your can turn off the autofilter by: + // 1) worksheet.AutoFilter.Clear() + // 2) worksheet.SetAutoFilter(false) + // 3) Pick any range in the worksheet and call the above methods on the range wb.SaveAs(filePath); } diff --git a/ClosedXML_Examples/Styles/StyleFont.cs b/ClosedXML_Examples/Styles/StyleFont.cs index 7da4adf..6fed30f 100644 --- a/ClosedXML_Examples/Styles/StyleFont.cs +++ b/ClosedXML_Examples/Styles/StyleFont.cs @@ -1,6 +1,5 @@ +using ClosedXML.Excel; using System; -using ClosedXML.Excel; - namespace ClosedXML_Examples.Styles { @@ -23,6 +22,11 @@ ws.Cell(++ro, co).Value = "FontFamilyNumbering - Script"; ws.Cell(ro, co).Style.Font.FontFamilyNumbering = XLFontFamilyNumberingValues.Script; + ws.Cell(++ro, co).Value = "FontCharSet - العربية التنضيد"; + ws.Cell(ro, co).Style + .Font.SetFontName("Arabic Typesetting") + .Font.SetFontCharSet(XLFontCharSet.Arabic); + ws.Cell(++ro, co).Value = "FontName - Stencil"; ws.Cell(ro, co).Style.Font.FontName = "Stencil"; diff --git a/ClosedXML_Tests/Examples/ConditionalFormattingTests.cs b/ClosedXML_Tests/Examples/ConditionalFormattingTests.cs index 0760da0..8a298ae 100644 --- a/ClosedXML_Tests/Examples/ConditionalFormattingTests.cs +++ b/ClosedXML_Tests/Examples/ConditionalFormattingTests.cs @@ -37,6 +37,12 @@ } [Test] + public void CFDataBarNegative() + { + TestHelper.RunTestExample(@"ConditionalFormatting\CFDataBarNegative.xlsx"); + } + + [Test] public void CFEndsWith() { TestHelper.RunTestExample(@"ConditionalFormatting\CFEndsWith.xlsx"); diff --git a/ClosedXML_Tests/Excel/AutoFilters/AutoFilterTests.cs b/ClosedXML_Tests/Excel/AutoFilters/AutoFilterTests.cs index 25dff84..da8bde2 100644 --- a/ClosedXML_Tests/Excel/AutoFilters/AutoFilterTests.cs +++ b/ClosedXML_Tests/Excel/AutoFilters/AutoFilterTests.cs @@ -72,5 +72,27 @@ ws.AutoFilter.Clear(); Assert.That(!ws.AutoFilter.Enabled); } + + [Test] + public void CanClearAutoFilter2() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.Worksheets.Add("AutoFilter"); + ws.Cell("A1").Value = "Names"; + ws.Cell("A2").Value = "John"; + ws.Cell("A3").Value = "Hank"; + ws.Cell("A4").Value = "Dagny"; + + ws.SetAutoFilter(false); + Assert.That(!ws.AutoFilter.Enabled); + + ws.RangeUsed().SetAutoFilter(); + Assert.That(ws.AutoFilter.Enabled); + + ws.RangeUsed().SetAutoFilter(false); + Assert.That(!ws.AutoFilter.Enabled); + } + } } } diff --git a/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs b/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs index 5012bb5..b9003d2 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs @@ -1,6 +1,8 @@ using ClosedXML.Excel; +using ClosedXML.Excel.CalcEngine.Exceptions; using NUnit.Framework; using System; +using System.Linq; namespace ClosedXML_Tests.Excel.CalcEngine { @@ -119,5 +121,23 @@ actual = (double)XLWorkbook.EvaluateExpr(@"MOD(6.2, 1.1)"); Assert.AreEqual(0.7, actual, tolerance); } + + [Test] + public void SumProduct() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + + ws.FirstCell().Value = Enumerable.Range(1, 10); + ws.FirstCell().CellRight().Value = Enumerable.Range(1, 10).Reverse(); + + Assert.AreEqual(2, ws.Evaluate("SUMPRODUCT(A2)")); + Assert.AreEqual(55, ws.Evaluate("SUMPRODUCT(A1:A10)")); + Assert.AreEqual(220, ws.Evaluate("SUMPRODUCT(A1:A10, B1:B10)")); + + Assert.Throws(() => ws.Evaluate("SUMPRODUCT(A1:A10, B1:B5)")); + } + } } } diff --git a/ClosedXML_Tests/Excel/Cells/XLCellTests.cs b/ClosedXML_Tests/Excel/Cells/XLCellTests.cs index cfa1951..161f17a 100644 --- a/ClosedXML_Tests/Excel/Cells/XLCellTests.cs +++ b/ClosedXML_Tests/Excel/Cells/XLCellTests.cs @@ -235,7 +235,6 @@ Assert.IsFalse(success); } - [Test] public void TryGetValue_DateTime_BadString2() { @@ -426,5 +425,90 @@ Assert.AreEqual(string.Empty, cell.Value); } } + + [Test] + public void CurrentRegion() + { + // Partially based on sample in https://github.com/ClosedXML/ClosedXML/issues/120 + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + + ws.Cell("B1").SetValue("x") + .CellBelow().SetValue("x") + .CellBelow().SetValue("x"); + + ws.Cell("C1").SetValue("x") + .CellBelow().SetValue("x") + .CellBelow().SetValue("x"); + + //Deliberately D2 + ws.Cell("D2").SetValue("x") + .CellBelow().SetValue("x"); + + ws.Cell("G1").SetValue("x") + .CellBelow() // skip a cell + .CellBelow().SetValue("x") + .CellBelow().SetValue("x"); + + // Deliberately H2 + ws.Cell("H2").SetValue("x") + .CellBelow().SetValue("x") + .CellBelow().SetValue("x"); + + // A diagonal + ws.Cell("E8").SetValue("x") + .CellBelow().CellRight().SetValue("x") + .CellBelow().CellRight().SetValue("x") + .CellBelow().CellRight().SetValue("x") + .CellBelow().CellRight().SetValue("x"); + + Assert.AreEqual("A10:A10", ws.Cell("A10").CurrentRegion.RangeAddress.ToString()); + Assert.AreEqual("B5:B5", ws.Cell("B5").CurrentRegion.RangeAddress.ToString()); + Assert.AreEqual("P1:P1", ws.Cell("P1").CurrentRegion.RangeAddress.ToString()); + + Assert.AreEqual("B1:D3", ws.Cell("D3").CurrentRegion.RangeAddress.ToString()); + Assert.AreEqual("B1:D4", ws.Cell("D4").CurrentRegion.RangeAddress.ToString()); + Assert.AreEqual("B1:E4", ws.Cell("E4").CurrentRegion.RangeAddress.ToString()); + + foreach (var c in ws.Range("B1:D3").Cells()) + { + Assert.AreEqual("B1:D3", c.CurrentRegion.RangeAddress.ToString()); + } + + foreach (var c in ws.Range("A1:A3").Cells()) + { + Assert.AreEqual("A1:D3", c.CurrentRegion.RangeAddress.ToString()); + } + + Assert.AreEqual("A1:D4", ws.Cell("A4").CurrentRegion.RangeAddress.ToString()); + + foreach (var c in ws.Range("E1:E3").Cells()) + { + Assert.AreEqual("B1:E3", c.CurrentRegion.RangeAddress.ToString()); + } + Assert.AreEqual("B1:E4", ws.Cell("E4").CurrentRegion.RangeAddress.ToString()); + + //// SECOND REGION + foreach (var c in ws.Range("F1:F4").Cells()) + { + Assert.AreEqual("F1:H4", c.CurrentRegion.RangeAddress.ToString()); + } + Assert.AreEqual("F1:H5", ws.Cell("F5").CurrentRegion.RangeAddress.ToString()); + + //// DIAGONAL + Assert.AreEqual("E8:I12", ws.Cell("E8").CurrentRegion.RangeAddress.ToString()); + Assert.AreEqual("E8:I12", ws.Cell("F9").CurrentRegion.RangeAddress.ToString()); + Assert.AreEqual("E8:I12", ws.Cell("G10").CurrentRegion.RangeAddress.ToString()); + Assert.AreEqual("E8:I12", ws.Cell("H11").CurrentRegion.RangeAddress.ToString()); + Assert.AreEqual("E8:I12", ws.Cell("I12").CurrentRegion.RangeAddress.ToString()); + + Assert.AreEqual("E8:I12", ws.Cell("G9").CurrentRegion.RangeAddress.ToString()); + Assert.AreEqual("E8:I12", ws.Cell("F10").CurrentRegion.RangeAddress.ToString()); + + Assert.AreEqual("D7:I12", ws.Cell("D7").CurrentRegion.RangeAddress.ToString()); + Assert.AreEqual("E8:J13", ws.Cell("J13").CurrentRegion.RangeAddress.ToString()); + } + } } } diff --git a/ClosedXML_Tests/Excel/ImageHandling/PictureTests.cs b/ClosedXML_Tests/Excel/ImageHandling/PictureTests.cs index 16e609b..bfb9e96 100644 --- a/ClosedXML_Tests/Excel/ImageHandling/PictureTests.cs +++ b/ClosedXML_Tests/Excel/ImageHandling/PictureTests.cs @@ -283,5 +283,40 @@ Assert.Throws(() => picture.Name = "picTURE 1"); } } + + [Test] + public void HandleDuplicatePictureIdsAcrossWorksheets() + { + using (var wb = new XLWorkbook()) + { + var ws1 = wb.AddWorksheet("Sheet1"); + var ws2 = wb.AddWorksheet("Sheet2"); + + using (var stream = Assembly.GetExecutingAssembly().GetManifestResourceStream("ClosedXML_Tests.Resource.Images.ImageHandling.png")) + { + (ws1 as XLWorksheet).AddPicture(stream, "Picture 1", 2); + (ws1 as XLWorksheet).AddPicture(stream, "Picture 2", 3); + + //Internal method - used for loading files + var pic = (ws2 as XLWorksheet).AddPicture(stream, "Picture 1", 2) + .WithPlacement(XLPicturePlacement.FreeFloating) + .MoveTo(220, 155) as XLPicture; + + var id = pic.Id; + + pic.Id = id; + Assert.AreEqual(id, pic.Id); + + pic.Id = 3; + Assert.AreEqual(3, pic.Id); + + pic.Id = id; + + var pic2 = (ws2 as XLWorksheet).AddPicture(stream, "Picture 2", 3) + .WithPlacement(XLPicturePlacement.FreeFloating) + .MoveTo(440, 300) as XLPicture; + } + } + } } } diff --git a/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTests.cs b/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTests.cs index 348b213..28a7b6f 100644 --- a/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTests.cs +++ b/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTests.cs @@ -1,7 +1,7 @@ -using System; -using System.Linq; using ClosedXML.Excel; using NUnit.Framework; +using System; +using System.Linq; namespace ClosedXML_Tests { @@ -106,7 +106,7 @@ IXLRange namedRange = wb.Range("FNameColumn"); Assert.AreEqual(3, namedRange.Cells().Count()); Assert.IsTrue( - namedRange.CellsUsed().Select(cell => cell.GetString()).SequenceEqual(new[] {"John", "Hank", "Dagny"})); + namedRange.CellsUsed().Select(cell => cell.GetString()).SequenceEqual(new[] { "John", "Hank", "Dagny" })); } [Test] @@ -184,7 +184,119 @@ // memoryStream.Close(); // } - //} + + [Test] + public void GrowRange() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + Assert.AreEqual("A1:B2", ws.Cell("A1").AsRange().Grow().RangeAddress.ToString()); + Assert.AreEqual("A1:B3", ws.Cell("A2").AsRange().Grow().RangeAddress.ToString()); + Assert.AreEqual("A1:C2", ws.Cell("B1").AsRange().Grow().RangeAddress.ToString()); + + Assert.AreEqual("E4:G6", ws.Cell("F5").AsRange().Grow().RangeAddress.ToString()); + Assert.AreEqual("D3:H7", ws.Cell("F5").AsRange().Grow(2).RangeAddress.ToString()); + Assert.AreEqual("A1:DB105", ws.Cell("F5").AsRange().Grow(100).RangeAddress.ToString()); + } + } + + [Test] + public void ShrinkRange() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + Assert.Null(ws.Cell("A1").AsRange().Shrink()); + Assert.Null(ws.Range("B2:C3").Shrink()); + Assert.AreEqual("C3:C3", ws.Range("B2:D4").Shrink().RangeAddress.ToString()); + Assert.AreEqual("K11:P16", ws.Range("A1:Z26").Shrink(10).RangeAddress.ToString()); + + // Grow and shrink back + Assert.AreEqual("Z26:Z26", ws.Cell("Z26").AsRange().Grow(10).Shrink(10).RangeAddress.ToString()); + } + } + + [Test] + public void Intersection() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + + Assert.AreEqual("D9:G11", ws.Range("B9:I11").Intersection(ws.Range("D4:G16")).RangeAddress.ToString()); + Assert.AreEqual("E9:G11", ws.Range("E9:I11").Intersection(ws.Range("D4:G16")).RangeAddress.ToString()); + Assert.AreEqual("E9:E9", ws.Cell("E9").AsRange().Intersection(ws.Range("D4:G16")).RangeAddress.ToString()); + Assert.AreEqual("E9:E9", ws.Range("D4:G16").Intersection(ws.Cell("E9").AsRange()).RangeAddress.ToString()); + + Assert.Null(ws.Cell("A1").AsRange().Intersection(ws.Cell("C3").AsRange())); + + Assert.Null(ws.Range("A1:C3").Intersection(null)); + + var otherWs = wb.AddWorksheet("Sheet2"); + Assert.Null(ws.Intersection(otherWs)); + Assert.Null(ws.Cell("A1").AsRange().Intersection(otherWs.Cell("A2").AsRange())); + } + } + + [Test] + public void Union() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + + Assert.AreEqual(64, ws.Range("B9:I11").Union(ws.Range("D4:G16")).Count()); + Assert.AreEqual(58, ws.Range("E9:I11").Union(ws.Range("D4:G16")).Count()); + Assert.AreEqual(52, ws.Cell("E9").AsRange().Union(ws.Range("D4:G16")).Count()); + Assert.AreEqual(52, ws.Range("D4:G16").Union(ws.Cell("E9").AsRange()).Count()); + + Assert.AreEqual(2, ws.Cell("A1").AsRange().Union(ws.Cell("C3").AsRange()).Count()); + + Assert.AreEqual(9, ws.Range("A1:C3").Union(null).Count()); + + var otherWs = wb.AddWorksheet("Sheet2"); + Assert.False(ws.Union(otherWs).Any()); + Assert.False(ws.Cell("A1").AsRange().Union(otherWs.Cell("A2").AsRange()).Any()); + } + } + + [Test] + public void Difference() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + + Assert.AreEqual(12, ws.Range("B9:I11").Difference(ws.Range("D4:G16")).Count()); + Assert.AreEqual(6, ws.Range("E9:I11").Difference(ws.Range("D4:G16")).Count()); + Assert.AreEqual(0, ws.Cell("E9").AsRange().Difference(ws.Range("D4:G16")).Count()); + Assert.AreEqual(51, ws.Range("D4:G16").Difference(ws.Cell("E9").AsRange()).Count()); + + Assert.AreEqual(1, ws.Cell("A1").AsRange().Difference(ws.Cell("C3").AsRange()).Count()); + + Assert.AreEqual(9, ws.Range("A1:C3").Difference(null).Count()); + + var otherWs = wb.AddWorksheet("Sheet2"); + Assert.False(ws.Difference(otherWs).Any()); + Assert.False(ws.Cell("A1").AsRange().Difference(otherWs.Cell("A2").AsRange()).Any()); + } + } + + [Test] + public void SurroundingCells() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + + Assert.AreEqual(3, ws.FirstCell().AsRange().SurroundingCells().Count()); + Assert.AreEqual(8, ws.Cell("C3").AsRange().SurroundingCells().Count()); + Assert.AreEqual(16, ws.Range("C3:D6").AsRange().SurroundingCells().Count()); + + Assert.AreEqual(0, ws.Range("C3:D6").AsRange().SurroundingCells(c => !c.IsEmpty()).Count()); + } + } } } diff --git a/ClosedXML_Tests/Excel/Tables/TablesTests.cs b/ClosedXML_Tests/Excel/Tables/TablesTests.cs index 1378f90..b889e08 100644 --- a/ClosedXML_Tests/Excel/Tables/TablesTests.cs +++ b/ClosedXML_Tests/Excel/Tables/TablesTests.cs @@ -52,6 +52,20 @@ } [Test] + public void PreventAddingOfEmptyDataTable() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + + var dt = new DataTable(); + var table = ws.FirstCell().InsertTable(dt); + + Assert.AreEqual(null, table); + } + } + + [Test] public void CanSaveTableCreatedFromSingleRow() { using (var wb = new XLWorkbook()) diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFDataBarNegative.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFDataBarNegative.xlsx new file mode 100644 index 0000000..fa46948 --- /dev/null +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFDataBarNegative.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Styles/StyleFont.xlsx b/ClosedXML_Tests/Resource/Examples/Styles/StyleFont.xlsx index 0dadd37..a9c9728 100644 --- a/ClosedXML_Tests/Resource/Examples/Styles/StyleFont.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Styles/StyleFont.xlsx Binary files differ diff --git a/ClosedXML_Tests/Utils/StreamHelper.cs b/ClosedXML_Tests/Utils/StreamHelper.cs index b68a11f..8ecb4f9 100644 --- a/ClosedXML_Tests/Utils/StreamHelper.cs +++ b/ClosedXML_Tests/Utils/StreamHelper.cs @@ -51,7 +51,7 @@ throw new ArgumentException("Can't write to stream", "pStream"); } - #endregion + #endregion Check params foreach (byte b in pBynaryArray) { @@ -86,7 +86,7 @@ throw new ArgumentException("Can't write to stream", "streamToWrite"); } - #endregion + #endregion Check params var buf = new byte[512]; long length; @@ -135,37 +135,53 @@ throw new ArgumentException("Must be in position 0", "other"); } - #endregion + #endregion Check - var stringOne = new StreamReader(one).ReadToEnd().StripColumnWidths(stripColumnWidths); - var stringOther = new StreamReader(other).ReadToEnd().StripColumnWidths(stripColumnWidths); + var stringOne = new StreamReader(one).ReadToEnd().RemoveIgnoredParts(stripColumnWidths, ignoreGuids: true); + var stringOther = new StreamReader(other).ReadToEnd().RemoveIgnoredParts(stripColumnWidths, ignoreGuids: true); return stringOne == stringOther; } + private static string RemoveIgnoredParts(this string s, Boolean ignoreColumnWidths, Boolean ignoreGuids) + { + if (ignoreColumnWidths) + s = RemoveColumnWidths(s); + + if (ignoreGuids) + s = RemoveGuids(s); + + return s; + } + private static Regex columnRegex = new Regex("", RegexOptions.Compiled); private static Regex widthRegex = new Regex("width=\"\\d+(\\.\\d+)?\"\\s+", RegexOptions.Compiled); - private static string StripColumnWidths(this string s, bool stripIt) + private static String RemoveColumnWidths(String s) { - if (!stripIt) - return s; - else - { - var replacements = new Dictionary(); - - foreach (var m in columnRegex.Matches(s).OfType()) - { - var original = m.Groups[0].Value; - var replacement = widthRegex.Replace(original, ""); - replacements.Add(original, replacement); - } + var replacements = new Dictionary(); - foreach (var r in replacements) - { - s = s.Replace(r.Key, r.Value); - } - return s; + foreach (var m in columnRegex.Matches(s).OfType()) + { + var original = m.Groups[0].Value; + var replacement = widthRegex.Replace(original, ""); + replacements.Add(original, replacement); } + + foreach (var r in replacements) + { + s = s.Replace(r.Key, r.Value); + } + return s; + } + + private static Regex guidRegex = new Regex(@"{[0-9A-Fa-f]{8}-([0-9A-Fa-f]{4}-){3}[0-9A-Fa-f]{12}}", RegexOptions.Compiled | RegexOptions.Multiline); + + private static String RemoveGuids(String s) + { + return guidRegex.Replace(s, delegate (Match m) + { + return string.Empty; + }); } } -} \ No newline at end of file +}