public static void Calculate(this ExcelWorkbook workbook, ExcelCalculationOption options)
{
Init(workbook);
var dc = DependencyChainFactory.Create(workbook, options);
workbook.FormulaParser.InitNewCalc();
if (workbook.FormulaParser.Logger != null)
{
var msg = string.Format("Starting... number of cells to parse: {0}", dc.list.Count);
workbook.FormulaParser.Logger.Log(msg);
}
//TODO: Remove when tests are done. Outputs the dc to a text file.
//var fileDc = new System.IO.StreamWriter("c:\\temp\\dc.txt");
//for (int i = 0; i < dc.list.Count; i++)
//{
// fileDc.WriteLine(i.ToString() + "," + dc.list[i].Column.ToString() + "," + dc.list[i].Row.ToString() + "," + (dc.list[i].ws==null ? "" : dc.list[i].ws.Name) + "," + dc.list[i].Formula);
//}
//fileDc.Close();
//fileDc = new System.IO.StreamWriter("c:\\temp\\dcorder.txt");
//for (int i = 0; i < dc.CalcOrder.Count; i++)
//{
// fileDc.WriteLine(dc.CalcOrder[i].ToString());
//}
//fileDc.Close();
//fileDc = null;
//TODO: Add calculation here
CalcChain(workbook, workbook.FormulaParser, dc);
//workbook._isCalculated = true;
}
internal static DependencyChain Create(ExcelWorksheet ws, ExcelCalculationOption options)
{
ws.CheckSheetType();
var depChain = new DependencyChain();
GetChain(depChain, ws.Workbook.FormulaParser.Lexer, ws.Cells, options);
return(depChain);
}
internal static DependencyChain Create(ExcelRangeBase range, ExcelCalculationOption options)
{
var depChain = new DependencyChain();
GetChain(depChain, range.Worksheet.Workbook.FormulaParser.Lexer, range, options);
return(depChain);
}
internal static DependencyChain Create(ExcelWorksheet ws, string Formula, ExcelCalculationOption options)
{
ws.CheckSheetType();
var depChain = new DependencyChain();
GetChain(depChain, ws.Workbook.FormulaParser.Lexer, ws, Formula, options);
return depChain;
}
public static void Calculate(this ExcelWorksheet worksheet, ExcelCalculationOption options)
{
Init(worksheet.Workbook);
//worksheet.Workbook._formulaParser = null; TODO:Cant reset. Don't work with userdefined or overrided worksheet functions
var parser = worksheet.Workbook.FormulaParser;
parser.InitNewCalc();
var dc = DependencyChainFactory.Create(worksheet, options);
CalcChain(worksheet.Workbook, parser, dc);
}
private static void GetWorksheetNames(ExcelWorksheet ws, DependencyChain depChain, ExcelCalculationOption options)
{
foreach (var name in ws.Names)
{
if (!string.IsNullOrEmpty(name.NameFormula))
{
GetChain(depChain, ws.Workbook.FormulaParser.Lexer, name, options);
}
}
}
public static void Calculate(this ExcelWorkbook workbook, ExcelCalculationOption options)
{
Init(workbook);
var dc = DependencyChainFactory.Create(workbook, options);
workbook._formulaParser = null;
var parser = workbook.FormulaParser;
CalcChain(workbook, parser, dc);
//workbook._isCalculated = true;
}
internal static DependencyChain Create(ExcelWorkbook wb, ExcelCalculationOption options)
{
var depChain = new DependencyChain();
foreach (var ws in wb.Worksheets)
{
if (!(ws is ExcelChartsheet))
{
GetChain(depChain, wb.FormulaParser.Lexer, ws.Cells, options);
}
}
return(depChain);
}
public static void Calculate(this ExcelWorksheet worksheet, ExcelCalculationOption options)
{
Init(worksheet.Workbook);
//worksheet.Workbook._formulaParser = null; TODO:Cant reset. Don't work with userdefined or overrided worksheet functions
var dc = DependencyChainFactory.Create(worksheet, options);
var parser = worksheet.Workbook.FormulaParser;
parser.InitNewCalc();
if (parser.Logger != null)
{
var msg = string.Format("Starting... number of cells to parse: {0}", dc.list.Count);
parser.Logger.Log(msg);
}
CalcChain(worksheet.Workbook, parser, dc);
}
internal static DependencyChain Create(ExcelWorkbook wb, ExcelCalculationOption options)
{
var depChain = new DependencyChain();
foreach (var ws in wb.Worksheets)
{
if (!(ws is ExcelChartsheet))
{
GetChain(depChain, wb.FormulaParser.Lexer, ws.Cells, options);
GetWorksheetNames(ws, depChain, options);
}
}
foreach (var name in wb.Names)
{
if (name.NameValue==null)
{
GetChain(depChain, wb.FormulaParser.Lexer, name, options);
}
}
return depChain;
}
/// <summary>
/// This method follows the calculation chain to get the order of the calculation
/// Goto (!) is used internally to prevent stackoverflow on extremly larget dependency trees (that is, many recursive formulas).
/// </summary>
/// <param name="depChain">The dependency chain object</param>
/// <param name="lexer">The formula tokenizer</param>
/// <param name="wb">The workbook where the formula comes from</param>
/// <param name="ws">The worksheet where the formula comes from</param>
/// <param name="f">The cell function object</param>
/// <param name="options">Calcultaiton options</param>
private static void FollowChain(DependencyChain depChain, ILexer lexer, ExcelWorkbook wb, ExcelWorksheet ws, FormulaCell f, ExcelCalculationOption options)
{
Stack<FormulaCell> stack = new Stack<FormulaCell>();
iterateToken:
while (f.tokenIx < f.Tokens.Count)
{
var t = f.Tokens[f.tokenIx];
if (t.TokenType == TokenType.ExcelAddress)
{
var adr = new ExcelFormulaAddress(t.Value);
if (adr.Table != null)
{
adr.SetRCFromTable(ws._package, new ExcelAddressBase(f.Row, f.Column, f.Row, f.Column));
}
if (adr.WorkSheet == null && adr.Collide(new ExcelAddressBase(f.Row, f.Column, f.Row, f.Column))!=ExcelAddressBase.eAddressCollition.No)
{
throw (new CircularReferenceException(string.Format("Circular Reference in cell {0}", ExcelAddressBase.GetAddress(f.Row, f.Column))));
}
if (adr._fromRow > 0 && adr._fromCol > 0)
{
if (string.IsNullOrEmpty(adr.WorkSheet))
{
if (f.ws == null)
{
f.ws = ws;
}
else if (f.ws.SheetID != f.SheetID)
{
f.ws = wb.Worksheets.GetBySheetID(f.SheetID);
}
}
else
{
f.ws = wb.Worksheets[adr.WorkSheet];
}
if (f.ws != null)
{
f.iterator = new CellsStoreEnumerator<object>(f.ws._formulas, adr.Start.Row, adr.Start.Column, adr.End.Row, adr.End.Column);
goto iterateCells;
}
}
}
else if (t.TokenType == TokenType.NameValue)
{
string adrWb, adrWs, adrName;
ExcelNamedRange name;
ExcelAddressBase.SplitAddress(t.Value, out adrWb, out adrWs, out adrName, f.ws==null ? "" : f.ws.Name);
if (!string.IsNullOrEmpty(adrWs))
{
if (f.ws == null)
{
f.ws = wb.Worksheets[adrWs];
}
if(f.ws.Names.ContainsKey(t.Value))
{
name = f.ws.Names[adrName];
}
else if (wb.Names.ContainsKey(adrName))
{
name = wb.Names[adrName];
}
else
{
name = null;
}
if(name != null) f.ws = name.Worksheet;
}
else if (wb.Names.ContainsKey(adrName))
{
name = wb.Names[t.Value];
if (string.IsNullOrEmpty(adrWs))
{
f.ws = name.Worksheet;
}
}
else
{
name = null;
}
if (name != null)
{
if (string.IsNullOrEmpty(name.NameFormula))
{
if (name.NameValue == null)
{
f.iterator = new CellsStoreEnumerator<object>(f.ws._formulas, name.Start.Row,
name.Start.Column, name.End.Row, name.End.Column);
goto iterateCells;
}
}
else
{
var id = ExcelAddressBase.GetCellID(name.LocalSheetId, name.Index, 0);
if (!depChain.index.ContainsKey(id))
{
var rf = new FormulaCell() { SheetID = name.LocalSheetId, Row = name.Index, Column = 0 };
rf.Formula = name.NameFormula;
rf.Tokens = name.LocalSheetId == -1 ? lexer.Tokenize(rf.Formula).ToList() : lexer.Tokenize(rf.Formula, wb.Worksheets.GetBySheetID(name.LocalSheetId).Name).ToList();
depChain.Add(rf);
stack.Push(f);
f = rf;
goto iterateToken;
}
else
{
if (stack.Count > 0)
{
//Check for circular references
foreach (var par in stack)
{
if (ExcelAddressBase.GetCellID(par.SheetID, par.Row, par.Column) == id)
{
throw (new CircularReferenceException(string.Format("Circular Reference in name {0}", name.Name)));
}
}
}
}
}
}
}
f.tokenIx++;
}
depChain.CalcOrder.Add(f.Index);
if (stack.Count > 0)
{
f = stack.Pop();
goto iterateCells;
}
return;
iterateCells:
while (f.iterator != null && f.iterator.Next())
{
var v = f.iterator.Value;
if (v == null || v.ToString().Trim() == "") continue;
var id = ExcelAddressBase.GetCellID(f.ws.SheetID, f.iterator.Row, f.iterator.Column);
if (!depChain.index.ContainsKey(id))
{
var rf = new FormulaCell() { SheetID = f.ws.SheetID, Row = f.iterator.Row, Column = f.iterator.Column };
if (f.iterator.Value is int)
{
rf.Formula = f.ws._sharedFormulas[(int)v].GetFormula(f.iterator.Row, f.iterator.Column, ws.Name);
}
else
{
rf.Formula = v.ToString();
}
rf.ws = f.ws;
rf.Tokens = lexer.Tokenize(rf.Formula, f.ws.Name).ToList();
ws._formulaTokens.SetValue(rf.Row, rf.Column, rf.Tokens);
depChain.Add(rf);
stack.Push(f);
f = rf;
goto iterateToken;
}
else
{
if (stack.Count > 0)
{
//Check for circular references
foreach (var par in stack)
{
if (ExcelAddressBase.GetCellID(par.ws.SheetID, par.iterator.Row, par.iterator.Column) == id)
{
if (options.AllowCirculareReferences == false)
{
throw (new CircularReferenceException(string.Format("Circular Reference in cell {0}!{1}", par.ws.Name, ExcelAddress.GetAddress(f.Row, f.Column))));
}
else
{
f = stack.Pop();
goto iterateCells;
}
}
}
}
}
}
f.tokenIx++;
goto iterateToken;
}
private static void GetChain(DependencyChain depChain, ILexer lexer, ExcelRangeBase Range, ExcelCalculationOption options)
{
var ws = Range.Worksheet;
var fs = new CellsStoreEnumerator<object>(ws._formulas, Range.Start.Row, Range.Start.Column, Range.End.Row, Range.End.Column);
while (fs.Next())
{
if (fs.Value == null || fs.Value.ToString().Trim() == "") continue;
var id = ExcelCellBase.GetCellID(ws.SheetID, fs.Row, fs.Column);
if (!depChain.index.ContainsKey(id))
{
var f = new FormulaCell() { SheetID = ws.SheetID, Row = fs.Row, Column = fs.Column };
if (fs.Value is int)
{
f.Formula = ws._sharedFormulas[(int)fs.Value].GetFormula(fs.Row, fs.Column, ws.Name);
}
else
{
f.Formula = fs.Value.ToString();
}
if (!string.IsNullOrEmpty(f.Formula))
{
f.Tokens = lexer.Tokenize(f.Formula, Range.Worksheet.Name).ToList();
ws._formulaTokens.SetValue(fs.Row, fs.Column, f.Tokens);
depChain.Add(f);
FollowChain(depChain, lexer, ws.Workbook, ws, f, options);
}
}
}
}
private static void GetChain(DependencyChain depChain, ILexer lexer, ExcelWorksheet ws, string formula, ExcelCalculationOption options)
{
var f = new FormulaCell() { SheetID = ws.SheetID, Row = -1, Column = -1 };
f.Formula = formula;
if (!string.IsNullOrEmpty(f.Formula))
{
f.Tokens = lexer.Tokenize(f.Formula, ws.Name).ToList();
depChain.Add(f);
FollowChain(depChain, lexer, ws.Workbook, ws, f, options);
}
}
private static void GetChain(DependencyChain depChain, ILexer lexer, ExcelNamedRange name, ExcelCalculationOption options)
{
var ws = name.Worksheet;
var id = ExcelCellBase.GetCellID(ws==null?0:ws.SheetID, name.Index, 0);
if (!depChain.index.ContainsKey(id))
{
var f = new FormulaCell() { SheetID = ws == null ? 0 : ws.SheetID, Row = name.Index, Column = 0, Formula=name.NameFormula };
if (!string.IsNullOrEmpty(f.Formula))
{
f.Tokens = lexer.Tokenize(f.Formula, (ws==null ? null : ws.Name)).ToList();
if (ws == null)
{
name._workbook._formulaTokens.SetValue(name.Index, 0, f.Tokens);
}
else
{
ws._formulaTokens.SetValue(name.Index, 0, f.Tokens);
}
depChain.Add(f);
FollowChain(depChain, lexer,name._workbook, ws, f, options);
}
}
}
public static void Calculate(this ExcelRangeBase range, ExcelCalculationOption options)
{
Init(range._workbook);
var parser = range._workbook.FormulaParser;
parser.InitNewCalc();
var dc = DependencyChainFactory.Create(range, options);
CalcChain(range._workbook, parser, dc);
}
private static void GetWorksheetNames(ExcelWorksheet ws, DependencyChain depChain, ExcelCalculationOption options)
{
foreach (var name in ws.Names)
{
if (!string.IsNullOrEmpty(name.NameFormula))
{
GetChain(depChain, ws.Workbook.FormulaParser.Lexer, name, options);
}
}
}
/// <summary>
/// This method follows the calculation chain to get the order of the calculation
/// Goto (!) is used internally to prevent stackoverflow on extremly larget dependency trees (that is, many recursive formulas).
/// </summary>
/// <param name="depChain">The dependency chain object</param>
/// <param name="lexer">The formula tokenizer</param>
/// <param name="wb">The workbook where the formula comes from</param>
/// <param name="ws">The worksheet where the formula comes from</param>
/// <param name="f">The cell function object</param>
/// <param name="options">Calcultaiton options</param>
private static void FollowChain(DependencyChain depChain, ILexer lexer, ExcelWorkbook wb, ExcelWorksheet ws, FormulaCell f, ExcelCalculationOption options)
{
Stack <FormulaCell> stack = new Stack <FormulaCell>();
iterateToken:
while (f.tokenIx < f.Tokens.Count)
{
var t = f.Tokens[f.tokenIx];
if (t.TokenType == TokenType.ExcelAddress)
{
var adr = new ExcelFormulaAddress(t.Value);
if (adr.Table != null)
{
adr.SetRCFromTable(ws._package, new ExcelAddressBase(f.Row, f.Column, f.Row, f.Column));
}
if (adr.WorkSheet == null && adr.Collide(new ExcelAddressBase(f.Row, f.Column, f.Row, f.Column)) != ExcelAddressBase.eAddressCollition.No && !options.AllowCirculareReferences)
{
throw (new CircularReferenceException(string.Format("Circular Reference in cell {0}", ExcelAddressBase.GetAddress(f.Row, f.Column))));
}
if (adr._fromRow > 0 && adr._fromCol > 0)
{
if (string.IsNullOrEmpty(adr.WorkSheet))
{
if (f.ws == null)
{
f.ws = ws;
}
else if (f.ws.SheetID != f.SheetID)
{
f.ws = wb.Worksheets.GetBySheetID(f.SheetID);
}
}
else
{
f.ws = wb.Worksheets[adr.WorkSheet];
}
if (f.ws != null)
{
f.iterator = new CellsStoreEnumerator <object>(f.ws._formulas, adr.Start.Row, adr.Start.Column, adr.End.Row, adr.End.Column);
goto iterateCells;
}
}
}
else if (t.TokenType == TokenType.NameValue)
{
string adrWb, adrWs, adrName;
ExcelNamedRange name;
ExcelAddressBase.SplitAddress(t.Value, out adrWb, out adrWs, out adrName, f.ws == null ? "" : f.ws.Name);
if (!string.IsNullOrEmpty(adrWs))
{
if (f.ws == null)
{
f.ws = wb.Worksheets[adrWs];
}
if (f.ws.Names.ContainsKey(t.Value))
{
name = f.ws.Names[adrName];
}
else if (wb.Names.ContainsKey(adrName))
{
name = wb.Names[adrName];
}
else
{
name = null;
}
if (name != null)
{
f.ws = name.Worksheet;
}
}
else if (wb.Names.ContainsKey(adrName))
{
name = wb.Names[t.Value];
f.ws = name.Worksheet;
}
else
{
name = null;
}
if (name != null)
{
if (string.IsNullOrEmpty(name.NameFormula))
{
if (name.NameValue == null)
{
f.iterator = new CellsStoreEnumerator <object>(f.ws._formulas, name.Start.Row,
name.Start.Column, name.End.Row, name.End.Column);
goto iterateCells;
}
}
else
{
var id = ExcelAddressBase.GetCellID(name.LocalSheetId, name.Index, 0);
if (!depChain.index.ContainsKey(id))
{
var rf = new FormulaCell()
{
SheetID = name.LocalSheetId, Row = name.Index, Column = 0
};
rf.Formula = name.NameFormula;
rf.Tokens = name.LocalSheetId == -1 ? lexer.Tokenize(rf.Formula).ToList() : lexer.Tokenize(rf.Formula, wb.Worksheets.GetBySheetID(name.LocalSheetId).Name).ToList();
depChain.Add(rf);
stack.Push(f);
f = rf;
goto iterateToken;
}
else
{
if (stack.Count > 0)
{
//Check for circular references
foreach (var par in stack)
{
if (ExcelAddressBase.GetCellID(par.SheetID, par.Row, par.Column) == id && !options.AllowCirculareReferences)
{
throw (new CircularReferenceException(string.Format("Circular Reference in name {0}", name.Name)));
}
}
}
}
}
}
}
f.tokenIx++;
}
depChain.CalcOrder.Add(f.Index);
if (stack.Count > 0)
{
f = stack.Pop();
goto iterateCells;
}
return;
iterateCells:
while (f.iterator != null && f.iterator.Next())
{
var v = f.iterator.Value;
if (v == null || v.ToString().Trim() == "")
{
continue;
}
var id = ExcelAddressBase.GetCellID(f.ws.SheetID, f.iterator.Row, f.iterator.Column);
if (!depChain.index.ContainsKey(id))
{
var rf = new FormulaCell()
{
SheetID = f.ws.SheetID, Row = f.iterator.Row, Column = f.iterator.Column
};
if (f.iterator.Value is int)
{
rf.Formula = f.ws._sharedFormulas[(int)v].GetFormula(f.iterator.Row, f.iterator.Column, ws.Name);
}
else
{
rf.Formula = v.ToString();
}
rf.ws = f.ws;
rf.Tokens = lexer.Tokenize(rf.Formula, f.ws.Name).ToList();
ws._formulaTokens.SetValue(rf.Row, rf.Column, rf.Tokens);
depChain.Add(rf);
stack.Push(f);
f = rf;
goto iterateToken;
}
else
{
if (stack.Count > 0)
{
//Check for circular references
foreach (var par in stack)
{
if (ExcelAddressBase.GetCellID(par.ws.SheetID, par.iterator.Row, par.iterator.Column) == id)
{
if (options.AllowCirculareReferences == false)
{
throw (new CircularReferenceException(string.Format("Circular Reference in cell {0}!{1}", par.ws.Name, ExcelAddress.GetAddress(f.Row, f.Column))));
}
else
{
f = stack.Pop();
goto iterateCells;
}
}
}
}
}
}
f.tokenIx++;
goto iterateToken;
}
private static void GetChain(DependencyChain depChain, ILexer lexer, ExcelRangeBase Range, ExcelCalculationOption options)
{
var ws = Range.Worksheet;
var fs = new CellsStoreEnumerator <object>(ws._formulas, Range.Start.Row, Range.Start.Column, Range.End.Row, Range.End.Column);
while (fs.Next())
{
if (fs.Value == null || fs.Value.ToString().Trim() == "")
{
continue;
}
var id = ExcelCellBase.GetCellID(ws.SheetID, fs.Row, fs.Column);
if (!depChain.index.ContainsKey(id))
{
var f = new FormulaCell()
{
SheetID = ws.SheetID, Row = fs.Row, Column = fs.Column
};
if (fs.Value is int)
{
f.Formula = ws._sharedFormulas[(int)fs.Value].GetFormula(fs.Row, fs.Column, ws.Name);
}
else
{
f.Formula = fs.Value.ToString();
}
if (!string.IsNullOrEmpty(f.Formula))
{
f.Tokens = lexer.Tokenize(f.Formula, Range.Worksheet.Name).ToList();
ws._formulaTokens.SetValue(fs.Row, fs.Column, f.Tokens);
depChain.Add(f);
FollowChain(depChain, lexer, ws.Workbook, ws, f, options);
}
}
}
}
private static void GetChain(DependencyChain depChain, ILexer lexer, ExcelWorksheet ws, string formula, ExcelCalculationOption options)
{
var f = new FormulaCell()
{
SheetID = ws.SheetID, Row = -1, Column = -1
};
f.Formula = formula;
if (!string.IsNullOrEmpty(f.Formula))
{
f.Tokens = lexer.Tokenize(f.Formula, ws.Name).ToList();
depChain.Add(f);
FollowChain(depChain, lexer, ws.Workbook, ws, f, options);
}
}
private static void GetChain(DependencyChain depChain, ILexer lexer, ExcelNamedRange name, ExcelCalculationOption options)
{
var ws = name.Worksheet;
var id = ExcelCellBase.GetCellID(ws == null?0:ws.SheetID, name.Index, 0);
if (!depChain.index.ContainsKey(id))
{
var f = new FormulaCell()
{
SheetID = ws == null ? 0 : ws.SheetID, Row = name.Index, Column = 0, Formula = name.NameFormula
};
if (!string.IsNullOrEmpty(f.Formula))
{
f.Tokens = lexer.Tokenize(f.Formula, (ws == null ? null : ws.Name)).ToList();
if (ws == null)
{
name._workbook._formulaTokens.SetValue(name.Index, 0, f.Tokens);
}
else
{
ws._formulaTokens.SetValue(name.Index, 0, f.Tokens);
}
depChain.Add(f);
FollowChain(depChain, lexer, name._workbook, ws, f, options);
}
}
}
public static object Calculate(this ExcelWorksheet worksheet, string Formula, ExcelCalculationOption options)
{
try
{
worksheet.CheckSheetType();
if(string.IsNullOrEmpty(Formula.Trim())) return null;
Init(worksheet.Workbook);
var parser = worksheet.Workbook.FormulaParser;
parser.InitNewCalc();
if (Formula[0] == '=') Formula = Formula.Substring(1); //Remove any starting equal sign
var dc = DependencyChainFactory.Create(worksheet, Formula, options);
var f = dc.list[0];
dc.CalcOrder.RemoveAt(dc.CalcOrder.Count - 1);
CalcChain(worksheet.Workbook, parser, dc);
return parser.ParseCell(f.Tokens, worksheet.Name, -1, -1);
}
catch (Exception ex)
{
return new ExcelErrorValueException(ex.Message, ExcelErrorValue.Create(eErrorType.Value));
}
}
public static void Calculate(this ExcelWorksheet worksheet, ExcelCalculationOption options)
{
Init(worksheet.Workbook);
var parser = worksheet.Workbook.FormulaParser;
var dc = DependencyChainFactory.Create(worksheet, options);
CalcChain(worksheet.Workbook, parser, dc);
}
internal static DependencyChain Create(ExcelRangeBase range, ExcelCalculationOption options)
{
var depChain = new DependencyChain();
GetChain(depChain, range.Worksheet.Workbook.FormulaParser.Lexer, range, options);
return depChain;
}
/// <summary>
/// This method follows the calculation chain to get the order of the calculation
/// Goto (!) is used internally to prevent stackoverflow on extremly large dependency trees (that is, many recursive formulas).
/// </summary>
/// <param name="depChain">The dependency chain object</param>
/// <param name="lexer">The formula tokenizer</param>
/// <param name="wb">The workbook where the formula comes from</param>
/// <param name="ws">The worksheet where the formula comes from</param>
/// <param name="f">The cell function object</param>
/// <param name="options">Calcultaiton options</param>
private static void FollowChain(DependencyChain depChain, ILexer lexer, ExcelWorkbook wb, ExcelWorksheet ws, FormulaCell f, ExcelCalculationOption options)
{
Stack <FormulaCell> stack = new Stack <FormulaCell>();
iterateToken:
while (f.tokenIx < f.Tokens.Count)
{
var t = f.Tokens[f.tokenIx];
if (t.TokenType == TokenType.ExcelAddress)
{
var adr = new ExcelFormulaAddress(t.Value);
if (adr.IsTableAddress)
{
adr.SetRCFromTable(ws.Package, new ExcelAddress(f.Row, f.Column, f.Row, f.Column));
}
if (adr.WorkSheet == null && adr.Collide(new ExcelAddress(f.Row, f.Column, f.Row, f.Column)) != ExcelAddress.eAddressCollition.No && !options.AllowCircularReferences)
{
throw (new CircularReferenceException(string.Format("Circular Reference in cell {0}", ExcelAddress.GetAddress(f.Row, f.Column))));
}
if (adr._fromRow > 0 && adr._fromCol > 0)
{
if (string.IsNullOrEmpty(adr.WorkSheet))
{
if (f.ws == null)
{
f.ws = ws;
}
else if (f.ws.SheetID != f.SheetID)
{
f.ws = wb.Worksheets.GetBySheetID(f.SheetID);
}
}
else
{
f.ws = wb.Worksheets[adr.WorkSheet];
}
if (f.ws != null)
{
f.iterator = f.ws._formulas.GetEnumerator(adr.Start.Row, adr.Start.Column, adr.End.Row, adr.End.Column);
goto iterateCells;
}
}
}
else if (t.TokenType == TokenType.NameValue)
{
ExcelNamedRange name = null;
var worksheet = f.ws ?? ws;
// Worksheet-scoped named ranges take precedence over workbook-scoped named ranges.
if (worksheet?.Names?.ContainsKey(t.Value) == true)
{
name = worksheet.Names[t.Value];
}
else if (wb.Names.ContainsKey(t.Value))
{
name = wb.Names[t.Value];
}
if (name != null)
{
var nameFormulaTokens = name.GetRelativeNameFormula(f.Row, f.Column)?.ToList();
if (nameFormulaTokens.Count == 0 && !string.IsNullOrEmpty(name.NameFormula))
{
nameFormulaTokens = name.Workbook.FormulaParser.Lexer.Tokenize(name.NameFormula)?.ToList();
}
// Remove the current named range token and replace it with the named range's formula.
f.Tokens.RemoveAt(f.tokenIx);
f.Tokens.InsertRange(f.tokenIx, nameFormulaTokens);
goto iterateToken;
}
}
else if (t.TokenType == TokenType.Function && t.Value.IsEquivalentTo(Offset.Name))
{
var stringBuilder = new StringBuilder($"{OffsetAddress.Name}(");
int offsetStartIndex = f.tokenIx;
int parenCount = 1;
for (f.tokenIx += 2; parenCount > 0 && f.tokenIx < f.Tokens.Count; f.tokenIx++)
{
var token = f.Tokens[f.tokenIx];
stringBuilder.Append(token.Value);
if (token.TokenType == TokenType.OpeningParenthesis)
{
parenCount++;
}
else if (token.TokenType == TokenType.ClosingParenthesis)
{
parenCount--;
}
}
ExcelRange cell = ws.Cells[f.Row, f.Column];
string originalFormula = cell.Formula;
string addressOffsetFormula = stringBuilder.ToString();
stringBuilder.Clear();
for (int i = 0; i < f.Tokens.Count; i++)
{
if (i == offsetStartIndex)
{
stringBuilder.Append(0);
}
else if (i < offsetStartIndex || i >= f.tokenIx)
{
stringBuilder.Append(f.Tokens[i].Value);
}
}
cell.Formula = stringBuilder.ToString();
var offsetResult = ws.Calculate(addressOffsetFormula, f.Row, f.Column);
cell.Formula = originalFormula;
if (offsetResult is string resultString)
{
ExcelAddress adr = new ExcelAddress(resultString);
var worksheet = string.IsNullOrEmpty(adr.WorkSheet) ? ws : wb.Worksheets[adr.WorkSheet];
// Only complete the OFFSET's dependency chain if a valid existing address was successfully parsed.
if (worksheet != null)
{
f.Tokens.RemoveRange(offsetStartIndex, f.tokenIx - offsetStartIndex);
var offsetResultTokens = wb.FormulaParser.Lexer.Tokenize(adr.FullAddress);
f.Tokens.InsertRange(offsetStartIndex, offsetResultTokens);
f.ws = worksheet;
f.iterator = f.ws._formulas.GetEnumerator(adr.Start.Row, adr.Start.Column, adr.End.Row, adr.End.Column);
goto iterateCells;
}
}
}
f.tokenIx++;
}
depChain.CalcOrder.Add(f.Index);
if (stack.Count > 0)
{
f = stack.Pop();
goto iterateCells;
}
return;
iterateCells:
while (f.iterator != null && f.iterator.MoveNext())
{
var v = f.iterator.Value;
if (v == null || v.ToString().Trim() == "")
{
continue;
}
var id = ExcelAddress.GetCellID(f.ws.SheetID, f.iterator.Row, f.iterator.Column);
if (!depChain.Index.ContainsKey(id))
{
var rf = new FormulaCell()
{
SheetID = f.ws.SheetID, Row = f.iterator.Row, Column = f.iterator.Column
};
if (f.iterator.Value is int)
{
rf.Formula = f.ws._sharedFormulas[(int)v].GetFormula(f.iterator.Row, f.iterator.Column, ws.Name);
}
else
{
rf.Formula = v.ToString();
}
rf.ws = f.ws;
rf.Tokens = lexer.Tokenize(rf.Formula, f.ws.Name).ToList();
ws._formulaTokens.SetValue(rf.Row, rf.Column, rf.Tokens);
depChain.Add(rf);
stack.Push(f);
f = rf;
goto iterateToken;
}
else
{
if (stack.Count > 0)
{
//Check for circular references
foreach (var par in stack)
{
if (ExcelAddress.GetCellID(par.ws.SheetID, par.iterator.Row, par.iterator.Column) == id ||
ExcelAddress.GetCellID(par.ws.SheetID, par.Row, par.Column) == id)
{
if (options.AllowCircularReferences == false)
{
throw (new CircularReferenceException(string.Format("Circular Reference in cell {0}!{1}", par.ws.Name, ExcelAddress.GetAddress(f.Row, f.Column))));
}
else
{
f = stack.Pop();
goto iterateCells;
}
}
}
}
}
}
f.tokenIx++;
goto iterateToken;
}