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/Cells/XLCell.cs b/ClosedXML/Excel/Cells/XLCell.cs index 7c60428..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; } } @@ -1618,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; @@ -1929,7 +1929,7 @@ return columnPart; } - internal void CopyValues(XLCell source) + internal void CopyValuesFrom(XLCell source) { _cellValue = source._cellValue; _dataType = source._dataType; @@ -1962,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/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/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/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/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_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs index bb05e45..e087af4 100644 --- a/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/Excel/XLWorkbook_Load.cs @@ -180,8 +180,16 @@ 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(); @@ -245,6 +253,7 @@ LoadColumnBreaks((ColumnBreaks)reader.LoadCurrentElement(), ws); else if (reader.ElementType == typeof(LegacyDrawing)) ws.LegacyDrawingId = (reader.LoadCurrentElement() as LegacyDrawing).Id.Value; + } reader.Close(); } @@ -472,60 +481,70 @@ pt.ErrorValueReplacement = pivotTableDefinition.ErrorCaption.Value; // Row labels - foreach (var rf in pivotTableDefinition.RowFields.Cast()) + if (pivotTableDefinition.RowFields != null) { - if (rf.Index.Value == -2) - pt.RowLabels.Add(XLConstants.PivotTableValuesSentinalLabel); - else if (rf.Index < pivotTableDefinition.PivotFields.Count) + foreach (var rf in pivotTableDefinition.RowFields.Cast()) { - var pf = pivotTableDefinition.PivotFields.ElementAt(rf.Index.Value) as PivotField; - if (pf != null && pf.Name != null) pt.RowLabels.Add(pf.Name.Value); + if (rf.Index.Value == -2) + pt.RowLabels.Add(XLConstants.PivotTableValuesSentinalLabel); + else if (rf.Index < pivotTableDefinition.PivotFields.Count) + { + var pf = pivotTableDefinition.PivotFields.ElementAt(rf.Index.Value) as PivotField; + if (pf != null && pf.Name != null) pt.RowLabels.Add(pf.Name.Value); + } } } // Column labels - foreach (var cf in pivotTableDefinition.ColumnFields.Cast()) + if (pivotTableDefinition.ColumnFields != null) { - if (cf.Index.Value == -2) - pt.ColumnLabels.Add(XLConstants.PivotTableValuesSentinalLabel); - - else if (cf.Index < pivotTableDefinition.PivotFields.Count) + foreach (var cf in pivotTableDefinition.ColumnFields.Cast()) { - var pf = pivotTableDefinition.PivotFields.ElementAt(cf.Index.Value) as PivotField; - if (pf != null && pf.Name != null) pt.ColumnLabels.Add(pf.Name.Value); + if (cf.Index.Value == -2) + 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 && pf.Name != null) pt.ColumnLabels.Add(pf.Name.Value); + } } } // Values - foreach (var df in pivotTableDefinition.DataFields.Cast()) + if (pivotTableDefinition.DataFields != null) { - if ((int)df.Field.Value == -2) - pt.Values.Add(XLConstants.PivotTableValuesSentinalLabel); - - else if (df.Field.Value < pivotTableDefinition.PivotFields.Count) + foreach (var df in pivotTableDefinition.DataFields.Cast()) { - var pf = pivotTableDefinition.PivotFields.ElementAt((int)df.Field.Value) as PivotField; - if (pf != null && pf.Name != null) + if ((int)df.Field.Value == -2) + pt.Values.Add(XLConstants.PivotTableValuesSentinalLabel); + + else if (df.Field.Value < pivotTableDefinition.PivotFields.Count) { - var pv = pt.Values.Add(pf.Name.Value, df.Name.Value); - if (df.NumberFormatId != null) pv.NumberFormat.SetNumberFormatId((int)df.NumberFormatId.Value); - if (df.Subtotal != null) pv = pv.SetSummaryFormula(df.Subtotal.Value.ToClosedXml()); - if (df.ShowDataAs != null) + var pf = pivotTableDefinition.PivotFields.ElementAt((int)df.Field.Value) as PivotField; + if (pf != null && pf.Name != null) { - var calculation = pv.Calculation; - calculation = df.ShowDataAs.Value.ToClosedXml(); - pv = pv.SetCalculation(calculation); - } - if (df.BaseField != null) { - var col = pt.SourceRange.Column(df.BaseField.Value + 1); + var pv = pt.Values.Add(pf.Name.Value, df.Name.Value); + if (df.NumberFormatId != null) pv.NumberFormat.SetNumberFormatId((int)df.NumberFormatId.Value); + if (df.Subtotal != null) pv = pv.SetSummaryFormula(df.Subtotal.Value.ToClosedXml()); + if (df.ShowDataAs != null) + { + var calculation = pv.Calculation; + calculation = df.ShowDataAs.Value.ToClosedXml(); + pv = pv.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(); + var items = col.CellsUsed() + .Select(c => c.Value) + .Skip(1) // Skip header column + .Distinct().ToList(); - pv.BaseField = col.FirstCell().GetValue(); - if (df.BaseItem != null) pv.BaseItem = items[(int)df.BaseItem.Value].ToString(); + pv.BaseField = col.FirstCell().GetValue(); + if (df.BaseItem != null) pv.BaseItem = items[(int)df.BaseItem.Value].ToString(); + } } } } @@ -536,7 +555,6 @@ } #endregion - } #region Comment Helpers @@ -1050,18 +1068,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) @@ -1075,6 +1086,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)) { @@ -1087,15 +1099,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); } } } @@ -1360,7 +1364,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(); @@ -1374,7 +1400,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) @@ -1917,8 +1943,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 || diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index 0c40d9d..9fc1d76 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 { @@ -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()); @@ -225,6 +238,7 @@ // 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()) @@ -276,8 +290,9 @@ 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); @@ -302,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 @@ -2097,10 +2119,9 @@ pf.Axis = PivotTableAxisValues.AxisPage; 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(); @@ -2607,7 +2628,15 @@ // 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.BuiltinId != null && c.BuiltinId.HasValue && c.BuiltinId.Value == 0)) - defaultFormatId = workbookStylesPart.Stylesheet.CellStyles.Elements().Single(c => c.BuiltinId != null && c.BuiltinId.HasValue && c.BuiltinId.Value == 0).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; else @@ -3647,6 +3676,26 @@ 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; @@ -4689,8 +4738,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/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 ea08869..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 9af9775..92d28a7 100644 --- a/ClosedXML_Examples/PivotTables/PivotTables.cs +++ b/ClosedXML_Examples/PivotTables/PivotTables.cs @@ -63,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"); @@ -96,8 +96,8 @@ } // 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"); @@ -105,6 +105,15 @@ pt.Values.Add("NumberOfOrders").SetSummaryFormula(XLPivotSummary.Sum);//.NumberFormat.Format = "#0.00"; pt.Values.Add("Quality").SetSummaryFormula(XLPivotSummary.Sum);//.NumberFormat.Format = "#0.00"; + // 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 bf8a300..eabbee3 100644 --- a/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -73,6 +73,7 @@ + @@ -163,6 +164,7 @@ + @@ -255,7 +257,9 @@ + + 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/Loading/LoadingTests.cs b/ClosedXML_Tests/Excel/Loading/LoadingTests.cs index 8398a20..d2e7069 100644 --- a/ClosedXML_Tests/Excel/Loading/LoadingTests.cs +++ b/ClosedXML_Tests/Excel/Loading/LoadingTests.cs @@ -19,7 +19,8 @@ { @"Misc\TableWithCustomTheme.xlsx", @"Misc\EmptyTable.xlsx", - @"Misc\LoadPivotTables.xlsx" + @"Misc\LoadPivotTables.xlsx", + @"Misc\LoadFileWithCustomSheetViews.xlsx" }; foreach (var file in files) @@ -96,5 +97,25 @@ } } } + + /// + /// 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/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/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 9b5b3c3..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/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/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/PivotTables/PivotTables.xlsx b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx index d28109f..04cd992 100644 --- a/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx +++ b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Misc/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