diff --git a/ClosedXML/Attributes/ColumnOrderAttribute.cs b/ClosedXML/Attributes/ColumnOrderAttribute.cs new file mode 100644 index 0000000..b85c541 --- /dev/null +++ b/ClosedXML/Attributes/ColumnOrderAttribute.cs @@ -0,0 +1,15 @@ +using System; + +namespace ClosedXML.Attributes +{ + [AttributeUsage(AttributeTargets.Field | AttributeTargets.Property, AllowMultiple = false, Inherited = false)] + public class ColumnOrderAttribute : Attribute + { + public ColumnOrderAttribute(long order) + { + this.Order = order; + } + + public long Order { get; private set; } + } +} diff --git a/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML.csproj index 12bf76a..d4073e5 100644 --- a/ClosedXML/ClosedXML.csproj +++ b/ClosedXML/ClosedXML.csproj @@ -22,7 +22,7 @@ false bin\Debug\ DEBUG;TRACE - NET4 + NET4;TRACE;DEBUG prompt 4 1591 @@ -32,7 +32,7 @@ true bin\Release\ TRACE - NET4 + NET4;TRACE prompt 4 bin\Release\ClosedXML.xml @@ -61,15 +61,17 @@ + - + + diff --git a/ClosedXML/Excel/CalcEngine/CalcEngine.cs b/ClosedXML/Excel/CalcEngine/CalcEngine.cs index b946fa2..9ba60b2 100644 --- a/ClosedXML/Excel/CalcEngine/CalcEngine.cs +++ b/ClosedXML/Excel/CalcEngine/CalcEngine.cs @@ -9,8 +9,7 @@ using ClosedXML.Excel.CalcEngine; using ClosedXML.Excel.CalcEngine.Functions; -namespace ClosedXML.Excel.CalcEngine -{ +namespace ClosedXML.Excel.CalcEngine { /// /// CalcEngine parses strings and returns Expression objects that can /// be evaluated. @@ -21,19 +20,18 @@ /// Use the RegisterFunction method to define custom functions. /// Override the GetExternalObject method to add arbitrary variables to the engine scope. /// - internal class CalcEngine - { - //--------------------------------------------------------------------------- - #region ** fields + internal class CalcEngine { + //--------------------------------------------------------------------------- + #region ** fields - // members - string _expr; // expression being parsed - int _len; // length of the expression being parsed - int _ptr; // current pointer into expression - string _idChars; // valid characters in identifiers (besides alpha and digits) - Token _token; // current token being parsed + // members + string _expr; // expression being parsed + int _len; // length of the expression being parsed + int _ptr; // current pointer into expression + string _idChars; // valid characters in identifiers (besides alpha and digits) + Token _token; // current token being parsed Dictionary _tkTbl; // table with tokens (+, -, etc) - Dictionary _fnTbl; // table with constants and functions (pi, sin, etc) + Dictionary _fnTbl; // table with constants and functions (pi, sin, etc) Dictionary _vars; // table with variables object _dataContext; // object with properties bool _optimize; // optimize expressions when parsing @@ -46,8 +44,7 @@ //--------------------------------------------------------------------------- #region ** ctor - public CalcEngine() - { + public CalcEngine() { CultureInfo = CultureInfo.InvariantCulture; _tkTbl = GetSymbolTable(); _fnTbl = GetFunctionTable(); @@ -58,48 +55,44 @@ //this.Test(); #endif } - + #endregion //--------------------------------------------------------------------------- - #region ** object model + #region ** object model - /// - /// Parses a string into an . - /// - /// String to parse. + /// + /// Parses a string into an . + /// + /// String to parse. /// An object that can be evaluated. - public Expression Parse(string expression) - { - // initialize - _expr = expression; - _len = _expr.Length; - _ptr = 0; + public Expression Parse(string expression) { + // initialize + _expr = expression; + _len = _expr.Length; + _ptr = 0; // skip leading equals sign - if (_len > 0 && _expr[0] == '=') - { + if (_len > 0 && _expr[0] == '=') { _ptr++; } - // parse the expression - var expr = ParseExpression(); + // parse the expression + var expr = ParseExpression(); - // check for errors - if (_token.ID != TKID.END) - { + // check for errors + if (_token.ID != TKID.END) { Throw(); - } + } // optimize expression - if (_optimize) - { + if (_optimize) { expr = expr.Optimize(); } // done - return expr; - } + return expr; + } /// /// Evaluates a string. /// @@ -111,25 +104,21 @@ /// method and then using the Expression.Evaluate method to evaluate /// the parsed expression. /// - public object Evaluate(string expression) - { + public object Evaluate(string expression) { var x = //Parse(expression); _cache != null ? _cache[expression] : Parse(expression); - return x.Evaluate(); - } + return x.Evaluate(); + } /// /// Gets or sets whether the calc engine should keep a cache with parsed /// expressions. /// - public bool CacheExpressions - { + public bool CacheExpressions { get { return _cache != null; } - set - { - if (value != CacheExpressions) - { + set { + if (value != CacheExpressions) { _cache = value ? new ExpressionCache(this) : null; @@ -140,8 +129,7 @@ /// Gets or sets whether the calc engine should optimize expressions when /// they are parsed. /// - public bool OptimizeExpressions - { + public bool OptimizeExpressions { get { return _optimize; } set { _optimize = value; } } @@ -154,8 +142,7 @@ /// additional valid characters such as ':' or '!' (used in Excel range references /// for example). /// - public string IdentifierChars - { + public string IdentifierChars { get { return _idChars; } set { _idChars = value; } } @@ -166,8 +153,7 @@ /// Minimum parameter count. /// Maximum parameter count. /// Delegate that evaluates the function. - public void RegisterFunction(string functionName, int parmMin, int parmMax, CalcEngineFunction fn) - { + public void RegisterFunction(string functionName, int parmMin, int parmMax, CalcEngineFunction fn) { _fnTbl.Add(functionName, new FunctionDefinition(parmMin, parmMax, fn)); } /// @@ -176,8 +162,7 @@ /// Function name. /// Parameter count. /// Delegate that evaluates the function. - public void RegisterFunction(string functionName, int parmCount, CalcEngineFunction fn) - { + public void RegisterFunction(string functionName, int parmCount, CalcEngineFunction fn) { RegisterFunction(functionName, parmCount, parmCount, fn); } /// @@ -189,8 +174,7 @@ /// range objects based on identifiers that cannot be enumerated at design time /// (such as "AB12", "A1:AB12", etc.) /// - public virtual object GetExternalObject(string identifier) - { + public virtual object GetExternalObject(string identifier) { return null; } /// @@ -201,33 +185,28 @@ /// to the CalcEngine, including sub-properties such as "Address.Street". These may /// be used with expressions just like any other constant. /// - public virtual object DataContext - { + public virtual object DataContext { get { return _dataContext; } set { _dataContext = value; } } /// /// Gets the dictionary that contains function definitions. /// - public Dictionary Functions - { + public Dictionary Functions { get { return _fnTbl; } } /// /// Gets the dictionary that contains simple variables (not in the DataContext). /// - public Dictionary Variables - { + public Dictionary Variables { get { return _vars; } } /// /// Gets or sets the to use when parsing numbers and dates. /// - public CultureInfo CultureInfo - { + public CultureInfo CultureInfo { get { return _ci; } - set - { + set { _ci = value; var nf = _ci.NumberFormat; _decimal = nf.NumberDecimalSeparator[0]; @@ -236,16 +215,14 @@ } } - #endregion + #endregion //--------------------------------------------------------------------------- #region ** token/keyword tables // build/get static token table - Dictionary GetSymbolTable() - { - if (_tkTbl == null) - { + Dictionary GetSymbolTable() { + if (_tkTbl == null) { _tkTbl = new Dictionary(); AddToken('&', TKID.CONCAT, TKTYPE.ADDSUB); AddToken('+', TKID.ADD, TKTYPE.ADDSUB); @@ -263,29 +240,26 @@ AddToken("<>", TKID.NE, TKTYPE.COMPARE); AddToken(">=", TKID.GE, TKTYPE.COMPARE); AddToken("<=", TKID.LE, TKTYPE.COMPARE); - + // list separator is localized, not necessarily a comma // so it can't be on the static table //AddToken(',', TKID.COMMA, TKTYPE.GROUP); } return _tkTbl; } - void AddToken(object symbol, TKID id, TKTYPE type) - { + void AddToken(object symbol, TKID id, TKTYPE type) { var token = new Token(symbol, id, type); _tkTbl.Add(symbol, token); } // build/get static keyword table - Dictionary GetFunctionTable() - { - if (_fnTbl == null) - { + Dictionary GetFunctionTable() { + if (_fnTbl == null) { // create table _fnTbl = new Dictionary(StringComparer.InvariantCultureIgnoreCase); // register built-in functions (and constants) - Is.Register(this); + Information.Register(this); Logical.Register(this); Lookup.Register(this); MathTrig.Register(this); @@ -299,87 +273,74 @@ #endregion //--------------------------------------------------------------------------- - #region ** private stuff + #region ** private stuff - Expression ParseExpression() - { - GetToken(); - return ParseCompare(); - } - Expression ParseCompare() - { - var x = ParseAddSub(); - while (_token.Type == TKTYPE.COMPARE) - { - var t = _token; - GetToken(); - var exprArg = ParseAddSub(); - x = new BinaryExpression(t, x, exprArg); - } - return x; - } - Expression ParseAddSub() - { - var x = ParseMulDiv(); - while (_token.Type == TKTYPE.ADDSUB) - { - var t = _token; - GetToken(); - var exprArg = ParseMulDiv(); - x = new BinaryExpression(t, x, exprArg); - } - return x; + Expression ParseExpression() { + GetToken(); + return ParseCompare(); } - Expression ParseMulDiv() - { - var x = ParsePower(); - while (_token.Type == TKTYPE.MULDIV) - { - var t = _token; - GetToken(); - var a = ParsePower(); - x = new BinaryExpression(t, x, a); - } - return x; + Expression ParseCompare() { + var x = ParseAddSub(); + while (_token.Type == TKTYPE.COMPARE) { + var t = _token; + GetToken(); + var exprArg = ParseAddSub(); + x = new BinaryExpression(t, x, exprArg); + } + return x; } - Expression ParsePower() - { - var x = ParseUnary(); - while (_token.Type == TKTYPE.POWER) - { - var t = _token; - GetToken(); - var a = ParseUnary(); - x = new BinaryExpression(t, x, a); - } - return x; - } - Expression ParseUnary() - { - // unary plus and minus - if (_token.ID == TKID.ADD || _token.ID == TKID.SUB) - { - var t = _token; - GetToken(); + Expression ParseAddSub() { + var x = ParseMulDiv(); + while (_token.Type == TKTYPE.ADDSUB) { + var t = _token; + GetToken(); + var exprArg = ParseMulDiv(); + x = new BinaryExpression(t, x, exprArg); + } + return x; + } + Expression ParseMulDiv() { + var x = ParsePower(); + while (_token.Type == TKTYPE.MULDIV) { + var t = _token; + GetToken(); + var a = ParsePower(); + x = new BinaryExpression(t, x, a); + } + return x; + } + Expression ParsePower() { + var x = ParseUnary(); + while (_token.Type == TKTYPE.POWER) { + var t = _token; + GetToken(); + var a = ParseUnary(); + x = new BinaryExpression(t, x, a); + } + return x; + } + Expression ParseUnary() { + // unary plus and minus + if (_token.ID == TKID.ADD || _token.ID == TKID.SUB) { + var t = _token; + GetToken(); var a = ParseAtom(); return new UnaryExpression(t, a); - } + } - // not unary, return atom - return ParseAtom(); - } - Expression ParseAtom() - { + // not unary, return atom + return ParseAtom(); + } + Expression ParseAtom() { string id; Expression x = null; FunctionDefinition fnDef = null; - switch (_token.Type) - { - // literals - case TKTYPE.LITERAL: - x = new Expression(_token); - break; + switch (_token.Type) { + // literals + case TKTYPE.LITERAL: + x = new Expression(_token); + break; // identifiers case TKTYPE.IDENTIFIER: @@ -388,16 +349,13 @@ id = (string)_token.Value; // look for functions - if (_fnTbl.TryGetValue(id, out fnDef)) - { + if (_fnTbl.TryGetValue(id, out fnDef)) { var p = GetParameters(); var pCnt = p == null ? 0 : p.Count; - if (fnDef.ParmMin != -1 && pCnt < fnDef.ParmMin) - { + if (fnDef.ParmMin != -1 && pCnt < fnDef.ParmMin) { Throw("Too few parameters."); } - if (fnDef.ParmMax != -1 && pCnt > fnDef.ParmMax) - { + if (fnDef.ParmMax != -1 && pCnt > fnDef.ParmMax) { Throw("Too many parameters."); } x = new FunctionExpression(fnDef, p); @@ -405,26 +363,22 @@ } // look for simple variables (much faster than binding!) - if (_vars.ContainsKey(id)) - { + if (_vars.ContainsKey(id)) { x = new VariableExpression(_vars, id); break; } // look for external objects var xObj = GetExternalObject(id); - if (xObj != null) - { + if (xObj != null) { x = new XObjectExpression(xObj); break; } // look for bindings - if (DataContext != null) - { + if (DataContext != null) { var list = new List(); - for (var t = _token; t != null; t = GetMember()) - { + for (var t = _token; t != null; t = GetMember()) { list.Add(new BindingInfo((string)t.Value, GetParameters())); } x = new BindingExpression(this, list, _ci); @@ -433,172 +387,148 @@ Throw("Unexpected identifier"); break; - // sub-expressions - case TKTYPE.GROUP: + // sub-expressions + case TKTYPE.GROUP: // anything other than opening parenthesis is illegal here - if (_token.ID != TKID.OPEN) - { + if (_token.ID != TKID.OPEN) { Throw("Expression expected."); } - // get expression - GetToken(); - x = ParseCompare(); + // get expression + GetToken(); + x = ParseCompare(); - // check that the parenthesis was closed - if (_token.ID != TKID.CLOSE) - { - Throw("Unbalanced parenthesis."); - } + // check that the parenthesis was closed + if (_token.ID != TKID.CLOSE) { + Throw("Unbalanced parenthesis."); + } - break; - } + break; + } // make sure we got something... - if (x == null) - { + if (x == null) { Throw(); } - // done - GetToken(); - return x; - } + // done + GetToken(); + return x; + } - #endregion + #endregion - //--------------------------------------------------------------------------- - #region ** parser + //--------------------------------------------------------------------------- + #region ** parser - void GetToken() - { - // eat white space - while (_ptr < _len && _expr[_ptr] <= ' ') - { - _ptr++; - } + void GetToken() { + // eat white space + while (_ptr < _len && _expr[_ptr] <= ' ') { + _ptr++; + } - // are we done? - if (_ptr >= _len) - { + // are we done? + if (_ptr >= _len) { _token = new Token(null, TKID.END, TKTYPE.GROUP); - return; - } + return; + } - // prepare to parse + // prepare to parse int i; - var c = _expr[_ptr]; + var c = _expr[_ptr]; - // operators - // this gets called a lot, so it's pretty optimized. - // note that operators must start with non-letter/digit characters. + // operators + // this gets called a lot, so it's pretty optimized. + // note that operators must start with non-letter/digit characters. var isLetter = (c >= 'a' && c <= 'z') || (c >= 'A' && c <= 'Z'); var isDigit = c >= '0' && c <= '9'; - if (!isLetter && !isDigit) - { - // if this is a number starting with a decimal, don't parse as operator + if (!isLetter && !isDigit) { + // if this is a number starting with a decimal, don't parse as operator var nxt = _ptr + 1 < _len ? _expr[_ptr + 1] : 0; bool isNumber = c == _decimal && nxt >= '0' && nxt <= '9'; - if (!isNumber) - { + if (!isNumber) { // look up localized list separator - if (c == _listSep) - { + if (c == _listSep) { _token = new Token(c, TKID.COMMA, TKTYPE.GROUP); _ptr++; return; } - + // look up single-char tokens on table Token tk; - if (_tkTbl.TryGetValue(c, out tk)) - { - // save token we found - _token = tk; - _ptr++; + if (_tkTbl.TryGetValue(c, out tk)) { + // save token we found + _token = tk; + _ptr++; - // look for double-char tokens (special case) - if (_ptr < _len && (c == '>' || c == '<')) - { - if (_tkTbl.TryGetValue(_expr.Substring(_ptr - 1, 2), out tk)) - { - _token = tk; - _ptr++; - } - } + // look for double-char tokens (special case) + if (_ptr < _len && (c == '>' || c == '<')) { + if (_tkTbl.TryGetValue(_expr.Substring(_ptr - 1, 2), out tk)) { + _token = tk; + _ptr++; + } + } // found token on the table - return; - } - } - } + return; + } + } + } - // parse numbers - if (isDigit || c == _decimal) - { - var sci = false; + // parse numbers + if (isDigit || c == _decimal) { + var sci = false; var pct = false; var div = -1.0; // use double, not int (this may get really big) var val = 0.0; - for (i = 0; i + _ptr < _len; i++) - { - c = _expr[_ptr + i]; + for (i = 0; i + _ptr < _len; i++) { + c = _expr[_ptr + i]; // digits always OK - if (c >= '0' && c <= '9') - { + if (c >= '0' && c <= '9') { val = val * 10 + (c - '0'); - if (div > -1) - { + if (div > -1) { div *= 10; } continue; } - // one decimal is OK - if (c == _decimal && div < 0) - { - div = 1; - continue; - } - - // scientific notation? - if ((c == 'E' || c == 'e') && !sci) - { - sci = true; - c = _expr[_ptr + i + 1]; - if (c == '+' || c == '-') i++; - continue; - } + // one decimal is OK + if (c == _decimal && div < 0) { + div = 1; + continue; + } + + // scientific notation? + if ((c == 'E' || c == 'e') && !sci) { + sci = true; + c = _expr[_ptr + i + 1]; + if (c == '+' || c == '-') i++; + continue; + } // percentage? - if (c == _percent) - { + if (c == _percent) { pct = true; i++; break; } - // end of literal - break; - } + // end of literal + break; + } // end of number, get value - if (!sci) - { + if (!sci) { // much faster than ParseDouble - if (div > 1) - { + if (div > 1) { val /= div; } - if (pct) - { + if (pct) { val /= 100.0; } - } - else - { + } else { var lit = _expr.Substring(_ptr, i); val = ParseDouble(lit, _ci); } @@ -609,71 +539,62 @@ // advance pointer and return _ptr += i; return; - } + } - // parse strings - if (c == '\"') - { - // look for end quote, skip double quotes - for (i = 1; i + _ptr < _len; i++) - { - c = _expr[_ptr + i]; - if (c != '\"') continue; - char cNext = i + _ptr < _len - 1 ? _expr[_ptr + i + 1]: ' '; - if (cNext != '\"') break; - i++; - } + // parse strings + if (c == '\"') { + // look for end quote, skip double quotes + for (i = 1; i + _ptr < _len; i++) { + c = _expr[_ptr + i]; + if (c != '\"') continue; + char cNext = i + _ptr < _len - 1 ? _expr[_ptr + i + 1] : ' '; + if (cNext != '\"') break; + i++; + } - // check that we got the end of the string - if (c != '\"') - { - Throw("Can't find final quote."); - } + // check that we got the end of the string + if (c != '\"') { + Throw("Can't find final quote."); + } - // end of string - var lit = _expr.Substring(_ptr + 1, i - 1); - _ptr += i + 1; + // end of string + var lit = _expr.Substring(_ptr + 1, i - 1); + _ptr += i + 1; _token = new Token(lit.Replace("\"\"", "\""), TKID.ATOM, TKTYPE.LITERAL); - return; - } + return; + } - // parse dates (review) - if (c == '#') - { - // look for end # - for (i = 1; i + _ptr < _len; i++) - { - c = _expr[_ptr + i]; - if (c == '#') break; - } + // parse dates (review) + if (c == '#') { + // look for end # + for (i = 1; i + _ptr < _len; i++) { + c = _expr[_ptr + i]; + if (c == '#') break; + } - // check that we got the end of the date - if (c != '#') - { - Throw("Can't find final date delimiter ('#')."); - } + // check that we got the end of the date + if (c != '#') { + Throw("Can't find final date delimiter ('#')."); + } - // end of date - var lit = _expr.Substring(_ptr + 1, i - 1); - _ptr += i + 1; + // end of date + var lit = _expr.Substring(_ptr + 1, i - 1); + _ptr += i + 1; _token = new Token(DateTime.Parse(lit, _ci), TKID.ATOM, TKTYPE.LITERAL); - return; - } + return; + } // identifiers (functions, objects) must start with alpha or underscore - if (!isLetter && c != '_' && (_idChars == null || _idChars.IndexOf(c) < 0)) - { + if (!isLetter && c != '_' && (_idChars == null || _idChars.IndexOf(c) < 0)) { Throw("Identifier expected."); } // and must contain only letters/digits/_idChars - for (i = 1; i + _ptr < _len; i++) - { + for (i = 1; i + _ptr < _len; i++) { c = _expr[_ptr + i]; isLetter = (c >= 'a' && c <= 'z') || (c >= 'A' && c <= 'Z'); isDigit = c >= '0' && c <= '9'; - if (!isLetter && !isDigit && c != '_' && (_idChars == null || _idChars.IndexOf(c) < 0)) - { + if (!isLetter && !isDigit && c != '_' && (_idChars == null || _idChars.IndexOf(c) < 0)) { break; } } @@ -682,67 +603,59 @@ var id = _expr.Substring(_ptr, i); _ptr += i; _token = new Token(id, TKID.ATOM, TKTYPE.IDENTIFIER); - } - static double ParseDouble(string str, CultureInfo ci) - { - if (str.Length > 0 && str[str.Length - 1] == ci.NumberFormat.PercentSymbol[0]) - { + } + static double ParseDouble(string str, CultureInfo ci) { + if (str.Length > 0 && str[str.Length - 1] == ci.NumberFormat.PercentSymbol[0]) { str = str.Substring(0, str.Length - 1); return double.Parse(str, NumberStyles.Any, ci) / 100.0; } return double.Parse(str, NumberStyles.Any, ci); } List GetParameters() // e.g. myfun(a, b, c+2) - { - // check whether next token is a (, - // restore state and bail if it's not - var pos = _ptr; - var tk = _token; - GetToken(); - if (_token.ID != TKID.OPEN) - { + { + // check whether next token is a (, + // restore state and bail if it's not + var pos = _ptr; + var tk = _token; + GetToken(); + if (_token.ID != TKID.OPEN) { _ptr = pos; _token = tk; - return null; - } - - // check for empty Parameter list - pos = _ptr; - GetToken(); - if (_token.ID == TKID.CLOSE) - { return null; } - _ptr = pos; - // get Parameters until we reach the end of the list + // check for empty Parameter list + pos = _ptr; + GetToken(); + if (_token.ID == TKID.CLOSE) { + return null; + } + _ptr = pos; + + // get Parameters until we reach the end of the list var parms = new List(); - var expr = ParseExpression(); - parms.Add(expr); - while (_token.ID == TKID.COMMA) - { - expr = ParseExpression(); - parms.Add(expr); - } + var expr = ParseExpression(); + parms.Add(expr); + while (_token.ID == TKID.COMMA) { + expr = ParseExpression(); + parms.Add(expr); + } - // make sure the list was closed correctly - if (_token.ID != TKID.CLOSE) - { + // make sure the list was closed correctly + if (_token.ID != TKID.CLOSE) { Throw(); - } + } - // done - return parms; - } - Token GetMember() - { + // done + return parms; + } + Token GetMember() { // check whether next token is a MEMBER token ('.'), // restore state and bail if it's not var pos = _ptr; var tk = _token; GetToken(); - if (_token.ID != TKID.PERIOD) - { + if (_token.ID != TKID.PERIOD) { _ptr = pos; _token = tk; return null; @@ -750,29 +663,26 @@ // skip member token GetToken(); - if (_token.Type != TKTYPE.IDENTIFIER) - { + if (_token.Type != TKTYPE.IDENTIFIER) { Throw("Identifier expected"); } return _token; } - #endregion + #endregion - //--------------------------------------------------------------------------- - #region ** static helpers + //--------------------------------------------------------------------------- + #region ** static helpers - static void Throw() - { + static void Throw() { Throw("Syntax error."); } - static void Throw(string msg) - { + static void Throw(string msg) { throw new Exception(msg); } #endregion - } + } /// /// Delegate that represents CalcEngine functions. diff --git a/ClosedXML/Excel/CalcEngine/Expression.cs b/ClosedXML/Excel/CalcEngine/Expression.cs index 3e2c9bc..1b97924 100644 --- a/ClosedXML/Excel/CalcEngine/Expression.cs +++ b/ClosedXML/Excel/CalcEngine/Expression.cs @@ -10,503 +10,510 @@ namespace ClosedXML.Excel.CalcEngine { - /// - /// Base class that represents parsed expressions. - /// - /// - /// For example: - /// - /// Expression expr = scriptEngine.Parse(strExpression); - /// object val = expr.Evaluate(); - /// - /// - internal class Expression : IComparable - { - //--------------------------------------------------------------------------- - #region ** fields + /// + /// Base class that represents parsed expressions. + /// + /// + /// For example: + /// + /// Expression expr = scriptEngine.Parse(strExpression); + /// object val = expr.Evaluate(); + /// + /// + internal class Expression : IComparable + { + //--------------------------------------------------------------------------- + #region ** fields - internal Token _token; + internal Token _token; - #endregion + #endregion - //--------------------------------------------------------------------------- - #region ** ctors + //--------------------------------------------------------------------------- + #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; - } + 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 - //--------------------------------------------------------------------------- - #region ** object model + //--------------------------------------------------------------------------- + #region ** object model - public virtual object Evaluate() - { - if (_token.Type != TKTYPE.LITERAL) - { - throw new ArgumentException("Bad expression."); - } - return _token.Value; - } - public virtual Expression Optimize() - { - return this; - } + public virtual object Evaluate() + { + if (_token.Type != TKTYPE.LITERAL) + { + throw new ArgumentException("Bad expression."); + } + return _token.Value; + } + public virtual Expression Optimize() + { + return this; + } - #endregion + #endregion - //--------------------------------------------------------------------------- - #region ** implicit converters + //--------------------------------------------------------------------------- + #region ** implicit converters - public static implicit operator string(Expression x) - { - var v = x.Evaluate(); - return v == null ? string.Empty : v.ToString(); - } - public static implicit operator double(Expression x) - { - // evaluate - var v = x.Evaluate(); + public static implicit operator string(Expression x) + { + var v = x.Evaluate(); + return v == null ? string.Empty : v.ToString(); + } + public static implicit operator double(Expression x) + { + // evaluate + var v = x.Evaluate(); - // handle doubles - if (v is double) - { - return (double)v; - } + // handle doubles + if (v is double) + { + return (double)v; + } - // handle booleans - if (v is bool) - { - return (bool)v ? 1 : 0; - } + // handle booleans + if (v is bool) + { + return (bool)v ? 1 : 0; + } - // handle dates - if (v is DateTime) - { - return ((DateTime)v).ToOADate(); - } + // handle dates + if (v is DateTime) + { + return ((DateTime)v).ToOADate(); + } - // handle nulls - if (v == null || v is String) - { - return 0; - } - - // handle everything else - CultureInfo _ci = Thread.CurrentThread.CurrentCulture; - return (double)Convert.ChangeType(v, typeof(double), _ci); - } - public static implicit operator bool(Expression x) - { - // evaluate - var v = x.Evaluate(); - - // handle booleans - if (v is bool) - { - return (bool)v; - } - - // handle nulls - if (v == null) - { - return false; - } - - // handle doubles - if (v is double) - { - return (double)v == 0 ? false : true; - } - - // handle everything else - return (double)x == 0 ? false : true; - } - public static implicit operator DateTime(Expression x) - { - // evaluate - var v = x.Evaluate(); - - // handle dates - if (v is DateTime) - { - return (DateTime)v; - } - - // handle doubles - if (v is double || v is int) - { - return DateTime.FromOADate((double)x); - } + // handle nulls + if (v == null || v is string) + { + return 0; + } // handle everything else CultureInfo _ci = Thread.CurrentThread.CurrentCulture; - return (DateTime)Convert.ChangeType(v, typeof(DateTime), _ci); - } + return (double)Convert.ChangeType(v, typeof(double), _ci); + } + public static implicit operator bool(Expression x) + { + // evaluate + var v = x.Evaluate(); - #endregion + // handle booleans + if (v is bool) + { + return (bool)v; + } - //--------------------------------------------------------------------------- - #region ** IComparable + // handle nulls + if (v == null) + { + return false; + } - public int CompareTo(Expression other) - { - // get both values - var c1 = this.Evaluate() as IComparable; - var c2 = other.Evaluate() as IComparable; + // handle doubles + if (v is double) + { + return (double)v == 0 ? false : true; + } - // handle nulls - if (c1 == null && c2 == null) - { - return 0; - } - if (c2 == null) - { - return -1; - } - if (c1 == null) - { - return +1; - } + // handle everything else + return (double)x == 0 ? false : true; + } + public static implicit operator DateTime(Expression x) + { + // evaluate + var v = x.Evaluate(); - // make sure types are the same - if (c1.GetType() != c2.GetType()) - { - if (c1 is DateTime) - c2 = ((DateTime)other); - else if (c2 is DateTime) - c1 = ((DateTime)this); - else - c2 = Convert.ChangeType(c2, c1.GetType()) as IComparable; - } + // handle dates + if (v is DateTime) + { + return (DateTime)v; + } - // compare - return c1.CompareTo(c2); - } + // handle doubles + if (v is double || v is int) + { + return DateTime.FromOADate((double)x); + } - #endregion - } - /// - /// Unary expression, e.g. +123 - /// - class UnaryExpression : Expression - { - // ** fields - Expression _expr; + // handle everything else + CultureInfo _ci = Thread.CurrentThread.CurrentCulture; + return (DateTime)Convert.ChangeType(v, typeof(DateTime), _ci); + } - // ** ctor - public UnaryExpression(Token tk, Expression expr) : base(tk) - { - _expr = expr; - } + #endregion - // ** object model - override public object Evaluate() - { - switch (_token.ID) - { - case TKID.ADD: - return +(double)_expr; - case TKID.SUB: - return -(double)_expr; - } - throw new ArgumentException("Bad expression."); - } - public override Expression Optimize() - { - _expr = _expr.Optimize(); - return _expr._token.Type == TKTYPE.LITERAL - ? new Expression(this.Evaluate()) - : this; - } - } - /// - /// Binary expression, e.g. 1+2 - /// - class BinaryExpression : Expression - { - // ** fields - Expression _lft; - Expression _rgt; + //--------------------------------------------------------------------------- + #region ** IComparable - // ** ctor - public BinaryExpression(Token tk, Expression exprLeft, Expression exprRight) : base(tk) - { - _lft = exprLeft; - _rgt = exprRight; - } + public int CompareTo(Expression other) + { + // get both values + var c1 = this.Evaluate() as IComparable; + var c2 = other.Evaluate() as IComparable; - // ** object model - override public object Evaluate() - { - // handle comparisons - if (_token.Type == TKTYPE.COMPARE) - { - var cmp = _lft.CompareTo(_rgt); - switch (_token.ID) - { - case TKID.GT: return cmp > 0; - case TKID.LT: return cmp < 0; - case TKID.GE: return cmp >= 0; - case TKID.LE: return cmp <= 0; - case TKID.EQ: return cmp == 0; - case TKID.NE: return cmp != 0; - } - } + // handle nulls + if (c1 == null && c2 == null) + { + return 0; + } + if (c2 == null) + { + return -1; + } + if (c1 == null) + { + return +1; + } - // handle everything else - switch (_token.ID) - { - 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; - if (b == 0.0) return 1.0; - if (b == 0.5) return Math.Sqrt(a); - if (b == 1.0) return a; - if (b == 2.0) return a * a; - if (b == 3.0) return a * a * a; - if (b == 4.0) return a * a * a * a; - return Math.Pow((double)_lft, (double)_rgt); - } - throw new ArgumentException("Bad expression."); - } - public override Expression Optimize() - { - _lft = _lft.Optimize(); - _rgt = _rgt.Optimize(); - return _lft._token.Type == TKTYPE.LITERAL && _rgt._token.Type == TKTYPE.LITERAL - ? new Expression(this.Evaluate()) - : this; - } - } - /// - /// Function call expression, e.g. sin(0.5) - /// - class FunctionExpression : Expression - { - // ** fields - FunctionDefinition _fn; - List _parms; + // make sure types are the same + if (c1.GetType() != c2.GetType()) + { + try + { + if (c1 is DateTime) + c2 = ((DateTime)other); + else if (c2 is DateTime) + c1 = ((DateTime)this); + else + c2 = Convert.ChangeType(c2, c1.GetType()) as IComparable; + } + catch (InvalidCastException) { return -1; } + catch (FormatException) { return -1; } + catch (OverflowException) { return -1; } + catch (ArgumentNullException) { return -1; } + } - // ** ctor - internal FunctionExpression() - { - } - public FunctionExpression(FunctionDefinition function, List parms) - { - _fn = function; - _parms = parms; - } + // compare + return c1.CompareTo(c2); + } - // ** object model - override public object Evaluate() - { - return _fn.Function(_parms); - } - public override Expression Optimize() - { - bool allLits = true; - if (_parms != null) - { - for (int i = 0; i < _parms.Count; i++) - { - var p = _parms[i].Optimize(); - _parms[i] = p; - if (p._token.Type != TKTYPE.LITERAL) - { - allLits = false; - } - } - } - return allLits - ? new Expression(this.Evaluate()) - : this; - } - } - /// - /// Simple variable reference. - /// - class VariableExpression : Expression - { - Dictionary _dct; - string _name; + #endregion + } + /// + /// Unary expression, e.g. +123 + /// + class UnaryExpression : Expression + { + // ** fields + Expression _expr; - public VariableExpression(Dictionary dct, string name) - { - _dct = dct; - _name = name; - } - public override object Evaluate() - { - return _dct[_name]; - } - } - /// - /// Expression based on an object's properties. - /// - class BindingExpression : Expression - { - CalcEngine _ce; - CultureInfo _ci; - List _bindingPath; + // ** ctor + public UnaryExpression(Token tk, Expression expr) : base(tk) + { + _expr = expr; + } - // ** ctor - internal BindingExpression(CalcEngine engine, List bindingPath, CultureInfo ci) - { - _ce = engine; - _bindingPath = bindingPath; - _ci = ci; - } + // ** object model + override public object Evaluate() + { + switch (_token.ID) + { + case TKID.ADD: + return +(double)_expr; + case TKID.SUB: + return -(double)_expr; + } + throw new ArgumentException("Bad expression."); + } + public override Expression Optimize() + { + _expr = _expr.Optimize(); + return _expr._token.Type == TKTYPE.LITERAL + ? new Expression(this.Evaluate()) + : this; + } + } + /// + /// Binary expression, e.g. 1+2 + /// + class BinaryExpression : Expression + { + // ** fields + Expression _lft; + Expression _rgt; - // ** object model - override public object Evaluate() - { - return GetValue(_ce.DataContext); - } + // ** ctor + public BinaryExpression(Token tk, Expression exprLeft, Expression exprRight) : base(tk) + { + _lft = exprLeft; + _rgt = exprRight; + } - // ** implementation - object GetValue(object obj) - { - const BindingFlags bf = - BindingFlags.IgnoreCase | - BindingFlags.Instance | - BindingFlags.Public | - BindingFlags.Static; + // ** object model + override public object Evaluate() + { + // handle comparisons + if (_token.Type == TKTYPE.COMPARE) + { + var cmp = _lft.CompareTo(_rgt); + switch (_token.ID) + { + case TKID.GT: return cmp > 0; + case TKID.LT: return cmp < 0; + case TKID.GE: return cmp >= 0; + case TKID.LE: return cmp <= 0; + case TKID.EQ: return cmp == 0; + case TKID.NE: return cmp != 0; + } + } - if (obj != null) - { - foreach (var bi in _bindingPath) - { - // get property - if (bi.PropertyInfo == null) - { - bi.PropertyInfo = obj.GetType().GetProperty(bi.Name, bf); - } + // handle everything else + switch (_token.ID) + { + 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; + if (b == 0.0) return 1.0; + if (b == 0.5) return Math.Sqrt(a); + if (b == 1.0) return a; + if (b == 2.0) return a * a; + if (b == 3.0) return a * a * a; + if (b == 4.0) return a * a * a * a; + return Math.Pow((double)_lft, (double)_rgt); + } + throw new ArgumentException("Bad expression."); + } + public override Expression Optimize() + { + _lft = _lft.Optimize(); + _rgt = _rgt.Optimize(); + return _lft._token.Type == TKTYPE.LITERAL && _rgt._token.Type == TKTYPE.LITERAL + ? new Expression(this.Evaluate()) + : this; + } + } + /// + /// Function call expression, e.g. sin(0.5) + /// + class FunctionExpression : Expression + { + // ** fields + FunctionDefinition _fn; + List _parms; - // get object - try - { - obj = bi.PropertyInfo.GetValue(obj, null); - } - catch - { - // REVIEW: is this needed? - System.Diagnostics.Debug.Assert(false, "shouldn't happen!"); - bi.PropertyInfo = obj.GetType().GetProperty(bi.Name, bf); - bi.PropertyInfoItem = null; - obj = bi.PropertyInfo.GetValue(obj, null); - } + // ** ctor + internal FunctionExpression() + { + } + public FunctionExpression(FunctionDefinition function, List parms) + { + _fn = function; + _parms = parms; + } - // handle indexers (lists and dictionaries) - if (bi.Parms != null && bi.Parms.Count > 0) - { - // get indexer property (always called "Item") - if (bi.PropertyInfoItem == null) - { - bi.PropertyInfoItem = obj.GetType().GetProperty("Item", bf); - } + // ** object model + override public object Evaluate() + { + return _fn.Function(_parms); + } + public override Expression Optimize() + { + bool allLits = true; + if (_parms != null) + { + for (int i = 0; i < _parms.Count; i++) + { + var p = _parms[i].Optimize(); + _parms[i] = p; + if (p._token.Type != TKTYPE.LITERAL) + { + allLits = false; + } + } + } + return allLits + ? new Expression(this.Evaluate()) + : this; + } + } + /// + /// Simple variable reference. + /// + class VariableExpression : Expression + { + Dictionary _dct; + string _name; - // get indexer parameters - var pip = bi.PropertyInfoItem.GetIndexParameters(); - var list = new List(); - for (int i = 0; i < pip.Length; i++) - { - var pv = bi.Parms[i].Evaluate(); - pv = Convert.ChangeType(pv, pip[i].ParameterType, _ci); - list.Add(pv); - } + public VariableExpression(Dictionary dct, string name) + { + _dct = dct; + _name = name; + } + public override object Evaluate() + { + return _dct[_name]; + } + } + /// + /// Expression based on an object's properties. + /// + class BindingExpression : Expression + { + CalcEngine _ce; + CultureInfo _ci; + List _bindingPath; - // get value - obj = bi.PropertyInfoItem.GetValue(obj, list.ToArray()); - } - } - } + // ** ctor + internal BindingExpression(CalcEngine engine, List bindingPath, CultureInfo ci) + { + _ce = engine; + _bindingPath = bindingPath; + _ci = ci; + } - // all done - return obj; - } - } - /// - /// Helper used for building BindingExpression objects. - /// - class BindingInfo - { - public BindingInfo(string member, List parms) - { - Name = member; - Parms = parms; - } - public string Name { get; set; } - public PropertyInfo PropertyInfo { get; set; } - public PropertyInfo PropertyInfoItem { get; set; } - public List Parms { get; set; } - } - /// - /// Expression that represents an external object. - /// - class XObjectExpression : - Expression, - IEnumerable - { - object _value; + // ** object model + override public object Evaluate() + { + return GetValue(_ce.DataContext); + } - // ** ctor - internal XObjectExpression(object value) - { - _value = value; - } + // ** implementation + object GetValue(object obj) + { + const BindingFlags bf = + BindingFlags.IgnoreCase | + BindingFlags.Instance | + BindingFlags.Public | + BindingFlags.Static; - public object Value { get { return _value; } } + if (obj != null) + { + foreach (var bi in _bindingPath) + { + // get property + if (bi.PropertyInfo == null) + { + bi.PropertyInfo = obj.GetType().GetProperty(bi.Name, bf); + } - // ** object model - public override object Evaluate() - { - // use IValueObject if available - var iv = _value as IValueObject; - if (iv != null) - { - return iv.GetValue(); - } + // get object + try + { + obj = bi.PropertyInfo.GetValue(obj, null); + } + catch + { + // REVIEW: is this needed? + System.Diagnostics.Debug.Assert(false, "shouldn't happen!"); + bi.PropertyInfo = obj.GetType().GetProperty(bi.Name, bf); + bi.PropertyInfoItem = null; + obj = bi.PropertyInfo.GetValue(obj, null); + } - // return raw object - return _value; - } - public IEnumerator GetEnumerator() - { - return (_value as IEnumerable).GetEnumerator(); - } - } - /// - /// Interface supported by external objects that have to return a value - /// other than themselves (e.g. a cell range object should return the - /// cell content instead of the range itself). - /// - public interface IValueObject - { - object GetValue(); - } + // handle indexers (lists and dictionaries) + if (bi.Parms != null && bi.Parms.Count > 0) + { + // get indexer property (always called "Item") + if (bi.PropertyInfoItem == null) + { + bi.PropertyInfoItem = obj.GetType().GetProperty("Item", bf); + } + + // get indexer parameters + var pip = bi.PropertyInfoItem.GetIndexParameters(); + var list = new List(); + for (int i = 0; i < pip.Length; i++) + { + var pv = bi.Parms[i].Evaluate(); + pv = Convert.ChangeType(pv, pip[i].ParameterType, _ci); + list.Add(pv); + } + + // get value + obj = bi.PropertyInfoItem.GetValue(obj, list.ToArray()); + } + } + } + + // all done + return obj; + } + } + /// + /// Helper used for building BindingExpression objects. + /// + class BindingInfo + { + public BindingInfo(string member, List parms) + { + Name = member; + Parms = parms; + } + public string Name { get; set; } + public PropertyInfo PropertyInfo { get; set; } + public PropertyInfo PropertyInfoItem { get; set; } + public List Parms { get; set; } + } + /// + /// Expression that represents an external object. + /// + class XObjectExpression : + Expression, + IEnumerable + { + object _value; + + // ** ctor + internal XObjectExpression(object value) + { + _value = value; + } + + public object Value { get { return _value; } } + + // ** object model + public override object Evaluate() + { + // use IValueObject if available + var iv = _value as IValueObject; + if (iv != null) + { + return iv.GetValue(); + } + + // return raw object + return _value; + } + public IEnumerator GetEnumerator() + { + return (_value as IEnumerable).GetEnumerator(); + } + } + /// + /// Interface supported by external objects that have to return a value + /// other than themselves (e.g. a cell range object should return the + /// cell content instead of the range itself). + /// + public interface IValueObject + { + object GetValue(); + } } diff --git a/ClosedXML/Excel/CalcEngine/Functions/DateAndTime.cs b/ClosedXML/Excel/CalcEngine/Functions/DateAndTime.cs index d1fe392..e1678b6 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/DateAndTime.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/DateAndTime.cs @@ -14,10 +14,12 @@ ce.RegisterFunction("DATE", 3, Date); // Returns the serial number of a particular date ce.RegisterFunction("DATEVALUE", 1, Datevalue); // Converts a date in the form of text to a serial number ce.RegisterFunction("DAY", 1, Day); // Converts a serial number to a day of the month + ce.RegisterFunction("DAYS", 2, Days); // Returns the number of days between two dates. ce.RegisterFunction("DAYS360", 2, 3, Days360); // Calculates the number of days between two dates based on a 360-day year ce.RegisterFunction("EDATE", 2, Edate); // Returns the serial number of the date that is the indicated number of months before or after the start date ce.RegisterFunction("EOMONTH", 2, Eomonth); // Returns the serial number of the last day of the month before or after a specified number of months ce.RegisterFunction("HOUR", 1, Hour); // Converts a serial number to an hour + ce.RegisterFunction("ISOWEEKNUM", 1, IsoWeekNum); // Returns number of the ISO week number of the year for a given date. ce.RegisterFunction("MINUTE", 1, Minute); // Converts a serial number to a minute ce.RegisterFunction("MONTH", 1, Month); // Converts a serial number to a month ce.RegisterFunction("NETWORKDAYS", 2, 3, Networkdays); // Returns the number of whole workdays between two dates @@ -34,156 +36,6 @@ } - private static object Date(List p) - { - var year = (int) p[0]; - var month = (int) p[1]; - var day = (int) p[2]; - - return (int) Math.Floor(new DateTime(year, month, day).ToOADate()); - } - - private static object Datevalue(List p) - { - var date = (string) p[0]; - - return (int) Math.Floor(DateTime.Parse(date).ToOADate()); - } - - private static object Day(List p) - { - var date = (DateTime) p[0]; - - return date.Day; - } - - private static object Month(List p) - { - var date = (DateTime) p[0]; - - return date.Month; - } - - private static object Year(List p) - { - var date = (DateTime) p[0]; - - return date.Year; - } - - private static object Minute(List p) - { - var date = (DateTime) p[0]; - - return date.Minute; - } - - private static object Hour(List p) - { - var date = (DateTime) p[0]; - - return date.Hour; - } - - private static object Second(List p) - { - var date = (DateTime) p[0]; - - return date.Second; - } - - private static object Now(List p) - { - return DateTime.Now; - } - - private static object Time(List p) - { - var hour = (int) p[0]; - var minute = (int) p[1]; - var second = (int) p[2]; - - return new TimeSpan(0, hour, minute, second); - } - - private static object Timevalue(List p) - { - var date = (DateTime) p[0]; - - return (DateTime.MinValue + date.TimeOfDay).ToOADate(); - } - - private static object Today(List p) - { - return DateTime.Now.Date; - } - - private static object Days360(List p) - { - var date1 = (DateTime) p[0]; - var date2 = (DateTime) p[1]; - var isEuropean = p.Count == 3 ? p[2] : false; - - return Days360(date1, date2, isEuropean); - } - - private static Int32 Days360(DateTime date1, DateTime date2, Boolean isEuropean) - { - var d1 = date1.Day; - var m1 = date1.Month; - var y1 = date1.Year; - var d2 = date2.Day; - var m2 = date2.Month; - var y2 = date2.Year; - - if (isEuropean) - { - if (d1 == 31) d1 = 30; - if (d2 == 31) d2 = 30; - } - else - { - if (d1 == 31) d1 = 30; - if (d2 == 31 && d1 == 30) d2 = 30; - } - - return 360 * (y2 - y1) + 30 * (m2 - m1) + (d2 - d1); - - } - - private static object Edate(List p) - { - var date = (DateTime)p[0]; - var mod = (int)p[1]; - - var retDate = date.AddMonths(mod); - return retDate; - } - - private static object Eomonth(List p) - { - var date = (DateTime)p[0]; - var mod = (int)p[1]; - - var retDate = date.AddMonths(mod); - return new DateTime(retDate.Year, retDate.Month, 1).AddMonths(1).AddDays(-1); - } - - private static object Networkdays(List p) - { - var date1 = (DateTime)p[0]; - var date2 = (DateTime)p[1]; - var bankHolidays = new List(); - if (p.Count == 3) - { - var t = new Tally {p[2]}; - - bankHolidays.AddRange(t.Select(XLHelper.GetDate)); - } - - return BusinessDaysUntil(date1, date2, bankHolidays); - } - /// /// Calculates number of business days, taking into account: /// - weekends (Saturdays and Sundays) @@ -236,6 +88,198 @@ return businessDays; } + private static object Date(List p) + { + var year = (int) p[0]; + var month = (int) p[1]; + var day = (int) p[2]; + + return (int) Math.Floor(new DateTime(year, month, day).ToOADate()); + } + + private static object Datevalue(List p) + { + var date = (string) p[0]; + + return (int) Math.Floor(DateTime.Parse(date).ToOADate()); + } + + private static object Day(List p) + { + var date = (DateTime) p[0]; + + return date.Day; + } + + private static object Days(List p) + { + Type type; + + int end_date; + + type = p[0]._token.Value.GetType(); + if (type == typeof(string)) + end_date = (int)Datevalue(new List() { p[0] }); + else + end_date = (int)p[0]; + + int start_date; + + type = p[1]._token.Value.GetType(); + if (type == typeof(string)) + start_date = (int)Datevalue(new List() { p[1] }); + else + start_date = (int)p[1]; + + return end_date - start_date; + } + + private static object Days360(List p) + { + var date1 = (DateTime)p[0]; + var date2 = (DateTime)p[1]; + var isEuropean = p.Count == 3 ? p[2] : false; + + return Days360(date1, date2, isEuropean); + } + + private static Int32 Days360(DateTime date1, DateTime date2, Boolean isEuropean) + { + var d1 = date1.Day; + var m1 = date1.Month; + var y1 = date1.Year; + var d2 = date2.Day; + var m2 = date2.Month; + var y2 = date2.Year; + + if (isEuropean) + { + if (d1 == 31) d1 = 30; + if (d2 == 31) d2 = 30; + } + else + { + if (d1 == 31) d1 = 30; + if (d2 == 31 && d1 == 30) d2 = 30; + } + + return 360 * (y2 - y1) + 30 * (m2 - m1) + (d2 - d1); + + } + + private static object Edate(List p) + { + var date = (DateTime)p[0]; + var mod = (int)p[1]; + + var retDate = date.AddMonths(mod); + return retDate; + } + + private static object Eomonth(List p) + { + var start_date = (DateTime)p[0]; + var months = (int)p[1]; + + var retDate = start_date.AddMonths(months); + return new DateTime(retDate.Year, retDate.Month, DateTime.DaysInMonth(retDate.Year, retDate.Month)); + } + + private static Double GetYearAverage(DateTime date1, DateTime date2) + { + var daysInYears = new List(); + for (int year = date1.Year; year <= date2.Year; year++) + daysInYears.Add(DateTime.IsLeapYear(year) ? 366 : 365); + return daysInYears.Average(); + } + + private static object Hour(List p) + { + var date = (DateTime)p[0]; + + return date.Hour; + } + + // http://stackoverflow.com/questions/11154673/get-the-correct-week-number-of-a-given-date + private static object IsoWeekNum(List p) + { + var date = (DateTime)p[0]; + + // Seriously cheat. If its Monday, Tuesday or Wednesday, then it'll + // be the same week# as whatever Thursday, Friday or Saturday are, + // and we always get those right + DayOfWeek day = CultureInfo.InvariantCulture.Calendar.GetDayOfWeek(date); + if (day >= DayOfWeek.Monday && day <= DayOfWeek.Wednesday) + { + date = date.AddDays(3); + } + + // Return the week of our adjusted day + return CultureInfo.InvariantCulture.Calendar.GetWeekOfYear(date, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday); + } + + private static object Minute(List p) + { + var date = (DateTime)p[0]; + + return date.Minute; + } + + private static object Month(List p) + { + var date = (DateTime) p[0]; + + return date.Month; + } + + private static object Networkdays(List p) + { + var date1 = (DateTime)p[0]; + var date2 = (DateTime)p[1]; + var bankHolidays = new List(); + if (p.Count == 3) + { + var t = new Tally { p[2] }; + + bankHolidays.AddRange(t.Select(XLHelper.GetDate)); + } + + return BusinessDaysUntil(date1, date2, bankHolidays); + } + + private static object Now(List p) + { + return DateTime.Now; + } + + private static object Second(List p) + { + var date = (DateTime)p[0]; + + return date.Second; + } + + private static object Time(List p) + { + var hour = (int)p[0]; + var minute = (int)p[1]; + var second = (int)p[2]; + + return new TimeSpan(0, hour, minute, second); + } + + private static object Timevalue(List p) + { + var date = (DateTime)p[0]; + + return (DateTime.MinValue + date.TimeOfDay).ToOADate(); + } + + private static object Today(List p) + { + return DateTime.Today; + } + private static object Weekday(List p) { var dayOfWeek = (int)((DateTime)p[0]).DayOfWeek; @@ -265,7 +309,7 @@ var daysRequired = (int)p[1]; if (daysRequired == 0) return startDate; - if (daysRequired < 0) throw new ArgumentOutOfRangeException("DaysRequired must be >= 0."); + if (daysRequired < 0) throw new ArgumentOutOfRangeException("DaysRequired must be >= 0."); var bankHolidays = new List(); if (p.Count == 3) @@ -280,7 +324,7 @@ private static DateTime Workday(DateTime startDate, DateTime testDate, int daysRequired, IEnumerable bankHolidays) { - + var businessDays = BusinessDaysUntil(startDate, testDate, bankHolidays); if (businessDays == daysRequired) return testDate; @@ -290,6 +334,12 @@ return Workday(startDate, testDate.AddDays(days), daysRequired, bankHolidays); } + private static object Year(List p) + { + var date = (DateTime) p[0]; + + return date.Year; + } private static object Yearfrac(List p) { var date1 = (DateTime) p[0]; @@ -307,14 +357,6 @@ return Days360(date1, date2, true) / 360.0; } - - private static Double GetYearAverage(DateTime date1, DateTime date2) - { - var daysInYears = new List(); - for (int year = date1.Year; year <= date2.Year; year++) - daysInYears.Add(DateTime.IsLeapYear(year) ? 366 : 365); - return daysInYears.Average(); - } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/CalcEngine/Functions/Information.cs b/ClosedXML/Excel/CalcEngine/Functions/Information.cs new file mode 100644 index 0000000..2af09f9 --- /dev/null +++ b/ClosedXML/Excel/CalcEngine/Functions/Information.cs @@ -0,0 +1,193 @@ +using System; +using System.Collections.Generic; +using System.Globalization; + +namespace ClosedXML.Excel.CalcEngine.Functions +{ + internal static class Information + { + public static void Register(CalcEngine ce) + { + //TODO: Add documentation + ce.RegisterFunction("ERRORTYPE",1,ErrorType); + ce.RegisterFunction("ISBLANK", 1,int.MaxValue, IsBlank); + ce.RegisterFunction("ISERR",1, int.MaxValue, IsErr); + ce.RegisterFunction("ISERROR",1, int.MaxValue, IsError); + ce.RegisterFunction("ISEVEN",1, IsEven); + ce.RegisterFunction("ISLOGICAL",1,int.MaxValue,IsLogical); + ce.RegisterFunction("ISNA",1, int.MaxValue, IsNa); + ce.RegisterFunction("ISNONTEXT",1, int.MaxValue, IsNonText); + ce.RegisterFunction("ISNUMBER",1, int.MaxValue, IsNumber); + ce.RegisterFunction("ISODD",1,IsOdd); + ce.RegisterFunction("ISREF",1, int.MaxValue, IsRef); + ce.RegisterFunction("ISTEXT", 1, int.MaxValue, IsText); + ce.RegisterFunction("N",1,N); + ce.RegisterFunction("NA",0,NA); + ce.RegisterFunction("TYPE",1,Type); + } + + static object ErrorType(List p) + { + //TODO: Write Code + throw new NotSupportedException();; + } + + static object IsBlank(List p) + { + var v = (string) p[0]; + var isBlank = string.IsNullOrEmpty(v); + + + if (isBlank && p.Count > 1) { + var sublist = p.GetRange(1, p.Count); + isBlank = (bool)IsBlank(sublist); + } + + return isBlank; + } + + //TODO: Support for Error Values + static object IsErr(List p) + { + //TODO: Write Code + throw new NotSupportedException(); + } + + static object IsError(List p) + { + //TODO: Write Code + throw new NotSupportedException(); + } + + static object IsEven(List p) + { + var v = p[0].Evaluate(); + if (v is double) + { + return Math.Abs((double) v%2) < 1; + } + //TODO: Error Exceptions + throw new ArgumentException("Expression doesn't evaluate to double"); + } + + static object IsLogical(List p) + { + var v = p[0].Evaluate(); + var isLogical = v is bool; + + if (isLogical && p.Count > 1) + { + var sublist = p.GetRange(1, p.Count); + isLogical = (bool) IsLogical(sublist); + } + + return isLogical; + } + + static object IsNa(List p) + { + //TODO: Write Code + throw new NotSupportedException();; + } + + static object IsNonText(List p) + { + return !(bool) IsText(p); + } + + static object IsNumber(List p) + { + var v = p[0].Evaluate(); + + var isNumber = v is double; //Normal number formatting + if (!isNumber) + { + isNumber = v is DateTime; //Handle DateTime Format + } + if (!isNumber) + { + //Handle Number Styles + try + { + var stringValue = (string) v; + double.Parse(stringValue.TrimEnd('%', ' '), NumberStyles.Any); + isNumber = true; + } + catch (Exception) + { + isNumber = false; + } + } + + if (isNumber && p.Count > 1) + { + var sublist = p.GetRange(1, p.Count); + isNumber = (bool)IsNumber(sublist); + } + + return isNumber; + } + + static object IsOdd(List p) + { + return !(bool) IsEven(p); + } + + static object IsRef(List p) + { + //TODO: Write Code + throw new NotSupportedException();; + } + + static object IsText(List p) + { + //Evaluate Expressions + var isText = !(bool) IsBlank(p); + if (isText) + { + isText = !(bool) IsNumber(p); + } + if (isText) + { + isText = !(bool) IsLogical(p); + } + return isText; + } + + static object N(List p) + { + return (double) p[0]; + } + + static object NA(List p) + { + //TODO: Write Code + throw new NotSupportedException();; + } + + static object Type(List p) + { + if ((bool) IsNumber(p)) + { + return 1; + } + if ((bool) IsText(p)) + { + return 2; + } + if ((bool) IsLogical(p)) + { + return 4; + } + if ((bool) IsError(p)) + { + return 16; + } + if(p.Count > 1) + { + return 64; + } + return null; + } + } +} \ No newline at end of file diff --git a/ClosedXML/Excel/CalcEngine/Functions/Is.cs b/ClosedXML/Excel/CalcEngine/Functions/Is.cs deleted file mode 100644 index 544f92e..0000000 --- a/ClosedXML/Excel/CalcEngine/Functions/Is.cs +++ /dev/null @@ -1,21 +0,0 @@ -using System; -using System.Diagnostics; -using System.Collections.Generic; -using System.Text; - -namespace ClosedXML.Excel.CalcEngine -{ - internal static class Is - { - public static void Register(CalcEngine ce) - { - ce.RegisterFunction("ISBLANK", 1, IsBlank); - } - - static object IsBlank(List p) - { - var v = (string)p[0]; - return String.IsNullOrEmpty(v); - } - } -} diff --git a/ClosedXML/Excel/CalcEngine/Functions/Logical.cs b/ClosedXML/Excel/CalcEngine/Functions/Logical.cs index 1b6695e..18d2a9d 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/Logical.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/Logical.cs @@ -1,7 +1,5 @@ using System; -using System.Diagnostics; using System.Collections.Generic; -using System.Text; namespace ClosedXML.Excel.CalcEngine { @@ -15,6 +13,7 @@ ce.RegisterFunction("IF", 2, 3, If); ce.RegisterFunction("TRUE", 0, True); ce.RegisterFunction("FALSE", 0, False); + ce.RegisterFunction("IFERROR",2,IfError); } static object And(List p) @@ -22,41 +21,55 @@ var b = true; foreach (var v in p) { - b = b && (bool)v; + b = b && v; } return b; } + static object Or(List p) { var b = false; foreach (var v in p) { - b = b || (bool)v; + b = b || v; } return b; } + static object Not(List p) { - return !(bool)p[0]; + return !p[0]; } + static object If(List p) { - if ((bool)p[0] ) + if (p[0]) { return p[1].Evaluate(); } - else - { - return p.Count > 2 ? p[2].Evaluate() : false; - } + return p.Count > 2 ? p[2].Evaluate() : false; } + static object True(List p) { return true; } + static object False(List p) { return false; } + + static object IfError(List p) + { + try + { + return p[0].Evaluate(); + } + catch (ArgumentException) + { + return p[1].Evaluate(); + } + } } -} +} \ No newline at end of file diff --git a/ClosedXML/Excel/CalcEngine/Functions/Lookup.cs b/ClosedXML/Excel/CalcEngine/Functions/Lookup.cs index 4a5b720..dfa41bb 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/Lookup.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/Lookup.cs @@ -50,14 +50,16 @@ throw new ApplicationException("col_index_num must be smaller or equal to the number of rows in the table array"); IXLRangeColumn matching_column; - matching_column = range.FindColumn(c => new Expression(c.Cell(1).Value).CompareTo(lookup_value) == 0); + matching_column = range.FindColumn(c => !c.Cell(1).IsEmpty() && new Expression(c.Cell(1).Value).CompareTo(lookup_value) == 0); if (range_lookup && matching_column == null) { + var first_column = range.FirstColumn().ColumnNumber(); matching_column = range.FindColumn(c => { - if (c.ColumnNumber() < range.ColumnsUsed().Count() && new Expression(c.Cell(1).Value).CompareTo(lookup_value) <= 0 && new Expression(c.ColumnRight().Cell(1).Value).CompareTo(lookup_value) > 0) + var column_index_in_range = c.ColumnNumber() - first_column + 1; + if (column_index_in_range < range.ColumnsUsed().Count() && !c.Cell(1).IsEmpty() && new Expression(c.Cell(1).Value).CompareTo(lookup_value) <= 0 && !c.ColumnRight().Cell(1).IsEmpty() && new Expression(c.ColumnRight().Cell(1).Value).CompareTo(lookup_value) > 0) return true; - else if (c.ColumnNumber() == range.ColumnsUsed().Count() && new Expression(c.Cell(1).Value).CompareTo(lookup_value) <= 0) + else if (column_index_in_range == range.ColumnsUsed().Count() && !c.Cell(1).IsEmpty() && new Expression(c.Cell(1).Value).CompareTo(lookup_value) <= 0) return true; else return false; @@ -93,14 +95,16 @@ throw new ApplicationException("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 => new Expression(r.Cell(1).Value).CompareTo(lookup_value) == 0); + matching_row = range.FindRow(r => !r.Cell(1).IsEmpty() && new Expression(r.Cell(1).Value).CompareTo(lookup_value) == 0); if (range_lookup && matching_row == null) { + var first_row = range.FirstRow().RowNumber(); matching_row = range.FindRow(r => { - if (r.RowNumber() < range.RowsUsed().Count() && new Expression(r.Cell(1).Value).CompareTo(lookup_value) <= 0 && new Expression(r.RowBelow().Cell(1).Value).CompareTo(lookup_value) > 0) + var row_index_in_range = r.RowNumber() - first_row + 1; + if (row_index_in_range < range.RowsUsed().Count() && !r.Cell(1).IsEmpty() && new Expression(r.Cell(1).Value).CompareTo(lookup_value) <= 0 && !r.RowBelow().Cell(1).IsEmpty() && new Expression(r.RowBelow().Cell(1).Value).CompareTo(lookup_value) > 0) return true; - else if (r.RowNumber() == range.RowsUsed().Count() && new Expression(r.Cell(1).Value).CompareTo(lookup_value) <= 0) + else if (row_index_in_range == range.RowsUsed().Count() && !r.Cell(1).IsEmpty() && new Expression(r.Cell(1).Value).CompareTo(lookup_value) <= 0) return true; else return false; @@ -115,4 +119,4 @@ .Value; } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/Excel/Cells/XLCell.cs index 1bd50c4..4360176 100644 --- a/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/Excel/Cells/XLCell.cs @@ -1,6 +1,4 @@ -namespace ClosedXML.Excel -{ - using System; + using System; using System.Collections; using System.Collections.Generic; using System.Data; @@ -11,9 +9,14 @@ using System.Text.RegularExpressions; #if NET4 using System.ComponentModel.DataAnnotations; - +#else +using System.ComponentModel; #endif +namespace ClosedXML.Excel +{ + using Attributes; + internal class XLCell : IXLCell, IXLStylized { public static readonly DateTime BaseDate = new DateTime(1899, 12, 30); @@ -56,7 +59,7 @@ , RegexOptions.Compiled); private static readonly Regex R1C1Regex = new Regex( - @"(?<=\W)([Rr]\[?-?\d{0,7}\]?[Cc]\[?-?\d{0,7}\]?)(?=\W)" // R1C1 + @"(?<=\W)([Rr](?:\[-?\d{0,7}\]|\d{0,7})?[Cc](?:\[-?\d{0,7}\]|\d{0,7})?)(?=\W)" // R1C1 + @"|(?<=\W)([Rr]\[?-?\d{0,7}\]?:[Rr]\[?-?\d{0,7}\]?)(?=\W)" // R:R + @"|(?<=\W)([Cc]\[?-?\d{0,5}\]?:[Cc]\[?-?\d{0,5}\]?)(?=\W)", RegexOptions.Compiled); // C:C @@ -572,23 +575,14 @@ { var fieldInfo = m.GetType().GetFields(); var propertyInfo = m.GetType().GetProperties(); + + var columnOrders = fieldInfo.Select(fi => new KeyValuePair(GetFieldOrder(fi.GetCustomAttributes(true)), fi)) + .Concat(propertyInfo.Select(pi => new KeyValuePair(GetFieldOrder(pi.GetCustomAttributes(true)), pi))) + .OrderBy(pair => pair.Key); + if (!hasTitles) { - foreach (var info in fieldInfo) - { - if ((info as IEnumerable) == null) - { - var fieldName = GetFieldName(info.GetCustomAttributes(true)); - if (XLHelper.IsNullOrWhiteSpace(fieldName)) - fieldName = info.Name; - - SetValue(fieldName, fRo, co); - } - - co++; - } - - foreach (var info in propertyInfo) + foreach (var info in columnOrders.Select(o => o.Value)) { if ((info as IEnumerable) == null) { @@ -606,16 +600,17 @@ hasTitles = true; } - foreach (var info in fieldInfo) - { - SetValue(info.GetValue(m), ro, co); - co++; - } - foreach (var info in propertyInfo) + foreach (var info in columnOrders.Select(o => o.Value)) { - if ((info as IEnumerable) == null) - SetValue(info.GetValue(m, null), ro, co); + var fi = info as FieldInfo; + var pi = info as PropertyInfo; + + if (fi != null) + SetValue(fi.GetValue(m), ro, co); + else if (pi != null && info as IEnumerable == null) + SetValue(pi.GetValue(m, null), ro, co); + co++; } } @@ -856,7 +851,7 @@ { double dTest; if (Double.TryParse(_cellValue, XLHelper.NumberStyle, CultureInfo.InvariantCulture, out dTest)) - _cellValue = dTest.ToString(CultureInfo.InvariantCulture); + _cellValue = dTest.ToInvariantString(); else { throw new ArgumentException( @@ -870,7 +865,7 @@ if (_dataType == XLCellValues.Boolean) _cellValue = (_cellValue != "0").ToString(); else if (_dataType == XLCellValues.TimeSpan) - _cellValue = BaseDate.Add(GetTimeSpan()).ToOADate().ToString(CultureInfo.InvariantCulture); + _cellValue = BaseDate.Add(GetTimeSpan()).ToOADate().ToInvariantString(); } } @@ -971,8 +966,6 @@ set { _formulaR1C1 = XLHelper.IsNullOrWhiteSpace(value) ? null : value; - -// FormulaA1 = GetFormulaA1(value); } } @@ -1483,7 +1476,6 @@ private IXLStyle GetStyle() { - //return _style ?? (_style = new XLStyle(this, Worksheet.Workbook.GetStyleById(_styleCacheId))); if (_style != null) return _style; @@ -1766,7 +1758,8 @@ { var matchString = match.Value; var matchIndex = match.Index; - if (value.Substring(0, matchIndex).CharCount('"') % 2 == 0) + if (value.Substring(0, matchIndex).CharCount('"') % 2 == 0 + && value.Substring(0, matchIndex).CharCount('\'') % 2 == 0) { // Check if the match is in between quotes sb.Append(value.Substring(lastIndex, matchIndex - lastIndex)); @@ -1963,14 +1956,15 @@ } - public IXLCell CopyFrom(XLCell otherCell, Boolean copyDataValidations) + public IXLCell CopyFrom(IXLCell otherCell, Boolean copyDataValidations) { - var source = otherCell; - CopyValues(otherCell); + var source = otherCell as XLCell; // To expose GetFormulaR1C1, etc + //var source = castedOtherCell; + CopyValues(source); SetStyle(source._style ?? source.Worksheet.Workbook.GetStyleById(source._styleCacheId)); - var conditionalFormats = otherCell.Worksheet.ConditionalFormats.Where(c => c.Range.Contains(otherCell)).ToList(); + var conditionalFormats = source.Worksheet.ConditionalFormats.Where(c => c.Range.Contains(source)).ToList(); foreach (var cf in conditionalFormats) { var c = new XLConditionalFormat(cf as XLConditionalFormat) {Range = AsRange()}; @@ -1981,7 +1975,7 @@ var f = v.Value; if (v.IsFormula) { - var r1c1 = otherCell.GetFormulaR1C1(f); + var r1c1 = source.GetFormulaR1C1(f); f = GetFormulaA1(r1c1); } @@ -1996,8 +1990,8 @@ { var eventTracking = Worksheet.EventTrackingEnabled; Worksheet.EventTrackingEnabled = false; - if (otherCell.HasDataValidation) - CopyDataValidation(otherCell, otherCell.DataValidation); + if (source.HasDataValidation) + CopyDataValidation(source, source.DataValidation); else if (HasDataValidation) { using (var asRange = AsRange()) @@ -2491,10 +2485,17 @@ var attribute = customAttributes.FirstOrDefault(a => a is DisplayAttribute); return attribute != null ? (attribute as DisplayAttribute).Name : null; #else - return null; + var attribute = customAttributes.FirstOrDefault(a => a is DisplayNameAttribute); + return attribute != null ? (attribute as DisplayNameAttribute).DisplayName : null; #endif } + private static long GetFieldOrder(Object[] customAttributes) + { + var attribute = customAttributes.FirstOrDefault(a => a is ColumnOrderAttribute); + return attribute != null ? (attribute as ColumnOrderAttribute).Order : long.MaxValue; + } + #region Nested type: FormulaConversionType private enum FormulaConversionType @@ -2605,4 +2606,4 @@ public IXLRangeAddress FormulaReference { get; set; } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/Columns/XLColumn.cs b/ClosedXML/Excel/Columns/XLColumn.cs index c713895..9ee72db 100644 --- a/ClosedXML/Excel/Columns/XLColumn.cs +++ b/ClosedXML/Excel/Columns/XLColumn.cs @@ -127,7 +127,7 @@ { asRange.Delete(XLShiftDeletedCells.ShiftCellsLeft); } - + Worksheet.Internals.ColumnsCollection.Remove(columnNumber); var columnsToMove = new List(); columnsToMove.AddRange( @@ -161,7 +161,15 @@ public new IXLCells Cells() { - return CellsUsed(true); + return Cells(true, true); + } + + public new IXLCells Cells(Boolean usedCellsOnly) + { + if (usedCellsOnly) + return Cells(true, true); + else + return Cells(FirstCellUsed().Address.RowNumber, LastCellUsed().Address.RowNumber); } public IXLCells Cells(Int32 firstRow, Int32 lastRow) @@ -433,6 +441,13 @@ } else thisWidthMax = c.Style.Font.GetWidth(c.GetFormattedString(), fontCache); + + if (c.Worksheet.AutoFilter != null + && c.Worksheet.AutoFilter.Range != null + && c.Worksheet.AutoFilter.Range.Contains(c)) + thisWidthMax += 2.7148; // Allow room for arrow icon in autofilter + + if (thisWidthMax >= maxWidth) { colMaxWidth = maxWidth; @@ -763,4 +778,4 @@ return base.IsEmpty(includeFormats); } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFColorScaleConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFColorScaleConverter.cs index 5d970d5..6f38667 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFColorScaleConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFColorScaleConverter.cs @@ -7,20 +7,26 @@ namespace ClosedXML.Excel { - internal class XLCFColorScaleConverter:IXLCFConverter + internal class XLCFColorScaleConverter : IXLCFConverter { public ConditionalFormattingRule Convert(IXLConditionalFormat cf, Int32 priority, XLWorkbook.SaveContext context) { var conditionalFormattingRule = new ConditionalFormattingRule { Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; - + var colorScale = new ColorScale(); - for(Int32 i = 1; i <= cf.Values.Count; i++) + for (Int32 i = 1; i <= cf.ContentTypes.Count; i++) { - var conditionalFormatValueObject = new ConditionalFormatValueObject { Type = cf.ContentTypes[i].ToOpenXml(), Val = cf.Values[i].Value }; + var type = cf.ContentTypes[i].ToOpenXml(); + var val = (cf.Values.ContainsKey(i) && cf.Values[i] != null) ? cf.Values[i].Value : null; + + var conditionalFormatValueObject = new ConditionalFormatValueObject { Type = type }; + if (val != null) + conditionalFormatValueObject.Val = val; + colorScale.Append(conditionalFormatValueObject); } - for (Int32 i = 1; i <= cf.Values.Count; i++) + for (Int32 i = 1; i <= cf.Colors.Count; i++) { Color color = new Color { Rgb = cf.Colors[i].Color.ToHex() }; colorScale.Append(color); diff --git a/ClosedXML/Excel/ConditionalFormats/XLCFColorScaleMax.cs b/ClosedXML/Excel/ConditionalFormats/XLCFColorScaleMax.cs index 64614d8..7fdf8de 100644 --- a/ClosedXML/Excel/ConditionalFormats/XLCFColorScaleMax.cs +++ b/ClosedXML/Excel/ConditionalFormats/XLCFColorScaleMax.cs @@ -22,7 +22,9 @@ } public void HighestValue(XLColor color) { - Maximum(XLCFContentType.Maximum, "0", color); + _conditionalFormat.Values.Add(null); + _conditionalFormat.Colors.Add(color); + _conditionalFormat.ContentTypes.Add(XLCFContentType.Maximum); } } } diff --git a/ClosedXML/Excel/ConditionalFormats/XLCFColorScaleMid.cs b/ClosedXML/Excel/ConditionalFormats/XLCFColorScaleMid.cs index 13c31ec..d868a73 100644 --- a/ClosedXML/Excel/ConditionalFormats/XLCFColorScaleMid.cs +++ b/ClosedXML/Excel/ConditionalFormats/XLCFColorScaleMid.cs @@ -30,7 +30,9 @@ } public void HighestValue(XLColor color) { - Midpoint(XLCFContentType.Maximum, "0", color); + _conditionalFormat.Values.Initialize(null); + _conditionalFormat.Colors.Add(color); + _conditionalFormat.ContentTypes.Add(XLCFContentType.Maximum); } } } diff --git a/ClosedXML/Excel/ConditionalFormats/XLCFColorScaleMin.cs b/ClosedXML/Excel/ConditionalFormats/XLCFColorScaleMin.cs index 0c33c04..9f2b647 100644 --- a/ClosedXML/Excel/ConditionalFormats/XLCFColorScaleMin.cs +++ b/ClosedXML/Excel/ConditionalFormats/XLCFColorScaleMin.cs @@ -27,7 +27,10 @@ public IXLCFColorScaleMid LowestValue(XLColor color) { - return Minimum(XLCFContentType.Minimum, "0", color); + _conditionalFormat.Values.Initialize(null); + _conditionalFormat.Colors.Initialize(color); + _conditionalFormat.ContentTypes.Initialize(XLCFContentType.Minimum); + return new XLCFColorScaleMid(_conditionalFormat); } } } diff --git a/ClosedXML/Excel/Coordinates/XLAddress.cs b/ClosedXML/Excel/Coordinates/XLAddress.cs index d9b9a8e..cdd0703 100644 --- a/ClosedXML/Excel/Coordinates/XLAddress.cs +++ b/ClosedXML/Excel/Coordinates/XLAddress.cs @@ -54,7 +54,7 @@ columnLetter = cellAddressString.Substring(startPos, rowPos); } - rowNumber = int.Parse(cellAddressString.Substring(rowPos + 1), XLHelper.NumberFormatForParse); + rowNumber = int.Parse(cellAddressString.Substring(rowPos + 1), XLHelper.NumberStyle, XLHelper.ParseCulture); } else { @@ -67,7 +67,7 @@ columnLetter = cellAddressString.Substring(startPos, rowPos); } - rowNumber = Int32.Parse(cellAddressString.Substring(rowPos), XLHelper.NumberFormatForParse); + rowNumber = Int32.Parse(cellAddressString.Substring(rowPos), XLHelper.NumberStyle, XLHelper.ParseCulture); } return new XLAddress(worksheet, rowNumber, columnLetter, fixedRow, fixedColumn); } @@ -142,7 +142,7 @@ _fixedColumn = fixedColumn; _fixedRow = fixedRow; - + } #endregion #region Properties @@ -380,4 +380,4 @@ public String UniqueId { get { return RowNumber.ToString("0000000") + ColumnNumber.ToString("00000"); } } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/DataValidation/XLDateCriteria.cs b/ClosedXML/Excel/DataValidation/XLDateCriteria.cs index 792e6b0..8962b0a 100644 --- a/ClosedXML/Excel/DataValidation/XLDateCriteria.cs +++ b/ClosedXML/Excel/DataValidation/XLDateCriteria.cs @@ -12,51 +12,51 @@ public void EqualTo(DateTime value) { - dataValidation.Value = value.ToOADate().ToString(CultureInfo.InvariantCulture); + dataValidation.Value = value.ToOADate().ToInvariantString(); dataValidation.Operator = XLOperator.EqualTo; } public void NotEqualTo(DateTime value) { - dataValidation.Value = value.ToOADate().ToString(CultureInfo.InvariantCulture); + dataValidation.Value = value.ToOADate().ToInvariantString(); dataValidation.Operator = XLOperator.NotEqualTo; } public void GreaterThan(DateTime value) { - dataValidation.Value = value.ToOADate().ToString(CultureInfo.InvariantCulture); + dataValidation.Value = value.ToOADate().ToInvariantString(); dataValidation.Operator = XLOperator.GreaterThan; } public void LessThan(DateTime value) { - dataValidation.Value = value.ToOADate().ToString(CultureInfo.InvariantCulture); + dataValidation.Value = value.ToOADate().ToInvariantString(); dataValidation.Operator = XLOperator.LessThan; } public void EqualOrGreaterThan(DateTime value) { - dataValidation.Value = value.ToOADate().ToString(CultureInfo.InvariantCulture); + dataValidation.Value = value.ToOADate().ToInvariantString(); dataValidation.Operator = XLOperator.EqualOrGreaterThan; } public void EqualOrLessThan(DateTime value) { - dataValidation.Value = value.ToOADate().ToString(CultureInfo.InvariantCulture); + dataValidation.Value = value.ToOADate().ToInvariantString(); dataValidation.Operator = XLOperator.EqualOrLessThan; } public void Between(DateTime minValue, DateTime maxValue) { - dataValidation.MinValue = minValue.ToOADate().ToString(CultureInfo.InvariantCulture); - dataValidation.MaxValue = maxValue.ToOADate().ToString(CultureInfo.InvariantCulture); + dataValidation.MinValue = minValue.ToOADate().ToInvariantString(); + dataValidation.MaxValue = maxValue.ToOADate().ToInvariantString(); dataValidation.Operator = XLOperator.Between; } public void NotBetween(DateTime minValue, DateTime maxValue) { - dataValidation.MinValue = minValue.ToOADate().ToString(CultureInfo.InvariantCulture); - dataValidation.MaxValue = maxValue.ToOADate().ToString(CultureInfo.InvariantCulture); + dataValidation.MinValue = minValue.ToOADate().ToInvariantString(); + dataValidation.MaxValue = maxValue.ToOADate().ToInvariantString(); dataValidation.Operator = XLOperator.NotBetween; } } diff --git a/ClosedXML/Excel/EnumConverter.cs b/ClosedXML/Excel/EnumConverter.cs index e1384ca..f35d89b 100644 --- a/ClosedXML/Excel/EnumConverter.cs +++ b/ClosedXML/Excel/EnumConverter.cs @@ -463,12 +463,12 @@ { case XLPivotCalculation.Normal: return ShowDataAsValues.Normal; case XLPivotCalculation.DifferenceFrom: return ShowDataAsValues.Difference; - case XLPivotCalculation.PctOf: return ShowDataAsValues.Percent; - case XLPivotCalculation.PctDifferenceFrom: return ShowDataAsValues.PercentageDifference; + case XLPivotCalculation.PercentageOf: return ShowDataAsValues.Percent; + case XLPivotCalculation.PercentageDifferenceFrom: return ShowDataAsValues.PercentageDifference; case XLPivotCalculation.RunningTotal: return ShowDataAsValues.RunTotal; - case XLPivotCalculation.PctOfRow: return ShowDataAsValues.PercentOfRaw; // There's a typo in the OpenXML SDK =) - case XLPivotCalculation.PctOfColumn: return ShowDataAsValues.PercentOfColumn; - case XLPivotCalculation.PctOfTotal: return ShowDataAsValues.PercentOfTotal; + case XLPivotCalculation.PercentageOfRow: return ShowDataAsValues.PercentOfRaw; // There's a typo in the OpenXML SDK =) + case XLPivotCalculation.PercentageOfColumn: return ShowDataAsValues.PercentOfColumn; + case XLPivotCalculation.PercentageOfTotal: return ShowDataAsValues.PercentOfTotal; case XLPivotCalculation.Index: return ShowDataAsValues.Index; #region default @@ -1085,12 +1085,12 @@ { case ShowDataAsValues.Normal: return XLPivotCalculation.Normal; case ShowDataAsValues.Difference: return XLPivotCalculation.DifferenceFrom; - case ShowDataAsValues.Percent: return XLPivotCalculation.PctOf; - case ShowDataAsValues.PercentageDifference: return XLPivotCalculation.PctDifferenceFrom; + case ShowDataAsValues.Percent: return XLPivotCalculation.PercentageOf; + case ShowDataAsValues.PercentageDifference: return XLPivotCalculation.PercentageDifferenceFrom; case ShowDataAsValues.RunTotal: return XLPivotCalculation.RunningTotal; - case ShowDataAsValues.PercentOfRaw: return XLPivotCalculation.PctOfRow; // There's a typo in the OpenXML SDK =) - case ShowDataAsValues.PercentOfColumn: return XLPivotCalculation.PctOfColumn; - case ShowDataAsValues.PercentOfTotal: return XLPivotCalculation.PctOfTotal; + case ShowDataAsValues.PercentOfRaw: return XLPivotCalculation.PercentageOfRow; // There's a typo in the OpenXML SDK =) + case ShowDataAsValues.PercentOfColumn: return XLPivotCalculation.PercentageOfColumn; + case ShowDataAsValues.PercentOfTotal: return XLPivotCalculation.PercentageOfTotal; case ShowDataAsValues.Index: return XLPivotCalculation.Index; #region default @@ -1259,4 +1259,4 @@ } #endregion } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/NamedRanges/IXLNamedRange.cs b/ClosedXML/Excel/NamedRanges/IXLNamedRange.cs index 9695475..c1d3831 100644 --- a/ClosedXML/Excel/NamedRanges/IXLNamedRange.cs +++ b/ClosedXML/Excel/NamedRanges/IXLNamedRange.cs @@ -27,6 +27,14 @@ String Comment { get; set; } /// + /// Gets or sets the visibility of this named range. + /// + /// + /// true if visible; otherwise, false. + /// + Boolean Visible { get; set; } + + /// /// Adds the specified range to this named range. /// Note: A named range can point to multiple ranges. /// diff --git a/ClosedXML/Excel/NamedRanges/XLNamedRange.cs b/ClosedXML/Excel/NamedRanges/XLNamedRange.cs index a2958fb..1cef341 100644 --- a/ClosedXML/Excel/NamedRanges/XLNamedRange.cs +++ b/ClosedXML/Excel/NamedRanges/XLNamedRange.cs @@ -13,6 +13,7 @@ private readonly XLNamedRanges _namedRanges; public XLNamedRange(XLNamedRanges namedRanges , String rangeName, String range, String comment = null) { + Visible = true; Name = rangeName; _rangeList.Add(range); Comment = comment; @@ -21,6 +22,7 @@ public XLNamedRange(XLNamedRanges namedRanges, String rangeName, IXLRanges ranges, String comment = null) { + Visible = true; Name = rangeName; ranges.ForEach(r => _rangeList.Add(r.RangeAddress.ToStringFixed(XLReferenceStyle.A1, true))); Comment = comment; @@ -33,7 +35,7 @@ get { var ranges = new XLRanges(); - foreach (var rangeToAdd in + foreach (var rangeToAdd in from rangeAddress in _rangeList.SelectMany(c=>c.Split(',')).Where(s=>s[0] != '"') let match = XLHelper.NamedRangeReferenceRegex.Match(rangeAddress) select @@ -49,6 +51,8 @@ public String Comment { get; set; } + public Boolean Visible { get; set; } + public IXLRanges Add(XLWorkbook workbook, String rangeAddress) { var ranges = new XLRanges(); diff --git a/ClosedXML/Excel/PageSetup/IXLPageSetup.cs b/ClosedXML/Excel/PageSetup/IXLPageSetup.cs index 463c0ee..5376f9a 100644 --- a/ClosedXML/Excel/PageSetup/IXLPageSetup.cs +++ b/ClosedXML/Excel/PageSetup/IXLPageSetup.cs @@ -76,7 +76,7 @@ public enum XLPageOrderValues { DownThenOver, OverThenDown } public enum XLShowCommentsValues { None, AtEnd, AsDisplayed } public enum XLPrintErrorValues { Blank, Dash, Displayed, NA } - + public interface IXLPageSetup { /// @@ -178,8 +178,8 @@ /// The worksheet will be printed on as many pages as necessary to print at the given scale. /// Setting this value will override the PagesWide and PagesTall values. /// - /// The scale at which the worksheet will be printed. - void AdjustTo(Int32 pctOfNormalSize); + /// The scale at which the worksheet will be printed. + void AdjustTo(Int32 percentageOfNormalSize); /// /// Gets or sets the number of pages the worksheet will be printed on. /// This is equivalent to setting both PagesWide and PagesTall properties. diff --git a/ClosedXML/Excel/PageSetup/XLPageSetup.cs b/ClosedXML/Excel/PageSetup/XLPageSetup.cs index 5eaa80a..9aca46d 100644 --- a/ClosedXML/Excel/PageSetup/XLPageSetup.cs +++ b/ClosedXML/Excel/PageSetup/XLPageSetup.cs @@ -8,7 +8,7 @@ { public XLPageSetup(XLPageSetup defaultPageOptions, XLWorksheet worksheet) { - + if (defaultPageOptions != null) { PrintAreas = new XLPrintAreas(defaultPageOptions.PrintAreas as XLPrintAreas, worksheet); @@ -23,7 +23,7 @@ _pagesTall = defaultPageOptions.PagesTall; _pagesWide = defaultPageOptions.PagesWide; _scale = defaultPageOptions.Scale; - + if (defaultPageOptions.Margins != null) { @@ -113,7 +113,7 @@ public IXLMargins Margins { get; set; } private Int32 _pagesWide; - public Int32 PagesWide + public Int32 PagesWide { get { @@ -126,7 +126,7 @@ _scale = 0; } } - + private Int32 _pagesTall; public Int32 PagesTall { @@ -158,9 +158,9 @@ } } - public void AdjustTo(Int32 pctOfNormalSize) + public void AdjustTo(Int32 percentageOfNormalSize) { - Scale = pctOfNormalSize; + Scale = percentageOfNormalSize; _pagesWide = 0; _pagesTall = 0; } diff --git a/ClosedXML/Excel/PivotTables/PivotValues/IXLPivotValue.cs b/ClosedXML/Excel/PivotTables/PivotValues/IXLPivotValue.cs index 48c6190..1784044 100644 --- a/ClosedXML/Excel/PivotTables/PivotValues/IXLPivotValue.cs +++ b/ClosedXML/Excel/PivotTables/PivotValues/IXLPivotValue.cs @@ -24,12 +24,12 @@ { Normal, DifferenceFrom, - PctOf, - PctDifferenceFrom, + PercentageOf, + PercentageDifferenceFrom, RunningTotal, - PctOfRow, - PctOfColumn, - PctOfTotal, + PercentageOfRow, + PercentageOfColumn, + PercentageOfTotal, Index } public enum XLPivotCalculationItem @@ -59,12 +59,12 @@ IXLPivotValue ShowAsNormal(); IXLPivotValueCombination ShowAsDifferenceFrom(String fieldSourceName); - IXLPivotValueCombination ShowAsPctFrom(String fieldSourceName); - IXLPivotValueCombination ShowAsPctDifferenceFrom(String fieldSourceName); + IXLPivotValueCombination ShowAsPercentageFrom(String fieldSourceName); + IXLPivotValueCombination ShowAsPercentageDifferenceFrom(String fieldSourceName); IXLPivotValue ShowAsRunningTotalIn(String fieldSourceName); - IXLPivotValue ShowAsPctOfRow(); - IXLPivotValue ShowAsPctOfColumn(); - IXLPivotValue ShowAsPctOfTotal(); + IXLPivotValue ShowAsPercentageOfRow(); + IXLPivotValue ShowAsPercentageOfColumn(); + IXLPivotValue ShowAsPercentageOfTotal(); IXLPivotValue ShowAsIndex(); } diff --git a/ClosedXML/Excel/PivotTables/PivotValues/IXLPivotValueFormat.cs b/ClosedXML/Excel/PivotTables/PivotValues/IXLPivotValueFormat.cs index 6ccaedf..3c6391c 100644 --- a/ClosedXML/Excel/PivotTables/PivotValues/IXLPivotValueFormat.cs +++ b/ClosedXML/Excel/PivotTables/PivotValues/IXLPivotValueFormat.cs @@ -1,13 +1,11 @@ using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; namespace ClosedXML.Excel { - public interface IXLPivotValueFormat: IXLNumberFormatBase + public interface IXLPivotValueFormat : IXLNumberFormatBase, IEquatable { IXLPivotValue SetNumberFormatId(Int32 value); + IXLPivotValue SetFormat(String value); } } diff --git a/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValue.cs b/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValue.cs index 37ec344..50753cb 100644 --- a/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValue.cs +++ b/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValue.cs @@ -14,7 +14,7 @@ } public IXLPivotValueFormat NumberFormat { get; private set; } - public String SourceName { get; private set; } + public String SourceName { get; private set; } public String CustomName { get; set; } public IXLPivotValue SetCustomName(String value) { CustomName = value; return this; } public XLPivotSummary SummaryFormula { get; set; } public IXLPivotValue SetSummaryFormula(XLPivotSummary value) { SummaryFormula = value; return this; } @@ -34,16 +34,16 @@ SetCalculation(XLPivotCalculation.DifferenceFrom); return new XLPivotValueCombination(this); } - public IXLPivotValueCombination ShowAsPctFrom(String fieldSourceName) + public IXLPivotValueCombination ShowAsPercentageFrom(String fieldSourceName) { BaseField = fieldSourceName; - SetCalculation(XLPivotCalculation.PctOf); + SetCalculation(XLPivotCalculation.PercentageOf); return new XLPivotValueCombination(this); } - public IXLPivotValueCombination ShowAsPctDifferenceFrom(String fieldSourceName) + public IXLPivotValueCombination ShowAsPercentageDifferenceFrom(String fieldSourceName) { BaseField = fieldSourceName; - SetCalculation(XLPivotCalculation.PctDifferenceFrom); + SetCalculation(XLPivotCalculation.PercentageDifferenceFrom); return new XLPivotValueCombination(this); } public IXLPivotValue ShowAsRunningTotalIn(String fieldSourceName) @@ -51,19 +51,19 @@ BaseField = fieldSourceName; return SetCalculation(XLPivotCalculation.RunningTotal); } - public IXLPivotValue ShowAsPctOfRow() + public IXLPivotValue ShowAsPercentageOfRow() { - return SetCalculation(XLPivotCalculation.PctOfRow); + return SetCalculation(XLPivotCalculation.PercentageOfRow); } - public IXLPivotValue ShowAsPctOfColumn() + public IXLPivotValue ShowAsPercentageOfColumn() { - return SetCalculation(XLPivotCalculation.PctOfColumn); + return SetCalculation(XLPivotCalculation.PercentageOfColumn); } - public IXLPivotValue ShowAsPctOfTotal() + public IXLPivotValue ShowAsPercentageOfTotal() { - return SetCalculation(XLPivotCalculation.PctOfTotal); + return SetCalculation(XLPivotCalculation.PercentageOfTotal); } public IXLPivotValue ShowAsIndex() diff --git a/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValueFormat.cs b/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValueFormat.cs index 191d46c..7e5b918 100644 --- a/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValueFormat.cs +++ b/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValueFormat.cs @@ -78,5 +78,27 @@ return _pivotValue; } + #region Overrides + public bool Equals(IXLNumberFormatBase other) + { + return + _numberFormatId == other.NumberFormatId + && _format == other.Format + ; + } + + public override bool Equals(object obj) + { + return Equals((IXLNumberFormatBase)obj); + } + + public override int GetHashCode() + { + return NumberFormatId + ^ Format.GetHashCode(); + } + + #endregion + } } diff --git a/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValues.cs b/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValues.cs index b855923..a7542ba 100644 --- a/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValues.cs +++ b/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValues.cs @@ -8,6 +8,14 @@ internal class XLPivotValues: IXLPivotValues { private readonly Dictionary _pivotValues = new Dictionary(); + + private readonly IXLPivotTable _pivotTable; + + internal XLPivotValues(IXLPivotTable pivotTable) + { + this._pivotTable = pivotTable; + } + public IEnumerator GetEnumerator() { return _pivotValues.Values.GetEnumerator(); @@ -25,7 +33,11 @@ public IXLPivotValue Add(String sourceName, String customName) { var pivotValue = new XLPivotValue(sourceName) { CustomName = customName }; - _pivotValues.Add(sourceName, pivotValue); + _pivotValues.Add(customName, pivotValue); + + if (_pivotValues.Count > 1 && !this._pivotTable.ColumnLabels.Any(cl => cl.SourceName == XLConstants.PivotTableValuesSentinalLabel) && !this._pivotTable.RowLabels.Any(rl => rl.SourceName == XLConstants.PivotTableValuesSentinalLabel)) + _pivotTable.ColumnLabels.Add(XLConstants.PivotTableValuesSentinalLabel); + return pivotValue; } diff --git a/ClosedXML/Excel/PivotTables/XLPivotTable.cs b/ClosedXML/Excel/PivotTables/XLPivotTable.cs index dc2b6ee..b6020cd 100644 --- a/ClosedXML/Excel/PivotTables/XLPivotTable.cs +++ b/ClosedXML/Excel/PivotTables/XLPivotTable.cs @@ -7,13 +7,14 @@ { internal class XLPivotTable: IXLPivotTable { + public XLPivotTable() { Fields = new XLPivotFields(); ReportFilters = new XLPivotFields(); ColumnLabels=new XLPivotFields(); RowLabels = new XLPivotFields(); - Values = new XLPivotValues(); + Values = new XLPivotValues(this); Theme = XLPivotTableTheme.PivotStyleLight16; SetExcelDefaults(); diff --git a/ClosedXML/Excel/Ranges/IXLRangeBase.cs b/ClosedXML/Excel/Ranges/IXLRangeBase.cs index d86698a..de5fa11 100644 --- a/ClosedXML/Excel/Ranges/IXLRangeBase.cs +++ b/ClosedXML/Excel/Ranges/IXLRangeBase.cs @@ -6,7 +6,7 @@ { Workbook, Worksheet - } + } public interface IXLRangeBase: IDisposable { @@ -70,6 +70,10 @@ /// IXLCells Cells(); + IXLCells Cells(Boolean usedCellsOnly); + + IXLCells Cells(Boolean usedCellsOnly, Boolean includeFormats); + IXLCells Cells(String cells); IXLCells Cells(Func predicate); @@ -189,7 +193,7 @@ IXLRange Merge(Boolean checkIntersect); /// - /// Creates a named range out of this range. + /// Creates a named range out of this range. /// If the named range exists, it will add this range to that named range. /// The default scope for the named range is Workbook. /// @@ -197,7 +201,7 @@ IXLRange AddToNamed(String rangeName); /// - /// Creates a named range out of this range. + /// Creates a named range out of this range. /// If the named range exists, it will add this range to that named range. /// Name of the range. /// The scope for the named range. @@ -205,7 +209,7 @@ IXLRange AddToNamed(String rangeName, XLScope scope); /// - /// Creates a named range out of this range. + /// Creates a named range out of this range. /// If the named range exists, it will add this range to that named range. /// Name of the range. /// The scope for the named range. @@ -218,7 +222,7 @@ /// /// Specify what you want to clear. IXLRangeBase Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats); - + /// /// Deletes the cell comments from this range. /// @@ -251,4 +255,4 @@ void Select(); } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/Ranges/XLRangeAddress.cs b/ClosedXML/Excel/Ranges/XLRangeAddress.cs index d5b0e94..ddf27f4 100644 --- a/ClosedXML/Excel/Ranges/XLRangeAddress.cs +++ b/ClosedXML/Excel/Ranges/XLRangeAddress.cs @@ -65,7 +65,7 @@ else { FirstAddress = XLAddress.Create(worksheet, firstPart + "1"); - LastAddress = XLAddress.Create(worksheet, secondPart + XLHelper.MaxRowNumber.ToString(CultureInfo.InvariantCulture)); + LastAddress = XLAddress.Create(worksheet, secondPart + XLHelper.MaxRowNumber.ToInvariantString()); } } diff --git a/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/Excel/Ranges/XLRangeBase.cs index ddb94bf..60d12d4 100644 --- a/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -101,7 +101,7 @@ { var newRanges = new XLRanges { AsRange() }; var dataValidation = new XLDataValidation(newRanges); - + Worksheet.DataValidations.Add(dataValidation); return dataValidation; } @@ -305,7 +305,17 @@ public IXLCells Cells() { - var cells = new XLCells(false, false) {RangeAddress}; + return Cells(false); + } + + public IXLCells Cells(Boolean usedCellsOnly) + { + return Cells(usedCellsOnly, false); + } + + public IXLCells Cells(Boolean usedCellsOnly, Boolean includeFormats) + { + var cells = new XLCells(usedCellsOnly, includeFormats) { RangeAddress }; return cells; } @@ -322,13 +332,7 @@ public IXLCells CellsUsed() { - var cells = new XLCells(true, false) {RangeAddress}; - return cells; - } - - IXLCells IXLRangeBase.CellsUsed(Boolean includeFormats) - { - return CellsUsed(includeFormats); + return Cells(true); } public IXLRange Merge() @@ -370,7 +374,7 @@ clearOptions == XLClearOptions.ContentsAndFormats; foreach (var cell in CellsUsed(includeFormats)) { - cell.Clear(clearOptions, true); + (cell as XLCell).Clear(clearOptions, true); } if (includeFormats) @@ -456,7 +460,7 @@ return Worksheet.Range(RangeAddress.FirstAddress, RangeAddress.LastAddress); } - + public IXLRange AddToNamed(String rangeName) { @@ -502,8 +506,8 @@ public Boolean IsEmpty(Boolean includeFormats) { - return !CellsUsed(includeFormats).Any() || - CellsUsed(includeFormats).Any(c => c.IsEmpty(includeFormats)); + return !CellsUsed(includeFormats).Cast().Any() || + CellsUsed(includeFormats).Cast().Any(c => c.IsEmpty(includeFormats)); } #endregion @@ -632,8 +636,8 @@ } } - - if (sp.Row > 0) + + if (sp.Row > 0) return Worksheet.Cell(sp.Row, sp.Column); return null; @@ -724,7 +728,7 @@ } } - + if (sp.Row > 0) return Worksheet.Cell(sp.Row, sp.Column); @@ -838,7 +842,7 @@ { var newFirstCellAddress = firstCell.Address as XLAddress; var newLastCellAddress = lastCell.Address as XLAddress; - + return GetRange(newFirstCellAddress, newLastCellAddress); } @@ -942,7 +946,7 @@ return address; } - public XLCells CellsUsed(bool includeFormats) + public IXLCells CellsUsed(bool includeFormats) { var cells = new XLCells(true, includeFormats) {RangeAddress}; return cells; @@ -1176,13 +1180,13 @@ : Worksheet.Style; rangeToReturn.Row(ro).Style = styleToUse; } - + } } if(nullReturn) return null; - + return rangeToReturn.Columns(); } @@ -1318,7 +1322,7 @@ newCell.CopyValues(oldCell); newCell.FormulaA1 = oldCell.FormulaA1; cellsToInsert.Add(newKey, newCell); - cellsToDelete.Add(oldKey); + cellsToDelete.Add(oldKey); } } } @@ -1346,7 +1350,7 @@ newCell.FormulaA1 = c.FormulaA1; cellsToInsert.Add(newKey, newCell); cellsToDelete.Add(c.Address); - + } } @@ -1363,7 +1367,7 @@ 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)); - + Int32 firstRowReturn = RangeAddress.FirstAddress.RowNumber; Int32 lastRowReturn = RangeAddress.FirstAddress.RowNumber + numberOfRows - 1; @@ -1384,7 +1388,7 @@ { var modelFirstColumn = model.FirstCellUsed(true); var modelLastColumn = model.LastCellUsed(true); - if (modelLastColumn != null) + if (modelFirstColumn != null && modelLastColumn != null) { Int32 firstCoReturned = modelFirstColumn.Address.ColumnNumber - model.RangeAddress.FirstAddress.ColumnNumber + 1; @@ -1702,7 +1706,7 @@ return (XLPivotTable)Worksheet.PivotTables.AddNew(name, targetCell, AsRange()); } - public IXLAutoFilter SetAutoFilter() + public IXLAutoFilter SetAutoFilter() { using (var asRange = AsRange()) return Worksheet.AutoFilter.Set(asRange); diff --git a/ClosedXML/Excel/Ranges/XLRangeColumn.cs b/ClosedXML/Excel/Ranges/XLRangeColumn.cs index 77fc1ba..50841a9 100644 --- a/ClosedXML/Excel/Ranges/XLRangeColumn.cs +++ b/ClosedXML/Excel/Ranges/XLRangeColumn.cs @@ -2,7 +2,7 @@ { using System; using System.Linq; - + internal class XLRangeColumn : XLRangeBase, IXLRangeColumn { @@ -242,7 +242,7 @@ else if (thisCell.DataType == XLCellValues.TimeSpan) comparison = thisCell.GetTimeSpan().CompareTo(otherCell.GetTimeSpan()); else - comparison = Double.Parse(thisCell.InnerText).CompareTo(Double.Parse(otherCell.InnerText)); + comparison = Double.Parse(thisCell.InnerText, XLHelper.NumberStyle, XLHelper.ParseCulture).CompareTo(Double.Parse(otherCell.InnerText, XLHelper.NumberStyle, XLHelper.ParseCulture)); } else if (e.MatchCase) comparison = String.Compare(thisCell.GetString(), otherCell.GetString(), true); @@ -319,7 +319,7 @@ public IXLTable AsTable() { using (var asRange = AsRange()) - return asRange.AsTable(); + return asRange.AsTable(); } public IXLTable AsTable(string name) diff --git a/ClosedXML/Excel/Rows/XLRow.cs b/ClosedXML/Excel/Rows/XLRow.cs index c431481..b5447d5 100644 --- a/ClosedXML/Excel/Rows/XLRow.cs +++ b/ClosedXML/Excel/Rows/XLRow.cs @@ -224,7 +224,15 @@ public new IXLCells Cells() { - return CellsUsed(true); + return Cells(true, true); + } + + public new IXLCells Cells(Boolean usedCellsOnly) + { + if (usedCellsOnly) + return Cells(true, true); + else + return Cells(FirstCellUsed().Address.ColumnNumber, LastCellUsed().Address.ColumnNumber); } public new IXLCells Cells(String cellsInRow) @@ -707,6 +715,6 @@ return base.IsEmpty(includeFormats); } - + } } diff --git a/ClosedXML/Excel/Style/IXLNumberFormat.cs b/ClosedXML/Excel/Style/IXLNumberFormat.cs index 1490413..2a8ef5e 100644 --- a/ClosedXML/Excel/Style/IXLNumberFormat.cs +++ b/ClosedXML/Excel/Style/IXLNumberFormat.cs @@ -2,9 +2,10 @@ namespace ClosedXML.Excel { - public interface IXLNumberFormat: IXLNumberFormatBase, IEquatable + public interface IXLNumberFormat : IXLNumberFormatBase, IEquatable { IXLStyle SetNumberFormatId(Int32 value); + IXLStyle SetFormat(String value); } } diff --git a/ClosedXML/Excel/Style/XLNumberFormat.cs b/ClosedXML/Excel/Style/XLNumberFormat.cs index 1b6620a..7a15cc7 100644 --- a/ClosedXML/Excel/Style/XLNumberFormat.cs +++ b/ClosedXML/Excel/Style/XLNumberFormat.cs @@ -6,13 +6,11 @@ { #region IXLNumberFormat Members - public bool Equals(IXLNumberFormat other) + public bool Equals(IXLNumberFormatBase other) { - var otherNf = other as XLNumberFormat; - return - _numberFormatId == otherNf._numberFormatId - && _format == otherNf._format + _numberFormatId == other.NumberFormatId + && _format == other.Format ; } @@ -25,7 +23,7 @@ public override bool Equals(object obj) { - return Equals((XLNumberFormat)obj); + return Equals((IXLNumberFormatBase)obj); } public override int GetHashCode() @@ -114,4 +112,4 @@ #endregion } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/Tables/XLTable.cs b/ClosedXML/Excel/Tables/XLTable.cs index a090dfc..5e8f3a6 100644 --- a/ClosedXML/Excel/Tables/XLTable.cs +++ b/ClosedXML/Excel/Tables/XLTable.cs @@ -65,7 +65,7 @@ foreach (var cell in headersRow.Cells()) { var name = cell.GetString(); - if (XLHelper.IsNullOrWhiteSpace(name)) + if (XLHelper.IsNullOrWhiteSpace(name)) { name = "Column" + (cellPos + 1); cell.SetValue(name); @@ -73,7 +73,7 @@ if (_fieldNames.ContainsKey(name)) throw new ArgumentException("The header row contains more than one field name '" + name + "'."); - _fieldNames.Add(name, new XLTableField(this) {Index = cellPos++ }); + _fieldNames.Add(name, new XLTableField(this, name) {Index = cellPos++ }); } } else @@ -87,7 +87,7 @@ { var name = "Column" + i; - _fieldNames.Add(name, new XLTableField(this) {Index = i - 1 }); + _fieldNames.Add(name, new XLTableField(this, name) {Index = i - 1 }); } } } @@ -102,7 +102,7 @@ Int32 cellPos = 0; foreach(var name in fieldNames) { - _fieldNames.Add(name, new XLTableField(this) { Index = cellPos++, Name = name }); + _fieldNames.Add(name, new XLTableField(this, name) { Index = cellPos++ }); } } @@ -164,8 +164,8 @@ public Boolean ShowColumnStripes { get; set; } private Boolean _showAutoFilter; - public Boolean ShowAutoFilter { - get { return _showHeaderRow && _showAutoFilter; } + public Boolean ShowAutoFilter { + get { return _showHeaderRow && _showAutoFilter; } set { _showAutoFilter = value; } } public XLTableTheme Theme { get; set; } @@ -450,7 +450,7 @@ _uniqueNames.Add(c.GetString()); co++; } - + headersRow.Clear(); RangeAddress.FirstAddress = new XLAddress(Worksheet, RangeAddress.FirstAddress.RowNumber + 1, RangeAddress.FirstAddress.ColumnNumber, @@ -462,7 +462,7 @@ else { using(var asRange = Worksheet.Range( - RangeAddress.FirstAddress.RowNumber - 1 , + RangeAddress.FirstAddress.RowNumber - 1 , RangeAddress.FirstAddress.ColumnNumber, RangeAddress.LastAddress.RowNumber, RangeAddress.LastAddress.ColumnNumber @@ -473,7 +473,7 @@ if (firstRow.IsEmpty(true)) { rangeRow = firstRow; - RangeAddress.FirstAddress = new XLAddress(Worksheet, + RangeAddress.FirstAddress = new XLAddress(Worksheet, RangeAddress.FirstAddress.RowNumber - 1, RangeAddress.FirstAddress.ColumnNumber, RangeAddress.FirstAddress.FixedRow, @@ -499,7 +499,7 @@ } Int32 co = 1; - foreach (var name in FieldNames.Keys) + foreach (var name in FieldNames.Values.Select(f => f.Name)) { rangeRow.Cell(co).SetValue(name); co++; @@ -513,7 +513,7 @@ public IXLTable SetShowHeaderRow() { return SetShowHeaderRow(true); - } + } public IXLTable SetShowHeaderRow(Boolean value) { ShowHeaderRow = value; @@ -529,4 +529,4 @@ } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/Tables/XLTableField.cs b/ClosedXML/Excel/Tables/XLTableField.cs index 0f98646..19e9655 100644 --- a/ClosedXML/Excel/Tables/XLTableField.cs +++ b/ClosedXML/Excel/Tables/XLTableField.cs @@ -5,22 +5,28 @@ internal class XLTableField: IXLTableField { private XLTable table; - public XLTableField(XLTable table) + public XLTableField(XLTable table, String name) { this.table = table; + this.name = name; } public Int32 Index { get; internal set; } - public String Name + private String name; + + public String Name { get { - return table.HeadersRow().Cell(Index + 1).GetString(); + return name; } set { - table.HeadersRow().Cell(Index + 1).SetValue(value); + if (table.ShowHeaderRow) + table.HeadersRow().Cell(Index + 1).SetValue(value); + + name = value; } } @@ -28,18 +34,18 @@ public String TotalsRowLabel { get { return totalsRowLabel; } - set + set { totalsRowFunction = XLTotalsRowFunction.None; table.TotalsRow().Cell(Index + 1).SetValue(value); - totalsRowLabel = value; + totalsRowLabel = value; } } public String TotalsRowFormulaA1 { get { return table.TotalsRow().Cell(Index + 1).FormulaA1; } - set + set { totalsRowFunction = XLTotalsRowFunction.Custom; table.TotalsRow().Cell(Index + 1).FormulaA1 = value; @@ -59,7 +65,7 @@ public XLTotalsRowFunction TotalsRowFunction { get { return totalsRowFunction; } - set + set { if (value != XLTotalsRowFunction.None && value != XLTotalsRowFunction.Custom) { @@ -85,7 +91,7 @@ cell.Style.NumberFormat = lastCell.Style.NumberFormat; } } - totalsRowFunction = value; + totalsRowFunction = value; } } } diff --git a/ClosedXML/Excel/XLConstants.cs b/ClosedXML/Excel/XLConstants.cs index f01f06e..d1cbe58 100644 --- a/ClosedXML/Excel/XLConstants.cs +++ b/ClosedXML/Excel/XLConstants.cs @@ -1,9 +1,10 @@ - -namespace ClosedXML.Excel +namespace ClosedXML.Excel { //Use the class to store magic strings or variables. - internal static class XLConstants + public static class XLConstants { + public const string PivotTableValuesSentinalLabel = "{{Values}}"; + internal static class Comment { internal const string ShapeTypeId = "#_x0000_t202"; diff --git a/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/Excel/XLWorkbook.cs index ac31b4e..3cfe9ef 100644 --- a/ClosedXML/Excel/XLWorkbook.cs +++ b/ClosedXML/Excel/XLWorkbook.cs @@ -175,7 +175,7 @@ private readonly Dictionary _stylesByStyle = new Dictionary(); public XLEventTracking EventTracking { get; set; } - + internal Int32 GetStyleId(IXLStyle style) { Int32 cached; @@ -411,16 +411,28 @@ /// public void Save() { +#if DEBUG + Save(true); +#else + Save(false); +#endif + } + + /// + /// Saves the current workbook and optionally performs validation + /// + public void Save(bool validate) + { checkForWorksheetsPresent(); if (_loadSource == XLLoadSource.New) throw new Exception("This is a new file, please use one of the SaveAs methods."); if (_loadSource == XLLoadSource.Stream) { - CreatePackage(_originalStream, false, _spreadsheetDocumentType); + CreatePackage(_originalStream, false, _spreadsheetDocumentType, validate); } else - CreatePackage(_originalFile, _spreadsheetDocumentType); + CreatePackage(_originalFile, _spreadsheetDocumentType, validate); } /// @@ -428,6 +440,18 @@ /// public void SaveAs(String file) { +#if DEBUG + SaveAs(file, true); +#else + SaveAs(file, false); +#endif + } + + /// + /// Saves the current workbook to a file and optionally validates it. + /// + public void SaveAs(String file, Boolean validate) + { checkForWorksheetsPresent(); PathHelper.CreateDirectory(Path.GetDirectoryName(file)); if (_loadSource == XLLoadSource.New) @@ -435,14 +459,14 @@ if (File.Exists(file)) File.Delete(file); - CreatePackage(file, GetSpreadsheetDocumentType(file)); + CreatePackage(file, GetSpreadsheetDocumentType(file), validate); } else if (_loadSource == XLLoadSource.File) { if (String.Compare(_originalFile.Trim(), file.Trim(), true) != 0) File.Copy(_originalFile, file, true); - CreatePackage(file, GetSpreadsheetDocumentType(file)); + CreatePackage(file, GetSpreadsheetDocumentType(file), validate); } else if (_loadSource == XLLoadSource.Stream) { @@ -452,7 +476,7 @@ { CopyStream(_originalStream, fileStream); //fileStream.Position = 0; - CreatePackage(fileStream, false, _spreadsheetDocumentType); + CreatePackage(fileStream, false, _spreadsheetDocumentType, validate); fileStream.Close(); } } @@ -481,6 +505,18 @@ /// public void SaveAs(Stream stream) { +#if DEBUG + SaveAs(stream, true); +#else + SaveAs(stream, false); +#endif + } + + /// + /// Saves the current workbook to a stream and optionally validates it. + /// + public void SaveAs(Stream stream, Boolean validate) + { checkForWorksheetsPresent(); if (_loadSource == XLLoadSource.New) { @@ -491,13 +527,13 @@ if (stream.CanRead && stream.CanSeek && stream.CanWrite) { // all is fine the package can be created in a direct way - CreatePackage(stream, true, _spreadsheetDocumentType); + CreatePackage(stream, true, _spreadsheetDocumentType, validate); } else { // the harder way MemoryStream ms = new MemoryStream(); - CreatePackage(ms, true, _spreadsheetDocumentType); + CreatePackage(ms, true, _spreadsheetDocumentType, validate); // not really nessesary, because I changed CopyStream too. // but for better understanding and if somebody in the future // provide an changed version of CopyStream @@ -512,7 +548,7 @@ CopyStream(fileStream, stream); fileStream.Close(); } - CreatePackage(stream, false, _spreadsheetDocumentType); + CreatePackage(stream, false, _spreadsheetDocumentType, validate); } else if (_loadSource == XLLoadSource.Stream) { @@ -520,7 +556,7 @@ if (_originalStream != stream) CopyStream(_originalStream, stream); - CreatePackage(stream, false, _spreadsheetDocumentType); + CreatePackage(stream, false, _spreadsheetDocumentType, validate); } } @@ -589,24 +625,24 @@ return columns; } - #region Fields +#region Fields private readonly XLLoadSource _loadSource = XLLoadSource.New; private readonly String _originalFile; private readonly Stream _originalStream; - #endregion +#endregion - #region Constructor +#region Constructor - + /// /// Creates a new Excel workbook. /// public XLWorkbook() :this(XLEventTracking.Enabled) { - + } public XLWorkbook(XLEventTracking eventTracking) @@ -665,7 +701,7 @@ /// The stream to open. public XLWorkbook(Stream stream):this(stream, XLEventTracking.Enabled) { - + } public XLWorkbook(Stream stream, XLEventTracking eventTracking) @@ -676,9 +712,9 @@ Load(stream); } - #endregion +#endregion - #region Nested type: UnsupportedSheet +#region Nested type: UnsupportedSheet internal sealed class UnsupportedSheet { @@ -687,7 +723,7 @@ public Int32 Position; } - #endregion +#endregion public IXLCell Cell(String namedCell) { @@ -814,4 +850,4 @@ LockWindows = LockWindows; } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs index 6aa64ab..b551198 100644 --- a/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/Excel/XLWorkbook_Load.cs @@ -1,17 +1,16 @@ #region +using ClosedXML.Utils; +using DocumentFormat.OpenXml; +using DocumentFormat.OpenXml.Packaging; +using DocumentFormat.OpenXml.Spreadsheet; using System; using System.Collections.Generic; using System.Globalization; using System.IO; using System.Linq; -using DocumentFormat.OpenXml; -using DocumentFormat.OpenXml.Packaging; -using DocumentFormat.OpenXml.Spreadsheet; using Ap = DocumentFormat.OpenXml.ExtendedProperties; using Op = DocumentFormat.OpenXml.CustomProperties; -using Vml = DocumentFormat.OpenXml.Vml; -using Ss = DocumentFormat.OpenXml.Vml.Spreadsheet; #endregion @@ -19,11 +18,10 @@ { #region - using System.Drawing; using Ap; using Op; + using System.Drawing; using System.Xml.Linq; - using System.Text.RegularExpressions; #endregion @@ -123,7 +121,6 @@ var referenceMode = calculationProperties.ReferenceMode; if (referenceMode != null) ReferenceStyle = referenceMode.Value.ToClosedXml(); - } var efp = dSpreadsheet.ExtendedFilePropertiesPart; @@ -149,11 +146,11 @@ Fonts fonts = s == null ? null : s.Fonts; Int32 dfCount = 0; Dictionary differentialFormats; - if (s != null &&s.DifferentialFormats != null) + if (s != null && s.DifferentialFormats != null) differentialFormats = s.DifferentialFormats.Elements().ToDictionary(k => dfCount++); else differentialFormats = new Dictionary(); - + var sheets = dSpreadsheet.WorkbookPart.Workbook.Sheets; Int32 position = 0; foreach (Sheet dSheet in sheets.OfType()) @@ -165,16 +162,15 @@ if (wsPart == null) { - UnsupportedSheets.Add(new UnsupportedSheet {SheetId = dSheet.SheetId.Value, Position = position}); + UnsupportedSheets.Add(new UnsupportedSheet { SheetId = dSheet.SheetId.Value, Position = position }); continue; } var sheetName = dSheet.Name; - var ws = (XLWorksheet) WorksheetsInternal.Add(sheetName, position); + var ws = (XLWorksheet)WorksheetsInternal.Add(sheetName, position); ws.RelId = dSheet.Id; - ws.SheetId = (Int32) dSheet.SheetId.Value; - + ws.SheetId = (Int32)dSheet.SheetId.Value; if (dSheet.State != null) ws.Visibility = dSheet.State.Value.ToClosedXml(); @@ -248,7 +244,6 @@ LoadColumnBreaks((ColumnBreaks)reader.LoadCurrentElement(), ws); else if (reader.ElementType == typeof(LegacyDrawing)) ws.LegacyDrawingId = (reader.LoadCurrentElement() as LegacyDrawing).Id.Value; - } reader.Close(); } @@ -264,7 +259,7 @@ { xlTable._showHeaderRow = false; //foreach (var tableColumn in dTable.TableColumns.Cast()) - xlTable.AddFields(dTable.TableColumns.Cast().Select(t=>GetTableColumnName(t.Name.Value))); + xlTable.AddFields(dTable.TableColumns.Cast().Select(t => GetTableColumnName(t.Name.Value))); } else { @@ -272,7 +267,7 @@ } if (dTable.TotalsRowCount != null && dTable.TotalsRowCount.Value > 0) - ((XLTable) xlTable)._showTotalsRow = true; + ((XLTable)xlTable)._showTotalsRow = true; if (dTable.TableStyleInfo != null) { @@ -296,15 +291,13 @@ xlTable.Theme = XLTableTheme.None; } - if (dTable.AutoFilter != null) { xlTable.ShowAutoFilter = true; - LoadAutoFilterColumns( dTable.AutoFilter, (xlTable as XLTable).AutoFilter); + LoadAutoFilterColumns(dTable.AutoFilter, (xlTable as XLTable).AutoFilter); } else xlTable.ShowAutoFilter = false; - if (xlTable.ShowTotalsRow) { @@ -328,47 +321,49 @@ xlTable.RangeAddress.LastAddress.RowNumber - 1, xlTable.RangeAddress.LastAddress.ColumnNumber); } else if (xlTable.AutoFilter != null) - xlTable.AutoFilter.Range = xlTable.Worksheet.Range(xlTable.RangeAddress); + xlTable.AutoFilter.Range = xlTable.Worksheet.Range(xlTable.RangeAddress); } #endregion #region LoadComments - if (wsPart.WorksheetCommentsPart != null) { + if (wsPart.WorksheetCommentsPart != null) + { var root = wsPart.WorksheetCommentsPart.Comments; var authors = root.GetFirstChild().ChildElements; var comments = root.GetFirstChild().ChildElements; // **** MAYBE FUTURE SHAPE SIZE SUPPORT XDocument xdoc = GetCommentVmlFile(wsPart); - - foreach (Comment c in comments) { + + foreach (Comment c in comments) + { // find cell by reference var cell = ws.Cell(c.Reference); - + XLComment xlComment = cell.Comment as XLComment; xlComment.Author = authors[(int)c.AuthorId.Value].InnerText; //xlComment.ShapeId = (Int32)c.ShapeId.Value; //ShapeIdManager.Add(xlComment.ShapeId); var runs = c.GetFirstChild().Elements(); - foreach (Run run in runs) { + foreach (Run run in runs) + { var runProperties = run.RunProperties; String text = run.Text.InnerText.FixNewLines(); var rt = cell.Comment.AddText(text); LoadFont(runProperties, rt); } - XElement shape = GetCommentShape(xdoc); - + LoadShapeProperties(xlComment, shape); var clientData = shape.Elements().First(e => e.Name.LocalName == "ClientData"); LoadClientData(xlComment, clientData); - var textBox = shape.Elements().First(e=>e.Name.LocalName == "textbox"); + var textBox = shape.Elements().First(e => e.Name.LocalName == "textbox"); LoadTextBox(xlComment, textBox); var alt = shape.Attribute("alt"); @@ -378,7 +373,7 @@ //var insetmode = (string)shape.Attributes().First(a=> a.Name.LocalName == "insetmode"); //xlComment.Style.Margins.Automatic = insetmode != null && insetmode.Equals("auto"); - + shape.Remove(); } } @@ -395,12 +390,12 @@ if (workbookView != null && workbookView.ActiveTab != null) { UnsupportedSheet unsupportedSheet = - UnsupportedSheets.FirstOrDefault(us => us.Position == (Int32) (workbookView.ActiveTab.Value + 1)); + UnsupportedSheets.FirstOrDefault(us => us.Position == (Int32)(workbookView.ActiveTab.Value + 1)); if (unsupportedSheet != null) unsupportedSheet.IsActive = true; else { - Worksheet((Int32) (workbookView.ActiveTab.Value + 1)).SetTabActive(); + Worksheet((Int32)(workbookView.ActiveTab.Value + 1)).SetTabActive(); } } } @@ -434,19 +429,18 @@ XElement shape; if (xml != null) shape = - xml.Elements().FirstOrDefault(e => (string) e.Attribute("type") == XLConstants.Comment.ShapeTypeId); + xml.Elements().FirstOrDefault(e => (string)e.Attribute("type") == XLConstants.Comment.ShapeTypeId); else shape = xdoc.Root.Elements().FirstOrDefault(e => - (string) e.Attribute("type") == + (string)e.Attribute("type") == XLConstants.Comment.ShapeTypeId || - (string) e.Attribute("type") == + (string)e.Attribute("type") == XLConstants.Comment.AlternateShapeTypeId); return shape; } #endregion - private String GetTableColumnName(string name) { return name.Replace("_x000a_", Environment.NewLine).Replace("_x005f_x000a_", "_x000a_"); @@ -466,7 +460,6 @@ { return XLColor.FromHtml(color); } - } private void LoadColorsAndLines(IXLDrawing drawing, XElement shape) @@ -489,14 +482,14 @@ { String opacityVal = opacity.Value; if (opacityVal.EndsWith("f")) - drawing.Style.ColorsAndLines.FillTransparency = + drawing.Style.ColorsAndLines.FillTransparency = Double.Parse(opacityVal.Substring(0, opacityVal.Length - 1), CultureInfo.InvariantCulture) / 65536.0; else drawing.Style.ColorsAndLines.FillTransparency = Double.Parse(opacityVal, CultureInfo.InvariantCulture); } } - var stroke = shape.Elements().FirstOrDefault(e=>e.Name.LocalName == "stroke"); + var stroke = shape.Elements().FirstOrDefault(e => e.Name.LocalName == "stroke"); if (stroke != null) { var opacity = stroke.Attribute("opacity"); @@ -504,7 +497,7 @@ { String opacityVal = opacity.Value; if (opacityVal.EndsWith("f")) - drawing.Style.ColorsAndLines.LineTransparency = + drawing.Style.ColorsAndLines.LineTransparency = Double.Parse(opacityVal.Substring(0, opacityVal.Length - 1), CultureInfo.InvariantCulture) / 65536.0; else drawing.Style.ColorsAndLines.LineTransparency = Double.Parse(opacityVal, CultureInfo.InvariantCulture); @@ -522,7 +515,7 @@ else drawing.Style.ColorsAndLines.LineDash = XLDashStyle.SquareDot; } - else + else { switch (dashStyleVal) { @@ -541,7 +534,7 @@ String lineStyleVal = lineStyle.Value.ToLower(); switch (lineStyleVal) { - case "single": drawing.Style.ColorsAndLines.LineStyle = XLLineStyle.Single ; break; + case "single": drawing.Style.ColorsAndLines.LineStyle = XLLineStyle.Single; break; case "thickbetweenthin": drawing.Style.ColorsAndLines.LineStyle = XLLineStyle.ThickBetweenThin; break; case "thickthin": drawing.Style.ColorsAndLines.LineStyle = XLLineStyle.ThickThin; break; case "thinthick": drawing.Style.ColorsAndLines.LineStyle = XLLineStyle.ThinThick; break; @@ -597,6 +590,7 @@ if (value.Equals("bottom-to-top")) xlDrawing.Style.Alignment.SetOrientation(XLDrawingTextOrientation.BottomToTop); else if (value.Equals("top-to-bottom")) xlDrawing.Style.Alignment.SetOrientation(XLDrawingTextOrientation.Vertical); break; + case "layout-flow": isVertical = value.Equals("vertical"); break; case "mso-direction-alt": if (value == "auto") xlDrawing.Style.Alignment.Direction = XLDrawingTextDirection.Context; break; case "direction": if (value == "RTL") xlDrawing.Style.Alignment.Direction = XLDrawingTextDirection.RightToLeft; break; @@ -608,7 +602,7 @@ private void LoadClientData(IXLDrawing drawing, XElement clientData) { - var anchor = clientData.Elements().FirstOrDefault(e=>e.Name.LocalName == "Anchor"); + var anchor = clientData.Elements().FirstOrDefault(e => e.Name.LocalName == "Anchor"); if (anchor != null) LoadClientDataAnchor(drawing, anchor); LoadDrawingPositioning(drawing, clientData); @@ -643,7 +637,6 @@ Boolean lockText = lockTextElement != null && lockTextElement.Value.ToLower() == "true"; drawing.Style.Protection.Locked = locked; drawing.Style.Protection.LockText = lockText; - } private static void LoadDrawingPositioning(IXLDrawing drawing, XElement clientData) @@ -692,7 +685,6 @@ case "z-index": xlDrawing.ZOrder = Int32.Parse(value); break; } } - } private readonly Dictionary knownUnits = new Dictionary @@ -711,7 +703,6 @@ return Double.Parse(value.Replace(knownUnit.Key, String.Empty), CultureInfo.InvariantCulture) * knownUnit.Value; } - private void LoadDefinedNames(Workbook workbook) { @@ -720,6 +711,8 @@ foreach (DefinedName definedName in workbook.DefinedNames) { var name = definedName.Name; + var visible = true; + if (definedName.Hidden != null) visible = !BooleanValue.ToBoolean(definedName.Hidden); if (name == "_xlnm.Print_Area") { foreach (string area in definedName.Text.Split(',')) @@ -757,12 +750,12 @@ if (localSheetId == null) { if (!NamedRanges.Any(nr => nr.Name == name)) - NamedRanges.Add(name, text, comment); + NamedRanges.Add(name, text, comment).Visible = visible; } else { if (!Worksheet(Int32.Parse(localSheetId) + 1).NamedRanges.Any(nr => nr.Name == name)) - Worksheet(Int32.Parse(localSheetId) + 1).NamedRanges.Add(name, text, comment); + Worksheet(Int32.Parse(localSheetId) + 1).NamedRanges.Add(name, text, comment).Visible = visible; } } } @@ -816,6 +809,7 @@ } private Int32 lastCell; + private void LoadCells(SharedStringItem[] sharedStrings, Stylesheet s, NumberingFormats numberingFormats, Fills fills, Borders borders, Fonts fonts, Dictionary sharedFormulasR1C1, XLWorksheet ws, Dictionary styleList, Cell cell, Int32 rowIndex) @@ -837,7 +831,6 @@ styleList.Add(styleIndex, xlCell.Style); } - if (cell.CellFormula != null && cell.CellFormula.SharedIndex != null && cell.CellFormula.Reference != null) { String formula; @@ -880,77 +873,35 @@ { if (cell.DataType == CellValues.InlineString) { - xlCell._cellValue = cell.InlineString != null && cell.InlineString.Text != null ? cell.InlineString.Text.Text.FixNewLines() : String.Empty; + if (cell.InlineString != null) + { + if (cell.InlineString.Text != null) + xlCell._cellValue = cell.InlineString.Text.Text.FixNewLines(); + else + ParseCellValue(cell.InlineString, xlCell); + } + else + xlCell._cellValue = String.Empty; + xlCell._dataType = XLCellValues.Text; xlCell.ShareString = false; } else if (cell.DataType == CellValues.SharedString) { - if (cell.CellValue != null) + if (cell.CellValue != null && !XLHelper.IsNullOrWhiteSpace(cell.CellValue.Text)) { - if (!XLHelper.IsNullOrWhiteSpace(cell.CellValue.Text)) - { - var sharedString = sharedStrings[Int32.Parse(cell.CellValue.Text, XLHelper.NumberStyle, XLHelper.ParseCulture)]; - - var runs = sharedString.Elements(); - var phoneticRuns = sharedString.Elements(); - var phoneticProperties = sharedString.Elements(); - Boolean hasRuns = false; - foreach (Run run in runs) - { - var runProperties = run.RunProperties; - String text = run.Text.InnerText.FixNewLines(); - - if (runProperties == null) - xlCell.RichText.AddText(text, xlCell.Style.Font); - else - { - var rt = xlCell.RichText.AddText(text); - LoadFont(runProperties, rt); - } - if (!hasRuns) - hasRuns = true; - } - - if(!hasRuns) - xlCell._cellValue = sharedString.Text.InnerText; - - #region Load PhoneticProperties - - var pp = phoneticProperties.FirstOrDefault(); - if (pp != null) - { - if (pp.Alignment != null) - xlCell.RichText.Phonetics.Alignment = pp.Alignment.Value.ToClosedXml(); - if (pp.Type != null) - xlCell.RichText.Phonetics.Type = pp.Type.Value.ToClosedXml(); - - LoadFont(pp, xlCell.RichText.Phonetics); - } - - #endregion - - #region Load Phonetic Runs - - foreach (PhoneticRun pr in phoneticRuns) - { - xlCell.RichText.Phonetics.Add(pr.Text.InnerText.FixNewLines(), (Int32)pr.BaseTextStartIndex.Value, - (Int32) pr.EndingBaseIndex.Value); - } - - #endregion - } - else - xlCell._cellValue = cell.CellValue.Text.FixNewLines(); + var sharedString = sharedStrings[Int32.Parse(cell.CellValue.Text, XLHelper.NumberStyle, XLHelper.ParseCulture)]; + ParseCellValue(sharedString, xlCell); } else xlCell._cellValue = String.Empty; + xlCell._dataType = XLCellValues.Text; } else if (cell.DataType == CellValues.Date) { if (!XLHelper.IsNullOrWhiteSpace(cell.CellValue.Text)) - xlCell._cellValue = Double.Parse(cell.CellValue.Text, CultureInfo.InvariantCulture).ToInvariantString(); + xlCell._cellValue = Double.Parse(cell.CellValue.Text, XLHelper.NumberStyle, XLHelper.ParseCulture).ToInvariantString(); xlCell._dataType = XLCellValues.DateTime; } else if (cell.DataType == CellValues.Boolean) @@ -961,7 +912,7 @@ else if (cell.DataType == CellValues.Number) { if (!XLHelper.IsNullOrWhiteSpace(cell.CellValue.Text)) - xlCell._cellValue = Double.Parse(cell.CellValue.Text, CultureInfo.InvariantCulture).ToInvariantString(); + xlCell._cellValue = Double.Parse(cell.CellValue.Text, XLHelper.NumberStyle, XLHelper.ParseCulture).ToInvariantString(); if (s == null) { xlCell._dataType = XLCellValues.Number; @@ -974,7 +925,6 @@ else xlCell._dataType = XLCellValues.Number; } - } } else if (cell.CellValue != null) @@ -985,22 +935,21 @@ } else { - var numberFormatId = ((CellFormat) (s.CellFormats).ElementAt(styleIndex)).NumberFormatId; + 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)) + s.NumberingFormats.Any(nf => ((NumberingFormat)nf).NumberFormatId.Value == numberFormatId)) { xlCell.Style.NumberFormat.Format = - ((NumberingFormat) s.NumberingFormats + ((NumberingFormat)s.NumberingFormats .First( - nf => ((NumberingFormat) nf).NumberFormatId.Value == numberFormatId) + nf => ((NumberingFormat)nf).NumberFormatId.Value == numberFormatId) ).FormatCode.Value; } 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) || @@ -1014,6 +963,63 @@ } } + /// + /// Parses the cell value for normal or rich text + /// Input element should either be a shared string or inline string + /// + /// The element (either a shared string or inline string) + /// The cell. + private void ParseCellValue(RstType element, XLCell xlCell) + { + var runs = element.Elements(); + var phoneticRuns = element.Elements(); + var phoneticProperties = element.Elements(); + Boolean hasRuns = false; + foreach (Run run in runs) + { + var runProperties = run.RunProperties; + String text = run.Text.InnerText.FixNewLines(); + + if (runProperties == null) + xlCell.RichText.AddText(text, xlCell.Style.Font); + else + { + var rt = xlCell.RichText.AddText(text); + LoadFont(runProperties, rt); + } + if (!hasRuns) + hasRuns = true; + } + + if (!hasRuns) + xlCell._cellValue = XmlEncoder.DecodeString(element.Text.InnerText); + + #region Load PhoneticProperties + + var pp = phoneticProperties.FirstOrDefault(); + if (pp != null) + { + if (pp.Alignment != null) + xlCell.RichText.Phonetics.Alignment = pp.Alignment.Value.ToClosedXml(); + if (pp.Type != null) + xlCell.RichText.Phonetics.Type = pp.Type.Value.ToClosedXml(); + + LoadFont(pp, xlCell.RichText.Phonetics); + } + + #endregion + + #region Load Phonetic Runs + + foreach (PhoneticRun pr in phoneticRuns) + { + xlCell.RichText.Phonetics.Add(pr.Text.InnerText.FixNewLines(), (Int32)pr.BaseTextStartIndex.Value, + (Int32)pr.EndingBaseIndex.Value); + } + + #endregion + } + private void LoadNumberFormat(NumberingFormat nfSource, IXLNumberFormat nf) { if (nfSource == null) return; @@ -1030,7 +1036,7 @@ LoadBorderValues(borderSource.DiagonalBorder, border.SetDiagonalBorder, border.SetDiagonalBorderColor); - if (borderSource.DiagonalUp != null ) + if (borderSource.DiagonalUp != null) border.DiagonalUp = borderSource.DiagonalUp.Value; if (borderSource.DiagonalDown != null) border.DiagonalDown = borderSource.DiagonalDown.Value; @@ -1039,10 +1045,9 @@ LoadBorderValues(borderSource.RightBorder, border.SetRightBorder, border.SetRightBorderColor); LoadBorderValues(borderSource.TopBorder, border.SetTopBorder, border.SetTopBorderColor); LoadBorderValues(borderSource.BottomBorder, border.SetBottomBorder, border.SetBottomBorderColor); - } - private void LoadBorderValues(BorderPropertiesType source, Func setBorder, Func setColor ) + private void LoadBorderValues(BorderPropertiesType source, Func setBorder, Func setColor) { if (source != null) { @@ -1053,13 +1058,11 @@ } } - - private void LoadFill(Fill fillSource, IXLFill fill) { if (fillSource == null) return; - if(fillSource.PatternFill != null) + if (fillSource.PatternFill != null) { if (fillSource.PatternFill.PatternType != null) fill.PatternType = fillSource.PatternFill.PatternType.Value.ToClosedXml(); @@ -1086,7 +1089,7 @@ fontSource.Elements().FirstOrDefault(); if (fontFamilyNumbering != null && fontFamilyNumbering.Val != null) fontBase.FontFamilyNumbering = - (XLFontFamilyNumberingValues) Int32.Parse(fontFamilyNumbering.Val.ToString()); + (XLFontFamilyNumberingValues)Int32.Parse(fontFamilyNumbering.Val.ToString()); var runFont = fontSource.Elements().FirstOrDefault(); if (runFont != null) { @@ -1111,21 +1114,22 @@ } var verticalTextAlignment = fontSource.Elements().FirstOrDefault(); - + if (verticalTextAlignment == null) return; fontBase.VerticalAlignment = verticalTextAlignment.Val != null ? verticalTextAlignment.Val.Value.ToClosedXml() : XLFontVerticalTextAlignmentValues.Baseline; } private Int32 lastRow; + private void LoadRows(Stylesheet s, NumberingFormats numberingFormats, Fills fills, Borders borders, Fonts fonts, XLWorksheet ws, SharedStringItem[] sharedStrings, Dictionary sharedFormulasR1C1, Dictionary styleList, Row row) { - Int32 rowIndex = row.RowIndex == null ? ++lastRow : (Int32) row.RowIndex.Value; + Int32 rowIndex = row.RowIndex == null ? ++lastRow : (Int32)row.RowIndex.Value; var xlRow = ws.Row(rowIndex, false); - + if (row.Height != null) xlRow.Height = row.Height; else @@ -1149,7 +1153,7 @@ Int32 styleIndex = row.StyleIndex != null ? Int32.Parse(row.StyleIndex.InnerText) : -1; if (styleIndex > 0) { - ApplyStyle(xlRow, styleIndex, s, fills, borders, fonts, numberingFormats); + ApplyStyle(xlRow, styleIndex, s, fills, borders, fonts, numberingFormats); } else { @@ -1185,7 +1189,7 @@ //IXLStylized toApply; if (col.Max == XLHelper.MaxColumnNumber) continue; - var xlColumns = (XLColumns) ws.Columns(col.Min, col.Max); + var xlColumns = (XLColumns)ws.Columns(col.Min, col.Max); if (col.Width != null) { Double width = col.Width - ColumnWidthOffset; @@ -1293,6 +1297,7 @@ else condition = o => (o as IComparable).CompareTo(xlFilter.Value) == 0; break; + case XLFilterOperator.EqualOrGreaterThan: condition = o => (o as IComparable).CompareTo(xlFilter.Value) >= 0; break; case XLFilterOperator.EqualOrLessThan: condition = o => (o as IComparable).CompareTo(xlFilter.Value) <= 0; break; case XLFilterOperator.GreaterThan: condition = o => (o as IComparable).CompareTo(xlFilter.Value) > 0; break; @@ -1346,7 +1351,6 @@ xlFilter.Condition = condition; filterList.Add(xlFilter); } - } else if (filterColumn.Top10 != null) { @@ -1388,7 +1392,7 @@ var condition = sort.Elements().FirstOrDefault(); if (condition != null) { - Int32 column = ws.Range(condition.Reference.Value).FirstCell().Address.ColumnNumber - autoFilter.Range.FirstCell().Address.ColumnNumber + 1 ; + Int32 column = ws.Range(condition.Reference.Value).FirstCell().Address.ColumnNumber - autoFilter.Range.FirstCell().Address.ColumnNumber + 1; autoFilter.SortColumn = column; autoFilter.Sorted = true; autoFilter.SortOrder = condition.Descending != null && condition.Descending.Value ? XLSortOrder.Descending : XLSortOrder.Ascending; @@ -1455,10 +1459,10 @@ var conditionalFormat = new XLConditionalFormat(ws.Range(sor.Value)); if (fr.FormatId != null) { - LoadFont(differentialFormats[(Int32) fr.FormatId.Value].Font, conditionalFormat.Style.Font); - LoadFill(differentialFormats[(Int32) fr.FormatId.Value].Fill, conditionalFormat.Style.Fill); - LoadBorder(differentialFormats[(Int32) fr.FormatId.Value].Border, conditionalFormat.Style.Border); - LoadNumberFormat(differentialFormats[(Int32) fr.FormatId.Value].NumberingFormat, conditionalFormat.Style.NumberFormat); + LoadFont(differentialFormats[(Int32)fr.FormatId.Value].Font, conditionalFormat.Style.Font); + LoadFill(differentialFormats[(Int32)fr.FormatId.Value].Fill, conditionalFormat.Style.Fill); + LoadBorder(differentialFormats[(Int32)fr.FormatId.Value].Border, conditionalFormat.Style.Border); + LoadNumberFormat(differentialFormats[(Int32)fr.FormatId.Value].NumberingFormat, conditionalFormat.Style.NumberFormat); } if (fr.Operator != null) conditionalFormat.Operator = fr.Operator.Value.ToClosedXml(); @@ -1492,7 +1496,7 @@ conditionalFormat.ShowIconOnly = !iconSet.ShowValue.Value; if (iconSet.Reverse != null) conditionalFormat.ReverseIconOrder = iconSet.Reverse.Value; - + if (iconSet.IconSetValue != null) conditionalFormat.IconSetStyle = iconSet.IconSetValue.Value.ToClosedXml(); else @@ -1504,7 +1508,7 @@ { foreach (var formula in fr.Elements()) { - if (formula.Text != null + if (formula.Text != null && (conditionalFormat.ConditionalFormatType == XLConditionalFormatType.CellIs || conditionalFormat.ConditionalFormatType == XLConditionalFormatType.Expression)) { @@ -1515,7 +1519,6 @@ ws.ConditionalFormats.Add(conditionalFormat); } } - } private static XLFormula GetFormula(String value) @@ -1523,7 +1526,7 @@ var formula = new XLFormula(); formula._value = value; formula.IsFormula = !(value[0] == '"' && value.EndsWith("\"")); - return formula; + return formula; } private void ExtractConditionalFormatValueObjects(XLConditionalFormat conditionalFormat, OpenXmlElement element) @@ -1534,6 +1537,8 @@ conditionalFormat.ContentTypes.Add(c.Type.Value.ToClosedXml()); if (c.Val != null) conditionalFormat.Values.Add(new XLFormula { Value = c.Val.Value }); + else + conditionalFormat.Values.Add(null); if (c.GreaterThanOrEqual != null) conditionalFormat.IconSetOperators.Add(c.GreaterThanOrEqual.Value ? XLCFIconSetOperator.EqualOrGreaterThan : XLCFIconSetOperator.GreaterThan); @@ -1632,7 +1637,7 @@ ws.PageSetup.DifferentOddEvenPagesOnHF = headerFooter.DifferentOddEven; // Footers - var xlFooter = (XLHeaderFooter) ws.PageSetup.Footer; + var xlFooter = (XLHeaderFooter)ws.PageSetup.Footer; var evenFooter = headerFooter.EvenFooter; if (evenFooter != null) xlFooter.SetInnerText(XLHFOccurrence.EvenPages, evenFooter.Text); @@ -1643,7 +1648,7 @@ if (firstFooter != null) xlFooter.SetInnerText(XLHFOccurrence.FirstPage, firstFooter.Text); // Headers - var xlHeader = (XLHeaderFooter) ws.PageSetup.Header; + var xlHeader = (XLHeaderFooter)ws.PageSetup.Header; var evenHeader = headerFooter.EvenHeader; if (evenHeader != null) xlHeader.SetInnerText(XLHFOccurrence.EvenPages, evenHeader.Text); @@ -1663,7 +1668,7 @@ if (pageSetup == null) return; if (pageSetup.PaperSize != null) - ws.PageSetup.PaperSize = (XLPaperSize) Int32.Parse(pageSetup.PaperSize.InnerText); + ws.PageSetup.PaperSize = (XLPaperSize)Int32.Parse(pageSetup.PaperSize.InnerText); if (pageSetup.Scale != null) ws.PageSetup.Scale = Int32.Parse(pageSetup.Scale.InnerText); else @@ -1685,8 +1690,8 @@ ws.PageSetup.ShowComments = pageSetup.CellComments.Value.ToClosedXml(); if (pageSetup.Errors != null) ws.PageSetup.PrintErrorValue = pageSetup.Errors.Value.ToClosedXml(); - if (pageSetup.HorizontalDpi != null) ws.PageSetup.HorizontalDpi = (Int32) pageSetup.HorizontalDpi.Value; - if (pageSetup.VerticalDpi != null) ws.PageSetup.VerticalDpi = (Int32) pageSetup.VerticalDpi.Value; + if (pageSetup.HorizontalDpi != null) ws.PageSetup.HorizontalDpi = (Int32)pageSetup.HorizontalDpi.Value; + if (pageSetup.VerticalDpi != null) ws.PageSetup.VerticalDpi = (Int32)pageSetup.VerticalDpi.Value; if (pageSetup.FirstPageNumber != null) ws.PageSetup.FirstPageNumber = UInt32.Parse(pageSetup.FirstPageNumber.InnerText); } @@ -1731,6 +1736,7 @@ if (sheetView == null) return; + if (sheetView.RightToLeft != null) ws.RightToLeft = sheetView.RightToLeft.Value; if (sheetView.ShowFormulas != null) ws.ShowFormulas = sheetView.ShowFormulas.Value; if (sheetView.ShowGridLines != null) ws.ShowGridLines = sheetView.ShowGridLines.Value; if (sheetView.ShowOutlineSymbols != null) @@ -1759,10 +1765,9 @@ (pane.State != PaneStateValues.FrozenSplit && pane.State != PaneStateValues.Frozen)) return; if (pane.HorizontalSplit != null) - ws.SheetView.SplitColumn = (Int32) pane.HorizontalSplit.Value; + ws.SheetView.SplitColumn = (Int32)pane.HorizontalSplit.Value; if (pane.VerticalSplit != null) - ws.SheetView.SplitRow = (Int32) pane.VerticalSplit.Value; - + ws.SheetView.SplitRow = (Int32)pane.VerticalSplit.Value; } private void SetProperties(SpreadsheetDocument dSpreadsheet) @@ -1799,10 +1804,10 @@ retVal = XLColor.FromColor(thisColor); } else if (color.Indexed != null && color.Indexed < 64) - retVal = XLColor.FromIndex((Int32) color.Indexed.Value); + retVal = XLColor.FromIndex((Int32)color.Indexed.Value); else if (color.Theme != null) { - retVal = color.Tint != null ? XLColor.FromTheme((XLThemeColor) color.Theme.Value, color.Tint.Value) : XLColor.FromTheme((XLThemeColor) color.Theme.Value); + retVal = color.Tint != null ? XLColor.FromTheme((XLThemeColor)color.Theme.Value, color.Tint.Value) : XLColor.FromTheme((XLThemeColor)color.Theme.Value); } } return retVal ?? XLColor.NoColor; @@ -1813,7 +1818,7 @@ { if (s == null) return; //No Stylesheet, no Styles - var cellFormat = (CellFormat) s.CellFormats.ElementAt(styleIndex); + var cellFormat = (CellFormat)s.CellFormats.ElementAt(styleIndex); if (cellFormat.ApplyProtection != null) { @@ -1847,7 +1852,6 @@ } } - var alignment = cellFormat.Alignment; if (alignment != null) { @@ -1860,21 +1864,20 @@ if (alignment.ReadingOrder != null) { xlStylized.InnerStyle.Alignment.ReadingOrder = - (XLAlignmentReadingOrderValues) Int32.Parse(alignment.ReadingOrder.ToString()); + (XLAlignmentReadingOrderValues)Int32.Parse(alignment.ReadingOrder.ToString()); } if (alignment.RelativeIndent != null) xlStylized.InnerStyle.Alignment.RelativeIndent = alignment.RelativeIndent; if (alignment.ShrinkToFit != null) xlStylized.InnerStyle.Alignment.ShrinkToFit = alignment.ShrinkToFit; if (alignment.TextRotation != null) - xlStylized.InnerStyle.Alignment.TextRotation = (Int32) alignment.TextRotation.Value; + xlStylized.InnerStyle.Alignment.TextRotation = (Int32)alignment.TextRotation.Value; if (alignment.Vertical != null) xlStylized.InnerStyle.Alignment.Vertical = alignment.Vertical.Value.ToClosedXml(); if (alignment.WrapText != null) xlStylized.InnerStyle.Alignment.WrapText = alignment.WrapText; } - if (UInt32HasValue(cellFormat.BorderId)) { uint borderId = cellFormat.BorderId.Value; @@ -1983,10 +1986,8 @@ } } - - if (!UInt32HasValue(cellFormat.NumberFormatId)) return; - + var numberFormatId = cellFormat.NumberFormatId; string formatCode = String.Empty; @@ -1995,8 +1996,8 @@ var numberingFormat = numberingFormats.FirstOrDefault( nf => - ((NumberingFormat) nf).NumberFormatId != null && - ((NumberingFormat) nf).NumberFormatId.Value == numberFormatId) as NumberingFormat; + ((NumberingFormat)nf).NumberFormatId != null && + ((NumberingFormat)nf).NumberFormatId.Value == numberFormatId) as NumberingFormat; if (numberingFormat != null && numberingFormat.FormatCode != null) formatCode = numberingFormat.FormatCode.Value; @@ -2004,7 +2005,7 @@ if (formatCode.Length > 0) xlStylized.InnerStyle.NumberFormat.Format = formatCode; else - xlStylized.InnerStyle.NumberFormat.NumberFormatId = (Int32) numberFormatId.Value; + xlStylized.InnerStyle.NumberFormat.NumberFormatId = (Int32)numberFormatId.Value; } private static Boolean UInt32HasValue(UInt32Value value) diff --git a/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs b/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs index 0f1c4ca..9bcb8a2 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs @@ -16,6 +16,8 @@ [DebuggerBrowsable(DebuggerBrowsableState.Never)] private readonly Dictionary _sharedStyles; [DebuggerBrowsable(DebuggerBrowsableState.Never)] + private readonly Dictionary _sharedNumberFormats; + [DebuggerBrowsable(DebuggerBrowsableState.Never)] private readonly Dictionary _sharedFonts; [DebuggerBrowsable(DebuggerBrowsableState.Never)] private readonly HashSet _tableNames; @@ -27,6 +29,7 @@ { _relIdGenerator = new RelIdGenerator(); _sharedStyles = new Dictionary(); + _sharedNumberFormats = new Dictionary(); _sharedFonts = new Dictionary(); _tableNames = new HashSet(); _tableId = 0; @@ -43,6 +46,11 @@ [DebuggerStepThrough] get { return _sharedStyles; } } + public Dictionary SharedNumberFormats + { + [DebuggerStepThrough] + get { return _sharedNumberFormats; } + } public Dictionary SharedFonts { [DebuggerStepThrough] @@ -99,7 +107,7 @@ { _relIds.Add(relType, new List()); } - _relIds[relType].AddRange(values); + _relIds[relType].AddRange(values.Where(v => !_relIds[relType].Contains(v))); } public void Reset(RelType relType) { @@ -133,7 +141,7 @@ internal struct NumberFormatInfo { public Int32 NumberFormatId; - public IXLNumberFormat NumberFormat; + public IXLNumberFormatBase NumberFormat; } #endregion #region Nested type: StyleInfo @@ -147,9 +155,5 @@ public IXLStyle Style; } #endregion - - - - } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index b612e7e..bc6df99 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -10,6 +10,7 @@ using DocumentFormat.OpenXml.Packaging; using DocumentFormat.OpenXml.Spreadsheet; using DocumentFormat.OpenXml.VariantTypes; +using DocumentFormat.OpenXml.Validation; using DocumentFormat.OpenXml.Vml.Office; using DocumentFormat.OpenXml.Vml.Spreadsheet; using Vml = DocumentFormat.OpenXml.Vml; @@ -36,13 +37,13 @@ using System.Xml; using System.Xml.Linq; using System.Text; +using ClosedXML.Utils; using Anchor = DocumentFormat.OpenXml.Vml.Spreadsheet.Anchor; using Field = DocumentFormat.OpenXml.Spreadsheet.Field; using Run = DocumentFormat.OpenXml.Spreadsheet.Run; using RunProperties = DocumentFormat.OpenXml.Spreadsheet.RunProperties; using VerticalTextAlignment = DocumentFormat.OpenXml.Spreadsheet.VerticalTextAlignment; - namespace ClosedXML.Excel { public partial class XLWorkbook @@ -79,7 +80,19 @@ } } - private void CreatePackage(String filePath, SpreadsheetDocumentType spreadsheetDocumentType) + private bool Validate(SpreadsheetDocument package) + { + var validator = new OpenXmlValidator(); + var errors = validator.Validate(package); + if (errors.Any()) + { + var message = string.Join("\r\n", errors.Select(e => string.Format("{0} in {1}", e.Description, e.Path.XPath)).ToArray()); + throw new ApplicationException(message); + } + return true; + } + + private void CreatePackage(String filePath, SpreadsheetDocumentType spreadsheetDocumentType, bool validate) { PathHelper.CreateDirectory(Path.GetDirectoryName(filePath)); var package = File.Exists(filePath) @@ -89,11 +102,11 @@ using (package) { CreateParts(package); - //package.Close(); + if (validate) Validate(package); } } - private void CreatePackage(Stream stream, bool newStream, SpreadsheetDocumentType spreadsheetDocumentType) + private void CreatePackage(Stream stream, bool newStream, SpreadsheetDocumentType spreadsheetDocumentType, bool validate) { var package = newStream ? SpreadsheetDocument.Create(stream, spreadsheetDocumentType) @@ -102,7 +115,7 @@ using (package) { CreateParts(package); - //package.Close(); + if (validate) Validate(package); } } @@ -287,6 +300,10 @@ } } + // Remove empty pivot cache part + if (workbookPart.Workbook.PivotCaches != null && !workbookPart.Workbook.PivotCaches.Any()) + workbookPart.Workbook.RemoveChild(workbookPart.Workbook.PivotCaches); + GenerateCalculationChainPartContent(workbookPart, context); if (workbookPart.ThemePart == null) @@ -561,16 +578,27 @@ sheet.Name = wks.Name; } - foreach (var xlSheet in - WorksheetsInternal.Cast().Where(s => s.SheetId == 0).OrderBy(w => w.Position)) + foreach (var xlSheet in WorksheetsInternal.Cast().OrderBy(w => w.Position)) { - var rId = context.RelIdGenerator.GetNext(RelType.Workbook); + string rId; + if (xlSheet.SheetId == 0) + { + rId = context.RelIdGenerator.GetNext(RelType.Workbook); while (WorksheetsInternal.Cast().Any(w => w.SheetId == Int32.Parse(rId.Substring(3)))) rId = context.RelIdGenerator.GetNext(RelType.Workbook); xlSheet.SheetId = Int32.Parse(rId.Substring(3)); xlSheet.RelId = rId; + } + else + { + rId = String.Format("rId{0}", xlSheet.SheetId); + context.RelIdGenerator.AddValues(new List { rId }, RelType.Workbook); + } + + if (!workbook.Sheets.Cast().Any(s => s.Id == rId)) + { var newSheet = new Sheet { Name = xlSheet.Name, @@ -580,6 +608,7 @@ workbook.Sheets.AppendChild(newSheet); } + } var sheetElements = from sheet in workbook.Sheets.Elements() join worksheet in ((IEnumerable)WorksheetsInternal) on sheet.Id.Value @@ -700,6 +729,10 @@ LocalSheetId = sheetId, Text = nr.ToString() }; + + if (!nr.Visible) + definedName.Hidden = BooleanValue.FromBoolean(true); + if (!XLHelper.IsNullOrWhiteSpace(nr.Comment)) definedName.Comment = nr.Comment; definedNames.AppendChild(definedName); @@ -751,6 +784,10 @@ Name = nr.Name, Text = nr.ToString() }; + + if (!nr.Visible) + definedName.Hidden = BooleanValue.FromBoolean(true); + if (!XLHelper.IsNullOrWhiteSpace(nr.Comment)) definedName.Comment = nr.Comment; definedNames.AppendChild(definedName); @@ -867,7 +904,7 @@ { var s = c.Value.ToString(); var sharedStringItem = new SharedStringItem(); - var text = new Text {Text = s}; + var text = new Text {Text = XmlEncoder.EncodeString(s)}; if (!s.Trim().Equals(s)) text.Space = SpaceProcessingModeValues.Preserve; sharedStringItem.Append(text); @@ -1639,7 +1676,7 @@ { var vTDouble1 = new VTDouble { - Text = p.GetValue().ToString(CultureInfo.InvariantCulture) + Text = p.GetValue().ToInvariantString() }; customDocumentProperty.AppendChild(vTDouble1); } @@ -1778,23 +1815,32 @@ XLWorksheet xlWorksheet, SaveContext context) { + PivotCaches pivotCaches; + uint cacheId = 0; + if (workbookPart.Workbook.PivotCaches == null) + pivotCaches = workbookPart.Workbook.AppendChild(new PivotCaches()); + else + { + pivotCaches = workbookPart.Workbook.PivotCaches; + if (pivotCaches.Any()) + cacheId = pivotCaches.Cast().Max(pc => pc.CacheId.Value) + 1; + } + foreach (var pt in xlWorksheet.PivotTables) { + // TODO: Avoid duplicate pivot caches of same source range var ptCdp = context.RelIdGenerator.GetNext(RelType.Workbook); var pivotTableCacheDefinitionPart = workbookPart.AddNewPart(ptCdp); GeneratePivotTableCacheDefinitionPartContent(pivotTableCacheDefinitionPart, pt); - var pivotCaches = new PivotCaches(); - var pivotCache = new PivotCache {CacheId = 0U, Id = ptCdp}; + var pivotCache = new PivotCache { CacheId = cacheId++, Id = ptCdp }; pivotCaches.AppendChild(pivotCache); - workbookPart.Workbook.AppendChild(pivotCaches); - var pivotTablePart = worksheetPart.AddNewPart(context.RelIdGenerator.GetNext(RelType.Workbook)); - GeneratePivotTablePartContent(pivotTablePart, pt); + GeneratePivotTablePartContent(pivotTablePart, pt, pivotCache.CacheId, context); pivotTablePart.AddPart(pivotTableCacheDefinitionPart, context.RelIdGenerator.GetNext(RelType.Workbook)); } @@ -1888,12 +1934,12 @@ } // Generates content of pivotTablePart - private static void GeneratePivotTablePartContent(PivotTablePart pivotTablePart1, IXLPivotTable pt) + private static void GeneratePivotTablePartContent(PivotTablePart pivotTablePart, IXLPivotTable pt, uint cacheId, SaveContext context) { var pivotTableDefinition = new PivotTableDefinition { Name = pt.Name, - CacheId = 0U, + CacheId = cacheId, DataCaption = "Values", MergeItem = GetBooleanValue(pt.MergeAndCenterWithLabels, true), Indent = Convert.ToUInt32(pt.RowLabelIndent), @@ -1956,11 +2002,11 @@ var rowItems = new RowItems(); var columnItems = new ColumnItems(); var pageFields = new PageFields {Count = (uint)pt.ReportFilters.Count()}; - var pivotFields = new PivotFields {Count = Convert.ToUInt32(pt.SourceRange.ColumnCount())}; + foreach (var xlpf in pt.Fields.OrderBy(f => pt.RowLabels.Any(p => p.SourceName == f.SourceName) ? pt.RowLabels.IndexOf(f) : Int32.MaxValue )) { - if (pt.RowLabels.FirstOrDefault(p => p.SourceName == xlpf.SourceName) != null) + if (pt.RowLabels.Any(p => p.SourceName == xlpf.SourceName)) { var f = new Field {Index = pt.Fields.IndexOf(xlpf)}; rowFields.AppendChild(f); @@ -1976,7 +2022,7 @@ rowItemTotal.AppendChild(new MemberPropertyIndex()); rowItems.AppendChild(rowItemTotal); } - else if (pt.ColumnLabels.FirstOrDefault(p => p.SourceName == xlpf.SourceName) != null) + else if (pt.ColumnLabels.Any(p => p.SourceName == xlpf.SourceName)) { var f = new Field {Index = pt.Fields.IndexOf(xlpf)}; columnFields.AppendChild(f); @@ -1994,26 +2040,38 @@ } } + if (pt.Values.Count() > 1) + { + // -2 is the sentinal value for "Values" + if (pt.ColumnLabels.Any(cl => cl.SourceName == XLConstants.PivotTableValuesSentinalLabel)) + columnFields.AppendChild(new Field { Index = -2 }); + else if (pt.RowLabels.Any(rl => rl.SourceName == XLConstants.PivotTableValuesSentinalLabel)) + { + pivotTableDefinition.DataOnRows = true; + rowFields.AppendChild(new Field { Index = -2 }); + } + } + foreach (var xlpf in pt.Fields) { var pf = new PivotField {ShowAll = false, Name = xlpf.CustomName}; - if (pt.RowLabels.FirstOrDefault(p => p.SourceName == xlpf.SourceName) != null) + if (pt.RowLabels.Any(p => p.SourceName == xlpf.SourceName)) { pf.Axis = PivotTableAxisValues.AxisRow; } - else if (pt.ColumnLabels.FirstOrDefault(p => p.SourceName == xlpf.SourceName) != null) + else if (pt.ColumnLabels.Any(p => p.SourceName == xlpf.SourceName)) { pf.Axis = PivotTableAxisValues.AxisColumn; } - else if (pt.ReportFilters.FirstOrDefault(p => p.SourceName == xlpf.SourceName) != null) + else if (pt.ReportFilters.Any(p => p.SourceName == xlpf.SourceName)) { location.ColumnsPerPage = 1; location.RowPageCount = 1; pf.Axis = PivotTableAxisValues.AxisPage; pageFields.AppendChild(new PageField {Hierarchy = -1, Field = pt.Fields.IndexOf(xlpf)}); } - else if (pt.Values.FirstOrDefault(p => p.CustomName == xlpf.SourceName) != null) + else if (pt.Values.Any(p => p.SourceName == xlpf.SourceName)) { pf.DataField = true; } @@ -2088,6 +2146,7 @@ fieldItems.AppendChild(new Item {ItemType = ItemValues.Default}); } + fieldItems.Count = Convert.ToUInt32(fieldItems.Count()); pf.AppendChild(fieldItems); pivotFields.AppendChild(pf); } @@ -2097,27 +2156,43 @@ if (pt.RowLabels.Any()) { + rowFields.Count = Convert.ToUInt32(rowFields.Count()); pivotTableDefinition.AppendChild(rowFields); } else { rowItems.AppendChild(new RowItem()); } + + rowItems.Count = Convert.ToUInt32(rowItems.Count()); pivotTableDefinition.AppendChild(rowItems); - if (!pt.ColumnLabels.Any()) + if (!pt.ColumnLabels.Any(cl => cl.CustomName != XLConstants.PivotTableValuesSentinalLabel)) { - columnItems.AppendChild(new RowItem()); - pivotTableDefinition.AppendChild(columnItems); + for (int i = 0; i < pt.Values.Count(); i++) + { + var rowItem = new RowItem(); + rowItem.Index = Convert.ToUInt32(i); + rowItem.AppendChild(new MemberPropertyIndex() { Val = i }); + columnItems.AppendChild(rowItem); } - else + } + + if (columnFields.Any()) { + columnFields.Count = Convert.ToUInt32(columnFields.Count()); pivotTableDefinition.AppendChild(columnFields); + } + + if (columnItems.Any()) + { + columnItems.Count = Convert.ToUInt32(columnItems.Count()); pivotTableDefinition.AppendChild(columnItems); } if (pt.ReportFilters.Any()) { + pageFields.Count = Convert.ToUInt32(pageFields.Count()); pivotTableDefinition.AppendChild(pageFields); } @@ -2129,21 +2204,36 @@ pt.SourceRange.Columns().FirstOrDefault(c => c.Cell(1).Value.ToString() == value.SourceName); if (sourceColumn == null) continue; + UInt32 numberFormatId = 0; + if (value.NumberFormat.NumberFormatId != -1 || context.SharedNumberFormats.ContainsKey(value.NumberFormat.NumberFormatId)) + numberFormatId = (UInt32)value.NumberFormat.NumberFormatId; + else if (context.SharedNumberFormats.Any(snf => snf.Value.NumberFormat.Format == value.NumberFormat.Format)) + numberFormatId = (UInt32)context.SharedNumberFormats.First(snf => snf.Value.NumberFormat.Format == value.NumberFormat.Format).Key; + var df = new DataField { - Name = value.SourceName, + Name = value.CustomName, Field = (UInt32)sourceColumn.ColumnNumber() - 1, Subtotal = value.SummaryFormula.ToOpenXml(), ShowDataAs = value.Calculation.ToOpenXml(), - NumberFormatId = (UInt32)value.NumberFormat.NumberFormatId + NumberFormatId = numberFormatId }; if (!String.IsNullOrEmpty(value.BaseField)) { - var baseField = - pt.SourceRange.Columns().FirstOrDefault(c => c.Cell(1).Value.ToString() == value.BaseField); + var baseField = pt.SourceRange.Columns().FirstOrDefault(c => c.Cell(1).Value.ToString() == value.BaseField); if (baseField != null) + { df.BaseField = baseField.ColumnNumber() - 1; + + var items = baseField.CellsUsed() + .Select(c => c.Value) + .Skip(1) // Skip header column + .Distinct().ToList(); + + if (items.Any(i => i.Equals(value.BaseItem))) + df.BaseItem = Convert.ToUInt32(items.IndexOf(value.BaseItem)); + } } else { @@ -2154,12 +2244,13 @@ df.BaseItem = 1048828U; else if (value.CalculationItem == XLPivotCalculationItem.Next) df.BaseItem = 1048829U; - else + else if (df.BaseItem == null || !df.BaseItem.HasValue) df.BaseItem = 0U; - dataFields.AppendChild(df); } + + dataFields.Count = Convert.ToUInt32(dataFields.Count()); pivotTableDefinition.AppendChild(dataFields); pivotTableDefinition.AppendChild(new PivotTableStyle @@ -2191,7 +2282,7 @@ #endregion - pivotTablePart1.PivotTableDefinition = pivotTableDefinition; + pivotTablePart.PivotTableDefinition = pivotTableDefinition; } @@ -2380,10 +2471,10 @@ var dm = ds.Margins; if (!dm.Automatic) retVal.Inset = String.Format("{0}in,{1}in,{2}in,{3}in", - dm.Left.ToString(CultureInfo.InvariantCulture), - dm.Top.ToString(CultureInfo.InvariantCulture), - dm.Right.ToString(CultureInfo.InvariantCulture), - dm.Bottom.ToString(CultureInfo.InvariantCulture)); + dm.Left.ToInvariantString(), + dm.Top.ToInvariantString(), + dm.Right.ToInvariantString(), + dm.Bottom.ToInvariantString()); return retVal; } @@ -2439,10 +2530,10 @@ sb.Append(";"); sb.Append("width:"); - sb.Append(Math.Round(c.Style.Size.Width * 7.5, 2).ToString(CultureInfo.InvariantCulture)); + sb.Append(Math.Round(c.Style.Size.Width * 7.5, 2).ToInvariantString()); sb.Append("pt;"); sb.Append("height:"); - sb.Append(Math.Round(c.Style.Size.Height, 2).ToString(CultureInfo.InvariantCulture)); + sb.Append(Math.Round(c.Style.Size.Height, 2).ToInvariantString()); sb.Append("pt;"); sb.Append("z-index:"); @@ -2467,7 +2558,7 @@ var sharedBorders = new Dictionary {{defaultStyle.Border, new BorderInfo {BorderId = 0, Border = defaultStyle.Border as XLBorder}}}; - var sharedNumberFormats = new Dictionary + var sharedNumberFormats = new Dictionary { { defaultStyle.NumberFormat, @@ -2518,6 +2609,7 @@ UInt32 borderCount = 1; var numberFormatCount = 1; var xlStyles = new HashSet(); + var pivotTableNumberFormats = new HashSet(); foreach (var worksheet in WorksheetsInternal) { @@ -2536,6 +2628,24 @@ s => !xlStyles.Contains(s)) ) xlStyles.Add(s); + + foreach (var ptnf in worksheet.PivotTables.SelectMany(pt => pt.Values.Select(ptv => ptv.NumberFormat)).Distinct().Where(nf => !pivotTableNumberFormats.Contains(nf))) + pivotTableNumberFormats.Add(ptnf); + } + + foreach (var numberFormat in pivotTableNumberFormats) + { + if (numberFormat.NumberFormatId != -1 + || sharedNumberFormats.ContainsKey(numberFormat)) + continue; + + sharedNumberFormats.Add(numberFormat, + new NumberFormatInfo + { + NumberFormatId = numberFormatCount + 164, + NumberFormat = numberFormat + }); + numberFormatCount++; } foreach (var xlStyle in xlStyles.Select(GetStyleById)) @@ -2565,6 +2675,11 @@ } var allSharedNumberFormats = ResolveNumberFormats(workbookStylesPart, sharedNumberFormats, defaultFormatId); + foreach (var nf in allSharedNumberFormats) + { + context.SharedNumberFormats.Add(nf.Value.NumberFormatId, nf.Value); + } + ResolveFonts(workbookStylesPart, context); var allSharedFills = ResolveFills(workbookStylesPart, sharedFills); var allSharedBorders = ResolveBorders(workbookStylesPart, sharedBorders); @@ -3262,9 +3377,9 @@ return nf.Equals(xlFont); } - private static Dictionary ResolveNumberFormats( + private static Dictionary ResolveNumberFormats( WorkbookStylesPart workbookStylesPart, - Dictionary sharedNumberFormats, + Dictionary sharedNumberFormats, UInt32 defaultFormatId) { if (workbookStylesPart.Stylesheet.NumberingFormats == null) @@ -3277,7 +3392,7 @@ }); } - var allSharedNumberFormats = new Dictionary(); + var allSharedNumberFormats = new Dictionary(); foreach (var numberFormatInfo in sharedNumberFormats.Values.Where(nf => nf.NumberFormatId != defaultFormatId)) { var numberingFormatId = 164; @@ -3313,7 +3428,7 @@ return allSharedNumberFormats; } - private static bool NumberFormatsAreEqual(NumberingFormat nf, IXLNumberFormat xlNumberFormat) + private static bool NumberFormatsAreEqual(NumberingFormat nf, IXLNumberFormatBase xlNumberFormat) { var newXLNumberFormat = new XLNumberFormat(); @@ -3420,6 +3535,10 @@ else sheetView.TabSelected = null; + if (xlWorksheet.RightToLeft) + sheetView.RightToLeft = true; + else + sheetView.RightToLeft = null; if (xlWorksheet.ShowFormulas) sheetView.ShowFormulas = true; @@ -3713,7 +3832,7 @@ } var distinctRows = xlWorksheet.Internals.CellsCollection.RowsCollection.Keys.Union(xlWorksheet.Internals.RowsCollection.Keys); - var noRows = (sheetData.Elements().FirstOrDefault() == null); + var noRows = !sheetData.Elements().Any(); foreach (var distinctRow in distinctRows.OrderBy(r => r)) { Row row; @@ -3908,7 +4027,7 @@ var timeSpan = opCell.GetTimeSpan(); var cellValue = new CellValue(); cellValue.Text = - XLCell.BaseDate.Add(timeSpan).ToOADate().ToString(CultureInfo.InvariantCulture); + XLCell.BaseDate.Add(timeSpan).ToOADate().ToInvariantString(); cell.CellValue = cellValue; } else if (dataType == XLCellValues.DateTime || dataType == XLCellValues.Number) @@ -3916,7 +4035,7 @@ if (!XLHelper.IsNullOrWhiteSpace(opCell.InnerText)) { var cellValue = new CellValue(); - cellValue.Text = Double.Parse(opCell.InnerText, XLHelper.NumberStyle, CultureInfo.InvariantCulture).ToString(CultureInfo.InvariantCulture); + cellValue.Text = Double.Parse(opCell.InnerText, XLHelper.NumberStyle, XLHelper.ParseCulture).ToInvariantString(); cell.CellValue = cellValue; } } @@ -4636,4 +4755,4 @@ #endregion } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/Excel/XLWorksheet.cs index b8018fa..c622c30 100644 --- a/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/Excel/XLWorksheet.cs @@ -17,8 +17,8 @@ #region Events - public XLReentrantEnumerableSet RangeShiftedRows; - public XLReentrantEnumerableSet RangeShiftedColumns; + public XLReentrantEnumerableSet RangeShiftedRows; + public XLReentrantEnumerableSet RangeShiftedColumns; #endregion @@ -45,8 +45,8 @@ { EventTrackingEnabled = workbook.EventTracking == XLEventTracking.Enabled; - RangeShiftedRows = new XLReentrantEnumerableSet(); - RangeShiftedColumns = new XLReentrantEnumerableSet(); + RangeShiftedRows = new XLReentrantEnumerableSet(); + RangeShiftedColumns = new XLReentrantEnumerableSet(); RangeAddress.Worksheet = this; RangeAddress.FirstAddress.Worksheet = this; @@ -70,9 +70,9 @@ _columnWidth = workbook.ColumnWidth; _rowHeight = workbook.RowHeight; RowHeightChanged = Math.Abs(workbook.RowHeight - XLWorkbook.DefaultRowHeight) > XLHelper.Epsilon; - Name = sheetName; - SubscribeToShiftedRows((range, rowsShifted) => this.WorksheetRangeShiftedRows(range, rowsShifted)); - SubscribeToShiftedColumns((range, columnsShifted) => this.WorksheetRangeShiftedColumns(range, columnsShifted)); + Name = sheetName; + SubscribeToShiftedRows((range, rowsShifted) => this.WorksheetRangeShiftedRows(range, rowsShifted)); + SubscribeToShiftedColumns((range, columnsShifted) => this.WorksheetRangeShiftedColumns(range, columnsShifted)); Charts = new XLCharts(); ShowFormulas = workbook.ShowFormulas; ShowGridLines = workbook.ShowGridLines; @@ -1111,7 +1111,7 @@ Int32 thisStyleId = GetStyleId(); if (!Internals.ColumnsCollection.ContainsKey(column)) { - // This is a new row so we're going to reference all + // This is a new row so we're going to reference all // cells in this row to preserve their formatting Internals.RowsCollection.Keys.ForEach(r => Cell(r, column)); Internals.ColumnsCollection.Add(column, @@ -1348,7 +1348,7 @@ { if (pingCells) { - // This is a new row so we're going to reference all + // This is a new row so we're going to reference all // cells in columns of this row to preserve their formatting var usedColumns = from c in Internals.ColumnsCollection @@ -1379,9 +1379,17 @@ return (XLPivotTable)PivotTables.PivotTable(name); } - public new XLCells Cells() + public new IXLCells Cells() { - return CellsUsed(true); + return Cells(true, true); + } + + public new IXLCells Cells(Boolean usedCellsOnly) + { + if (usedCellsOnly) + return Cells(true, true); + else + return Range(FirstCellUsed(), LastCellUsed()).Cells(false, true); } public new XLCell Cell(String cellAddressInRange) @@ -1396,11 +1404,11 @@ String.Compare(n.Name, cellAddressInRange, true) == 0 && n.Ranges.Count == 1); if (namedRanges == null || !namedRanges.Ranges.Any()) return null; - + return (XLCell)namedRanges.Ranges.First().FirstCell(); } - public XLCell CellFast(String cellAddressInRange) + internal XLCell CellFast(String cellAddressInRange) { return Cell(XLAddress.Create(this, cellAddressInRange)); } @@ -1438,7 +1446,7 @@ { EventTrackingEnabled = _eventTracking; } - + public IXLRanges SelectedRanges { get; internal set; } public IXLCell ActiveCell { get; set; } @@ -1456,4 +1464,4 @@ public String Author { get; set; } } -} \ No newline at end of file +} diff --git a/ClosedXML/Extensions.cs b/ClosedXML/Extensions.cs index 1a33550..a37daf3 100644 --- a/ClosedXML/Extensions.cs +++ b/ClosedXML/Extensions.cs @@ -75,10 +75,10 @@ HashSet distinctItems = new HashSet(); foreach (var item in source) { - if (distinctItems.Contains(item)) - return true; - else - distinctItems.Add(item); + if (!distinctItems.Add(item)) + { + return true; + } } return false; } diff --git a/ClosedXML/Utils/XmlEncoder.cs b/ClosedXML/Utils/XmlEncoder.cs new file mode 100644 index 0000000..5c42ed9 --- /dev/null +++ b/ClosedXML/Utils/XmlEncoder.cs @@ -0,0 +1,47 @@ +using System.Text; +using System.Xml; + +namespace ClosedXML.Utils +{ + public static class XmlEncoder + { + /// + /// Checks if a character is not allowed to the XML Spec http://www.w3.org/TR/REC-xml/#charsets + /// + /// Input Character + /// Returns false if the character is invalid according to the XML specification, and will not be + /// escaped by an XmlWriter. + public static bool IsXmlChar(char ch) + { + return (((ch >= 0x0020 && ch <= 0xD7FF) || + (ch >= 0xE000 && ch <= 0xFFFD) || + ch == 0x0009 || ch == 0x000A || + ch == 0x000D)); + } + + public static string EncodeString(string encodeStr) + { + if (encodeStr == null) return null; + + var newString = new StringBuilder(); + + foreach (var ch in encodeStr) + { + if (IsXmlChar(ch)) //this method is new in .NET 4 + { + newString.Append(ch); + } + else + { + newString.Append(XmlConvert.EncodeName(ch.ToString())); + } + } + return newString.ToString(); + } + + public static string DecodeString(string decodeStr) + { + return XmlConvert.DecodeName(decodeStr); + } + } +} diff --git a/ClosedXML/XLHelper.cs b/ClosedXML/XLHelper.cs index b2bafb8..8729fef 100644 --- a/ClosedXML/XLHelper.cs +++ b/ClosedXML/XLHelper.cs @@ -22,10 +22,9 @@ public const Double Epsilon = 1e-10; private const Int32 TwoT26 = 26*26; - internal static readonly NumberFormatInfo NumberFormatForParse = CultureInfo.InvariantCulture.NumberFormat; internal static readonly Graphics Graphic = Graphics.FromImage(new Bitmap(200, 200)); internal static readonly Double DpiX = Graphic.DpiX; - internal static readonly NumberStyles NumberStyle = NumberStyles.AllowDecimalPoint | NumberStyles.AllowLeadingSign | NumberStyles.AllowLeadingWhite | NumberStyles.AllowTrailingWhite; + internal static readonly NumberStyles NumberStyle = NumberStyles.AllowDecimalPoint | NumberStyles.AllowLeadingSign | NumberStyles.AllowLeadingWhite | NumberStyles.AllowTrailingWhite | NumberStyles.AllowExponent; internal static readonly CultureInfo ParseCulture = CultureInfo.InvariantCulture; internal static readonly Regex A1SimpleRegex = new Regex( @@ -70,7 +69,7 @@ //Extra check because we allow users to pass row col positions in as strings if (columnLetter[0] <= '9') { - retVal = Int32.Parse(columnLetter, NumberFormatForParse); + retVal = Int32.Parse(columnLetter, XLHelper.NumberStyle, XLHelper.ParseCulture); return retVal; } @@ -166,7 +165,7 @@ public static Boolean IsValidRangeAddress(IXLRangeAddress rangeAddress) { - return !rangeAddress.IsInvalid + return !rangeAddress.IsInvalid && rangeAddress.FirstAddress.RowNumber >= 1 && rangeAddress.LastAddress.RowNumber <= MaxRowNumber && rangeAddress.FirstAddress.ColumnNumber >= 1 && rangeAddress.LastAddress.ColumnNumber <= MaxColumnNumber && rangeAddress.FirstAddress.RowNumber <= rangeAddress.LastAddress.RowNumber @@ -301,4 +300,4 @@ return (DateTime)Convert.ChangeType(v, typeof(DateTime)); } } -} \ No newline at end of file +} diff --git a/ClosedXML_Examples/ClosedXML_Examples.csproj b/ClosedXML_Examples/ClosedXML_Examples.csproj index 5012f6d..9bdf8de 100644 --- a/ClosedXML_Examples/ClosedXML_Examples.csproj +++ b/ClosedXML_Examples/ClosedXML_Examples.csproj @@ -71,8 +71,10 @@ + + diff --git a/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs b/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs index efd53e8..5adc52f 100644 --- a/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs +++ b/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs @@ -18,7 +18,7 @@ ws.RangeUsed().AddConditionalFormat().ColorScale() .LowestValue(XLColor.Red) - .Midpoint(XLCFContentType.Percent, "50", XLColor.Yellow) + .Midpoint(XLCFContentType.Percent, "50", XLColor.Yellow) .HighestValue(XLColor.Green); workbook.SaveAs(filePath); @@ -46,6 +46,26 @@ } } + public class CFColorScaleMinimumMaximum : IXLExample + { + public void Create(String filePath) + { + var workbook = new XLWorkbook(); + var ws = workbook.AddWorksheet("Sheet1"); + + ws.FirstCell().SetValue(1) + .CellBelow().SetValue(1) + .CellBelow().SetValue(2) + .CellBelow().SetValue(3); + + ws.RangeUsed().AddConditionalFormat().ColorScale() + .LowestValue(XLColor.FromHtml("#FFFF7128")) + .HighestValue(XLColor.FromHtml("#FFFFEF9C")); + + workbook.SaveAs(filePath); + } + } + public class CFStartsWith : IXLExample { public void Create(String filePath) @@ -567,7 +587,7 @@ var range = ws.RangeUsed(); range.AddConditionalFormat().WhenEquals("1").Font.SetBold(); range.InsertRowsAbove(1); - + workbook.SaveAs(filePath); } @@ -589,7 +609,7 @@ ws.RangeUsed().AddConditionalFormat().DataBar(XLColor.Red) .LowestValue() .HighestValue(); - + workbook.SaveAs(filePath); } } diff --git a/ClosedXML_Examples/Creating/CreateFiles.cs b/ClosedXML_Examples/Creating/CreateFiles.cs index 2b94f60..694109c 100644 --- a/ClosedXML_Examples/Creating/CreateFiles.cs +++ b/ClosedXML_Examples/Creating/CreateFiles.cs @@ -52,6 +52,7 @@ new MergeMoves().Create(Path.Combine(path, "MergedMoves.xlsx")); new WorkbookProperties().Create(Path.Combine(path, "WorkbookProperties.xlsx")); new AdjustToContents().Create(Path.Combine(path, "AdjustToContents.xlsx")); + new AdjustToContentsWithAutoFilter().Create(Path.Combine(path, "AdjustToContentsWithAutoFilter.xlsx")); new HideUnhide().Create(Path.Combine(path, "HideUnhide.xlsx")); new Outline().Create(Path.Combine(path, "Outline.xlsx")); new Formulas().Create(Path.Combine(path, "Formulas.xlsx")); @@ -67,6 +68,7 @@ new FreezePanes().Create(Path.Combine(path, "FreezePanes.xlsx")); new UsingTables().Create(Path.Combine(path, "UsingTables.xlsx")); new AddingRowToTables().Create(Path.Combine(path, "AddingRowToTables.xlsx")); + new RightToLeft().Create(Path.Combine(path, "RightToLeft.xlsx")); new ShowCase().Create(Path.Combine(path, "ShowCase.xlsx")); new CopyingWorksheets().Create(Path.Combine(path, "CopyingWorksheets.xlsx")); new InsertingTables().Create(Path.Combine(path, "InsertingTables.xlsx")); @@ -87,6 +89,7 @@ new UsingPhonetics().Create(Path.Combine(path, "UsingPhonetics.xlsx")); new WalkingRanges().Create(Path.Combine(path, "CellMoves.xlsx")); new AddingComments().Create(Path.Combine(path, "AddingComments.xlsx")); + new PivotTables().Create(Path.Combine(path, "PivotTables.xlsx")); } } } diff --git a/ClosedXML_Examples/Misc/AdjustToContentsWithAutoFilter.cs b/ClosedXML_Examples/Misc/AdjustToContentsWithAutoFilter.cs new file mode 100644 index 0000000..84c39ad --- /dev/null +++ b/ClosedXML_Examples/Misc/AdjustToContentsWithAutoFilter.cs @@ -0,0 +1,25 @@ +using ClosedXML.Excel; +using System; + +namespace ClosedXML_Examples.Misc +{ + public class AdjustToContentsWithAutoFilter : IXLExample + { + // Public + public void Create(String filePath) + { + var wb = new XLWorkbook(); + var ws = wb.Worksheets.Add("AutoFilter"); + ws.Cell("A1").Value = "AVeryLongColumnHeader"; + ws.Cell("A2").Value = "John"; + ws.Cell("A3").Value = "Hank"; + ws.Cell("A4").Value = "Dagny"; + + ws.RangeUsed().SetAutoFilter(); + + ws.Columns().AdjustToContents(); + + wb.SaveAs(filePath); + } + } +} diff --git a/ClosedXML_Examples/Misc/RightToLeft.cs b/ClosedXML_Examples/Misc/RightToLeft.cs new file mode 100644 index 0000000..c9a5fa8 --- /dev/null +++ b/ClosedXML_Examples/Misc/RightToLeft.cs @@ -0,0 +1,21 @@ +using ClosedXML.Excel; +using System; + +namespace ClosedXML_Examples.Misc +{ + public class RightToLeft : IXLExample + { + public void Create(String filePath) + { + var wb = new XLWorkbook(); + + var ws = wb.Worksheets.Add("RightToLeftSheet"); + ws.Cell("A1").Value = "A1"; + ws.Cell("B1").Value = "B1"; + ws.Cell("C1").Value = "C1"; + ws.RightToLeft = true; + + wb.SaveAs(filePath); + } + } +} diff --git a/ClosedXML_Examples/PivotTables/PivotTables.cs b/ClosedXML_Examples/PivotTables/PivotTables.cs index b36a956..5678228 100644 --- a/ClosedXML_Examples/PivotTables/PivotTables.cs +++ b/ClosedXML_Examples/PivotTables/PivotTables.cs @@ -1,31 +1,110 @@ -using System; -using ClosedXML.Excel; +using ClosedXML.Excel; +using System; +using System.Collections.Generic; namespace ClosedXML_Examples { - public class PivotTables + public class PivotTables : IXLExample { + private class Pastry + { + public Pastry(string name, int numberOfOrders, double quality, string month) + { + Name = name; + NumberOfOrders = numberOfOrders; + Quality = quality; + Month = month; + } + + public string Name { get; set; } + public int NumberOfOrders { get; set; } + public double Quality { get; set; } + public string Month { get; set; } + } + public void Create(String filePath) { - var wb = new XLWorkbook(); - var ws = wb.Worksheets.Add("Pivot Table"); - - ws.Cell("A1").Value = "Category"; - ws.Cell("A2").Value = "A"; - ws.Cell("A3").Value = "B"; - ws.Cell("A4").Value = "B"; - ws.Cell("B1").Value = "Number"; - ws.Cell("B2").Value = 100; - ws.Cell("B3").Value = 150; - ws.Cell("B4").Value = 75; + var pastries = new List + { + new Pastry("Croissant", 150, 60.2, "Apr"), + new Pastry("Croissant", 250, 50.42, "May"), + new Pastry("Croissant", 134, 22.12, "June"), + new Pastry("Doughnut", 250, 89.99, "Apr"), + new Pastry("Doughnut", 225, 70, "May"), + new Pastry("Doughnut", 210, 75.33, "June"), + new Pastry("Bearclaw", 134, 10.24, "Apr"), + new Pastry("Bearclaw", 184, 33.33, "May"), + new Pastry("Bearclaw", 124, 25, "June"), + new Pastry("Danish", 394, -20.24, "Apr"), + new Pastry("Danish", 190, 60, "May"), + new Pastry("Danish", 221, 24.76, "June"), + new Pastry("Scone", 135, 0, "Apr"), + new Pastry("Scone", 122, 5.19, "May"), + new Pastry("Scone", 243, 44.2, "June") + }; - //var pivotTable = ws.Range("A1:B4").CreatePivotTable(ws.Cell("D1")); - //pivotTable.RowLabels.Add("Category"); - //pivotTable.Values.Add("Number") - // .ShowAsPctFrom("Category").And("A") - // .NumberFormat.Format = "0%"; + using (var wb = new XLWorkbook()) + { + var sheet = wb.Worksheets.Add("PastrySalesData"); + // Insert our list of pastry data into the "PastrySalesData" sheet at cell 1,1 + var source = sheet.Cell(1, 1).InsertTable(pastries, "PastrySalesData", true); + sheet.Columns().AdjustToContents(); - wb.SaveAs(filePath); + // Create a range that includes our table, including the header row + var range = source.DataRange; + var header = sheet.Range(1, 1, 1, 3); + var dataRange = sheet.Range(header.FirstCell(), range.LastCell()); + + IXLWorksheet ptSheet; + IXLPivotTable pt; + + for (int i = 1; i <= 3; i++) + { + // Add a new sheet for our pivot table + ptSheet = wb.Worksheets.Add("PivotTable" + i); + + // Create the pivot table, using the data from the "PastrySalesData" table + pt = ptSheet.PivotTables.AddNew("PivotTable", ptSheet.Cell(1, 1), dataRange); + + // The rows in our pivot table will be the names of the pastries + pt.RowLabels.Add("Name"); + if (i == 2) pt.RowLabels.Add(XLConstants.PivotTableValuesSentinalLabel); + + // The columns will be the months + pt.ColumnLabels.Add("Month"); + if (i == 3) pt.ColumnLabels.Add(XLConstants.PivotTableValuesSentinalLabel); + + // The values in our table will come from the "NumberOfOrders" field + // The default calculation setting is a total of each row/column + pt.Values.Add("NumberOfOrders", "NumberOfOrdersPercentageOfBearclaw") + .ShowAsPercentageFrom("Name").And("Bearclaw") + .NumberFormat.Format = "0%"; + + if (i > 1) + { + pt.Values.Add("Quality", "Sum of Quality") + .NumberFormat.SetFormat("#,##0.00"); + } + if (i > 2) + { + pt.Values.Add("NumberOfOrders", "Sum of NumberOfOrders"); + } + + ptSheet.Columns().AdjustToContents(); + } + + // Different kind of pivot + ptSheet = wb.Worksheets.Add("PivotTableNoColumnLabels"); + pt = ptSheet.PivotTables.AddNew("PivotTableNoColumnLabels", ptSheet.Cell(1, 1), dataRange); + + pt.RowLabels.Add("Name"); + pt.RowLabels.Add("Month"); + + pt.Values.Add("NumberOfOrders").SetSummaryFormula(XLPivotSummary.Sum);//.NumberFormat.Format = "#0.00"; + pt.Values.Add("Quality").SetSummaryFormula(XLPivotSummary.Sum);//.NumberFormat.Format = "#0.00"; + + wb.SaveAs(filePath); + } } } } diff --git a/ClosedXML_Examples/Ranges/NamedRanges.cs b/ClosedXML_Examples/Ranges/NamedRanges.cs index 8b035df..325a98f 100644 --- a/ClosedXML_Examples/Ranges/NamedRanges.cs +++ b/ClosedXML_Examples/Ranges/NamedRanges.cs @@ -1,6 +1,5 @@ -using System; -using ClosedXML.Excel; - +using ClosedXML.Excel; +using System; namespace ClosedXML_Examples.Misc { @@ -28,6 +27,12 @@ // Create a named range with the data: wsData.Range("A2:B4").AddToNamed("PeopleData"); // Default named range scope is Workbook + // Create a hidden named range + wb.NamedRanges.Add("Headers", wsData.Range("A1:B1")).Visible = false; + + // Create a hidden named range n worksheet scope + wsData.NamedRanges.Add("HeadersAndData", wsData.Range("A1:B4")).Visible = false; + // Let's use the named range in a formula: wsPresentation.Cell(1, 1).Value = "People Count:"; wsPresentation.Cell(1, 2).FormulaA1 = "COUNT(PeopleData)"; @@ -42,7 +47,6 @@ // Copy the data in a named range: wsPresentation.Cell(4, 1).Value = "People Data:"; wsPresentation.Cell(5, 1).Value = wb.Range("PeopleData"); - ///////////////////////////////////////////////////////////////////////// // For the Excel geeks out there who actually know about @@ -76,7 +80,6 @@ // Override - - #endregion + #endregion Methods } } diff --git a/ClosedXML_Examples/Styles/StyleFont.cs b/ClosedXML_Examples/Styles/StyleFont.cs index 43c86a3..d1f3a9e 100644 --- a/ClosedXML_Examples/Styles/StyleFont.cs +++ b/ClosedXML_Examples/Styles/StyleFont.cs @@ -13,7 +13,7 @@ var co = 2; var ro = 1; - + ws.Cell(++ro, co).Value = "Bold"; ws.Cell(ro, co).Style.Font.Bold = true; @@ -32,9 +32,6 @@ ws.Cell(++ro, co).Value = "Italic - true"; ws.Cell(ro, co).Style.Font.Italic = true; - ws.Cell(++ro, co).Value = "Shadow - true"; - ws.Cell(ro, co).Style.Font.Shadow = true; - ws.Cell(++ro, co).Value = "Strikethrough - true"; ws.Cell(ro, co).Style.Font.Strikethrough = true; @@ -45,7 +42,7 @@ ws.Cell(ro, co).Style.Font.VerticalAlignment = XLFontVerticalTextAlignmentValues.Superscript; ws.Column(co).AdjustToContents(); - + workbook.SaveAs(filePath); } } diff --git a/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj b/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj index 8040570..7cd14ba 100644 --- a/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj +++ b/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj @@ -55,6 +55,9 @@ + + Attributes\ColumnOrderAttribute.cs + Excel\AutoFilters\IXLAutoFilter.cs @@ -109,8 +112,8 @@ Excel\CalcEngine\Functions\DateAndTime.cs - - Excel\CalcEngine\Functions\Is.cs + + Excel\CalcEngine\Functions\Information.cs Excel\CalcEngine\Functions\Logical.cs @@ -817,6 +820,9 @@ Utils\GraphicsUtils.cs + + Utils\XmlEncoder.cs + XLHelper.cs diff --git a/ClosedXML_Net3.5/Excel/CalcEngine/Functions/Information.cs b/ClosedXML_Net3.5/Excel/CalcEngine/Functions/Information.cs new file mode 100644 index 0000000..dcb1340 --- /dev/null +++ b/ClosedXML_Net3.5/Excel/CalcEngine/Functions/Information.cs @@ -0,0 +1,152 @@ +using System; +using System.Collections.Generic; +using System.Drawing.Design; + +namespace ClosedXML.Excel.CalcEngine.Functions +{ + internal static class Information + { + public static void Register(CalcEngine ce) + { + ce.RegisterFunction("ERRORTYPE",1,ErrorType); + ce.RegisterFunction("ISBLANK", 1,int.MaxValue, IsBlank); + ce.RegisterFunction("ISERR",1, int.MaxValue, IsErr); + ce.RegisterFunction("ISERROR",1, int.MaxValue, IsError); + ce.RegisterFunction("ISEVEN",1, IsEven); + ce.RegisterFunction("ISLOGICAL",1,int.MaxValue,IsLogical); + ce.RegisterFunction("ISNA",1, int.MaxValue, IsNa); + ce.RegisterFunction("ISNONTEXT",1, int.MaxValue, IsNonText); + ce.RegisterFunction("ISNUMBER",1, int.MaxValue, IsNumber); + ce.RegisterFunction("ISODD",1,IsOdd); + ce.RegisterFunction("ISREF",1, int.MaxValue, IsRef); + ce.RegisterFunction("ISTEXT",1, int.MaxValue, IsText); + ce.RegisterFunction("N",1,N); + ce.RegisterFunction("NA",0,NA); + ce.RegisterFunction("TYPE",1,Type); + } + + static object ErrorType(List p) + { + //TODO: Write Code + throw new NotSupportedException();; + } + + static object IsBlank(List p) + { + var v = (string) p[0].Evaluate(); + var isBlank = string.IsNullOrEmpty(v); + p.RemoveAt(0); + + if (isBlank && p.Count > 0) { + isBlank = (bool)IsBlank(p); + } + + return isBlank; + } + + //TODO: Support for Error Values + static object IsErr(List p) + { + //TODO: Write Code + throw new NotSupportedException(); + } + + static object IsError(List p) + { + //TODO: Write Code + throw new NotSupportedException(); + } + + static object IsEven(List p) + { + var v = p[0].Evaluate(); + if (v is double) + { + return Math.Abs((double) v%2) < 0; + } + throw new ArgumentException("Expression doesn't evaluate to double"); + } + + static object IsLogical(List p) + { + var v = p[0].Evaluate(); + var isLogical = v is bool; + p.RemoveAt(0); + + if (isLogical && p.Count > 0) + { + isLogical = (bool) IsLogical(p); + } + + return isLogical; + } + + static object IsNa(List p) + { + //TODO: Write Code + throw new NotSupportedException();; + } + + static object IsNonText(List p) + { + return !(bool) IsText(p); + } + + static object IsNumber(List p) + { + var v = p[0].Evaluate(); + var isNumber = v is double; + p.RemoveAt(0); + + if (isNumber && p.Count > 0) { + isNumber = (bool)IsNumber(p); + } + + return isNumber; + } + + static object IsOdd(List p) + { + return !(bool) IsEven(p); + } + + static object IsRef(List p) + { + //TODO: Write Code + throw new NotSupportedException();; + } + + static object IsText(List p) + { + //Evaluate Expressions + var isText = !(bool) IsBlank(p); + if (isText) + { + isText = !(bool) IsNumber(p); + } + if (isText) + { + isText = !(bool) IsLogical(p); + } + return isText; + } + + static object N(List p) + { + //TODO: Write Code + throw new NotSupportedException();; + } + + static object NA(List p) + { + //TODO: Write Code + throw new NotSupportedException();; + } + + static object Type(List p) + { + //TODO: Write Code + throw new NotSupportedException();; + } + } +} \ No newline at end of file diff --git a/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML_Tests/ClosedXML_Tests.csproj index d4d5ba6..89a0eff 100644 --- a/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -70,12 +70,15 @@ - + + + + @@ -138,6 +141,7 @@ + @@ -229,6 +233,7 @@ + @@ -245,10 +250,12 @@ - + - + + + diff --git a/ClosedXML_Tests/Examples/ConditionalFormattingTests.cs b/ClosedXML_Tests/Examples/ConditionalFormattingTests.cs index 823ebeb..1ab8fe2 100644 --- a/ClosedXML_Tests/Examples/ConditionalFormattingTests.cs +++ b/ClosedXML_Tests/Examples/ConditionalFormattingTests.cs @@ -19,6 +19,12 @@ } [Test] + public void CFColorScaleMinimumMaximum() + { + TestHelper.RunTestExample(@"ConditionalFormatting\CFColorScaleMinimumMaximum.xlsx"); + } + + [Test] public void CFContains() { TestHelper.RunTestExample(@"ConditionalFormatting\CFContains.xlsx"); @@ -107,23 +113,5 @@ { TestHelper.RunTestExample(@"ConditionalFormatting\CFMultipleConditions.xlsx"); } - - //[Test] - //public void XXX() - //{ - // TestHelper.RunTestExample(@"ConditionalFormatting\XXX.xlsx"); - //} - // - //[Test] - //public void XXX() - //{ - // TestHelper.RunTestExample(@"ConditionalFormatting\XXX.xlsx"); - //} - // - //[Test] - //public void XXX() - //{ - // TestHelper.RunTestExample(@"ConditionalFormatting\XXX.xlsx"); - //} } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Examples/MiscTests.cs b/ClosedXML_Tests/Examples/MiscTests.cs index 38f7e8e..eebb483 100644 --- a/ClosedXML_Tests/Examples/MiscTests.cs +++ b/ClosedXML_Tests/Examples/MiscTests.cs @@ -26,6 +26,12 @@ } [Test] + public void AdjustToContentsWithAutoFilter() + { + TestHelper.RunTestExample(@"Misc\AdjustToContentsWithAutoFilter.xlsx"); + } + + [Test] public void AutoFilter() { TestHelper.RunTestExample(@"Misc\AutoFilter.xlsx"); @@ -152,6 +158,12 @@ } [Test] + public void RightToLeft() + { + TestHelper.RunTestExample(@"Misc\RightToLeft.xlsx"); + } + + [Test] public void SheetProtection() { TestHelper.RunTestExample(@"Misc\SheetProtection.xlsx"); @@ -181,4 +193,4 @@ TestHelper.RunTestExample(@"Misc\WorkbookProperties.xlsx"); } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Excel/CalcEngine/DateAndTimeTests.cs b/ClosedXML_Tests/Excel/CalcEngine/DateAndTimeTests.cs index 6213f8c..b6202d9 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/DateAndTimeTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/DateAndTimeTests.cs @@ -40,6 +40,16 @@ } [Test] + public void Days() + { + Object actual = XLWorkbook.EvaluateExpr("DAYS(DATE(2016,10,1),DATE(1992,2,29))"); + Assert.AreEqual(8981, actual); + + actual = XLWorkbook.EvaluateExpr("DAYS(\"2016-10-1\",\"1992-2-29\")"); + Assert.AreEqual(8981, actual); + } + + [Test] public void DayWithDifferentCulture() { CultureInfo ci = new CultureInfo(CultureInfo.InvariantCulture.LCID); @@ -148,6 +158,16 @@ } [Test] + public void IsoWeekNum() + { + Object actual = XLWorkbook.EvaluateExpr("ISOWEEKNUM(DATEVALUE(\"2012-3-9\"))"); + Assert.AreEqual(10, actual); + + actual = XLWorkbook.EvaluateExpr("ISOWEEKNUM(DATE(2012,12,31))"); + Assert.AreEqual(1, actual); + } + + [Test] public void Networkdays_MultipleHolidaysGiven() { var wb = new XLWorkbook(); @@ -473,4 +493,4 @@ Assert.IsTrue(XLHelper.AreEqual(5.24722222222222, (double) actual)); } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Excel/CalcEngine/InformationTests.cs b/ClosedXML_Tests/Excel/CalcEngine/InformationTests.cs new file mode 100644 index 0000000..351ad70 --- /dev/null +++ b/ClosedXML_Tests/Excel/CalcEngine/InformationTests.cs @@ -0,0 +1,401 @@ +using ClosedXML.Excel; +using NUnit.Framework; +using System; +using System.Globalization; +using System.Threading; + +namespace ClosedXML_Tests.Excel.CalcEngine +{ + [TestFixture] + public class InformationTests + { + [OneTimeSetUp] + public void SetCultureInfo() + { + Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture("en-US"); + } + + #region IsBlank Tests + + [Test] + public void IsBlank_MultipleAllEmpty_true() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet"); + var actual = ws.Evaluate("=IsBlank(A1:A3)"); + Assert.AreEqual(true, actual); + } + } + + [Test] + public void IsBlank_MultipleAllFill_false() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet"); + ws.Cell("A1").Value = "1"; + ws.Cell("A2").Value = "1"; + ws.Cell("A3").Value = "1"; + var actual = ws.Evaluate("=IsBlank(A1:A3)"); + Assert.AreEqual(false, actual); + } + } + + [Test] + public void IsBlank_MultipleMixedFill_false() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet"); + ws.Cell("A1").Value = "1"; + ws.Cell("A3").Value = "1"; + var actual = ws.Evaluate("=IsBlank(A1:A3)"); + Assert.AreEqual(false, actual); + } + } + + [Test] + public void IsBlank_Single_false() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet"); + ws.Cell("A1").Value = " "; + var actual = ws.Evaluate("=IsBlank(A1)"); + Assert.AreEqual(false, actual); + } + } + + [Test] + public void IsBlank_Single_true() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet"); + var actual = ws.Evaluate("=IsBlank(A1)"); + Assert.AreEqual(true, actual); + } + } + #endregion IsBlank Tests + + #region IsEven Tests + + [Test] + public void IsEven_Single_False() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet"); + + ws.Cell("A1").Value = 1; + ws.Cell("A2").Value = 1.2; + ws.Cell("A3").Value = 3; + + var actual = ws.Evaluate("=IsEven(A1)"); + Assert.AreEqual(false, actual); + + actual = ws.Evaluate("=IsEven(A2)"); + Assert.AreEqual(false, actual); + + actual = ws.Evaluate("=IsEven(A3)"); + Assert.AreEqual(false, actual); + } + } + + [Test] + public void IsEven_Single_True() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet"); + + ws.Cell("A1").Value = 4; + ws.Cell("A2").Value = 0.2; + ws.Cell("A3").Value = 12.2; + + var actual = ws.Evaluate("=IsEven(A1)"); + Assert.AreEqual(true, actual); + + actual = ws.Evaluate("=IsEven(A2)"); + Assert.AreEqual(true, actual); + + actual = ws.Evaluate("=IsEven(A3)"); + Assert.AreEqual(true, actual); + } + } + + #endregion IsEven Tests + + #region IsLogical Tests + + [Test] + public void IsLogical_Simpe_False() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet"); + + ws.Cell("A1").Value = 123; + + var actual = ws.Evaluate("=IsLogical(A1)"); + Assert.AreEqual(false, actual); + } + } + + [Test] + public void IsLogical_Simple_True() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet"); + + ws.Cell("A1").Value = true; + + var actual = ws.Evaluate("=IsLogical(A1)"); + Assert.AreEqual(true, actual); + } + } + #endregion IsLogical Tests + + #region IsNotText Tests + + [Test] + public void IsNotText_Simple_false() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet"); + ws.Cell("A1").Value = "asd"; + var actual = ws.Evaluate("=IsNonText(A1)"); + Assert.AreEqual(false, actual); + } + } + + [Test] + public void IsNotText_Simple_true() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet"); + ws.Cell("A1").Value = "123"; //Double Value + ws.Cell("A2").Value = DateTime.Now; //Date Value + ws.Cell("A3").Value = "12,235.5"; //Comma Formatting + ws.Cell("A4").Value = "$12,235.5"; //Currency Value + ws.Cell("A5").Value = true; //Bool Value + ws.Cell("A6").Value = "12%"; //Percentage Value + + var actual = ws.Evaluate("=IsNonText(A1)"); + Assert.AreEqual(true, actual); + actual = ws.Evaluate("=IsNonText(A2)"); + Assert.AreEqual(true, actual); + actual = ws.Evaluate("=IsNonText(A3)"); + Assert.AreEqual(true, actual); + actual = ws.Evaluate("=IsNonText(A4)"); + Assert.AreEqual(true, actual); + actual = ws.Evaluate("=IsNonText(A5)"); + Assert.AreEqual(true, actual); + actual = ws.Evaluate("=IsNonText(A6)"); + Assert.AreEqual(true, actual); + } + } + #endregion IsNotText Tests + + #region IsNumber Tests + + [Test] + public void IsNumber_Simple_false() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet"); + ws.Cell("A1").Value = "asd"; //String Value + ws.Cell("A2").Value = true; //Bool Value + + var actual = ws.Evaluate("=IsNumber(A1)"); + Assert.AreEqual(false, actual); + actual = ws.Evaluate("=IsNumber(A2)"); + Assert.AreEqual(false, actual); + } + } + + [Test] + public void IsNumber_Simple_true() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet"); + ws.Cell("A1").Value = "123"; //Double Value + ws.Cell("A2").Value = DateTime.Now; //Date Value + ws.Cell("A3").Value = "12,235.5"; //Coma Formatting + ws.Cell("A4").Value = "$12,235.5"; //Currency Value + ws.Cell("A5").Value = "12%"; //Percentage Value + + var actual = ws.Evaluate("=IsNumber(A1)"); + Assert.AreEqual(true, actual); + actual = ws.Evaluate("=IsNumber(A2)"); + Assert.AreEqual(true, actual); + actual = ws.Evaluate("=IsNumber(A3)"); + Assert.AreEqual(true, actual); + actual = ws.Evaluate("=IsNumber(A4)"); + Assert.AreEqual(true, actual); + actual = ws.Evaluate("=IsNumber(A5)"); + Assert.AreEqual(true, actual); + } + } + #endregion IsNumber Tests + + #region IsOdd Test + + [Test] + public void IsOdd_Simple_false() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet"); + + ws.Cell("A1").Value = 4; + ws.Cell("A2").Value = 0.2; + ws.Cell("A3").Value = 12.2; + + var actual = ws.Evaluate("=IsOdd(A1)"); + Assert.AreEqual(false, actual); + actual = ws.Evaluate("=IsOdd(A2)"); + Assert.AreEqual(false, actual); + actual = ws.Evaluate("=IsOdd(A3)"); + Assert.AreEqual(false, actual); + } + } + + [Test] + public void IsOdd_Simple_true() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet"); + + ws.Cell("A1").Value = 1; + ws.Cell("A2").Value = 1.2; + ws.Cell("A3").Value = 3; + + var actual = ws.Evaluate("=IsOdd(A1)"); + Assert.AreEqual(true, actual); + actual = ws.Evaluate("=IsOdd(A2)"); + Assert.AreEqual(true, actual); + actual = ws.Evaluate("=IsOdd(A3)"); + Assert.AreEqual(true, actual); + } + } + #endregion IsOdd Test + + #region IsText Tests + + [Test] + public void IsText_Simple_false() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet"); + ws.Cell("A1").Value = "123"; //Double Value + ws.Cell("A2").Value = DateTime.Now; //Date Value + ws.Cell("A3").Value = "12,235.5"; //Comma Formatting + ws.Cell("A4").Value = "$12,235.5"; //Currency Value + ws.Cell("A5").Value = true; //Bool Value + ws.Cell("A6").Value = "12%"; //Percentage Value + + var actual = ws.Evaluate("=IsText(A1)"); + Assert.AreEqual(false, actual); + actual = ws.Evaluate("=IsText(A2)"); + Assert.AreEqual(false, actual); + actual = ws.Evaluate("=IsText(A3)"); + Assert.AreEqual(false, actual); + actual = ws.Evaluate("=IsText(A4)"); + Assert.AreEqual(false, actual); + actual = ws.Evaluate("=IsText(A5)"); + Assert.AreEqual(false, actual); + actual = ws.Evaluate("=IsText(A6)"); + Assert.AreEqual(false, actual); + } + } + + [Test] + public void IsText_Simple_true() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet"); + + ws.Cell("A1").Value = "asd"; + + var actual = ws.Evaluate("=IsText(A1)"); + Assert.AreEqual(true, actual); + } + } + #endregion IsText Tests + + #region N Tests + + [Test] + public void N_Date_SerialNumber() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet"); + var testedDate = DateTime.Now; + ws.Cell("A1").Value = testedDate; + var actual = ws.Evaluate("=N(A1)"); + Assert.AreEqual(testedDate.ToOADate(), actual); + } + } + + [Test] + public void N_False_Zero() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet"); + ws.Cell("A1").Value = false; + var actual = ws.Evaluate("=N(A1)"); + Assert.AreEqual(0, actual); + } + } + + [Test] + public void N_Number_Number() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet"); + var testedValue = 123; + ws.Cell("A1").Value = testedValue; + var actual = ws.Evaluate("=N(A1)"); + Assert.AreEqual(testedValue, actual); + } + } + + [Test] + public void N_String_Zero() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet"); + ws.Cell("A1").Value = "asd"; + var actual = ws.Evaluate("=N(A1)"); + Assert.AreEqual(0, actual); + } + } + + [Test] + public void N_True_One() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet"); + ws.Cell("A1").Value = true; + var actual = ws.Evaluate("=N(A1)"); + Assert.AreEqual(1, actual); + } + } + #endregion N Tests + } +} diff --git a/ClosedXML_Tests/Excel/CalcEngine/IsTests.cs b/ClosedXML_Tests/Excel/CalcEngine/IsTests.cs deleted file mode 100644 index 2d98c68..0000000 --- a/ClosedXML_Tests/Excel/CalcEngine/IsTests.cs +++ /dev/null @@ -1,28 +0,0 @@ -using System; -using ClosedXML.Excel; -using NUnit.Framework; - -namespace ClosedXML_Tests.Excel.CalcEngine -{ - - [TestFixture] - public class IsTests - { - [Test] - public void IsBlank_true() - { - var ws = new XLWorkbook().AddWorksheet("Sheet"); - var actual = ws.Evaluate("=IsBlank(A1)"); - Assert.AreEqual(true, actual); - } - - [Test] - public void IsBlank_false() - { - var ws = new XLWorkbook().AddWorksheet("Sheet"); - ws.Cell("A1").Value = " "; - var actual = ws.Evaluate("=IsBlank(A1)"); - Assert.AreEqual(false, actual); - } - } -} \ No newline at end of file diff --git a/ClosedXML_Tests/Excel/CalcEngine/LogicalTests.cs b/ClosedXML_Tests/Excel/CalcEngine/LogicalTests.cs index c3aeee4..a78ce97 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/LogicalTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/LogicalTests.cs @@ -33,5 +33,22 @@ Object actual = XLWorkbook.EvaluateExpr(@"if(1 = 2, ""T"", ""F"")"); Assert.AreEqual("F", actual); } + + [Test] + public void If_Comparing_Against_Empty_String() + { + Object actual; + actual = XLWorkbook.EvaluateExpr(@"if(date(2016, 1, 1) = """", ""A"",""B"")"); + Assert.AreEqual("B", actual); + + actual = XLWorkbook.EvaluateExpr(@"if("""" = date(2016, 1, 1), ""A"",""B"")"); + Assert.AreEqual("B", actual); + + actual = XLWorkbook.EvaluateExpr(@"if("""" = 123, ""A"",""B"")"); + Assert.AreEqual("B", actual); + + actual = XLWorkbook.EvaluateExpr(@"if("""" = """", ""A"",""B"")"); + Assert.AreEqual("A", actual); + } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Excel/CalcEngine/LookupTests.cs b/ClosedXML_Tests/Excel/CalcEngine/LookupTests.cs index 5d232f6..babfab0 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/LookupTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/LookupTests.cs @@ -17,57 +17,67 @@ workbook = SetupWorkbook(); } + [OneTimeTearDown] + public void Dispose() + { + workbook.Dispose(); + } + 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} + 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().InsertTable(data); + ws.FirstCell() + .CellBelow() + .CellRight() + .InsertTable(data); + return wb; } @@ -75,7 +85,7 @@ public void Hlookup() { // Range lookup false - var value = workbook.Evaluate(@"=HLOOKUP(""Total"",Data!$A$1:$H$70,4,FALSE)"); + var value = workbook.Evaluate(@"=HLOOKUP(""Total"",Data!$B$2:$I$71,4,FALSE)"); Assert.AreEqual(179.64, value); } @@ -83,34 +93,34 @@ public void Vlookup() { // Range lookup false - var value = workbook.Evaluate("=VLOOKUP(3,Data!$A$1:$H$70,3,FALSE)"); + var value = workbook.Evaluate("=VLOOKUP(3,Data!$B$2:$I$71,3,FALSE)"); Assert.AreEqual("Central", value); - value = workbook.Evaluate("=VLOOKUP(DATE(2015,5,22),Data!B:H,7,FALSE)"); + value = workbook.Evaluate("=VLOOKUP(DATE(2015,5,22),Data!C:I,7,FALSE)"); Assert.AreEqual(63.68, value); - value = workbook.Evaluate(@"=VLOOKUP(""Central"",Data!C:D,2,FALSE)"); + value = workbook.Evaluate(@"=VLOOKUP(""Central"",Data!D:E,2,FALSE)"); Assert.AreEqual("Kivell", value); // Range lookup true - value = workbook.Evaluate("=VLOOKUP(3,Data!$A$1:$H$70,8,TRUE)"); + value = workbook.Evaluate("=VLOOKUP(3,Data!$B$2:$I$71,8,TRUE)"); Assert.AreEqual(179.64, value); - value = workbook.Evaluate("=VLOOKUP(14.5,Data!$A$1:$H$70,8,TRUE)"); + value = workbook.Evaluate("=VLOOKUP(14.5,Data!$B$2:$I$71,8,TRUE)"); Assert.AreEqual(174.65, value); - value = workbook.Evaluate("=VLOOKUP(50,Data!$A$1:$H$70,8,TRUE)"); + value = workbook.Evaluate("=VLOOKUP(50,Data!$B$2:$I$71,8,TRUE)"); Assert.AreEqual(139.72, value); } [Test] public void Vlookup_Exceptions() { - Assert.That(() => workbook.Evaluate(@"=VLOOKUP("""",Data!$A$1:$H$70,3,FALSE)"), Throws.Exception); - Assert.That(() => workbook.Evaluate(@"=VLOOKUP(50,Data!$A$1:$H$70,3,FALSE)"), Throws.Exception); - Assert.That(() => workbook.Evaluate(@"=VLOOKUP(20,Data!$A$1:$H$70,9,FALSE)"), Throws.Exception); + Assert.That(() => workbook.Evaluate(@"=VLOOKUP("""",Data!$B$2:$I$71,3,FALSE)"), Throws.Exception); + Assert.That(() => workbook.Evaluate(@"=VLOOKUP(50,Data!$B$2:$I$71,3,FALSE)"), Throws.Exception); + Assert.That(() => workbook.Evaluate(@"=VLOOKUP(20,Data!$B$2:$I$71,9,FALSE)"), Throws.Exception); - Assert.That(() => workbook.Evaluate(@"=VLOOKUP(-1,Data!$A$1:$H$70,9,TRUE)"), Throws.Exception); + Assert.That(() => workbook.Evaluate(@"=VLOOKUP(-1,Data!$B$2:$I$71,9,TRUE)"), Throws.Exception); } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Excel/Cells/XLCellTests.cs b/ClosedXML_Tests/Excel/Cells/XLCellTests.cs index 1415703..750af3a 100644 --- a/ClosedXML_Tests/Excel/Cells/XLCellTests.cs +++ b/ClosedXML_Tests/Excel/Cells/XLCellTests.cs @@ -1,10 +1,11 @@ -using System; +using ClosedXML.Excel; +using NUnit.Framework; +using System; using System.Collections.Generic; using System.Globalization; +using System.IO; using System.Linq; using System.Threading; -using ClosedXML.Excel; -using NUnit.Framework; namespace ClosedXML_Tests { @@ -56,7 +57,7 @@ { IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1"); IXLCell cell = ws.Cell("A1"); - var doubleList = new List {1.0/0.0}; + var doubleList = new List { 1.0 / 0.0 }; cell.Value = doubleList.AsEnumerable(); Assert.AreNotEqual(XLCellValues.Number, cell.DataType); @@ -67,7 +68,7 @@ { IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1"); IXLCell cell = ws.Cell("A1"); - var doubleList = new List {0.0/0.0}; + var doubleList = new List { 0.0 / 0.0 }; cell.Value = doubleList.AsEnumerable(); Assert.AreNotEqual(XLCellValues.Number, cell.DataType); @@ -77,7 +78,7 @@ public void InsertData1() { IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1"); - IXLRange range = ws.Cell(2, 2).InsertData(new[] {"a", "b", "c"}); + IXLRange range = ws.Cell(2, 2).InsertData(new[] { "a", "b", "c" }); Assert.AreEqual("'Sheet1'!B2:B4", range.ToString()); } @@ -345,5 +346,25 @@ var actual = (DateTime)cell.Value; Assert.AreEqual(expected, actual); } + + [Test] + public void TestInvalidXmlCharacters() + { + byte[] data; + + using (var stream = new MemoryStream()) + { + var wb = new XLWorkbook(); + wb.AddWorksheet("Sheet1").FirstCell().SetValue("\u0018"); + wb.SaveAs(stream); + data = stream.ToArray(); + } + + using (var stream = new MemoryStream(data)) + { + var wb = new XLWorkbook(stream); + Assert.AreEqual("\u0018", wb.Worksheets.First().FirstCell().Value); + } + } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Excel/Columns/ColumnTests.cs b/ClosedXML_Tests/Excel/Columns/ColumnTests.cs index 0a98c4f..4297a99 100644 --- a/ClosedXML_Tests/Excel/Columns/ColumnTests.cs +++ b/ClosedXML_Tests/Excel/Columns/ColumnTests.cs @@ -107,8 +107,8 @@ IXLColumn column3 = ws.Column(3); IXLColumn columnIns = ws.Column(2).InsertColumnsBefore(1).First(); - string outputPath = Path.Combine(TestHelper.TestsOutputDirectory, @"ForTesting\Sandbox.xlsx"); - wb.SaveAs(outputPath); + string outputPath = Path.Combine(TestHelper.TestsOutputDirectory, "ForTesting", "Sandbox.xlsx"); + wb.SaveAs(outputPath, true); Assert.AreEqual(XLColor.Red, ws.Column(1).Cell(1).Style.Fill.BackgroundColor); Assert.AreEqual(XLColor.Red, ws.Column(1).Cell(2).Style.Fill.BackgroundColor); @@ -237,4 +237,4 @@ Assert.AreEqual(2, lastCoUsed); } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Excel/Loading/LoadingTests.cs b/ClosedXML_Tests/Excel/Loading/LoadingTests.cs index 5412c0a..3568007 100644 --- a/ClosedXML_Tests/Excel/Loading/LoadingTests.cs +++ b/ClosedXML_Tests/Excel/Loading/LoadingTests.cs @@ -8,7 +8,7 @@ namespace ClosedXML_Tests.Excel { - // Tests in this fixture test only the successful loading of existing Excel files, + // Tests in this fixture test only the successful loading of existing Excel files, // i.e. we test that ClosedXML doesn't choke on a given input file // These tests DO NOT test that ClosedXML successfully recognises all the Excel parts or that it can successfully save those parts again. [TestFixture] @@ -19,7 +19,8 @@ { var files = new List() { - @"Misc\TableWithCustomTheme.xlsx" + @"Misc\TableWithCustomTheme.xlsx", + @"Misc\EmptyTable.xlsx" }; foreach (var file in files) @@ -27,5 +28,17 @@ TestHelper.LoadFile(file); } } + + [Test] + public void CanLoadAndManipulateFileWithEmptyTable() + { + using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Misc\EmptyTable.xlsx"))) + using (var wb = new XLWorkbook(stream)) + { + var ws = wb.Worksheets.First(); + var table = ws.Tables.First(); + table.DataRange.InsertRowsBelow(5); + } + } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Excel/Misc/CopyContentsTests.cs b/ClosedXML_Tests/Excel/Misc/CopyContentsTests.cs index ce055cc..c9c77f6 100644 --- a/ClosedXML_Tests/Excel/Misc/CopyContentsTests.cs +++ b/ClosedXML_Tests/Excel/Misc/CopyContentsTests.cs @@ -113,7 +113,7 @@ copyRowSheet.Cell("G2").Value = "must be removed after copy"; originalRow.CopyTo(destinationRow); } - TestHelper.SaveWorkbook(workbook, @"Misc\CopyRowContents.xlsx"); + TestHelper.SaveWorkbook(workbook, "Misc", "CopyRowContents.xlsx"); } } } \ No newline at end of file diff --git a/ClosedXML_Tests/Excel/Misc/FormulaTests.cs b/ClosedXML_Tests/Excel/Misc/FormulaTests.cs index 9d7cc15..d9bb00a 100644 --- a/ClosedXML_Tests/Excel/Misc/FormulaTests.cs +++ b/ClosedXML_Tests/Excel/Misc/FormulaTests.cs @@ -1,5 +1,6 @@ using ClosedXML.Excel; using NUnit.Framework; +using System; namespace ClosedXML_Tests.Excel { @@ -18,5 +19,65 @@ ws.Cell("A1").CopyTo("A2"); Assert.AreEqual("B2", ws.Cell("A2").FormulaA1); } + + [Test] + public void CopyFormula2() + { + using (var wb = new XLWorkbook()) + { + IXLWorksheet ws = wb.Worksheets.Add("Sheet1"); + + ws.Cell("A1").FormulaA1 = "A2-1"; + ws.Cell("A1").CopyTo("B1"); + Assert.AreEqual("R[1]C-1", ws.Cell("A1").FormulaR1C1); + Assert.AreEqual("R[1]C-1", ws.Cell("B1").FormulaR1C1); + Assert.AreEqual("B2-1", ws.Cell("B1").FormulaA1); + + ws.Cell("A1").FormulaA1 = "B1+1"; + ws.Cell("A1").CopyTo("A2"); + Assert.AreEqual("RC[1]+1", ws.Cell("A1").FormulaR1C1); + Assert.AreEqual("RC[1]+1", ws.Cell("A2").FormulaR1C1); + Assert.AreEqual("B2+1", ws.Cell("A2").FormulaA1); + } + } + + [Test] + public void CopyFormulaWithSheetNameThatResemblesFormula() + { + using (var wb = new XLWorkbook()) + { + IXLWorksheet ws = wb.Worksheets.Add("S10 Data"); + ws.Cell("A1").Value = "Some value"; + + ws = wb.Worksheets.Add("Summary"); + ws.Cell("A1").FormulaA1 = "='S10 Data'!A1"; + Assert.AreEqual("Some value", ws.Cell("A1").Value); + + ws.Cell("A1").CopyTo("A2"); + Assert.AreEqual("'S10 Data'!A2", ws.Cell("A2").FormulaA1); + + ws.Cell("A1").CopyTo("B1"); + Assert.AreEqual("'S10 Data'!B1", ws.Cell("B1").FormulaA1); + } + } + + [Test] + public void DateAgainstStringComparison() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + ws.Cell("A1").Value = new DateTime(2016, 1, 1); + ws.Cell("A1").DataType = XLCellValues.DateTime; + + ws.Cell("A2").FormulaA1 = @"=IF(A1 = """", ""A"", ""B"")"; + var actual = ws.Cell("A2").Value; + Assert.AreEqual(actual, "B"); + + ws.Cell("A3").FormulaA1 = @"=IF("""" = A1, ""A"", ""B"")"; + actual = ws.Cell("A3").Value; + Assert.AreEqual(actual, "B"); + } + } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Excel/Misc/XmlEncoderTests.cs b/ClosedXML_Tests/Excel/Misc/XmlEncoderTests.cs new file mode 100644 index 0000000..d6bac0a --- /dev/null +++ b/ClosedXML_Tests/Excel/Misc/XmlEncoderTests.cs @@ -0,0 +1,33 @@ +using ClosedXML.Utils; +using NUnit.Framework; + +namespace ClosedXML_Tests.Excel +{ + [TestFixture] + public class XmlEncoderTest + { + [Test] + public void TestControlChars() + { + Assert.AreEqual("_x0001_ _x0002_ _x0003_ _x0004_", XmlEncoder.EncodeString("\u0001 \u0002 \u0003 \u0004")); + Assert.AreEqual("_x0005_ _x0006_ _x0007_ _x0008_", XmlEncoder.EncodeString("\u0005 \u0006 \u0007 \u0008")); + Assert.AreEqual("\u0001 \u0002 \u0003 \u0004", XmlEncoder.DecodeString("_x0001_ _x0002_ _x0003_ _x0004_")); + Assert.AreEqual("\u0005 \u0006 \u0007 \u0008", XmlEncoder.DecodeString("_x0005_ _x0006_ _x0007_ _x0008_")); + } + + [Test] + public void TestIsXmlChar() + { + Assert.AreEqual(false, XmlEncoder.IsXmlChar('\u0001')); + Assert.AreEqual(false, XmlEncoder.IsXmlChar('\u0005')); + Assert.AreEqual(false, XmlEncoder.IsXmlChar('\u0007')); + Assert.AreEqual(false, XmlEncoder.IsXmlChar('\u0008')); + Assert.AreEqual(true, XmlEncoder.IsXmlChar('J')); + Assert.AreEqual(true, XmlEncoder.IsXmlChar('+')); + Assert.AreEqual(true, XmlEncoder.IsXmlChar('S')); + Assert.AreEqual(true, XmlEncoder.IsXmlChar('4')); + Assert.AreEqual(true, XmlEncoder.IsXmlChar('!')); + Assert.AreEqual(true, XmlEncoder.IsXmlChar('$')); + } + } +} diff --git a/ClosedXML_Tests/Excel/PageSetup/HeaderFooterTests.cs b/ClosedXML_Tests/Excel/PageSetup/HeaderFooterTests.cs index d1748a9..18fe221 100644 --- a/ClosedXML_Tests/Excel/PageSetup/HeaderFooterTests.cs +++ b/ClosedXML_Tests/Excel/PageSetup/HeaderFooterTests.cs @@ -17,7 +17,7 @@ ws.PageSetup.Header.Center.AddText("Initial page header", XLHFOccurrence.EvenPages); var ms = new MemoryStream(); - wb.SaveAs(ms); + wb.SaveAs(ms, true); wb = new XLWorkbook(ms); ws = wb.Worksheets.First(); @@ -25,7 +25,7 @@ ws.PageSetup.Header.Center.Clear(); ws.PageSetup.Header.Center.AddText("Changed header", XLHFOccurrence.EvenPages); - wb.SaveAs(ms); + wb.SaveAs(ms, true); wb = new XLWorkbook(ms); ws = wb.Worksheets.First(); @@ -34,4 +34,4 @@ Assert.AreEqual("Changed header", newHeader); } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs b/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs index dadb7f9..345daa6 100644 --- a/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs +++ b/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs @@ -1,16 +1,15 @@ -using NUnit.Framework; +using ClosedXML_Examples; +using NUnit.Framework; namespace ClosedXML_Tests { [TestFixture] public class XLPivotTableTests { - //[Test] - //public void CreateTable() - //{ - // var ws = new XLWorkbook().Worksheets.Add("Sheet1"); - - - //} + [Test] + public void PivotTables() + { + TestHelper.RunTestExample(@"PivotTables\PivotTables.xlsx"); + } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Excel/Ranges/UsedAndUnusedCellsTests.cs b/ClosedXML_Tests/Excel/Ranges/UsedAndUnusedCellsTests.cs new file mode 100644 index 0000000..a096c33 --- /dev/null +++ b/ClosedXML_Tests/Excel/Ranges/UsedAndUnusedCellsTests.cs @@ -0,0 +1,133 @@ +using ClosedXML.Excel; +using NUnit.Framework; +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML_Tests.Excel.Ranges +{ + [TestFixture] + public class UsedAndUnusedCellsTests + { + private XLWorkbook workbook; + + [OneTimeSetUp] + public void SetupWorkbook() + { + workbook = new XLWorkbook(); + var ws = workbook.AddWorksheet("Sheet1"); + ws.Cell(1, 1).Value = "A1"; + ws.Cell(1, 3).Value = "C1"; + ws.Cell(2, 2).Value = "B2"; + ws.Cell(4, 1).Value = "A4"; + ws.Cell(5, 2).Value = "B5"; + } + + [Test] + public void CountUsedCellsInRow() + { + int i = 0; + var row = workbook.Worksheets.First().FirstRow(); + foreach (var cell in row.Cells()) // Cells() returns UnUsed cells by default + { + i++; + } + Assert.AreEqual(2, i); + + i = 0; + row = workbook.Worksheets.First().FirstRow().RowBelow(); + foreach (var cell in row.Cells()) + { + i++; + } + Assert.AreEqual(1, i); + } + + [Test] + public void CountAllCellsInRow() + { + int i = 0; + var row = workbook.Worksheets.First().FirstRow(); + foreach (var cell in row.Cells(false)) // All cells in range between first and last cells used + { + i++; + } + Assert.AreEqual(3, i); + + i = 0; + row = workbook.Worksheets.First().FirstRow().RowBelow(); //This row has no empty cells BETWEEN used cells + foreach (var cell in row.Cells(false)) + { + i++; + } + Assert.AreEqual(1, i); + } + + [Test] + public void CountUsedCellsInColumn() + { + int i = 0; + var column = workbook.Worksheets.First().FirstColumn(); + foreach (var cell in column.Cells()) // Cells() returns UnUsed cells by default + { + i++; + } + Assert.AreEqual(2, i); + + i = 0; + column = workbook.Worksheets.First().FirstColumn().ColumnRight().ColumnRight(); + foreach (var cell in column.Cells()) + { + i++; + } + Assert.AreEqual(1, i); + } + + [Test] + public void CountAllCellsInColumn() + { + int i = 0; + var column = workbook.Worksheets.First().FirstColumn(); + foreach (var cell in column.Cells(false)) // All cells in range between first and last cells used + { + i++; + } + Assert.AreEqual(4, i); + + i = 0; + column = workbook.Worksheets.First().FirstColumn().ColumnRight().ColumnRight(); //This column has no empty cells BETWEEN used cells + foreach (var cell in column.Cells(false)) + { + i++; + } + Assert.AreEqual(1, i); + } + + [Test] + public void CountUsedCellsInWorksheet() + { + var ws = workbook.Worksheets.First(); + int i = 0; + + foreach (var cell in ws.Cells()) // Only used cells in worksheet + { + i++; + } + Assert.AreEqual(5, i); + } + + [Test] + public void CountAllCellsInWorksheet() + { + var ws = workbook.Worksheets.First(); + int i = 0; + + foreach (var cell in ws.Cells(false)) // All cells in range between first and last cells used (cartesian product of range) + { + i++; + } + Assert.AreEqual(15, i); + } + } +} diff --git a/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTests.cs b/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTests.cs index c90f624..22bd7c6 100644 --- a/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTests.cs +++ b/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTests.cs @@ -178,7 +178,7 @@ // ws.NamedRanges.Add("TestRange", "\"Hello\""); // using (MemoryStream memoryStream = new MemoryStream()) // { - // wb.SaveAs(memoryStream); + // wb.SaveAs(memoryStream, true); // var wb2 = new XLWorkbook(memoryStream); // var text = wb2.Worksheet("Sheet1").NamedRanges.First() // memoryStream.Close(); @@ -187,4 +187,4 @@ //} } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Excel/Saving/SavingTests.cs b/ClosedXML_Tests/Excel/Saving/SavingTests.cs new file mode 100644 index 0000000..22aa34a --- /dev/null +++ b/ClosedXML_Tests/Excel/Saving/SavingTests.cs @@ -0,0 +1,30 @@ +using ClosedXML.Excel; +using NUnit.Framework; +using System.IO; + +namespace ClosedXML_Tests.Excel.Saving +{ + [TestFixture] + public class SavingTests + { + [Test] + public void CanSuccessfullySaveFileMultipleTimes() + { + using (var wb = new XLWorkbook()) + { + var sheet = wb.Worksheets.Add("TestSheet"); + var memoryStream = new MemoryStream(); + wb.SaveAs(memoryStream, true); + + for (int i = 1; i <= 3; i++) + { + sheet.Cell(i, 1).Value = "test" + i; + wb.SaveAs(memoryStream, true); + } + + memoryStream.Close(); + memoryStream.Dispose(); + } + } + } +} diff --git a/ClosedXML_Tests/Excel/Tables/TablesTests.cs b/ClosedXML_Tests/Excel/Tables/TablesTests.cs index 7ebf9bc..ea27d0d 100644 --- a/ClosedXML_Tests/Excel/Tables/TablesTests.cs +++ b/ClosedXML_Tests/Excel/Tables/TablesTests.cs @@ -1,10 +1,12 @@ -using System; +using ClosedXML.Attributes; +using ClosedXML.Excel; +using NUnit.Framework; +using System; using System.Collections.Generic; +using System.ComponentModel.DataAnnotations; using System.Data; using System.IO; using System.Linq; -using ClosedXML.Excel; -using NUnit.Framework; namespace ClosedXML_Tests.Excel { @@ -14,12 +16,26 @@ [TestFixture] public class TablesTests { - public class TestObject + public class TestObjectWithoutAttributes { public String Column1 { get; set; } public String Column2 { get; set; } } + public class TestObjectWithAttributes + { + public int UnOrderedColumn { get; set; } + + [Display(Name ="SecondColumn"), ColumnOrder(1)] + public String Column1 { get; set; } + + [Display(Name = "FirstColumn"), ColumnOrder(0)] + public String Column2 { get; set; } + + [Display(Name = "SomeFieldNotProperty"), ColumnOrder(2)] + public int MyField; + } + [Test] public void CanSaveTableCreatedFromEmptyDataTable() { @@ -31,7 +47,7 @@ wb.AddWorksheet(dt); using (var ms = new MemoryStream()) - wb.SaveAs(ms); + wb.SaveAs(ms, true); } [Test] @@ -43,7 +59,7 @@ ws.Range("A1").CreateTable(); using (var ms = new MemoryStream()) - wb.SaveAs(ms); + wb.SaveAs(ms, true); } [Test] @@ -85,7 +101,7 @@ ws.RangeUsed().CreateTable(); using (var ms = new MemoryStream()) { - wb.SaveAs(ms); + wb.SaveAs(ms, true); var wb2 = new XLWorkbook(ms); IXLWorksheet ws2 = wb2.Worksheet(1); IXLTable table2 = ws2.Table(0); @@ -115,7 +131,7 @@ using (var ms = new MemoryStream()) { - wb.SaveAs(ms); + wb.SaveAs(ms, true); var wb2 = new XLWorkbook(ms); IXLWorksheet ws2 = wb2.Worksheet(1); IXLTable table2 = ws2.Table(0); @@ -151,7 +167,7 @@ [Test] public void TableCreatedFromEmptyListOfObject() { - var l = new List(); + var l = new List(); var wb = new XLWorkbook(); IXLWorksheet ws = wb.AddWorksheet("Sheet1"); @@ -160,6 +176,25 @@ } [Test] + public void TableCreatedFromListOfObjectWithPropertyAttributes() + { + var l = new List() + { + new TestObjectWithAttributes() { Column1 = "a", Column2 = "b", MyField = 4, UnOrderedColumn = 999 }, + new TestObjectWithAttributes() { Column1 = "c", Column2 = "d", MyField = 5, UnOrderedColumn = 777 } + }; + + var wb = new XLWorkbook(); + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().InsertTable(l); + Assert.AreEqual(4, ws.Tables.First().ColumnCount()); + Assert.AreEqual("FirstColumn", ws.FirstCell().Value); + Assert.AreEqual("SecondColumn", ws.FirstCell().CellRight().Value); + Assert.AreEqual("SomeFieldNotProperty", ws.FirstCell().CellRight().CellRight().Value); + Assert.AreEqual("UnOrderedColumn", ws.FirstCell().CellRight().CellRight().CellRight().Value); + } + + [Test] public void TableInsertAboveFromData() { var wb = new XLWorkbook(); @@ -177,13 +212,9 @@ row = table.DataRange.InsertRowsAbove(1).First(); row.Field("Value").Value = 1; - //wb.SaveAs(@"D:\Excel Files\ForTesting\Sandbox.xlsx"); - Assert.AreEqual(1, ws.Cell(2, 1).GetDouble()); Assert.AreEqual(2, ws.Cell(3, 1).GetDouble()); Assert.AreEqual(3, ws.Cell(4, 1).GetDouble()); - - //wb.SaveAs(@"D:\Excel Files\ForTesting\Sandbox.xlsx"); } [Test] @@ -196,20 +227,17 @@ IXLTable table = ws.Range("A1:A2").CreateTable(); table.SetShowTotalsRow() .Field(0).TotalsRowFunction = XLTotalsRowFunction.Sum; - //wb.SaveAs(@"D:\Excel Files\ForTesting\Sandbox1.xlsx"); + IXLTableRow row = table.DataRange.FirstRow(); row.Field("Value").Value = 3; row = row.InsertRowsAbove(1).First(); row.Field("Value").Value = 2; row = row.InsertRowsAbove(1).First(); row.Field("Value").Value = 1; - //wb.SaveAs(@"D:\Excel Files\ForTesting\Sandbox2.xlsx"); Assert.AreEqual(1, ws.Cell(2, 1).GetDouble()); Assert.AreEqual(2, ws.Cell(3, 1).GetDouble()); Assert.AreEqual(3, ws.Cell(4, 1).GetDouble()); - - //wb.SaveAs(@"D:\Excel Files\ForTesting\Sandbox.xlsx"); } [Test] @@ -230,8 +258,6 @@ row = table.DataRange.InsertRowsBelow(1).First(); row.Field("Value").Value = 3; - //wb.SaveAs(@"D:\Excel Files\ForTesting\Sandbox.xlsx"); - Assert.AreEqual(1, ws.Cell(2, 1).GetDouble()); Assert.AreEqual(2, ws.Cell(3, 1).GetDouble()); Assert.AreEqual(3, ws.Cell(4, 1).GetDouble()); @@ -258,8 +284,6 @@ Assert.AreEqual(1, ws.Cell(2, 1).GetDouble()); Assert.AreEqual(2, ws.Cell(3, 1).GetDouble()); Assert.AreEqual(3, ws.Cell(4, 1).GetDouble()); - - //wb.SaveAs(@"D:\Excel Files\ForTesting\Sandbox.xlsx"); } [Test] @@ -272,11 +296,13 @@ .CellBelow().SetValue("B") .CellBelow().SetValue("C"); - ws.RangeUsed().CreateTable().SetShowHeaderRow(false); + IXLTable table = ws.RangeUsed().CreateTable(); - IXLTable table = ws.Tables.First(); + Assert.AreEqual("Categories", table.Fields.First().Name); - //wb.SaveAs(@"D:\Excel Files\ForTesting\Sandbox1.xlsx"); + table.SetShowHeaderRow(false); + + Assert.AreEqual("Categories", table.Fields.First().Name); Assert.IsTrue(ws.Cell(1, 1).IsEmpty(true)); Assert.AreEqual(null, table.HeadersRow()); @@ -290,15 +316,11 @@ Assert.AreNotEqual(null, headerRow); Assert.AreEqual("Categories", headerRow.Cell(1).GetString()); - table.SetShowHeaderRow(false); ws.FirstCell().SetValue("x"); table.SetShowHeaderRow(); - //wb.SaveAs(@"D:\Excel Files\ForTesting\Sandbox2.xlsx"); - - //wb.SaveAs(@"D:\Excel Files\ForTesting\Sandbox3.xlsx"); Assert.AreEqual("x", ws.FirstCell().GetString()); Assert.AreEqual("Categories", ws.Cell("A2").GetString()); @@ -331,6 +353,15 @@ 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.SetShowHeaderRow(true); + nameAfter = tbl.Cell("B1").Value.ToString(); + Assert.AreEqual("LastNameChanged", nameAfter); } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/ExcelDocsComparerTests.cs b/ClosedXML_Tests/ExcelDocsComparerTests.cs index 3397f25..ed16735 100644 --- a/ClosedXML_Tests/ExcelDocsComparerTests.cs +++ b/ClosedXML_Tests/ExcelDocsComparerTests.cs @@ -17,7 +17,7 @@ new BasicTable().Create(left); new BasicTable().Create(right); string message; - Assert.IsTrue(ExcelDocsComparer.Compare(left, right, out message)); + Assert.IsTrue(ExcelDocsComparer.Compare(left, right, TestHelper.IsRunningOnUnix, out message)); } finally { @@ -43,7 +43,7 @@ new HelloWorld().Create(right); string message; - Assert.IsFalse(ExcelDocsComparer.Compare(left, right, out message)); + Assert.IsFalse(ExcelDocsComparer.Compare(left, right, TestHelper.IsRunningOnUnix, out message)); } finally { diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFColorScaleLowMidHigh.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFColorScaleLowMidHigh.xlsx index 640d65d..9bc4eb6 100644 --- a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFColorScaleLowMidHigh.xlsx +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFColorScaleLowMidHigh.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFColorScaleMinimumMaximum.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFColorScaleMinimumMaximum.xlsx new file mode 100644 index 0000000..ef35d8b --- /dev/null +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFColorScaleMinimumMaximum.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/AdjustToContentsWithAutoFilter.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/AdjustToContentsWithAutoFilter.xlsx new file mode 100644 index 0000000..739fe19 --- /dev/null +++ b/ClosedXML_Tests/Resource/Examples/Misc/AdjustToContentsWithAutoFilter.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/RightToLeft.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/RightToLeft.xlsx new file mode 100644 index 0000000..1faf041 --- /dev/null +++ b/ClosedXML_Tests/Resource/Examples/Misc/RightToLeft.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx new file mode 100644 index 0000000..c4025e5 --- /dev/null +++ b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/NamedRanges.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/NamedRanges.xlsx index 7087248..27bb627 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/NamedRanges.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/NamedRanges.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Styles/StyleFont.xlsx b/ClosedXML_Tests/Resource/Examples/Styles/StyleFont.xlsx index 589c8e5..04267cc 100644 --- a/ClosedXML_Tests/Resource/Examples/Styles/StyleFont.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Styles/StyleFont.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Misc/EmptyTable.xlsx b/ClosedXML_Tests/Resource/Misc/EmptyTable.xlsx new file mode 100644 index 0000000..e902376 --- /dev/null +++ b/ClosedXML_Tests/Resource/Misc/EmptyTable.xlsx Binary files differ diff --git a/ClosedXML_Tests/TestHelper.cs b/ClosedXML_Tests/TestHelper.cs index be509a2..477facf 100644 --- a/ClosedXML_Tests/TestHelper.cs +++ b/ClosedXML_Tests/TestHelper.cs @@ -1,4 +1,6 @@ using System; +using System.Collections.Generic; +using System.Linq; using System.IO; using System.Threading; using ClosedXML.Excel; @@ -19,22 +21,36 @@ //Note: Run example tests parameters public static string TestsOutputDirectory { - get { - return Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location); + get + { + return Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location); } } public const string ActualTestResultPostFix = ""; public static readonly string TestsExampleOutputDirectory = Path.Combine(TestsOutputDirectory, "Examples"); - + private const bool CompareWithResources = true; private static readonly ResourceFileExtractor _extractor = new ResourceFileExtractor(null, ".Resource.Examples."); - public static void SaveWorkbook(XLWorkbook workbook, string fileName) + public static void SaveWorkbook(XLWorkbook workbook, params string[] fileNameParts) { - workbook.SaveAs(Path.Combine(TestsOutputDirectory, fileName)); + workbook.SaveAs(Path.Combine(new string[] { TestsOutputDirectory }.Concat(fileNameParts).ToArray()), true); + } + + // Because different fonts are installed on Unix, + // the columns widths after AdjustToContents() will + // cause the tests to fail. + // Therefore we ignore the width attribute when running on Unix + public static bool IsRunningOnUnix + { + get + { + int p = (int)Environment.OSVersion.Platform; + return ((p == 4) || (p == 6) || (p == 128)); + } } public static void RunTestExample(string filePartName) @@ -44,7 +60,8 @@ Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); var example = new T(); - string filePath1 = Path.Combine(TestsExampleOutputDirectory, filePartName); + string[] pathParts = filePartName.Split(new char[] {'\\'}); + string filePath1 = Path.Combine(new List() { TestsExampleOutputDirectory }.Concat(pathParts).ToArray()); var extension = Path.GetExtension(filePath1); var directory = Path.GetDirectoryName(filePath1); @@ -57,7 +74,7 @@ var filePath2 = Path.Combine(directory, fileName); //Run test example.Create(filePath1); - new XLWorkbook(filePath1).SaveAs(filePath2); + new XLWorkbook(filePath1).SaveAs(filePath2, true); bool success = true; #pragma warning disable 162 try @@ -73,7 +90,7 @@ using (var streamActual = File.OpenRead(filePath2)) { string message; - success = ExcelDocsComparer.Compare(streamActual, streamExpected, out message); + success = ExcelDocsComparer.Compare(streamActual, streamExpected, TestHelper.IsRunningOnUnix, out message); var formattedMessage = String.Format( "Actual file '{0}' is different than the expected file '{1}'. The difference is: '{2}'", @@ -90,16 +107,24 @@ #pragma warning restore 162 } - public static void LoadFile(string filePartName) + public static string GetResourcePath(string filePartName) + { + return filePartName.Replace('\\', '.').TrimStart('.'); + } + + public static Stream GetStreamFromResource(string resourcePath) { var extractor = new ResourceFileExtractor(null, ".Resource."); + return extractor.ReadFileFromResToStream(resourcePath); + } - string resourcePath = filePartName.Replace('\\', '.').TrimStart('.'); - using (var stream = extractor.ReadFileFromResToStream(resourcePath)) + public static void LoadFile(string filePartName) + { + using (var stream = GetStreamFromResource(GetResourcePath(filePartName))) { var wb = new XLWorkbook(stream); wb.Dispose(); } } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Utils/ExcelDocsComparer.cs b/ClosedXML_Tests/Utils/ExcelDocsComparer.cs index 25dc07f..0bbeafd 100644 --- a/ClosedXML_Tests/Utils/ExcelDocsComparer.cs +++ b/ClosedXML_Tests/Utils/ExcelDocsComparer.cs @@ -6,24 +6,24 @@ { internal static class ExcelDocsComparer { - public static bool Compare(string left, string right, out string message) + public static bool Compare(string left, string right, bool stripColumnWidths, out string message) { using (FileStream leftStream = File.OpenRead(left)) { using (FileStream rightStream = File.OpenRead(right)) { - return Compare(leftStream, rightStream, out message); + return Compare(leftStream, rightStream, stripColumnWidths, out message); } } } - public static bool Compare(Stream left, Stream right, out string message) + public static bool Compare(Stream left, Stream right, bool stripColumnWidths, out string message) { using (Package leftPackage = Package.Open(left)) { using (Package rightPackage = Package.Open(right)) { - return PackageHelper.Compare(leftPackage, rightPackage, false, ExcludeMethod, out message); + return PackageHelper.Compare(leftPackage, rightPackage, false, ExcludeMethod, stripColumnWidths, out message); } } } diff --git a/ClosedXML_Tests/Utils/PackageHelper.cs b/ClosedXML_Tests/Utils/PackageHelper.cs index ec92716..33ba3e4 100644 --- a/ClosedXML_Tests/Utils/PackageHelper.cs +++ b/ClosedXML_Tests/Utils/PackageHelper.cs @@ -273,9 +273,9 @@ /// /// /// - public static bool Compare(Package left, Package right, bool compareToFirstDifference, out string message) + public static bool Compare(Package left, Package right, bool compareToFirstDifference, bool stripColumnWidths, out string message) { - return Compare(left, right, compareToFirstDifference, null, out message); + return Compare(left, right, compareToFirstDifference, null, stripColumnWidths, out message); } /// @@ -288,7 +288,7 @@ /// /// public static bool Compare(Package left, Package right, bool compareToFirstDifference, - Func excludeMethod, out string message) + Func excludeMethod, bool stripColumnWidths, out string message) { #region Check @@ -344,10 +344,16 @@ { continue; } - using (Stream oneStream = left.GetPart(pair.Uri).GetStream(FileMode.Open, FileAccess.Read)) - using (Stream otherStream = right.GetPart(pair.Uri).GetStream(FileMode.Open, FileAccess.Read)) + var leftPart = left.GetPart(pair.Uri); + var rightPart = right.GetPart(pair.Uri); + using (Stream oneStream = leftPart.GetStream(FileMode.Open, FileAccess.Read)) + using (Stream otherStream = rightPart.GetStream(FileMode.Open, FileAccess.Read)) { - if (!StreamHelper.Compare(oneStream, otherStream)) + bool stripColumnWidthsFromSheet = stripColumnWidths && + leftPart.ContentType == @"application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" && + rightPart.ContentType == @"application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"; + + if (!StreamHelper.Compare(oneStream, otherStream, stripColumnWidthsFromSheet)) { pair.Status = CompareStatus.NonEqual; if (compareToFirstDifference) diff --git a/ClosedXML_Tests/Utils/StreamHelper.cs b/ClosedXML_Tests/Utils/StreamHelper.cs index 30b8d08..05a057e 100644 --- a/ClosedXML_Tests/Utils/StreamHelper.cs +++ b/ClosedXML_Tests/Utils/StreamHelper.cs @@ -1,5 +1,8 @@ using System; +using System.Collections.Generic; using System.IO; +using System.Linq; +using System.Text.RegularExpressions; namespace ClosedXML_Tests { @@ -19,7 +22,7 @@ var bytes = new byte[iLength]; for (int i = 0; i < iLength; i++) { - bytes[i] = (byte) pStream.ReadByte(); + bytes[i] = (byte)pStream.ReadByte(); } pStream.Close(); return bytes; @@ -98,7 +101,7 @@ long rest = length; while (rest > 0) { - int len1 = streamIn.Read(buf, 0, rest >= 512 ? 512 : (int) rest); + int len1 = streamIn.Read(buf, 0, rest >= 512 ? 512 : (int)rest); streamToWrite.Write(buf, 0, len1); rest -= len1; } @@ -109,8 +112,9 @@ /// /// /// + /// /// /// - public static bool Compare(Stream one, Stream other) + public static bool Compare(Stream one, Stream other, bool stripColumnWidths) { #region Check @@ -133,9 +137,35 @@ #endregion - var stringOne = new StreamReader(one).ReadToEnd(); - var stringOther = new StreamReader(other).ReadToEnd(); + var stringOne = new StreamReader(one).ReadToEnd().StripColumnWidths(stripColumnWidths); + var stringOther = new StreamReader(other).ReadToEnd().StripColumnWidths(stripColumnWidths); return stringOne == stringOther; } + + private static Regex columnRegex = new Regex("", RegexOptions.Compiled); + private static Regex widthRegex = new Regex("width=\"\\d+(\\.\\d+)?\"\\s+", RegexOptions.Compiled); + + private static string StripColumnWidths(this string s, bool stripIt) + { + if (!stripIt) + return s; + else + { + var replacements = new Dictionary(); + + foreach (var m in columnRegex.Matches(s).OfType()) + { + var original = m.Groups[0].Value; + var replacement = widthRegex.Replace(original, ""); + replacements.Add(original, replacement); + } + + foreach (var r in replacements) + { + s = s.Replace(r.Key, r.Value); + } + return s; + } + } } } \ No newline at end of file diff --git a/README.md b/README.md index bd0789c..5d195da 100644 --- a/README.md +++ b/README.md @@ -2,18 +2,27 @@ # ClosedXML [![Build status](https://ci.appveyor.com/api/projects/status/wobbmnlbukxejjgb?svg=true)](https://ci.appveyor.com/project/Pyropace/closedxml) - we are in process of moving the project from codeplex to github - Documentations to follow +ClosedXML makes it easier for developers to create Excel 2007/2010/2013 files. It provides a nice object oriented way to manipulate the files (similar to VBA) without dealing with the hassles of XML Documents. It can be used by any .NET language like C# and Visual Basic (VB). -ClosedXML makes it easier for developers to create Excel 2007/2010 files. It provides a nice object oriented way to manipulate the files (similar to VBA) without dealing with the hassles of XML Documents. It can be used by any .NET language like C# and Visual Basic (VB). +### Install ClosedXML via NuGet + +If you want to include ClosedXML in your project, you can [install it directly from NuGet](https://www.nuget.org/packages/ClosedXML) + +To install ClosedXML, run the following command in the Package Manager Console + +``` +PM> Install-Package ClosedXML +``` ### What can you do with this? -ClosedXML allows you to create Excel 2007/2010 files without the Excel application. The typical example is creating Excel reports on a web server. +ClosedXML allows you to create Excel 2007/2010/2013 files without the Excel application. The typical example is creating Excel reports on a web server. If you've ever used the Microsoft Open XML Format SDK you know just how much code you have to write to get the same results as the following 4 lines of code. - var workbook = new XLWorkbook(); - var worksheet = workbook.Worksheets.Add("Sample Sheet"); - worksheet.Cell("A1").Value = "Hello World!"; - workbook.SaveAs("HelloWorld.xlsx"); +```c# +var workbook = new XLWorkbook(); +var worksheet = workbook.Worksheets.Add("Sample Sheet"); +worksheet.Cell("A1").Value = "Hello World!"; +workbook.SaveAs("HelloWorld.xlsx"); +```