diff --git a/.editorconfig b/.editorconfig new file mode 100644 index 0000000..d02b48a --- /dev/null +++ b/.editorconfig @@ -0,0 +1,11 @@ +root = true + +[*] +end_of_line = crlf +insert_final_newline = true +trim_trailing_whitespace = true + +# 4 space indentation +[*.cs] +indent_style = space +indent_size = 4 \ No newline at end of file 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 8b01542..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 @@ + - + + @@ -321,6 +323,9 @@ + + .editorconfig + 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..831db51 100644 --- a/ClosedXML/Excel/CalcEngine/Expression.cs +++ b/ClosedXML/Excel/CalcEngine/Expression.cs @@ -97,7 +97,7 @@ } // handle nulls - if (v == null || v is String) + if (v == null || v is string) { return 0; } 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..b115375 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/Lookup.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/Lookup.cs @@ -53,11 +53,13 @@ matching_column = range.FindColumn(c => 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() && new Expression(c.Cell(1).Value).CompareTo(lookup_value) <= 0 && 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() && new Expression(c.Cell(1).Value).CompareTo(lookup_value) <= 0) return true; else return false; @@ -96,11 +98,13 @@ matching_row = range.FindRow(r => 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() && new Expression(r.Cell(1).Value).CompareTo(lookup_value) <= 0 && 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() && 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 2a163a9..d667470 100644 --- a/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/Excel/Cells/XLCell.cs @@ -1,19 +1,22 @@ -namespace ClosedXML.Excel -{ - using System; - using System.Collections; - using System.Collections.Generic; - using System.Data; - using System.Globalization; - using System.Linq; - using System.Reflection; - using System.Text; - using System.Text.RegularExpressions; +using System; +using System.Collections; +using System.Collections.Generic; +using System.Data; +using System.Globalization; +using System.Linq; +using System.Reflection; +using System.Text; +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); @@ -241,7 +244,7 @@ var dtTest = (DateTime)Convert.ChangeType(value, typeof (DateTime)); Style.NumberFormat.NumberFormatId = dtTest.Date == dtTest ? 14 : 22; - _cellValue = dtTest.ToOADate().ToString(); + _cellValue = dtTest.ToOADate().ToInvariantString(); } else if ( value is sbyte @@ -266,7 +269,7 @@ else { _dataType = XLCellValues.Number; - _cellValue = value.ToString(); + _cellValue = ((Double)Convert.ChangeType(value, typeof (Double))).ToInvariantString(); } } else if (value is Boolean) @@ -326,7 +329,7 @@ { cValue = GetString(); } - catch + catch { cValue = String.Empty; } @@ -343,7 +346,7 @@ if (_dataType == XLCellValues.DateTime || IsDateFormat()) { double dTest; - if (Double.TryParse(cValue, out dTest)) + if (Double.TryParse(cValue, XLHelper.NumberStyle, XLHelper.ParseCulture, out dTest)) { var format = GetFormat(); return DateTime.FromOADate(dTest).ToString(format); @@ -355,7 +358,7 @@ if (_dataType == XLCellValues.Number) { double dTest; - if (Double.TryParse(cValue, out dTest)) + if (Double.TryParse(cValue, XLHelper.NumberStyle, XLHelper.ParseCulture, out dTest)) { var format = GetFormat(); return dTest.ToString(format); @@ -418,10 +421,10 @@ return cellValue != "0"; if (_dataType == XLCellValues.DateTime) - return DateTime.FromOADate(Double.Parse(cellValue)); + return DateTime.FromOADate(Double.Parse(cellValue, XLHelper.NumberStyle, XLHelper.ParseCulture)); if (_dataType == XLCellValues.Number) - return Double.Parse(cellValue); + return Double.Parse(cellValue, XLHelper.NumberStyle, XLHelper.ParseCulture); if (_dataType == XLCellValues.TimeSpan) { @@ -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++; } } @@ -813,9 +808,9 @@ DateTime dtTest; double dblTest; if (DateTime.TryParse(_cellValue, out dtTest)) - _cellValue = dtTest.ToOADate().ToString(); - else if (Double.TryParse(_cellValue, out dblTest)) - _cellValue = dblTest.ToString(); + _cellValue = dtTest.ToOADate().ToInvariantString(); + else if (Double.TryParse(_cellValue, XLHelper.NumberStyle, XLHelper.ParseCulture, out dblTest)) + _cellValue = dblTest.ToInvariantString(); else { throw new ArgumentException( @@ -841,7 +836,7 @@ { try { - _cellValue = (DateTime.FromOADate(Double.Parse(_cellValue)) - BaseDate).ToString(); + _cellValue = (DateTime.FromOADate(Double.Parse(_cellValue, XLHelper.NumberStyle, XLHelper.ParseCulture)) - BaseDate).ToString(); } catch { @@ -855,8 +850,8 @@ else if (value == XLCellValues.Number) { double dTest; - if (Double.TryParse(_cellValue, out dTest)) - _cellValue = Double.Parse(_cellValue).ToString(); + if (Double.TryParse(_cellValue, NumberStyles.AllowDecimalPoint, CultureInfo.InvariantCulture, out dTest)) + _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(); } } @@ -1586,6 +1581,9 @@ private bool SetEnumerable(object collectionObject) { + // IXLRichText implements IEnumerable, but we don't want to handle this here. + if ((collectionObject as IXLRichText) != null) return false; + var asEnumerable = collectionObject as IEnumerable; return InsertData(asEnumerable) != null; } @@ -1620,6 +1618,8 @@ val = string.Empty; else if (value is DateTime) val = ((DateTime)value).ToString("o"); + else if (value is double) + val = ((double)value).ToInvariantString(); else val = value.ToString(); _richText = null; @@ -1645,7 +1645,7 @@ if (val.Contains(Environment.NewLine) && !style.Alignment.WrapText) Style.Alignment.WrapText = true; } - else if (value is TimeSpan || (!Double.TryParse(val, out dTest) && TimeSpan.TryParse(val, out tsTest))) + else if (value is TimeSpan || (!Double.TryParse(val, XLHelper.NumberStyle, XLHelper.ParseCulture, out dTest) && TimeSpan.TryParse(val, out tsTest))) { if (!(value is TimeSpan) && TimeSpan.TryParse(val, out tsTest)) val = tsTest.ToString(); @@ -1654,7 +1654,7 @@ if (style.NumberFormat.Format == String.Empty && style.NumberFormat.NumberFormatId == 0) Style.NumberFormat.NumberFormatId = 46; } - else if (val.Trim() != "NaN" && Double.TryParse(val, out dTest)) + else if (val.Trim() != "NaN" && Double.TryParse(val, XLHelper.NumberStyle, XLHelper.ParseCulture, out dTest)) _dataType = XLCellValues.Number; else if (DateTime.TryParse(val, out dtTest) && dtTest >= BaseDate) { @@ -1667,14 +1667,14 @@ DateTime forMillis; if (value is DateTime && (forMillis = (DateTime)value).Millisecond > 0) { - val = forMillis.ToOADate().ToString(); + val = forMillis.ToOADate().ToInvariantString(); } else { - val = dtTest.ToOADate().ToString(); + val = dtTest.ToOADate().ToInvariantString(); } } - + } else if (Boolean.TryParse(val, out bTest)) { @@ -1958,14 +1958,15 @@ } - public IXLCell CopyFrom(XLCell otherCell, Boolean copyDataValidations) + public IXLCell CopyFrom(IXLCell otherCell, Boolean copyDataValidations) { - var source = otherCell; - CopyValues(otherCell); + var castedOtherCell = otherCell as XLCell; // To expose GetFormulaR1C1, etc + var source = castedOtherCell; + CopyValues(castedOtherCell); SetStyle(source._style ?? source.Worksheet.Workbook.GetStyleById(source._styleCacheId)); - var conditionalFormats = otherCell.Worksheet.ConditionalFormats.Where(c => c.Range.Contains(otherCell)).ToList(); + var conditionalFormats = castedOtherCell.Worksheet.ConditionalFormats.Where(c => c.Range.Contains(castedOtherCell)).ToList(); foreach (var cf in conditionalFormats) { var c = new XLConditionalFormat(cf as XLConditionalFormat) {Range = AsRange()}; @@ -1976,7 +1977,7 @@ var f = v.Value; if (v.IsFormula) { - var r1c1 = otherCell.GetFormulaR1C1(f); + var r1c1 = castedOtherCell.GetFormulaR1C1(f); f = GetFormulaA1(r1c1); } @@ -1991,8 +1992,8 @@ { var eventTracking = Worksheet.EventTrackingEnabled; Worksheet.EventTrackingEnabled = false; - if (otherCell.HasDataValidation) - CopyDataValidation(otherCell, otherCell.DataValidation); + if (castedOtherCell.HasDataValidation) + CopyDataValidation(castedOtherCell, castedOtherCell.DataValidation); else if (HasDataValidation) { using (var asRange = AsRange()) @@ -2486,10 +2487,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 @@ -2600,4 +2608,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/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..3484ed6 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; @@ -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/Ranges/XLRangeRow.cs b/ClosedXML/Excel/Ranges/XLRangeRow.cs index 802342f..df4e9da 100644 --- a/ClosedXML/Excel/Ranges/XLRangeRow.cs +++ b/ClosedXML/Excel/Ranges/XLRangeRow.cs @@ -256,7 +256,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); 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/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/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 bed27ac..21c7cf9 100644 --- a/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/Excel/XLWorkbook_Load.cs @@ -5,6 +5,7 @@ using System.Globalization; using System.IO; using System.Linq; +using ClosedXML.Utils; using DocumentFormat.OpenXml; using DocumentFormat.OpenXml.Packaging; using DocumentFormat.OpenXml.Spreadsheet; @@ -153,7 +154,7 @@ 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()) @@ -304,7 +305,7 @@ } else xlTable.ShowAutoFilter = false; - + if (xlTable.ShowTotalsRow) { @@ -342,11 +343,11 @@ // **** MAYBE FUTURE SHAPE SIZE SUPPORT XDocument xdoc = GetCommentVmlFile(wsPart); - + 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; @@ -360,9 +361,9 @@ LoadFont(runProperties, rt); } - + XElement shape = GetCommentShape(xdoc); - + LoadShapeProperties(xlComment, shape); var clientData = shape.Elements().First(e => e.Name.LocalName == "ClientData"); @@ -378,7 +379,7 @@ //var insetmode = (string)shape.Attributes().First(a=> a.Name.LocalName == "insetmode"); //xlComment.Style.Margins.Automatic = insetmode != null && insetmode.Equals("auto"); - + shape.Remove(); } } @@ -466,7 +467,7 @@ { return XLColor.FromHtml(color); } - + } private void LoadColorsAndLines(IXLDrawing drawing, XElement shape) @@ -489,7 +490,7 @@ { 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); @@ -504,7 +505,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 +523,7 @@ else drawing.Style.ColorsAndLines.LineDash = XLDashStyle.SquareDot; } - else + else { switch (dashStyleVal) { @@ -711,7 +712,7 @@ return Double.Parse(value.Replace(knownUnit.Key, String.Empty), CultureInfo.InvariantCulture) * knownUnit.Value; } - + private void LoadDefinedNames(Workbook workbook) { @@ -890,7 +891,7 @@ { if (!XLHelper.IsNullOrWhiteSpace(cell.CellValue.Text)) { - var sharedString = sharedStrings[Int32.Parse(cell.CellValue.Text)]; + var sharedString = sharedStrings[Int32.Parse(cell.CellValue.Text, XLHelper.NumberStyle, XLHelper.ParseCulture)]; var runs = sharedString.Elements(); var phoneticRuns = sharedString.Elements(); @@ -911,9 +912,9 @@ if (!hasRuns) hasRuns = true; } - + if(!hasRuns) - xlCell._cellValue = sharedString.Text.InnerText; + xlCell._cellValue = XmlEncoder.DecodeString(sharedString.Text.InnerText); #region Load PhoneticProperties @@ -950,7 +951,7 @@ else if (cell.DataType == CellValues.Date) { if (!XLHelper.IsNullOrWhiteSpace(cell.CellValue.Text)) - xlCell._cellValue = Double.Parse(cell.CellValue.Text, CultureInfo.InvariantCulture).ToString(); + xlCell._cellValue = Double.Parse(cell.CellValue.Text, XLHelper.NumberStyle, XLHelper.ParseCulture).ToInvariantString(); xlCell._dataType = XLCellValues.DateTime; } else if (cell.DataType == CellValues.Boolean) @@ -961,7 +962,7 @@ else if (cell.DataType == CellValues.Number) { if (!XLHelper.IsNullOrWhiteSpace(cell.CellValue.Text)) - xlCell._cellValue = Double.Parse(cell.CellValue.Text, CultureInfo.InvariantCulture).ToString(); + xlCell._cellValue = Double.Parse(cell.CellValue.Text, XLHelper.NumberStyle, XLHelper.ParseCulture).ToInvariantString(); if (s == null) { xlCell._dataType = XLCellValues.Number; @@ -974,7 +975,7 @@ else xlCell._dataType = XLCellValues.Number; } - + } } else if (cell.CellValue != null) @@ -987,7 +988,7 @@ { var numberFormatId = ((CellFormat) (s.CellFormats).ElementAt(styleIndex)).NumberFormatId; if (!XLHelper.IsNullOrWhiteSpace(cell.CellValue.Text)) - xlCell._cellValue = Double.Parse(cell.CellValue.Text, CultureInfo.InvariantCulture).ToString(); + xlCell._cellValue = Double.Parse(cell.CellValue.Text, CultureInfo.InvariantCulture).ToInvariantString(); if (s.NumberingFormats != null && s.NumberingFormats.Any(nf => ((NumberingFormat) nf).NumberFormatId.Value == numberFormatId)) { @@ -1039,7 +1040,7 @@ 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 ) @@ -1053,7 +1054,7 @@ } } - + private void LoadFill(Fill fillSource, IXLFill fill) { @@ -1111,7 +1112,7 @@ } var verticalTextAlignment = fontSource.Elements().FirstOrDefault(); - + if (verticalTextAlignment == null) return; fontBase.VerticalAlignment = verticalTextAlignment.Val != null ? verticalTextAlignment.Val.Value.ToClosedXml() : XLFontVerticalTextAlignmentValues.Baseline; @@ -1125,7 +1126,7 @@ { 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 @@ -1492,7 +1493,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 +1505,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 +1516,7 @@ ws.ConditionalFormats.Add(conditionalFormat); } } - + } private static XLFormula GetFormula(String value) @@ -1523,7 +1524,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 +1535,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); @@ -1731,6 +1734,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) @@ -1983,10 +1987,10 @@ } } - + if (!UInt32HasValue(cellFormat.NumberFormatId)) return; - + var numberFormatId = cellFormat.NumberFormatId; string formatCode = String.Empty; @@ -2024,4 +2028,4 @@ return false; } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index 0ad3737..8dd7f14 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); } } @@ -159,8 +172,8 @@ } } - // Get the CalculationChainPart - //Note: An instance of this part type contains an ordered set of references to all cells in all worksheets in the + // Get the CalculationChainPart + //Note: An instance of this part type contains an ordered set of references to all cells in all worksheets in the //workbook whose value is calculated from any formula CalculationChainPart calChainPart; @@ -194,7 +207,7 @@ var workbookPart = document.WorkbookPart ?? document.AddWorkbookPart(); var worksheets = WorksheetsInternal; - + var partsToRemove = workbookPart.Parts.Where(s => worksheets.Deleted.Contains(s.RelationshipId)).ToList(); @@ -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) @@ -538,7 +555,7 @@ { workbook.WorkbookProtection = null; } - + if (workbook.BookViews == null) workbook.BookViews = new BookViews(); @@ -823,7 +840,7 @@ EndingBaseIndex = (UInt32)p.End }; - var text = new Text {Text = p.Text}; + var text = new Text { Text = p.Text }; if (p.Text.PreserveSpaces()) text.Space = SpaceProcessingModeValues.Preserve; @@ -867,7 +884,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 +1656,7 @@ { var vTDouble1 = new VTDouble { - Text = p.GetValue().ToString(CultureInfo.InvariantCulture) + Text = p.GetValue().ToInvariantString() }; customDocumentProperty.AppendChild(vTDouble1); } @@ -1778,23 +1795,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); pivotTablePart.AddPart(pivotTableCacheDefinitionPart, context.RelIdGenerator.GetNext(RelType.Workbook)); } @@ -1888,12 +1914,12 @@ } // Generates content of pivotTablePart - private static void GeneratePivotTablePartContent(PivotTablePart pivotTablePart1, IXLPivotTable pt) + private static void GeneratePivotTablePartContent(PivotTablePart pivotTablePart1, IXLPivotTable pt, uint cacheId) { var pivotTableDefinition = new PivotTableDefinition { Name = pt.Name, - CacheId = 0U, + CacheId = cacheId, DataCaption = "Values", MergeItem = GetBooleanValue(pt.MergeAndCenterWithLabels, true), Indent = Convert.ToUInt32(pt.RowLabelIndent), @@ -2380,10 +2406,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 +2465,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:"); @@ -3420,6 +3446,10 @@ else sheetView.TabSelected = null; + if (xlWorksheet.RightToLeft) + sheetView.RightToLeft = true; + else + sheetView.RightToLeft = null; if (xlWorksheet.ShowFormulas) sheetView.ShowFormulas = true; @@ -3908,7 +3938,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 +3946,7 @@ if (!XLHelper.IsNullOrWhiteSpace(opCell.InnerText)) { var cellValue = new CellValue(); - cellValue.Text = Double.Parse(opCell.InnerText).ToString(CultureInfo.InvariantCulture); + cellValue.Text = Double.Parse(opCell.InnerText, XLHelper.NumberStyle, XLHelper.ParseCulture).ToInvariantString(); cell.CellValue = cellValue; } } @@ -4064,7 +4094,7 @@ } worksheetPart.Worksheet.InsertAfter(conditionalFormatting, previousElement); previousElement = conditionalFormatting; - cm.SetElement(XLWSContentManager.XLWSContents.ConditionalFormatting, conditionalFormatting); + cm.SetElement(XLWSContentManager.XLWSContents.ConditionalFormatting, conditionalFormatting); } } @@ -4636,4 +4666,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/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 d5f704d..8729fef 100644 --- a/ClosedXML/XLHelper.cs +++ b/ClosedXML/XLHelper.cs @@ -22,9 +22,10 @@ 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 | NumberStyles.AllowExponent; + internal static readonly CultureInfo ParseCulture = CultureInfo.InvariantCulture; internal static readonly Regex A1SimpleRegex = new Regex( @"\A" @@ -68,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; } @@ -164,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 @@ -299,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 9cd5941..9bdf8de 100644 --- a/ClosedXML_Examples/ClosedXML_Examples.csproj +++ b/ClosedXML_Examples/ClosedXML_Examples.csproj @@ -71,7 +71,10 @@ + + + @@ -153,6 +156,9 @@ + + .editorconfig + 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 956a13d..694109c 100644 --- a/ClosedXML_Examples/Creating/CreateFiles.cs +++ b/ClosedXML_Examples/Creating/CreateFiles.cs @@ -27,6 +27,7 @@ new InsertColumns().Create(Path.Combine(path, "InsertColumns.xlsx")); new ColumnCollection().Create(Path.Combine(path, "ColumnCollection.xlsx")); new DataTypes().Create(Path.Combine(path, "DataTypes.xlsx")); + new DataTypesUnderDifferentCulture().Create(Path.Combine(path, "DataTypesUnderDifferentCulture.xlsx")); new MultipleSheets().Create(Path.Combine(path, "MultipleSheets.xlsx")); new RowCollection().Create(Path.Combine(path, "RowCollection.xlsx")); new DefiningRanges().Create(Path.Combine(path, "DefiningRanges.xlsx")); @@ -51,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")); @@ -66,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")); @@ -86,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/DataTypesUnderDifferentCulture.cs b/ClosedXML_Examples/Misc/DataTypesUnderDifferentCulture.cs new file mode 100644 index 0000000..4b77144 --- /dev/null +++ b/ClosedXML_Examples/Misc/DataTypesUnderDifferentCulture.cs @@ -0,0 +1,35 @@ +using System; +using ClosedXML.Excel; +using System.Threading; +using System.Globalization; +using System.IO; + +namespace ClosedXML_Examples.Misc +{ + public class DataTypesUnderDifferentCulture : IXLExample + { + public void Create(string filePath) + { + var backupCulture = Thread.CurrentThread.CurrentCulture; + + // Set thread culture to French, which should format numbers using decimal COMMA + Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture("fr-FR"); + + string tempFile = ExampleHelper.GetTempFilePath(filePath); + try + { + new DataTypes().Create(tempFile); + var workbook = new XLWorkbook(tempFile); + workbook.SaveAs(filePath); + } + finally + { + Thread.CurrentThread.CurrentCulture = backupCulture; + if (File.Exists(tempFile)) + { + File.Delete(tempFile); + } + } + } + } +} 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..4649a17 100644 --- a/ClosedXML_Examples/PivotTables/PivotTables.cs +++ b/ClosedXML_Examples/PivotTables/PivotTables.cs @@ -1,29 +1,35 @@ -using System; -using ClosedXML.Excel; +using ClosedXML.Excel; +using System; namespace ClosedXML_Examples { - public class PivotTables + public class PivotTables : IXLExample { 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 pivotTable = ws.Range("A1:B4").CreatePivotTable(ws.Cell("D1")); - //pivotTable.RowLabels.Add("Category"); - //pivotTable.Values.Add("Number") - // .ShowAsPctFrom("Category").And("A") - // .NumberFormat.Format = "0%"; + var wsData = wb.Worksheets.Add("Data"); + wsData.Cell("A1").Value = "Category"; + wsData.Cell("A2").Value = "A"; + wsData.Cell("A3").Value = "B"; + wsData.Cell("A4").Value = "B"; + wsData.Cell("B1").Value = "Number"; + wsData.Cell("B2").Value = 100; + wsData.Cell("B3").Value = 150; + wsData.Cell("B4").Value = 75; + var source = wsData.Range("A1:B4"); + + for (int i = 1; i <= 3; i++) + { + var name = "PT" + i; + var wsPT = wb.Worksheets.Add(name); + var pt = wsPT.PivotTables.AddNew(name, wsPT.Cell("A1"), source); + pt.RowLabels.Add("Category"); + pt.Values.Add("Number") + .ShowAsPctFrom("Category").And("A") + .NumberFormat.Format = "0%"; + } wb.SaveAs(filePath); } 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_Examples/Styles/UsingRichText.cs b/ClosedXML_Examples/Styles/UsingRichText.cs index af0ad36..3ceed77 100644 --- a/ClosedXML_Examples/Styles/UsingRichText.cs +++ b/ClosedXML_Examples/Styles/UsingRichText.cs @@ -91,7 +91,21 @@ if(richText.Bold) ws.Cell(3, 2).Value = String.Format("\"{0}\" is Bold.", richText.Text); } - + + + // Now we'll build a cell with rich text, and some other styles + cell = ws.Cell(5, 1); + + // Add the text parts + cell.RichText.AddText("Some").SetFontColor(XLColor.Green); + cell.RichText.AddText(" rich text ").SetFontColor(XLColor.Blue).SetBold(); + cell.RichText.AddText("with a gray background").SetItalic(); + + cell.Style.Fill.SetBackgroundColor(XLColor.Gray); + + ws.Cell(5, 2).Value = cell.RichText; // Should copy only rich text, but not background + + ws.Columns().AdjustToContents(); wb.SaveAs(filePath); diff --git a/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj b/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj index f39ac33..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 @@ -824,6 +830,9 @@ + + .editorconfig + 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_Sandbox/ClosedXML_Sandbox.csproj b/ClosedXML_Sandbox/ClosedXML_Sandbox.csproj index 6c9f064..3581c95 100644 --- a/ClosedXML_Sandbox/ClosedXML_Sandbox.csproj +++ b/ClosedXML_Sandbox/ClosedXML_Sandbox.csproj @@ -58,6 +58,9 @@ + + .editorconfig + diff --git a/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML_Tests/ClosedXML_Tests.csproj index 0d280d1..f1c0f09 100644 --- a/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -70,12 +70,14 @@ - + + + @@ -138,11 +140,13 @@ + + @@ -214,6 +218,9 @@ + + .editorconfig + @@ -225,6 +232,7 @@ + @@ -241,10 +249,12 @@ - + + + @@ -255,4 +265,4 @@ --> - \ No newline at end of file + 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 2433aab..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"); @@ -74,6 +80,12 @@ } [Test] + public void DataTypesUnderDifferentCulture() + { + TestHelper.RunTestExample(@"Misc\DataTypesUnderDifferentCulture.xlsx"); + } + + [Test] public void DataValidation() { TestHelper.RunTestExample(@"Misc\DataValidation.xlsx"); @@ -146,6 +158,12 @@ } [Test] + public void RightToLeft() + { + TestHelper.RunTestExample(@"Misc\RightToLeft.xlsx"); + } + + [Test] public void SheetProtection() { TestHelper.RunTestExample(@"Misc\SheetProtection.xlsx"); @@ -175,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..2bb46a3 --- /dev/null +++ b/ClosedXML_Tests/Excel/CalcEngine/InformationTests.cs @@ -0,0 +1,331 @@ +using System; +using System.Runtime.InteropServices; +using ClosedXML.Excel; +using NUnit.Framework; + +namespace ClosedXML_Tests.Excel.CalcEngine +{ + + [TestFixture] + public class InformationTests + { + + #region IsBlank Tests + [Test] + public void IsBlank_Single_true() + { + var ws = new XLWorkbook().AddWorksheet("Sheet"); + var actual = ws.Evaluate("=IsBlank(A1)"); + Assert.AreEqual(true, actual); + } + + [Test] + public void IsBlank_Single_false() + { + var ws = new XLWorkbook().AddWorksheet("Sheet"); + ws.Cell("A1").Value = " "; + var actual = ws.Evaluate("=IsBlank(A1)"); + Assert.AreEqual(false, actual); + } + + [Test] + public void IsBlank_MultipleAllEmpty_true() + { + var ws = new XLWorkbook().AddWorksheet("Sheet"); + var actual = ws.Evaluate("=IsBlank(A1:A3)"); + Assert.AreEqual(true,actual); + + } + + [Test] + public void IsBlank_MultipleAllFill_false() + { + var ws = new XLWorkbook().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() + { + var ws = new XLWorkbook().AddWorksheet("Sheet"); + ws.Cell("A1").Value = "1"; + ws.Cell("A3").Value = "1"; + var actual = ws.Evaluate("=IsBlank(A1:A3)"); + Assert.AreEqual(false, actual); + } + #endregion + + #region IsEven Tests + + [Test] + public void IsEven_Single_False() + { + var ws = new XLWorkbook().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() + { + var ws = new XLWorkbook().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 + + #region IsLogical Tests + + [Test] + public void IsLogical_Simple_True() + { + var ws = new XLWorkbook().AddWorksheet("Sheet"); + + ws.Cell("A1").Value = true; + + var actual = ws.Evaluate("=IsLogical(A1)"); + Assert.AreEqual(true, actual); + } + + [Test] + public void IsLogical_Simpe_False() + { + var ws = new XLWorkbook().AddWorksheet("Sheet"); + + ws.Cell("A1").Value = 123; + + var actual = ws.Evaluate("=IsLogical(A1)"); + Assert.AreEqual(false, actual); + } + #endregion + + #region IsNotText Tests + + [Test] + public void IsNotText_Simple_true() + { + var ws = new XLWorkbook().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 = 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); + } + + [Test] + public void IsNotText_Simple_false() + { + var ws = new XLWorkbook().AddWorksheet("Sheet"); + ws.Cell("A1").Value = "asd"; + var actual = ws.Evaluate("=IsNonText(A1)"); + Assert.AreEqual(false, actual); + } + + #endregion + + #region IsNumber Tests + + [Test] + public void IsNumber_Simple_true() + { + var ws = new XLWorkbook().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); + } + + [Test] + public void IsNumber_Simple_false() + { + var ws = new XLWorkbook().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); + } + #endregion + + #region IsOdd Test + + [Test] + public void IsOdd_Simple_true() + { + var ws = new XLWorkbook().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); + } + + [Test] + public void IsOdd_Simple_false() + { + var ws = new XLWorkbook().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); + } + #endregion + + #region IsText Tests + [Test] + public void IsText_Simple_true() + { + var ws = new XLWorkbook().AddWorksheet("Sheet"); + + ws.Cell("A1").Value = "asd"; + + var actual = ws.Evaluate("=IsText(A1)"); + Assert.AreEqual(true, actual); + } + + [Test] + public void IsText_Simple_false() + { + var ws = new XLWorkbook().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 = 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); + } + #endregion + + #region N Tests + + [Test] + public void N_Date_SerialNumber() + { + var ws = new XLWorkbook().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_Number_Number() + { + var ws = new XLWorkbook().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() + { + var ws = new XLWorkbook().AddWorksheet("Sheet"); + ws.Cell("A1").Value = "asd"; + var actual = ws.Evaluate("=N(A1)"); + Assert.AreEqual(0, actual); + } + + [Test] + public void N_True_One() + { + var ws = new XLWorkbook().AddWorksheet("Sheet"); + ws.Cell("A1").Value = true; + var actual = ws.Evaluate("=N(A1)"); + Assert.AreEqual(1, actual); + } + + [Test] + public void N_False_Zero() + { + var ws = new XLWorkbook().AddWorksheet("Sheet"); + ws.Cell("A1").Value = false; + var actual = ws.Evaluate("=N(A1)"); + Assert.AreEqual(0, actual); + } + #endregion + + + } +} \ No newline at end of file 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/LookupTests.cs b/ClosedXML_Tests/Excel/CalcEngine/LookupTests.cs index 5d232f6..6938b37 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/LookupTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/LookupTests.cs @@ -23,51 +23,55 @@ 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 +79,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 +87,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 dc452c7..4297a99 100644 --- a/ClosedXML_Tests/Excel/Columns/ColumnTests.cs +++ b/ClosedXML_Tests/Excel/Columns/ColumnTests.cs @@ -108,7 +108,7 @@ IXLColumn columnIns = ws.Column(2).InsertColumnsBefore(1).First(); string outputPath = Path.Combine(TestHelper.TestsOutputDirectory, "ForTesting", "Sandbox.xlsx"); - wb.SaveAs(outputPath); + 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/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/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/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/DataTypesUnderDifferentCulture.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/DataTypesUnderDifferentCulture.xlsx new file mode 100644 index 0000000..ca68eee --- /dev/null +++ b/ClosedXML_Tests/Resource/Examples/Misc/DataTypesUnderDifferentCulture.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..43136a8 --- /dev/null +++ b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.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/Examples/Styles/UsingRichText.xlsx b/ClosedXML_Tests/Resource/Examples/Styles/UsingRichText.xlsx index 8fd19ce..c44ada1 100644 --- a/ClosedXML_Tests/Resource/Examples/Styles/UsingRichText.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Styles/UsingRichText.xlsx Binary files differ diff --git a/ClosedXML_Tests/TestHelper.cs b/ClosedXML_Tests/TestHelper.cs index 6aa39fe..9f67d25 100644 --- a/ClosedXML_Tests/TestHelper.cs +++ b/ClosedXML_Tests/TestHelper.cs @@ -1,6 +1,4 @@ using System; -using System.Collections.Generic; -using System.Linq; using System.IO; using System.Threading; using ClosedXML.Excel; @@ -21,22 +19,23 @@ //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, params string[] fileNameParts) { - workbook.SaveAs(Path.Combine(new string[] { TestsOutputDirectory }.Concat(fileNameParts).ToArray())); + workbook.SaveAs(Path.Combine(new string[] { TestsOutputDirectory }.Concat(fileNameParts).ToArray()), true); } // Because different fonts are installed on Unix, @@ -73,7 +72,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 @@ -118,4 +117,4 @@ } } } -} \ 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"); +```