diff --git a/ClosedXML/Excel/CalcEngine/CalcEngine.cs b/ClosedXML/Excel/CalcEngine/CalcEngine.cs index bafa237..8cc752e 100644 --- a/ClosedXML/Excel/CalcEngine/CalcEngine.cs +++ b/ClosedXML/Excel/CalcEngine/CalcEngine.cs @@ -77,18 +77,20 @@ // skip leading equals sign if (_len > 0 && _expr[0] == '=') - { _ptr++; - } + + // skip leading +'s + while (_len > _ptr && _expr[_ptr] == '+') + _ptr++; // parse the expression var expr = ParseExpression(); // check for errors - if (_token.ID != TKID.END) - { - Throw(); - } + if (_token.ID == TKID.OPEN) + Throw("Unknown function: " + expr.LastParseItem); + else if (_token.ID != TKID.END) + Throw("Expected end of expression"); // optimize expression if (_optimize) @@ -113,10 +115,9 @@ /// public object Evaluate(string expression) { - var x = //Parse(expression); - _cache != null - ? _cache[expression] - : Parse(expression); + var x = _cache != null + ? _cache[expression] + : Parse(expression); return x.Evaluate(); } @@ -413,11 +414,11 @@ var pCnt = p == null ? 0 : p.Count; if (fnDef.ParmMin != -1 && pCnt < fnDef.ParmMin) { - Throw("Too few parameters."); + Throw(string.Format("Too few parameters for function '{0}'. Expected a minimum of {1} and a maximum of {2}.", id, fnDef.ParmMin, fnDef.ParmMax)); } if (fnDef.ParmMax != -1 && pCnt > fnDef.ParmMax) { - Throw("Too many parameters."); + Throw(string.Format("Too many parameters for function '{0}'.Expected a minimum of {1} and a maximum of {2}.", id, fnDef.ParmMin, fnDef.ParmMax)); } x = new FunctionExpression(fnDef, p); break; @@ -713,7 +714,7 @@ if (isEnclosed && disallowedSymbols.Contains(c)) break; - var allowedSymbols = new List() { '_' }; + var allowedSymbols = new List() { '_', '.' }; if (!isLetter && !isDigit && !(isEnclosed || allowedSymbols.Contains(c)) @@ -771,10 +772,10 @@ } // make sure the list was closed correctly - if (_token.ID != TKID.CLOSE) - { - Throw(); - } + if (_token.ID == TKID.OPEN) + Throw("Unknown function: " + expr.LastParseItem); + else if (_token.ID != TKID.CLOSE) + Throw("Syntax error: expected ')'"); // done return parms; diff --git a/ClosedXML/Excel/CalcEngine/Expression.cs b/ClosedXML/Excel/CalcEngine/Expression.cs index dcac3c0..9dfdf28 100644 --- a/ClosedXML/Excel/CalcEngine/Expression.cs +++ b/ClosedXML/Excel/CalcEngine/Expression.cs @@ -1,11 +1,17 @@ using System; -using System.Threading; using System.Collections; using System.Collections.Generic; using System.Globalization; +using System.Linq; +using System.Threading; namespace ClosedXML.Excel.CalcEngine { + internal abstract class ExpressionBase + { + public abstract string LastParseItem { get; } + } + /// /// Base class that represents parsed expressions. /// @@ -16,34 +22,39 @@ /// object val = expr.Evaluate(); /// /// - internal class Expression : IComparable + internal class Expression : ExpressionBase, IComparable { //--------------------------------------------------------------------------- + #region ** fields - internal Token _token; + internal readonly Token _token; - #endregion + #endregion ** fields //--------------------------------------------------------------------------- + #region ** ctors internal Expression() { _token = new Token(null, TKID.ATOM, TKTYPE.IDENTIFIER); } + internal Expression(object value) { _token = new Token(value, TKID.ATOM, TKTYPE.LITERAL); } + internal Expression(Token tk) { _token = tk; } - #endregion + #endregion ** ctors //--------------------------------------------------------------------------- + #region ** object model public virtual object Evaluate() @@ -54,14 +65,16 @@ } return _token.Value; } + public virtual Expression Optimize() { return this; } - #endregion + #endregion ** object model //--------------------------------------------------------------------------- + #region ** implicit converters public static implicit operator string(Expression x) @@ -69,6 +82,7 @@ var v = x.Evaluate(); return v == null ? string.Empty : v.ToString(); } + public static implicit operator double(Expression x) { // evaluate @@ -102,6 +116,7 @@ CultureInfo _ci = Thread.CurrentThread.CurrentCulture; return (double)Convert.ChangeType(v, typeof(double), _ci); } + public static implicit operator bool(Expression x) { // evaluate @@ -128,6 +143,7 @@ // handle everything else return (double)x == 0 ? false : true; } + public static implicit operator DateTime(Expression x) { // evaluate @@ -150,9 +166,10 @@ return (DateTime)Convert.ChangeType(v, typeof(DateTime), _ci); } - #endregion + #endregion ** implicit converters //--------------------------------------------------------------------------- + #region ** IComparable public int CompareTo(Expression other) @@ -197,15 +214,27 @@ return c1.CompareTo(c2); } - #endregion + #endregion ** IComparable + + //--------------------------------------------------------------------------- + + #region ** ExpressionBase + + public override string LastParseItem + { + get { return _token?.Value?.ToString() ?? "Unknown value"; } + } + + #endregion ** ExpressionBase } + /// /// Unary expression, e.g. +123 /// - class UnaryExpression : Expression + internal class UnaryExpression : Expression { // ** fields - Expression _expr; + private Expression _expr; // ** ctor public UnaryExpression(Token tk, Expression expr) : base(tk) @@ -220,11 +249,13 @@ { case TKID.ADD: return +(double)_expr; + case TKID.SUB: return -(double)_expr; } throw new ArgumentException("Bad expression."); } + public override Expression Optimize() { _expr = _expr.Optimize(); @@ -232,20 +263,27 @@ ? new Expression(this.Evaluate()) : this; } + + public override string LastParseItem + { + get { return _expr.LastParseItem; } + } } + /// /// Binary expression, e.g. 1+2 /// - class BinaryExpression : Expression + internal class BinaryExpression : Expression { // ** fields - Expression _lft; - Expression _rgt; + private Expression _lft; + + private Expression _rgt; // ** ctor public BinaryExpression(Token tk, Expression exprLeft, Expression exprRight) : base(tk) { - _lft = exprLeft; + _lft = exprLeft; _rgt = exprRight; } @@ -272,18 +310,25 @@ { case TKID.CONCAT: return (string)_lft + (string)_rgt; + case TKID.ADD: return (double)_lft + (double)_rgt; + case TKID.SUB: return (double)_lft - (double)_rgt; + case TKID.MUL: return (double)_lft * (double)_rgt; + case TKID.DIV: return (double)_lft / (double)_rgt; + case TKID.DIVINT: return (double)(int)((double)_lft / (double)_rgt); + case TKID.MOD: return (double)(int)((double)_lft % (double)_rgt); + case TKID.POWER: var a = (double)_lft; var b = (double)_rgt; @@ -297,6 +342,7 @@ } throw new ArgumentException("Bad expression."); } + public override Expression Optimize() { _lft = _lft.Optimize(); @@ -305,20 +351,27 @@ ? new Expression(this.Evaluate()) : this; } + + public override string LastParseItem + { + get { return _rgt.LastParseItem; } + } } + /// /// Function call expression, e.g. sin(0.5) /// - class FunctionExpression : Expression + internal class FunctionExpression : Expression { // ** fields - FunctionDefinition _fn; - List _parms; + private readonly FunctionDefinition _fn; + + private readonly List _parms; // ** ctor internal FunctionExpression() - { - } + { } + public FunctionExpression(FunctionDefinition function, List parms) { _fn = function; @@ -330,6 +383,7 @@ { return _fn.Function(_parms); } + public override Expression Optimize() { bool allLits = true; @@ -349,33 +403,46 @@ ? new Expression(this.Evaluate()) : this; } + + public override string LastParseItem + { + get { return _parms.Last().LastParseItem; } + } } + /// /// Simple variable reference. /// - class VariableExpression : Expression + internal class VariableExpression : Expression { - Dictionary _dct; - string _name; + private readonly Dictionary _dct; + private readonly string _name; public VariableExpression(Dictionary dct, string name) { _dct = dct; _name = name; } + public override object Evaluate() { return _dct[_name]; } + + public override string LastParseItem + { + get { return _name; } + } } + /// /// Expression that represents an external object. /// - class XObjectExpression : + internal class XObjectExpression : Expression, IEnumerable { - object _value; + private readonly object _value; // ** ctor internal XObjectExpression(object value) @@ -398,18 +465,29 @@ // return raw object return _value; } + public IEnumerator GetEnumerator() { return (_value as IEnumerable).GetEnumerator(); } + + public override string LastParseItem + { + get { return Value.ToString(); } + } } /// /// Expression that represents an omitted parameter. /// - class EmptyValueExpression : Expression + internal class EmptyValueExpression : Expression { internal EmptyValueExpression() { } + + public override string LastParseItem + { + get { return ""; } + } } /// diff --git a/ClosedXML/Excel/CalcEngine/Functions/Lookup.cs b/ClosedXML/Excel/CalcEngine/Functions/Lookup.cs index 8a9ac19..964e72b 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/Lookup.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/Lookup.cs @@ -15,7 +15,7 @@ //ce.RegisterFunction("COLUMNS", , Columns); // Returns the number of columns in a reference //ce.RegisterFunction("FORMULATEXT", , Formulatext); // Returns the formula at the given reference as text //ce.RegisterFunction("GETPIVOTDATA", , Getpivotdata); // Returns data stored in a PivotTable report - ce.RegisterFunction("HLOOKUP", 4, Hlookup); // Looks in the top row of an array and returns the value of the indicated cell + ce.RegisterFunction("HLOOKUP", 3, 4, Hlookup); // Looks in the top row of an array and returns the value of the indicated cell //ce.RegisterFunction("HYPERLINK", , Hyperlink); // Creates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet //ce.RegisterFunction("INDEX", , Index); // Uses an index to choose a value from a reference or array //ce.RegisterFunction("INDIRECT", , Indirect); // Returns a reference indicated by a text value @@ -26,7 +26,7 @@ //ce.RegisterFunction("ROWS", , Rows); // Returns the number of rows in a reference //ce.RegisterFunction("RTD", , Rtd); // Retrieves real-time data from a program that supports COM automation //ce.RegisterFunction("TRANSPOSE", , Transpose); // Returns the transpose of an array - ce.RegisterFunction("VLOOKUP", 4, Vlookup); // Looks in the first column of an array and moves across the row to return the value of a cell + ce.RegisterFunction("VLOOKUP", 3, 4, Vlookup); // Looks in the first column of an array and moves across the row to return the value of a cell } private static object Hlookup(List p) @@ -34,20 +34,25 @@ var lookup_value = p[0]; var table_array = p[1] as XObjectExpression; + if (table_array == null) + throw new ArgumentException("table_array has to be a range"); + var range_reference = table_array.Value as CellRangeReference; + if (range_reference == null) + throw new ArgumentException("table_array has to be a range"); + var range = range_reference.Range; var row_index_num = (int)(p[2]); - var range_lookup = p.Count < 4 || (bool)(p[3]); - - if (table_array == null || range_reference == null) - throw new ApplicationException("table_array has to be a range"); + var range_lookup = p.Count < 4 + || p[3] is EmptyValueExpression + || (bool)(p[3]); if (row_index_num < 1) - throw new ApplicationException("col_index_num has to be positive"); + throw new ArgumentOutOfRangeException("Row index", "row_index_num has to be positive"); if (row_index_num > range.RowCount()) - throw new ApplicationException("col_index_num must be smaller or equal to the number of rows in the table array"); + throw new ArgumentOutOfRangeException("Row index", "row_index_num has to be positive"); IXLRangeColumn matching_column; matching_column = range.FindColumn(c => !c.Cell(1).IsEmpty() && new Expression(c.Cell(1).Value).CompareTo(lookup_value) == 0); @@ -67,7 +72,7 @@ } if (matching_column == null) - throw new ApplicationException("No matches found."); + throw new ArgumentException("No matches found."); return matching_column .Cell(row_index_num) @@ -79,20 +84,25 @@ var lookup_value = p[0]; var table_array = p[1] as XObjectExpression; + if (table_array == null) + throw new ArgumentException("table_array has to be a range"); + var range_reference = table_array.Value as CellRangeReference; + if (range_reference == null) + throw new ArgumentException("table_array has to be a range"); + var range = range_reference.Range; var col_index_num = (int)(p[2]); - var range_lookup = p.Count < 4 || (bool)(p[3]); - - if (table_array == null || range_reference == null) - throw new ApplicationException("table_array has to be a range"); + var range_lookup = p.Count < 4 + || p[3] is EmptyValueExpression + || (bool)(p[3]); if (col_index_num < 1) - throw new ApplicationException("col_index_num has to be positive"); + throw new ArgumentOutOfRangeException("Column index", "col_index_num has to be positive"); if (col_index_num > range.ColumnCount()) - throw new ApplicationException("col_index_num must be smaller or equal to the number of columns in the table array"); + throw new ArgumentOutOfRangeException("Column index", "col_index_num must be smaller or equal to the number of columns in the table array"); IXLRangeRow matching_row; matching_row = range.FindRow(r => !r.Cell(1).IsEmpty() && new Expression(r.Cell(1).Value).CompareTo(lookup_value) == 0); @@ -112,7 +122,7 @@ } if (matching_row == null) - throw new ApplicationException("No matches found."); + throw new ArgumentException("No matches found."); return matching_row .Cell(col_index_num) diff --git a/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs b/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs index c4e86ca..12e0995 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs @@ -1,8 +1,8 @@ +using ClosedXML.Excel.CalcEngine.Functions; using System; using System.Collections; using System.Collections.Generic; using System.Linq; -using ClosedXML.Excel.CalcEngine.Functions; namespace ClosedXML.Excel.CalcEngine { @@ -29,7 +29,8 @@ ce.RegisterFunction("EXP", 1, Exp); ce.RegisterFunction("FACT", 1, Fact); ce.RegisterFunction("FACTDOUBLE", 1, FactDouble); - ce.RegisterFunction("FLOOR", 1, Floor); + ce.RegisterFunction("FLOOR", 1, 2, Floor); + ce.RegisterFunction("FLOOR.MATH", 1, 3, FloorMath); ce.RegisterFunction("GCD", 1, 255, Gcd); ce.RegisterFunction("INT", 1, Int); ce.RegisterFunction("LCM", 1, 255, Lcm); @@ -120,12 +121,44 @@ private static object Floor(List p) { - return Math.Floor(p[0]); + double number = p[0]; + double significance = 1; + if (p.Count > 1) + significance = p[1]; + + if (significance < 0) + { + number = -number; + significance = -significance; + + return -Math.Floor(number / significance) * significance; + } + else if (significance == 1) + return Math.Floor(number); + else + return Math.Floor(number / significance) * significance; + } + + private static object FloorMath(List p) + { + double number = p[0]; + double significance = 1; + if (p.Count > 1) significance = p[1]; + + double mode = 0; + if (p.Count > 2) mode = p[2]; + + if (number >= 0) + return Math.Floor(number / Math.Abs(significance)) * Math.Abs(significance); + else if (mode >= 0) + return Math.Floor(number / Math.Abs(significance)) * Math.Abs(significance); + else + return -Math.Floor(-number / Math.Abs(significance)) * Math.Abs(significance); } private static object Int(List p) { - return (int) ((double) p[0]); + return (int)((double)p[0]); } private static object Ln(List p) @@ -135,7 +168,7 @@ private static object Log(List p) { - var lbase = p.Count > 1 ? (double) p[1] : 10; + var lbase = p.Count > 1 ? (double)p[1] : 10; return Math.Log(p[0], lbase); } @@ -161,7 +194,7 @@ private static object RandBetween(List p) { - return _rnd.Next((int) (double) p[0], (int) (double) p[1]); + return _rnd.Next((int)(double)p[0], (int)(double)p[1]); } private static object Sign(List p) @@ -240,42 +273,42 @@ private static object Trunc(List p) { - return (double) (int) ((double) p[0]); + return (double)(int)((double)p[0]); } public static double DegreesToRadians(double degrees) { - return (Math.PI/180.0)*degrees; + return (Math.PI / 180.0) * degrees; } public static double RadiansToDegrees(double radians) { - return (180.0/Math.PI)*radians; + return (180.0 / Math.PI) * radians; } public static double GradsToRadians(double grads) { - return (grads/200.0)*Math.PI; + return (grads / 200.0) * Math.PI; } public static double RadiansToGrads(double radians) { - return (radians/Math.PI)*200.0; + return (radians / Math.PI) * 200.0; } public static double DegreesToGrads(double degrees) { - return (degrees/9.0)*10.0; + return (degrees / 9.0) * 10.0; } public static double GradsToDegrees(double grads) { - return (grads/10.0)*9.0; + return (grads / 10.0) * 9.0; } public static double ASinh(double x) { - return (Math.Log(x + Math.Sqrt(x*x + 1.0))); + return (Math.Log(x + Math.Sqrt(x * x + 1.0))); } private static object Acosh(List p) @@ -295,8 +328,8 @@ private static object Combin(List p) { - Int32 n = (int) p[0]; - Int32 k = (int) p[1]; + Int32 n = (int)p[0]; + Int32 k = (int)p[1]; return XLMath.Combin(n, k); } @@ -305,8 +338,6 @@ return p[0] * (180.0 / Math.PI); } - - private static object Fact(List p) { var num = Math.Floor(p[0]); @@ -348,15 +379,15 @@ private static int Lcm(int a, int b) { if (a == 0 || b == 0) return 0; - return a * ( b / Gcd(a, b)); + return a * (b / Gcd(a, b)); } private static object Mod(List p) { - Int32 n = (int)Math.Abs(p[0]); - Int32 d = (int)p[1]; - var ret = n % d; - return d < 0 ? ret * -1 : ret; + double number = p[0]; + double divisor = p[1]; + + return number - Math.Floor(number / divisor) * divisor; } private static object MRound(List p) @@ -479,7 +510,6 @@ temp = Math.Round(temp, 0, MidpointRounding.AwayFromZero); return temp * Math.Pow(10, digits); } - } private static object RoundDown(List p) @@ -512,7 +542,7 @@ var obj = p[3] as XObjectExpression; if (obj == null) - return p[3] * Math.Pow(x , n); + return p[3] * Math.Pow(x, n); Double total = 0; Int32 i = 0; @@ -540,26 +570,37 @@ { case 1: return tally.Average(); + case 2: return tally.Count(true); + case 3: return tally.Count(false); + case 4: return tally.Max(); + case 5: return tally.Min(); + case 6: return tally.Product(); + case 7: return tally.Std(); + case 8: return tally.StdP(); + case 9: return tally.Sum(); + case 10: return tally.Var(); + case 11: return tally.VarP(); + default: throw new ArgumentException("Function not supported."); } @@ -591,19 +632,18 @@ } } - return C; } private static double[,] GetArray(Expression expression) { var oExp1 = expression as XObjectExpression; - if (oExp1 == null) return new [,]{{(Double)expression}}; + if (oExp1 == null) return new[,] { { (Double)expression } }; var range = (oExp1.Value as CellRangeReference).Range; var rowCount = range.RowCount(); var columnCount = range.ColumnCount(); - var arr = new double[rowCount,columnCount]; + var arr = new double[rowCount, columnCount]; for (int row = 0; row < rowCount; row++) { diff --git a/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/Excel/Cells/XLCell.cs index 8aaea02..ae3d84c 100644 --- a/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/Excel/Cells/XLCell.cs @@ -1,4 +1,4 @@ -using FastMember; +using FastMember; using System; using System.Collections; using System.Collections.Generic; @@ -221,6 +221,9 @@ public IXLCell SetValue(T value) { + if (value == null) + return this.Clear(XLClearOptions.Contents); + FormulaA1 = String.Empty; _richText = null; var style = GetStyleForRead(); @@ -438,6 +441,8 @@ if (value as XLCells != null) throw new ArgumentException("Cannot assign IXLCells object to the cell value."); + if (SetTableHeader(value)) return; + if (SetRangeRows(value)) return; if (SetRangeColumns(value)) return; @@ -1487,6 +1492,23 @@ #endregion IXLStylized Members + private bool SetTableHeader(object value) + { + foreach (var table in Worksheet.Tables.Where(t => t.ShowHeaderRow)) + { + var cells = table.HeadersRow().CellsUsed(c => c.Address.Equals(this.Address)); + if (cells.Any()) + { + var oldName = cells.First().GetString(); + var field = table.Field(oldName); + field.Name = value.ToString(); + return true; + } + } + + return false; + } + private bool SetRangeColumns(object value) { var columns = value as XLRangeColumns; diff --git a/ClosedXML/Excel/Columns/XLColumn.cs b/ClosedXML/Excel/Columns/XLColumn.cs index b8036d4..f106457 100644 --- a/ClosedXML/Excel/Columns/XLColumn.cs +++ b/ClosedXML/Excel/Columns/XLColumn.cs @@ -786,5 +786,15 @@ return base.IsEmpty(includeFormats); } + + public Boolean IsEntireRow() + { + return false; + } + + public Boolean IsEntireColumn() + { + return true; + } } } diff --git a/ClosedXML/Excel/NamedRanges/XLNamedRanges.cs b/ClosedXML/Excel/NamedRanges/XLNamedRanges.cs index a7b191f..f3f7304 100644 --- a/ClosedXML/Excel/NamedRanges/XLNamedRanges.cs +++ b/ClosedXML/Excel/NamedRanges/XLNamedRanges.cs @@ -69,10 +69,16 @@ if (!match.Success) { - if (Worksheet == null || !XLHelper.NamedRangeReferenceRegex.Match(Worksheet.Range(rangeAddress).ToString()).Success) - throw new ArgumentException("For named ranges in the workbook scope, specify the sheet name in the reference."); + var range = Worksheet?.Range(rangeAddress) ?? Workbook.Range(rangeAddress); + if (range == null) + throw new ArgumentException(string.Format("The range address '{0}' for the named range '{1}' is not a valid range.", rangeAddress, rangeName)); else - rangeAddress = Worksheet.Range(rangeAddress).ToString(); + { + if (Worksheet == null || !XLHelper.NamedRangeReferenceRegex.Match(range.ToString()).Success) + throw new ArgumentException("For named ranges in the workbook scope, specify the sheet name in the reference."); + else + rangeAddress = Worksheet.Range(rangeAddress).ToString(); + } } } diff --git a/ClosedXML/Excel/Ranges/IXLRangeBase.cs b/ClosedXML/Excel/Ranges/IXLRangeBase.cs index 8129cfa..064d975 100644 --- a/ClosedXML/Excel/Ranges/IXLRangeBase.cs +++ b/ClosedXML/Excel/Ranges/IXLRangeBase.cs @@ -250,6 +250,10 @@ Boolean IsEmpty(Boolean includeFormats); + Boolean IsEntireRow(); + + Boolean IsEntireColumn(); + IXLPivotTable CreatePivotTable(IXLCell targetCell); IXLPivotTable CreatePivotTable(IXLCell targetCell, String name); diff --git a/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/Excel/Ranges/XLRangeBase.cs index 8f3f613..b08cda8 100644 --- a/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -86,8 +86,6 @@ #region Public properties - //public XLRangeAddress RangeAddress { get; protected set; } - private XLRangeAddress _rangeAddress; public XLRangeAddress RangeAddress @@ -519,6 +517,18 @@ CellsUsed(includeFormats).Cast().Any(c => c.IsEmpty(includeFormats)); } + public Boolean IsEntireRow() + { + return RangeAddress.FirstAddress.ColumnNumber == 1 + && RangeAddress.LastAddress.ColumnNumber == XLHelper.MaxColumnNumber; + } + + public Boolean IsEntireColumn() + { + return RangeAddress.FirstAddress.RowNumber == 1 + && RangeAddress.LastAddress.RowNumber == XLHelper.MaxRowNumber; + } + #endregion IXLRangeBase Members #region IXLStylized Members @@ -1111,10 +1121,10 @@ { for (int co = lastColumn; co >= firstColumn; co--) { + int newColumn = co + numberOfColumns; for (int ro = lastRow; ro >= firstRow; ro--) { var oldKey = new XLAddress(Worksheet, ro, co, false, false); - int newColumn = co + numberOfColumns; var newKey = new XLAddress(Worksheet, ro, newColumn, false, false); var oldCell = Worksheet.Internals.CellsCollection.GetCell(ro, co) ?? Worksheet.Cell(oldKey); @@ -1125,6 +1135,11 @@ cellsToInsert.Add(newKey, newCell); cellsToDelete.Add(oldKey); } + + if (this.IsEntireColumn()) + { + Worksheet.Column(newColumn).Width = Worksheet.Column(co).Width; + } } } } @@ -1343,10 +1358,11 @@ { for (int ro = lastRow; ro >= firstRow; ro--) { + int newRow = ro + numberOfRows; + for (int co = lastColumn; co >= firstColumn; co--) { var oldKey = new XLAddress(Worksheet, ro, co, false, false); - int newRow = ro + numberOfRows; var newKey = new XLAddress(Worksheet, newRow, co, false, false); var oldCell = Worksheet.Internals.CellsCollection.GetCell(ro, co); if (oldCell != null) @@ -1358,6 +1374,10 @@ cellsToDelete.Add(oldKey); } } + if (this.IsEntireRow()) + { + Worksheet.Row(newRow).Height = Worksheet.Row(ro).Height; + } } } } diff --git a/ClosedXML/Excel/Rows/IXLRow.cs b/ClosedXML/Excel/Rows/IXLRow.cs index 3bec732..70816fb 100644 --- a/ClosedXML/Excel/Rows/IXLRow.cs +++ b/ClosedXML/Excel/Rows/IXLRow.cs @@ -15,6 +15,11 @@ Double Height { get; set; } /// + /// Clears the height for the row and defaults it to the spreadsheet row height. + /// + void ClearHeight(); + + /// /// Deletes this row and shifts the rows below this one accordingly. /// void Delete(); diff --git a/ClosedXML/Excel/Rows/XLRow.cs b/ClosedXML/Excel/Rows/XLRow.cs index 944cb17..721e29e 100644 --- a/ClosedXML/Excel/Rows/XLRow.cs +++ b/ClosedXML/Excel/Rows/XLRow.cs @@ -1,8 +1,7 @@ using System; using System.Collections.Generic; -using System.Linq; using System.Drawing; - +using System.Linq; namespace ClosedXML.Excel { @@ -15,7 +14,7 @@ private Boolean _isHidden; private Int32 _outlineLevel; - #endregion + #endregion Private fields #region Constructor @@ -44,7 +43,7 @@ _height = row._height; IsReference = row.IsReference; if (IsReference) - SubscribeToShiftedRows((range, rowShifted) => this.WorksheetRangeShiftedRows(range, rowShifted)); + SubscribeToShiftedRows((range, rowShifted) => this.WorksheetRangeShiftedRows(range, rowShifted)); _collapsed = row._collapsed; _isHidden = row._isHidden; @@ -53,7 +52,7 @@ SetStyle(row.GetStyleId()); } - #endregion + #endregion Constructor public Boolean IsReference { get; private set; } @@ -108,6 +107,7 @@ #region IXLRow Members private Boolean _loading; + public Boolean Loading { get { return IsReference ? Worksheet.Internals.RowsCollection[RowNumber()].Loading : _loading; } @@ -121,6 +121,7 @@ } public Boolean HeightChanged { get; private set; } + public Double Height { get { return IsReference ? Worksheet.Internals.RowsCollection[RowNumber()].Height : _height; } @@ -136,6 +137,12 @@ } } + public void ClearHeight() + { + Height = Worksheet.RowHeight; + HeightChanged = false; + } + public void Delete() { int rowNumber = RowNumber(); @@ -291,7 +298,7 @@ foreach (IXLRichString rt in c.RichText) { String formattedString = rt.Text; - var arr = formattedString.Split(new[] {Environment.NewLine}, StringSplitOptions.None); + var arr = formattedString.Split(new[] { Environment.NewLine }, StringSplitOptions.None); Int32 arrCount = arr.Count(); for (Int32 i = 0; i < arrCount; i++) { @@ -305,7 +312,7 @@ else { String formattedString = c.GetFormattedString(); - var arr = formattedString.Split(new[] {Environment.NewLine}, StringSplitOptions.None); + var arr = formattedString.Split(new[] { Environment.NewLine }, StringSplitOptions.None); Int32 arrCount = arr.Count(); for (Int32 i = 0; i < arrCount; i++) { @@ -338,7 +345,7 @@ } } else - thisHeight = c.Style.Font.GetHeight( fontCache); + thisHeight = c.Style.Font.GetHeight(fontCache); if (thisHeight >= maxHeight) { @@ -520,15 +527,15 @@ IXLRangeRow IXLRow.CopyTo(IXLCell target) { using (var asRange = AsRange()) - using (var copy = asRange.CopyTo(target)) - return copy.Row(1); + using (var copy = asRange.CopyTo(target)) + return copy.Row(1); } IXLRangeRow IXLRow.CopyTo(IXLRangeBase target) { using (var asRange = AsRange()) - using (var copy = asRange.CopyTo(target)) - return copy.Row(1); + using (var copy = asRange.CopyTo(target)) + return copy.Row(1); } public IXLRow CopyTo(IXLRow row) @@ -581,7 +588,7 @@ return Row(FirstCellUsed(includeFormats), LastCellUsed(includeFormats)); } - #endregion + #endregion IXLRow Members public override XLRange AsRange() { @@ -676,7 +683,7 @@ return RowShift(step * -1); } - #endregion + #endregion XLRow Above #region XLRow Below @@ -700,7 +707,7 @@ return RowShift(step); } - #endregion + #endregion XLRow Below public new Boolean IsEmpty() { @@ -715,6 +722,14 @@ return base.IsEmpty(includeFormats); } + public Boolean IsEntireRow() + { + return true; + } + public Boolean IsEntireColumn() + { + return false; + } } } diff --git a/ClosedXML/Excel/Tables/XLTable.cs b/ClosedXML/Excel/Tables/XLTable.cs index 3a3bc84..076a2a8 100644 --- a/ClosedXML/Excel/Tables/XLTable.cs +++ b/ClosedXML/Excel/Tables/XLTable.cs @@ -1,4 +1,4 @@ -using System; +using System; using System.Collections.Generic; using System.Linq; using System.Text; @@ -106,6 +106,16 @@ } } + internal void RenameField(String oldName, String newName) + { + if (!_fieldNames.ContainsKey(oldName)) + throw new ArgumentException("The field does not exist in this table", "oldName"); + + var field = _fieldNames[oldName]; + _fieldNames.Remove(oldName); + _fieldNames.Add(newName, field); + } + internal String RelId { get; set; } diff --git a/ClosedXML/Excel/Tables/XLTableField.cs b/ClosedXML/Excel/Tables/XLTableField.cs index 744700f..89ee2bb 100644 --- a/ClosedXML/Excel/Tables/XLTableField.cs +++ b/ClosedXML/Excel/Tables/XLTableField.cs @@ -1,4 +1,4 @@ -using System; +using System; namespace ClosedXML.Excel { @@ -26,6 +26,7 @@ if (table.ShowHeaderRow) table.HeadersRow().Cell(Index + 1).SetValue(value); + table.RenameField(name, value); name = value; } } diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs index 4b5ab28..4490d5a 100644 --- a/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/Excel/XLWorkbook_Load.cs @@ -271,11 +271,15 @@ #region LoadTables - foreach (TableDefinitionPart tablePart in wsPart.TableDefinitionParts) + foreach (var tablePart in wsPart.TableDefinitionParts) { var dTable = tablePart.Table; - string reference = dTable.Reference.Value; - XLTable xlTable = ws.Range(reference).CreateTable(dTable.Name, false) as XLTable; + String reference = dTable.Reference.Value; + String tableName = dTable?.Name ?? dTable.DisplayName ?? string.Empty; + if (String.IsNullOrWhiteSpace(tableName)) + throw new InvalidDataException("The table name is missing."); + + XLTable xlTable = ws.Range(reference).CreateTable(tableName, false) as XLTable; if (dTable.HeaderRowCount != null && dTable.HeaderRowCount == 0) { xlTable._showHeaderRow = false; @@ -1076,7 +1080,7 @@ else { if (!Worksheet(Int32.Parse(localSheetId) + 1).NamedRanges.Any(nr => nr.Name == name)) - Worksheet(Int32.Parse(localSheetId) + 1).NamedRanges.Add(name, text, comment).Visible = visible; + (Worksheet(Int32.Parse(localSheetId) + 1).NamedRanges as XLNamedRanges).Add(name, text, comment, true).Visible = visible; } } } @@ -1087,7 +1091,6 @@ private IEnumerable validateDefinedNames(IEnumerable definedNames) { - var fixedNames = new List(); var sb = new StringBuilder(); foreach (string testName in definedNames) { diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index d3026f1..da45762 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -142,9 +142,10 @@ private void DeleteSheetAndDependencies(WorkbookPart wbPart, string sheetId) { //Get the SheetToDelete from workbook.xml - Sheet worksheet = wbPart.Workbook.Descendants().Where(s => s.Id == sheetId).FirstOrDefault(); + Sheet worksheet = wbPart.Workbook.Descendants().FirstOrDefault(s => s.Id == sheetId); if (worksheet == null) - { } + return; + string sheetName = worksheet.Name; // Get the pivot Table Parts @@ -154,8 +155,8 @@ { PivotCacheDefinition pvtCacheDef = Item.PivotCacheDefinition; //Check if this CacheSource is linked to SheetToDelete - var pvtCahce = pvtCacheDef.Descendants().Where(s => s.WorksheetSource.Sheet == sheetName); - if (pvtCahce.Count() > 0) + var pvtCache = pvtCacheDef.Descendants().Where(s => s.WorksheetSource.Sheet == sheetName); + if (pvtCache.Any()) { pvtTableCacheDefinationPart.Add(Item, Item.ToString()); } @@ -178,7 +179,7 @@ { List defNamesToDelete = new List(); - foreach (DefinedName Item in definedNames) + foreach (var Item in definedNames.OfType()) { // This condition checks to delete only those names which are part of Sheet in question if (Item.Text.Contains(worksheet.Name + "!")) @@ -201,19 +202,15 @@ var calChainEntries = calChainPart.CalculationChain.Descendants().Where(c => c.SheetId == sheetId); List calcsToDelete = new List(); foreach (CalculationCell Item in calChainEntries) - { calcsToDelete.Add(Item); - } + foreach (CalculationCell Item in calcsToDelete) - { Item.Remove(); - } - if (calChainPart.CalculationChain.Count() == 0) - { + + if (!calChainPart.CalculationChain.Any()) wbPart.DeletePart(calChainPart); - } } } @@ -350,6 +347,9 @@ GenerateCustomFilePropertiesPartContent(customFilePropertiesPart); } SetPackageProperties(document); + + // Clear list of deleted worksheets to prevent errors on multiple saves + worksheets.Deleted.Clear(); } private void DeleteComments(WorksheetPart worksheetPart, XLWorksheet worksheet, SaveContext context) diff --git a/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/Excel/XLWorksheet.cs index 033fd27..453e566 100644 --- a/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/Excel/XLWorksheet.cs @@ -606,11 +606,18 @@ } } - foreach (IXLNamedRange r in NamedRanges) + foreach (var nr in NamedRanges) { var ranges = new XLRanges(); - r.Ranges.ForEach(ranges.Add); - targetSheet.NamedRanges.Add(r.Name, ranges); + foreach (var r in nr.Ranges) + { + if (this == r.Worksheet) + // Named ranges on the source worksheet have to point to the new destination sheet + ranges.Add(targetSheet.Range(r.RangeAddress.FirstAddress.RowNumber, r.RangeAddress.FirstAddress.ColumnNumber, r.RangeAddress.LastAddress.RowNumber, r.RangeAddress.LastAddress.ColumnNumber)); + else + ranges.Add(r); + } + targetSheet.NamedRanges.Add(nr.Name, ranges); } foreach (XLTable t in Tables.Cast()) @@ -1569,5 +1576,14 @@ { return Pictures.Add(imageFile, name); } + public Boolean IsEntireRow() + { + return true; + } + + public Boolean IsEntireColumn() + { + return true; + } } } diff --git a/ClosedXML/Excel/XLWorksheets.cs b/ClosedXML/Excel/XLWorksheets.cs index e3f42b7..9061e1d 100644 --- a/ClosedXML/Excel/XLWorksheets.cs +++ b/ClosedXML/Excel/XLWorksheets.cs @@ -180,7 +180,8 @@ { if (String.IsNullOrWhiteSpace(oldSheetName) || !_worksheets.ContainsKey(oldSheetName)) return; - if (_worksheets.Any(ws1 => ws1.Key.Equals(newSheetName, StringComparison.OrdinalIgnoreCase))) + if (!oldSheetName.Equals(newSheetName, StringComparison.OrdinalIgnoreCase) + && _worksheets.Any(ws1 => ws1.Key.Equals(newSheetName, StringComparison.OrdinalIgnoreCase))) throw new ArgumentException(String.Format("A worksheet with the same name ({0}) has already been added.", newSheetName), nameof(newSheetName)); var ws = _worksheets[oldSheetName]; diff --git a/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML_Tests/ClosedXML_Tests.csproj index d1ba111..c9b60d4 100644 --- a/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -80,6 +80,7 @@ + @@ -288,6 +289,7 @@ + diff --git a/ClosedXML_Tests/Excel/CalcEngine/LookupTests.cs b/ClosedXML_Tests/Excel/CalcEngine/LookupTests.cs index 60427e6..7d39689 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/LookupTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/LookupTests.cs @@ -106,6 +106,12 @@ value = workbook.Evaluate("=VLOOKUP(3,Data!$B$2:$I$71,8,TRUE)"); Assert.AreEqual(179.64, value); + value = workbook.Evaluate("=VLOOKUP(3,Data!$B$2:$I$71,8)"); + Assert.AreEqual(179.64, value); + + value = workbook.Evaluate("=VLOOKUP(3,Data!$B$2:$I$71,8,)"); + Assert.AreEqual(179.64, value); + value = workbook.Evaluate("=VLOOKUP(14.5,Data!$B$2:$I$71,8,TRUE)"); Assert.AreEqual(174.65, value); diff --git a/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs b/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs new file mode 100644 index 0000000..5012bb5 --- /dev/null +++ b/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs @@ -0,0 +1,123 @@ +using ClosedXML.Excel; +using NUnit.Framework; +using System; + +namespace ClosedXML_Tests.Excel.CalcEngine +{ + [TestFixture] + public class MathTrigTests + { + private readonly double tolerance = 1e-10; + + [Test] + public void Floor() + { + Object actual; + + actual = XLWorkbook.EvaluateExpr(@"FLOOR(1.2)"); + Assert.AreEqual(1, actual); + + actual = XLWorkbook.EvaluateExpr(@"FLOOR(1.7)"); + Assert.AreEqual(1, actual); + + actual = XLWorkbook.EvaluateExpr(@"FLOOR(-1.7)"); + Assert.AreEqual(-2, actual); + + actual = XLWorkbook.EvaluateExpr(@"FLOOR(1.2, 1)"); + Assert.AreEqual(1, actual); + + actual = XLWorkbook.EvaluateExpr(@"FLOOR(1.7, 1)"); + Assert.AreEqual(1, actual); + + actual = XLWorkbook.EvaluateExpr(@"FLOOR(-1.7, 1)"); + Assert.AreEqual(-2, actual); + + actual = XLWorkbook.EvaluateExpr(@"FLOOR(0.4, 2)"); + Assert.AreEqual(0, actual); + + actual = XLWorkbook.EvaluateExpr(@"FLOOR(2.7, 2)"); + Assert.AreEqual(2, actual); + + actual = XLWorkbook.EvaluateExpr(@"FLOOR(7.8, 2)"); + Assert.AreEqual(6, actual); + + actual = XLWorkbook.EvaluateExpr(@"FLOOR(-5.5, -2)"); + Assert.AreEqual(-4, actual); + } + + [Test] + // Functions have to support a period first before we can implement this + public void FloorMath() + { + double actual; + + actual = (double)XLWorkbook.EvaluateExpr(@"FLOOR.MATH(24.3, 5)"); + Assert.AreEqual(20, actual, tolerance); + + actual = (double)XLWorkbook.EvaluateExpr(@"FLOOR.MATH(6.7)"); + Assert.AreEqual(6, actual, tolerance); + + actual = (double)XLWorkbook.EvaluateExpr(@"FLOOR.MATH(-8.1, 2)"); + Assert.AreEqual(-10, actual, tolerance); + + actual = (double)XLWorkbook.EvaluateExpr(@"FLOOR.MATH(5.5, 2.1, 0)"); + Assert.AreEqual(4.2, actual, tolerance); + + actual = (double)XLWorkbook.EvaluateExpr(@"FLOOR.MATH(5.5, -2.1, 0)"); + Assert.AreEqual(4.2, actual, tolerance); + + actual = (double)XLWorkbook.EvaluateExpr(@"FLOOR.MATH(5.5, 2.1, -1)"); + Assert.AreEqual(4.2, actual, tolerance); + + actual = (double)XLWorkbook.EvaluateExpr(@"FLOOR.MATH(5.5, -2.1, -1)"); + Assert.AreEqual(4.2, actual, tolerance); + + actual = (double)XLWorkbook.EvaluateExpr(@"FLOOR.MATH(-5.5, 2.1, 0)"); + Assert.AreEqual(-6.3, actual, tolerance); + + actual = (double)XLWorkbook.EvaluateExpr(@"FLOOR.MATH(-5.5, -2.1, 0)"); + Assert.AreEqual(-6.3, actual, tolerance); + + actual = (double)XLWorkbook.EvaluateExpr(@"FLOOR.MATH(-5.5, 2.1, -1)"); + Assert.AreEqual(-4.2, actual, tolerance); + + actual = (double)XLWorkbook.EvaluateExpr(@"FLOOR.MATH(-5.5, -2.1, -1)"); + Assert.AreEqual(-4.2, actual, tolerance); + } + + [Test] + public void Mod() + { + double actual; + + actual = (double)XLWorkbook.EvaluateExpr(@"MOD(1.5, 1)"); + Assert.AreEqual(0.5, actual, tolerance); + + actual = (double)XLWorkbook.EvaluateExpr(@"MOD(3, 2)"); + Assert.AreEqual(1, actual, tolerance); + + actual = (double)XLWorkbook.EvaluateExpr(@"MOD(-3, 2)"); + Assert.AreEqual(1, actual, tolerance); + + actual = (double)XLWorkbook.EvaluateExpr(@"MOD(3, -2)"); + Assert.AreEqual(-1, actual, tolerance); + + actual = (double)XLWorkbook.EvaluateExpr(@"MOD(-3, -2)"); + Assert.AreEqual(-1, actual, tolerance); + + ////// + + actual = (double)XLWorkbook.EvaluateExpr(@"MOD(-4.3, -0.5)"); + Assert.AreEqual(-0.3, actual, tolerance); + + actual = (double)XLWorkbook.EvaluateExpr(@"MOD(6.9, -0.2)"); + Assert.AreEqual(-0.1, actual, tolerance); + + actual = (double)XLWorkbook.EvaluateExpr(@"MOD(0.7, 0.6)"); + Assert.AreEqual(0.1, actual, tolerance); + + actual = (double)XLWorkbook.EvaluateExpr(@"MOD(6.2, 1.1)"); + Assert.AreEqual(0.7, actual, tolerance); + } + } +} diff --git a/ClosedXML_Tests/Excel/Cells/XLCellTests.cs b/ClosedXML_Tests/Excel/Cells/XLCellTests.cs index 9620511..2fb6166 100644 --- a/ClosedXML_Tests/Excel/Cells/XLCellTests.cs +++ b/ClosedXML_Tests/Excel/Cells/XLCellTests.cs @@ -404,5 +404,27 @@ Assert.AreEqual("\u0018", wb.Worksheets.First().FirstCell().Value); } } + + [Test] + public void CanClearCellValueBySettingNullValue() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + var cell = ws.FirstCell(); + + cell.Value = "Test"; + Assert.AreEqual("Test", cell.Value); + Assert.AreEqual(XLCellValues.Text, cell.DataType); + + string s = null; + cell.SetValue(s); + Assert.AreEqual(string.Empty, cell.Value); + + cell.Value = "Test"; + cell.Value = null; + Assert.AreEqual(string.Empty, cell.Value); + } + } } } diff --git a/ClosedXML_Tests/Excel/Loading/LoadingTests.cs b/ClosedXML_Tests/Excel/Loading/LoadingTests.cs index a6b3312..7298460 100644 --- a/ClosedXML_Tests/Excel/Loading/LoadingTests.cs +++ b/ClosedXML_Tests/Excel/Loading/LoadingTests.cs @@ -30,7 +30,8 @@ @"Misc\ExcelProducedWorkbookWithImages.xlsx", @"Misc\EmptyCellValue.xlsx", @"Misc\AllShapes.xlsx", - @"Misc\TableHeadersWithLineBreaks.xlsx" + @"Misc\TableHeadersWithLineBreaks.xlsx", + @"Misc\TableWithNameNull.xlsx" }; foreach (var file in files) diff --git a/ClosedXML_Tests/Excel/Misc/FormulaTests.cs b/ClosedXML_Tests/Excel/Misc/FormulaTests.cs index 8d2d470..16ac2a1 100644 --- a/ClosedXML_Tests/Excel/Misc/FormulaTests.cs +++ b/ClosedXML_Tests/Excel/Misc/FormulaTests.cs @@ -156,5 +156,22 @@ Assert.AreEqual(6, actual); } } + + [Test] + public void FormulaThatStartsWithEqualsAndPlus() + { + object actual; + actual = XLWorkbook.EvaluateExpr("=MID(\"This is a test\", 6, 2)"); + Assert.AreEqual("is", actual); + + actual = XLWorkbook.EvaluateExpr("=+MID(\"This is a test\", 6, 2)"); + Assert.AreEqual("is", actual); + + actual = XLWorkbook.EvaluateExpr("=+++++MID(\"This is a test\", 6, 2)"); + Assert.AreEqual("is", actual); + + actual = XLWorkbook.EvaluateExpr("+MID(\"This is a test\", 6, 2)"); + Assert.AreEqual("is", actual); + } } } diff --git a/ClosedXML_Tests/Excel/NamedRanges/NamedRangesTests.cs b/ClosedXML_Tests/Excel/NamedRanges/NamedRangesTests.cs index 907bc96..afe5c7b 100644 --- a/ClosedXML_Tests/Excel/NamedRanges/NamedRangesTests.cs +++ b/ClosedXML_Tests/Excel/NamedRanges/NamedRangesTests.cs @@ -140,5 +140,38 @@ Assert.Throws(() => wb.NamedRanges.Add("MyRange", "A1:C1")); } } + + [Test] + public void NamedRangesWhenCopyingWorksheets() + { + using (var wb = new XLWorkbook()) + { + var ws1 = wb.AddWorksheet("Sheet1"); + ws1.FirstCell().Value = Enumerable.Range(1, 10); + wb.NamedRanges.Add("wbNamedRange", ws1.Range("A1:A10")); + ws1.NamedRanges.Add("wsNamedRange", ws1.Range("A3")); + + var ws2 = wb.AddWorksheet("Sheet2"); + ws2.FirstCell().Value = Enumerable.Range(101, 10); + ws1.NamedRanges.Add("wsNamedRangeAcrossSheets", ws2.Range("A4")); + + ws1.Cell("C1").FormulaA1 = "=wbNamedRange"; + ws1.Cell("C2").FormulaA1 = "=wsNamedRange"; + ws1.Cell("C3").FormulaA1 = "=wsNamedRangeAcrossSheets"; + + Assert.AreEqual(1, ws1.Cell("C1").Value); + Assert.AreEqual(3, ws1.Cell("C2").Value); + Assert.AreEqual(104, ws1.Cell("C3").Value); + + var wsCopy = ws1.CopyTo("Copy"); + Assert.AreEqual(1, wsCopy.Cell("C1").Value); + Assert.AreEqual(3, wsCopy.Cell("C2").Value); + Assert.AreEqual(104, wsCopy.Cell("C3").Value); + + Assert.AreEqual("Sheet1!A1:A10", wb.NamedRange("wbNamedRange").Ranges.First().RangeAddress.ToStringRelative(true)); + Assert.AreEqual("Copy!A3:A3", wsCopy.NamedRange("wsNamedRange").Ranges.First().RangeAddress.ToStringRelative(true)); + Assert.AreEqual("Sheet2!A4:A4", wsCopy.NamedRange("wsNamedRangeAcrossSheets").Ranges.First().RangeAddress.ToStringRelative(true)); + } + } } } diff --git a/ClosedXML_Tests/Excel/Rows/RowTests.cs b/ClosedXML_Tests/Excel/Rows/RowTests.cs index 17cddeb..bb74d4f 100644 --- a/ClosedXML_Tests/Excel/Rows/RowTests.cs +++ b/ClosedXML_Tests/Excel/Rows/RowTests.cs @@ -185,6 +185,36 @@ } [Test] + public void InsertingRowsAbove4() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.Worksheets.Add("Sheet1"); + + ws.Row(2).Height = 15; + ws.Row(3).Height = 20; + ws.Row(4).Height = 25; + ws.Row(5).Height = 35; + + ws.Row(2).FirstCell().SetValue("Row height: 15"); + ws.Row(3).FirstCell().SetValue("Row height: 20"); + ws.Row(4).FirstCell().SetValue("Row height: 25"); + ws.Row(5).FirstCell().SetValue("Row height: 35"); + + ws.Range("3:3").InsertRowsAbove(1); + + Assert.AreEqual(15, ws.Row(2).Height); + Assert.AreEqual(20, ws.Row(4).Height); + Assert.AreEqual(25, ws.Row(5).Height); + Assert.AreEqual(35, ws.Row(6).Height); + + Assert.AreEqual(20, ws.Row(3).Height); + ws.Row(3).ClearHeight(); + Assert.AreEqual(ws.RowHeight, ws.Row(3).Height); + } + } + + [Test] public void NoRowsUsed() { var wb = new XLWorkbook(); @@ -224,4 +254,4 @@ ws.Rows(1, 2).Ungroup(true); } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Excel/Saving/SavingTests.cs b/ClosedXML_Tests/Excel/Saving/SavingTests.cs index ca2fd98..60a1d7a 100644 --- a/ClosedXML_Tests/Excel/Saving/SavingTests.cs +++ b/ClosedXML_Tests/Excel/Saving/SavingTests.cs @@ -34,6 +34,35 @@ } [Test] + public void CanSaveFileMultipleTimesAfterDeletingWorksheet() + { + // https://github.com/ClosedXML/ClosedXML/issues/435 + + + using (var ms = new MemoryStream()) + { + using (XLWorkbook book1 = new XLWorkbook()) + { + book1.AddWorksheet("sheet1"); + book1.AddWorksheet("sheet2"); + + book1.SaveAs(ms); + } + ms.Position = 0; + + using (XLWorkbook book2 = new XLWorkbook(ms)) + { + var ws = book2.Worksheet(1); + Assert.AreEqual("sheet1", ws.Name); + ws.Delete(); + book2.Save(); + book2.Save(); + } + } + } + + + [Test] public void CanSaveAndValidateFileInAnotherCulture() { string[] cultures = new string[] { "it", "de-AT" }; diff --git a/ClosedXML_Tests/Excel/Tables/TablesTests.cs b/ClosedXML_Tests/Excel/Tables/TablesTests.cs index ddc7b84..1190f9e 100644 --- a/ClosedXML_Tests/Excel/Tables/TablesTests.cs +++ b/ClosedXML_Tests/Excel/Tables/TablesTests.cs @@ -1,4 +1,4 @@ -using ClosedXML.Attributes; +using ClosedXML.Attributes; using ClosedXML.Excel; using NUnit.Framework; using System; @@ -333,34 +333,41 @@ [Test] public void ChangeFieldName() { - XLWorkbook wb = new XLWorkbook(); + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet"); + ws.Cell("A1").SetValue("FName") + .CellBelow().SetValue("John"); - var ws = wb.AddWorksheet("Sheet"); - ws.Cell("A1").SetValue("FName") - .CellBelow().SetValue("John"); + ws.Cell("B1").SetValue("LName") + .CellBelow().SetValue("Doe"); - ws.Cell("B1").SetValue("LName") - .CellBelow().SetValue("Doe"); + var tbl = ws.RangeUsed().CreateTable(); + var nameBefore = tbl.Field(tbl.Fields.Last().Index).Name; + tbl.Field(tbl.Fields.Last().Index).Name = "LastName"; + var nameAfter = tbl.Field(tbl.Fields.Last().Index).Name; - var tbl = ws.RangeUsed().CreateTable(); - var nameBefore = tbl.Field(tbl.Fields.Last().Index).Name; - tbl.Field(tbl.Fields.Last().Index).Name = "LastName"; - var nameAfter = tbl.Field(tbl.Fields.Last().Index).Name; + var cellValue = ws.Cell("B1").GetString(); - var cellValue = ws.Cell("B1").GetString(); + Assert.AreEqual("LName", nameBefore); + Assert.AreEqual("LastName", nameAfter); + Assert.AreEqual("LastName", cellValue); - Assert.AreEqual("LName", nameBefore); - Assert.AreEqual("LastName", nameAfter); - Assert.AreEqual("LastName", cellValue); + tbl.ShowHeaderRow = false; + tbl.Field(tbl.Fields.Last().Index).Name = "LastNameChanged"; + nameAfter = tbl.Field(tbl.Fields.Last().Index).Name; + Assert.AreEqual("LastNameChanged", nameAfter); - tbl.ShowHeaderRow = false; - tbl.Field(tbl.Fields.Last().Index).Name = "LastNameChanged"; - nameAfter = tbl.Field(tbl.Fields.Last().Index).Name; - Assert.AreEqual("LastNameChanged", nameAfter); + tbl.SetShowHeaderRow(true); + nameAfter = tbl.Cell("B1").Value.ToString(); + Assert.AreEqual("LastNameChanged", nameAfter); - tbl.SetShowHeaderRow(true); - nameAfter = tbl.Cell("B1").Value.ToString(); - Assert.AreEqual("LastNameChanged", nameAfter); + var field = tbl.Field("LastNameChanged"); + Assert.AreEqual("LastNameChanged", field.Name); + + tbl.Cell(1, 1).Value = "FirstName"; + Assert.AreEqual("FirstName", tbl.Field(0).Name); + } } } } diff --git a/ClosedXML_Tests/Excel/Worksheets/XLWorksheetTests.cs b/ClosedXML_Tests/Excel/Worksheets/XLWorksheetTests.cs index f679187..0918e5a 100644 --- a/ClosedXML_Tests/Excel/Worksheets/XLWorksheetTests.cs +++ b/ClosedXML_Tests/Excel/Worksheets/XLWorksheetTests.cs @@ -140,5 +140,25 @@ Assert.AreEqual(6, value); } } + + [Test] + public void CanRenameWorksheet() + { + using (var wb = new XLWorkbook()) + { + var ws1 = wb.AddWorksheet("Sheet1"); + var ws2 = wb.AddWorksheet("Sheet2"); + + ws1.Name = "New sheet name"; + Assert.AreEqual("New sheet name", ws1.Name); + + ws2.Name = "sheet2"; + Assert.AreEqual("sheet2", ws2.Name); + + Assert.Throws(() => ws1.Name = "SHEET2"); + + + } + } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Resource/Misc/TableWithNameNull.xlsx b/ClosedXML_Tests/Resource/Misc/TableWithNameNull.xlsx new file mode 100644 index 0000000..0a5a8de --- /dev/null +++ b/ClosedXML_Tests/Resource/Misc/TableWithNameNull.xlsx Binary files differ