diff --git a/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML.csproj index d4073e5..5960577 100644 --- a/ClosedXML/ClosedXML.csproj +++ b/ClosedXML/ClosedXML.csproj @@ -62,6 +62,7 @@ + diff --git a/ClosedXML/Excel/CalcEngine/CalcEngineHelpers.cs b/ClosedXML/Excel/CalcEngine/CalcEngineHelpers.cs new file mode 100644 index 0000000..b5853b8 --- /dev/null +++ b/ClosedXML/Excel/CalcEngine/CalcEngineHelpers.cs @@ -0,0 +1,74 @@ +using System; +using System.Diagnostics; +using System.Text.RegularExpressions; + +namespace ClosedXML.Excel.CalcEngine +{ + internal class CalcEngineHelpers + { + internal static bool ValueSatisfiesCriteria(object value, object criteria, CalcEngine ce) + { + // safety... + if (value == null) + { + return false; + } + + // if criteria is a number, straight comparison + if (criteria is double) + { + if (value is Double) + return (double)value == (double)criteria; + Double dValue; + return Double.TryParse(value.ToString(), out dValue) && dValue == (double)criteria; + } + + // convert criteria to string + var cs = criteria as string; + if (!string.IsNullOrEmpty(cs)) + { + // if criteria is an expression (e.g. ">20"), use calc engine + if (cs[0] == '=' || cs[0] == '<' || cs[0] == '>') + { + // build expression + var expression = string.Format("{0}{1}", value, cs); + + // add quotes if necessary + var pattern = @"(\w+)(\W+)(\w+)"; + var m = Regex.Match(expression, pattern); + if (m.Groups.Count == 4) + { + double d; + if (!double.TryParse(m.Groups[1].Value, out d) || + !double.TryParse(m.Groups[3].Value, out d)) + { + expression = string.Format("\"{0}\"{1}\"{2}\"", + m.Groups[1].Value, + m.Groups[2].Value, + m.Groups[3].Value); + } + } + + // evaluate + return (bool)ce.Evaluate(expression); + } + + // if criteria is a regular expression, use regex + if (cs.IndexOf('*') > -1) + { + var pattern = cs.Replace(@"\", @"\\"); + pattern = pattern.Replace(".", @"\"); + pattern = pattern.Replace("*", ".*"); + return Regex.IsMatch(value.ToString(), pattern, RegexOptions.IgnoreCase); + } + + // straight string comparison + return string.Equals(value.ToString(), cs, StringComparison.OrdinalIgnoreCase); + } + + // should never get here? + Debug.Assert(false, "failed to evaluate criteria in SumIf"); + return false; + } + } +} diff --git a/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs b/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs index 4c33e64..48a2bdd 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs @@ -1,10 +1,7 @@ using System; -using System.Diagnostics; using System.Collections; using System.Collections.Generic; -using System.Text; using System.Linq; -using System.Text.RegularExpressions; using ClosedXML.Excel.CalcEngine.Functions; namespace ClosedXML.Excel.CalcEngine @@ -221,7 +218,7 @@ var tally = new Tally(); for (var i = 0; i < Math.Min(rangeValues.Count, sumRangeValues.Count); i++) { - if (ValueSatisfiesCriteria(rangeValues[i], criteria, ce)) + if (CalcEngineHelpers.ValueSatisfiesCriteria(rangeValues[i], criteria, ce)) { tally.AddValue(sumRangeValues[i]); } @@ -231,71 +228,6 @@ return tally.Sum(); } - private static bool ValueSatisfiesCriteria(object value, object criteria, CalcEngine ce) - { - // safety... - if (value == null) - { - return false; - } - - // if criteria is a number, straight comparison - if (criteria is double) - { - if (value is Double) - return (double) value == (double) criteria; - Double dValue; - return Double.TryParse(value.ToString(), out dValue) && dValue == (double) criteria; - } - - // convert criteria to string - var cs = criteria as string; - if (!string.IsNullOrEmpty(cs)) - { - // if criteria is an expression (e.g. ">20"), use calc engine - if (cs[0] == '=' || cs[0] == '<' || cs[0] == '>') - { - // build expression - var expression = string.Format("{0}{1}", value, cs); - - // add quotes if necessary - var pattern = @"(\w+)(\W+)(\w+)"; - var m = Regex.Match(expression, pattern); - if (m.Groups.Count == 4) - { - double d; - if (!double.TryParse(m.Groups[1].Value, out d) || - !double.TryParse(m.Groups[3].Value, out d)) - { - expression = string.Format("\"{0}\"{1}\"{2}\"", - m.Groups[1].Value, - m.Groups[2].Value, - m.Groups[3].Value); - } - } - - // evaluate - return (bool) ce.Evaluate(expression); - } - - // if criteria is a regular expression, use regex - if (cs.IndexOf('*') > -1) - { - var pattern = cs.Replace(@"\", @"\\"); - pattern = pattern.Replace(".", @"\"); - pattern = pattern.Replace("*", ".*"); - return Regex.IsMatch(value.ToString(), pattern, RegexOptions.IgnoreCase); - } - - // straight string comparison - return string.Equals(value.ToString(), cs, StringComparison.OrdinalIgnoreCase); - } - - // should never get here? - Debug.Assert(false, "failed to evaluate criteria in SumIf"); - return false; - } - private static object Tan(List p) { return Math.Tan(p[0]); @@ -609,9 +541,9 @@ case 1: return tally.Average(); case 2: - return tally.Count(); + return tally.Count(true); case 3: - return tally.CountA(); + return tally.Count(false); case 4: return tally.Max(); case 5: @@ -636,7 +568,7 @@ private static object SumSq(List p) { var t = new Tally(p); - return t.Numerics().Sum(v => Math.Pow(v, 2)); + return t.NumericValues().Sum(v => Math.Pow(v, 2)); } private static object MMult(List p) @@ -700,4 +632,4 @@ return m.Invert().mat; } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/CalcEngine/Functions/Statistical.cs b/ClosedXML/Excel/CalcEngine/Functions/Statistical.cs index 973bb90..34b14f1 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/Statistical.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/Statistical.cs @@ -10,7 +10,7 @@ public static void Register(CalcEngine ce) { //ce.RegisterFunction("AVEDEV", AveDev, 1, int.MaxValue); - ce.RegisterFunction("AVERAGE", 1, int.MaxValue, Average); + ce.RegisterFunction("AVERAGE", 1, int.MaxValue, Average); // Returns the average (arithmetic mean) of the arguments ce.RegisterFunction("AVERAGEA", 1, int.MaxValue, AverageA); //BETADIST Returns the beta cumulative distribution function //BETAINV Returns the inverse of the cumulative distribution function for a specified beta distribution @@ -22,7 +22,7 @@ //CORREL Returns the correlation coefficient between two data sets ce.RegisterFunction("COUNT", 1, int.MaxValue, Count); ce.RegisterFunction("COUNTA", 1, int.MaxValue, CountA); - ce.RegisterFunction("COUNTBLANK", 1, int.MaxValue, CountBlank); + ce.RegisterFunction("COUNTBLANK", 1, CountBlank); ce.RegisterFunction("COUNTIF", 2, CountIf); //COVAR Returns covariance, the average of the products of paired deviations //CRITBINOM Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value @@ -77,6 +77,8 @@ ce.RegisterFunction("STDEVA", 1, int.MaxValue, StDevA); ce.RegisterFunction("STDEVP", 1, int.MaxValue, StDevP); ce.RegisterFunction("STDEVPA", 1, int.MaxValue, StDevPA); + ce.RegisterFunction("STDEV.S", 1, int.MaxValue, StDev); + ce.RegisterFunction("STDEV.P", 1, int.MaxValue, StDevP); //STEYX Returns the standard error of the predicted y-value for each x in the regression //TDIST Returns the Student's t-distribution //TINV Returns the inverse of the Student's t-distribution @@ -87,6 +89,8 @@ ce.RegisterFunction("VARA", 1, int.MaxValue, VarA); ce.RegisterFunction("VARP", 1, int.MaxValue, VarP); ce.RegisterFunction("VARPA", 1, int.MaxValue, VarPA); + ce.RegisterFunction("VAR.S", 1, int.MaxValue, Var); + ce.RegisterFunction("VAR.P", 1, int.MaxValue, VarP); //WEIBULL Returns the Weibull distribution //ZTEST Returns the one-tailed probability-value of a z-test } @@ -131,10 +135,10 @@ } return cnt; } - static bool IsBlank(object value) + internal static bool IsBlank(object value) { - return - value == null || + return + value == null || value is string && ((string)value).Length == 0; } static object CountIf(List p) @@ -144,13 +148,12 @@ var ienum = p[0] as IEnumerable; if (ienum != null) { - var crit = (string)p[1].Evaluate(); + var criteria = (string)p[1].Evaluate(); foreach (var value in ienum) { if (!IsBlank(value)) { - var exp = string.Format("{0}{1}", value, crit); - if ((bool)ce.Evaluate(exp)) + if (CalcEngineHelpers.ValueSatisfiesCriteria(value, criteria, ce)) cnt++; } } @@ -209,7 +212,7 @@ // utility for tallying statistics static Tally GetTally(List p, bool numbersOnly) { - return new Tally(p); + return new Tally(p, numbersOnly); } } } diff --git a/ClosedXML/Excel/CalcEngine/Functions/Tally.cs b/ClosedXML/Excel/CalcEngine/Functions/Tally.cs index f7d76fc..7f5766b 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/Tally.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/Tally.cs @@ -1,22 +1,38 @@ using System; -using System.Linq; -using System.Collections.Generic; -using System.Net; using System.Collections; +using System.Collections.Generic; +using System.Linq; namespace ClosedXML.Excel.CalcEngine { - internal class Tally: IEnumerable + internal class Tally : IEnumerable { private readonly List _list = new List(); + private readonly bool NumbersOnly; - public Tally(){} + public Tally() + : this(false) + { } + + public Tally(bool numbersOnly) + : this(null, numbersOnly) + { } + public Tally(IEnumerable p) + : this(p, false) + { } + + public Tally(IEnumerable p, bool numbersOnly) { - foreach (var e in p) + if (p != null) { - Add(e); + foreach (var e in p) + { + Add(e); + } } + + this.NumbersOnly = numbersOnly; } public void Add(Expression e) @@ -47,93 +63,74 @@ _list.Add(v); } - public double Count() { return _list.Count; } - public double CountA() + public double Count() { - Double cntA = 0; + return this.Count(this.NumbersOnly); + } + + public double Count(bool numbersOnly) + { + if (numbersOnly) + return NumericValues().Count(); + else + return _list.Where(o => !Statistical.IsBlank(o)).Count(); + } + + public IEnumerable NumericValues() + { + var retVal = new List(); foreach (var value in _list) { + Double tmp; var vEnumerable = value as IEnumerable; - if (vEnumerable == null) - cntA += AddCount(value); + if (vEnumerable == null && Double.TryParse(value.ToString(), out tmp)) + yield return tmp; else { foreach (var v in vEnumerable) { - cntA += AddCount(v); + if (Double.TryParse(v.ToString(), out tmp)) + yield return tmp; break; } } } - return cntA; - } - - private static double AddCount(object value) - { - var strVal = value as String; - if (value != null && (strVal == null || !XLHelper.IsNullOrWhiteSpace(strVal))) - return 1; - return 0; - } - - public List Numerics() - { - List retVal = new List(); - foreach (var value in _list) - { - var vEnumerable = value as IEnumerable; - if (vEnumerable == null) - AddNumericValue(value, retVal); - else - { - foreach (var v in vEnumerable) - { - AddNumericValue(v, retVal); - break; - } - } - } - return retVal; - } - - private static void AddNumericValue(object value, List retVal) - { - Double tmp; - if (Double.TryParse(value.ToString(), out tmp)) - { - retVal.Add(tmp); - } } public double Product() { - var nums = Numerics(); - if (nums.Count == 0) return 0; + var nums = NumericValues(); + if (!nums.Any()) return 0; Double retVal = 1; nums.ForEach(n => retVal *= n); return retVal; } - public double Sum() { return Numerics().Sum(); } + + public double Sum() { return NumericValues().Sum(); } + public double Average() { - return Numerics().Count == 0 ? 0 : Numerics().Average(); + if (NumericValues().Any()) + return NumericValues().Average(); + else + throw new ApplicationException("No values"); } public double Min() { - return Numerics().Count == 0 ? 0 : Numerics().Min(); + return NumericValues().Any() ? NumericValues().Min() : 0; } public double Max() { - return Numerics().Count == 0 ? 0 : Numerics().Max(); + return NumericValues().Any() ? NumericValues().Max() : 0; } public double Range() { - var nums = Numerics(); + var nums = NumericValues(); return nums.Max() - nums.Min(); } @@ -144,40 +141,46 @@ public double VarP() { - var nums = Numerics(); + var nums = NumericValues(); var avg = nums.Average(); var sum2 = nums.Sum(d => d * d); - return nums.Count <= 1 ? 0 : sum2 / nums.Count - avg * avg; + return nums.Count() <= 1 ? 0 : sum2 / nums.Count() - avg * avg; } + public double StdP() { - var nums = Numerics(); + var nums = NumericValues(); var avg = nums.Average(); var sum2 = nums.Sum(d => d * d); - return nums.Count <= 1 ? 0 : Math.Sqrt(sum2 / nums.Count - avg * avg); + return nums.Count() <= 1 ? 0 : Math.Sqrt(sum2 / nums.Count() - avg * avg); } + public double Var() { - var nums = Numerics(); + var nums = NumericValues(); var avg = nums.Average(); var sum2 = nums.Sum(d => d * d); - return nums.Count <= 1 ? 0 : (sum2 / nums.Count - avg * avg) * nums.Count / (nums.Count - 1); + return nums.Count() <= 1 ? 0 : (sum2 / nums.Count() - avg * avg) * nums.Count() / (nums.Count() - 1); } + public double Std() { - var values = Numerics(); + var values = NumericValues(); double ret = 0; - if (values.Count > 0) + if (values.Any()) { - //Compute the Average + //Compute the Average double avg = values.Average(); - //Perform the Sum of (value-avg)_2_2 + //Perform the Sum of (value-avg)_2_2 double sum = values.Sum(d => Math.Pow(d - avg, 2)); - //Put it all together + //Put it all together ret = Math.Sqrt((sum) / (values.Count() - 1)); } + else + { + throw new ApplicationException("No values"); + } return ret; - } public IEnumerator GetEnumerator() diff --git a/ClosedXML/Excel/CalcEngine/Functions/Text.cs b/ClosedXML/Excel/CalcEngine/Functions/Text.cs index 8e35edf..b91728c 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/Text.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/Text.cs @@ -256,6 +256,11 @@ private static object _Text(List p) { + var value = p[0].Evaluate(); + + // Input values of type string don't get any formatting applied. + if (value is string) return value; + var number = (double)p[0]; var format = (string)p[1]; if (string.IsNullOrEmpty(format.Trim())) return ""; @@ -343,4 +348,4 @@ return retVal; } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/Excel/Cells/XLCell.cs index 4360176..e901eb3 100644 --- a/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/Excel/Cells/XLCell.cs @@ -139,7 +139,7 @@ if (HasRichText) return _richText.ToString(); - return XLHelper.IsNullOrWhiteSpace(_cellValue) ? FormulaA1 : _cellValue; + return string.Empty == _cellValue ? FormulaA1 : _cellValue; } } @@ -225,6 +225,7 @@ { FormulaA1 = String.Empty; _richText = null; + var style = GetStyleForRead(); if (value is String || value is char) { _cellValue = value.ToString(); @@ -236,13 +237,15 @@ { _cellValue = value.ToString(); _dataType = XLCellValues.TimeSpan; - Style.NumberFormat.NumberFormatId = 46; + if (style.NumberFormat.Format == String.Empty && style.NumberFormat.NumberFormatId == 0) + Style.NumberFormat.NumberFormatId = 46; } else if (value is DateTime) { _dataType = XLCellValues.DateTime; var dtTest = (DateTime)Convert.ChangeType(value, typeof (DateTime)); - Style.NumberFormat.NumberFormatId = dtTest.Date == dtTest ? 14 : 22; + if (style.NumberFormat.Format == String.Empty && style.NumberFormat.NumberFormatId == 0) + Style.NumberFormat.NumberFormatId = dtTest.Date == dtTest ? 14 : 22; _cellValue = dtTest.ToOADate().ToInvariantString(); } @@ -329,7 +332,7 @@ { cValue = GetString(); } - catch + catch { cValue = String.Empty; } @@ -1615,8 +1618,8 @@ val = string.Empty; else if (value is DateTime) val = ((DateTime)value).ToString("o"); - else if (value is double) - val = ((double)value).ToInvariantString(); + else if (value.IsNumber()) + val = Convert.ToDecimal(value).ToInvariantString(); else val = value.ToString(); _richText = null; @@ -1671,7 +1674,6 @@ val = dtTest.ToOADate().ToInvariantString(); } } - } else if (Boolean.TryParse(val, out bTest)) { @@ -1927,7 +1929,7 @@ return columnPart; } - internal void CopyValues(XLCell source) + internal void CopyValuesFrom(XLCell source) { _cellValue = source._cellValue; _dataType = source._dataType; @@ -1960,7 +1962,7 @@ { var source = otherCell as XLCell; // To expose GetFormulaR1C1, etc //var source = castedOtherCell; - CopyValues(source); + CopyValuesFrom(source); SetStyle(source._style ?? source.Worksheet.Workbook.GetStyleById(source._styleCacheId)); diff --git a/ClosedXML/Excel/Columns/XLColumn.cs b/ClosedXML/Excel/Columns/XLColumn.cs index 9ee72db..6a266c6 100644 --- a/ClosedXML/Excel/Columns/XLColumn.cs +++ b/ClosedXML/Excel/Columns/XLColumn.cs @@ -47,8 +47,8 @@ _width = column._width; IsReference = column.IsReference; if (IsReference) - SubscribeToShiftedColumns((range, columnsShifted) => this.WorksheetRangeShiftedColumns(range, columnsShifted)); - _collapsed = column._collapsed; + SubscribeToShiftedColumns((range, columnsShifted) => this.WorksheetRangeShiftedColumns(range, columnsShifted)); + _collapsed = column._collapsed; _isHidden = column._isHidden; _outlineLevel = column._outlineLevel; SetStyle(column.GetStyleId()); @@ -299,6 +299,17 @@ { var fontCache = new Dictionary(); Double colMaxWidth = minWidth; + + List autoFilterRows = new List(); + if (this.Worksheet.AutoFilter != null && this.Worksheet.AutoFilter.Range != null) + autoFilterRows.Add(this.Worksheet.AutoFilter.Range.FirstRow().RowNumber()); + + autoFilterRows.AddRange(Worksheet.Tables.Where(t => + t.AutoFilter != null + && t.AutoFilter.Range != null + && !autoFilterRows.Contains(t.AutoFilter.Range.FirstRow().RowNumber())) + .Select(t => t.AutoFilter.Range.FirstRow().RowNumber())); + foreach (XLCell c in Column(startRow, endRow).CellsUsed()) { if (c.IsMerged()) continue; @@ -442,9 +453,7 @@ else thisWidthMax = c.Style.Font.GetWidth(c.GetFormattedString(), fontCache); - if (c.Worksheet.AutoFilter != null - && c.Worksheet.AutoFilter.Range != null - && c.Worksheet.AutoFilter.Range.Contains(c)) + if (autoFilterRows.Contains(c.Address.RowNumber)) thisWidthMax += 2.7148; // Allow room for arrow icon in autofilter diff --git a/ClosedXML/Excel/Comments/XLComment.cs b/ClosedXML/Excel/Comments/XLComment.cs index 83f5846..87cae88 100644 --- a/ClosedXML/Excel/Comments/XLComment.cs +++ b/ClosedXML/Excel/Comments/XLComment.cs @@ -164,7 +164,7 @@ if (pRow > 1) { pRow--; - double prevHeight = cell.CellAbove().WorksheetRow().Height; + double prevHeight = cell.Worksheet.Row(pRow).Height; if (prevHeight > 7) pRowOffset = prevHeight - 7; } @@ -204,4 +204,4 @@ ShapeId = cell.Worksheet.Workbook.ShapeIdManager.GetNext(); } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFContainsConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFContainsConverter.cs index 3abf4c3..83ccf67 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFContainsConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFContainsConverter.cs @@ -11,7 +11,7 @@ public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) { String val = cf.Values[1].Value; - var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Operator = cf.Operator.ToOpenXml(), Text = val, Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; + var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Operator = ConditionalFormattingOperatorValues.ContainsText, Text = val, Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; var formula = new Formula { Text = "NOT(ISERROR(SEARCH(\"" + val + "\"," + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + ")))" }; diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFEndsWithConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFEndsWithConverter.cs index 1fa1532..1425fc6 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFEndsWithConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFEndsWithConverter.cs @@ -11,7 +11,7 @@ public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) { String val = cf.Values[1].Value; - var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Operator = cf.Operator.ToOpenXml(), Text = val, Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; + var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Operator = ConditionalFormattingOperatorValues.EndsWith, Text = val, Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; var formula = new Formula { Text = "RIGHT(" + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + "," + val.Length.ToString() + ")=\"" + val + "\"" }; diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotContainsConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotContainsConverter.cs index 86dcee8..6d86b12 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotContainsConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotContainsConverter.cs @@ -11,7 +11,7 @@ public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) { String val = cf.Values[1].Value; - var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Operator = cf.Operator.ToOpenXml(), Text = val, Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; + var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Operator = ConditionalFormattingOperatorValues.NotContains, Text = val, Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; var formula = new Formula { Text = "ISERROR(SEARCH(\"" + val + "\"," + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + "))" }; diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFStartsWithConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFStartsWithConverter.cs index 82cfb88..7cee965 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFStartsWithConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFStartsWithConverter.cs @@ -11,7 +11,7 @@ public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) { String val = cf.Values[1].Value; - var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Operator = cf.Operator.ToOpenXml(), Text = val, Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; + var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Operator = ConditionalFormattingOperatorValues.BeginsWith, Text = val, Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; var formula = new Formula { Text = "LEFT(" + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + "," + val.Length.ToString() + ")=\"" + val + "\"" }; diff --git a/ClosedXML/Excel/IXLSheetView.cs b/ClosedXML/Excel/IXLSheetView.cs index eee1ed0..9de885a 100644 --- a/ClosedXML/Excel/IXLSheetView.cs +++ b/ClosedXML/Excel/IXLSheetView.cs @@ -3,27 +3,45 @@ namespace ClosedXML.Excel { public enum XLSheetViewOptions { Normal, PageBreakPreview, PageLayout } + public interface IXLSheetView { /// - /// Gets or sets the row after which the vertical split should take place. - /// - Int32 SplitRow { get; set; } - /// /// Gets or sets the column after which the horizontal split should take place. /// Int32 SplitColumn { get; set; } - //Boolean FreezePanes { get; set; } + /// - /// Freezes the top X rows. + /// Gets or sets the row after which the vertical split should take place. /// - /// The rows to freeze. - void FreezeRows(Int32 rows); + Int32 SplitRow { get; set; } + + XLSheetViewOptions View { get; set; } + /// - /// Freezes the left X columns. + /// Window zoom magnification for current view representing percent values. Horizontal & Vertical scale together. /// - /// The columns to freeze. - void FreezeColumns(Int32 columns); + /// Representing percent values ranging from 10 to 400. + Int32 ZoomScale { get; set; } + + /// + /// Zoom magnification to use when in normal view. Horizontal & Vertical scale together + /// + /// Representing percent values ranging from 10 to 400. + Int32 ZoomScaleNormal { get; set; } + + /// + /// Zoom magnification to use when in page layout view. Horizontal & Vertical scale together. + /// + /// Representing percent values ranging from 10 to 400. + Int32 ZoomScalePageLayoutView { get; set; } + + /// + /// Zoom magnification to use when in page break preview. Horizontal & Vertical scale together. + /// + /// Representing percent values ranging from 10 to 400. + Int32 ZoomScaleSheetLayoutView { get; set; } + /// /// Freezes the specified rows and columns. /// @@ -31,7 +49,18 @@ /// The columns to freeze. void Freeze(Int32 rows, Int32 columns); - XLSheetViewOptions View { get; set; } + /// + /// Freezes the left X columns. + /// + /// The columns to freeze. + void FreezeColumns(Int32 columns); + + //Boolean FreezePanes { get; set; } + /// + /// Freezes the top X rows. + /// + /// The rows to freeze. + void FreezeRows(Int32 rows); IXLSheetView SetView(XLSheetViewOptions value); } diff --git a/ClosedXML/Excel/PageSetup/XLHFItem.cs b/ClosedXML/Excel/PageSetup/XLHFItem.cs index 1f03c36..4fd398e 100644 --- a/ClosedXML/Excel/PageSetup/XLHFItem.cs +++ b/ClosedXML/Excel/PageSetup/XLHFItem.cs @@ -40,7 +40,7 @@ public IXLRichString AddText(String text, XLHFOccurrence occurrence) { - XLRichString richText = new XLRichString(text, XLWorkbook.DefaultStyle.Font, this); + XLRichString richText = new XLRichString(text, this.HeaderFooter.Worksheet.Style.Font, this); var hfText = new XLHFText(richText, this); if (occurrence == XLHFOccurrence.AllPages) diff --git a/ClosedXML/Excel/PageSetup/XLPageSetup.cs b/ClosedXML/Excel/PageSetup/XLPageSetup.cs index 9aca46d..8fdc197 100644 --- a/ClosedXML/Excel/PageSetup/XLPageSetup.cs +++ b/ClosedXML/Excel/PageSetup/XLPageSetup.cs @@ -192,11 +192,13 @@ { if (!RowBreaks.Contains(row)) RowBreaks.Add(row); + RowBreaks.Sort(); } public void AddVerticalPageBreak(Int32 column) { if (!ColumnBreaks.Contains(column)) ColumnBreaks.Add(column); + ColumnBreaks.Sort(); } //public void SetPageBreak(IXLRange range, XLPageBreakLocations breakLocation) diff --git a/ClosedXML/Excel/PivotTables/IXLPivotField.cs b/ClosedXML/Excel/PivotTables/IXLPivotField.cs index 8e69e8b..011e222 100644 --- a/ClosedXML/Excel/PivotTables/IXLPivotField.cs +++ b/ClosedXML/Excel/PivotTables/IXLPivotField.cs @@ -36,6 +36,7 @@ Boolean InsertBlankLines { get; set; } Boolean ShowBlankItems { get; set; } Boolean InsertPageBreaks { get; set; } + Boolean Collapsed { get; set; } IXLPivotField SetCustomName(String value); @@ -48,6 +49,7 @@ IXLPivotField SetInsertBlankLines(); IXLPivotField SetInsertBlankLines(Boolean value); IXLPivotField SetShowBlankItems(); IXLPivotField SetShowBlankItems(Boolean value); IXLPivotField SetInsertPageBreaks(); IXLPivotField SetInsertPageBreaks(Boolean value); + IXLPivotField SetCollapsed(); IXLPivotField SetCollapsed(Boolean value); List SharedStrings { get; set; } } diff --git a/ClosedXML/Excel/PivotTables/XLPivotField.cs b/ClosedXML/Excel/PivotTables/XLPivotField.cs index 84291fd..909db34 100644 --- a/ClosedXML/Excel/PivotTables/XLPivotField.cs +++ b/ClosedXML/Excel/PivotTables/XLPivotField.cs @@ -1,12 +1,9 @@ using System; using System.Collections.Generic; -using System.Linq; -using System.Text; namespace ClosedXML.Excel { - - public class XLPivotField: IXLPivotField + public class XLPivotField : IXLPivotField { public XLPivotField(string sourceName) { @@ -15,18 +12,60 @@ Subtotals = new List(); } - public String SourceName { get; private set; } - public String CustomName { get; set; } public IXLPivotField SetCustomName(String value) { CustomName = value; return this; } + public String SourceName { get; private set; } + public String CustomName { get; set; } - public List Subtotals { get; private set; } public IXLPivotField AddSubtotal(XLSubtotalFunction value) { Subtotals.Add(value); return this; } - public Boolean IncludeNewItemsInFilter { get; set; } public IXLPivotField SetIncludeNewItemsInFilter() { IncludeNewItemsInFilter = true; return this; } public IXLPivotField SetIncludeNewItemsInFilter(Boolean value) { IncludeNewItemsInFilter = value; return this; } + public IXLPivotField SetCustomName(String value) { CustomName = value; return this; } - public XLPivotLayout Layout { get; set; } public IXLPivotField SetLayout(XLPivotLayout value) { Layout = value; return this; } - public Boolean SubtotalsAtTop { get; set; } public IXLPivotField SetSubtotalsAtTop() { SubtotalsAtTop = true; return this; } public IXLPivotField SetSubtotalsAtTop(Boolean value) { SubtotalsAtTop = value; return this; } - public Boolean RepeatItemLabels { get; set; } public IXLPivotField SetRepeatItemLabels() { RepeatItemLabels = true; return this; } public IXLPivotField SetRepeatItemLabels(Boolean value) { RepeatItemLabels = value; return this; } - public Boolean InsertBlankLines { get; set; } public IXLPivotField SetInsertBlankLines() { InsertBlankLines = true; return this; } public IXLPivotField SetInsertBlankLines(Boolean value) { InsertBlankLines = value; return this; } - public Boolean ShowBlankItems { get; set; } public IXLPivotField SetShowBlankItems() { ShowBlankItems = true; return this; } public IXLPivotField SetShowBlankItems(Boolean value) { ShowBlankItems = value; return this; } - public Boolean InsertPageBreaks { get; set; } public IXLPivotField SetInsertPageBreaks() { InsertPageBreaks = true; return this; } public IXLPivotField SetInsertPageBreaks(Boolean value) { InsertPageBreaks = value; return this; } + public List Subtotals { get; private set; } + + public IXLPivotField AddSubtotal(XLSubtotalFunction value) { Subtotals.Add(value); return this; } + + public Boolean IncludeNewItemsInFilter { get; set; } + + public IXLPivotField SetIncludeNewItemsInFilter() { IncludeNewItemsInFilter = true; return this; } + + public IXLPivotField SetIncludeNewItemsInFilter(Boolean value) { IncludeNewItemsInFilter = value; return this; } + + public XLPivotLayout Layout { get; set; } + + public IXLPivotField SetLayout(XLPivotLayout value) { Layout = value; return this; } + + public Boolean SubtotalsAtTop { get; set; } + + public IXLPivotField SetSubtotalsAtTop() { SubtotalsAtTop = true; return this; } + + public IXLPivotField SetSubtotalsAtTop(Boolean value) { SubtotalsAtTop = value; return this; } + + public Boolean RepeatItemLabels { get; set; } + + public IXLPivotField SetRepeatItemLabels() { RepeatItemLabels = true; return this; } + + public IXLPivotField SetRepeatItemLabels(Boolean value) { RepeatItemLabels = value; return this; } + + public Boolean InsertBlankLines { get; set; } + + public IXLPivotField SetInsertBlankLines() { InsertBlankLines = true; return this; } + + public IXLPivotField SetInsertBlankLines(Boolean value) { InsertBlankLines = value; return this; } + + public Boolean ShowBlankItems { get; set; } + + public IXLPivotField SetShowBlankItems() { ShowBlankItems = true; return this; } + + public IXLPivotField SetShowBlankItems(Boolean value) { ShowBlankItems = value; return this; } + + public Boolean InsertPageBreaks { get; set; } + + public IXLPivotField SetInsertPageBreaks() { InsertPageBreaks = true; return this; } + + public IXLPivotField SetInsertPageBreaks(Boolean value) { InsertPageBreaks = value; return this; } + + public Boolean Collapsed { get; set; } + + public IXLPivotField SetCollapsed() { Collapsed = true; return this; } + + public IXLPivotField SetCollapsed(Boolean value) { Collapsed = value; return this; } public List SharedStrings { get; set; } } diff --git a/ClosedXML/Excel/PivotTables/XLPivotTable.cs b/ClosedXML/Excel/PivotTables/XLPivotTable.cs index b6020cd..c762261 100644 --- a/ClosedXML/Excel/PivotTables/XLPivotTable.cs +++ b/ClosedXML/Excel/PivotTables/XLPivotTable.cs @@ -91,6 +91,10 @@ public IXLPivotTable SetInsertBlankLines() { InsertBlankLines = true; return this; } public IXLPivotTable SetInsertBlankLines(Boolean value) { InsertBlankLines = value; return this; } + internal String RelId { get; set; } + internal String CacheDefinitionRelId { get; set; } + internal String WorkbookCacheRelId { get; set; } + private void SetExcelDefaults() { EmptyCellReplacement = String.Empty; diff --git a/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/Excel/Ranges/XLRangeBase.cs index 60d12d4..8251bcd 100644 --- a/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -58,25 +58,25 @@ #endregion - private XLCallbackAction _shiftedRowsAction; + private XLCallbackAction _shiftedRowsAction; protected void SubscribeToShiftedRows(Action action) { if (Worksheet == null || !Worksheet.EventTrackingEnabled) return; - _shiftedRowsAction = new XLCallbackAction(action); + _shiftedRowsAction = new XLCallbackAction(action); - RangeAddress.Worksheet.RangeShiftedRows.Add(_shiftedRowsAction); + RangeAddress.Worksheet.RangeShiftedRows.Add(_shiftedRowsAction); } private XLCallbackAction _shiftedColumnsAction; - protected void SubscribeToShiftedColumns(Action action) + protected void SubscribeToShiftedColumns(Action action) { if (Worksheet == null || !Worksheet.EventTrackingEnabled) return; _shiftedColumnsAction = new XLCallbackAction(action); - RangeAddress.Worksheet.RangeShiftedColumns.Add(_shiftedColumnsAction); + RangeAddress.Worksheet.RangeShiftedColumns.Add(_shiftedColumnsAction); } #region Public properties @@ -178,7 +178,7 @@ dvEmpty.ForEach(dv => Worksheet.DataValidations.Delete(dv)); - var newRanges = new XLRanges {AsRange()}; + var newRanges = new XLRanges { AsRange() }; var dataValidation = new XLDataValidation(newRanges); if (dataValidationToCopy != null) dataValidation.CopyFrom(dataValidationToCopy); @@ -262,7 +262,7 @@ { get { - var retVal = new XLRanges {AsRange()}; + var retVal = new XLRanges { AsRange() }; return retVal; } } @@ -394,7 +394,8 @@ return this; } - public void DeleteComments() { + public void DeleteComments() + { Cells().DeleteComments(); } @@ -427,7 +428,7 @@ public bool Intersects(string rangeAddress) { - using (var range = Worksheet.Range(rangeAddress)) + using (var range = Worksheet.Range(rangeAddress)) return Intersects(range); } @@ -948,7 +949,7 @@ public IXLCells CellsUsed(bool includeFormats) { - var cells = new XLCells(true, includeFormats) {RangeAddress}; + var cells = new XLCells(true, includeFormats) { RangeAddress }; return cells; } @@ -991,14 +992,14 @@ } public IXLRangeColumns InsertColumnsAfter(Boolean onlyUsedCells, Int32 numberOfColumns, Boolean formatFromLeft = true) - { - return InsertColumnsAfterInternal(onlyUsedCells, numberOfColumns, formatFromLeft); - } + { + return InsertColumnsAfterInternal(onlyUsedCells, numberOfColumns, formatFromLeft); + } - public void InsertColumnsAfterVoid(Boolean onlyUsedCells, Int32 numberOfColumns, Boolean formatFromLeft = true) - { - InsertColumnsAfterInternal(onlyUsedCells, numberOfColumns, formatFromLeft, nullReturn: true); - } + public void InsertColumnsAfterVoid(Boolean onlyUsedCells, Int32 numberOfColumns, Boolean formatFromLeft = true) + { + InsertColumnsAfterInternal(onlyUsedCells, numberOfColumns, formatFromLeft, nullReturn: true); + } private IXLRangeColumns InsertColumnsAfterInternal(Boolean onlyUsedCells, Int32 numberOfColumns, Boolean formatFromLeft = true, Boolean nullReturn = false) { @@ -1045,15 +1046,15 @@ return retVal; } - public IXLRangeColumns InsertColumnsBefore(Boolean onlyUsedCells, Int32 numberOfColumns, Boolean formatFromLeft = true) - { - return InsertColumnsBeforeInternal(onlyUsedCells, numberOfColumns, formatFromLeft); - } + public IXLRangeColumns InsertColumnsBefore(Boolean onlyUsedCells, Int32 numberOfColumns, Boolean formatFromLeft = true) + { + return InsertColumnsBeforeInternal(onlyUsedCells, numberOfColumns, formatFromLeft); + } - public void InsertColumnsBeforeVoid(Boolean onlyUsedCells, Int32 numberOfColumns, Boolean formatFromLeft = true) - { - InsertColumnsBeforeInternal(onlyUsedCells, numberOfColumns, formatFromLeft, nullReturn: true); - } + public void InsertColumnsBeforeVoid(Boolean onlyUsedCells, Int32 numberOfColumns, Boolean formatFromLeft = true) + { + InsertColumnsBeforeInternal(onlyUsedCells, numberOfColumns, formatFromLeft, nullReturn: true); + } private IXLRangeColumns InsertColumnsBeforeInternal(Boolean onlyUsedCells, Int32 numberOfColumns, Boolean formatFromLeft = true, Boolean nullReturn = false) { @@ -1088,7 +1089,7 @@ Worksheet.Cell(oldKey); var newCell = new XLCell(Worksheet, newKey, oldCell.GetStyleId()); - newCell.CopyValues(oldCell); + newCell.CopyValuesFrom(oldCell); newCell.FormulaA1 = oldCell.FormulaA1; cellsToInsert.Add(newKey, newCell); cellsToDelete.Add(oldKey); @@ -1106,12 +1107,12 @@ int newColumn = c.Address.ColumnNumber + numberOfColumns; var newKey = new XLAddress(Worksheet, c.Address.RowNumber, newColumn, false, false); var newCell = new XLCell(Worksheet, newKey, c.GetStyleId()); - newCell.CopyValues(c); + newCell.CopyValuesFrom(c); if (c.HasDataValidation) { cellsDataValidations.Add(newCell.Address, new DataValidationToCopy - {DataValidation = c.DataValidation, SourceAddress = c.Address}); + { DataValidation = c.DataValidation, SourceAddress = c.Address }); c.DataValidation.Clear(); } newCell.FormulaA1 = c.FormulaA1; @@ -1135,8 +1136,8 @@ //cellsDataValidations.ForEach(kp => Worksheet.Cell(kp.Key).CopyDataValidation(Worksheet.Cell(kp.Value.SourceAddress), kp.Value.DataValidation)); Int32 firstRowReturn = RangeAddress.FirstAddress.RowNumber; - Int32 lastRowReturn = RangeAddress.LastAddress.RowNumber ; - Int32 firstColumnReturn = RangeAddress.FirstAddress.ColumnNumber ; + Int32 lastRowReturn = RangeAddress.LastAddress.RowNumber; + Int32 firstColumnReturn = RangeAddress.FirstAddress.ColumnNumber; Int32 lastColumnReturn = RangeAddress.FirstAddress.ColumnNumber + numberOfColumns - 1; Worksheet.BreakConditionalFormatsIntoCells(cellsToDelete.Except(cellsToInsert.Keys).ToList()); @@ -1184,8 +1185,8 @@ } } - if(nullReturn) - return null; + if (nullReturn) + return null; return rangeToReturn.Columns(); } @@ -1216,18 +1217,18 @@ return retVal; } - public IXLRangeRows InsertRowsBelow(Boolean onlyUsedCells, Int32 numberOfRows, Boolean formatFromAbove = true) - { - return InsertRowsBelowInternal(onlyUsedCells, numberOfRows, formatFromAbove, nullReturn: false); - } + public IXLRangeRows InsertRowsBelow(Boolean onlyUsedCells, Int32 numberOfRows, Boolean formatFromAbove = true) + { + return InsertRowsBelowInternal(onlyUsedCells, numberOfRows, formatFromAbove, nullReturn: false); + } - public void InsertRowsBelowVoid(Boolean onlyUsedCells, Int32 numberOfRows, Boolean formatFromAbove = true) - { - InsertRowsBelowInternal(onlyUsedCells, numberOfRows, formatFromAbove, nullReturn: true); - } + public void InsertRowsBelowVoid(Boolean onlyUsedCells, Int32 numberOfRows, Boolean formatFromAbove = true) + { + InsertRowsBelowInternal(onlyUsedCells, numberOfRows, formatFromAbove, nullReturn: true); + } - private IXLRangeRows InsertRowsBelowInternal(Boolean onlyUsedCells, Int32 numberOfRows, Boolean formatFromAbove, Boolean nullReturn) - { + private IXLRangeRows InsertRowsBelowInternal(Boolean onlyUsedCells, Int32 numberOfRows, Boolean formatFromAbove, Boolean nullReturn) + { int rowCount = RowCount(); int firstRow = RangeAddress.FirstAddress.RowNumber + rowCount; if (firstRow > XLHelper.MaxRowNumber) @@ -1276,22 +1277,22 @@ public XLAddress SourceAddress; public XLDataValidation DataValidation; } - public void InsertRowsAboveVoid(Boolean onlyUsedCells, Int32 numberOfRows, Boolean formatFromAbove = true) - { - InsertRowsAboveInternal(onlyUsedCells, numberOfRows, formatFromAbove, nullReturn: true); - } - public IXLRangeRows InsertRowsAbove(Boolean onlyUsedCells, Int32 numberOfRows, Boolean formatFromAbove = true) - { - return InsertRowsAboveInternal(onlyUsedCells, numberOfRows, formatFromAbove, nullReturn: false); - } + public void InsertRowsAboveVoid(Boolean onlyUsedCells, Int32 numberOfRows, Boolean formatFromAbove = true) + { + InsertRowsAboveInternal(onlyUsedCells, numberOfRows, formatFromAbove, nullReturn: true); + } + public IXLRangeRows InsertRowsAbove(Boolean onlyUsedCells, Int32 numberOfRows, Boolean formatFromAbove = true) + { + return InsertRowsAboveInternal(onlyUsedCells, numberOfRows, formatFromAbove, nullReturn: false); + } - private IXLRangeRows InsertRowsAboveInternal(Boolean onlyUsedCells, Int32 numberOfRows, Boolean formatFromAbove, Boolean nullReturn) - { + private IXLRangeRows InsertRowsAboveInternal(Boolean onlyUsedCells, Int32 numberOfRows, Boolean formatFromAbove, Boolean nullReturn) + { using (var asRange = AsRange()) foreach (XLWorksheet ws in Worksheet.Workbook.WorksheetsInternal) { foreach (XLCell cell in ws.Internals.CellsCollection.GetCells(c => !XLHelper.IsNullOrWhiteSpace(c.FormulaA1))) - cell.ShiftFormulaRows(asRange, numberOfRows); + cell.ShiftFormulaRows(asRange, numberOfRows); } var cellsToInsert = new Dictionary(); @@ -1319,7 +1320,7 @@ if (oldCell != null) { var newCell = new XLCell(Worksheet, newKey, oldCell.GetStyleId()); - newCell.CopyValues(oldCell); + newCell.CopyValuesFrom(oldCell); newCell.FormulaA1 = oldCell.FormulaA1; cellsToInsert.Add(newKey, newCell); cellsToDelete.Add(oldKey); @@ -1339,12 +1340,12 @@ int newRow = c.Address.RowNumber + numberOfRows; var newKey = new XLAddress(Worksheet, newRow, c.Address.ColumnNumber, false, false); var newCell = new XLCell(Worksheet, newKey, c.GetStyleId()); - newCell.CopyValues(c); + newCell.CopyValuesFrom(c); if (c.HasDataValidation) { cellsDataValidations.Add(newCell.Address, new DataValidationToCopy - {DataValidation = c.DataValidation, SourceAddress = c.Address}); + { DataValidation = c.DataValidation, SourceAddress = c.Address }); c.DataValidation.Clear(); } newCell.FormulaA1 = c.FormulaA1; @@ -1354,19 +1355,19 @@ } } - cellsDataValidations.ForEach(kp => - { - XLCell targetCell; - if(!cellsToInsert.TryGetValue(kp.Key, out targetCell)) - targetCell = Worksheet.Cell(kp.Key); + cellsDataValidations + .ForEach(kp => + { + XLCell targetCell; + if (!cellsToInsert.TryGetValue(kp.Key, out targetCell)) + targetCell = Worksheet.Cell(kp.Key); - targetCell.CopyDataValidation( - Worksheet.Cell(kp.Value.SourceAddress), kp.Value.DataValidation); - }); + targetCell.CopyDataValidation( + Worksheet.Cell(kp.Value.SourceAddress), kp.Value.DataValidation); + }); cellsToDelete.ForEach(c => Worksheet.Internals.CellsCollection.Remove(c.RowNumber, c.ColumnNumber)); - cellsToInsert.ForEach( - c => Worksheet.Internals.CellsCollection.Add(c.Key.RowNumber, c.Key.ColumnNumber, c.Value)); + cellsToInsert.ForEach(c => Worksheet.Internals.CellsCollection.Add(c.Key.RowNumber, c.Key.ColumnNumber, c.Value)); Int32 firstRowReturn = RangeAddress.FirstAddress.RowNumber; @@ -1418,9 +1419,9 @@ } } - // Skip calling .Rows() for performance reasons if required. - if(nullReturn) - return null; + // Skip calling .Rows() for performance reasons if required. + if (nullReturn) + return null; return rangeToReturn.Rows(); } @@ -1498,7 +1499,7 @@ c.Address.ColumnNumber - columnModifier, false, false); var newCell = new XLCell(Worksheet, newKey, c.GetStyleId()); - newCell.CopyValues(c); + newCell.CopyValuesFrom(c); newCell.FormulaA1 = c.FormulaA1; cellsToDelete.Add(c.Address); @@ -1709,7 +1710,7 @@ public IXLAutoFilter SetAutoFilter() { using (var asRange = AsRange()) - return Worksheet.AutoFilter.Set(asRange); + return Worksheet.AutoFilter.Set(asRange); } #region Sort @@ -1976,17 +1977,17 @@ public void Dispose() { - if(_shiftedRowsAction != null) + if (_shiftedRowsAction != null) { RangeAddress.Worksheet.RangeShiftedRows.Remove(_shiftedRowsAction); - _shiftedRowsAction = null; + _shiftedRowsAction = null; } - if(_shiftedColumnsAction != null) - { - RangeAddress.Worksheet.RangeShiftedColumns.Remove(_shiftedColumnsAction); - _shiftedColumnsAction = null; - } + if (_shiftedColumnsAction != null) + { + RangeAddress.Worksheet.RangeShiftedColumns.Remove(_shiftedColumnsAction); + _shiftedColumnsAction = null; + } } public IXLDataValidation SetDataValidation() diff --git a/ClosedXML/Excel/Tables/XLTable.cs b/ClosedXML/Excel/Tables/XLTable.cs index 5e8f3a6..4e193cf 100644 --- a/ClosedXML/Excel/Tables/XLTable.cs +++ b/ClosedXML/Excel/Tables/XLTable.cs @@ -107,7 +107,7 @@ } - public String RelId { get; set; } + internal String RelId { get; set; } public IXLTableRange DataRange { diff --git a/ClosedXML/Excel/XLConstants.cs b/ClosedXML/Excel/XLConstants.cs index d1cbe58..bf4a2a8 100644 --- a/ClosedXML/Excel/XLConstants.cs +++ b/ClosedXML/Excel/XLConstants.cs @@ -4,6 +4,7 @@ public static class XLConstants { public const string PivotTableValuesSentinalLabel = "{{Values}}"; + public const int NumberOfBuiltInStyles = 163; internal static class Comment { diff --git a/ClosedXML/Excel/XLSheetView.cs b/ClosedXML/Excel/XLSheetView.cs index 2d7b22d..169bb67 100644 --- a/ClosedXML/Excel/XLSheetView.cs +++ b/ClosedXML/Excel/XLSheetView.cs @@ -2,31 +2,62 @@ namespace ClosedXML.Excel { - internal class XLSheetView: IXLSheetView + internal class XLSheetView : IXLSheetView { - public XLSheetView() { + public XLSheetView() + { View = XLSheetViewOptions.Normal; + + ZoomScale = 100; + ZoomScaleNormal = 100; + ZoomScalePageLayoutView = 100; + ZoomScaleSheetLayoutView = 100; } - public XLSheetView(IXLSheetView sheetView):this() + + public XLSheetView(IXLSheetView sheetView) + : this() { this.SplitRow = sheetView.SplitRow; this.SplitColumn = sheetView.SplitColumn; this.FreezePanes = ((XLSheetView)sheetView).FreezePanes; } - public Int32 SplitRow { get; set; } - public Int32 SplitColumn { get; set; } public Boolean FreezePanes { get; set; } - public void FreezeRows(Int32 rows) + public Int32 SplitColumn { get; set; } + public Int32 SplitRow { get; set; } + public XLSheetViewOptions View { get; set; } + + public int ZoomScale { - SplitRow = rows; - FreezePanes = true; + get { return _zoomScale; } + set + { + _zoomScale = value; + switch (View) + { + case XLSheetViewOptions.Normal: + ZoomScaleNormal = value; + break; + + case XLSheetViewOptions.PageBreakPreview: + ZoomScalePageLayoutView = value; + break; + + case XLSheetViewOptions.PageLayout: + ZoomScaleSheetLayoutView = value; + break; + } + } } - public void FreezeColumns(Int32 columns) - { - SplitColumn = columns; - FreezePanes = true; - } + + public int ZoomScaleNormal { get; set; } + + public int ZoomScalePageLayoutView { get; set; } + + public int ZoomScaleSheetLayoutView { get; set; } + + private int _zoomScale { get; set; } + public void Freeze(Int32 rows, Int32 columns) { SplitRow = rows; @@ -34,8 +65,17 @@ FreezePanes = true; } + public void FreezeColumns(Int32 columns) + { + SplitColumn = columns; + FreezePanes = true; + } - public XLSheetViewOptions View { get; set; } + public void FreezeRows(Int32 rows) + { + SplitRow = rows; + FreezePanes = true; + } public IXLSheetView SetView(XLSheetViewOptions value) { diff --git a/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/Excel/XLWorkbook.cs index 3cfe9ef..8eaf483 100644 --- a/ClosedXML/Excel/XLWorkbook.cs +++ b/ClosedXML/Excel/XLWorkbook.cs @@ -406,6 +406,23 @@ return false; } + public IXLRange RangeFromFullAddress(String rangeAddress, out IXLWorksheet ws) + { + ws = null; + if (!rangeAddress.Contains('!')) return null; + + var split = rangeAddress.Split('!'); + var first = split[0]; + var wsName = first.StartsWith("'") ? first.Substring(1, first.Length - 2) : first; + var localRange = split[1]; + if (TryGetWorksheet(wsName, out ws)) + { + return ws.Range(localRange); + } + return null; + } + + /// /// Saves the current workbook. /// @@ -739,17 +756,23 @@ return Ranges(namedCells).Cells(); } - public IXLRange Range(String namedRange) + public IXLRange Range(String range) { - var range = NamedRange(namedRange); - if (range == null) return null; - return range.Ranges.FirstOrDefault(); + var namedRange = NamedRange(range); + if (namedRange != null) + return namedRange.Ranges.FirstOrDefault(); + else + { + IXLWorksheet ws; + var r = RangeFromFullAddress(range, out ws); + return r; + } } - public IXLRanges Ranges(String namedRanges) + public IXLRanges Ranges(String ranges) { var retVal = new XLRanges(); - var rangePairs = namedRanges.Split(','); + var rangePairs = ranges.Split(','); foreach (var range in rangePairs.Select(r => Range(r.Trim())).Where(range => range != null)) { retVal.Add(range); diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs index b551198..d62bb95 100644 --- a/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/Excel/XLWorkbook_Load.cs @@ -176,11 +176,20 @@ ws.Visibility = dSheet.State.Value.ToClosedXml(); var styleList = new Dictionary();// {{0, ws.Style}}; + PageSetupProperties pageSetupProperties = null; using (var reader = OpenXmlReader.Create(wsPart)) { + Type[] ignoredElements = new Type[] + { + typeof(CustomSheetViews) // Custom sheet views contain its own auto filter data, and more, which should be ignored for now + }; + while (reader.Read()) { + while (ignoredElements.Contains(reader.ElementType)) + reader.ReadNextSibling(); + if (reader.ElementType == typeof(SheetFormatProperties)) { var sheetFormatProperties = (SheetFormatProperties)reader.LoadCurrentElement(); @@ -233,11 +242,11 @@ else if (reader.ElementType == typeof(PageMargins)) LoadPageMargins((PageMargins)reader.LoadCurrentElement(), ws); else if (reader.ElementType == typeof(PageSetup)) - LoadPageSetup((PageSetup)reader.LoadCurrentElement(), ws); + LoadPageSetup((PageSetup)reader.LoadCurrentElement(), ws, pageSetupProperties); else if (reader.ElementType == typeof(HeaderFooter)) LoadHeaderFooter((HeaderFooter)reader.LoadCurrentElement(), ws); else if (reader.ElementType == typeof(SheetProperties)) - LoadSheetProperties((SheetProperties)reader.LoadCurrentElement(), ws); + LoadSheetProperties((SheetProperties)reader.LoadCurrentElement(), ws, out pageSetupProperties); else if (reader.ElementType == typeof(RowBreaks)) LoadRowBreaks((RowBreaks)reader.LoadCurrentElement(), ws); else if (reader.ElementType == typeof(ColumnBreaks)) @@ -400,6 +409,209 @@ } } LoadDefinedNames(workbook); + + #region Pivot tables + + // Delay loading of pivot tables until all sheets have been loaded + foreach (Sheet dSheet in sheets.OfType()) + { + var wsPart = dSpreadsheet.WorkbookPart.GetPartById(dSheet.Id) as WorksheetPart; + + if (wsPart != null) + { + var ws = (XLWorksheet)WorksheetsInternal.Worksheet(dSheet.Name); + + foreach (var pivotTablePart in wsPart.PivotTableParts) + { + var pivotTableCacheDefinitionPart = pivotTablePart.PivotTableCacheDefinitionPart; + var pivotTableDefinition = pivotTablePart.PivotTableDefinition; + + var target = ws.FirstCell(); + if (pivotTableDefinition.Location != null && pivotTableDefinition.Location.Reference != null && pivotTableDefinition.Location.Reference.HasValue) + { + target = ws.Range(pivotTableDefinition.Location.Reference.Value).FirstCell(); + } + + IXLRange source = null; + if (pivotTableCacheDefinitionPart.PivotCacheDefinition != null + && pivotTableCacheDefinitionPart.PivotCacheDefinition.CacheSource != null + && pivotTableCacheDefinitionPart.PivotCacheDefinition.CacheSource.WorksheetSource != null) + { + // TODO: Implement other sources besides worksheetSource (e.g. Table source?) + // But for now assume names and references point directly to a range + var wss = pivotTableCacheDefinitionPart.PivotCacheDefinition.CacheSource.WorksheetSource; + string rangeAddress = string.Empty; + if (wss.Name != null) + rangeAddress = wss.Name.Value; + else + { + var sourceSheet = wss.Sheet == null ? ws : this.Worksheet(wss.Sheet.Value); + rangeAddress = sourceSheet.Range(wss.Reference.Value).RangeAddress.ToStringRelative(true); + } + + source = this.Range(rangeAddress); + if (source == null) + continue; + } + + if (target != null && source != null) + { + var pt = ws.PivotTables.AddNew(pivotTableDefinition.Name, target, source) as XLPivotTable; + pt.RelId = wsPart.GetIdOfPart(pivotTablePart); + pt.CacheDefinitionRelId = pivotTablePart.GetIdOfPart(pivotTableCacheDefinitionPart); + pt.WorkbookCacheRelId = dSpreadsheet.WorkbookPart.GetIdOfPart(pivotTableCacheDefinitionPart); + + if (pivotTableDefinition.MergeItem != null) pt.MergeAndCenterWithLabels = pivotTableDefinition.MergeItem.Value; + if (pivotTableDefinition.Indent != null) pt.RowLabelIndent = (int)pivotTableDefinition.Indent.Value; + if (pivotTableDefinition.PageOverThenDown != null) pt.FilterAreaOrder = pivotTableDefinition.PageOverThenDown.Value ? XLFilterAreaOrder.OverThenDown : XLFilterAreaOrder.DownThenOver; + if (pivotTableDefinition.PageWrap != null) pt.FilterFieldsPageWrap = (int)pivotTableDefinition.PageWrap.Value; + if (pivotTableDefinition.UseAutoFormatting != null) pt.AutofitColumns = pivotTableDefinition.UseAutoFormatting.Value; + if (pivotTableDefinition.PreserveFormatting != null) pt.PreserveCellFormatting = pivotTableDefinition.PreserveFormatting.Value; + if (pivotTableDefinition.RowGrandTotals != null) pt.ShowGrandTotalsRows = pivotTableDefinition.RowGrandTotals.Value; + if (pivotTableDefinition.ColumnGrandTotals != null) pt.ShowGrandTotalsColumns = pivotTableDefinition.ColumnGrandTotals.Value; + if (pivotTableDefinition.SubtotalHiddenItems != null) pt.FilteredItemsInSubtotals = pivotTableDefinition.SubtotalHiddenItems.Value; + if (pivotTableDefinition.MultipleFieldFilters != null) pt.AllowMultipleFilters = pivotTableDefinition.MultipleFieldFilters.Value; + if (pivotTableDefinition.CustomListSort != null) pt.UseCustomListsForSorting = pivotTableDefinition.CustomListSort.Value; + if (pivotTableDefinition.ShowDrill != null) pt.ShowExpandCollapseButtons = pivotTableDefinition.ShowDrill.Value; + if (pivotTableDefinition.ShowDataTips != null) pt.ShowContextualTooltips = pivotTableDefinition.ShowDataTips.Value; + if (pivotTableDefinition.ShowMemberPropertyTips != null) pt.ShowPropertiesInTooltips = pivotTableDefinition.ShowMemberPropertyTips.Value; + if (pivotTableDefinition.ShowHeaders != null) pt.DisplayCaptionsAndDropdowns = pivotTableDefinition.ShowHeaders.Value; + if (pivotTableDefinition.GridDropZones != null) pt.ClassicPivotTableLayout = pivotTableDefinition.GridDropZones.Value; + if (pivotTableDefinition.ShowEmptyRow != null) pt.ShowEmptyItemsOnRows = pivotTableDefinition.ShowEmptyRow.Value; + if (pivotTableDefinition.ShowEmptyColumn != null) pt.ShowEmptyItemsOnColumns = pivotTableDefinition.ShowEmptyColumn.Value; + if (pivotTableDefinition.ShowItems != null) pt.DisplayItemLabels = pivotTableDefinition.ShowItems.Value; + if (pivotTableDefinition.FieldListSortAscending != null) pt.SortFieldsAtoZ = pivotTableDefinition.FieldListSortAscending.Value; + if (pivotTableDefinition.PrintDrill != null) pt.PrintExpandCollapsedButtons = pivotTableDefinition.PrintDrill.Value; + 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 (pivotTableDefinition.ShowMissing != null && pivotTableDefinition.MissingCaption != null) + pt.EmptyCellReplacement = pivotTableDefinition.MissingCaption.Value; + + if (pivotTableDefinition.ShowError != null && pivotTableDefinition.ErrorCaption != null) + pt.ErrorValueReplacement = pivotTableDefinition.ErrorCaption.Value; + + // Row labels + if (pivotTableDefinition.RowFields != null) + { + foreach (var rf in pivotTableDefinition.RowFields.Cast()) + { + if (rf.Index < pivotTableDefinition.PivotFields.Count) + { + IXLPivotField pivotField = null; + if (rf.Index.Value == -2) + pivotField = pt.RowLabels.Add(XLConstants.PivotTableValuesSentinalLabel); + else + { + var pf = pivotTableDefinition.PivotFields.ElementAt(rf.Index.Value) as PivotField; + if (pf == null) + continue; + + var cacheField = pivotTableCacheDefinitionPart.PivotCacheDefinition.CacheFields.ElementAt(rf.Index.Value) as CacheField; + if (pf.Name != null) + pivotField = pt.RowLabels.Add(pf.Name.Value); + else if (cacheField.Name != null) + pivotField = pt.RowLabels.Add(cacheField.Name.Value); + else + continue; + + if (pivotField != null) + { + var items = pf.Items.OfType().Where(i => i.Index != null && i.Index.HasValue); + if (!items.Any(i => i.HideDetails == null || BooleanValue.ToBoolean(i.HideDetails))) + pivotField.SetCollapsed(); + } + } + } + } + } + + // Column labels + if (pivotTableDefinition.ColumnFields != null) + { + foreach (var cf in pivotTableDefinition.ColumnFields.Cast()) + { + IXLPivotField pivotField = null; + if (cf.Index.Value == -2) + pivotField = pt.ColumnLabels.Add(XLConstants.PivotTableValuesSentinalLabel); + else if (cf.Index < pivotTableDefinition.PivotFields.Count) + { + var pf = pivotTableDefinition.PivotFields.ElementAt(cf.Index.Value) as PivotField; + if (pf == null) + continue; + + var cacheField = pivotTableCacheDefinitionPart.PivotCacheDefinition.CacheFields.ElementAt(cf.Index.Value) as CacheField; + if (pf.Name != null) + pivotField = pt.ColumnLabels.Add(pf.Name.Value); + else if (cacheField.Name != null) + pivotField = pt.ColumnLabels.Add(cacheField.Name.Value); + else + continue; + + if (pivotField != null) + { + var items = pf.Items.OfType().Where(i => i.Index != null && i.Index.HasValue); + if (!items.Any(i => i.HideDetails == null || BooleanValue.ToBoolean(i.HideDetails))) + pivotField.SetCollapsed(); + } + } + } + } + + // Values + if (pivotTableDefinition.DataFields != null) + { + foreach (var df in pivotTableDefinition.DataFields.Cast()) + { + IXLPivotValue pivotValue = null; + if ((int)df.Field.Value == -2) + pivotValue = pt.Values.Add(XLConstants.PivotTableValuesSentinalLabel); + else if (df.Field.Value < pivotTableDefinition.PivotFields.Count) + { + var pf = pivotTableDefinition.PivotFields.ElementAt((int)df.Field.Value) as PivotField; + if (pf == null) + continue; + + var cacheField = pivotTableCacheDefinitionPart.PivotCacheDefinition.CacheFields.ElementAt((int)df.Field.Value) as CacheField; + + if (pf.Name != null) + pivotValue = pt.Values.Add(pf.Name.Value, df.Name.Value); + else if (cacheField.Name != null) + pivotValue = pt.Values.Add(cacheField.Name.Value, df.Name.Value); + else + continue; + + if (df.NumberFormatId != null) pivotValue.NumberFormat.SetNumberFormatId((int)df.NumberFormatId.Value); + if (df.Subtotal != null) pivotValue = pivotValue.SetSummaryFormula(df.Subtotal.Value.ToClosedXml()); + if (df.ShowDataAs != null) + { + var calculation = pivotValue.Calculation; + calculation = df.ShowDataAs.Value.ToClosedXml(); + pivotValue = pivotValue.SetCalculation(calculation); + } + + if (df.BaseField != null) + { + var col = pt.SourceRange.Column(df.BaseField.Value + 1); + + var items = col.CellsUsed() + .Select(c => c.Value) + .Skip(1) // Skip header column + .Distinct().ToList(); + + pivotValue.BaseField = col.FirstCell().GetValue(); + if (df.BaseItem != null) pivotValue.BaseItem = items[(int)df.BaseItem.Value].ToString(); + } + } + } + } + } + } + } + } + + #endregion } #region Comment Helpers @@ -913,18 +1125,11 @@ { if (!XLHelper.IsNullOrWhiteSpace(cell.CellValue.Text)) xlCell._cellValue = Double.Parse(cell.CellValue.Text, XLHelper.NumberStyle, XLHelper.ParseCulture).ToInvariantString(); + if (s == null) - { xlCell._dataType = XLCellValues.Number; - } else - { - var numberFormatId = ((CellFormat)(s.CellFormats).ElementAt(styleIndex)).NumberFormatId; - if (numberFormatId == 46U) - xlCell.DataType = XLCellValues.TimeSpan; - else - xlCell._dataType = XLCellValues.Number; - } + xlCell.DataType = GetDataTypeFromCell(xlCell.Style.NumberFormat); } } else if (cell.CellValue != null) @@ -938,6 +1143,7 @@ var numberFormatId = ((CellFormat)(s.CellFormats).ElementAt(styleIndex)).NumberFormatId; if (!XLHelper.IsNullOrWhiteSpace(cell.CellValue.Text)) xlCell._cellValue = Double.Parse(cell.CellValue.Text, CultureInfo.InvariantCulture).ToInvariantString(); + if (s.NumberingFormats != null && s.NumberingFormats.Any(nf => ((NumberingFormat)nf).NumberFormatId.Value == numberFormatId)) { @@ -950,15 +1156,7 @@ else xlCell.Style.NumberFormat.NumberFormatId = Int32.Parse(numberFormatId); - if (!XLHelper.IsNullOrWhiteSpace(xlCell.Style.NumberFormat.Format)) - xlCell._dataType = GetDataTypeFromFormat(xlCell.Style.NumberFormat.Format); - else if ((numberFormatId >= 14 && numberFormatId <= 22) || - (numberFormatId >= 45 && numberFormatId <= 47)) - xlCell._dataType = XLCellValues.DateTime; - else if (numberFormatId == 49) - xlCell._dataType = XLCellValues.Text; - else - xlCell._dataType = XLCellValues.Number; + xlCell.DataType = GetDataTypeFromCell(xlCell.Style.NumberFormat); } } } @@ -1223,7 +1421,29 @@ } } - private static XLCellValues GetDataTypeFromFormat(String format) + private static XLCellValues GetDataTypeFromCell(IXLNumberFormat numberFormat) + { + var numberFormatId = numberFormat.NumberFormatId; + if (numberFormatId == 46U) + return XLCellValues.TimeSpan; + else if ((numberFormatId >= 14 && numberFormatId <= 22) || + (numberFormatId >= 45 && numberFormatId <= 47)) + return XLCellValues.DateTime; + else if (numberFormatId == 49) + return XLCellValues.Text; + else + { + if (!XLHelper.IsNullOrWhiteSpace(numberFormat.Format)) + { + var dataType = GetDataTypeFromFormat(numberFormat.Format); + return dataType.HasValue ? dataType.Value : XLCellValues.Number; + } + else + return XLCellValues.Number; + } + } + + private static XLCellValues? GetDataTypeFromFormat(String format) { int length = format.Length; String f = format.ToLower(); @@ -1237,7 +1457,7 @@ else if (c == 'y' || c == 'm' || c == 'd' || c == 'h' || c == 's') return XLCellValues.DateTime; } - return XLCellValues.Text; + return null; } private static void LoadAutoFilter(AutoFilter af, XLWorksheet ws) @@ -1448,6 +1668,10 @@ } } + /// + /// Loads the conditional formatting. + /// https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.conditionalformattingrule%28v=office.15%29.aspx?f=255&MSPPError=-2147217396 + /// private void LoadConditionalFormatting(ConditionalFormatting conditionalFormatting, XLWorksheet ws, Dictionary differentialFormats) { if (conditionalFormatting == null) return; @@ -1464,18 +1688,26 @@ LoadBorder(differentialFormats[(Int32)fr.FormatId.Value].Border, conditionalFormat.Style.Border); LoadNumberFormat(differentialFormats[(Int32)fr.FormatId.Value].NumberingFormat, conditionalFormat.Style.NumberFormat); } - if (fr.Operator != null) + + // The conditional formatting type is compulsory. If it doesn't exist, skip the entire rule. + if (fr.Type == null) continue; + conditionalFormat.ConditionalFormatType = fr.Type.Value.ToClosedXml(); + + if (conditionalFormat.ConditionalFormatType == XLConditionalFormatType.CellIs && fr.Operator != null) conditionalFormat.Operator = fr.Operator.Value.ToClosedXml(); - if (fr.Type != null) - conditionalFormat.ConditionalFormatType = fr.Type.Value.ToClosedXml(); - if (fr.Text != null) + + if (fr.Text != null && !XLHelper.IsNullOrWhiteSpace(fr.Text)) conditionalFormat.Values.Add(GetFormula(fr.Text.Value)); - if (fr.Percent != null) - conditionalFormat.Percent = fr.Percent.Value; - if (fr.Bottom != null) - conditionalFormat.Bottom = fr.Bottom.Value; - if (fr.Rank != null) - conditionalFormat.Values.Add(GetFormula(fr.Rank.Value.ToString())); + + if (conditionalFormat.ConditionalFormatType == XLConditionalFormatType.Top10) + { + if (fr.Percent != null) + conditionalFormat.Percent = fr.Percent.Value; + if (fr.Bottom != null) + conditionalFormat.Bottom = fr.Bottom.Value; + if (fr.Rank != null) + conditionalFormat.Values.Add(GetFormula(fr.Rank.Value.ToString())); + } if (fr.Elements().Any()) { @@ -1598,28 +1830,33 @@ ws.PageSetup.RowBreaks.Add(Int32.Parse(rowBreak.Id.InnerText)); } - private void LoadSheetProperties(SheetProperties sheetProperty, XLWorksheet ws) + private void LoadSheetProperties(SheetProperties sheetProperty, XLWorksheet ws, out PageSetupProperties pageSetupProperties) { + pageSetupProperties = null; if (sheetProperty == null) return; if (sheetProperty.TabColor != null) ws.TabColor = GetColor(sheetProperty.TabColor); - if (sheetProperty.OutlineProperties == null) return; - - if (sheetProperty.OutlineProperties.SummaryBelow != null) + if (sheetProperty.OutlineProperties != null) { - ws.Outline.SummaryVLocation = sheetProperty.OutlineProperties.SummaryBelow - ? XLOutlineSummaryVLocation.Bottom - : XLOutlineSummaryVLocation.Top; + if (sheetProperty.OutlineProperties.SummaryBelow != null) + { + ws.Outline.SummaryVLocation = sheetProperty.OutlineProperties.SummaryBelow + ? XLOutlineSummaryVLocation.Bottom + : XLOutlineSummaryVLocation.Top; + } + + if (sheetProperty.OutlineProperties.SummaryRight != null) + { + ws.Outline.SummaryHLocation = sheetProperty.OutlineProperties.SummaryRight + ? XLOutlineSummaryHLocation.Right + : XLOutlineSummaryHLocation.Left; + } } - if (sheetProperty.OutlineProperties.SummaryRight != null) - { - ws.Outline.SummaryHLocation = sheetProperty.OutlineProperties.SummaryRight - ? XLOutlineSummaryHLocation.Right - : XLOutlineSummaryHLocation.Left; - } + if (sheetProperty.PageSetupProperties != null) + pageSetupProperties = sheetProperty.PageSetupProperties; } private static void LoadHeaderFooter(HeaderFooter headerFooter, XLWorksheet ws) @@ -1663,7 +1900,7 @@ ((XLHeaderFooter)ws.PageSetup.Footer).SetAsInitial(); } - private static void LoadPageSetup(PageSetup pageSetup, XLWorksheet ws) + private static void LoadPageSetup(PageSetup pageSetup, XLWorksheet ws, PageSetupProperties pageSetupProperties) { if (pageSetup == null) return; @@ -1671,11 +1908,16 @@ ws.PageSetup.PaperSize = (XLPaperSize)Int32.Parse(pageSetup.PaperSize.InnerText); if (pageSetup.Scale != null) ws.PageSetup.Scale = Int32.Parse(pageSetup.Scale.InnerText); - else + if (pageSetupProperties != null && pageSetupProperties.FitToPage != null && pageSetupProperties.FitToPage.Value) { - if (pageSetup.FitToWidth != null) + if (pageSetup.FitToWidth == null) + ws.PageSetup.PagesWide = 1; + else ws.PageSetup.PagesWide = Int32.Parse(pageSetup.FitToWidth.InnerText); - if (pageSetup.FitToHeight != null) + + if (pageSetup.FitToHeight == null) + ws.PageSetup.PagesTall = 1; + else ws.PageSetup.PagesTall = Int32.Parse(pageSetup.FitToHeight.InnerText); } if (pageSetup.PageOrder != null) @@ -1757,8 +1999,16 @@ ws.Cell(selection.ActiveCell).SetActive(); } - var pane = sheetView.Elements().FirstOrDefault(); + if (sheetView.ZoomScale != null) + ws.SheetView.ZoomScale = (int)UInt32Value.ToUInt32(sheetView.ZoomScale); + if (sheetView.ZoomScaleNormal != null) + ws.SheetView.ZoomScaleNormal = (int)UInt32Value.ToUInt32(sheetView.ZoomScaleNormal); + if (sheetView.ZoomScalePageLayoutView != null) + ws.SheetView.ZoomScalePageLayoutView = (int)UInt32Value.ToUInt32(sheetView.ZoomScalePageLayoutView); + if (sheetView.ZoomScaleSheetLayoutView != null) + ws.SheetView.ZoomScaleSheetLayoutView = (int)UInt32Value.ToUInt32(sheetView.ZoomScaleSheetLayoutView); + var pane = sheetView.Elements().FirstOrDefault(); if (pane == null) return; if (pane.State == null || @@ -2025,4 +2275,4 @@ return false; } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs b/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs index 9bcb8a2..c4dee57 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs @@ -101,7 +101,7 @@ id++; } } - public void AddValues(List values, RelType relType) + public void AddValues(IEnumerable values, RelType relType) { if (!_relIds.ContainsKey(relType)) { diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index bc6df99..9a99f83 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -43,6 +43,7 @@ using Run = DocumentFormat.OpenXml.Spreadsheet.Run; using RunProperties = DocumentFormat.OpenXml.Spreadsheet.RunProperties; using VerticalTextAlignment = DocumentFormat.OpenXml.Spreadsheet.VerticalTextAlignment; +using System.Threading; namespace ClosedXML.Excel { @@ -64,9 +65,9 @@ switch (xlCell.DataType) { case XLCellValues.Text: - { - return xlCell.ShareString ? CvSharedString : CvInlineString; - } + { + return xlCell.ShareString ? CvSharedString : CvInlineString; + } case XLCellValues.Number: return CvNumber; case XLCellValues.DateTime: @@ -82,8 +83,20 @@ private bool Validate(SpreadsheetDocument package) { - var validator = new OpenXmlValidator(); - var errors = validator.Validate(package); + var backupCulture = Thread.CurrentThread.CurrentCulture; + + IEnumerable errors; + try + { + Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture; + var validator = new OpenXmlValidator(); + errors = validator.Validate(package); + } + finally + { + Thread.CurrentThread.CurrentCulture = backupCulture; + } + if (errors.Any()) { var message = string.Join("\r\n", errors.Select(e => string.Format("{0} in {1}", e.Description, e.Path.XPath)).ToArray()); @@ -172,8 +185,8 @@ } } - // Get the CalculationChainPart - //Note: An instance of this part type contains an ordered set of references to all cells in all worksheets in the + // Get the CalculationChainPart + //Note: An instance of this part type contains an ordered set of references to all cells in all worksheets in the //workbook whose value is calculated from any formula CalculationChainPart calChainPart; @@ -207,7 +220,7 @@ var workbookPart = document.WorkbookPart ?? document.AddWorkbookPart(); var worksheets = WorksheetsInternal; - + var partsToRemove = workbookPart.Parts.Where(s => worksheets.Deleted.Contains(s.RelationshipId)).ToList(); @@ -222,7 +235,15 @@ worksheets.Deleted.ToList().ForEach(ws => DeleteSheetAndDependencies(workbookPart, ws)); - context.RelIdGenerator.AddValues(workbookPart.Parts.Select(p => p.RelationshipId).ToList(), RelType.Workbook); + // Ensure all RelId's have been added to the context + context.RelIdGenerator.AddValues(workbookPart.Parts.Select(p => p.RelationshipId), RelType.Workbook); + context.RelIdGenerator.AddValues(WorksheetsInternal.Cast().Where(ws => !XLHelper.IsNullOrWhiteSpace(ws.RelId)).Select(ws => ws.RelId), RelType.Workbook); + context.RelIdGenerator.AddValues(WorksheetsInternal.Cast().Where(ws => !XLHelper.IsNullOrWhiteSpace(ws.LegacyDrawingId)).Select(ws => ws.LegacyDrawingId), RelType.Workbook); + context.RelIdGenerator.AddValues(WorksheetsInternal + .Cast() + .SelectMany(ws => ws.Tables.Cast()) + .Where(t => !XLHelper.IsNullOrWhiteSpace(t.RelId)) + .Select(t => t.RelId), RelType.Workbook); var extendedFilePropertiesPart = document.ExtendedFilePropertiesPart ?? document.AddNewPart( @@ -259,21 +280,19 @@ worksheetPart = workbookPart.AddNewPart(wsRelId); - context.RelIdGenerator.AddValues(worksheetPart.HyperlinkRelationships.Select(hr => hr.Id).ToList(), - RelType.Workbook); - context.RelIdGenerator.AddValues(worksheetPart.Parts.Select(p => p.RelationshipId).ToList(), - RelType.Workbook); + context.RelIdGenerator.AddValues(worksheetPart.HyperlinkRelationships.Select(hr => hr.Id), RelType.Workbook); + context.RelIdGenerator.AddValues(worksheetPart.Parts.Select(p => p.RelationshipId), RelType.Workbook); if (worksheetPart.DrawingsPart != null) - context.RelIdGenerator.AddValues( - worksheetPart.DrawingsPart.Parts.Select(p => p.RelationshipId).ToList(), RelType.Workbook); + context.RelIdGenerator.AddValues(worksheetPart.DrawingsPart.Parts.Select(p => p.RelationshipId), RelType.Workbook); // delete comment related parts (todo: review) DeleteComments(worksheetPart, worksheet, context); if (worksheet.Internals.CellsCollection.GetCells(c => c.HasComment).Any()) { + var id = context.RelIdGenerator.GetNext(RelType.Workbook); var worksheetCommentsPart = - worksheetPart.AddNewPart(context.RelIdGenerator.GetNext(RelType.Workbook)); + worksheetPart.AddNewPart(id); GenerateWorksheetCommentsPartContent(worksheetCommentsPart, worksheet); @@ -298,6 +317,13 @@ { GeneratePivotTables(workbookPart, worksheetPart, worksheet, context); } + + // Remove any orphaned references - maybe more types? + foreach (var orphan in worksheetPart.Worksheet.OfType().Where(lg => !worksheetPart.Parts.Any(p => p.RelationshipId == lg.Id))) + worksheetPart.Worksheet.RemoveChild(orphan); + + foreach (var orphan in worksheetPart.Worksheet.OfType().Where(d => !worksheetPart.Parts.Any(p => p.RelationshipId == d.Id))) + worksheetPart.Worksheet.RemoveChild(orphan); } // Remove empty pivot cache part @@ -402,13 +428,13 @@ } if (properties.Application == null) - properties.AppendChild(new Application {Text = "Microsoft Excel"}); + properties.AppendChild(new Application { Text = "Microsoft Excel" }); if (properties.DocumentSecurity == null) - properties.AppendChild(new DocumentSecurity {Text = "0"}); + properties.AppendChild(new DocumentSecurity { Text = "0" }); if (properties.ScaleCrop == null) - properties.AppendChild(new ScaleCrop {Text = "false"}); + properties.AppendChild(new ScaleCrop { Text = "false" }); if (properties.HeadingPairs == null) properties.HeadingPairs = new HeadingPairs(); @@ -416,16 +442,16 @@ if (properties.TitlesOfParts == null) properties.TitlesOfParts = new TitlesOfParts(); - properties.HeadingPairs.VTVector = new VTVector {BaseType = VectorBaseValues.Variant}; + properties.HeadingPairs.VTVector = new VTVector { BaseType = VectorBaseValues.Variant }; - properties.TitlesOfParts.VTVector = new VTVector {BaseType = VectorBaseValues.Lpstr}; + properties.TitlesOfParts.VTVector = new VTVector { BaseType = VectorBaseValues.Lpstr }; var vTVectorOne = properties.HeadingPairs.VTVector; var vTVectorTwo = properties.TitlesOfParts.VTVector; var modifiedWorksheets = - ((IEnumerable)WorksheetsInternal).Select(w => new {w.Name, Order = w.Position}).ToList(); + ((IEnumerable)WorksheetsInternal).Select(w => new { w.Name, Order = w.Position }).ToList(); var modifiedNamedRanges = GetModifiedNamedRanges(); var modifiedWorksheetsCount = modifiedWorksheets.Count; var modifiedNamedRangesCount = modifiedNamedRanges.Count; @@ -436,10 +462,10 @@ vTVectorTwo.Size = (UInt32)(modifiedNamedRangesCount + modifiedWorksheetsCount); foreach ( - var vTlpstr3 in modifiedWorksheets.OrderBy(w => w.Order).Select(w => new VTLPSTR {Text = w.Name})) + var vTlpstr3 in modifiedWorksheets.OrderBy(w => w.Order).Select(w => new VTLPSTR { Text = w.Name })) vTVectorTwo.AppendChild(vTlpstr3); - foreach (var vTlpstr7 in modifiedNamedRanges.Select(nr => new VTLPSTR {Text = nr})) + foreach (var vTlpstr7 in modifiedNamedRanges.Select(nr => new VTLPSTR { Text = nr })) vTVectorTwo.AppendChild(vTlpstr7); if (Properties.Manager != null) @@ -471,8 +497,8 @@ private static void InsertOnVtVector(VTVector vTVector, String property, Int32 index, String text) { var m = from e1 in vTVector.Elements() - where e1.Elements().Any(e2 => e2.Text == property) - select e1; + where e1.Elements().Any(e2 => e2.Text == property) + select e1; if (!m.Any()) { if (vTVector.Size == null) @@ -480,7 +506,7 @@ vTVector.Size += 2U; var variant1 = new Variant(); - var vTlpstr1 = new VTLPSTR {Text = property}; + var vTlpstr1 = new VTLPSTR { Text = property }; variant1.AppendChild(vTlpstr1); vTVector.InsertAt(variant1, index); @@ -555,7 +581,7 @@ { workbook.WorkbookProtection = null; } - + if (workbook.BookViews == null) workbook.BookViews = new BookViews(); @@ -581,40 +607,45 @@ foreach (var xlSheet in WorksheetsInternal.Cast().OrderBy(w => w.Position)) { string rId; - if (xlSheet.SheetId == 0) + if (xlSheet.SheetId == 0 && XLHelper.IsNullOrWhiteSpace(xlSheet.RelId)) { rId = context.RelIdGenerator.GetNext(RelType.Workbook); - while (WorksheetsInternal.Cast().Any(w => w.SheetId == Int32.Parse(rId.Substring(3)))) - rId = context.RelIdGenerator.GetNext(RelType.Workbook); + while (WorksheetsInternal.Cast().Any(w => w.SheetId == Int32.Parse(rId.Substring(3)))) + rId = context.RelIdGenerator.GetNext(RelType.Workbook); - xlSheet.SheetId = Int32.Parse(rId.Substring(3)); - xlSheet.RelId = rId; + xlSheet.SheetId = Int32.Parse(rId.Substring(3)); + xlSheet.RelId = rId; } else { + if (XLHelper.IsNullOrWhiteSpace(xlSheet.RelId)) + { rId = String.Format("rId{0}", xlSheet.SheetId); - context.RelIdGenerator.AddValues(new List { rId }, RelType.Workbook); + context.RelIdGenerator.AddValues(new List { rId }, RelType.Workbook); + } + else + rId = xlSheet.RelId; } if (!workbook.Sheets.Cast().Any(s => s.Id == rId)) { - var newSheet = new Sheet - { - Name = xlSheet.Name, - Id = rId, - SheetId = (UInt32)xlSheet.SheetId - }; + var newSheet = new Sheet + { + Name = xlSheet.Name, + Id = rId, + SheetId = (UInt32)xlSheet.SheetId + }; - workbook.Sheets.AppendChild(newSheet); - } + workbook.Sheets.AppendChild(newSheet); + } } var sheetElements = from sheet in workbook.Sheets.Elements() - join worksheet in ((IEnumerable)WorksheetsInternal) on sheet.Id.Value - equals worksheet.RelId - orderby worksheet.Position - select sheet; + join worksheet in ((IEnumerable)WorksheetsInternal) on sheet.Id.Value + equals worksheet.RelId + orderby worksheet.Position + select sheet; UInt32 firstSheetVisible = 0; var activeTab = @@ -665,7 +696,7 @@ if (workbookView == null) { - workbookView = new WorkbookView {ActiveTab = activeTab, FirstSheet = firstSheetVisible}; + workbookView = new WorkbookView { ActiveTab = activeTab, FirstSheet = firstSheetVisible }; workbook.BookViews.AppendChild(workbookView); } else @@ -686,7 +717,7 @@ if (worksheet.PageSetup.PrintAreas.Any()) { - var definedName = new DefinedName {Name = "_xlnm.Print_Area", LocalSheetId = sheetId}; + var definedName = new DefinedName { Name = "_xlnm.Print_Area", LocalSheetId = sheetId }; var worksheetName = worksheet.Name; var definedNameText = worksheet.PageSetup.PrintAreas.Aggregate(String.Empty, (current, printArea) => @@ -796,7 +827,7 @@ workbook.DefinedNames = definedNames; if (workbook.CalculationProperties == null) - workbook.CalculationProperties = new CalculationProperties {CalculationId = 125725U}; + workbook.CalculationProperties = new CalculationProperties { CalculationId = 125725U }; if (CalculateMode == XLCalculateMode.Default) workbook.CalculationProperties.CalculationMode = null; @@ -821,7 +852,7 @@ var x = 0; Worksheets.ForEach(w => w.Tables.ForEach(t => x = (t as XLTable).FieldNames.Count)); - sharedStringTablePart.SharedStringTable = new SharedStringTable {Count = 0, UniqueCount = 0}; + sharedStringTablePart.SharedStringTable = new SharedStringTable { Count = 0, UniqueCount = 0 }; var stringId = 0; @@ -860,7 +891,7 @@ EndingBaseIndex = (UInt32)p.End }; - var text = new Text {Text = p.Text}; + var text = new Text { Text = p.Text }; if (p.Text.PreserveSpaces()) text.Space = SpaceProcessingModeValues.Preserve; @@ -869,7 +900,7 @@ } var f = new XLFont(null, c.RichText.Phonetics); if (!context.SharedFonts.ContainsKey(f)) - context.SharedFonts.Add(f, new FontInfo {Font = f}); + context.SharedFonts.Add(f, new FontInfo { Font = f }); var phoneticProperties = new PhoneticProperties { @@ -904,7 +935,7 @@ { var s = c.Value.ToString(); var sharedStringItem = new SharedStringItem(); - var text = new Text {Text = XmlEncoder.EncodeString(s)}; + var text = new Text { Text = XmlEncoder.EncodeString(s) }; if (!s.Trim().Equals(s)) text.Space = SpaceProcessingModeValues.Preserve; sharedStringItem.Append(text); @@ -930,16 +961,16 @@ var bold = rt.Bold ? new Bold() : null; var italic = rt.Italic ? new Italic() : null; var underline = rt.Underline != XLFontUnderlineValues.None - ? new Underline {Val = rt.Underline.ToOpenXml()} + ? new Underline { Val = rt.Underline.ToOpenXml() } : null; var strike = rt.Strikethrough ? new Strike() : null; var verticalAlignment = new VerticalTextAlignment - {Val = rt.VerticalAlignment.ToOpenXml()}; + { Val = rt.VerticalAlignment.ToOpenXml() }; var shadow = rt.Shadow ? new Shadow() : null; - var fontSize = new FontSize {Val = rt.FontSize}; + var fontSize = new FontSize { Val = rt.FontSize }; var color = GetNewColor(rt.FontColor); - var fontName = new RunFont {Val = rt.FontName}; - var fontFamilyNumbering = new FontFamily {Val = (Int32)rt.FontFamilyNumbering}; + var fontName = new RunFont { Val = rt.FontName }; + var fontFamilyNumbering = new FontFamily { Val = (Int32)rt.FontFamilyNumbering }; if (bold != null) runProperties.Append(bold); if (italic != null) runProperties.Append(italic); @@ -954,7 +985,7 @@ runProperties.Append(fontName); runProperties.Append(fontFamilyNumbering); - var text = new Text {Text = rt.Text}; + var text = new Text { Text = rt.Text }; if (rt.Text.PreserveSpaces()) text.Space = SpaceProcessingModeValues.Preserve; @@ -998,7 +1029,7 @@ { cc.Array = true; calculationChain.AppendChild(cc); - calculationChain.AppendChild(new CalculationCell {CellReference = c.Address.ToString(), InChildChain = true}); + calculationChain.AppendChild(new CalculationCell { CellReference = c.Address.ToString(), InChildChain = true }); } else { @@ -1018,13 +1049,13 @@ //var cCellsToRemove = new List(); var m = from cc in calculationChain.Elements() - where !(cc.SheetId != null || cc.InChildChain != null) - && calculationChain.Elements() - .Where(c1 => c1.SheetId != null) - .Select(c1 => c1.CellReference.Value) - .Contains(cc.CellReference.Value) - || cellsWithoutFormulas.Contains(cc.CellReference.Value) - select cc; + where !(cc.SheetId != null || cc.InChildChain != null) + && calculationChain.Elements() + .Where(c1 => c1.SheetId != null) + .Select(c1 => c1.CellReference.Value) + .Contains(cc.CellReference.Value) + || cellsWithoutFormulas.Contains(cc.CellReference.Value) + select cc; //m.ToList().ForEach(cc => cCellsToRemove.Add(cc)); m.ToList().ForEach(cc => calculationChain.RemoveChild(cc)); } @@ -1035,12 +1066,12 @@ private void GenerateThemePartContent(ThemePart themePart) { - var theme1 = new Theme {Name = "Office Theme"}; + var theme1 = new Theme { Name = "Office Theme" }; theme1.AddNamespaceDeclaration("a", "http://schemas.openxmlformats.org/drawingml/2006/main"); var themeElements1 = new ThemeElements(); - var colorScheme1 = new ColorScheme {Name = "Office"}; + var colorScheme1 = new ColorScheme { Name = "Office" }; var dark1Color1 = new Dark1Color(); var systemColor1 = new SystemColor @@ -1061,52 +1092,52 @@ light1Color1.AppendChild(systemColor2); var dark2Color1 = new Dark2Color(); - var rgbColorModelHex1 = new RgbColorModelHex {Val = Theme.Text2.Color.ToHex().Substring(2)}; + var rgbColorModelHex1 = new RgbColorModelHex { Val = Theme.Text2.Color.ToHex().Substring(2) }; dark2Color1.AppendChild(rgbColorModelHex1); var light2Color1 = new Light2Color(); - var rgbColorModelHex2 = new RgbColorModelHex {Val = Theme.Background2.Color.ToHex().Substring(2)}; + var rgbColorModelHex2 = new RgbColorModelHex { Val = Theme.Background2.Color.ToHex().Substring(2) }; light2Color1.AppendChild(rgbColorModelHex2); var accent1Color1 = new Accent1Color(); - var rgbColorModelHex3 = new RgbColorModelHex {Val = Theme.Accent1.Color.ToHex().Substring(2)}; + var rgbColorModelHex3 = new RgbColorModelHex { Val = Theme.Accent1.Color.ToHex().Substring(2) }; accent1Color1.AppendChild(rgbColorModelHex3); var accent2Color1 = new Accent2Color(); - var rgbColorModelHex4 = new RgbColorModelHex {Val = Theme.Accent2.Color.ToHex().Substring(2)}; + var rgbColorModelHex4 = new RgbColorModelHex { Val = Theme.Accent2.Color.ToHex().Substring(2) }; accent2Color1.AppendChild(rgbColorModelHex4); var accent3Color1 = new Accent3Color(); - var rgbColorModelHex5 = new RgbColorModelHex {Val = Theme.Accent3.Color.ToHex().Substring(2)}; + var rgbColorModelHex5 = new RgbColorModelHex { Val = Theme.Accent3.Color.ToHex().Substring(2) }; accent3Color1.AppendChild(rgbColorModelHex5); var accent4Color1 = new Accent4Color(); - var rgbColorModelHex6 = new RgbColorModelHex {Val = Theme.Accent4.Color.ToHex().Substring(2)}; + var rgbColorModelHex6 = new RgbColorModelHex { Val = Theme.Accent4.Color.ToHex().Substring(2) }; accent4Color1.AppendChild(rgbColorModelHex6); var accent5Color1 = new Accent5Color(); - var rgbColorModelHex7 = new RgbColorModelHex {Val = Theme.Accent5.Color.ToHex().Substring(2)}; + var rgbColorModelHex7 = new RgbColorModelHex { Val = Theme.Accent5.Color.ToHex().Substring(2) }; accent5Color1.AppendChild(rgbColorModelHex7); var accent6Color1 = new Accent6Color(); - var rgbColorModelHex8 = new RgbColorModelHex {Val = Theme.Accent6.Color.ToHex().Substring(2)}; + var rgbColorModelHex8 = new RgbColorModelHex { Val = Theme.Accent6.Color.ToHex().Substring(2) }; accent6Color1.AppendChild(rgbColorModelHex8); var hyperlink1 = new DocumentFormat.OpenXml.Drawing.Hyperlink(); - var rgbColorModelHex9 = new RgbColorModelHex {Val = Theme.Hyperlink.Color.ToHex().Substring(2)}; + var rgbColorModelHex9 = new RgbColorModelHex { Val = Theme.Hyperlink.Color.ToHex().Substring(2) }; hyperlink1.AppendChild(rgbColorModelHex9); var followedHyperlinkColor1 = new FollowedHyperlinkColor(); - var rgbColorModelHex10 = new RgbColorModelHex {Val = Theme.FollowedHyperlink.Color.ToHex().Substring(2)}; + var rgbColorModelHex10 = new RgbColorModelHex { Val = Theme.FollowedHyperlink.Color.ToHex().Substring(2) }; followedHyperlinkColor1.AppendChild(rgbColorModelHex10); @@ -1123,41 +1154,41 @@ colorScheme1.AppendChild(hyperlink1); colorScheme1.AppendChild(followedHyperlinkColor1); - var fontScheme2 = new FontScheme {Name = "Office"}; + var fontScheme2 = new FontScheme { Name = "Office" }; var majorFont1 = new MajorFont(); - var latinFont1 = new LatinFont {Typeface = "Cambria"}; - var eastAsianFont1 = new EastAsianFont {Typeface = ""}; - var complexScriptFont1 = new ComplexScriptFont {Typeface = ""}; - var supplementalFont1 = new SupplementalFont {Script = "Jpan", Typeface = "ＭＳ Ｐゴシック"}; - var supplementalFont2 = new SupplementalFont {Script = "Hang", Typeface = "맑은 고딕"}; - var supplementalFont3 = new SupplementalFont {Script = "Hans", Typeface = "宋体"}; - var supplementalFont4 = new SupplementalFont {Script = "Hant", Typeface = "新細明體"}; - var supplementalFont5 = new SupplementalFont {Script = "Arab", Typeface = "Times New Roman"}; - var supplementalFont6 = new SupplementalFont {Script = "Hebr", Typeface = "Times New Roman"}; - var supplementalFont7 = new SupplementalFont {Script = "Thai", Typeface = "Tahoma"}; - var supplementalFont8 = new SupplementalFont {Script = "Ethi", Typeface = "Nyala"}; - var supplementalFont9 = new SupplementalFont {Script = "Beng", Typeface = "Vrinda"}; - var supplementalFont10 = new SupplementalFont {Script = "Gujr", Typeface = "Shruti"}; - var supplementalFont11 = new SupplementalFont {Script = "Khmr", Typeface = "MoolBoran"}; - var supplementalFont12 = new SupplementalFont {Script = "Knda", Typeface = "Tunga"}; - var supplementalFont13 = new SupplementalFont {Script = "Guru", Typeface = "Raavi"}; - var supplementalFont14 = new SupplementalFont {Script = "Cans", Typeface = "Euphemia"}; - var supplementalFont15 = new SupplementalFont {Script = "Cher", Typeface = "Plantagenet Cherokee"}; - var supplementalFont16 = new SupplementalFont {Script = "Yiii", Typeface = "Microsoft Yi Baiti"}; - var supplementalFont17 = new SupplementalFont {Script = "Tibt", Typeface = "Microsoft Himalaya"}; - var supplementalFont18 = new SupplementalFont {Script = "Thaa", Typeface = "MV Boli"}; - var supplementalFont19 = new SupplementalFont {Script = "Deva", Typeface = "Mangal"}; - var supplementalFont20 = new SupplementalFont {Script = "Telu", Typeface = "Gautami"}; - var supplementalFont21 = new SupplementalFont {Script = "Taml", Typeface = "Latha"}; - var supplementalFont22 = new SupplementalFont {Script = "Syrc", Typeface = "Estrangelo Edessa"}; - var supplementalFont23 = new SupplementalFont {Script = "Orya", Typeface = "Kalinga"}; - var supplementalFont24 = new SupplementalFont {Script = "Mlym", Typeface = "Kartika"}; - var supplementalFont25 = new SupplementalFont {Script = "Laoo", Typeface = "DokChampa"}; - var supplementalFont26 = new SupplementalFont {Script = "Sinh", Typeface = "Iskoola Pota"}; - var supplementalFont27 = new SupplementalFont {Script = "Mong", Typeface = "Mongolian Baiti"}; - var supplementalFont28 = new SupplementalFont {Script = "Viet", Typeface = "Times New Roman"}; - var supplementalFont29 = new SupplementalFont {Script = "Uigh", Typeface = "Microsoft Uighur"}; + var latinFont1 = new LatinFont { Typeface = "Cambria" }; + var eastAsianFont1 = new EastAsianFont { Typeface = "" }; + var complexScriptFont1 = new ComplexScriptFont { Typeface = "" }; + var supplementalFont1 = new SupplementalFont { Script = "Jpan", Typeface = "ＭＳ Ｐゴシック" }; + var supplementalFont2 = new SupplementalFont { Script = "Hang", Typeface = "맑은 고딕" }; + var supplementalFont3 = new SupplementalFont { Script = "Hans", Typeface = "宋体" }; + var supplementalFont4 = new SupplementalFont { Script = "Hant", Typeface = "新細明體" }; + var supplementalFont5 = new SupplementalFont { Script = "Arab", Typeface = "Times New Roman" }; + var supplementalFont6 = new SupplementalFont { Script = "Hebr", Typeface = "Times New Roman" }; + var supplementalFont7 = new SupplementalFont { Script = "Thai", Typeface = "Tahoma" }; + var supplementalFont8 = new SupplementalFont { Script = "Ethi", Typeface = "Nyala" }; + var supplementalFont9 = new SupplementalFont { Script = "Beng", Typeface = "Vrinda" }; + var supplementalFont10 = new SupplementalFont { Script = "Gujr", Typeface = "Shruti" }; + var supplementalFont11 = new SupplementalFont { Script = "Khmr", Typeface = "MoolBoran" }; + var supplementalFont12 = new SupplementalFont { Script = "Knda", Typeface = "Tunga" }; + var supplementalFont13 = new SupplementalFont { Script = "Guru", Typeface = "Raavi" }; + var supplementalFont14 = new SupplementalFont { Script = "Cans", Typeface = "Euphemia" }; + var supplementalFont15 = new SupplementalFont { Script = "Cher", Typeface = "Plantagenet Cherokee" }; + var supplementalFont16 = new SupplementalFont { Script = "Yiii", Typeface = "Microsoft Yi Baiti" }; + var supplementalFont17 = new SupplementalFont { Script = "Tibt", Typeface = "Microsoft Himalaya" }; + var supplementalFont18 = new SupplementalFont { Script = "Thaa", Typeface = "MV Boli" }; + var supplementalFont19 = new SupplementalFont { Script = "Deva", Typeface = "Mangal" }; + var supplementalFont20 = new SupplementalFont { Script = "Telu", Typeface = "Gautami" }; + var supplementalFont21 = new SupplementalFont { Script = "Taml", Typeface = "Latha" }; + var supplementalFont22 = new SupplementalFont { Script = "Syrc", Typeface = "Estrangelo Edessa" }; + var supplementalFont23 = new SupplementalFont { Script = "Orya", Typeface = "Kalinga" }; + var supplementalFont24 = new SupplementalFont { Script = "Mlym", Typeface = "Kartika" }; + var supplementalFont25 = new SupplementalFont { Script = "Laoo", Typeface = "DokChampa" }; + var supplementalFont26 = new SupplementalFont { Script = "Sinh", Typeface = "Iskoola Pota" }; + var supplementalFont27 = new SupplementalFont { Script = "Mong", Typeface = "Mongolian Baiti" }; + var supplementalFont28 = new SupplementalFont { Script = "Viet", Typeface = "Times New Roman" }; + var supplementalFont29 = new SupplementalFont { Script = "Uigh", Typeface = "Microsoft Uighur" }; majorFont1.AppendChild(latinFont1); majorFont1.AppendChild(eastAsianFont1); @@ -1193,38 +1224,38 @@ majorFont1.AppendChild(supplementalFont29); var minorFont1 = new MinorFont(); - var latinFont2 = new LatinFont {Typeface = "Calibri"}; - var eastAsianFont2 = new EastAsianFont {Typeface = ""}; - var complexScriptFont2 = new ComplexScriptFont {Typeface = ""}; - var supplementalFont30 = new SupplementalFont {Script = "Jpan", Typeface = "ＭＳ Ｐゴシック"}; - var supplementalFont31 = new SupplementalFont {Script = "Hang", Typeface = "맑은 고딕"}; - var supplementalFont32 = new SupplementalFont {Script = "Hans", Typeface = "宋体"}; - var supplementalFont33 = new SupplementalFont {Script = "Hant", Typeface = "新細明體"}; - var supplementalFont34 = new SupplementalFont {Script = "Arab", Typeface = "Arial"}; - var supplementalFont35 = new SupplementalFont {Script = "Hebr", Typeface = "Arial"}; - var supplementalFont36 = new SupplementalFont {Script = "Thai", Typeface = "Tahoma"}; - var supplementalFont37 = new SupplementalFont {Script = "Ethi", Typeface = "Nyala"}; - var supplementalFont38 = new SupplementalFont {Script = "Beng", Typeface = "Vrinda"}; - var supplementalFont39 = new SupplementalFont {Script = "Gujr", Typeface = "Shruti"}; - var supplementalFont40 = new SupplementalFont {Script = "Khmr", Typeface = "DaunPenh"}; - var supplementalFont41 = new SupplementalFont {Script = "Knda", Typeface = "Tunga"}; - var supplementalFont42 = new SupplementalFont {Script = "Guru", Typeface = "Raavi"}; - var supplementalFont43 = new SupplementalFont {Script = "Cans", Typeface = "Euphemia"}; - var supplementalFont44 = new SupplementalFont {Script = "Cher", Typeface = "Plantagenet Cherokee"}; - var supplementalFont45 = new SupplementalFont {Script = "Yiii", Typeface = "Microsoft Yi Baiti"}; - var supplementalFont46 = new SupplementalFont {Script = "Tibt", Typeface = "Microsoft Himalaya"}; - var supplementalFont47 = new SupplementalFont {Script = "Thaa", Typeface = "MV Boli"}; - var supplementalFont48 = new SupplementalFont {Script = "Deva", Typeface = "Mangal"}; - var supplementalFont49 = new SupplementalFont {Script = "Telu", Typeface = "Gautami"}; - var supplementalFont50 = new SupplementalFont {Script = "Taml", Typeface = "Latha"}; - var supplementalFont51 = new SupplementalFont {Script = "Syrc", Typeface = "Estrangelo Edessa"}; - var supplementalFont52 = new SupplementalFont {Script = "Orya", Typeface = "Kalinga"}; - var supplementalFont53 = new SupplementalFont {Script = "Mlym", Typeface = "Kartika"}; - var supplementalFont54 = new SupplementalFont {Script = "Laoo", Typeface = "DokChampa"}; - var supplementalFont55 = new SupplementalFont {Script = "Sinh", Typeface = "Iskoola Pota"}; - var supplementalFont56 = new SupplementalFont {Script = "Mong", Typeface = "Mongolian Baiti"}; - var supplementalFont57 = new SupplementalFont {Script = "Viet", Typeface = "Arial"}; - var supplementalFont58 = new SupplementalFont {Script = "Uigh", Typeface = "Microsoft Uighur"}; + var latinFont2 = new LatinFont { Typeface = "Calibri" }; + var eastAsianFont2 = new EastAsianFont { Typeface = "" }; + var complexScriptFont2 = new ComplexScriptFont { Typeface = "" }; + var supplementalFont30 = new SupplementalFont { Script = "Jpan", Typeface = "ＭＳ Ｐゴシック" }; + var supplementalFont31 = new SupplementalFont { Script = "Hang", Typeface = "맑은 고딕" }; + var supplementalFont32 = new SupplementalFont { Script = "Hans", Typeface = "宋体" }; + var supplementalFont33 = new SupplementalFont { Script = "Hant", Typeface = "新細明體" }; + var supplementalFont34 = new SupplementalFont { Script = "Arab", Typeface = "Arial" }; + var supplementalFont35 = new SupplementalFont { Script = "Hebr", Typeface = "Arial" }; + var supplementalFont36 = new SupplementalFont { Script = "Thai", Typeface = "Tahoma" }; + var supplementalFont37 = new SupplementalFont { Script = "Ethi", Typeface = "Nyala" }; + var supplementalFont38 = new SupplementalFont { Script = "Beng", Typeface = "Vrinda" }; + var supplementalFont39 = new SupplementalFont { Script = "Gujr", Typeface = "Shruti" }; + var supplementalFont40 = new SupplementalFont { Script = "Khmr", Typeface = "DaunPenh" }; + var supplementalFont41 = new SupplementalFont { Script = "Knda", Typeface = "Tunga" }; + var supplementalFont42 = new SupplementalFont { Script = "Guru", Typeface = "Raavi" }; + var supplementalFont43 = new SupplementalFont { Script = "Cans", Typeface = "Euphemia" }; + var supplementalFont44 = new SupplementalFont { Script = "Cher", Typeface = "Plantagenet Cherokee" }; + var supplementalFont45 = new SupplementalFont { Script = "Yiii", Typeface = "Microsoft Yi Baiti" }; + var supplementalFont46 = new SupplementalFont { Script = "Tibt", Typeface = "Microsoft Himalaya" }; + var supplementalFont47 = new SupplementalFont { Script = "Thaa", Typeface = "MV Boli" }; + var supplementalFont48 = new SupplementalFont { Script = "Deva", Typeface = "Mangal" }; + var supplementalFont49 = new SupplementalFont { Script = "Telu", Typeface = "Gautami" }; + var supplementalFont50 = new SupplementalFont { Script = "Taml", Typeface = "Latha" }; + var supplementalFont51 = new SupplementalFont { Script = "Syrc", Typeface = "Estrangelo Edessa" }; + var supplementalFont52 = new SupplementalFont { Script = "Orya", Typeface = "Kalinga" }; + var supplementalFont53 = new SupplementalFont { Script = "Mlym", Typeface = "Kartika" }; + var supplementalFont54 = new SupplementalFont { Script = "Laoo", Typeface = "DokChampa" }; + var supplementalFont55 = new SupplementalFont { Script = "Sinh", Typeface = "Iskoola Pota" }; + var supplementalFont56 = new SupplementalFont { Script = "Mong", Typeface = "Mongolian Baiti" }; + var supplementalFont57 = new SupplementalFont { Script = "Viet", Typeface = "Arial" }; + var supplementalFont58 = new SupplementalFont { Script = "Uigh", Typeface = "Microsoft Uighur" }; minorFont1.AppendChild(latinFont2); minorFont1.AppendChild(eastAsianFont2); @@ -1262,46 +1293,46 @@ fontScheme2.AppendChild(majorFont1); fontScheme2.AppendChild(minorFont1); - var formatScheme1 = new FormatScheme {Name = "Office"}; + var formatScheme1 = new FormatScheme { Name = "Office" }; var fillStyleList1 = new FillStyleList(); var solidFill1 = new SolidFill(); - var schemeColor1 = new SchemeColor {Val = SchemeColorValues.PhColor}; + var schemeColor1 = new SchemeColor { Val = SchemeColorValues.PhColor }; solidFill1.AppendChild(schemeColor1); - var gradientFill1 = new GradientFill {RotateWithShape = true}; + var gradientFill1 = new GradientFill { RotateWithShape = true }; var gradientStopList1 = new GradientStopList(); - var gradientStop1 = new GradientStop {Position = 0}; + var gradientStop1 = new GradientStop { Position = 0 }; - var schemeColor2 = new SchemeColor {Val = SchemeColorValues.PhColor}; - var tint1 = new Tint {Val = 50000}; - var saturationModulation1 = new SaturationModulation {Val = 300000}; + var schemeColor2 = new SchemeColor { Val = SchemeColorValues.PhColor }; + var tint1 = new Tint { Val = 50000 }; + var saturationModulation1 = new SaturationModulation { Val = 300000 }; schemeColor2.AppendChild(tint1); schemeColor2.AppendChild(saturationModulation1); gradientStop1.AppendChild(schemeColor2); - var gradientStop2 = new GradientStop {Position = 35000}; + var gradientStop2 = new GradientStop { Position = 35000 }; - var schemeColor3 = new SchemeColor {Val = SchemeColorValues.PhColor}; - var tint2 = new Tint {Val = 37000}; - var saturationModulation2 = new SaturationModulation {Val = 300000}; + var schemeColor3 = new SchemeColor { Val = SchemeColorValues.PhColor }; + var tint2 = new Tint { Val = 37000 }; + var saturationModulation2 = new SaturationModulation { Val = 300000 }; schemeColor3.AppendChild(tint2); schemeColor3.AppendChild(saturationModulation2); gradientStop2.AppendChild(schemeColor3); - var gradientStop3 = new GradientStop {Position = 100000}; + var gradientStop3 = new GradientStop { Position = 100000 }; - var schemeColor4 = new SchemeColor {Val = SchemeColorValues.PhColor}; - var tint3 = new Tint {Val = 15000}; - var saturationModulation3 = new SaturationModulation {Val = 350000}; + var schemeColor4 = new SchemeColor { Val = SchemeColorValues.PhColor }; + var tint3 = new Tint { Val = 15000 }; + var saturationModulation3 = new SaturationModulation { Val = 350000 }; schemeColor4.AppendChild(tint3); schemeColor4.AppendChild(saturationModulation3); @@ -1311,42 +1342,42 @@ gradientStopList1.AppendChild(gradientStop1); gradientStopList1.AppendChild(gradientStop2); gradientStopList1.AppendChild(gradientStop3); - var linearGradientFill1 = new LinearGradientFill {Angle = 16200000, Scaled = true}; + var linearGradientFill1 = new LinearGradientFill { Angle = 16200000, Scaled = true }; gradientFill1.AppendChild(gradientStopList1); gradientFill1.AppendChild(linearGradientFill1); - var gradientFill2 = new GradientFill {RotateWithShape = true}; + var gradientFill2 = new GradientFill { RotateWithShape = true }; var gradientStopList2 = new GradientStopList(); - var gradientStop4 = new GradientStop {Position = 0}; + var gradientStop4 = new GradientStop { Position = 0 }; - var schemeColor5 = new SchemeColor {Val = SchemeColorValues.PhColor}; - var shade1 = new Shade {Val = 51000}; - var saturationModulation4 = new SaturationModulation {Val = 130000}; + var schemeColor5 = new SchemeColor { Val = SchemeColorValues.PhColor }; + var shade1 = new Shade { Val = 51000 }; + var saturationModulation4 = new SaturationModulation { Val = 130000 }; schemeColor5.AppendChild(shade1); schemeColor5.AppendChild(saturationModulation4); gradientStop4.AppendChild(schemeColor5); - var gradientStop5 = new GradientStop {Position = 80000}; + var gradientStop5 = new GradientStop { Position = 80000 }; - var schemeColor6 = new SchemeColor {Val = SchemeColorValues.PhColor}; - var shade2 = new Shade {Val = 93000}; - var saturationModulation5 = new SaturationModulation {Val = 130000}; + var schemeColor6 = new SchemeColor { Val = SchemeColorValues.PhColor }; + var shade2 = new Shade { Val = 93000 }; + var saturationModulation5 = new SaturationModulation { Val = 130000 }; schemeColor6.AppendChild(shade2); schemeColor6.AppendChild(saturationModulation5); gradientStop5.AppendChild(schemeColor6); - var gradientStop6 = new GradientStop {Position = 100000}; + var gradientStop6 = new GradientStop { Position = 100000 }; - var schemeColor7 = new SchemeColor {Val = SchemeColorValues.PhColor}; - var shade3 = new Shade {Val = 94000}; - var saturationModulation6 = new SaturationModulation {Val = 135000}; + var schemeColor7 = new SchemeColor { Val = SchemeColorValues.PhColor }; + var shade3 = new Shade { Val = 94000 }; + var saturationModulation6 = new SaturationModulation { Val = 135000 }; schemeColor7.AppendChild(shade3); schemeColor7.AppendChild(saturationModulation6); @@ -1356,7 +1387,7 @@ gradientStopList2.AppendChild(gradientStop4); gradientStopList2.AppendChild(gradientStop5); gradientStopList2.AppendChild(gradientStop6); - var linearGradientFill2 = new LinearGradientFill {Angle = 16200000, Scaled = false}; + var linearGradientFill2 = new LinearGradientFill { Angle = 16200000, Scaled = false }; gradientFill2.AppendChild(gradientStopList2); gradientFill2.AppendChild(linearGradientFill2); @@ -1377,15 +1408,15 @@ var solidFill2 = new SolidFill(); - var schemeColor8 = new SchemeColor {Val = SchemeColorValues.PhColor}; - var shade4 = new Shade {Val = 95000}; - var saturationModulation7 = new SaturationModulation {Val = 105000}; + var schemeColor8 = new SchemeColor { Val = SchemeColorValues.PhColor }; + var shade4 = new Shade { Val = 95000 }; + var saturationModulation7 = new SaturationModulation { Val = 105000 }; schemeColor8.AppendChild(shade4); schemeColor8.AppendChild(saturationModulation7); solidFill2.AppendChild(schemeColor8); - var presetDash1 = new PresetDash {Val = PresetLineDashValues.Solid}; + var presetDash1 = new PresetDash { Val = PresetLineDashValues.Solid }; outline1.AppendChild(solidFill2); outline1.AppendChild(presetDash1); @@ -1399,10 +1430,10 @@ }; var solidFill3 = new SolidFill(); - var schemeColor9 = new SchemeColor {Val = SchemeColorValues.PhColor}; + var schemeColor9 = new SchemeColor { Val = SchemeColorValues.PhColor }; solidFill3.AppendChild(schemeColor9); - var presetDash2 = new PresetDash {Val = PresetLineDashValues.Solid}; + var presetDash2 = new PresetDash { Val = PresetLineDashValues.Solid }; outline2.AppendChild(solidFill3); outline2.AppendChild(presetDash2); @@ -1416,10 +1447,10 @@ }; var solidFill4 = new SolidFill(); - var schemeColor10 = new SchemeColor {Val = SchemeColorValues.PhColor}; + var schemeColor10 = new SchemeColor { Val = SchemeColorValues.PhColor }; solidFill4.AppendChild(schemeColor10); - var presetDash3 = new PresetDash {Val = PresetLineDashValues.Solid}; + var presetDash3 = new PresetDash { Val = PresetLineDashValues.Solid }; outline3.AppendChild(solidFill4); outline3.AppendChild(presetDash3); @@ -1442,8 +1473,8 @@ RotateWithShape = false }; - var rgbColorModelHex11 = new RgbColorModelHex {Val = "000000"}; - var alpha1 = new Alpha {Val = 38000}; + var rgbColorModelHex11 = new RgbColorModelHex { Val = "000000" }; + var alpha1 = new Alpha { Val = 38000 }; rgbColorModelHex11.AppendChild(alpha1); @@ -1465,8 +1496,8 @@ RotateWithShape = false }; - var rgbColorModelHex12 = new RgbColorModelHex {Val = "000000"}; - var alpha2 = new Alpha {Val = 35000}; + var rgbColorModelHex12 = new RgbColorModelHex { Val = "000000" }; + var alpha2 = new Alpha { Val = 35000 }; rgbColorModelHex12.AppendChild(alpha2); @@ -1488,8 +1519,8 @@ RotateWithShape = false }; - var rgbColorModelHex13 = new RgbColorModelHex {Val = "000000"}; - var alpha3 = new Alpha {Val = 35000}; + var rgbColorModelHex13 = new RgbColorModelHex { Val = "000000" }; + var alpha3 = new Alpha { Val = 35000 }; rgbColorModelHex13.AppendChild(alpha3); @@ -1499,13 +1530,13 @@ var scene3DType1 = new Scene3DType(); - var camera1 = new Camera {Preset = PresetCameraValues.OrthographicFront}; - var rotation1 = new Rotation {Latitude = 0, Longitude = 0, Revolution = 0}; + var camera1 = new Camera { Preset = PresetCameraValues.OrthographicFront }; + var rotation1 = new Rotation { Latitude = 0, Longitude = 0, Revolution = 0 }; camera1.AppendChild(rotation1); - var lightRig1 = new LightRig {Rig = LightRigValues.ThreePoints, Direction = LightRigDirectionValues.Top}; - var rotation2 = new Rotation {Latitude = 0, Longitude = 0, Revolution = 1200000}; + var lightRig1 = new LightRig { Rig = LightRigValues.ThreePoints, Direction = LightRigDirectionValues.Top }; + var rotation2 = new Rotation { Latitude = 0, Longitude = 0, Revolution = 1200000 }; lightRig1.AppendChild(rotation2); @@ -1513,7 +1544,7 @@ scene3DType1.AppendChild(lightRig1); var shape3DType1 = new Shape3DType(); - var bevelTop1 = new BevelTop {Width = 63500L, Height = 25400L}; + var bevelTop1 = new BevelTop { Width = 63500L, Height = 25400L }; shape3DType1.AppendChild(bevelTop1); @@ -1528,31 +1559,31 @@ var backgroundFillStyleList1 = new BackgroundFillStyleList(); var solidFill5 = new SolidFill(); - var schemeColor11 = new SchemeColor {Val = SchemeColorValues.PhColor}; + var schemeColor11 = new SchemeColor { Val = SchemeColorValues.PhColor }; solidFill5.AppendChild(schemeColor11); - var gradientFill3 = new GradientFill {RotateWithShape = true}; + var gradientFill3 = new GradientFill { RotateWithShape = true }; var gradientStopList3 = new GradientStopList(); - var gradientStop7 = new GradientStop {Position = 0}; + var gradientStop7 = new GradientStop { Position = 0 }; - var schemeColor12 = new SchemeColor {Val = SchemeColorValues.PhColor}; - var tint4 = new Tint {Val = 40000}; - var saturationModulation8 = new SaturationModulation {Val = 350000}; + var schemeColor12 = new SchemeColor { Val = SchemeColorValues.PhColor }; + var tint4 = new Tint { Val = 40000 }; + var saturationModulation8 = new SaturationModulation { Val = 350000 }; schemeColor12.AppendChild(tint4); schemeColor12.AppendChild(saturationModulation8); gradientStop7.AppendChild(schemeColor12); - var gradientStop8 = new GradientStop {Position = 40000}; + var gradientStop8 = new GradientStop { Position = 40000 }; - var schemeColor13 = new SchemeColor {Val = SchemeColorValues.PhColor}; - var tint5 = new Tint {Val = 45000}; - var shade5 = new Shade {Val = 99000}; - var saturationModulation9 = new SaturationModulation {Val = 350000}; + var schemeColor13 = new SchemeColor { Val = SchemeColorValues.PhColor }; + var tint5 = new Tint { Val = 45000 }; + var shade5 = new Shade { Val = 99000 }; + var saturationModulation9 = new SaturationModulation { Val = 350000 }; schemeColor13.AppendChild(tint5); schemeColor13.AppendChild(shade5); @@ -1560,11 +1591,11 @@ gradientStop8.AppendChild(schemeColor13); - var gradientStop9 = new GradientStop {Position = 100000}; + var gradientStop9 = new GradientStop { Position = 100000 }; - var schemeColor14 = new SchemeColor {Val = SchemeColorValues.PhColor}; - var shade6 = new Shade {Val = 20000}; - var saturationModulation10 = new SaturationModulation {Val = 255000}; + var schemeColor14 = new SchemeColor { Val = SchemeColorValues.PhColor }; + var shade6 = new Shade { Val = 20000 }; + var saturationModulation10 = new SaturationModulation { Val = 255000 }; schemeColor14.AppendChild(shade6); schemeColor14.AppendChild(saturationModulation10); @@ -1575,34 +1606,34 @@ gradientStopList3.AppendChild(gradientStop8); gradientStopList3.AppendChild(gradientStop9); - var pathGradientFill1 = new PathGradientFill {Path = PathShadeValues.Circle}; - var fillToRectangle1 = new FillToRectangle {Left = 50000, Top = -80000, Right = 50000, Bottom = 180000}; + var pathGradientFill1 = new PathGradientFill { Path = PathShadeValues.Circle }; + var fillToRectangle1 = new FillToRectangle { Left = 50000, Top = -80000, Right = 50000, Bottom = 180000 }; pathGradientFill1.AppendChild(fillToRectangle1); gradientFill3.AppendChild(gradientStopList3); gradientFill3.AppendChild(pathGradientFill1); - var gradientFill4 = new GradientFill {RotateWithShape = true}; + var gradientFill4 = new GradientFill { RotateWithShape = true }; var gradientStopList4 = new GradientStopList(); - var gradientStop10 = new GradientStop {Position = 0}; + var gradientStop10 = new GradientStop { Position = 0 }; - var schemeColor15 = new SchemeColor {Val = SchemeColorValues.PhColor}; - var tint6 = new Tint {Val = 80000}; - var saturationModulation11 = new SaturationModulation {Val = 300000}; + var schemeColor15 = new SchemeColor { Val = SchemeColorValues.PhColor }; + var tint6 = new Tint { Val = 80000 }; + var saturationModulation11 = new SaturationModulation { Val = 300000 }; schemeColor15.AppendChild(tint6); schemeColor15.AppendChild(saturationModulation11); gradientStop10.AppendChild(schemeColor15); - var gradientStop11 = new GradientStop {Position = 100000}; + var gradientStop11 = new GradientStop { Position = 100000 }; - var schemeColor16 = new SchemeColor {Val = SchemeColorValues.PhColor}; - var shade7 = new Shade {Val = 30000}; - var saturationModulation12 = new SaturationModulation {Val = 200000}; + var schemeColor16 = new SchemeColor { Val = SchemeColorValues.PhColor }; + var shade7 = new Shade { Val = 30000 }; + var saturationModulation12 = new SaturationModulation { Val = 200000 }; schemeColor16.AppendChild(shade7); schemeColor16.AppendChild(saturationModulation12); @@ -1612,8 +1643,8 @@ gradientStopList4.AppendChild(gradientStop10); gradientStopList4.AppendChild(gradientStop11); - var pathGradientFill2 = new PathGradientFill {Path = PathShadeValues.Circle}; - var fillToRectangle2 = new FillToRectangle {Left = 50000, Top = 50000, Right = 50000, Bottom = 50000}; + var pathGradientFill2 = new PathGradientFill { Path = PathShadeValues.Circle }; + var fillToRectangle2 = new FillToRectangle { Left = 50000, Top = 50000, Right = 50000, Bottom = 50000 }; pathGradientFill2.AppendChild(fillToRectangle2); @@ -1659,7 +1690,7 @@ }; if (p.Type == XLCustomPropertyType.Text) { - var vTlpwstr1 = new VTLPWSTR {Text = p.GetValue()}; + var vTlpwstr1 = new VTLPWSTR { Text = p.GetValue() }; customDocumentProperty.AppendChild(vTlpwstr1); } else if (p.Type == XLCustomPropertyType.Date) @@ -1682,7 +1713,7 @@ } else { - var vTBool1 = new VTBool {Text = p.GetValue().ToString().ToLower()}; + var vTBool1 = new VTBool { Text = p.GetValue().ToString().ToLower() }; customDocumentProperty.AppendChild(vTBool1); } properties2.AppendChild(customDocumentProperty); @@ -1749,7 +1780,7 @@ else table.TotalsRowShown = false; - var tableColumns1 = new TableColumns {Count = (UInt32)xlTable.ColumnCount()}; + var tableColumns1 = new TableColumns { Count = (UInt32)xlTable.ColumnCount() }; UInt32 columnId = 0; foreach (var xlField in xlTable.Fields) @@ -1818,7 +1849,7 @@ PivotCaches pivotCaches; uint cacheId = 0; if (workbookPart.Workbook.PivotCaches == null) - pivotCaches = workbookPart.Workbook.AppendChild(new PivotCaches()); + pivotCaches = workbookPart.Workbook.InsertAfter(new PivotCaches(), workbookPart.Workbook.CalculationProperties); else { pivotCaches = workbookPart.Workbook.PivotCaches; @@ -1826,23 +1857,40 @@ cacheId = pivotCaches.Cast().Max(pc => pc.CacheId.Value) + 1; } - foreach (var pt in xlWorksheet.PivotTables) + foreach (var pt in xlWorksheet.PivotTables.Cast()) { // TODO: Avoid duplicate pivot caches of same source range - var ptCdp = context.RelIdGenerator.GetNext(RelType.Workbook); - var pivotTableCacheDefinitionPart = workbookPart.AddNewPart(ptCdp); + var workbookCacheRelId = pt.WorkbookCacheRelId; + PivotCache pivotCache; + PivotTableCacheDefinitionPart pivotTableCacheDefinitionPart; + if (!XLHelper.IsNullOrWhiteSpace(pt.WorkbookCacheRelId)) + { + pivotCache = pivotCaches.Cast().Single(pc => pc.Id.Value == pt.WorkbookCacheRelId); + pivotTableCacheDefinitionPart = workbookPart.GetPartById(pt.WorkbookCacheRelId) as PivotTableCacheDefinitionPart; + } + else + { + workbookCacheRelId = context.RelIdGenerator.GetNext(RelType.Workbook); + pivotCache = new PivotCache { CacheId = cacheId++, Id = workbookCacheRelId }; + pivotTableCacheDefinitionPart = workbookPart.AddNewPart(workbookCacheRelId); + } + GeneratePivotTableCacheDefinitionPartContent(pivotTableCacheDefinitionPart, pt); - var pivotCache = new PivotCache { CacheId = cacheId++, Id = ptCdp }; + if (XLHelper.IsNullOrWhiteSpace(pt.WorkbookCacheRelId)) + pivotCaches.AppendChild(pivotCache); - pivotCaches.AppendChild(pivotCache); + PivotTablePart pivotTablePart; + if (XLHelper.IsNullOrWhiteSpace(pt.RelId)) + pivotTablePart = worksheetPart.AddNewPart(context.RelIdGenerator.GetNext(RelType.Workbook)); + else + pivotTablePart = worksheetPart.GetPartById(pt.RelId) as PivotTablePart; - var pivotTablePart = - worksheetPart.AddNewPart(context.RelIdGenerator.GetNext(RelType.Workbook)); GeneratePivotTablePartContent(pivotTablePart, pt, pivotCache.CacheId, context); - pivotTablePart.AddPart(pivotTableCacheDefinitionPart, context.RelIdGenerator.GetNext(RelType.Workbook)); + if (XLHelper.IsNullOrWhiteSpace(pt.RelId)) + pivotTablePart.AddPart(pivotTableCacheDefinitionPart, context.RelIdGenerator.GetNext(RelType.Workbook)); } } @@ -1866,8 +1914,8 @@ pivotCacheDefinition.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); - var cacheSource = new CacheSource {Type = SourceValues.Worksheet}; - cacheSource.AppendChild(new WorksheetSource {Name = source.ToString()}); + var cacheSource = new CacheSource { Type = SourceValues.Worksheet }; + cacheSource.AppendChild(new WorksheetSource { Name = source.ToString() }); var cacheFields = new CacheFields(); @@ -1895,27 +1943,25 @@ if (onlyNumbers) { sharedItems = new SharedItems - {ContainsSemiMixedTypes = false, ContainsString = false, ContainsNumber = true}; + { ContainsSemiMixedTypes = false, ContainsString = false, ContainsNumber = true }; } else { - foreach (var cellValue in source.Cells().Where(cell => - cell.Address.ColumnNumber == columnNumber && - cell.Address.RowNumber > - source.FirstRow().RowNumber()).Select( - cell => cell.Value.ToString()) - .Where(cellValue => !xlpf.SharedStrings.Contains(cellValue))) + foreach (var cellValue in source.Cells() + .Where(cell => cell.Address.ColumnNumber == columnNumber && cell.Address.RowNumber > source.FirstRow().RowNumber()) + .Select(cell => cell.Value.ToString()) + .Where(cellValue => !xlpf.SharedStrings.Select(ss => ss.ToLower()).Contains(cellValue.ToLower()))) { xlpf.SharedStrings.Add(cellValue); } foreach (var li in xlpf.SharedStrings) { - sharedItems.AppendChild(new StringItem {Val = li}); + sharedItems.AppendChild(new StringItem { Val = li }); } } - var cacheField = new CacheField {Name = xlpf.SourceName}; + var cacheField = new CacheField { Name = xlpf.SourceName }; cacheField.AppendChild(sharedItems); cacheFields.AppendChild(cacheField); } @@ -1925,7 +1971,9 @@ pivotTableCacheDefinitionPart.PivotCacheDefinition = pivotCacheDefinition; - var pivotTableCacheRecordsPart = pivotTableCacheDefinitionPart.AddNewPart("rId1"); + var pivotTableCacheRecordsPart = pivotTableCacheDefinitionPart.GetPartsOfType().Any() ? + pivotTableCacheDefinitionPart.GetPartsOfType().First() : + pivotTableCacheDefinitionPart.AddNewPart("rId1"); var pivotCacheRecords = new PivotCacheRecords(); pivotCacheRecords.AddNamespaceDeclaration("r", @@ -1957,7 +2005,7 @@ ShowDataTips = GetBooleanValue(pt.ShowContextualTooltips, true), ShowMemberPropertyTips = GetBooleanValue(pt.ShowPropertiesInTooltips, true), ShowHeaders = GetBooleanValue(pt.DisplayCaptionsAndDropdowns, true), - GridDropZones = GetBooleanValue(pt.ClassicPivotTableLayout, true), + GridDropZones = GetBooleanValue(pt.ClassicPivotTableLayout, false), ShowEmptyRow = GetBooleanValue(pt.ShowEmptyItemsOnRows, true), ShowEmptyColumn = GetBooleanValue(pt.ShowEmptyItemsOnColumns, true), ShowItems = GetBooleanValue(pt.DisplayItemLabels, true), @@ -2001,40 +2049,40 @@ var columnFields = new ColumnFields(); var rowItems = new RowItems(); var columnItems = new ColumnItems(); - var pageFields = new PageFields {Count = (uint)pt.ReportFilters.Count()}; - var pivotFields = new PivotFields {Count = Convert.ToUInt32(pt.SourceRange.ColumnCount())}; + var pageFields = new PageFields { Count = (uint)pt.ReportFilters.Count() }; + var pivotFields = new PivotFields { Count = Convert.ToUInt32(pt.SourceRange.ColumnCount()) }; - foreach (var xlpf in pt.Fields.OrderBy(f => pt.RowLabels.Any(p => p.SourceName == f.SourceName) ? pt.RowLabels.IndexOf(f) : Int32.MaxValue )) + foreach (var xlpf in pt.Fields.OrderBy(f => pt.RowLabels.Any(p => p.SourceName == f.SourceName) ? pt.RowLabels.IndexOf(f) : Int32.MaxValue)) { if (pt.RowLabels.Any(p => p.SourceName == xlpf.SourceName)) { - var f = new Field {Index = pt.Fields.IndexOf(xlpf)}; + var f = new Field { Index = pt.Fields.IndexOf(xlpf) }; rowFields.AppendChild(f); for (var i = 0; i < xlpf.SharedStrings.Count; i++) { var rowItem = new RowItem(); - rowItem.AppendChild(new MemberPropertyIndex {Val = i}); + rowItem.AppendChild(new MemberPropertyIndex { Val = i }); rowItems.AppendChild(rowItem); } - var rowItemTotal = new RowItem {ItemType = ItemValues.Grand}; + var rowItemTotal = new RowItem { ItemType = ItemValues.Grand }; rowItemTotal.AppendChild(new MemberPropertyIndex()); rowItems.AppendChild(rowItemTotal); } else if (pt.ColumnLabels.Any(p => p.SourceName == xlpf.SourceName)) { - var f = new Field {Index = pt.Fields.IndexOf(xlpf)}; + var f = new Field { Index = pt.Fields.IndexOf(xlpf) }; columnFields.AppendChild(f); for (var i = 0; i < xlpf.SharedStrings.Count; i++) { var rowItem = new RowItem(); - rowItem.AppendChild(new MemberPropertyIndex {Val = i}); + rowItem.AppendChild(new MemberPropertyIndex { Val = i }); columnItems.AppendChild(rowItem); } - var rowItemTotal = new RowItem {ItemType = ItemValues.Grand}; + var rowItemTotal = new RowItem { ItemType = ItemValues.Grand }; rowItemTotal.AppendChild(new MemberPropertyIndex()); columnItems.AppendChild(rowItemTotal); } @@ -2054,14 +2102,17 @@ foreach (var xlpf in pt.Fields) { - var pf = new PivotField {ShowAll = false, Name = xlpf.CustomName}; + IXLPivotField labelField = null; + var pf = new PivotField { ShowAll = false, Name = xlpf.CustomName }; if (pt.RowLabels.Any(p => p.SourceName == xlpf.SourceName)) { + labelField = pt.RowLabels.Single(p => p.SourceName == xlpf.SourceName); pf.Axis = PivotTableAxisValues.AxisRow; } else if (pt.ColumnLabels.Any(p => p.SourceName == xlpf.SourceName)) { + labelField = pt.ColumnLabels.Single(p => p.SourceName == xlpf.SourceName); pf.Axis = PivotTableAxisValues.AxisColumn; } else if (pt.ReportFilters.Any(p => p.SourceName == xlpf.SourceName)) @@ -2069,12 +2120,11 @@ location.ColumnsPerPage = 1; location.RowPageCount = 1; pf.Axis = PivotTableAxisValues.AxisPage; - pageFields.AppendChild(new PageField {Hierarchy = -1, Field = pt.Fields.IndexOf(xlpf)}); + pageFields.AppendChild(new PageField { Hierarchy = -1, Field = pt.Fields.IndexOf(xlpf) }); } - else if (pt.Values.Any(p => p.SourceName == xlpf.SourceName)) - { + + if (pt.Values.Any(p => p.SourceName == xlpf.SourceName)) pf.DataField = true; - } var fieldItems = new Items(); @@ -2082,7 +2132,10 @@ { for (uint i = 0; i < xlpf.SharedStrings.Count; i++) { - fieldItems.AppendChild(new Item {Index = i}); + var item = new Item { Index = i }; + if (labelField != null && labelField.Collapsed) + item.HideDetails = BooleanValue.FromBoolean(false); + fieldItems.AppendChild(item); } } @@ -2143,7 +2196,7 @@ } else { - fieldItems.AppendChild(new Item {ItemType = ItemValues.Default}); + fieldItems.AppendChild(new Item { ItemType = ItemValues.Default }); } fieldItems.Count = Convert.ToUInt32(fieldItems.Count()); @@ -2175,7 +2228,7 @@ rowItem.Index = Convert.ToUInt32(i); rowItem.AppendChild(new MemberPropertyIndex() { Val = i }); columnItems.AppendChild(rowItem); - } + } } if (columnFields.Any()) @@ -2233,7 +2286,7 @@ if (items.Any(i => i.Equals(value.BaseItem))) df.BaseItem = Convert.ToUInt32(items.IndexOf(value.BaseItem)); - } + } } else { @@ -2255,7 +2308,7 @@ pivotTableDefinition.AppendChild(new PivotTableStyle { - Name = Enum.GetName(typeof (XLPivotTableTheme), pt.Theme), + Name = Enum.GetName(typeof(XLPivotTableTheme), pt.Theme), ShowRowHeaders = pt.ShowRowHeaders, ShowColumnHeaders = pt.ShowColumnHeaders, ShowRowStripes = pt.ShowRowStripes, @@ -2267,12 +2320,12 @@ var pivotTableDefinitionExtensionList = new PivotTableDefinitionExtensionList(); var pivotTableDefinitionExtension = new PivotTableDefinitionExtension - {Uri = "{962EF5D1-5CA2-4c93-8EF4-DBF5C05439D2}"}; + { Uri = "{962EF5D1-5CA2-4c93-8EF4-DBF5C05439D2}" }; pivotTableDefinitionExtension.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"); var pivotTableDefinition2 = new DocumentFormat.OpenXml.Office2010.Excel.PivotTableDefinition - {EnableEdit = pt.EnableCellEditing, HideValuesRow = !pt.ShowValuesRow}; + { EnableEdit = pt.EnableCellEditing, HideValuesRow = !pt.ShowValuesRow }; pivotTableDefinition2.AddNamespaceDeclaration("xm", "http://schemas.microsoft.com/office/excel/2006/main"); pivotTableDefinitionExtension.AppendChild(pivotTableDefinition2); @@ -2294,7 +2347,7 @@ var authorsDict = new Dictionary(); foreach (var c in xlWorksheet.Internals.CellsCollection.GetCells(c => c.HasComment)) { - var comment = new Comment {Reference = c.Address.ToStringRelative()}; + var comment = new Comment { Reference = c.Address.ToStringRelative() }; var authorName = c.Comment.Author; Int32 authorId; @@ -2316,7 +2369,7 @@ } var authors = new Authors(); - foreach (var author in authorsDict.Select(a => new Author {Text = a.Key})) + foreach (var author in authorsDict.Select(a => new Author { Text = a.Key })) { authors.Append(author); } @@ -2340,8 +2393,8 @@ const string shapeTypeId = "_x0000_t202"; // arbitrary, assigned by office new Vml.Shapetype( - new Vml.Stroke {JoinStyle = Vml.StrokeJoinStyleValues.Miter}, - new Vml.Path {AllowGradientShape = true, ConnectionPointType = ConnectValues.Rectangle} + new Vml.Stroke { JoinStyle = Vml.StrokeJoinStyleValues.Miter }, + new Vml.Path { AllowGradientShape = true, ConnectionPointType = ConnectValues.Rectangle } ) { Id = shapeTypeId, @@ -2381,7 +2434,7 @@ // Unique per cell (workbook?), e.g.: "_x0000_s1026" var anchor = GetAnchor(c); var textBox = GetTextBox(c.Comment.Style); - var fill = new Vml.Fill {Color2 = "#" + c.Comment.Style.ColorsAndLines.FillColor.Color.ToHex().Substring(2)}; + var fill = new Vml.Fill { Color2 = "#" + c.Comment.Style.ColorsAndLines.FillColor.Color.ToHex().Substring(2) }; if (c.Comment.Style.ColorsAndLines.FillTransparency < 1) fill.Opacity = Math.Round(Convert.ToDouble(c.Comment.Style.ColorsAndLines.FillTransparency), 2).ToString( @@ -2390,8 +2443,8 @@ var shape = new Vml.Shape( fill, stroke, - new Vml.Shadow {On = true, Color = "black", Obscured = true}, - new Vml.Path {ConnectionPointType = ConnectValues.None}, + new Vml.Shadow { On = true, Color = "black", Obscured = true }, + new Vml.Path { ConnectionPointType = ConnectValues.None }, textBox, new ClientData( new MoveWithCells(c.Comment.Style.Properties.Positioning == XLDrawingAnchor.Absolute @@ -2404,12 +2457,13 @@ new HorizontalTextAlignment(c.Comment.Style.Alignment.Horizontal.ToString().ToCamel()), new Vml.Spreadsheet.VerticalTextAlignment(c.Comment.Style.Alignment.Vertical.ToString().ToCamel()), new AutoFill("False"), - new CommentRowTarget {Text = (rowNumber - 1).ToString()}, - new CommentColumnTarget {Text = (columnNumber - 1).ToString()}, + new CommentRowTarget { Text = (rowNumber - 1).ToString() }, + new CommentColumnTarget { Text = (columnNumber - 1).ToString() }, new Locked(c.Comment.Style.Protection.Locked ? "True" : "False"), new LockText(c.Comment.Style.Protection.LockText ? "True" : "False"), new Visible(c.Comment.Visible ? "True" : "False") - ) {ObjectType = ObjectValues.Note} + ) + { ObjectType = ObjectValues.Note } ) { Id = shapeId, @@ -2467,7 +2521,7 @@ } if (a.AutomaticSize) sb.Append("mso-fit-shape-to-text:t;"); - var retVal = new Vml.TextBox {Style = sb.ToString()}; + var retVal = new Vml.TextBox { Style = sb.ToString() }; var dm = ds.Margins; if (!dm.Automatic) retVal.Inset = String.Format("{0}in,{1}in,{2}in,{3}in", @@ -2550,7 +2604,7 @@ var defaultStyle = new XLStyle(null, DefaultStyle); var defaultStyleId = GetStyleId(defaultStyle); if (!context.SharedFonts.ContainsKey(defaultStyle.Font)) - context.SharedFonts.Add(defaultStyle.Font, new FontInfo {FontId = 0, Font = defaultStyle.Font as XLFont}); + context.SharedFonts.Add(defaultStyle.Font, new FontInfo { FontId = 0, Font = defaultStyle.Font as XLFont }); var sharedFills = new Dictionary {{defaultStyle.Fill, new FillInfo {FillId = 2, Fill = defaultStyle.Fill as XLFill}}}; @@ -2577,17 +2631,20 @@ if (workbookStylesPart.Stylesheet.CellStyles == null) workbookStylesPart.Stylesheet.CellStyles = new CellStyles(); + // To determine the default workbook style, we look for the style with builtInId = 0 (I hope that is the correct approach) UInt32 defaultFormatId; - if (workbookStylesPart.Stylesheet.CellStyles.Elements().Any(c => c.Name == "Normal")) + if (workbookStylesPart.Stylesheet.CellStyles.Elements().Any(c => c.BuiltinId != null && c.BuiltinId.HasValue && c.BuiltinId.Value == 0)) { - defaultFormatId = - workbookStylesPart.Stylesheet.CellStyles.Elements().Single(c => c.Name == "Normal").FormatId.Value; + // Possible to have duplicate default cell styles - occurs when file gets saved under different cultures. + // We prefer the style that is named Normal + var normalCellStyles = workbookStylesPart.Stylesheet.CellStyles.Elements() + .Where(c => c.BuiltinId != null && c.BuiltinId.HasValue && c.BuiltinId.Value == 0) + .OrderBy(c => c.Name != null && c.Name.HasValue && c.Name.Value == "Normal"); + + defaultFormatId = normalCellStyles.Last().FormatId.Value; } else if (workbookStylesPart.Stylesheet.CellStyles.Elements().Any()) - { - defaultFormatId = - workbookStylesPart.Stylesheet.CellStyles.Elements().Max(c => c.FormatId.Value) + 1; - } + defaultFormatId = workbookStylesPart.Stylesheet.CellStyles.Elements().Max(c => c.FormatId.Value) + 1; else defaultFormatId = 0; @@ -2642,7 +2699,7 @@ sharedNumberFormats.Add(numberFormat, new NumberFormatInfo { - NumberFormatId = numberFormatCount + 164, + NumberFormatId = XLConstants.NumberOfBuiltInStyles + numberFormatCount, NumberFormat = numberFormat }); numberFormatCount++; @@ -2652,14 +2709,14 @@ { if (!context.SharedFonts.ContainsKey(xlStyle.Font)) context.SharedFonts.Add(xlStyle.Font, - new FontInfo {FontId = fontCount++, Font = xlStyle.Font as XLFont}); + new FontInfo { FontId = fontCount++, Font = xlStyle.Font as XLFont }); if (!sharedFills.ContainsKey(xlStyle.Fill)) - sharedFills.Add(xlStyle.Fill, new FillInfo {FillId = fillCount++, Fill = xlStyle.Fill as XLFill}); + sharedFills.Add(xlStyle.Fill, new FillInfo { FillId = fillCount++, Fill = xlStyle.Fill as XLFill }); if (!sharedBorders.ContainsKey(xlStyle.Border)) sharedBorders.Add(xlStyle.Border, - new BorderInfo {BorderId = borderCount++, Border = xlStyle.Border as XLBorder}); + new BorderInfo { BorderId = borderCount++, Border = xlStyle.Border as XLBorder }); if (xlStyle.NumberFormat.NumberFormatId != -1 || sharedNumberFormats.ContainsKey(xlStyle.NumberFormat)) @@ -2668,7 +2725,7 @@ sharedNumberFormats.Add(xlStyle.NumberFormat, new NumberFormatInfo { - NumberFormatId = numberFormatCount + 164, + NumberFormatId = XLConstants.NumberOfBuiltInStyles + numberFormatCount, NumberFormat = xlStyle.NumberFormat }); numberFormatCount++; @@ -2708,13 +2765,9 @@ ResolveCellStyleFormats(workbookStylesPart, context); ResolveRest(workbookStylesPart, context); - if (workbookStylesPart.Stylesheet.CellStyles.Elements().All(c => c.Name != "Normal")) - { - //var defaultFormatId = context.SharedStyles.Values.Where(s => s.Style.Equals(DefaultStyle)).Single().StyleId; + if (!workbookStylesPart.Stylesheet.CellStyles.Elements().Any(c => c.BuiltinId != null && c.BuiltinId.HasValue && c.BuiltinId.Value == 0U)) + workbookStylesPart.Stylesheet.CellStyles.AppendChild(new CellStyle { Name = "Normal", FormatId = defaultFormatId, BuiltinId = 0U }); - var cellStyle1 = new CellStyle {Name = "Normal", FormatId = defaultFormatId, BuiltinId = 0U}; - workbookStylesPart.Stylesheet.CellStyles.AppendChild(cellStyle1); - } workbookStylesPart.Stylesheet.CellStyles.Count = (UInt32)workbookStylesPart.Stylesheet.CellStyles.Count(); var newSharedStyles = new Dictionary(); @@ -2785,18 +2838,18 @@ SaveContext context) { var differentialFormat = new DifferentialFormat(); - differentialFormat.Append(GetNewFont(new FontInfo {Font = cf.Style.Font as XLFont}, false)); + differentialFormat.Append(GetNewFont(new FontInfo { Font = cf.Style.Font as XLFont }, false)); if (!XLHelper.IsNullOrWhiteSpace(cf.Style.NumberFormat.Format)) { var numberFormat = new NumberingFormat { - NumberFormatId = (UInt32)(differentialFormats.Count() + 164), + NumberFormatId = (UInt32)(XLConstants.NumberOfBuiltInStyles + differentialFormats.Count()), FormatCode = cf.Style.NumberFormat.Format }; differentialFormat.Append(numberFormat); } - differentialFormat.Append(GetNewFill(new FillInfo {Fill = cf.Style.Fill as XLFill}, false)); - differentialFormat.Append(GetNewBorder(new BorderInfo {Border = cf.Style.Border as XLBorder}, false)); + differentialFormat.Append(GetNewFill(new FillInfo { Fill = cf.Style.Fill as XLFill }, false)); + differentialFormat.Append(GetNewBorder(new BorderInfo { Border = cf.Style.Border as XLBorder }, false)); differentialFormats.Append(differentialFormat); @@ -2994,7 +3047,7 @@ workbookStylesPart.Stylesheet.Borders.AppendChild(border); } allSharedBorders.Add(borderInfo.Border, - new BorderInfo {Border = borderInfo.Border, BorderId = (UInt32)borderId}); + new BorderInfo { Border = borderInfo.Border, BorderId = (UInt32)borderId }); } workbookStylesPart.Stylesheet.Borders.Count = (UInt32)workbookStylesPart.Stylesheet.Borders.Count(); return allSharedBorders; @@ -3011,7 +3064,7 @@ if (borderInfo.Border.LeftBorderModified || borderInfo.Border.LeftBorderColorModified || ignoreMod) { - var leftBorder = new LeftBorder {Style = borderInfo.Border.LeftBorder.ToOpenXml()}; + var leftBorder = new LeftBorder { Style = borderInfo.Border.LeftBorder.ToOpenXml() }; if (borderInfo.Border.LeftBorderColorModified || ignoreMod) { var leftBorderColor = GetNewColor(borderInfo.Border.LeftBorderColor); @@ -3022,7 +3075,7 @@ if (borderInfo.Border.RightBorderModified || borderInfo.Border.RightBorderColorModified || ignoreMod) { - var rightBorder = new RightBorder {Style = borderInfo.Border.RightBorder.ToOpenXml()}; + var rightBorder = new RightBorder { Style = borderInfo.Border.RightBorder.ToOpenXml() }; if (borderInfo.Border.RightBorderColorModified || ignoreMod) { var rightBorderColor = GetNewColor(borderInfo.Border.RightBorderColor); @@ -3033,7 +3086,7 @@ if (borderInfo.Border.TopBorderModified || borderInfo.Border.TopBorderColorModified || ignoreMod) { - var topBorder = new TopBorder {Style = borderInfo.Border.TopBorder.ToOpenXml()}; + var topBorder = new TopBorder { Style = borderInfo.Border.TopBorder.ToOpenXml() }; if (borderInfo.Border.TopBorderColorModified || ignoreMod) { var topBorderColor = GetNewColor(borderInfo.Border.TopBorderColor); @@ -3044,7 +3097,7 @@ if (borderInfo.Border.BottomBorderModified || borderInfo.Border.BottomBorderColorModified || ignoreMod) { - var bottomBorder = new BottomBorder {Style = borderInfo.Border.BottomBorder.ToOpenXml()}; + var bottomBorder = new BottomBorder { Style = borderInfo.Border.BottomBorder.ToOpenXml() }; if (borderInfo.Border.BottomBorderColorModified || ignoreMod) { var bottomBorderColor = GetNewColor(borderInfo.Border.BottomBorderColor); @@ -3055,7 +3108,7 @@ if (borderInfo.Border.DiagonalBorderModified || borderInfo.Border.DiagonalBorderColorModified || ignoreMod) { - var DiagonalBorder = new DiagonalBorder {Style = borderInfo.Border.DiagonalBorder.ToOpenXml()}; + var DiagonalBorder = new DiagonalBorder { Style = borderInfo.Border.DiagonalBorder.ToOpenXml() }; if (borderInfo.Border.DiagonalBorderColorModified || ignoreMod) { var DiagonalBorderColor = GetNewColor(borderInfo.Border.DiagonalBorderColor); @@ -3145,7 +3198,7 @@ var fill = GetNewFill(fillInfo); workbookStylesPart.Stylesheet.Fills.AppendChild(fill); } - allSharedFills.Add(fillInfo.Fill, new FillInfo {Fill = fillInfo.Fill, FillId = (UInt32)fillId}); + allSharedFills.Add(fillInfo.Fill, new FillInfo { Fill = fillInfo.Fill, FillId = (UInt32)fillId }); } workbookStylesPart.Stylesheet.Fills.Count = (UInt32)workbookStylesPart.Stylesheet.Fills.Count(); @@ -3161,7 +3214,7 @@ )) return; var fill1 = new Fill(); - var patternFill1 = new PatternFill {PatternType = patternValues}; + var patternFill1 = new PatternFill { PatternType = patternValues }; fill1.AppendChild(patternFill1); fills.AppendChild(fill1); } @@ -3254,7 +3307,7 @@ var font = GetNewFont(fontInfo); workbookStylesPart.Stylesheet.Fonts.AppendChild(font); } - newFonts.Add(fontInfo.Font, new FontInfo {Font = fontInfo.Font, FontId = (UInt32)fontId}); + newFonts.Add(fontInfo.Font, new FontInfo { Font = fontInfo.Font, FontId = (UInt32)fontId }); } context.SharedFonts.Clear(); foreach (var kp in newFonts) @@ -3270,25 +3323,25 @@ var italic = (fontInfo.Font.ItalicModified || ignoreMod) && fontInfo.Font.Italic ? new Italic() : null; var underline = (fontInfo.Font.UnderlineModified || ignoreMod) && fontInfo.Font.Underline != XLFontUnderlineValues.None - ? new Underline {Val = fontInfo.Font.Underline.ToOpenXml()} + ? new Underline { Val = fontInfo.Font.Underline.ToOpenXml() } : null; var strike = (fontInfo.Font.StrikethroughModified || ignoreMod) && fontInfo.Font.Strikethrough ? new Strike() : null; var verticalAlignment = fontInfo.Font.VerticalAlignmentModified || ignoreMod - ? new VerticalTextAlignment {Val = fontInfo.Font.VerticalAlignment.ToOpenXml()} + ? new VerticalTextAlignment { Val = fontInfo.Font.VerticalAlignment.ToOpenXml() } : null; var shadow = (fontInfo.Font.ShadowModified || ignoreMod) && fontInfo.Font.Shadow ? new Shadow() : null; var fontSize = fontInfo.Font.FontSizeModified || ignoreMod - ? new FontSize {Val = fontInfo.Font.FontSize} + ? new FontSize { Val = fontInfo.Font.FontSize } : null; var color = fontInfo.Font.FontColorModified || ignoreMod ? GetNewColor(fontInfo.Font.FontColor) : null; var fontName = fontInfo.Font.FontNameModified || ignoreMod - ? new FontName {Val = fontInfo.Font.FontName} + ? new FontName { Val = fontInfo.Font.FontName } : null; var fontFamilyNumbering = fontInfo.Font.FontFamilyNumberingModified || ignoreMod - ? new FontFamilyNumbering {Val = (Int32)fontInfo.Font.FontFamilyNumbering} + ? new FontFamilyNumbering { Val = (Int32)fontInfo.Font.FontFamilyNumbering } : null; if (bold != null) @@ -3349,7 +3402,7 @@ private bool FontsAreEqual(Font f, IXLFont xlFont) { - var nf = new XLFont {Bold = f.Bold != null, Italic = f.Italic != null}; + var nf = new XLFont { Bold = f.Bold != null, Italic = f.Italic != null }; if (f.Underline != null) { nf.Underline = f.Underline.Val != null @@ -3395,7 +3448,7 @@ var allSharedNumberFormats = new Dictionary(); foreach (var numberFormatInfo in sharedNumberFormats.Values.Where(nf => nf.NumberFormatId != defaultFormatId)) { - var numberingFormatId = 164; + var numberingFormatId = XLConstants.NumberOfBuiltInStyles + 1; var foundOne = false; foreach (NumberingFormat nf in workbookStylesPart.Stylesheet.NumberingFormats) { @@ -3489,7 +3542,7 @@ if (worksheetPart.Worksheet.SheetProperties.PageSetupProperties == null && (xlWorksheet.PageSetup.PagesTall > 0 || xlWorksheet.PageSetup.PagesWide > 0)) - worksheetPart.Worksheet.SheetProperties.PageSetupProperties = new PageSetupProperties {FitToPage = true}; + worksheetPart.Worksheet.SheetProperties.PageSetupProperties = new PageSetupProperties { FitToPage = true }; #endregion @@ -3514,7 +3567,7 @@ #region SheetViews if (worksheetPart.Worksheet.SheetDimension == null) - worksheetPart.Worksheet.SheetDimension = new SheetDimension {Reference = sheetDimensionReference}; + worksheetPart.Worksheet.SheetDimension = new SheetDimension { Reference = sheetDimensionReference }; cm.SetElement(XLWSContentManager.XLWSContents.SheetDimension, worksheetPart.Worksheet.SheetDimension); @@ -3526,7 +3579,7 @@ var sheetView = (SheetView)worksheetPart.Worksheet.SheetViews.FirstOrDefault(); if (sheetView == null) { - sheetView = new SheetView {WorkbookViewId = 0U}; + sheetView = new SheetView { WorkbookViewId = 0U }; worksheetPart.Worksheet.SheetViews.AppendChild(sheetView); } @@ -3619,16 +3672,36 @@ selection.ActiveCell = firstSelection.RangeAddress.FirstAddress.ToStringRelative(false); - var seqRef = new List {selection.ActiveCell.Value}; + var seqRef = new List { selection.ActiveCell.Value }; seqRef.AddRange(xlWorksheet.SelectedRanges .Select(range => range.RangeAddress.ToStringRelative(false))); - selection.SequenceOfReferences = new ListValue {InnerText = String.Join(" ", seqRef.Distinct().ToArray())}; + selection.SequenceOfReferences = new ListValue { InnerText = String.Join(" ", seqRef.Distinct().ToArray()) }; sheetView.Append(selection); } + if (xlWorksheet.SheetView.ZoomScale == 100) + sheetView.ZoomScale = null; + else + sheetView.ZoomScale = (UInt32)Math.Max(10, Math.Min(400, xlWorksheet.SheetView.ZoomScale)); + + if (xlWorksheet.SheetView.ZoomScaleNormal == 100) + sheetView.ZoomScaleNormal = null; + else + sheetView.ZoomScaleNormal = (UInt32)Math.Max(10, Math.Min(400, xlWorksheet.SheetView.ZoomScaleNormal)); + + if (xlWorksheet.SheetView.ZoomScalePageLayoutView == 100) + sheetView.ZoomScalePageLayoutView = null; + else + sheetView.ZoomScalePageLayoutView = (UInt32)Math.Max(10, Math.Min(400, xlWorksheet.SheetView.ZoomScalePageLayoutView)); + + if (xlWorksheet.SheetView.ZoomScaleSheetLayoutView == 100) + sheetView.ZoomScaleSheetLayoutView = null; + else + sheetView.ZoomScaleSheetLayoutView = (UInt32)Math.Max(10, Math.Min(400, xlWorksheet.SheetView.ZoomScaleSheetLayoutView)); + #endregion var maxOutlineColumn = 0; @@ -3840,7 +3913,7 @@ row = sheetDataRows[distinctRow]; else { - row = new Row {RowIndex = (UInt32)distinctRow}; + row = new Row { RowIndex = (UInt32)distinctRow }; if (noRows) { sheetData.AppendChild(row); @@ -3860,7 +3933,7 @@ } if (maxColumn > 0) - row.Spans = new ListValue {InnerText = "1:" + maxColumn.ToInvariantString()}; + row.Spans = new ListValue { InnerText = "1:" + maxColumn.ToInvariantString() }; row.Height = null; row.CustomHeight = null; @@ -4137,7 +4210,7 @@ foreach (var mergeCell in (xlWorksheet).Internals.MergedRanges.Select( m => m.RangeAddress.FirstAddress.ToString() + ":" + m.RangeAddress.LastAddress.ToString()).Select( - merged => new MergeCell {Reference = merged})) + merged => new MergeCell { Reference = merged })) mergeCells.AppendChild(mergeCell); mergeCells.Count = (UInt32)mergeCells.Count(); @@ -4176,14 +4249,14 @@ SequenceOfReferences = new ListValue { InnerText = cfGroup.RangeId } }; - foreach(var cf in cfGroup.CfList) + foreach (var cf in cfGroup.CfList) { conditionalFormatting.Append(XLCFConverters.Convert(cf, priority, context)); priority++; } worksheetPart.Worksheet.InsertAfter(conditionalFormatting, previousElement); previousElement = conditionalFormatting; - cm.SetElement(XLWSContentManager.XLWSContents.ConditionalFormatting, conditionalFormatting); + cm.SetElement(XLWSContentManager.XLWSContents.ConditionalFormatting, conditionalFormatting); } } @@ -4230,7 +4303,7 @@ ErrorStyle = dv.ErrorStyle.ToOpenXml(), Operator = dv.Operator.ToOpenXml(), SequenceOfReferences = - new ListValue {InnerText = sequence} + new ListValue { InnerText = sequence } }; dataValidations.AppendChild(dataValidation); @@ -4266,7 +4339,7 @@ if (hl.IsExternal) { var rId = context.RelIdGenerator.GetNext(RelType.Workbook); - hyperlink = new Hyperlink {Reference = hl.Cell.Address.ToString(), Id = rId}; + hyperlink = new Hyperlink { Reference = hl.Cell.Address.ToString(), Id = rId }; worksheetPart.AddHyperlinkRelationship(hl.ExternalAddress, true, rId); } else @@ -4373,15 +4446,11 @@ { pageSetup.Scale = null; - if (xlWorksheet.PageSetup.PagesWide > 0) + if (xlWorksheet.PageSetup.PagesWide >= 0 && xlWorksheet.PageSetup.PagesWide != 1) pageSetup.FitToWidth = (UInt32)xlWorksheet.PageSetup.PagesWide; - else - pageSetup.FitToWidth = 0; - if (xlWorksheet.PageSetup.PagesTall > 0) + if (xlWorksheet.PageSetup.PagesTall >= 0 && xlWorksheet.PageSetup.PagesTall != 1) pageSetup.FitToHeight = (UInt32)xlWorksheet.PageSetup.PagesTall; - else - pageSetup.FitToHeight = 0; } #endregion @@ -4522,7 +4591,7 @@ tableParts.Count = (UInt32)xlWorksheet.Tables.Count(); foreach ( var tablePart in - from XLTable xlTable in xlWorksheet.Tables select new TablePart {Id = xlTable.RelId}) + from XLTable xlTable in xlWorksheet.Tables select new TablePart { Id = xlTable.RelId }) tableParts.AppendChild(tablePart); #endregion @@ -4536,7 +4605,7 @@ if (!XLHelper.IsNullOrWhiteSpace(xlWorksheet.LegacyDrawingId)) { var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.LegacyDrawing); - worksheetPart.Worksheet.InsertAfter(new LegacyDrawing {Id = xlWorksheet.LegacyDrawingId}, + worksheetPart.Worksheet.InsertAfter(new LegacyDrawing { Id = xlWorksheet.LegacyDrawingId }, previousElement); } } @@ -4567,7 +4636,7 @@ foreach (var kp in xlAutoFilter.Filters) { - var filterColumn = new FilterColumn {ColumnId = (UInt32)kp.Key - 1}; + var filterColumn = new FilterColumn { ColumnId = (UInt32)kp.Key - 1 }; var xlFilterColumn = xlAutoFilter.Column(kp.Key); var filterType = xlFilterColumn.FilterType; if (filterType == XLFilterType.Custom) @@ -4575,7 +4644,7 @@ var customFilters = new CustomFilters(); foreach (var filter in kp.Value) { - var customFilter = new CustomFilter {Val = filter.Value.ToString()}; + var customFilter = new CustomFilter { Val = filter.Value.ToString() }; if (filter.Operator != XLFilterOperator.Equal) customFilter.Operator = filter.Operator.ToOpenXml(); @@ -4589,7 +4658,7 @@ } else if (filterType == XLFilterType.TopBottom) { - var top101 = new Top10 {Val = (double)xlFilterColumn.TopBottomValue}; + var top101 = new Top10 { Val = (double)xlFilterColumn.TopBottomValue }; if (xlFilterColumn.TopBottomType == XLTopBottomType.Percent) top101.Percent = true; if (xlFilterColumn.TopBottomPart == XLTopBottomPart.Bottom) @@ -4600,7 +4669,7 @@ else if (filterType == XLFilterType.Dynamic) { var dynamicFilter = new DynamicFilter - {Type = xlFilterColumn.DynamicType.ToOpenXml(), Val = xlFilterColumn.DynamicValue}; + { Type = xlFilterColumn.DynamicType.ToOpenXml(), Val = xlFilterColumn.DynamicValue }; filterColumn.Append(dynamicFilter); } else @@ -4608,7 +4677,7 @@ var filters = new Filters(); foreach (var filter in kp.Value) { - filters.Append(new Filter {Val = filter.Value.ToString()}); + filters.Append(new Filter { Val = filter.Value.ToString() }); } filterColumn.Append(filters); @@ -4675,8 +4744,7 @@ private static double GetColumnWidth(double columnWidth) { - var retVal = columnWidth + ColumnWidthOffset; - return retVal > 0 ? retVal : 0; + return Math.Min(255.0, Math.Max(0.0, columnWidth + ColumnWidthOffset)); } private static void UpdateColumn(Column column, Columns columns, Dictionary sheetColumnsByMin) diff --git a/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/Excel/XLWorksheet.cs index c622c30..227ee49 100644 --- a/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/Excel/XLWorksheet.cs @@ -121,7 +121,7 @@ internal Boolean ColumnWidthChanged { get; set; } public Int32 SheetId { get; set; } - public String RelId { get; set; } + internal String RelId { get; set; } public XLDataValidations DataValidations { get; private set; } public IXLCharts Charts { get; private set; } public XLSheetProtection Protection { get; private set; } @@ -1165,7 +1165,21 @@ Workbook.Worksheets.ForEach(ws => MoveNamedRangesColumns(range, columnsShifted, ws.NamedRanges)); MoveNamedRangesColumns(range, columnsShifted, Workbook.NamedRanges); ShiftConditionalFormattingColumns(range, columnsShifted); + ShiftPageBreaksColumns(range, columnsShifted); } + + private void ShiftPageBreaksColumns(XLRange range, int columnsShifted) + { + for (var i = 0; i < PageSetup.ColumnBreaks.Count; i++) + { + int br = PageSetup.ColumnBreaks[i]; + if (range.RangeAddress.FirstAddress.ColumnNumber <= br) + { + PageSetup.ColumnBreaks[i] = br + columnsShifted; + } + } + } + private void ShiftConditionalFormattingColumns(XLRange range, int columnsShifted) { Int32 firstColumn = range.RangeAddress.FirstAddress.ColumnNumber; @@ -1219,7 +1233,21 @@ Workbook.Worksheets.ForEach(ws => MoveNamedRangesRows(range, rowsShifted, ws.NamedRanges)); MoveNamedRangesRows(range, rowsShifted, Workbook.NamedRanges); ShiftConditionalFormattingRows(range, rowsShifted); + ShiftPageBreaksRows(range, rowsShifted); } + + private void ShiftPageBreaksRows(XLRange range, int rowsShifted) + { + for (var i = 0; i < PageSetup.RowBreaks.Count; i++) + { + int br = PageSetup.RowBreaks[i]; + if (range.RangeAddress.FirstAddress.RowNumber <= br) + { + PageSetup.RowBreaks[i] = br + rowsShifted; + } + } + } + private void ShiftConditionalFormattingRows(XLRange range, int rowsShifted) { Int32 firstRow = range.RangeAddress.FirstAddress.RowNumber; diff --git a/ClosedXML/Extensions.cs b/ClosedXML/Extensions.cs index 334b57b..aaca2a7 100644 --- a/ClosedXML/Extensions.cs +++ b/ClosedXML/Extensions.cs @@ -203,6 +203,20 @@ } + public static class DecimalExtensions + { + //All numbers are stored in XL files as invarient culture this is just a easy helper + public static String ToInvariantString(this Decimal value) + { + return value.ToString(CultureInfo.InvariantCulture); + } + + public static Decimal SaveRound(this Decimal value) + { + return Math.Round(value, 6); + } + } + public static class DoubleExtensions { //All numbers are stored in XL files as invarient culture this is just a easy helper @@ -310,5 +324,23 @@ return value.HasValue ? new DoubleValue(Math.Round(value, 6)) : value; } } + + public static class ObjectExtensions + { + public static bool IsNumber(this object value) + { + return value is sbyte + || value is byte + || value is short + || value is ushort + || value is int + || value is uint + || value is long + || value is ulong + || value is float + || value is double + || value is decimal; + } + } } diff --git a/ClosedXML_Examples/ClosedXML_Examples.csproj b/ClosedXML_Examples/ClosedXML_Examples.csproj index 9bdf8de..155d094 100644 --- a/ClosedXML_Examples/ClosedXML_Examples.csproj +++ b/ClosedXML_Examples/ClosedXML_Examples.csproj @@ -75,6 +75,7 @@ + diff --git a/ClosedXML_Examples/Creating/CreateFiles.cs b/ClosedXML_Examples/Creating/CreateFiles.cs index 694109c..64b2ce4 100644 --- a/ClosedXML_Examples/Creating/CreateFiles.cs +++ b/ClosedXML_Examples/Creating/CreateFiles.cs @@ -90,6 +90,7 @@ new WalkingRanges().Create(Path.Combine(path, "CellMoves.xlsx")); new AddingComments().Create(Path.Combine(path, "AddingComments.xlsx")); new PivotTables().Create(Path.Combine(path, "PivotTables.xlsx")); + new SheetViews().Create(Path.Combine(path, "SheetViews.xlsx")); } } } diff --git a/ClosedXML_Examples/Misc/AdjustToContents.cs b/ClosedXML_Examples/Misc/AdjustToContents.cs index c35304d..24c17c6 100644 --- a/ClosedXML_Examples/Misc/AdjustToContents.cs +++ b/ClosedXML_Examples/Misc/AdjustToContents.cs @@ -1,145 +1,113 @@ -using System; -using ClosedXML.Excel; - +using ClosedXML.Excel; +using System; namespace ClosedXML_Examples.Misc { public class AdjustToContents : IXLExample { - #region Variables - - // Public - - // Private - - - #endregion - - #region Properties - - // Public - - // Private - - // Override - - - #endregion - - #region Events - - // Public - - // Private - - // Override - - - #endregion - - #region Methods - // Public public void Create(String filePath) { - var wb = new XLWorkbook(); - var ws = wb.Worksheets.Add("Adjust To Contents"); - - // Set some values with different font sizes - ws.Cell(1, 1).Value = "Tall Row"; - ws.Cell(1, 1).Style.Font.FontSize = 30; - ws.Cell(2, 1).Value = "Very Wide Column"; - ws.Cell(2, 1).Style.Font.FontSize = 20; - - // Adjust column width - ws.Column(1).AdjustToContents(); - - // Adjust row heights - ws.Rows(1, 2).AdjustToContents(); - - // You can also adjust all rows/columns in one shot - // ws.Rows().AdjustToContents(); - // ws.Columns().AdjustToContents(); - - - // We'll now select which cells should be used for calculating the - // column widths (same method applies for row heights) - - // Set the values - ws.Cell(4, 2).Value = "Width ignored because calling column.AdjustToContents(5, 7)"; - ws.Cell(5, 2).Value = "Short text"; - ws.Cell(6, 2).Value = "Width ignored because it's part of a merge"; - ws.Range(6, 2, 6, 4).Merge(); - ws.Cell(7, 2).Value = "Width should adjust to this cell"; - ws.Cell(8, 2).Value = "Width ignored because calling column.AdjustToContents(5, 7)"; - - // Adjust column widths only taking into account rows 5-7 - // (merged cells will be ignored) - ws.Column(2).AdjustToContents(5, 7); - - // You can also specify the starting row to start calculating the widths: - // e.g. ws.Column(3).AdjustToContents(9); - - var ws2 = wb.Worksheets.Add("Adjust Widths"); - ws2.Cell(1, 1).SetValue("Text to adjust - 255").Style.Alignment.TextRotation = 255; - for (Int32 co = 0; co < 90; co += 5) + using (var wb = new XLWorkbook()) { - ws2.Cell(1, (co / 5) + 2).SetValue("Text to adjust - " + co).Style.Alignment.TextRotation = co; + var ws = wb.Worksheets.Add("Adjust To Contents"); + + // Set some values with different font sizes + ws.Cell(1, 1).Value = "Tall Row"; + ws.Cell(1, 1).Style.Font.FontSize = 30; + ws.Cell(2, 1).Value = "Very Wide Column"; + ws.Cell(2, 1).Style.Font.FontSize = 20; + + // Adjust column width + ws.Column(1).AdjustToContents(); + + // Adjust row heights + ws.Rows(1, 2).AdjustToContents(); + + // You can also adjust all rows/columns in one shot + // ws.Rows().AdjustToContents(); + // ws.Columns().AdjustToContents(); + + // We'll now select which cells should be used for calculating the + // column widths (same method applies for row heights) + + // Set the values + ws.Cell(4, 2).Value = "Width ignored because calling column.AdjustToContents(5, 7)"; + ws.Cell(5, 2).Value = "Short text"; + ws.Cell(6, 2).Value = "Width ignored because it's part of a merge"; + ws.Range(6, 2, 6, 4).Merge(); + ws.Cell(7, 2).Value = "Width should adjust to this cell"; + ws.Cell(8, 2).Value = "Width ignored because calling column.AdjustToContents(5, 7)"; + + // Adjust column widths only taking into account rows 5-7 + // (merged cells will be ignored) + ws.Column(2).AdjustToContents(5, 7); + + // You can also specify the starting row to start calculating the widths: + // e.g. ws.Column(3).AdjustToContents(9); + + var ws2 = wb.Worksheets.Add("Adjust Widths"); + ws2.Cell(1, 1).SetValue("Text to adjust - 255").Style.Alignment.TextRotation = 255; + for (Int32 co = 0; co < 90; co += 5) + { + ws2.Cell(1, (co / 5) + 2).SetValue("Text to adjust - " + co).Style.Alignment.TextRotation = co; + } + + ws2.Columns().AdjustToContents(); + + var ws4 = wb.Worksheets.Add("Adjust Widths 2"); + ws4.Cell(1, 1).SetValue("Text to adjust - 255").Style.Alignment.TextRotation = 255; + for (Int32 co = 0; co < 90; co += 5) + { + var c = ws4.Cell(1, (co / 5) + 2); + + c.RichText.AddText("Text to adjust - " + co).SetBold(); + c.RichText.AddText(Environment.NewLine); + c.RichText.AddText("World!").SetBold().SetFontColor(XLColor.Blue).SetFontSize(25); + c.RichText.AddText(Environment.NewLine); + c.RichText.AddText("Hello Cruel and unsusual world").SetBold().SetFontSize(20); + c.RichText.AddText(Environment.NewLine); + c.RichText.AddText("Hello").SetBold(); + c.Style.Alignment.SetTextRotation(co); + } + ws4.Columns().AdjustToContents(); + + var ws3 = wb.Worksheets.Add("Adjust Heights"); + ws3.Cell(1, 1).SetValue("Text to adjust - 255").Style.Alignment.TextRotation = 255; + for (Int32 ro = 0; ro < 90; ro += 5) + { + ws3.Cell((ro / 5) + 2, 1).SetValue("Text to adjust - " + ro).Style.Alignment.TextRotation = ro; + } + + ws3.Rows().AdjustToContents(); + + var ws5 = wb.Worksheets.Add("Adjust Heights 2"); + ws5.Cell(1, 1).SetValue("Text to adjust - 255").Style.Alignment.TextRotation = 255; + for (Int32 ro = 0; ro < 90; ro += 5) + { + var c = ws5.Cell((ro / 5) + 2, 1); + c.RichText.AddText("Text to adjust - " + ro).SetBold(); + c.RichText.AddText(Environment.NewLine); + c.RichText.AddText("World!").SetBold().SetFontColor(XLColor.Blue).SetFontSize(10); + c.RichText.AddText(Environment.NewLine); + c.RichText.AddText("Hello Cruel and unsusual world").SetBold().SetFontSize(15); + c.RichText.AddText(Environment.NewLine); + c.RichText.AddText("Hello").SetBold(); + c.Style.Alignment.SetTextRotation(ro); + } + + ws5.Rows().AdjustToContents(); + + var ws6 = wb.Worksheets.Add("Absurdly wide column"); + ws6.Cell("A1").Value = "Some string"; + + // This column's width should be capped at 255 + ws6.Cell("B1").Value = @"Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum."; + + ws6.Columns().AdjustToContents(); + + wb.SaveAs(filePath, true); } - - ws2.Columns().AdjustToContents(); - - var ws4 = wb.Worksheets.Add("Adjust Widths 2"); - ws4.Cell(1, 1).SetValue("Text to adjust - 255").Style.Alignment.TextRotation = 255; - for (Int32 co = 0; co < 90; co += 5) - { - var c = ws4.Cell(1, (co / 5) + 2); - - c.RichText.AddText("Text to adjust - " + co).SetBold(); - c.RichText.AddText(Environment.NewLine); - c.RichText.AddText("World!").SetBold().SetFontColor(XLColor.Blue).SetFontSize(25); - c.RichText.AddText(Environment.NewLine); - c.RichText.AddText("Hello Cruel and unsusual world").SetBold().SetFontSize(20); - c.RichText.AddText(Environment.NewLine); - c.RichText.AddText("Hello").SetBold(); - c.Style.Alignment.SetTextRotation(co); - } - ws4.Columns().AdjustToContents(); - - var ws3 = wb.Worksheets.Add("Adjust Heights"); - ws3.Cell(1, 1).SetValue("Text to adjust - 255").Style.Alignment.TextRotation = 255; - for (Int32 ro = 0; ro < 90; ro += 5) - { - ws3.Cell((ro / 5) + 2, 1).SetValue("Text to adjust - " + ro).Style.Alignment.TextRotation = ro; - } - - ws3.Rows().AdjustToContents(); - - var ws5 = wb.Worksheets.Add("Adjust Heights 2"); - ws5.Cell(1, 1).SetValue("Text to adjust - 255").Style.Alignment.TextRotation = 255; - for (Int32 ro = 0; ro < 90; ro += 5) - { - var c = ws5.Cell((ro / 5) + 2, 1); - c.RichText.AddText("Text to adjust - " + ro).SetBold(); - c.RichText.AddText(Environment.NewLine); - c.RichText.AddText("World!").SetBold().SetFontColor(XLColor.Blue).SetFontSize(10); - c.RichText.AddText(Environment.NewLine); - c.RichText.AddText("Hello Cruel and unsusual world").SetBold().SetFontSize(15); - c.RichText.AddText(Environment.NewLine); - c.RichText.AddText("Hello").SetBold(); - c.Style.Alignment.SetTextRotation(ro); - } - - ws5.Rows().AdjustToContents(); - - wb.SaveAs(filePath); } - - // Private - - // Override - - - #endregion } } diff --git a/ClosedXML_Examples/Misc/CellValues.cs b/ClosedXML_Examples/Misc/CellValues.cs index 6919bcf..2eac33e 100644 --- a/ClosedXML_Examples/Misc/CellValues.cs +++ b/ClosedXML_Examples/Misc/CellValues.cs @@ -1,185 +1,148 @@ -using System; -using ClosedXML.Excel; - +using ClosedXML.Excel; +using System; namespace ClosedXML_Examples.Misc { public class CellValues : IXLExample { - #region Variables - - // Public - - // Private - - - #endregion - - #region Properties - - // Public - - // Private - - // Override - - - #endregion - - #region Events - - // Public - - // Private - - // Override - - - #endregion - - #region Methods - - // Public public void Create(String filePath) { - var workbook = new XLWorkbook(); - var ws = workbook.Worksheets.Add("Cell Values"); + using (var workbook = new XLWorkbook()) + { + var ws = workbook.Worksheets.Add("Cell Values"); - // Set the titles - ws.Cell(2, 2).Value = "Initial Value"; - ws.Cell(2, 3).Value = "Casting"; - ws.Cell(2, 4).Value = "Using Get...()"; - ws.Cell(2, 5).Value = "Using GetValue()"; - ws.Cell(2, 6).Value = "GetString()"; - ws.Cell(2, 7).Value = "GetFormattedString()"; + // Set the titles + ws.Cell(2, 2).Value = "Initial Value"; + ws.Cell(2, 3).Value = "Casting"; + ws.Cell(2, 4).Value = "Using Get...()"; + ws.Cell(2, 5).Value = "Using GetValue()"; + ws.Cell(2, 6).Value = "GetString()"; + ws.Cell(2, 7).Value = "GetFormattedString()"; - ////////////////////////////////////////////////////////////////// - // DateTime + ////////////////////////////////////////////////////////////////// + // DateTime - // Fill a cell with a date - var cellDateTime = ws.Cell(3, 2); - cellDateTime.Value = new DateTime(2010, 9, 2); - cellDateTime.Style.DateFormat.Format = "yyyy-MMM-dd"; + // Fill a cell with a date + var cellDateTime = ws.Cell(3, 2); + cellDateTime.Value = new DateTime(2010, 9, 2); + cellDateTime.Style.DateFormat.Format = "yyyy-MMM-dd"; - // Extract the date in different ways - DateTime dateTime1 = (DateTime)cellDateTime.Value; - DateTime dateTime2 = cellDateTime.GetDateTime(); - DateTime dateTime3 = cellDateTime.GetValue(); - String dateTimeString = cellDateTime.GetString(); - String dateTimeFormattedString = cellDateTime.GetFormattedString(); + // Extract the date in different ways + DateTime dateTime1 = (DateTime)cellDateTime.Value; + DateTime dateTime2 = cellDateTime.GetDateTime(); + DateTime dateTime3 = cellDateTime.GetValue(); + String dateTimeString = cellDateTime.GetString(); + String dateTimeFormattedString = cellDateTime.GetFormattedString(); - // Set the values back to cells - // The apostrophe is to force ClosedXML to treat the date as a string - ws.Cell(3, 3).Value = dateTime1; - ws.Cell(3, 4).Value = dateTime2; - ws.Cell(3, 5).Value = dateTime3; - ws.Cell(3, 6).Value = "'" + dateTimeString; - ws.Cell(3, 7).Value = "'" + dateTimeFormattedString; + // Set the values back to cells + // The apostrophe is to force ClosedXML to treat the date as a string + ws.Cell(3, 3).Value = dateTime1; + ws.Cell(3, 4).Value = dateTime2; + ws.Cell(3, 5).Value = dateTime3; + ws.Cell(3, 6).Value = "'" + dateTimeString; + ws.Cell(3, 7).Value = "'" + dateTimeFormattedString; - ////////////////////////////////////////////////////////////////// - // Boolean + ////////////////////////////////////////////////////////////////// + // Boolean - // Fill a cell with a boolean - var cellBoolean = ws.Cell(4, 2); - cellBoolean.Value = true; + // Fill a cell with a boolean + var cellBoolean = ws.Cell(4, 2); + cellBoolean.Value = true; - // Extract the boolean in different ways - Boolean boolean1 = (Boolean)cellBoolean.Value; - Boolean boolean2 = cellBoolean.GetBoolean(); - Boolean boolean3 = cellBoolean.GetValue(); - String booleanString = cellBoolean.GetString(); - String booleanFormattedString = cellBoolean.GetFormattedString(); + // Extract the boolean in different ways + Boolean boolean1 = (Boolean)cellBoolean.Value; + Boolean boolean2 = cellBoolean.GetBoolean(); + Boolean boolean3 = cellBoolean.GetValue(); + String booleanString = cellBoolean.GetString(); + String booleanFormattedString = cellBoolean.GetFormattedString(); - // Set the values back to cells - // The apostrophe is to force ClosedXML to treat the boolean as a string - ws.Cell(4, 3).Value = boolean1; - ws.Cell(4, 4).Value = boolean2; - ws.Cell(4, 5).Value = boolean3; - ws.Cell(4, 6).Value = "'" + booleanString; - ws.Cell(4, 7).Value = "'" + booleanFormattedString; + // Set the values back to cells + // The apostrophe is to force ClosedXML to treat the boolean as a string + ws.Cell(4, 3).Value = boolean1; + ws.Cell(4, 4).Value = boolean2; + ws.Cell(4, 5).Value = boolean3; + ws.Cell(4, 6).Value = "'" + booleanString; + ws.Cell(4, 7).Value = "'" + booleanFormattedString; - ////////////////////////////////////////////////////////////////// - // Double + ////////////////////////////////////////////////////////////////// + // Double - // Fill a cell with a double - var cellDouble = ws.Cell(5, 2); - cellDouble.Value = 1234.567; - cellDouble.Style.NumberFormat.Format = "#,##0.00"; + // Fill a cell with a double + var cellDouble = ws.Cell(5, 2); + cellDouble.Value = 1234.567; + cellDouble.Style.NumberFormat.Format = "#,##0.00"; - // Extract the double in different ways - Double double1 = (Double)cellDouble.Value; - Double double2 = cellDouble.GetDouble(); - Double double3 = cellDouble.GetValue(); - String doubleString = cellDouble.GetString(); - String doubleFormattedString = cellDouble.GetFormattedString(); + // Extract the double in different ways + Double double1 = (Double)cellDouble.Value; + Double double2 = cellDouble.GetDouble(); + Double double3 = cellDouble.GetValue(); + String doubleString = cellDouble.GetString(); + String doubleFormattedString = cellDouble.GetFormattedString(); - // Set the values back to cells - // The apostrophe is to force ClosedXML to treat the double as a string - ws.Cell(5, 3).Value = double1; - ws.Cell(5, 4).Value = double2; - ws.Cell(5, 5).Value = double3; - ws.Cell(5, 6).Value = "'" + doubleString; - ws.Cell(5, 7).Value = "'" + doubleFormattedString; + // Set the values back to cells + // The apostrophe is to force ClosedXML to treat the double as a string + ws.Cell(5, 3).Value = double1; + ws.Cell(5, 4).Value = double2; + ws.Cell(5, 5).Value = double3; + ws.Cell(5, 6).Value = "'" + doubleString; + ws.Cell(5, 7).Value = "'" + doubleFormattedString; - ////////////////////////////////////////////////////////////////// - // String + ////////////////////////////////////////////////////////////////// + // String - // Fill a cell with a string - var cellString = ws.Cell(6, 2); - cellString.Value = "Test Case"; + // Fill a cell with a string + var cellString = ws.Cell(6, 2); + cellString.Value = "Test Case"; - // Extract the string in different ways - String string1 = (String)cellString.Value; - String string2 = cellString.GetString(); - String string3 = cellString.GetValue(); - String stringString = cellString.GetString(); - String stringFormattedString = cellString.GetFormattedString(); + // Extract the string in different ways + String string1 = (String)cellString.Value; + String string2 = cellString.GetString(); + String string3 = cellString.GetValue(); + String stringString = cellString.GetString(); + String stringFormattedString = cellString.GetFormattedString(); - // Set the values back to cells - ws.Cell(6, 3).Value = string1; - ws.Cell(6, 4).Value = string2; - ws.Cell(6, 5).Value = string3; - ws.Cell(6, 6).Value = stringString; - ws.Cell(6, 7).Value = stringFormattedString; + // Set the values back to cells + ws.Cell(6, 3).Value = string1; + ws.Cell(6, 4).Value = string2; + ws.Cell(6, 5).Value = string3; + ws.Cell(6, 6).Value = stringString; + ws.Cell(6, 7).Value = stringFormattedString; - ////////////////////////////////////////////////////////////////// - // TimeSpan + ////////////////////////////////////////////////////////////////// + // TimeSpan - // Fill a cell with a timeSpan - var cellTimeSpan = ws.Cell(7, 2); - cellTimeSpan.Value = new TimeSpan(1, 2, 31, 45); + // Fill a cell with a timeSpan + var cellTimeSpan = ws.Cell(7, 2); + cellTimeSpan.Value = new TimeSpan(1, 2, 31, 45); - // Extract the timeSpan in different ways - TimeSpan timeSpan1 = (TimeSpan)cellTimeSpan.Value; - TimeSpan timeSpan2 = cellTimeSpan.GetTimeSpan(); - TimeSpan timeSpan3 = cellTimeSpan.GetValue(); - String timeSpanString = cellTimeSpan.GetString(); - String timeSpanFormattedString = cellTimeSpan.GetFormattedString(); + // Extract the timeSpan in different ways + TimeSpan timeSpan1 = (TimeSpan)cellTimeSpan.Value; + TimeSpan timeSpan2 = cellTimeSpan.GetTimeSpan(); + TimeSpan timeSpan3 = cellTimeSpan.GetValue(); + String timeSpanString = "'" + cellTimeSpan.GetString(); + String timeSpanFormattedString = "'" + cellTimeSpan.GetFormattedString(); - // Set the values back to cells - ws.Cell(7, 3).Value = timeSpan1; - ws.Cell(7, 4).Value = timeSpan2; - ws.Cell(7, 5).Value = timeSpan3; - ws.Cell(7, 6).Value = timeSpanString; - ws.Cell(7, 7).Value = timeSpanFormattedString; + // Set the values back to cells + ws.Cell(7, 3).Value = timeSpan1; + ws.Cell(7, 4).Value = timeSpan2; + ws.Cell(7, 5).Value = timeSpan3; + ws.Cell(7, 6).Value = timeSpanString; + ws.Cell(7, 7).Value = timeSpanFormattedString; - ////////////////////////////////////////////////////////////////// - // Do some formatting - ws.Columns("B:G").Width = 20; - var rngTitle = ws.Range("B2:G2"); - rngTitle.Style.Font.Bold = true; - rngTitle.Style.Fill.BackgroundColor = XLColor.Cyan; + ////////////////////////////////////////////////////////////////// + // Do some formatting + ws.Columns("B:G").Width = 20; + var rngTitle = ws.Range("B2:G2"); + rngTitle.Style.Font.Bold = true; + rngTitle.Style.Fill.BackgroundColor = XLColor.Cyan; - ws.Columns().AdjustToContents(); + ws.Columns().AdjustToContents(); - workbook.SaveAs(filePath); + ws = workbook.AddWorksheet("Test Whitespace"); + ws.FirstCell().Value = "' "; + + workbook.SaveAs(filePath); + } } - - // Private - - // Override - - - #endregion } } diff --git a/ClosedXML_Examples/Misc/DataTypes.cs b/ClosedXML_Examples/Misc/DataTypes.cs index e4feeb1..14abad9 100644 --- a/ClosedXML_Examples/Misc/DataTypes.cs +++ b/ClosedXML_Examples/Misc/DataTypes.cs @@ -68,6 +68,17 @@ ro++; + ws.Cell(++ro, co).Value = "Decimal Number:"; + ws.Cell(ro, co + 1).Value = 123.45m; + + ws.Cell(++ro, co).Value = "Float Number:"; + ws.Cell(ro, co + 1).Value = 123.45f; + + ws.Cell(++ro, co).Value = "Double Number:"; + ws.Cell(ro, co + 1).Value = 123.45d; + + ro++; + ws.Cell(++ro, co).Value = "Explicit Text:"; ws.Cell(ro, co + 1).Value = "'Hello World."; @@ -192,7 +203,7 @@ .SetDataType(XLCellValues.DateTime) .SetDataType(XLCellValues.Boolean) .SetDataType(XLCellValues.Text); - + ws.Columns(2, 3).AdjustToContents(); workbook.SaveAs(filePath); diff --git a/ClosedXML_Examples/Misc/SheetViews.cs b/ClosedXML_Examples/Misc/SheetViews.cs new file mode 100644 index 0000000..70f9b5c --- /dev/null +++ b/ClosedXML_Examples/Misc/SheetViews.cs @@ -0,0 +1,45 @@ +using ClosedXML.Excel; +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML_Examples.Misc +{ + public class SheetViews : IXLExample + { + public void Create(string filePath) + { + using (var wb = new XLWorkbook()) + { + IXLWorksheet ws; + + ws = wb.AddWorksheet("ZoomScale"); + ws.FirstCell().SetValue(ws.Name); + ws.SheetView.ZoomScale = 50; + + ws = wb.AddWorksheet("ZoomScaleNormal"); + ws.FirstCell().SetValue(ws.Name); + ws.SheetView.ZoomScaleNormal = 70; + + ws = wb.AddWorksheet("ZoomScalePageLayoutView"); + ws.FirstCell().SetValue(ws.Name); + ws.SheetView.ZoomScalePageLayoutView = 85; + + ws = wb.AddWorksheet("ZoomScaleSheetLayoutView"); + ws.FirstCell().SetValue(ws.Name); + ws.SheetView.ZoomScaleSheetLayoutView = 120; + + ws = wb.AddWorksheet("ZoomScaleTooSmall"); + ws.FirstCell().SetValue(ws.Name); + ws.SheetView.ZoomScale = 5; + + ws = wb.AddWorksheet("ZoomScaleTooBig"); + ws.FirstCell().SetValue(ws.Name); + ws.SheetView.ZoomScale = 500; + + wb.SaveAs(filePath); + } + } + } +} diff --git a/ClosedXML_Examples/PivotTables/PivotTables.cs b/ClosedXML_Examples/PivotTables/PivotTables.cs index 5678228..61692b9 100644 --- a/ClosedXML_Examples/PivotTables/PivotTables.cs +++ b/ClosedXML_Examples/PivotTables/PivotTables.cs @@ -38,9 +38,11 @@ new Pastry("Danish", 394, -20.24, "Apr"), new Pastry("Danish", 190, 60, "May"), new Pastry("Danish", 221, 24.76, "June"), + + // Deliberately add different casings of same string to ensure pivot table doesn't duplicate it. new Pastry("Scone", 135, 0, "Apr"), - new Pastry("Scone", 122, 5.19, "May"), - new Pastry("Scone", 243, 44.2, "June") + new Pastry("SconE", 122, 5.19, "May"), + new Pastry("SCONE", 243, 44.2, "June") }; using (var wb = new XLWorkbook()) @@ -61,10 +63,10 @@ for (int i = 1; i <= 3; i++) { // Add a new sheet for our pivot table - ptSheet = wb.Worksheets.Add("PivotTable" + i); + ptSheet = wb.Worksheets.Add("pvt" + i); // Create the pivot table, using the data from the "PastrySalesData" table - pt = ptSheet.PivotTables.AddNew("PivotTable", ptSheet.Cell(1, 1), dataRange); + pt = ptSheet.PivotTables.AddNew("pvt", ptSheet.Cell(1, 1), dataRange); // The rows in our pivot table will be the names of the pastries pt.RowLabels.Add("Name"); @@ -94,14 +96,35 @@ } // Different kind of pivot - ptSheet = wb.Worksheets.Add("PivotTableNoColumnLabels"); - pt = ptSheet.PivotTables.AddNew("PivotTableNoColumnLabels", ptSheet.Cell(1, 1), dataRange); + ptSheet = wb.Worksheets.Add("pvtNoColumnLabels"); + pt = ptSheet.PivotTables.AddNew("pvtNoColumnLabels", ptSheet.Cell(1, 1), dataRange); pt.RowLabels.Add("Name"); pt.RowLabels.Add("Month"); - pt.Values.Add("NumberOfOrders").SetSummaryFormula(XLPivotSummary.Sum);//.NumberFormat.Format = "#0.00"; - pt.Values.Add("Quality").SetSummaryFormula(XLPivotSummary.Sum);//.NumberFormat.Format = "#0.00"; + pt.Values.Add("NumberOfOrders").SetSummaryFormula(XLPivotSummary.Sum); + pt.Values.Add("Quality").SetSummaryFormula(XLPivotSummary.Sum); + + + // Pivot table with collapsed fields + ptSheet = wb.Worksheets.Add("pvtCollapsedFields"); + pt = ptSheet.PivotTables.AddNew("pvtCollapsedFields", ptSheet.Cell(1, 1), dataRange); + + pt.RowLabels.Add("Name").SetCollapsed(); + pt.RowLabels.Add("Month").SetCollapsed(); + + pt.Values.Add("NumberOfOrders").SetSummaryFormula(XLPivotSummary.Sum); + pt.Values.Add("Quality").SetSummaryFormula(XLPivotSummary.Sum); + + + // Pivot table with a field both as a value and as a row/column/filter label + ptSheet = wb.Worksheets.Add("pvtFieldAsValueAndLabel"); + pt = ptSheet.PivotTables.AddNew("pvtFieldAsValueAndLabel", ptSheet.Cell(1, 1), dataRange); + + pt.RowLabels.Add("Name"); + pt.RowLabels.Add("Month"); + + pt.Values.Add("Name").SetSummaryFormula(XLPivotSummary.Count);//.NumberFormat.Format = "#0.00"; wb.SaveAs(filePath); } diff --git a/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj b/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj index 7cd14ba..a9f9ad8 100644 --- a/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj +++ b/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj @@ -97,6 +97,9 @@ Excel\CalcEngine\CalcEngine.cs + + Excel\CalcEngine\CalcEngineHelpers.cs + Excel\CalcEngine\Expression.cs diff --git a/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML_Tests/ClosedXML_Tests.csproj index 89a0eff..eabbee3 100644 --- a/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -73,10 +73,12 @@ + + @@ -102,6 +104,7 @@ + @@ -161,6 +164,7 @@ + @@ -251,6 +255,11 @@ + + + + + diff --git a/ClosedXML_Tests/Examples/MiscTests.cs b/ClosedXML_Tests/Examples/MiscTests.cs index eebb483..47865bc 100644 --- a/ClosedXML_Tests/Examples/MiscTests.cs +++ b/ClosedXML_Tests/Examples/MiscTests.cs @@ -170,6 +170,12 @@ } [Test] + public void SheetViews() + { + TestHelper.RunTestExample(@"Misc\SheetViews.xlsx"); + } + + [Test] public void ShiftingFormulas() { TestHelper.RunTestExample(@"Misc\ShiftingFormulas.xlsx"); diff --git a/ClosedXML_Tests/Excel/CalcEngine/FunctionsTests.cs b/ClosedXML_Tests/Excel/CalcEngine/FunctionsTests.cs index 98f8d87..48691a6 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/FunctionsTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/FunctionsTests.cs @@ -467,10 +467,10 @@ public void SubtotalCount() { object actual = XLWorkbook.EvaluateExpr("Subtotal(2,2,3)"); - Assert.AreEqual(2.0, actual); + Assert.AreEqual(2, actual); actual = XLWorkbook.EvaluateExpr(@"Subtotal(2,""A"",3)"); - Assert.AreEqual(2.0, actual); + Assert.AreEqual(1, actual); } [Test] @@ -609,14 +609,12 @@ XLWorkbook wb = new XLWorkbook(); wb.Worksheets.Add("TallyTests"); var cell = wb.Worksheet(1).Cell(1, 1).SetFormulaA1("=MAX(D1,D2)"); - Assert.AreEqual(0, cell.Value); + Assert.AreEqual(0, cell.Value); cell = wb.Worksheet(1).Cell(2, 1).SetFormulaA1("=MIN(D1,D2)"); Assert.AreEqual(0, cell.Value); cell = wb.Worksheet(1).Cell(3, 1).SetFormulaA1("=SUM(D1,D2)"); Assert.AreEqual(0, cell.Value); - cell = wb.Worksheet(1).Cell(3, 1).SetFormulaA1("=AVERAGE(D1,D2)"); - Assert.AreEqual(0, cell.Value); - + Assert.That(() => wb.Worksheet(1).Cell(3, 1).SetFormulaA1("=AVERAGE(D1,D2)").Value, Throws.Exception); } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Excel/CalcEngine/StatisticalTests.cs b/ClosedXML_Tests/Excel/CalcEngine/StatisticalTests.cs new file mode 100644 index 0000000..9285cb4 --- /dev/null +++ b/ClosedXML_Tests/Excel/CalcEngine/StatisticalTests.cs @@ -0,0 +1,270 @@ +using ClosedXML.Excel; +using NUnit.Framework; +using System; +using System.Linq; + +namespace ClosedXML_Tests.Excel.CalcEngine +{ + [TestFixture] + public class StatisticalTests + { + private double tolerance = 1e-6; + private XLWorkbook workbook; + + [Test] + public void Average() + { + double value; + value = workbook.Evaluate("AVERAGE(-27.5,93.93,64.51,-70.56)").CastTo(); + Assert.AreEqual(15.095, value, tolerance); + + var ws = workbook.Worksheets.First(); + value = ws.Evaluate("AVERAGE(G3:G45)").CastTo(); + Assert.AreEqual(49.3255814, value, tolerance); + + Assert.That(() => ws.Evaluate("AVERAGE(D3:D45)"), Throws.Exception); + } + + [Test] + public void Count() + { + var ws = workbook.Worksheets.First(); + int value; + value = ws.Evaluate(@"=COUNT(D3:D45)").CastTo(); + Assert.AreEqual(0, value); + + value = ws.Evaluate(@"=COUNT(G3:G45)").CastTo(); + Assert.AreEqual(43, value); + + value = ws.Evaluate(@"=COUNT(G:G)").CastTo(); + Assert.AreEqual(43, value); + + value = workbook.Evaluate(@"=COUNT(Data!G:G)").CastTo(); + Assert.AreEqual(43, value); + } + + [Test] + public void CountA() + { + var ws = workbook.Worksheets.First(); + int value; + value = ws.Evaluate(@"=COUNTA(D3:D45)").CastTo(); + Assert.AreEqual(43, value); + + value = ws.Evaluate(@"=COUNTA(G3:G45)").CastTo(); + Assert.AreEqual(43, value); + + value = ws.Evaluate(@"=COUNTA(G:G)").CastTo(); + Assert.AreEqual(44, value); + + value = workbook.Evaluate(@"=COUNTA(Data!G:G)").CastTo(); + Assert.AreEqual(44, value); + } + + [Test] + public void CountBlank() + { + var ws = workbook.Worksheets.First(); + int value; + value = ws.Evaluate(@"=COUNTBLANK(B:B)").CastTo(); + Assert.AreEqual(1048532, value); + + value = ws.Evaluate(@"=COUNTBLANK(D43:D49)").CastTo(); + Assert.AreEqual(4, value); + + value = workbook.Evaluate(@"=COUNTBLANK(E3:E45)").CastTo(); + Assert.AreEqual(0, value); + } + + [Test] + public void CountIf() + { + var ws = workbook.Worksheets.First(); + int value; + value = ws.Evaluate(@"=COUNTIF(D3:D45,""Central"")").CastTo(); + Assert.AreEqual(24, value); + + value = ws.Evaluate(@"=COUNTIF(D:D,""Central"")").CastTo(); + Assert.AreEqual(24, value); + + value = workbook.Evaluate(@"=COUNTIF(Data!D:D,""Central"")").CastTo(); + Assert.AreEqual(24, value); + } + + [OneTimeTearDown] + public void Dispose() + { + workbook.Dispose(); + } + + [OneTimeSetUp] + public void Init() + { + // Make sure tests run on a deterministic culture + System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); + workbook = SetupWorkbook(); + } + + [Test] + public void Max() + { + var ws = workbook.Worksheets.First(); + int value; + value = ws.Evaluate(@"=MAX(D3:D45)").CastTo(); + Assert.AreEqual(0, value); + + value = ws.Evaluate(@"=MAX(G3:G45)").CastTo(); + Assert.AreEqual(96, value); + + value = ws.Evaluate(@"=MAX(G:G)").CastTo(); + Assert.AreEqual(96, value); + + value = workbook.Evaluate(@"=MAX(Data!G:G)").CastTo(); + Assert.AreEqual(96, value); + } + + [Test] + public void Min() + { + var ws = workbook.Worksheets.First(); + int value; + value = ws.Evaluate(@"=MIN(D3:D45)").CastTo(); + Assert.AreEqual(0, value); + + value = ws.Evaluate(@"=MIN(G3:G45)").CastTo(); + Assert.AreEqual(2, value); + + value = ws.Evaluate(@"=MIN(G:G)").CastTo(); + Assert.AreEqual(2, value); + + value = workbook.Evaluate(@"=MIN(Data!G:G)").CastTo(); + Assert.AreEqual(2, value); + } + + [Test] + public void StDev() + { + var ws = workbook.Worksheets.First(); + double value; + Assert.That(() => ws.Evaluate(@"=STDEV(D3:D45)"), Throws.Exception); + + value = ws.Evaluate(@"=STDEV(H3:H45)").CastTo(); + Assert.AreEqual(47.34511769, value, tolerance); + + value = ws.Evaluate(@"=STDEV(H:H)").CastTo(); + Assert.AreEqual(47.34511769, value, tolerance); + + value = workbook.Evaluate(@"=STDEV(Data!H:H)").CastTo(); + Assert.AreEqual(47.34511769, value, tolerance); + } + + [Test] + public void StDevP() + { + var ws = workbook.Worksheets.First(); + double value; + Assert.That(() => ws.Evaluate(@"=STDEVP(D3:D45)"), Throws.Exception); + + value = ws.Evaluate(@"=STDEVP(H3:H45)").CastTo(); + Assert.AreEqual(46.79135458, value, tolerance); + + value = ws.Evaluate(@"=STDEVP(H:H)").CastTo(); + Assert.AreEqual(46.79135458, value, tolerance); + + value = workbook.Evaluate(@"=STDEVP(Data!H:H)").CastTo(); + Assert.AreEqual(46.79135458, value, tolerance); + } + + [Test] + public void Var() + { + var ws = workbook.Worksheets.First(); + double value; + Assert.That(() => ws.Evaluate(@"=VAR(D3:D45)"), Throws.Exception); + + value = ws.Evaluate(@"=VAR(H3:H45)").CastTo(); + Assert.AreEqual(2241.560169, value, tolerance); + + value = ws.Evaluate(@"=VAR(H:H)").CastTo(); + Assert.AreEqual(2241.560169, value, tolerance); + + value = workbook.Evaluate(@"=VAR(Data!H:H)").CastTo(); + Assert.AreEqual(2241.560169, value, tolerance); + } + + [Test] + public void VarP() + { + var ws = workbook.Worksheets.First(); + double value; + Assert.That(() => ws.Evaluate(@"=VARP(D3:D45)"), Throws.Exception); + + value = ws.Evaluate(@"=VARP(H3:H45)").CastTo(); + Assert.AreEqual(2189.430863, value, tolerance); + + value = ws.Evaluate(@"=VARP(H:H)").CastTo(); + Assert.AreEqual(2189.430863, value, tolerance); + + value = workbook.Evaluate(@"=VARP(Data!H:H)").CastTo(); + Assert.AreEqual(2189.430863, value, tolerance); + } + + private XLWorkbook SetupWorkbook() + { + var wb = new XLWorkbook(); + var ws = wb.AddWorksheet("Data"); + var data = new object[] + { + new {Id=1, OrderDate = DateTime.Parse("2015-01-06"), Region = "East", Rep = "Jones", Item = "Pencil", Units = 95, UnitCost = 1.99, Total = 189.05 }, + new {Id=2, OrderDate = DateTime.Parse("2015-01-23"), Region = "Central", Rep = "Kivell", Item = "Binder", Units = 50, UnitCost = 19.99, Total = 999.5}, + new {Id=3, OrderDate = DateTime.Parse("2015-02-09"), Region = "Central", Rep = "Jardine", Item = "Pencil", Units = 36, UnitCost = 4.99, Total = 179.64}, + new {Id=4, OrderDate = DateTime.Parse("2015-02-26"), Region = "Central", Rep = "Gill", Item = "Pen", Units = 27, UnitCost = 19.99, Total = 539.73}, + new {Id=5, OrderDate = DateTime.Parse("2015-03-15"), Region = "West", Rep = "Sorvino", Item = "Pencil", Units = 56, UnitCost = 2.99, Total = 167.44}, + new {Id=6, OrderDate = DateTime.Parse("2015-04-01"), Region = "East", Rep = "Jones", Item = "Binder", Units = 60, UnitCost = 4.99, Total = 299.4}, + new {Id=7, OrderDate = DateTime.Parse("2015-04-18"), Region = "Central", Rep = "Andrews", Item = "Pencil", Units = 75, UnitCost = 1.99, Total = 149.25}, + new {Id=8, OrderDate = DateTime.Parse("2015-05-05"), Region = "Central", Rep = "Jardine", Item = "Pencil", Units = 90, UnitCost = 4.99, Total = 449.1}, + new {Id=9, OrderDate = DateTime.Parse("2015-05-22"), Region = "West", Rep = "Thompson", Item = "Pencil", Units = 32, UnitCost = 1.99, Total = 63.68}, + new {Id=10, OrderDate = DateTime.Parse("2015-06-08"), Region = "East", Rep = "Jones", Item = "Binder", Units = 60, UnitCost = 8.99, Total = 539.4}, + new {Id=11, OrderDate = DateTime.Parse("2015-06-25"), Region = "Central", Rep = "Morgan", Item = "Pencil", Units = 90, UnitCost = 4.99, Total = 449.1}, + new {Id=12, OrderDate = DateTime.Parse("2015-07-12"), Region = "East", Rep = "Howard", Item = "Binder", Units = 29, UnitCost = 1.99, Total = 57.71}, + new {Id=13, OrderDate = DateTime.Parse("2015-07-29"), Region = "East", Rep = "Parent", Item = "Binder", Units = 81, UnitCost = 19.99, Total = 1619.19}, + new {Id=14, OrderDate = DateTime.Parse("2015-08-15"), Region = "East", Rep = "Jones", Item = "Pencil", Units = 35, UnitCost = 4.99, Total = 174.65}, + new {Id=15, OrderDate = DateTime.Parse("2015-09-01"), Region = "Central", Rep = "Smith", Item = "Desk", Units = 2, UnitCost = 125, Total = 250}, + new {Id=16, OrderDate = DateTime.Parse("2015-09-18"), Region = "East", Rep = "Jones", Item = "Pen Set", Units = 16, UnitCost = 15.99, Total = 255.84}, + new {Id=17, OrderDate = DateTime.Parse("2015-10-05"), Region = "Central", Rep = "Morgan", Item = "Binder", Units = 28, UnitCost = 8.99, Total = 251.72}, + new {Id=18, OrderDate = DateTime.Parse("2015-10-22"), Region = "East", Rep = "Jones", Item = "Pen", Units = 64, UnitCost = 8.99, Total = 575.36}, + new {Id=19, OrderDate = DateTime.Parse("2015-11-08"), Region = "East", Rep = "Parent", Item = "Pen", Units = 15, UnitCost = 19.99, Total = 299.85}, + new {Id=20, OrderDate = DateTime.Parse("2015-11-25"), Region = "Central", Rep = "Kivell", Item = "Pen Set", Units = 96, UnitCost = 4.99, Total = 479.04}, + new {Id=21, OrderDate = DateTime.Parse("2015-12-12"), Region = "Central", Rep = "Smith", Item = "Pencil", Units = 67, UnitCost = 1.29, Total = 86.43}, + new {Id=22, OrderDate = DateTime.Parse("2015-12-29"), Region = "East", Rep = "Parent", Item = "Pen Set", Units = 74, UnitCost = 15.99, Total = 1183.26}, + new {Id=23, OrderDate = DateTime.Parse("2016-01-15"), Region = "Central", Rep = "Gill", Item = "Binder", Units = 46, UnitCost = 8.99, Total = 413.54}, + new {Id=24, OrderDate = DateTime.Parse("2016-02-01"), Region = "Central", Rep = "Smith", Item = "Binder", Units = 87, UnitCost = 15, Total = 1305}, + new {Id=25, OrderDate = DateTime.Parse("2016-02-18"), Region = "East", Rep = "Jones", Item = "Binder", Units = 4, UnitCost = 4.99, Total = 19.96}, + new {Id=26, OrderDate = DateTime.Parse("2016-03-07"), Region = "West", Rep = "Sorvino", Item = "Binder", Units = 7, UnitCost = 19.99, Total = 139.93}, + new {Id=27, OrderDate = DateTime.Parse("2016-03-24"), Region = "Central", Rep = "Jardine", Item = "Pen Set", Units = 50, UnitCost = 4.99, Total = 249.5}, + new {Id=28, OrderDate = DateTime.Parse("2016-04-10"), Region = "Central", Rep = "Andrews", Item = "Pencil", Units = 66, UnitCost = 1.99, Total = 131.34}, + new {Id=29, OrderDate = DateTime.Parse("2016-04-27"), Region = "East", Rep = "Howard", Item = "Pen", Units = 96, UnitCost = 4.99, Total = 479.04}, + new {Id=30, OrderDate = DateTime.Parse("2016-05-14"), Region = "Central", Rep = "Gill", Item = "Pencil", Units = 53, UnitCost = 1.29, Total = 68.37}, + new {Id=31, OrderDate = DateTime.Parse("2016-05-31"), Region = "Central", Rep = "Gill", Item = "Binder", Units = 80, UnitCost = 8.99, Total = 719.2}, + new {Id=32, OrderDate = DateTime.Parse("2016-06-17"), Region = "Central", Rep = "Kivell", Item = "Desk", Units = 5, UnitCost = 125, Total = 625}, + new {Id=33, OrderDate = DateTime.Parse("2016-07-04"), Region = "East", Rep = "Jones", Item = "Pen Set", Units = 62, UnitCost = 4.99, Total = 309.38}, + new {Id=34, OrderDate = DateTime.Parse("2016-07-21"), Region = "Central", Rep = "Morgan", Item = "Pen Set", Units = 55, UnitCost = 12.49, Total = 686.95}, + new {Id=35, OrderDate = DateTime.Parse("2016-08-07"), Region = "Central", Rep = "Kivell", Item = "Pen Set", Units = 42, UnitCost = 23.95, Total = 1005.9}, + new {Id=36, OrderDate = DateTime.Parse("2016-08-24"), Region = "West", Rep = "Sorvino", Item = "Desk", Units = 3, UnitCost = 275, Total = 825}, + new {Id=37, OrderDate = DateTime.Parse("2016-09-10"), Region = "Central", Rep = "Gill", Item = "Pencil", Units = 7, UnitCost = 1.29, Total = 9.03}, + new {Id=38, OrderDate = DateTime.Parse("2016-09-27"), Region = "West", Rep = "Sorvino", Item = "Pen", Units = 76, UnitCost = 1.99, Total = 151.24}, + new {Id=39, OrderDate = DateTime.Parse("2016-10-14"), Region = "West", Rep = "Thompson", Item = "Binder", Units = 57, UnitCost = 19.99, Total = 1139.43}, + new {Id=40, OrderDate = DateTime.Parse("2016-10-31"), Region = "Central", Rep = "Andrews", Item = "Pencil", Units = 14, UnitCost = 1.29, Total = 18.06}, + new {Id=41, OrderDate = DateTime.Parse("2016-11-17"), Region = "Central", Rep = "Jardine", Item = "Binder", Units = 11, UnitCost = 4.99, Total = 54.89}, + new {Id=42, OrderDate = DateTime.Parse("2016-12-04"), Region = "Central", Rep = "Jardine", Item = "Binder", Units = 94, UnitCost = 19.99, Total = 1879.06}, + new {Id=43, OrderDate = DateTime.Parse("2016-12-21"), Region = "Central", Rep = "Andrews", Item = "Binder", Units = 28, UnitCost = 4.99, Total = 139.72} + }; + ws.FirstCell() + .CellBelow() + .CellRight() + .InsertTable(data, "Table1"); + + return wb; + } + } +} diff --git a/ClosedXML_Tests/Excel/CalcEngine/TextTests.cs b/ClosedXML_Tests/Excel/CalcEngine/TextTests.cs index 013950b..b5a940e 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/TextTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/TextTests.cs @@ -480,6 +480,13 @@ } [Test] + public void Text_String_Input() + { + Object actual = XLWorkbook.EvaluateExpr(@"TEXT(""211x"", ""#00"")"); + Assert.AreEqual("211x", actual); + } + + [Test] public void Trim_EmptyInput_Striong() { Object actual = XLWorkbook.EvaluateExpr(@"Trim("""")"); @@ -523,4 +530,4 @@ Assert.AreEqual(654.32, actual); } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Excel/Loading/LoadingTests.cs b/ClosedXML_Tests/Excel/Loading/LoadingTests.cs index 3568007..d2e7069 100644 --- a/ClosedXML_Tests/Excel/Loading/LoadingTests.cs +++ b/ClosedXML_Tests/Excel/Loading/LoadingTests.cs @@ -1,10 +1,8 @@ -using System; +using ClosedXML.Excel; +using NUnit.Framework; using System.Collections.Generic; -using System.Data; using System.IO; using System.Linq; -using ClosedXML.Excel; -using NUnit.Framework; namespace ClosedXML_Tests.Excel { @@ -20,7 +18,9 @@ var files = new List() { @"Misc\TableWithCustomTheme.xlsx", - @"Misc\EmptyTable.xlsx" + @"Misc\EmptyTable.xlsx", + @"Misc\LoadPivotTables.xlsx", + @"Misc\LoadFileWithCustomSheetViews.xlsx" }; foreach (var file in files) @@ -40,5 +40,82 @@ table.DataRange.InsertRowsBelow(5); } } + + [Test] + public void CanLoadAndSaveFileWithMismatchingSheetIdAndRelId() + { + // This file's workbook.xml contains: + // + // and the mismatch between the sheetId and r:id can create problems. + using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Misc\FileWithMismatchSheetIdAndRelId.xlsx"))) + using (var wb = new XLWorkbook(stream)) + { + using (var ms = new MemoryStream()) + { + wb.SaveAs(ms, true); + } + } + } + + [Test] + public void CanLoadBasicPivotTable() + { + using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Misc\LoadPivotTables.xlsx"))) + using (var wb = new XLWorkbook(stream)) + { + var ws = wb.Worksheet("PivotTable1"); + var pt = ws.PivotTable("PivotTable1"); + Assert.AreEqual("PivotTable1", pt.Name); + + Assert.AreEqual(1, pt.RowLabels.Count()); + Assert.AreEqual("Name", pt.RowLabels.Single().SourceName); + + Assert.AreEqual(1, pt.ColumnLabels.Count()); + Assert.AreEqual("Month", pt.ColumnLabels.Single().SourceName); + + var pv = pt.Values.Single(); + Assert.AreEqual("Sum of NumberOfOrders", pv.CustomName); + Assert.AreEqual("NumberOfOrders", pv.SourceName); + } + } + + /// + /// For non-English locales, the default style ("Normal" in English) can be + /// another piece of text (e.g. Обычный in Russian). + /// This test ensures that the default style is correctly detected and + /// no style conflicts occur on save. + /// + [Test] + public void CanSaveFileWithDefaultStyleNameNotInEnglish() + { + using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Misc\FileWithDefaultStyleNameNotInEnglish.xlsx"))) + using (var wb = new XLWorkbook(stream)) + { + using (var ms = new MemoryStream()) + { + wb.SaveAs(ms, true); + } + } + } + + /// + /// As per https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.cellvalues(v=office.15).aspx + /// the 'Date' DataType is available only in files saved with Microsoft Office + /// In other files, the data type will be saved as numeric + /// ClosedXML then deduces the data type by inspecting the number format string + /// + [Test] + public void CanLoadLibreOfficeFileWithDates() + { + using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Misc\LibreOfficeFileWithDates.xlsx"))) + using (var wb = new XLWorkbook(stream)) + { + var ws = wb.Worksheets.First(); + foreach (var cell in ws.CellsUsed()) + { + Assert.AreEqual(XLCellValues.DateTime, cell.DataType); + } + } + } } } diff --git a/ClosedXML_Tests/Excel/PageSetup/PageBreaksTests.cs b/ClosedXML_Tests/Excel/PageSetup/PageBreaksTests.cs new file mode 100644 index 0000000..04d7464 --- /dev/null +++ b/ClosedXML_Tests/Excel/PageSetup/PageBreaksTests.cs @@ -0,0 +1,78 @@ +using System.Diagnostics; +using ClosedXML.Excel; +using NUnit.Framework; + +namespace ClosedXML_Tests.Excel +{ + [TestFixture] + public class PageBreaksTests + { + [Test] + public void RowBreaksShouldBeSorted() + { + var wb = new XLWorkbook(); + IXLWorksheet sheet = wb.AddWorksheet("Sheet1"); + + sheet.PageSetup.AddHorizontalPageBreak(10); + sheet.PageSetup.AddHorizontalPageBreak(12); + sheet.PageSetup.AddHorizontalPageBreak(5); + Assert.That(sheet.PageSetup.RowBreaks, Is.EqualTo(new[] { 5, 10, 12 })); + } + + [Test] + public void ColumnBreaksShouldBeSorted() + { + var wb = new XLWorkbook(); + IXLWorksheet sheet = wb.AddWorksheet("Sheet1"); + + sheet.PageSetup.AddVerticalPageBreak(10); + sheet.PageSetup.AddVerticalPageBreak(12); + sheet.PageSetup.AddVerticalPageBreak(5); + Assert.That(sheet.PageSetup.ColumnBreaks, Is.EqualTo(new[] { 5, 10, 12 })); + } + + [Test] + public void RowBreaksShiftWhenInsertedRowAbove() + { + var wb = new XLWorkbook(); + IXLWorksheet sheet = wb.AddWorksheet("Sheet1"); + + sheet.PageSetup.AddHorizontalPageBreak(10); + sheet.Row(5).InsertRowsAbove(1); + Assert.AreEqual(11, sheet.PageSetup.RowBreaks[0]); + } + + [Test] + public void RowBreaksNotShiftWhenInsertedRowBelow() + { + var wb = new XLWorkbook(); + IXLWorksheet sheet = wb.AddWorksheet("Sheet1"); + + sheet.PageSetup.AddHorizontalPageBreak(10); + sheet.Row(15).InsertRowsAbove(1); + Assert.AreEqual(10, sheet.PageSetup.RowBreaks[0]); + } + + [Test] + public void ColumnBreaksShiftWhenInsertedColumnBefore() + { + var wb = new XLWorkbook(); + IXLWorksheet sheet = wb.AddWorksheet("Sheet1"); + + sheet.PageSetup.AddVerticalPageBreak(10); + sheet.Column(5).InsertColumnsBefore(1); + Assert.AreEqual(11, sheet.PageSetup.ColumnBreaks[0]); + } + + [Test] + public void ColumnBreaksNotShiftWhenInsertedColumnAfter() + { + var wb = new XLWorkbook(); + IXLWorksheet sheet = wb.AddWorksheet("Sheet1"); + + sheet.PageSetup.AddVerticalPageBreak(10); + sheet.Column(15).InsertColumnsBefore(1); + Assert.AreEqual(10, sheet.PageSetup.ColumnBreaks[0]); + } + } +} \ No newline at end of file diff --git a/ClosedXML_Tests/Excel/Ranges/InsertingRangesTests.cs b/ClosedXML_Tests/Excel/Ranges/InsertingRangesTests.cs index dcda221..a614b55 100644 --- a/ClosedXML_Tests/Excel/Ranges/InsertingRangesTests.cs +++ b/ClosedXML_Tests/Excel/Ranges/InsertingRangesTests.cs @@ -81,5 +81,33 @@ Assert.AreEqual(XLColor.Fulvous, ws.Cell(4, 2).Style.Fill.BackgroundColor); Assert.AreEqual(XLColor.MacaroniAndCheese, ws.Cell(5, 2).Style.Fill.BackgroundColor); } + + [Test] + public void InsertingRowsPreservesComments() + { + var wb = new XLWorkbook(); + var ws = wb.Worksheets.Add("Sheet1"); + + ws.Cell("A1").SetValue("Insert Below"); + ws.Cell("A2").SetValue("Already existing cell"); + ws.Cell("A3").SetValue("Cell with comment").Comment.AddText("Comment here"); + + ws.Row(1).InsertRowsBelow(2); + Assert.AreEqual("Comment here", ws.Cell("A5").Comment.Text); + } + + [Test] + public void InsertingColumnsPreservesComments() + { + var wb = new XLWorkbook(); + var ws = wb.Worksheets.Add("Sheet1"); + + ws.Cell("A1").SetValue("Insert to the right"); + ws.Cell("B1").SetValue("Already existing cell"); + ws.Cell("C1").SetValue("Cell with comment").Comment.AddText("Comment here"); + + ws.Column(1).InsertColumnsAfter(2); + Assert.AreEqual("Comment here", ws.Cell("E1").Comment.Text); + } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Excel/Saving/SavingTests.cs b/ClosedXML_Tests/Excel/Saving/SavingTests.cs index 22aa34a..064c912 100644 --- a/ClosedXML_Tests/Excel/Saving/SavingTests.cs +++ b/ClosedXML_Tests/Excel/Saving/SavingTests.cs @@ -1,6 +1,8 @@ using ClosedXML.Excel; using NUnit.Framework; +using System.Globalization; using System.IO; +using System.Threading; namespace ClosedXML_Tests.Excel.Saving { @@ -13,6 +15,10 @@ using (var wb = new XLWorkbook()) { var sheet = wb.Worksheets.Add("TestSheet"); + + // Comments might cause duplicate VmlDrawing Id's - ensure it's tested: + sheet.Cell(1, 1).Comment.AddText("abc"); + var memoryStream = new MemoryStream(); wb.SaveAs(memoryStream, true); @@ -26,5 +32,24 @@ memoryStream.Dispose(); } } + + [Test] + public void CanSaveAndValidateFileInAnotherCulture() + { + string[] cultures = new string[] { "it", "de-AT" }; + + foreach (var culture in cultures) + { + Thread.CurrentThread.CurrentCulture = CultureInfo.GetCultureInfo(culture); + + using (var wb = new XLWorkbook()) + { + var memoryStream = new MemoryStream(); + var ws = wb.Worksheets.Add("Sheet1"); + + wb.SaveAs(memoryStream, true); + } + } + } } } diff --git a/ClosedXML_Tests/Excel/Styles/NumberFormatTests.cs b/ClosedXML_Tests/Excel/Styles/NumberFormatTests.cs new file mode 100644 index 0000000..cf3aa2b --- /dev/null +++ b/ClosedXML_Tests/Excel/Styles/NumberFormatTests.cs @@ -0,0 +1,33 @@ +using ClosedXML.Excel; +using NUnit.Framework; +using System; +using System.Data; +using System.Linq; + +namespace ClosedXML_Tests.Excel +{ + public class NumberFormatTests + { + [Test] + public void PreserveCellFormat() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + ws.Column(1).Style.NumberFormat.Format = "yy-MM-dd"; + + var table = new DataTable(); + table.Columns.Add("Date", typeof(DateTime)); + + for (int i = 0; i < 10; i++) + { + table.Rows.Add(new DateTime(2017, 1, 1).AddMonths(i)); + } + + ws.Cell("A1").InsertData(table.AsEnumerable()); + + Assert.AreEqual("yy-MM-dd", ws.Cell("A5").Style.DateFormat.Format); + } + } + } +} diff --git a/ClosedXML_Tests/Resource/Examples/Misc/AddingDataSet.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/AddingDataSet.xlsx index 8fd67be..1c3d359 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/AddingDataSet.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/AddingDataSet.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/AddingDataTableAsWorksheet.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/AddingDataTableAsWorksheet.xlsx index 53d5ab2..21de466 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/AddingDataTableAsWorksheet.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/AddingDataTableAsWorksheet.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/AdjustToContents.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/AdjustToContents.xlsx index 2fe73bc..26c33b7 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/AdjustToContents.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/AdjustToContents.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/CellValues.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/CellValues.xlsx index ebd1aae..a438a52 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/CellValues.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/CellValues.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/CopyingWorksheets.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/CopyingWorksheets.xlsx index 23582c4..027c1a7 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/CopyingWorksheets.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/CopyingWorksheets.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/DataTypes.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/DataTypes.xlsx index 5b29266..9b03a64 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/DataTypes.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/DataTypes.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/DataTypesUnderDifferentCulture.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/DataTypesUnderDifferentCulture.xlsx index ca68eee..0221170 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/DataTypesUnderDifferentCulture.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/DataTypesUnderDifferentCulture.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/InsertingTables.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/InsertingTables.xlsx index 29317a1..80c7c3d 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/InsertingTables.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/InsertingTables.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/SheetViews.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/SheetViews.xlsx new file mode 100644 index 0000000..723756a --- /dev/null +++ b/ClosedXML_Tests/Resource/Examples/Misc/SheetViews.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/ShowCase.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/ShowCase.xlsx index a9f74c5..a9d4499 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/ShowCase.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/ShowCase.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/PageSetup/TwoPages.xlsx b/ClosedXML_Tests/Resource/Examples/PageSetup/TwoPages.xlsx index 1e2deb7..76199d6 100644 --- a/ClosedXML_Tests/Resource/Examples/PageSetup/TwoPages.xlsx +++ b/ClosedXML_Tests/Resource/Examples/PageSetup/TwoPages.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx index c4025e5..5bc0db9 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/Examples/Ranges/UsingTables.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/UsingTables.xlsx index 50cd14f..6e89783 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/UsingTables.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/UsingTables.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Misc/FileWithDefaultStyleNameNotInEnglish.xlsx b/ClosedXML_Tests/Resource/Misc/FileWithDefaultStyleNameNotInEnglish.xlsx new file mode 100644 index 0000000..e94bc6a --- /dev/null +++ b/ClosedXML_Tests/Resource/Misc/FileWithDefaultStyleNameNotInEnglish.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Misc/FileWithMismatchSheetIdAndRelId.xlsx b/ClosedXML_Tests/Resource/Misc/FileWithMismatchSheetIdAndRelId.xlsx new file mode 100644 index 0000000..23c9a88 --- /dev/null +++ b/ClosedXML_Tests/Resource/Misc/FileWithMismatchSheetIdAndRelId.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Misc/LibreOfficeFileWithDates.xlsx b/ClosedXML_Tests/Resource/Misc/LibreOfficeFileWithDates.xlsx new file mode 100644 index 0000000..9556636 --- /dev/null +++ b/ClosedXML_Tests/Resource/Misc/LibreOfficeFileWithDates.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Misc/LoadFileWithCustomSheetViews.xlsx b/ClosedXML_Tests/Resource/Misc/LoadFileWithCustomSheetViews.xlsx new file mode 100644 index 0000000..e6d28c1 --- /dev/null +++ b/ClosedXML_Tests/Resource/Misc/LoadFileWithCustomSheetViews.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Misc/LoadPivotTables.xlsx b/ClosedXML_Tests/Resource/Misc/LoadPivotTables.xlsx new file mode 100644 index 0000000..6fad7fe --- /dev/null +++ b/ClosedXML_Tests/Resource/Misc/LoadPivotTables.xlsx Binary files differ