private void CopyTableToWorkSheet()
{
ISheet grid = MyGrid;
for (int row = 1; row <= grid.RowCount; row++)
{
for (int col = 1; col <= grid.ColumnCount; col++)
{
object value = grid.GetCellValue(row, col);
if (value == null)
{
value = value;
}
else if (object.ReferenceEquals(value.GetType(), typeof(ErrorValueWrapper)))
{
value = value.ToString();
}
else if (object.ReferenceEquals(value.GetType(), typeof(string)))
{
value = "'" + value;
}
MyWorksheet.Cells[row, col] = value;
}
}
}
public double Calculate(double rate, List <double> values)
{
if (values.Count > maxRows)
{
throw new Exception(string.Format("Cannot handle values list over {0}!", values));
}
this.Clear();
sheet.SetCellValue(0, 1, rate);
var currRow = 3;
foreach (var value in values)
{
sheet.SetCellValue(currRow, 1, value);
currRow++;
}
this.sheet.SetCellFormula(0, 3, string.Format("IRR(B4:B{0}, {1})*12", currRow, rateFormula.Replace(IRR.RATE_VAR, "B1")));
HSSFFormulaEvaluator.EvaluateAllFormulaCells(workbook);
var irrValue = sheet.GetCellValue(0, 3, 0d);
return(irrValue);
}
public double Calculate(double rate, List <double> values)
{
if (values.Count > maxRows)
{
throw new Exception(string.Format("Cannot handle values list over {0}!", values));
}
this.Clear();
sheet.SetCellValue(0, 1, rate);
var startRow = 3;
foreach (var value in values)
{
sheet.SetCellValue(startRow, 1, value);
startRow++;
}
this.sheet.SetCellFormula(0, 3, string.Format("NPV({0},B4:B{1})", rateFormula.Replace(NPV.RATE_VAR, "B1"), startRow));
HSSFFormulaEvaluator.EvaluateAllFormulaCells(workbook);
var npvValue = sheet.GetCellValue(0, 3, 0d);
return(npvValue);
}