diff --git a/ClosedXML/Excel/CalcEngine/CalcEngine.cs b/ClosedXML/Excel/CalcEngine/CalcEngine.cs
index a392dc1..290208c 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,15 +606,20 @@
}
// 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);
}
@@ -533,9 +633,11 @@
}
// 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 +646,8 @@
}
// check that we got the end of the string
- if (c != '\"') {
+ if (c != '\"')
+ {
Throw("Can't find final quote.");
}
@@ -556,15 +659,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 +682,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 +722,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 +750,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 +760,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 +792,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 Exception(msg);
}
- #endregion
+ #endregion ** static helpers
}
///
diff --git a/ClosedXML/Excel/CalcEngine/XLCalcEngine.cs b/ClosedXML/Excel/CalcEngine/XLCalcEngine.cs
index eb359ac..e7457e2 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;
}
@@ -35,14 +36,13 @@
return identifier;
}
-
-
}
internal class CellRangeReference : IValueObject, IEnumerable
{
private IXLRange _range;
private XLCalcEngine _ce;
+
public CellRangeReference(IXLRange range, XLCalcEngine ce)
{
_range = range;
@@ -66,7 +66,7 @@
private Boolean _evaluating;
// ** implementation
- object GetValue(IXLCell cell)
+ private object GetValue(IXLCell cell)
{
if (_evaluating)
{
@@ -80,7 +80,6 @@
return cell.Value;
else
return new XLCalcEngine(cell.Worksheet).Evaluate(f);
-
}
finally
{
diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs
index 60aec68..76c27ec 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)
diff --git a/ClosedXML/Excel/XLWorksheets.cs b/ClosedXML/Excel/XLWorksheets.cs
index 673328a..ee17781 100644
--- a/ClosedXML/Excel/XLWorksheets.cs
+++ b/ClosedXML/Excel/XLWorksheets.cs
@@ -59,6 +59,9 @@
public IXLWorksheet Worksheet(String sheetName)
{
XLWorksheet w;
+ if (sheetName.StartsWith("'") && sheetName.EndsWith("'") && sheetName.Length > 2)
+ sheetName = sheetName.Substring(1, sheetName.Length - 2);
+
if (_worksheets.TryGetValue(sheetName, out w))
return w;
@@ -167,4 +170,4 @@
_worksheets.Add(newSheetName, ws);
}
}
-}
\ No newline at end of file
+}
diff --git a/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML_Tests/ClosedXML_Tests.csproj
index eabbee3..9eac192 100644
--- a/ClosedXML_Tests/ClosedXML_Tests.csproj
+++ b/ClosedXML_Tests/ClosedXML_Tests.csproj
@@ -261,6 +261,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..4f4b5ff 100644
--- a/ClosedXML_Tests/Excel/Misc/FormulaTests.cs
+++ b/ClosedXML_Tests/Excel/Misc/FormulaTests.cs
@@ -48,6 +48,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 +59,9 @@
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);
}
}
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