public static void Calculate_V2(this ExcelWorkbook workbook, ExcelCalculationOption options)
{
Init(workbook);
var dc = DependencyChainFactory.Create(workbook, options);
workbook._formulaParser = null;
var parser = workbook.FormulaParser;
//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_V2(workbook, parser, dc);
workbook._isCalculated = true;
}
/// <summary>
/// Calculate all formulas in the current workbook
/// </summary>
/// <param name="workbook">The workbook to calculate</param>
/// <param name="configHandler">Configuration handler</param>
/// <example>
/// <code>
/// workbook.Calculate(opt => opt.PrecisionAndRoundingStrategy = PrecisionAndRoundingStrategy.Excel);
/// </code>
/// </example>
public static void Calculate(this ExcelWorkbook workbook, Action <ExcelCalculationOption> configHandler)
{
var option = new ExcelCalculationOption();
configHandler.Invoke(option);
Calculate(workbook, option);
}
/// <summary>
/// Calculate all formulas in the current range
/// </summary>
/// <param name="range">The range to calculate</param>
/// <param name="configHandler">Configuration handler</param>
/// <example>
/// <code>
/// sheet.Cells["A1:A3"].Calculate(opt => opt.PrecisionAndRoundingStrategy = PrecisionAndRoundingStrategy.Excel);
/// </code>
/// </example>
public static void Calculate(this ExcelRangeBase range, Action <ExcelCalculationOption> configHandler)
{
var option = new ExcelCalculationOption();
configHandler.Invoke(option);
Calculate(range, option);
}
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;
}
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);
}
public static void Calculate(this ExcelRangeBase range, ExcelCalculationOption options)
{
Init(range._workbook);
var parser = range._workbook.FormulaParser;
var dc = DependencyChainFactory.Create(range, options);
CalcChain(range._workbook, parser, dc);
}
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;
var dc = DependencyChainFactory.Create(worksheet, options);
CalcChain(worksheet.Workbook, parser, dc);
}
public ExcelCalculator(NL_Model nlModel)
{
NL_Model = nlModel;
using (ExcelPackage = new ExcelPackage(new FileInfo("D:\\Excel\\Calculator.xlsx"))) {
Worksheet = ExcelPackage.Workbook.Worksheets[1];
//Loan Amount:
Worksheet.Cells["D5"].Value = nlModel.Loan.FirstHistory()
.Amount.ToString(CultureInfo.InvariantCulture);
//Annual Interest Rate:
Worksheet.Cells["D6"].Value = nlModel.Loan.LastHistory()
.InterestRate.ToString(CultureInfo.InvariantCulture);
//Loan Length in Years
Worksheet.Cells["D7"].Value = 1;
//Number of Payments per Year:
Worksheet.Cells["D8"].Value = 12;
//Start Date of Loan:
Worksheet.Cells["D9"].Value = "01-02-2016";
//Optional Extra Per Month Payment:
Worksheet.Cells["D10"].Value = "10";
//var calcOptions = new ExcelCalculationOption() {
// AllowCirculareReferences = true
//};
//Worksheet.Calculate(calcOptions);
var calcOptions = new ExcelCalculationOption()
{
AllowCirculareReferences = true
};
Worksheet.Cells[5, 9].Formula = "=IF(IF(D5*D6*D7*D9>0,1,0),-PMT(D6/D8,D7*D8,D5),\"\")";
Worksheet.Cells[5, 9].Calculate();
ExcelPackage.Save();
}
ExcelPackage.Dispose();
Worksheet.Dispose();
ExcelPackage = new ExcelPackage(new FileInfo("D:\\Excel\\Calculator.xlsx"));
Worksheet = ExcelPackage.Workbook.Worksheets[1];
}
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;
}
/// <summary>
/// Recalculate this <paramref name="range"/> with the specified <paramref name="options"/>.
/// </summary>
/// <param name="range">The range to be calculated.</param>
/// <param name="options">Settings for this calculation.</param>
/// <param name="setResultStyle">Indicates whether or not to set the cell's style based on the calculation result.</param>
public static void Calculate(this ExcelRangeBase range, ExcelCalculationOption options, bool setResultStyle = false)
{
Init(range.myWorkbook);
var parser = range.myWorkbook.FormulaParser;
parser.InitNewCalc();
if (range.IsName)
{
range = AddressUtility.GetFormulaAsCellRange(range.Worksheet.Workbook, range.Worksheet, range.Address);
}
var dc = DependencyChainFactory.Create(range, options);
CalcChain(range.myWorkbook, parser, dc, setResultStyle);
}
/// <summary>
/// Recalculate this <paramref name="worksheet"/> with the specified <paramref name="options"/>.
/// </summary>
/// <param name="worksheet">The worksheet to calculate.</param>
/// <param name="options">The calculation options (whether or not circular references are allowed). </param>
public static void Calculate(this ExcelWorksheet worksheet, ExcelCalculationOption options)
{
Init(worksheet.Workbook);
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);
}
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);
}
public void CircularRef_In_Sum_BetweenTwoCells_ShouldThow_WhenAllow()
{
using (var package = new ExcelPackage())
{
var sheet = package.Workbook.Worksheets.Add("test");
sheet.Cells["A1"].Value = 1;
sheet.Cells["A2"].Formula = "B2";
sheet.Cells["B2"].Formula = "A2";
sheet.Cells["A3"].Formula = "SUM(A1:A2)";
var calcOptions = new ExcelCalculationOption {
AllowCircularReferences = true
};
sheet.Calculate(calcOptions);
Assert.AreEqual(1d, sheet.Cells["A3"].Value);
}
}
public void IfShouldIgnoreCircularRefWhenIgnoredArg()
{
using (var package = new ExcelPackage())
{
var sheet = package.Workbook.Worksheets.Add("test");
sheet.Cells["A1"].Value = 2;
sheet.Cells["B2"].Formula = "B2";
sheet.Cells["B3"].Value = 6;
sheet.Cells["B4"].Formula = "IF(A1<>2, B2, B3)";
var calcOptions = new ExcelCalculationOption {
AllowCircularReferences = true
};
sheet.Calculate(calcOptions);
Assert.AreEqual(6, sheet.Cells["B4"].Value);
}
}
public void VLookupShouldNotThrowWhenCircularRefsAllowed()
{
using (var package = new ExcelPackage())
{
var sheet = package.Workbook.Worksheets.Add("test");
sheet.Cells["A1"].Value = 2;
sheet.Cells["A2"].Value = 2;
sheet.Cells["A3"].Value = 3;
sheet.Cells["B1"].Value = 4;
sheet.Cells["B2"].Formula = "B2";
sheet.Cells["B3"].Value = 6;
sheet.Cells["B4"].Formula = "VLOOKUP(3, A1:B3, 2)";
var calcOptions = new ExcelCalculationOption {
AllowCircularReferences = true
};
sheet.Calculate(calcOptions);
Assert.AreEqual(6, sheet.Cells["B4"].Value);
}
}
public void CalculateWithSetStyle()
{
using (var package = new ExcelPackage())
{
var worksheet = package.Workbook.Worksheets.Add("Sheet1");
var calcOption = new ExcelCalculationOption();
var range = worksheet.Cells[2, 2];
range.Formula = "TODAY()";
range.Calculate(calcOption, true);
Assert.AreEqual(14, range.Style.Numberformat.NumFmtID);
range.Formula = "NOW()";
range.Calculate(calcOption, true);
Assert.AreEqual(14, range.Style.Numberformat.NumFmtID);
range.Formula = "TODAY() + 5";
range.Calculate(calcOption, true);
Assert.AreEqual(14, range.Style.Numberformat.NumFmtID);
Assert.AreEqual(DateTime.Today.AddDays(5), range.Value);
range.Formula = "TIME(14, 23, 15)";
range.Calculate(calcOption, true);
Assert.AreEqual(21, range.Style.Numberformat.NumFmtID);
range.Formula = "1 + 2";
range.Calculate(calcOption, true);
Assert.AreEqual(1, range.Style.Numberformat.NumFmtID);
range.Formula = "1.5 * 2.3";
range.Calculate(calcOption, true);
Assert.AreEqual(2, range.Style.Numberformat.NumFmtID);
range.Formula = @"""some""&"" text""";
range.Calculate(calcOption, true);
Assert.AreEqual(2, range.Style.Numberformat.NumFmtID);
}
}
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)));
}
}
private static void CalcChain(ExcelWorkbook wb, FormulaParser parser, DependencyChain dc, ExcelCalculationOption options)
{
wb.FormulaParser.Configure(config => config.AllowCircularReferences = options.AllowCircularReferences);
var debug = parser.Logger != null;
foreach (var ix in dc.CalcOrder)
{
var item = dc.list[ix];
try
{
var ws = wb.Worksheets.GetBySheetID(item.SheetID);
var v = parser.ParseCell(item.Tokens, ws == null ? "" : ws.Name, item.Row, item.Column);
SetValue(wb, item, v);
if (debug)
{
parser.Logger.LogCellCounted();
}
Thread.Sleep(0);
}
catch (FormatException fe)
{
throw (fe);
}
catch (CircularReferenceException cre)
{
throw cre;
}
catch
{
var error = ExcelErrorValue.Parse(ExcelErrorValue.Values.Value);
SetValue(wb, item, error);
}
}
}
