diff --git a/.github/ISSUE_TEMPLATE.md b/.github/ISSUE_TEMPLATE.md index cfa55f4..d742a6d 100644 --- a/.github/ISSUE_TEMPLATE.md +++ b/.github/ISSUE_TEMPLATE.md @@ -12,3 +12,11 @@ **Did this work in previous versions of our tool? Which versions?** - [ ] I attached a sample spreadsheet. (You can drag files on to this issue) + +**Code to reproduce problem:** +```c# +public void Main() +{ + // Where possible, post full code to reproduce your issue. +} +``` diff --git a/.github/PULL_REQUEST_TEMPLATE.md b/.github/PULL_REQUEST_TEMPLATE.md index 891ee85..6856874 100644 --- a/.github/PULL_REQUEST_TEMPLATE.md +++ b/.github/PULL_REQUEST_TEMPLATE.md @@ -1,13 +1,14 @@ -Fixes # . -Changes proposed in this pull request: - - - - - - - -How did I test this code: - - - - - - - -- [ ] I attached a sample spreadsheet. (You can drag files on to this pull request) +#### What's this PR do? +#### Where should the reviewer start? +#### How should this be manually tested? +#### Any background context you want to provide? + +#### Screenshots (if appropriate) +#### Questions: +- Is there a blog post? +- Does the knowledge base need an update? +- Does this add new (C#) dependencies? + +- [ ] C# Code Review: @csreviewer +- [ ] Test Automation Review: @csreviewer \ No newline at end of file diff --git a/ClosedXML.sln b/ClosedXML.sln index 4104ff4..08ba5f8 100644 --- a/ClosedXML.sln +++ b/ClosedXML.sln @@ -7,13 +7,12 @@ EndProject Project("{2150E333-8FDC-42A3-9474-1A3956D46DE8}") = "Solution Items", "Solution Items", "{5C94E22C-85AA-48FD-B082-CF929FFC6C31}" ProjectSection(SolutionItems) = preProject + appveyor.yml = appveyor.yml ClosedXML.vsmdi = ClosedXML.vsmdi EndProjectSection EndProject Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "ClosedXML_Examples", "ClosedXML_Examples\ClosedXML_Examples.csproj", "{03A518D0-1CB7-488E-861C-C4E782B27A46}" EndProject -Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "ClosedXML_Net3.5", "ClosedXML_Net3.5\ClosedXML_Net3.5.csproj", "{5F43B12B-A900-40C6-9924-A0C0B032F791}" -EndProject Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "ClosedXML", "ClosedXML\ClosedXML.csproj", "{BD5E6BFE-E837-4A35-BCA9-39667D873A20}" EndProject Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "ClosedXML_Tests", "ClosedXML_Tests\ClosedXML_Tests.csproj", "{09B066ED-E4A7-4545-A1A4-FF03DD524BDF}" @@ -38,10 +37,6 @@ {03A518D0-1CB7-488E-861C-C4E782B27A46}.Debug|Any CPU.Build.0 = Debug|Any CPU {03A518D0-1CB7-488E-861C-C4E782B27A46}.Release|Any CPU.ActiveCfg = Release|Any CPU {03A518D0-1CB7-488E-861C-C4E782B27A46}.Release|Any CPU.Build.0 = Release|Any CPU - {5F43B12B-A900-40C6-9924-A0C0B032F791}.Debug|Any CPU.ActiveCfg = Debug|Any CPU - {5F43B12B-A900-40C6-9924-A0C0B032F791}.Debug|Any CPU.Build.0 = Debug|Any CPU - {5F43B12B-A900-40C6-9924-A0C0B032F791}.Release|Any CPU.ActiveCfg = Release|Any CPU - {5F43B12B-A900-40C6-9924-A0C0B032F791}.Release|Any CPU.Build.0 = Release|Any CPU {BD5E6BFE-E837-4A35-BCA9-39667D873A20}.Debug|Any CPU.ActiveCfg = Debug|Any CPU {BD5E6BFE-E837-4A35-BCA9-39667D873A20}.Debug|Any CPU.Build.0 = Debug|Any CPU {BD5E6BFE-E837-4A35-BCA9-39667D873A20}.Release|Any CPU.ActiveCfg = Release|Any CPU diff --git a/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML.csproj index 39cf027..3da4ce3 100644 --- a/ClosedXML/ClosedXML.csproj +++ b/ClosedXML/ClosedXML.csproj @@ -10,9 +10,8 @@ Properties ClosedXML ClosedXML - v4.0 + v4.5.2 512 - Client ..\ true @@ -26,6 +25,7 @@ prompt 4 1591 + false pdbonly @@ -37,6 +37,7 @@ 4 bin\Release\ClosedXML.xml 1591 + false true @@ -45,9 +46,9 @@ ClosedXML.snk - + + ..\packages\DocumentFormat.OpenXml.2.7.1\lib\net45\DocumentFormat.OpenXml.dll True - ..\packages\DocumentFormat.OpenXml.2.5\lib\DocumentFormat.OpenXml.dll ..\packages\FastMember.Signed.1.1.0\lib\net40\FastMember.Signed.dll diff --git a/ClosedXML/Excel/CalcEngine/CalcEngine.cs b/ClosedXML/Excel/CalcEngine/CalcEngine.cs index 514a975..66c8d5f 100644 --- a/ClosedXML/Excel/CalcEngine/CalcEngine.cs +++ b/ClosedXML/Excel/CalcEngine/CalcEngine.cs @@ -1,50 +1,51 @@ -using System; -using System.Reflection; -using System.Collections.Generic; -using System.ComponentModel; -using System.Globalization; -using System.Diagnostics; -using System.Text; -using System.Text.RegularExpressions; -using ClosedXML.Excel.CalcEngine; using ClosedXML.Excel.CalcEngine.Functions; +using System; +using System.Collections.Generic; +using System.Globalization; +using System.Linq; -namespace ClosedXML.Excel.CalcEngine { +namespace ClosedXML.Excel.CalcEngine +{ /// - /// CalcEngine parses strings and returns Expression objects that can + /// CalcEngine parses strings and returns Expression objects that can /// be evaluated. - /// + /// /// /// This class has three extensibility points: /// Use the DataContext property to add an object's properties to the engine scope. /// Use the RegisterFunction method to define custom functions. /// Override the GetExternalObject method to add arbitrary variables to the engine scope. /// - internal class CalcEngine { + 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 - Dictionary _tkTbl; // table with tokens (+, -, 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 - ExpressionCache _cache; // cache with parsed expressions - CultureInfo _ci; // culture info used to parse numbers/dates - char _decimal, _listSep, _percent; // localized decimal separator, list separator, percent sign + private string _expr; // expression being parsed - #endregion + private int _len; // length of the expression being parsed + private int _ptr; // current pointer into expression + private char[] _idChars; // valid characters in identifiers (besides alpha and digits) + private Token _token; // current token being parsed + private Dictionary _tkTbl; // table with tokens (+, -, etc) + private Dictionary _fnTbl; // table with constants and functions (pi, sin, etc) + private Dictionary _vars; // table with variables + private object _dataContext; // object with properties + private bool _optimize; // optimize expressions when parsing + private ExpressionCache _cache; // cache with parsed expressions + private CultureInfo _ci; // culture info used to parse numbers/dates + private char _decimal, _listSep, _percent; // localized decimal separator, list separator, percent sign + + #endregion ** fields //--------------------------------------------------------------------------- + #region ** ctor - public CalcEngine() { + public CalcEngine() + { CultureInfo = CultureInfo.InvariantCulture; _tkTbl = GetSymbolTable(); _fnTbl = GetFunctionTable(); @@ -56,9 +57,10 @@ #endif } - #endregion + #endregion ** ctor //--------------------------------------------------------------------------- + #region ** object model /// @@ -66,14 +68,16 @@ /// /// String to parse. /// An object that can be evaluated. - public Expression Parse(string expression) { + 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++; } @@ -81,18 +85,21 @@ var expr = ParseExpression(); // check for errors - if (_token.ID != TKID.END) { + if (_token.ID != TKID.END) + { Throw(); } // optimize expression - if (_optimize) { + if (_optimize) + { expr = expr.Optimize(); } // done return expr; } + /// /// Evaluates a string. /// @@ -104,35 +111,43 @@ /// 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(); } + /// /// 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; } } } + /// /// 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; } } + /// /// Gets or sets a string that specifies special characters that are valid for identifiers. /// @@ -142,10 +157,12 @@ /// additional valid characters such as ':' or '!' (used in Excel range references /// for example). /// - public string IdentifierChars { + public char[] IdentifierChars + { get { return _idChars; } set { _idChars = value; } } + /// /// Registers a function that can be evaluated by this . /// @@ -153,18 +170,22 @@ /// 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)); } + /// /// Registers a function that can be evaluated by this . /// /// 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); } + /// /// Gets an external object based on an identifier. /// @@ -174,9 +195,11 @@ /// 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; } + /// /// Gets or sets the DataContext for this . /// @@ -185,28 +208,36 @@ /// 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]; @@ -215,14 +246,17 @@ } } - #endregion + #endregion ** object model //--------------------------------------------------------------------------- + #region ** token/keyword tables // build/get static token table - Dictionary GetSymbolTable() { - if (_tkTbl == null) { + private Dictionary GetSymbolTable() + { + if (_tkTbl == null) + { _tkTbl = new Dictionary(); AddToken('&', TKID.CONCAT, TKTYPE.ADDSUB); AddToken('+', TKID.ADD, TKTYPE.ADDSUB); @@ -247,14 +281,18 @@ } return _tkTbl; } - void AddToken(object symbol, TKID id, TKTYPE type) { + + private 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) { + private Dictionary GetFunctionTable() + { + if (_fnTbl == null) + { // create table _fnTbl = new Dictionary(StringComparer.InvariantCultureIgnoreCase); @@ -270,18 +308,23 @@ return _fnTbl; } - #endregion + #endregion ** token/keyword tables //--------------------------------------------------------------------------- + #region ** private stuff - Expression ParseExpression() { + private Expression ParseExpression() + { GetToken(); return ParseCompare(); } - Expression ParseCompare() { + + private Expression ParseCompare() + { var x = ParseAddSub(); - while (_token.Type == TKTYPE.COMPARE) { + while (_token.Type == TKTYPE.COMPARE) + { var t = _token; GetToken(); var exprArg = ParseAddSub(); @@ -289,9 +332,12 @@ } return x; } - Expression ParseAddSub() { + + private Expression ParseAddSub() + { var x = ParseMulDiv(); - while (_token.Type == TKTYPE.ADDSUB) { + while (_token.Type == TKTYPE.ADDSUB) + { var t = _token; GetToken(); var exprArg = ParseMulDiv(); @@ -299,9 +345,12 @@ } return x; } - Expression ParseMulDiv() { + + private Expression ParseMulDiv() + { var x = ParsePower(); - while (_token.Type == TKTYPE.MULDIV) { + while (_token.Type == TKTYPE.MULDIV) + { var t = _token; GetToken(); var a = ParsePower(); @@ -309,9 +358,12 @@ } return x; } - Expression ParsePower() { + + private Expression ParsePower() + { var x = ParseUnary(); - while (_token.Type == TKTYPE.POWER) { + while (_token.Type == TKTYPE.POWER) + { var t = _token; GetToken(); var a = ParseUnary(); @@ -319,9 +371,12 @@ } return x; } - Expression ParseUnary() { + + private Expression ParseUnary() + { // unary plus and minus - if (_token.ID == TKID.ADD || _token.ID == TKID.SUB) { + if (_token.ID == TKID.ADD || _token.ID == TKID.SUB) + { var t = _token; GetToken(); var a = ParseAtom(); @@ -331,12 +386,15 @@ // not unary, return atom return ParseAtom(); } - Expression ParseAtom() { + + private Expression ParseAtom() + { string id; Expression x = null; FunctionDefinition fnDef = null; - switch (_token.Type) { + switch (_token.Type) + { // literals case TKTYPE.LITERAL: x = new Expression(_token); @@ -349,13 +407,16 @@ 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); @@ -363,14 +424,16 @@ } // 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; } @@ -382,7 +445,8 @@ case TKTYPE.GROUP: // anything other than opening parenthesis is illegal here - if (_token.ID != TKID.OPEN) { + if (_token.ID != TKID.OPEN) + { Throw("Expression expected."); } @@ -391,7 +455,8 @@ x = ParseCompare(); // check that the parenthesis was closed - if (_token.ID != TKID.CLOSE) { + if (_token.ID != TKID.CLOSE) + { Throw("Unbalanced parenthesis."); } @@ -399,7 +464,8 @@ } // make sure we got something... - if (x == null) { + if (x == null) + { Throw(); } @@ -408,19 +474,29 @@ return x; } - #endregion + #endregion ** private stuff //--------------------------------------------------------------------------- + #region ** parser - void GetToken() { + private static IDictionary matchingClosingSymbols = new Dictionary() + { + { '\'', '\'' }, + { '[', ']' } + }; + + private void GetToken() + { // eat white space - while (_ptr < _len && _expr[_ptr] <= ' ') { + while (_ptr < _len && _expr[_ptr] <= ' ') + { _ptr++; } // are we done? - if (_ptr >= _len) { + if (_ptr >= _len) + { _token = new Token(null, TKID.END, TKTYPE.GROUP); return; } @@ -434,13 +510,22 @@ // 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) { + + var isEnclosed = matchingClosingSymbols.Keys.Contains(c); + char matchingClosingSymbol = '\0'; + if (isEnclosed) + matchingClosingSymbol = matchingClosingSymbols[c]; + + if (!isLetter && !isDigit && !isEnclosed) + { // 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; @@ -448,14 +533,17 @@ // look up single-char tokens on table Token tk; - if (_tkTbl.TryGetValue(c, out tk)) { + 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)) { + if (_ptr < _len && (c == '>' || c == '<')) + { + if (_tkTbl.TryGetValue(_expr.Substring(_ptr - 1, 2), out tk)) + { _token = tk; _ptr++; } @@ -468,31 +556,37 @@ } // parse numbers - if (isDigit || c == _decimal) { + 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++) { + 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) { + if (c == _decimal && div < 0) + { div = 1; continue; } // scientific notation? - if ((c == 'E' || c == 'e') && !sci) { + if ((c == 'E' || c == 'e') && !sci) + { sci = true; c = _expr[_ptr + i + 1]; if (c == '+' || c == '-') i++; @@ -500,7 +594,8 @@ } // percentage? - if (c == _percent) { + if (c == _percent) + { pct = true; i++; break; @@ -511,31 +606,41 @@ } // 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); } - // build token - _token = new Token(val, TKID.ATOM, TKTYPE.LITERAL); + if (c != ':') + { + // build token + _token = new Token(val, TKID.ATOM, TKTYPE.LITERAL); - // advance pointer and return - _ptr += i; - return; + // advance pointer and return + _ptr += i; + return; + } } // parse strings - if (c == '\"') { + if (c == '\"') + { // look for end quote, skip double quotes - for (i = 1; i + _ptr < _len; i++) { + for (i = 1; i + _ptr < _len; i++) + { c = _expr[_ptr + i]; if (c != '\"') continue; char cNext = i + _ptr < _len - 1 ? _expr[_ptr + i + 1] : ' '; @@ -544,7 +649,8 @@ } // check that we got the end of the string - if (c != '\"') { + if (c != '\"') + { Throw("Can't find final quote."); } @@ -556,15 +662,18 @@ } // parse dates (review) - if (c == '#') { + if (c == '#') + { // look for end # - for (i = 1; i + _ptr < _len; i++) { + 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 != '#') { + if (c != '#') + { Throw("Can't find final date delimiter ('#')."); } @@ -576,18 +685,39 @@ } // identifiers (functions, objects) must start with alpha or underscore - if (!isLetter && c != '_' && (_idChars == null || _idChars.IndexOf(c) < 0)) { + if (!isEnclosed && !isLetter && c != '_' && (_idChars == null || !_idChars.Contains(c))) + { 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)) { - break; + + if (isEnclosed && c == matchingClosingSymbol) + { + isEnclosed = false; + matchingClosingSymbol = '\0'; + + i++; + c = _expr[_ptr + i]; + isLetter = (c >= 'a' && c <= 'z') || (c >= 'A' && c <= 'Z'); + isDigit = c >= '0' && c <= '9'; } + + var disallowedSymbols = new List() { '\\', '/', '*', '[', ':', '?' }; + if (isEnclosed && disallowedSymbols.Contains(c)) + break; + + var allowedSymbols = new List() { '_' }; + + if (!isLetter && !isDigit + && !(isEnclosed || allowedSymbols.Contains(c)) + && (_idChars == null || !_idChars.Contains(c))) + break; } // got identifier @@ -595,21 +725,26 @@ _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]) { + + private 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) + + private 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) { + if (_token.ID != TKID.OPEN) + { _ptr = pos; _token = tk; return null; @@ -618,7 +753,8 @@ // check for empty Parameter list pos = _ptr; GetToken(); - if (_token.ID == TKID.CLOSE) { + if (_token.ID == TKID.CLOSE) + { return null; } _ptr = pos; @@ -627,26 +763,31 @@ var parms = new List(); var expr = ParseExpression(); parms.Add(expr); - while (_token.ID == TKID.COMMA) { + while (_token.ID == TKID.COMMA) + { expr = ParseExpression(); parms.Add(expr); } // make sure the list was closed correctly - if (_token.ID != TKID.CLOSE) { + if (_token.ID != TKID.CLOSE) + { Throw(); } // done return parms; } - Token GetMember() { + + private 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; @@ -654,25 +795,30 @@ // skip member token GetToken(); - if (_token.Type != TKTYPE.IDENTIFIER) { + if (_token.Type != TKTYPE.IDENTIFIER) + { Throw("Identifier expected"); } return _token; } - #endregion + #endregion ** parser //--------------------------------------------------------------------------- + #region ** static helpers - static void Throw() { + private static void Throw() + { Throw("Syntax error."); } - static void Throw(string msg) { + + private static void Throw(string msg) + { throw new ExpressionParseException(msg); } - #endregion + #endregion ** static helpers } /// diff --git a/ClosedXML/Excel/CalcEngine/XLCalcEngine.cs b/ClosedXML/Excel/CalcEngine/XLCalcEngine.cs index 48457e5..e6a15d3 100644 --- a/ClosedXML/Excel/CalcEngine/XLCalcEngine.cs +++ b/ClosedXML/Excel/CalcEngine/XLCalcEngine.cs @@ -1,8 +1,6 @@ using System; using System.Collections; -using System.Collections.Generic; using System.Linq; -using System.Text; namespace ClosedXML.Excel.CalcEngine { @@ -10,14 +8,17 @@ { private readonly IXLWorksheet _ws; private readonly XLWorkbook _wb; + public XLCalcEngine() - {} + { } + public XLCalcEngine(XLWorkbook wb) { _wb = wb; - IdentifierChars = "$:!"; + IdentifierChars = new char[] { '$', ':', '!' }; } - public XLCalcEngine(IXLWorksheet ws): this(ws.Workbook) + + public XLCalcEngine(IXLWorksheet ws) : this(ws.Workbook) { _ws = ws; } @@ -26,23 +27,44 @@ { if (identifier.Contains("!") && _wb != null) { - var wsName = identifier.Substring(0, identifier.IndexOf("!")); - return new CellRangeReference(_wb.Worksheet(wsName).Range(identifier.Substring(identifier.IndexOf("!") + 1)), this); + var referencedSheetNames = identifier.Split(':') + .Select(part => + { + if (part.Contains("!")) + return part.Substring(0, part.IndexOf('!')).ToLower(); + else + return null; + }) + .Where(sheet => sheet != null) + .Distinct(); + + if (!referencedSheetNames.Any()) + return new CellRangeReference(_ws.Range(identifier), this); + else if (referencedSheetNames.Count() > 1) + throw new ArgumentOutOfRangeException(referencedSheetNames.Last(), "Cross worksheet references may references no more than 1 other worksheet"); + else + { + IXLWorksheet worksheet; + if (!_wb.TryGetWorksheet(referencedSheetNames.Single(), out worksheet)) + throw new ArgumentOutOfRangeException(referencedSheetNames.Single(), "The required worksheet cannot be found"); + + identifier = identifier.ToLower().Replace(string.Format("{0}!", worksheet.Name.ToLower()), ""); + + return new CellRangeReference(worksheet.Range(identifier), this); + } } - - if (_ws != null) + else if (_ws != null) return new CellRangeReference(_ws.Range(identifier), this); - - return identifier; + else + return identifier; } - - } internal class CellRangeReference : IValueObject, IEnumerable { private IXLRange _range; private XLCalcEngine _ce; + public CellRangeReference(IXLRange range, XLCalcEngine ce) { _range = range; @@ -66,7 +88,7 @@ private Boolean _evaluating; // ** implementation - object GetValue(IXLCell cell) + private object GetValue(IXLCell cell) { if (_evaluating) { @@ -80,7 +102,6 @@ return cell.Value; else return new XLCalcEngine(cell.Worksheet).Evaluate(f); - } finally { diff --git a/ClosedXML/Excel/IXLSheetView.cs b/ClosedXML/Excel/IXLSheetView.cs index 9de885a..8628fa2 100644 --- a/ClosedXML/Excel/IXLSheetView.cs +++ b/ClosedXML/Excel/IXLSheetView.cs @@ -19,25 +19,25 @@ XLSheetViewOptions View { get; set; } /// - /// Window zoom magnification for current view representing percent values. Horizontal & Vertical scale together. + /// Window zoom magnification for current view representing percent values. Horizontal and vertical scale together. /// /// Representing percent values ranging from 10 to 400. Int32 ZoomScale { get; set; } /// - /// Zoom magnification to use when in normal view. Horizontal & Vertical scale together + /// Zoom magnification to use when in normal view. Horizontal and vertical scale together /// /// Representing percent values ranging from 10 to 400. Int32 ZoomScaleNormal { get; set; } /// - /// Zoom magnification to use when in page layout view. Horizontal & Vertical scale together. + /// Zoom magnification to use when in page layout view. Horizontal and vertical scale together. /// /// Representing percent values ranging from 10 to 400. Int32 ZoomScalePageLayoutView { get; set; } /// - /// Zoom magnification to use when in page break preview. Horizontal & Vertical scale together. + /// Zoom magnification to use when in page break preview. Horizontal and vertical scale together. /// /// Representing percent values ranging from 10 to 400. Int32 ZoomScaleSheetLayoutView { get; set; } diff --git a/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/Excel/XLWorkbook.cs index 83763e8..ce47961 100644 --- a/ClosedXML/Excel/XLWorkbook.cs +++ b/ClosedXML/Excel/XLWorkbook.cs @@ -396,7 +396,7 @@ public Boolean TryGetWorksheet(String name, out IXLWorksheet worksheet) { - if (Worksheets.Any(w => w.Name.ToLower().Equals(name.ToLower()))) + if (Worksheets.Any(w => string.Equals(w.Name, XLWorksheets.TrimSheetName(name), StringComparison.OrdinalIgnoreCase))) { worksheet = Worksheet(name); return true; @@ -504,13 +504,22 @@ var extension = Path.GetExtension(filePath); if (extension == null) throw new ArgumentException("Empty extension is not supported."); + extension = extension.Substring(1).ToLowerInvariant(); - if (extension.ToLowerInvariant().Equals(".xlsm")) return SpreadsheetDocumentType.MacroEnabledWorkbook; + switch (extension) + { + case "xlsm": + case "xltm": + return SpreadsheetDocumentType.MacroEnabledWorkbook; + case "xlsx": + case "xltx": + return SpreadsheetDocumentType.Workbook; + default: + throw new ArgumentException(String.Format("Extension '{0}' is not supported. Supported extensions are '.xlsx', '.xslm', '.xltx' and '.xltm'.", extension)); - if (extension.ToLowerInvariant().Equals(".xlsx")) return SpreadsheetDocumentType.Workbook; - - throw new ArgumentException(String.Format("Extension '{0}' is not supported. Supported extensions are '.xlsx' and '.xslm'.", extension)); + } } + private void checkForWorksheetsPresent() { if (Worksheets.Count() == 0) diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs index 7b9b5e3..794d37b 100644 --- a/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/Excel/XLWorkbook_Load.cs @@ -9,6 +9,9 @@ using System.Globalization; using System.IO; using System.Linq; +using System.Text; +using System.Text.RegularExpressions; +using System.Xml.Linq; using Ap = DocumentFormat.OpenXml.ExtendedProperties; using Op = DocumentFormat.OpenXml.CustomProperties; @@ -21,7 +24,6 @@ using Ap; using Op; using System.Drawing; - using System.Xml.Linq; #endregion @@ -927,7 +929,8 @@ if (definedName.Hidden != null) visible = !BooleanValue.ToBoolean(definedName.Hidden); if (name == "_xlnm.Print_Area") { - foreach (string area in definedName.Text.Split(',')) + var fixedNames = validateDefinedNames(definedName.Text.Split(',')); + foreach (string area in fixedNames) { if (area.Contains("[")) { @@ -974,19 +977,38 @@ } } - private void LoadPrintTitles(DefinedName definedName) + private static Regex definedNameRegex = new Regex(@"\A'.*'!.*\z", RegexOptions.Compiled); + + private IEnumerable validateDefinedNames(IEnumerable definedNames) { - var areas = definedName.Text.Split(','); - if (areas.Length > 0) + var fixedNames = new List(); + var sb = new StringBuilder(); + foreach (string testName in definedNames) { - foreach (var item in areas) + if (sb.Length > 0) + sb.Append(','); + + sb.Append(testName); + + Match matchedValidPattern = definedNameRegex.Match(sb.ToString()); + if (matchedValidPattern.Success) { - SetColumnsOrRowsToRepeat(item); + yield return sb.ToString(); + sb = new StringBuilder(); } - return; } - SetColumnsOrRowsToRepeat(definedName.Text); + if (sb.Length > 0) + yield return sb.ToString(); + } + + private void LoadPrintTitles(DefinedName definedName) + { + var areas = validateDefinedNames(definedName.Text.Split(',')); + foreach (var item in areas) + { + SetColumnsOrRowsToRepeat(item); + } } private void SetColumnsOrRowsToRepeat(string area) @@ -1670,8 +1692,8 @@ /// /// Loads the conditional formatting. - /// https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.conditionalformattingrule%28v=office.15%29.aspx?f=255&MSPPError=-2147217396 /// + // https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.conditionalformattingrule%28v=office.15%29.aspx?f=255&MSPPError=-2147217396 private void LoadConditionalFormatting(ConditionalFormatting conditionalFormatting, XLWorksheet ws, Dictionary differentialFormats) { if (conditionalFormatting == null) return; diff --git a/ClosedXML/Excel/XLWorksheets.cs b/ClosedXML/Excel/XLWorksheets.cs index 80c2be5..3cc7295 100644 --- a/ClosedXML/Excel/XLWorksheets.cs +++ b/ClosedXML/Excel/XLWorksheets.cs @@ -56,14 +56,24 @@ return false; } + internal static string TrimSheetName(string sheetName) + { + if (sheetName.StartsWith("'") && sheetName.EndsWith("'") && sheetName.Length > 2) + sheetName = sheetName.Substring(1, sheetName.Length - 2); + + return sheetName; + } + public IXLWorksheet Worksheet(String sheetName) { + sheetName = TrimSheetName(sheetName); + XLWorksheet w; + if (_worksheets.TryGetValue(sheetName, out w)) return w; - var wss = _worksheets.Where(ws => ws.Key.ToLower().Equals(sheetName.ToLower())); - + var wss = _worksheets.Where(ws => string.Equals(ws.Key, sheetName, StringComparison.OrdinalIgnoreCase)); if (wss.Any()) return wss.First().Value; @@ -167,4 +177,4 @@ _worksheets.Add(newSheetName, ws); } } -} \ No newline at end of file +} diff --git a/ClosedXML/packages.config b/ClosedXML/packages.config index 682875d..cd2fc49 100644 --- a/ClosedXML/packages.config +++ b/ClosedXML/packages.config @@ -1,5 +1,5 @@  - - + + \ No newline at end of file diff --git a/ClosedXML_Examples/ClosedXML_Examples.csproj b/ClosedXML_Examples/ClosedXML_Examples.csproj index 155d094..2daeac4 100644 --- a/ClosedXML_Examples/ClosedXML_Examples.csproj +++ b/ClosedXML_Examples/ClosedXML_Examples.csproj @@ -10,7 +10,7 @@ Properties ClosedXML_Examples ClosedXML_Examples - v4.0 + v4.5.2 512 @@ -24,6 +24,7 @@ full AnyCPU prompt + false bin\Release\ @@ -32,6 +33,7 @@ pdbonly AnyCPU prompt + false ClosedXML_Examples.Program @@ -43,7 +45,8 @@ ClosedXML.snk - + + ..\packages\DocumentFormat.OpenXml.2.7.1\lib\net45\DocumentFormat.OpenXml.dll True @@ -55,6 +58,7 @@ + diff --git a/ClosedXML_Examples/app.config b/ClosedXML_Examples/app.config index cb2586b..af399dd 100644 --- a/ClosedXML_Examples/app.config +++ b/ClosedXML_Examples/app.config @@ -1,3 +1,6 @@ - + + + + diff --git a/ClosedXML_Examples/packages.config b/ClosedXML_Examples/packages.config index 3a74e72..93a1c7a 100644 --- a/ClosedXML_Examples/packages.config +++ b/ClosedXML_Examples/packages.config @@ -1,4 +1,4 @@  - + \ No newline at end of file diff --git a/ClosedXML_Net3.5/ClosedXML.snk b/ClosedXML_Net3.5/ClosedXML.snk deleted file mode 100644 index a17cff7..0000000 --- a/ClosedXML_Net3.5/ClosedXML.snk +++ /dev/null Binary files differ diff --git a/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj b/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj deleted file mode 100644 index 3c735ab..0000000 --- a/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj +++ /dev/null @@ -1,861 +0,0 @@ - - - - Debug - AnyCPU - 8.0.30703 - 2.0 - {5F43B12B-A900-40C6-9924-A0C0B032F791} - Library - Properties - ClosedXML - ClosedXML - v3.5 - 512 - Client - - - true - full - false - bin\Debug\ - DEBUG;TRACE - prompt - 4 - 1591 - - - pdbonly - true - bin\Release\ - TRACE - prompt - 4 - bin\Release\ClosedXML.xml - 1591 - - - true - - - ClosedXML.snk - - - - ..\packages\DocumentFormat.OpenXml.1.0\lib\DocumentFormat.OpenXml.dll - True - - - ..\packages\FastMember.Signed.1.1.0\lib\net35\FastMember.Signed.dll - True - - - - - - - - - - - - - AttributeExtensions.cs - - - Attributes\XLColumnAttribute.cs - - - Excel\AutoFilters\IXLAutoFilter.cs - - - Excel\AutoFilters\IXLBaseAutoFilter.cs - - - Excel\AutoFilters\IXLCustomFilteredColumn.cs - - - Excel\AutoFilters\IXLFilterColumn.cs - - - Excel\AutoFilters\IXLFilterConnector.cs - - - Excel\AutoFilters\IXLFilteredColumn.cs - - - Excel\AutoFilters\XLAutoFilter.cs - - - Excel\AutoFilters\XLCustomFilteredColumn.cs - - - Excel\AutoFilters\XLFilter.cs - - - Excel\AutoFilters\XLFilterColumn.cs - - - Excel\AutoFilters\XLFilterConnector.cs - - - Excel\AutoFilters\XLFilteredColumn.cs - - - Excel\CalcEngine\CalcEngine.cs - - - Excel\CalcEngine\CalcEngineHelpers.cs - - - Excel\CalcEngine\Expression.cs - - - Excel\CalcEngine\ExpressionCache.cs - - - Excel\CalcEngine\ExpressionParseException.cs - - - Excel\CalcEngine\FunctionDefinition.cs - - - Excel\CalcEngine\Functions\Database.cs - - - Excel\CalcEngine\Functions\DateAndTime.cs - - - Excel\CalcEngine\Functions\Information.cs - - - Excel\CalcEngine\Functions\Logical.cs - - - Excel\CalcEngine\Functions\Lookup.cs - - - Excel\CalcEngine\Functions\MathTrig.cs - - - Excel\CalcEngine\Functions\Statistical.cs - - - Excel\CalcEngine\Functions\Tally.cs - - - Excel\CalcEngine\Functions\Text.cs - - - Excel\CalcEngine\Functions\XLMath.cs - - - Excel\CalcEngine\Functions\XLMatrix.cs - - - Excel\CalcEngine\Token.cs - - - Excel\CalcEngine\XLCalcEngine.cs - - - Excel\Cells\IXLCell.cs - - - Excel\Cells\IXLCells.cs - - - Excel\Cells\XLCell.cs - - - Excel\Cells\XLCells.cs - - - Excel\Cells\XLCellsCollection.cs - - - Excel\Charts\IXLChart.cs - - - Excel\Charts\IXLCharts.cs - - - Excel\Charts\XLChart.cs - - - Excel\Charts\XLCharts.cs - - - Excel\Columns\IXLColumn.cs - - - Excel\Columns\IXLColumns.cs - - - Excel\Columns\XLColumn.cs - - - Excel\Columns\XLColumnCollection.cs - - - Excel\Columns\XLColumnParameters.cs - - - Excel\Columns\XLColumns.cs - - - Excel\Comments\IXLComment.cs - - - Excel\Comments\XLComment.cs - - - Excel\ConditionalFormats\IXLCFColorScaleMax.cs - - - Excel\ConditionalFormats\IXLCFColorScaleMid.cs - - - Excel\ConditionalFormats\IXLCFColorScaleMin.cs - - - Excel\ConditionalFormats\IXLCFDataBarMax.cs - - - Excel\ConditionalFormats\IXLCFDataBarMin.cs - - - Excel\ConditionalFormats\IXLCFIconSet.cs - - - Excel\ConditionalFormats\IXLConditionalFormat.cs - - - Excel\ConditionalFormats\IXLConditionalFormats.cs - - - Excel\ConditionalFormats\Save\IXLCFConverter.cs - - - Excel\ConditionalFormats\Save\XLCFCellIsConverter.cs - - - Excel\ConditionalFormats\Save\XLCFColorScaleConverter.cs - - - Excel\ConditionalFormats\Save\XLCFContainsConverter.cs - - - Excel\ConditionalFormats\Save\XLCFConverters.cs - - - Excel\ConditionalFormats\Save\XLCFDataBarConverter.cs - - - Excel\ConditionalFormats\Save\XLCFEndsWithConverter.cs - - - Excel\ConditionalFormats\Save\XLCFIconSetConverter.cs - - - Excel\ConditionalFormats\Save\XLCFIsBlankConverter.cs - - - Excel\ConditionalFormats\Save\XLCFIsErrorConverter.cs - - - Excel\ConditionalFormats\Save\XLCFNotBlankConverter.cs - - - Excel\ConditionalFormats\Save\XLCFNotContainsConverter.cs - - - Excel\ConditionalFormats\Save\XLCFNotErrorConverter.cs - - - Excel\ConditionalFormats\Save\XLCFStartsWithConverter.cs - - - Excel\ConditionalFormats\Save\XLCFTopConverter.cs - - - Excel\ConditionalFormats\Save\XLCFUniqueConverter.cs - - - Excel\ConditionalFormats\XLCFColorScaleMax.cs - - - Excel\ConditionalFormats\XLCFColorScaleMid.cs - - - Excel\ConditionalFormats\XLCFColorScaleMin.cs - - - Excel\ConditionalFormats\XLCFDataBarMax.cs - - - Excel\ConditionalFormats\XLCFDataBarMin.cs - - - Excel\ConditionalFormats\XLCFIconSet.cs - - - Excel\ConditionalFormats\XLConditionalFormat.cs - - - Excel\ConditionalFormats\XLConditionalFormats.cs - - - Excel\Coordinate\IXLAddress.cs - - - Excel\Coordinate\XLAddress.cs - - - Excel\Coordinate\XLSheetPoint.cs - - - Excel\Coordinate\XLSheetRange.cs - - - Excel\CustomProperties\IXLCustomProperties.cs - - - Excel\CustomProperties\IXLCustomProperty.cs - - - Excel\CustomProperties\XLCustomProperties.cs - - - Excel\CustomProperties\XLCustomProperty.cs - - - Excel\DataValidation\IXLDataValidation.cs - - - Excel\DataValidation\IXLDataValidations.cs - - - Excel\DataValidation\IXLValidationCriteria.cs - - - Excel\DataValidation\XLDataValidation.cs - - - Excel\DataValidation\XLDataValidations.cs - - - Excel\DataValidation\XLDateCriteria.cs - - - Excel\DataValidation\XLDecimalCriteria.cs - - - Excel\DataValidation\XLTextLengthCriteria.cs - - - Excel\DataValidation\XLTimeCriteria.cs - - - Excel\DataValidation\XLValidationCriteria.cs - - - Excel\DataValidation\XLWholeNumberCriteria.cs - - - Excel\Drawings\IXLDrawing.cs - - - Excel\Drawings\IXLDrawingPosition.cs - - - Excel\Drawings\Style\IXLDrawingAlignment.cs - - - Excel\Drawings\Style\IXLDrawingColorsAndLines.cs - - - Excel\Drawings\Style\IXLDrawingFont.cs - - - Excel\Drawings\Style\IXLDrawingMargins.cs - - - Excel\Drawings\Style\IXLDrawingProperties.cs - - - Excel\Drawings\Style\IXLDrawingProtection.cs - - - Excel\Drawings\Style\IXLDrawingSize.cs - - - Excel\Drawings\Style\IXLDrawingStyle.cs - - - Excel\Drawings\Style\IXLDrawingWeb.cs - - - Excel\Drawings\Style\XLDrawingAlignment.cs - - - Excel\Drawings\Style\XLDrawingColorsAndLines.cs - - - Excel\Drawings\Style\XLDrawingFont.cs - - - Excel\Drawings\Style\XLDrawingMargins.cs - - - Excel\Drawings\Style\XLDrawingProperties.cs - - - Excel\Drawings\Style\XLDrawingProtection.cs - - - Excel\Drawings\Style\XLDrawingSize.cs - - - Excel\Drawings\Style\XLDrawingStyle.cs - - - Excel\Drawings\Style\XLDrawingWeb.cs - - - Excel\Drawings\XLDrawing.cs - - - Excel\Drawings\XLDrawingPosition.cs - - - Excel\EnumConverter.cs - - - Excel\Hyperlinks\IXLHyperlinks.cs - - - Excel\Hyperlinks\XLHyperlinks.cs - - - Excel\Hyperlinks\XLHyperlink_Internal.cs - - - Excel\Hyperlinks\XLHyperlink_public.cs - - - Excel\IXLOutline.cs - - - Excel\IXLSheetProtection.cs - - - Excel\IXLSheetView.cs - - - Excel\IXLTheme.cs - - - Excel\IXLWorksheet.cs - - - Excel\IXLWorksheets.cs - - - Excel\Misc\XLCallbackAction.cs - - - Excel\Misc\XLDictionary.cs - - - Excel\Misc\XLFormula.cs - - - Excel\Misc\XLIdManager.cs - - - Excel\Misc\XLReentrantEnumerableSet.cs - - - Excel\NamedRanges\IXLNamedRange.cs - - - Excel\NamedRanges\IXLNamedRanges.cs - - - Excel\NamedRanges\XLNamedRange.cs - - - Excel\NamedRanges\XLNamedRanges.cs - - - Excel\PageSetup\IXLHeaderFooter.cs - - - Excel\PageSetup\IXLHFItem.cs - - - Excel\PageSetup\IXLMargins.cs - - - Excel\PageSetup\IXLPageSetup.cs - - - Excel\PageSetup\IXLPrintAreas.cs - - - Excel\PageSetup\XLHeaderFooter.cs - - - Excel\PageSetup\XLHFItem.cs - - - Excel\PageSetup\XLHFText.cs - - - Excel\PageSetup\XLMargins.cs - - - Excel\PageSetup\XLPageSetup.cs - - - Excel\PageSetup\XLPrintAreas.cs - - - Excel\PivotTables\IXLPivotField.cs - - - Excel\PivotTables\IXLPivotFields.cs - - - Excel\PivotTables\IXLPivotTable.cs - - - Excel\PivotTables\IXLPivotTables.cs - - - Excel\PivotTables\PivotValues\IXLPivotValue.cs - - - Excel\PivotTables\PivotValues\IXLPivotValueCombination.cs - - - Excel\PivotTables\PivotValues\IXLPivotValueFormat.cs - - - Excel\PivotTables\PivotValues\IXLPivotValues.cs - - - Excel\PivotTables\PivotValues\XLPivotValue.cs - - - Excel\PivotTables\PivotValues\XLPivotValueCombination.cs - - - Excel\PivotTables\PivotValues\XLPivotValueFormat.cs - - - Excel\PivotTables\PivotValues\XLPivotValues.cs - - - Excel\PivotTables\XLPivotField.cs - - - Excel\PivotTables\XLPivotFields.cs - - - Excel\PivotTables\XLPivotTable.cs - - - Excel\PivotTables\XLPivotTables.cs - - - Excel\Ranges\IXLRange.cs - - - Excel\Ranges\IXLRangeAddress.cs - - - Excel\Ranges\IXLRangeBase.cs - - - Excel\Ranges\IXLRangeColumn.cs - - - Excel\Ranges\IXLRangeColumns.cs - - - Excel\Ranges\IXLRangeRow.cs - - - Excel\Ranges\IXLRangeRows.cs - - - Excel\Ranges\IXLRanges.cs - - - Excel\Ranges\Sort\IXLSortElement.cs - - - Excel\Ranges\Sort\IXLSortElements.cs - - - Excel\Ranges\Sort\XLSortElement.cs - - - Excel\Ranges\Sort\XLSortElements.cs - - - Excel\Ranges\XLRange.cs - - - Excel\Ranges\XLRangeAddress.cs - - - Excel\Ranges\XLRangeBase.cs - - - Excel\Ranges\XLRangeColumn.cs - - - Excel\Ranges\XLRangeColumns.cs - - - Excel\Ranges\XLRangeParameters.cs - - - Excel\Ranges\XLRangeRow.cs - - - Excel\Ranges\XLRangeRows.cs - - - Excel\Ranges\XLRanges.cs - - - Excel\RichText\IXLFormattedText.cs - - - Excel\RichText\IXLPhonetic.cs - - - Excel\RichText\IXLPhonetics.cs - - - Excel\RichText\IXLRichString.cs - - - Excel\RichText\IXLRichText.cs - - - Excel\RichText\XLFormattedText.cs - - - Excel\RichText\XLPhonetic.cs - - - Excel\RichText\XLPhonetics.cs - - - Excel\RichText\XLRichString.cs - - - Excel\RichText\XLRichText.cs - - - Excel\Rows\IXLRow.cs - - - Excel\Rows\IXLRows.cs - - - Excel\Rows\XLRow.cs - - - Excel\Rows\XLRowCollection.cs - - - Excel\Rows\XLRowParameters.cs - - - Excel\Rows\XLRows.cs - - - Excel\Style\Colors\XLColor_Internal.cs - - - Excel\Style\Colors\XLColor_Public.cs - - - Excel\Style\Colors\XLColor_Static.cs - - - Excel\Style\IXLAlignment.cs - - - Excel\Style\IXLBorder.cs - - - Excel\Style\IXLFill.cs - - - Excel\Style\IXLFont.cs - - - Excel\Style\IXLFontBase.cs - - - Excel\Style\IXLNumberFormat.cs - - - Excel\Style\IXLNumberFormatBase.cs - - - Excel\Style\IXLProtection.cs - - - Excel\Style\IXLStyle.cs - - - Excel\Style\IXLStylized.cs - - - Excel\Style\XLAlignment.cs - - - Excel\Style\XLBorder.cs - - - Excel\Style\XLFill.cs - - - Excel\Style\XLFont.cs - - - Excel\Style\XLNumberFormat.cs - - - Excel\Style\XLProtection.cs - - - Excel\Style\XLStyle.cs - - - Excel\Style\XLStylizedContainer.cs - - - Excel\Tables\IXLTable.cs - - - Excel\Tables\IXLTableField.cs - - - Excel\Tables\IXLTableRange.cs - - - Excel\Tables\IXLTableRow.cs - - - Excel\Tables\IXLTableRows.cs - - - Excel\Tables\IXLTables.cs - - - Excel\Tables\XLTable.cs - - - Excel\Tables\XLTableField.cs - - - Excel\Tables\XLTableRange.cs - - - Excel\Tables\XLTableRow.cs - - - Excel\Tables\XLTableRows.cs - - - Excel\Tables\XLTables.cs - - - Excel\Tables\XLTableTheme.cs - - - Excel\XLConstants.cs - - - Excel\XLOutline.cs - - - Excel\XLSheetProtection.cs - - - Excel\XLSheetView.cs - - - Excel\XLTheme.cs - - - Excel\XLWorkbook.cs - - - Excel\XLWorkbookProperties.cs - - - Excel\XLWorkbook_Load.cs - - - Excel\XLWorkbook_Save.cs - - - Excel\XLWorkbook_Save.NestedTypes.cs - - - Excel\XLWorksheet.cs - - - Excel\XLWorksheetInternals.cs - - - Excel\XLWorksheets.cs - - - Excel\XLWSContentManager.cs - - - Extensions.cs - - - PathHelper.cs - - - Utils\GraphicsUtils.cs - - - Utils\XmlEncoder.cs - - - XLHelper.cs - - - - - - - .editorconfig - - - - - - - - \ No newline at end of file diff --git a/ClosedXML_Net3.5/Excel/CalcEngine/Functions/Information.cs b/ClosedXML_Net3.5/Excel/CalcEngine/Functions/Information.cs deleted file mode 100644 index dcb1340..0000000 --- a/ClosedXML_Net3.5/Excel/CalcEngine/Functions/Information.cs +++ /dev/null @@ -1,152 +0,0 @@ -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_Net3.5/Excel/Style/XLStylizedEmpty.cs b/ClosedXML_Net3.5/Excel/Style/XLStylizedEmpty.cs deleted file mode 100644 index 9e059f9..0000000 --- a/ClosedXML_Net3.5/Excel/Style/XLStylizedEmpty.cs +++ /dev/null @@ -1,37 +0,0 @@ -using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; - -namespace ClosedXML.Excel -{ - internal class XLStylizedEmpty: IXLStylized - { - public XLStylizedEmpty(IXLStyle defaultStyle) - { - Style = defaultStyle; - } - public IXLStyle Style { get; set; } - - public IEnumerable Styles - { - get - { - UpdatingStyle = true; - yield return Style; - UpdatingStyle = false; - } - } - - public bool UpdatingStyle { get; set; } - - public IXLStyle InnerStyle { get; set; } - - public IXLRanges RangesUsed - { - get { return new XLRanges(); } - } - - public bool StyleChanged { get; set; } - } -} diff --git a/ClosedXML_Net3.5/Properties/AssemblyInfo.cs b/ClosedXML_Net3.5/Properties/AssemblyInfo.cs deleted file mode 100644 index f650ca3..0000000 --- a/ClosedXML_Net3.5/Properties/AssemblyInfo.cs +++ /dev/null @@ -1,35 +0,0 @@ -using System.Reflection; -using System.Runtime.InteropServices; - -// General Information about an assembly is controlled through the following -// set of attributes. Change these attribute values to modify the information -// associated with an assembly. -[assembly: AssemblyTitle("ClosedXML_Net3.5")] -[assembly: AssemblyDescription("")] -[assembly: AssemblyConfiguration("")] -[assembly: AssemblyCompany("")] -[assembly: AssemblyProduct("ClosedXML_Net3.5")] -[assembly: AssemblyCopyright("Copyright © Manuel De Leon 2014")] -[assembly: AssemblyTrademark("")] -[assembly: AssemblyCulture("")] - -// Setting ComVisible to false makes the types in this assembly not visible -// to COM components. If you need to access a type in this assembly from -// COM, set the ComVisible attribute to true on that type. -[assembly: ComVisible(false)] - -// The following GUID is for the ID of the typelib if this project is exposed to COM -[assembly: Guid("d627551d-470d-4a39-a5f1-5a71696b8200")] - -// Version information for an assembly consists of the following four values: -// -// Major Version -// Minor Version -// Build Number -// Revision -// -// You can specify all the values or you can default the Build and Revision Numbers -// by using the '*' as shown below: -// [assembly: AssemblyVersion("1.0.*")] -[assembly: AssemblyVersion("0.80.0.0")] -[assembly: AssemblyFileVersion("0.80.0.0")] diff --git a/ClosedXML_Net3.5/packages.config b/ClosedXML_Net3.5/packages.config deleted file mode 100644 index 6908883..0000000 --- a/ClosedXML_Net3.5/packages.config +++ /dev/null @@ -1,5 +0,0 @@ - - - - - \ No newline at end of file diff --git a/ClosedXML_Sandbox/ClosedXML_Sandbox.csproj b/ClosedXML_Sandbox/ClosedXML_Sandbox.csproj index 3581c95..5c7b02a 100644 --- a/ClosedXML_Sandbox/ClosedXML_Sandbox.csproj +++ b/ClosedXML_Sandbox/ClosedXML_Sandbox.csproj @@ -10,9 +10,7 @@ Properties ClosedXML_Sandbox ClosedXML_Sandbox - v4.5.1 - - + v4.5.2 512 ..\ true @@ -36,8 +34,8 @@ false - - ..\packages\DocumentFormat.OpenXml.2.5\lib\DocumentFormat.OpenXml.dll + + ..\packages\DocumentFormat.OpenXml.2.7.1\lib\net45\DocumentFormat.OpenXml.dll True @@ -63,6 +61,9 @@ + + PreserveNewest + diff --git a/ClosedXML_Sandbox/PerformanceRunner.cs b/ClosedXML_Sandbox/PerformanceRunner.cs index 97d3a31..2d4f570 100644 --- a/ClosedXML_Sandbox/PerformanceRunner.cs +++ b/ClosedXML_Sandbox/PerformanceRunner.cs @@ -17,7 +17,8 @@ Console.WriteLine("Action done in " + stopwatch.Elapsed); } - const int rowCount = 5000; + private const int rowCount = 5000; + public static void RunInsertTable() { var rows = new List(); @@ -39,6 +40,16 @@ EmulateSave(workbook); } + public static void OpenTestFile() + { + using (var wb = new XLWorkbook("test.xlsx")) + { + var ws = wb.Worksheets.First(); + var cell = ws.FirstCellUsed(); + Console.WriteLine(cell.Value); + } + } + private static void CreateMergedCell(IXLWorksheet worksheet) { worksheet.Cell(rowCount + 2, 1).Value = "Merged cell"; diff --git a/ClosedXML_Sandbox/Program.cs b/ClosedXML_Sandbox/Program.cs index 0cb47d1..9136cbe 100644 --- a/ClosedXML_Sandbox/Program.cs +++ b/ClosedXML_Sandbox/Program.cs @@ -2,11 +2,17 @@ namespace ClosedXML_Sandbox { - class Program + internal class Program { private static void Main(string[] args) { + Console.WriteLine("Running {0}", "OpenTestFile"); + PerformanceRunner.TimeAction(PerformanceRunner.OpenTestFile); + Console.WriteLine(); + + Console.WriteLine("Running {0}", "RunInsertTable"); PerformanceRunner.TimeAction(PerformanceRunner.RunInsertTable); + Console.WriteLine(); Console.WriteLine("Press any key to continue"); Console.ReadKey(); diff --git a/ClosedXML_Sandbox/app.config b/ClosedXML_Sandbox/app.config index 22c902a..af399dd 100644 --- a/ClosedXML_Sandbox/app.config +++ b/ClosedXML_Sandbox/app.config @@ -1,3 +1,6 @@ - + + + + diff --git a/ClosedXML_Sandbox/packages.config b/ClosedXML_Sandbox/packages.config index 3a74e72..93a1c7a 100644 --- a/ClosedXML_Sandbox/packages.config +++ b/ClosedXML_Sandbox/packages.config @@ -1,4 +1,4 @@  - + \ No newline at end of file diff --git a/ClosedXML_Sandbox/test.xlsx b/ClosedXML_Sandbox/test.xlsx new file mode 100644 index 0000000..296c174 --- /dev/null +++ b/ClosedXML_Sandbox/test.xlsx Binary files differ diff --git a/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML_Tests/ClosedXML_Tests.csproj index eabbee3..1ad18c5 100644 --- a/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -11,7 +11,7 @@ Properties ClosedXML_Tests ClosedXML_Tests - v4.0 + v4.5.2 512 {3AC096D0-A1C2-E12C-1390-A8335801FDAB};{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC} ..\ @@ -22,17 +22,19 @@ full false bin\Debug\ - DEBUG;TRACE + DEBUG;TRACE;$(AppVeyor) prompt 4 + false pdbonly true bin\Release\ - TRACE + TRACE;$(AppVeyor) prompt 4 + false true @@ -41,13 +43,13 @@ ClosedXML.snk - - ..\packages\DocumentFormat.OpenXml.2.5\lib\DocumentFormat.OpenXml.dll + + ..\packages\DocumentFormat.OpenXml.2.7.1\lib\net45\DocumentFormat.OpenXml.dll True - ..\packages\NUnit.3.4.1\lib\net40\nunit.framework.dll + ..\packages\NUnit.3.4.1\lib\net45\nunit.framework.dll True @@ -261,6 +263,7 @@ + diff --git a/ClosedXML_Tests/Excel/Loading/LoadingTests.cs b/ClosedXML_Tests/Excel/Loading/LoadingTests.cs index d2e7069..5cff8ca 100644 --- a/ClosedXML_Tests/Excel/Loading/LoadingTests.cs +++ b/ClosedXML_Tests/Excel/Loading/LoadingTests.cs @@ -1,121 +1,122 @@ -using ClosedXML.Excel; -using NUnit.Framework; -using System.Collections.Generic; -using System.IO; -using System.Linq; - -namespace ClosedXML_Tests.Excel -{ - // Tests in this fixture test only the successful loading of existing Excel files, - // i.e. we test that ClosedXML doesn't choke on a given input file - // These tests DO NOT test that ClosedXML successfully recognises all the Excel parts or that it can successfully save those parts again. - [TestFixture] - public class LoadingTests - { - [Test] - public void CanSuccessfullyLoadFiles() - { - var files = new List() - { - @"Misc\TableWithCustomTheme.xlsx", - @"Misc\EmptyTable.xlsx", - @"Misc\LoadPivotTables.xlsx", - @"Misc\LoadFileWithCustomSheetViews.xlsx" - }; - - foreach (var file in files) - { - TestHelper.LoadFile(file); - } - } - - [Test] - public void CanLoadAndManipulateFileWithEmptyTable() - { - using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Misc\EmptyTable.xlsx"))) - using (var wb = new XLWorkbook(stream)) - { - var ws = wb.Worksheets.First(); - var table = ws.Tables.First(); - table.DataRange.InsertRowsBelow(5); - } - } - - [Test] - public void CanLoadAndSaveFileWithMismatchingSheetIdAndRelId() - { - // This file's workbook.xml contains: - // - // and the mismatch between the sheetId and r:id can create problems. - using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Misc\FileWithMismatchSheetIdAndRelId.xlsx"))) - using (var wb = new XLWorkbook(stream)) - { - using (var ms = new MemoryStream()) - { - wb.SaveAs(ms, true); - } - } - } - - [Test] - public void CanLoadBasicPivotTable() - { - using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Misc\LoadPivotTables.xlsx"))) - using (var wb = new XLWorkbook(stream)) - { - var ws = wb.Worksheet("PivotTable1"); - var pt = ws.PivotTable("PivotTable1"); - Assert.AreEqual("PivotTable1", pt.Name); - - Assert.AreEqual(1, pt.RowLabels.Count()); - Assert.AreEqual("Name", pt.RowLabels.Single().SourceName); - - Assert.AreEqual(1, pt.ColumnLabels.Count()); - Assert.AreEqual("Month", pt.ColumnLabels.Single().SourceName); - - var pv = pt.Values.Single(); - Assert.AreEqual("Sum of NumberOfOrders", pv.CustomName); - Assert.AreEqual("NumberOfOrders", pv.SourceName); - } - } - - /// - /// For non-English locales, the default style ("Normal" in English) can be - /// another piece of text (e.g. Обычный in Russian). - /// This test ensures that the default style is correctly detected and - /// no style conflicts occur on save. - /// - [Test] - public void CanSaveFileWithDefaultStyleNameNotInEnglish() - { - using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Misc\FileWithDefaultStyleNameNotInEnglish.xlsx"))) - using (var wb = new XLWorkbook(stream)) - { - using (var ms = new MemoryStream()) - { - wb.SaveAs(ms, true); - } - } - } - - /// - /// As per https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.cellvalues(v=office.15).aspx - /// the 'Date' DataType is available only in files saved with Microsoft Office - /// In other files, the data type will be saved as numeric - /// ClosedXML then deduces the data type by inspecting the number format string - /// - [Test] - public void CanLoadLibreOfficeFileWithDates() - { - using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Misc\LibreOfficeFileWithDates.xlsx"))) - using (var wb = new XLWorkbook(stream)) - { - var ws = wb.Worksheets.First(); - foreach (var cell in ws.CellsUsed()) - { - Assert.AreEqual(XLCellValues.DateTime, cell.DataType); - } - } - } - } -} +using ClosedXML.Excel; +using NUnit.Framework; +using System.Collections.Generic; +using System.IO; +using System.Linq; + +namespace ClosedXML_Tests.Excel +{ + // Tests in this fixture test only the successful loading of existing Excel files, + // i.e. we test that ClosedXML doesn't choke on a given input file + // These tests DO NOT test that ClosedXML successfully recognises all the Excel parts or that it can successfully save those parts again. + [TestFixture] + public class LoadingTests + { + [Test] + public void CanSuccessfullyLoadFiles() + { + var files = new List() + { + @"Misc\TableWithCustomTheme.xlsx", + @"Misc\EmptyTable.xlsx", + @"Misc\LoadPivotTables.xlsx", + @"Misc\LoadFileWithCustomSheetViews.xlsx", + @"Misc\LoadSheetsWithCommas.xlsx" + }; + + foreach (var file in files) + { + TestHelper.LoadFile(file); + } + } + + [Test] + public void CanLoadAndManipulateFileWithEmptyTable() + { + using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Misc\EmptyTable.xlsx"))) + using (var wb = new XLWorkbook(stream)) + { + var ws = wb.Worksheets.First(); + var table = ws.Tables.First(); + table.DataRange.InsertRowsBelow(5); + } + } + + [Test] + public void CanLoadAndSaveFileWithMismatchingSheetIdAndRelId() + { + // This file's workbook.xml contains: + // + // and the mismatch between the sheetId and r:id can create problems. + using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Misc\FileWithMismatchSheetIdAndRelId.xlsx"))) + using (var wb = new XLWorkbook(stream)) + { + using (var ms = new MemoryStream()) + { + wb.SaveAs(ms, true); + } + } + } + + [Test] + public void CanLoadBasicPivotTable() + { + using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Misc\LoadPivotTables.xlsx"))) + using (var wb = new XLWorkbook(stream)) + { + var ws = wb.Worksheet("PivotTable1"); + var pt = ws.PivotTable("PivotTable1"); + Assert.AreEqual("PivotTable1", pt.Name); + + Assert.AreEqual(1, pt.RowLabels.Count()); + Assert.AreEqual("Name", pt.RowLabels.Single().SourceName); + + Assert.AreEqual(1, pt.ColumnLabels.Count()); + Assert.AreEqual("Month", pt.ColumnLabels.Single().SourceName); + + var pv = pt.Values.Single(); + Assert.AreEqual("Sum of NumberOfOrders", pv.CustomName); + Assert.AreEqual("NumberOfOrders", pv.SourceName); + } + } + + /// + /// For non-English locales, the default style ("Normal" in English) can be + /// another piece of text (e.g. Обычный in Russian). + /// This test ensures that the default style is correctly detected and + /// no style conflicts occur on save. + /// + [Test] + public void CanSaveFileWithDefaultStyleNameNotInEnglish() + { + using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Misc\FileWithDefaultStyleNameNotInEnglish.xlsx"))) + using (var wb = new XLWorkbook(stream)) + { + using (var ms = new MemoryStream()) + { + wb.SaveAs(ms, true); + } + } + } + + /// + /// As per https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.cellvalues(v=office.15).aspx + /// the 'Date' DataType is available only in files saved with Microsoft Office + /// In other files, the data type will be saved as numeric + /// ClosedXML then deduces the data type by inspecting the number format string + /// + [Test] + public void CanLoadLibreOfficeFileWithDates() + { + using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Misc\LibreOfficeFileWithDates.xlsx"))) + using (var wb = new XLWorkbook(stream)) + { + var ws = wb.Worksheets.First(); + foreach (var cell in ws.CellsUsed()) + { + Assert.AreEqual(XLCellValues.DateTime, cell.DataType); + } + } + } + } +} diff --git a/ClosedXML_Tests/Excel/Misc/FormulaTests.cs b/ClosedXML_Tests/Excel/Misc/FormulaTests.cs index d9bb00a..9cfaf7e 100644 --- a/ClosedXML_Tests/Excel/Misc/FormulaTests.cs +++ b/ClosedXML_Tests/Excel/Misc/FormulaTests.cs @@ -1,12 +1,10 @@ using ClosedXML.Excel; using NUnit.Framework; using System; +using System.Linq; namespace ClosedXML_Tests.Excel { - /// - /// Summary description for UnitTest1 - /// [TestFixture] public class FormulaTests { @@ -48,6 +46,7 @@ { IXLWorksheet ws = wb.Worksheets.Add("S10 Data"); ws.Cell("A1").Value = "Some value"; + ws.Cell("A2").Value = 123; ws = wb.Worksheets.Add("Summary"); ws.Cell("A1").FormulaA1 = "='S10 Data'!A1"; @@ -58,6 +57,50 @@ ws.Cell("A1").CopyTo("B1"); Assert.AreEqual("'S10 Data'!B1", ws.Cell("B1").FormulaA1); + + ws.Cell("A3").FormulaA1 = "=SUM('S10 Data'!A2)"; + Assert.AreEqual(123, ws.Cell("A3").Value); + } + } + + [Test] + public void FormulaWithReferenceIncludingSheetName() + { + using (var wb = new XLWorkbook()) + { + object value; + var ws = wb.AddWorksheet("Sheet1"); + ws.Cell("A1").InsertData(Enumerable.Range(1, 50)); + ws.Cell("B1").FormulaA1 = "=SUM(A1:A50)"; + value = ws.Cell("B1").Value; + Assert.AreEqual(1275, value); + + ws = wb.AddWorksheet("Sheet2"); + + ws.Cell("A1").FormulaA1 = "=SUM(Sheet1!A1:Sheet1!A50)"; + value = ws.Cell("A1").Value; + Assert.AreEqual(1275, value); + + ws.Cell("B1").FormulaA1 = "=SUM(Sheet1!A1:A50)"; + value = ws.Cell("B1").Value; + Assert.AreEqual(1275, value); + } + } + + [Test] + public void InvalidReferences() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + ws.Cell("A1").InsertData(Enumerable.Range(1, 50)); + ws = wb.AddWorksheet("Sheet2"); + + ws.Cell("A1").FormulaA1 = "=SUM(Sheet1!A1:Sheet2!A50)"; + Assert.That(() => ws.Cell("A1").Value, Throws.InstanceOf()); + + ws.Cell("B1").FormulaA1 = "=SUM(Sheet1!A1:UnknownSheet!A50)"; + Assert.That(() => ws.Cell("B1").Value, Throws.InstanceOf()); } } @@ -79,5 +122,39 @@ Assert.AreEqual(actual, "B"); } } + + [Test] + public void FormulaThatReferencesEntireRow() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().Value = 1; + ws.FirstCell().CellRight().Value = 2; + ws.FirstCell().CellRight(5).Value = 3; + + ws.FirstCell().CellBelow().FormulaA1 = "=SUM(1:1)"; + + var actual = ws.FirstCell().CellBelow().Value; + Assert.AreEqual(6, actual); + } + } + + [Test] + public void FormulaThatReferencesEntireColumn() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().Value = 1; + ws.FirstCell().CellBelow().Value = 2; + ws.FirstCell().CellBelow(5).Value = 3; + + ws.FirstCell().CellRight().FormulaA1 = "=SUM(A:A)"; + + var actual = ws.FirstCell().CellRight().Value; + Assert.AreEqual(6, actual); + } + } } } diff --git a/ClosedXML_Tests/Excel/Worksheets/XLWorksheetTests.cs b/ClosedXML_Tests/Excel/Worksheets/XLWorksheetTests.cs index e5bf666..b259da7 100644 --- a/ClosedXML_Tests/Excel/Worksheets/XLWorksheetTests.cs +++ b/ClosedXML_Tests/Excel/Worksheets/XLWorksheetTests.cs @@ -1,7 +1,7 @@ -using System; -using System.Linq; -using ClosedXML.Excel; +using ClosedXML.Excel; using NUnit.Framework; +using System; +using System.Linq; namespace ClosedXML_Tests { @@ -78,7 +78,6 @@ wb.Worksheets.Add("Sheet2", 1); wb.Worksheets.Add("Sheet1", 1); - Assert.AreEqual("Sheet1", wb.Worksheet(1).Name); Assert.AreEqual("Sheet2", wb.Worksheet(2).Name); Assert.AreEqual("Sheet3", wb.Worksheet(3).Name); @@ -107,5 +106,24 @@ DateTime end = DateTime.Now; Assert.IsTrue((end - start).TotalMilliseconds < 500); } + + [Test] + public void SheetsWithCommas() + { + using (var wb = new XLWorkbook()) + { + var sourceSheetName = "Sheet1, Sheet3"; + var ws = wb.Worksheets.Add(sourceSheetName); + ws.Cell("A1").Value = 1; + ws.Cell("A2").Value = 2; + ws.Cell("B2").Value = 3; + + ws = wb.Worksheets.Add("Formula"); + ws.FirstCell().FormulaA1 = string.Format("=SUM('{0}'!A1:A2,'{0}'!B1:B2)", sourceSheetName); + + var value = ws.FirstCell().Value; + Assert.AreEqual(6, value); + } + } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Resource/Misc/LoadSheetsWithCommas.xlsx b/ClosedXML_Tests/Resource/Misc/LoadSheetsWithCommas.xlsx new file mode 100644 index 0000000..d86bd21 --- /dev/null +++ b/ClosedXML_Tests/Resource/Misc/LoadSheetsWithCommas.xlsx Binary files differ diff --git a/ClosedXML_Tests/packages.config b/ClosedXML_Tests/packages.config index dcf0b39..df4f149 100644 --- a/ClosedXML_Tests/packages.config +++ b/ClosedXML_Tests/packages.config @@ -1,5 +1,5 @@  - - + + \ No newline at end of file diff --git a/appveyor.yml b/appveyor.yml index 16d9a67..5d72b9a 100644 --- a/appveyor.yml +++ b/appveyor.yml @@ -9,6 +9,8 @@ - gh-pages # Build worker image (VM template) image: Visual Studio 2015 +environment: + AppVeyor: APPVEYOR # enable patching of AssemblyInfo.* files assembly_info: @@ -17,7 +19,7 @@ assembly_version: "{version}" assembly_file_version: "{version}" assembly_informational_version: "{version}" - + #---------------------------------# # build configuration # #---------------------------------# @@ -29,10 +31,12 @@ parallel: true # enable MSBuild parallel builds project: ClosedXML.sln # path to Visual Studio solution or project verbosity: minimal - + configuration : Release #Restore before_build: - nuget restore - +artifacts: + - path: ClosedXML/bin/Release/ClosedXML.dll + - path: ClosedXML/bin/Release/ClosedXML.xml