/**
*
* @return a constant from the local Result class denoting whether there were any Evaluation
* cases, and whether they all succeeded.
*/
private int ProcessFunctionRow(HSSFFormulaEvaluator Evaluator, String targetFunctionName,
IRow formulasRow, IRow expectedValuesRow)
{
int result = Result.NO_EVALUATIONS_FOUND; // so far
short endcolnum = (short)formulasRow.LastCellNum;
// iterate across the row for all the Evaluation cases
for (int colnum = SS.COLUMN_INDEX_FIRST_TEST_VALUE; colnum < endcolnum; colnum++)
{
ICell c = formulasRow.GetCell(colnum);
if (c == null || c.CellType != CellType.Formula)
{
continue;
}
CellValue actualValue = Evaluator.Evaluate(c);
ICell expectedValueCell = GetExpectedValueCell(expectedValuesRow, colnum);
try
{
ConfirmExpectedResult("Function '" + targetFunctionName + "': Formula: " + c.CellFormula + " @ " + formulasRow.RowNum + ":" + colnum,
expectedValueCell, actualValue);
_EvaluationSuccessCount++;
if (result != Result.SOME_EVALUATIONS_FAILED)
{
result = Result.ALL_EVALUATIONS_SUCCEEDED;
}
}
catch (AssertionException e)
{
_EvaluationFailureCount++;
printshortStackTrace(System.Console.Error, e);
result = Result.SOME_EVALUATIONS_FAILED;
}
}
return(result);
}
public void TestNpvFromSpreadsheet()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("IrrNpvTestCaseData.xls");
ISheet sheet = wb.GetSheet("IRR-NPV");
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
StringBuilder failures = new StringBuilder();
int failureCount = 0;
// TODO YK: Formulas in rows 16 and 17 operate with ArrayPtg which isn't yet supported
// FormulaEvaluator as of r1041407 throws "Unexpected ptg class (NPOI.SS.Formula.PTG.ArrayPtg)"
for (int rownum = 9; rownum <= 15; rownum++)
{
IRow row = sheet.GetRow(rownum);
ICell cellB = row.GetCell(1);
try
{
CellValue cv = fe.Evaluate(cellB);
assertFormulaResult(cv, cellB);
}
catch (Exception e)
{
if (failures.Length > 0)
{
failures.Append('\n');
}
failures.Append("Row[" + (cellB.RowIndex + 1) + "]: " + cellB.CellFormula + " ");
failures.Append(e.Message);
failureCount++;
}
}
if (failures.Length > 0)
{
throw new AssertionException(failureCount + " IRR Evaluations failed:\n" + failures.ToString());
}
}
public static string GetCellValue(ICell cell)
{
if (cell != null)
{
switch (cell.CellType)
{
default:
return(cell.ToString());
case CellType.String:
return(cell.StringCellValue);
case CellType.Formula:
try
{
HSSFFormulaEvaluator hSSFFormulaEvaluator = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
hSSFFormulaEvaluator.EvaluateInCell(cell);
return(cell.ToString());
}
catch
{
return(cell.NumericCellValue.ToString());
}
case CellType.Blank:
return(string.Empty);
case CellType.Boolean:
return(cell.BooleanCellValue.ToString());
case CellType.Error:
return(cell.ErrorCellValue.ToString());
}
}
return(string.Empty);
}
public void DrawInspector(int indent = 0, GUILayoutOption[] guiOpts = null)
{
// base.DrawInspector();
var rect = EditorGUILayout.GetControlRect();
var sn = 5;
var idx = -1;
if (GUI.Button(rect.Split(++idx, sn), "Save"))
{
Book.Write(Name);
}
guiOpts = new GUILayoutOption[]
{
GUILayout.Width(30),
GUILayout.ExpandWidth(true),
};
foreach (var s in Sheets)
{
Inspector.DrawComObj(s.Name, s, null, () => {
s.DrawInspector(0, guiOpts);
});
}
if (GUI.changed)
{
if (Book is XSSFWorkbook)
{
XSSFFormulaEvaluator.EvaluateAllFormulaCells(Book);
}
else
{
HSSFFormulaEvaluator.EvaluateAllFormulaCells(Book);
}
}
}
public void TestInvoke()
{
HSSFWorkbook wb;
NPOI.SS.UserModel.Sheet sheet ;
Cell cell;
if (false)
{
// TODO - this code won't work until we can create user-defined functions directly with POI
wb = new HSSFWorkbook();
sheet = wb.CreateSheet();
wb.SetSheetName(0, "Sheet1");
NPOI.SS.UserModel.Name hssfName = wb.CreateName();
hssfName.NameName = ("myFunc");
}
else
{
// This sample spreadsheet already has a VB function called 'myFunc'
wb = HSSFTestDataSamples.OpenSampleWorkbook("testNames.xls");
sheet = wb.GetSheetAt(0);
Row row = sheet.CreateRow(0);
cell = row.CreateCell(1);
}
cell.CellFormula = ("myFunc()");
String actualFormula = cell.CellFormula;
Assert.AreEqual("myFunc()", actualFormula);
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet, wb);
NPOI.SS.UserModel.CellValue evalResult = fe.Evaluate(cell);
// Check the return value from ExternalFunction.evaluate()
// TODO - make this test assert something more interesting as soon as ExternalFunction works a bit better
Assert.AreEqual(NPOI.SS.UserModel.CellType.ERROR, evalResult.CellType);
Assert.AreEqual(ErrorEval.FUNCTION_NOT_IMPLEMENTED.ErrorCode, evalResult.ErrorValue);
}
public System.Data.DataTable Import(string filepath, string[] headerNames)
{
DataTable dt = new DataTable();
this.AddColumn(dt, headerNames);
IWorkbook wb = InitializeWorkbook(filepath);
IFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
ISheet sht = wb.GetSheetAt(0);
IEnumerator rows = sht.GetRowEnumerator();
//默认第一行为头部列名
if (rows.MoveNext())
{
while (rows.MoveNext())
{
IRow row = rows.Current as HSSFRow;
//M by Duanqh 2012-7-27
//if (row == null) continue;
this.AddRow(dt, row, headerNames, evaluator);
}
}
return(dt);
}
public System.Data.DataTable Import(string filepath)
{
IWorkbook workBook = this.InitializeWorkbook(filepath);
IFormulaEvaluator evaluator = new HSSFFormulaEvaluator(workBook);
ISheet sheet = workBook.GetSheetAt(0);
IEnumerator rows = sheet.GetEnumerator();
DataTable dt = new DataTable();
string[] headerNames = new string[sheet.GetRow(0).PhysicalNumberOfCells];
for (int j = 0; j < headerNames.Length; j++)
{
headerNames[j] = Convert.ToChar(((int)'A') + j % 26).ToString() + ((j / 26) > 0 ? (j / 26).ToString() : string.Empty); // A-Z A1-Z1 An-Zn
}
this.AddColumn(dt, headerNames);
while (rows.MoveNext())
{
IRow row = rows.Current as HSSFRow;
this.AddRow(dt, row, headerNames, evaluator);
}
return(dt);
}
/// <summary>
/// 根据Excel列类型获取列的值
/// </summary>
/// <param name="cell">Excel列</param>
/// <returns></returns>
private static string GetCellValue(ICell cell)
{
if (cell == null)
{
return(string.Empty);
}
switch (cell.CellType)
{
case CellType.Blank:
return(string.Empty);
case CellType.Boolean:
return(cell.BooleanCellValue.ToString());
case CellType.Error:
return(cell.ErrorCellValue.ToString());
default:
return(cell.ToString()); //This is a trick to get the correct value of the cell. NumericCellValue will return a numeric value no matter the cell value is a date or a number
case CellType.String:
return(cell.StringCellValue);
case CellType.Formula:
try
{
HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
e.EvaluateInCell(cell);
return(cell.ToString());
}
catch
{
return(cell.NumericCellValue.ToString());
}
}
}
public void TestEvaluateInSheet()
{
HSSFWorkbook wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet("Sheet1");
IRow row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue(-4000d);
row.CreateCell(1).SetCellValue(1200d);
row.CreateCell(2).SetCellValue(1410d);
row.CreateCell(3).SetCellValue(1875d);
row.CreateCell(4).SetCellValue(1050d);
ICell cell = row.CreateCell(5);
cell.CellFormula = ("IRR(A1:E1)");
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
fe.ClearAllCachedResultValues();
fe.EvaluateFormulaCell(cell);
double res = cell.NumericCellValue;
Assert.AreEqual(0.143d, Math.Round(res * 1000d) / 1000d);
}
public void TestFind1()
{
HSSFWorkbook wb = new HSSFWorkbook();
ICell cell = wb.CreateSheet().CreateRow(0).CreateCell(0);
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
ConfirmResult(fe, cell, "Find(\"h\", \"haystack\")", 1);
ConfirmResult(fe, cell, "Find(\"a\", \"haystack\",2)", 2);
ConfirmResult(fe, cell, "Find(\"a\", \"haystack\",3)", 6);
// number args Converted to text
ConfirmResult(fe, cell, "Find(7, 32768)", 3);
ConfirmResult(fe, cell, "Find(\"34\", 1341235233412, 3)", 10);
ConfirmResult(fe, cell, "Find(5, 87654)", 4);
// Errors
ConfirmError(fe, cell, "Find(\"n\", \"haystack\")", HSSFErrorConstants.ERROR_VALUE);
ConfirmError(fe, cell, "Find(\"k\", \"haystack\",9)", HSSFErrorConstants.ERROR_VALUE);
ConfirmError(fe, cell, "Find(\"k\", \"haystack\",#REF!)", HSSFErrorConstants.ERROR_REF);
ConfirmError(fe, cell, "Find(\"k\", \"haystack\",0)", HSSFErrorConstants.ERROR_VALUE);
ConfirmError(fe, cell, "Find(#DIV/0!, #N/A, #REF!)", HSSFErrorConstants.ERROR_DIV_0);
ConfirmError(fe, cell, "Find(2, #N/A, #REF!)", HSSFErrorConstants.ERROR_NA);
}
public static void PrepareForOutput(HSSFWorkbook book)
{
//删除报表定义sheet
for (int i = book.NumberOfSheets - 1; i >= 0; i--)
{
HSSFSheet sheet = book.GetSheetAt(i) as HSSFSheet;
if (IsSystemSheet(sheet))
{
book.RemoveSheetAt(i);
}
}
HSSFFormulaEvaluator.EvaluateAllFormulaCells(book);
//设置打开时强制计算合计项
//for (int i = 0; i < book.NumberOfSheets; i++)
//{
// HSSFSheet sheet = book.GetSheetAt(i) as HSSFSheet;
// sheet.ForceFormulaRecalculation = true;
//}
book.SetActiveSheet(0);
}
public void TestEvaluateInCellWithErrorCode_bug44950()
{
HSSFWorkbook wb = new HSSFWorkbook();
Npoi.Core.SS.UserModel.ISheet sheet = wb.CreateSheet("Sheet1");
IRow row = sheet.CreateRow(1);
ICell cell = row.CreateCell(0);
cell.CellFormula = ("na()"); // this formula Evaluates to an Excel error code '#N/A'
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
try
{
fe.EvaluateInCell(cell);
}
catch (InvalidOperationException e)
{
if (e.Message.StartsWith("Cannot get a error value from"))
{
throw new AssertionException("Identified bug 44950 b");
}
throw;
}
}
public void TestMissingWorkbookMissingOverride()
{
mainWorkbook = HSSFTestDataSamples.OpenSampleWorkbook(MAIN_WORKBOOK_FILENAME);
HSSFSheet lSheet = (HSSFSheet)mainWorkbook.GetSheetAt(0);
HSSFCell lA1Cell = (HSSFCell)lSheet.GetRow(0).GetCell(0);
HSSFCell lB1Cell = (HSSFCell)lSheet.GetRow(1).GetCell(0);
HSSFCell lC1Cell = (HSSFCell)lSheet.GetRow(2).GetCell(0);
Assert.AreEqual(CellType.Formula, lA1Cell.CellType);
Assert.AreEqual(CellType.Formula, lB1Cell.CellType);
Assert.AreEqual(CellType.Formula, lC1Cell.CellType);
HSSFFormulaEvaluator evaluator = (HSSFFormulaEvaluator)mainWorkbook.GetCreationHelper().CreateFormulaEvaluator();
evaluator.IgnoreMissingWorkbooks = (true);
Assert.AreEqual(CellType.Numeric, evaluator.EvaluateFormulaCell(lA1Cell));
Assert.AreEqual(CellType.String, evaluator.EvaluateFormulaCell(lB1Cell));
Assert.AreEqual(CellType.Boolean, evaluator.EvaluateFormulaCell(lC1Cell));
Assert.AreEqual(10.0d, lA1Cell.NumericCellValue, 0.00001d);
Assert.AreEqual("POI rocks!", lB1Cell.StringCellValue);
Assert.AreEqual(true, lC1Cell.BooleanCellValue);
}
private object GetValue(ICell cell)
{
object value = null;
switch (cell.CellType)
{
case CellType.Blank:
break;
case CellType.Boolean:
value = cell.BooleanCellValue ? "1" : "0";
break;
case CellType.Error:
value = cell.ErrorCellValue;
break;
case CellType.Formula:
HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Row.Sheet.Workbook);
cell = e.EvaluateInCell(cell);
value = GetValue(cell);
break;
case CellType.Numeric:
value = cell.NumericCellValue.ToString();
break;
case CellType.String:
value = cell.StringCellValue;
break;
case CellType.Unknown:
break;
}
return(value);
}
public void Write(ISheet sheet, DataTable dt, int rowStart = 0, int colStart = 0, bool rownum = true)
{
//初始化参数
init();
AfterInit();//初始化之后的操作
int rOffset = 0;
int cOffset = 0;
int length = AllFields.Count > 0 ? AllFields.Count + (rownum ? 1 : 0) : dt.Columns.Count;
ICell cell;
IRow row;
foreach (DataRow dr in dt.Rows)
{
//创建一行
sheet.ShiftRows(rowStart + rOffset + 1, sheet.LastRowNum, 1); //下一行到最后一行，移动一行的距离
row = sheet.CreateRow(rowStart + rOffset + 1); //创建下一行供后面使用，写数据时仍使用当前行
for (int i = colStart; i < length + colStart; i++)
{
cell = row.CreateCell(colStart + i);
cell.CellStyle = sheet.GetRow(rowStart).GetCell(colStart + i).CellStyle;//上一行的Style
}
cOffset = colStart; //initial
//写行号
if (rownum)
{
cell = sheet.GetRow(rowStart + rOffset).GetCell(cOffset++);
cell.SetCellValue(rOffset + 1);
}
this.WriteToRow(sheet.GetRow(rowStart + rOffset), cOffset++, field => dr[field]);
#region
////写内容
//foreach (string columnName in AllFields)
//{
// cell = sheet.GetRow(rowStart + rOffset).GetCell(cOffset++);
// TextRender<object> textRender = null;
// if (TextFields.Count > 0 && TextFields.TryGetValue(columnName, out textRender))
// {
// cell.SetCellValue(textRender.ValueRender(dr[columnName]));
// }
// else if (DateTimeFields.Contains(columnName))
// {
// cell.SetCellValue(DateTime.Parse(dr[columnName].ToString()));
// }
// else if (DoubleFields.Contains(columnName))
// {
// cell.SetCellValue(Double.Parse(dr[columnName].ToString()));
// }
// else
// {
// cell.SetCellValue(dr[columnName].ToString());
// }
//}
#endregion
rOffset++;
}
//将所有“行公式”转换成值单元
HSSFFormulaEvaluator.EvaluateAllFormulaCells(sheet.Workbook);//
//sheet.RemoveRow(sheet.GetRow(rowStart + rOffset));
if (rowStart + rOffset + 1 <= sheet.LastRowNum)
{
sheet.ShiftRows(rowStart + rOffset + 1, sheet.LastRowNum, -1);
}
}
/// <summary>
/// Npoi读取Excel 赫
/// </summary>
/// <param name="savePath"></param>
/// <param name="SheetName"></param>
/// <param name="outMsg"></param>
/// <returns></returns>
public static DataTable NpoiReadExcle2(string savePath, string sheetName, out string outMsg)
{
outMsg = "";
DataTable table = new DataTable();
outMsg = "";
IWorkbook workbook = null;
FileStream fs = null;
ISheet sheet = null;
DataTable data = new DataTable();
int startRow = 0;
try
{
fs = new FileStream(savePath, FileMode.Open, FileAccess.Read);
if (savePath.EndsWith(".xlsx")) // 2007版本
{
workbook = new XSSFWorkbook(fs);
}
else if (savePath.EndsWith(".xls")) // 2003版本
{
workbook = new HSSFWorkbook(fs);
}
if (sheetName != null)
{
sheet = workbook.GetSheet(sheetName);
if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
{
sheet = workbook.GetSheetAt(0);
}
}
else
{
sheet = workbook.GetSheetAt(0);
}
//获取sheet的首行
var headerRow = sheet.GetRow(0);
//一行最后一个方格的编号 即总的列数
int cellCount = headerRow.LastCellNum;
//最后一列的标号 即总的行数
int rowCount = sheet.LastRowNum;
for (int j = 0; j < headerRow.LastCellNum; j++)
{
table.Columns.Add(headerRow.GetCell(j).ToString());
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum && sheet.GetRow(i) != null; i++)
{
var row = sheet.GetRow(i);
DataRow dataRow = table.NewRow();
//当首列为空时,则认为此行以及此行之后为空
if (row.GetCell(row.FirstCellNum) == null || string.IsNullOrWhiteSpace(row.GetCell(row.FirstCellNum).ToString()))
{
continue;
}
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
ICell cell = row.GetCell(j);
//读取Excel格式，根据格式读取数据类型
#region //Excel格式，根据格式读取数据类型
switch (cell.CellType)
{
case CellType.Blank: //空数据类型处理
dataRow[j] = "";
break;
case CellType.String: //字符串类型
dataRow[j] = cell.StringCellValue;
break;
case CellType.Numeric: //数字类型
if (DateUtil.IsCellDateFormatted(cell)) //数字类型中分为数字、日期类型
{
dataRow[j] = cell.DateCellValue;
}
else
{
dataRow[j] = cell.NumericCellValue;
}
break;
case CellType.Formula:
HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(workbook);
dataRow[j] = e.Evaluate(cell).StringValue;
break;
default:
dataRow[j] = "";
break;
}
#endregion
}
}
table.Rows.Add(dataRow);
}
}
catch (Exception ex)
{
outMsg = ex.ToString();
}
return(table);
}
/**
* Loops over all cells in all sheets of the supplied
* workbook.
* For cells that contain formulas, their formulas are
* Evaluated, and the results are saved. These cells
* remain as formula cells.
* For cells that do not contain formulas, no Changes
* are made.
* This is a helpful wrapper around looping over all
* cells, and calling EvaluateFormulaCell on each one.
*/
public void EvaluateAll()
{
HSSFFormulaEvaluator.EvaluateAllFormulaCells(_book);
}
/**
* Loops over all cells in all sheets of the supplied
* workbook.
* For cells that contain formulas, their formulas are
* Evaluated, and the results are saved. These cells
* remain as formula cells.
* For cells that do not contain formulas, no Changes
* are made.
* This is a helpful wrapper around looping over all
* cells, and calling EvaluateFormulaCell on each one.
*/
public static void EvaluateAllFormulaCells(IWorkbook wb)
{
HSSFFormulaEvaluator.EvaluateAllFormulaCells(wb);
}
private int ProcessTestSheet(HSSFWorkbook workbook, int sheetIndex, String sheetName)
{
ISheet sheet = workbook.GetSheetAt(sheetIndex);
HSSFFormulaEvaluator Evaluator = new HSSFFormulaEvaluator(workbook);
int maxRows = sheet.LastRowNum + 1;
int result = Result.NO_EVALUATIONS_FOUND; // so far
String currentGroupComment = null;
for (int rowIndex = SS.START_TEST_CASES_ROW_INDEX; rowIndex < maxRows; rowIndex++)
{
IRow r = sheet.GetRow(rowIndex);
String newMarkerValue = GetMarkerColumnValue(r);
if (r == null)
{
continue;
}
if (SS.TEST_CASES_END_MARKER.Equals(newMarkerValue, StringComparison.InvariantCultureIgnoreCase))
{
// normal exit point
return(result);
}
if (SS.SKIP_CURRENT_TEST_CASE_MARKER.Equals(newMarkerValue, StringComparison.InvariantCultureIgnoreCase))
{
// currently disabled test case row
continue;
}
if (newMarkerValue != null)
{
currentGroupComment = newMarkerValue;
}
ICell c = r.GetCell(SS.COLUMN_INDEX_EVALUATION);
if (c == null || c.CellType != CellType.Formula)
{
continue;
}
ICell expectedValueCell = r.GetCell(SS.COLUMN_INDEX_EXPECTED_RESULT);
String rowComment = GetRowCommentColumnValue(r);
String msgPrefix = formatTestCaseDetails(this.Filename, sheetName, r.RowNum, c, currentGroupComment, rowComment);
try
{
CellValue actualValue = Evaluator.Evaluate(c);
ConfirmExpectedResult(msgPrefix, expectedValueCell, actualValue);
_evaluationSuccessCount++;
if (result != Result.SOME_EVALUATIONS_FAILED)
{
result = Result.ALL_EVALUATIONS_SUCCEEDED;
}
}
catch (RuntimeException e)
{
_evaluationFailureCount++;
printshortStackTrace(System.Console.Error, e);
result = Result.SOME_EVALUATIONS_FAILED;
}
catch (AssertionException e)
{
_evaluationFailureCount++;
printshortStackTrace(System.Console.Error, e);
result = Result.SOME_EVALUATIONS_FAILED;
}
}
throw new Exception("Missing end marker '" + SS.TEST_CASES_END_MARKER
+ "' on sheet '" + sheetName + "'");
}
public void EvaluateAll()
{
HSSFFormulaEvaluator.EvaluateAllFormulaCells((IWorkbook)this._book);
}
CustomWorkbook(FileInfo file)
{
ThreadPool.QueueUserWorkItem(o =>
{
fileName = file.Name;
FileStream fs = new FileStream(file.FullName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
// 表的格式
if (file.Extension == ".xlsx")
{
var book = new XSSFWorkbook(fs);
foreach (var link in book.ExternalLinksTable)
{
string[] arr = link.LinkedFileName.Split('/');
if (arr.Length > 1)
{
link.LinkedFileName = arr[arr.Length - 1];
}
}
workbook = book;
evaluator = new XSSFFormulaEvaluator(workbook);
}
else if (file.Extension == ".xls")
{
workbook = new HSSFWorkbook(fs);
evaluator = new HSSFFormulaEvaluator(workbook);
}
else if (file.Extension == ".xlsm")
{
var book = new XSSFWorkbook(fs);
foreach (var link in book.ExternalLinksTable)
{
string[] arr = link.LinkedFileName.Split('/');
if (arr.Length > 1)
{
link.LinkedFileName = arr[arr.Length - 1];
}
}
workbook = book;
evaluator = new XSSFFormulaEvaluator(workbook);
}
else
{
// csv
workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet(file.Name.Substring(0, file.Name.Length - 4));
string[] lines = File.ReadAllLines(file.FullName);
for (int i = 0; i < lines.Length; i++)
{
IRow row = sheet.CreateRow(i);
string[] values = lines[i].Split(',');
row.CreateCell(0).SetCellValue(int.Parse(values[0]));
for (int j = 1; j < values.Length; j++)
{
row.CreateCell(j).SetCellValue(values[j]);
}
}
evaluator = new XSSFFormulaEvaluator(workbook);
}
fs.Close();
lock (allBooks)
{
allBooks.Add(this);
evaluatorEnv.Add(file.Name, evaluator);
}
});
}
/**
* @param startRowIndex row index in the spreadsheet where the first function/operator is found
* @param testFocusFunctionName name of a single function/operator to test alone.
* Typically pass <code>null</code> to test all functions
*/
private void ProcessFunctionGroup(int startRowIndex, string testFocusFunctionName)
{
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(workbook);
ReadOnlyCollection <string> funcs = FunctionEval.GetSupportedFunctionNames();
int rowIndex = startRowIndex;
while (true)
{
IRow r = sheet.GetRow(rowIndex);
// only Evaluate non empty row
if (r != null)
{
string targetFunctionName = GetTargetFunctionName(r);
string targetTestName = GetTargetTestName(r);
if (targetFunctionName == null)
{
throw new AssertFailedException("Test spreadsheet cell empty on row ("
+ (rowIndex + 1) + "). Expected function name or '"
+ SS.FUNCTION_NAMES_END_SENTINEL + "'");
}
if (targetFunctionName.Equals(SS.FUNCTION_NAMES_END_SENTINEL))
{
// found end of functions list
break;
}
if (testFocusFunctionName == null || targetFunctionName.Equals(testFocusFunctionName, StringComparison.CurrentCultureIgnoreCase))
{
// expected results are on the row below
ICell expectedValueCell = r.GetCell(SS.COLUMN_INDEX_EXPECTED_VALUE);
if (expectedValueCell == null)
{
int missingRowNum = rowIndex + 1;
throw new AssertFailedException("Missing expected values cell for function '"
+ targetFunctionName + ", test" + targetTestName + " (row " +
missingRowNum + ")");
}
switch (ProcessFunctionRow(evaluator, targetFunctionName, targetTestName, r, expectedValueCell))
{
case Result.ALL_EVALUATIONS_SUCCEEDED: _functionSuccessCount++; break;
case Result.SOME_EVALUATIONS_FAILED: _functionFailureCount++; break;
default:
throw new Exception("unexpected result");
case Result.NO_EVALUATIONS_FOUND: // do nothing
string uname = targetFunctionName.ToUpper();
if (startRowIndex >= SS.START_FUNCTIONS_ROW_INDEX &&
funcs.Contains(uname))
{
logger.Log(POILogger.WARN, uname + ": function is supported but missing test data");
}
break;
}
}
}
rowIndex++;
}
}
public DataTable ToDataTable(Stream stream, int minRow = 0, int maxRow = 0)
{
ISheet sheet = null;
DataTable data = new DataTable();
int startRow = 0;
//var fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
IWorkbook workbook = null;
try
{
workbook = new XSSFWorkbook(stream);//2007
}
catch (Exception)
{
try
{
workbook = new HSSFWorkbook(stream);//2003
}
catch
{
throw new Exception("读取Excel文件失败！");
}
}
sheet = workbook.GetSheetAt(0);
if (sheet != null)
{
if (minRow != 0) startRow = minRow - 1;//真实序号
IRow firstRow = sheet.GetRow(startRow);
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
for (int i = 0; i < cellCount; ++i)
{
DataColumn column = new DataColumn(i.ToString());
data.Columns.Add(column);
}
//最后一列的标号
int rowCount = sheet.LastRowNum;
//if (maxRow != 0 && rowCount > maxRow) rowCount = maxRow - 1;
if (maxRow != 0) rowCount = maxRow - 1;
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null || row.FirstCellNum < 0) continue; //没有数据的行默认是null
DataRow dataRow = data.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
//同理，没有数据的单元格都默认是null
if (row.GetCell(j) != null)
{
if (row.GetCell(j).CellType == CellType.Formula)
{
//公式类型
sheet.ForceFormulaRecalculation = true;
HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(workbook);
dataRow[j] = eva.EvaluateInCell(row.GetCell(j));
}
else if (row.GetCell(j).CellType == CellType.Numeric)
{
if (HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))
{
//日期类型
dataRow[j] = row.GetCell(j).DateCellValue;
}
else
{
//其他数字类型
dataRow[j] = row.GetCell(j).NumericCellValue;
}
}
else if (row.GetCell(j).CellType == CellType.Blank)
{
//空数据类型
dataRow[j] = "";
}
else
{
dataRow[j] = row.GetCell(j).StringCellValue;
}
}
}
data.Rows.Add(dataRow);
}
//fs.Close();
}
return data;
}
private void SheetVariableReplace(ISheet sheet, IWorkbook workbook, dynamic valuePair,
DateTime startTime, DateTime endTime, int reportType, string precision, bool isShouldResizeColumn = true)
{
bool hadBeenDraw = false;
IRow row;
for (int i = 0; i <= sheet.LastRowNum; i++)
{
row = sheet.GetRow(i);
if (row != null)
{
var bmfId = string.Empty;
var startIndex = 0;
int endIndex = 0;
for (int j = 0; j <= row.LastCellNum; j++)
{
startIndex = j;
sheet.AutoSizeColumn(j);
ICell cell = row.GetCell(j);
if (cell == null)
{
continue;
}
string cellValue = cell.ToString();
if (j == 1 && IsGuid(cellValue))
{
bmfId = cellValue;
continue;
}
if (cellValue == REPORT_TPL_START_FLAG)
{
if (!hadBeenDraw)
{
hadBeenDraw = true;
DrawTableHeader(startTime, endTime, workbook, sheet, reportType, startIndex, i, valuePair, precision);
}
ExcelReplaceValueDiffByType(workbook, sheet, row, valuePair, startTime, endTime,
reportType, bmfId, precision, startIndex, ref endIndex);
j = endIndex;
}
if (startIndex != j)
{
cell = row.GetCell(j);
if (cell == null)
{
continue;
}
}
if (cell.CellType == CellType.Formula)
{
HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(workbook);
if (cell.ToString().Contains(".xls"))
{
continue;
}
double result = eva.Evaluate(cell).NumberValue;
cell.SetCellValue(result);
sheet.ForceFormulaRecalculation = true;
}
if (isShouldResizeColumn)
{
sheet.SetColumnWidth(j, (int)(GetCellWidth(cell)) * 256);
}
}
}
}
}
public void TestXRefs()
{
HSSFWorkbook wb1 = HSSFTestDataSamples.OpenSampleWorkbook("XRefCalc.xls");
HSSFWorkbook wb2 = HSSFTestDataSamples.OpenSampleWorkbook("XRefCalcData.xls");
ICell cell;
// VLookup on a name in another file
cell = wb1.GetSheetAt(0).GetRow(1).GetCell(2);
Assert.AreEqual(CellType.Formula, cell.CellType);
Assert.AreEqual(CellType.Numeric, cell.CachedFormulaResultType);
Assert.AreEqual(12.30, cell.NumericCellValue, 0.0001);
// WARNING - this is wrong!
// The file name should be Showing, but bug #45970 is fixed
// we seem to loose it
Assert.AreEqual("VLOOKUP(PART,COSTS,2,FALSE)", cell.CellFormula);
// Simple reference to a name in another file
cell = wb1.GetSheetAt(0).GetRow(1).GetCell(4);
Assert.AreEqual(CellType.Formula, cell.CellType);
Assert.AreEqual(CellType.Numeric, cell.CachedFormulaResultType);
Assert.AreEqual(36.90, cell.NumericCellValue, 0.0001);
// WARNING - this is wrong!
// The file name should be Showing, but bug #45970 is fixed
// we seem to loose it
Assert.AreEqual("Cost*Markup_Cost", cell.CellFormula);
// Evaluate the cells
HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb1);
HSSFFormulaEvaluator.SetupEnvironment(
new String[] { "XRefCalc.xls", "XRefCalcData.xls" },
new HSSFFormulaEvaluator[] {
eval,
new HSSFFormulaEvaluator(wb2)
}
);
eval.EvaluateFormulaCell(
wb1.GetSheetAt(0).GetRow(1).GetCell(2)
);
eval.EvaluateFormulaCell(
wb1.GetSheetAt(0).GetRow(1).GetCell(4)
);
// Re-check VLOOKUP one
cell = wb1.GetSheetAt(0).GetRow(1).GetCell(2);
Assert.AreEqual(CellType.Formula, cell.CellType);
Assert.AreEqual(CellType.Numeric, cell.CachedFormulaResultType);
Assert.AreEqual(12.30, cell.NumericCellValue, 0.0001);
// Re-check ref one
cell = wb1.GetSheetAt(0).GetRow(1).GetCell(4);
Assert.AreEqual(CellType.Formula, cell.CellType);
Assert.AreEqual(CellType.Numeric, cell.CachedFormulaResultType);
Assert.AreEqual(36.90, cell.NumericCellValue, 0.0001);
// Add a formula that refers to one of the existing external workbooks
cell = wb1.GetSheetAt(0).GetRow(1).CreateCell(40);
cell.CellFormula = (/*setter*/ "Cost*[XRefCalcData.xls]MarkupSheet!$B$1");
// Check is was stored correctly
Assert.AreEqual("Cost*[XRefCalcData.xls]MarkupSheet!$B$1", cell.CellFormula);
// Check it Evaluates correctly
eval.EvaluateFormulaCell(cell);
Assert.AreEqual(24.60 * 1.8, cell.NumericCellValue, 0);
// Try to add a formula for a new external workbook, won't be allowed to start
try
{
cell = wb1.GetSheetAt(0).GetRow(1).CreateCell(42);
cell.CellFormula = (/*setter*/ "[alt.xls]Sheet0!$A$1");
Assert.Fail("New workbook not linked, shouldn't be able to Add");
}
catch (Exception) { }
// Link our new workbook
HSSFWorkbook wb3 = new HSSFWorkbook();
wb3.CreateSheet().CreateRow(0).CreateCell(0).SetCellValue("In another workbook");
wb1.LinkExternalWorkbook("alt.xls", wb3);
// Now add a formula that refers to our new workbook
cell.CellFormula = (/*setter*/ "[alt.xls]Sheet0!$A$1");
Assert.AreEqual("[alt.xls]Sheet0!$A$1", cell.CellFormula);
// Evaluate it, without a link to that workbook
try
{
eval.Evaluate(cell);
Assert.Fail("No cached value and no link to workbook, shouldn't Evaluate");
}
catch (Exception) { }
// Add a link, check it does
HSSFFormulaEvaluator.SetupEnvironment(
new String[] { "XRefCalc.xls", "XRefCalcData.xls", "alt.xls" },
new HSSFFormulaEvaluator[] {
eval,
new HSSFFormulaEvaluator(wb2),
new HSSFFormulaEvaluator(wb3)
}
);
eval.EvaluateFormulaCell(cell);
Assert.AreEqual("In another workbook", cell.StringCellValue);
// Save and re-load
HSSFWorkbook wb4 = HSSFTestDataSamples.WriteOutAndReadBack(wb1);
eval = new HSSFFormulaEvaluator(wb4);
HSSFFormulaEvaluator.SetupEnvironment(
new String[] { "XRefCalc.xls", "XRefCalcData.xls", "alt.xls" },
new HSSFFormulaEvaluator[] {
eval,
new HSSFFormulaEvaluator(wb2),
new HSSFFormulaEvaluator(wb3)
}
);
// Check the one referring to the previously existing workbook behaves
cell = wb4.GetSheetAt(0).GetRow(1).GetCell(40);
Assert.AreEqual("Cost*[XRefCalcData.xls]MarkupSheet!$B$1", cell.CellFormula);
eval.EvaluateFormulaCell(cell);
Assert.AreEqual(24.60 * 1.8, cell.NumericCellValue);
// Now check the newly Added reference
cell = wb4.GetSheetAt(0).GetRow(1).GetCell(42);
Assert.AreEqual("[alt.xls]Sheet0!$A$1", cell.CellFormula);
eval.EvaluateFormulaCell(cell);
Assert.AreEqual("In another workbook", cell.StringCellValue);
wb4.Close();
wb3.Close();
wb2.Close();
wb1.Close();
}
public void Test55747_55324()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFFormulaEvaluator ev = wb.GetCreationHelper().CreateFormulaEvaluator() as HSSFFormulaEvaluator;
HSSFSheet ws = wb.CreateSheet() as HSSFSheet;
HSSFRow row = ws.CreateRow(0) as HSSFRow;
HSSFCell cell;
// Our test value
cell = row.CreateCell(0) as HSSFCell;
cell.SetCellValue("abc");
// Lots of IF cases
cell = row.CreateCell(1) as HSSFCell;
cell.SetCellFormula("IF(A1<>\"\",MID(A1,1,2),\"X\")");//if(expr,func,val)
cell = row.CreateCell(2) as HSSFCell;
cell.SetCellFormula("IF(A1<>\"\",\"A\",\"B\")");// if(expr,val,val)
cell = row.CreateCell(3) as HSSFCell;
cell.SetCellFormula("IF(A1=\"\",\"X\",MID(A1,1,2))");//if(expr,val,func),
cell = row.CreateCell(4) as HSSFCell;
cell.SetCellFormula("IF(A1<>\"\",\"X\",MID(A1,1,2))");//if(expr,val,func),
cell = row.CreateCell(5) as HSSFCell;
cell.SetCellFormula("IF(A1=\"\",MID(A1,1,2),MID(A1,2,2))"); //if(exp,func,func)
cell = row.CreateCell(6) as HSSFCell;
cell.SetCellFormula("IF(A1<>\"\",MID(A1,1,2),MID(A1,2,2))"); //if(exp,func,func)
cell = row.CreateCell(7) as HSSFCell;
cell.SetCellFormula("IF(MID(A1,1,2)<>\"\",\"A\",\"B\")");//if(func_expr,val,val)
// And some MID ones just to check
row = ws.CreateRow(1) as HSSFRow;
cell = row.CreateCell(1) as HSSFCell;
cell.SetCellFormula("MID(A1,1,2)");
cell = row.CreateCell(2) as HSSFCell;
cell.SetCellFormula("MID(A1,2,2)");
cell = row.CreateCell(3) as HSSFCell;
cell.SetCellFormula("MID(A1,2,1)");
cell = row.CreateCell(4) as HSSFCell;
cell.SetCellFormula("MID(A1,3,1)");
// Evaluate
ev.EvaluateAll();
// Save and re-load
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb) as HSSFWorkbook;
ws = wb.GetSheetAt(0) as HSSFSheet;
// Check the MID Ptgs in Row 2 have V RefPtgs for A1
row = ws.GetRow(1) as HSSFRow;
for (int i = 1; i <= 4; i++)
{
cell = row.GetCell(i) as HSSFCell;
Ptg[] ptgs = getPtgs(cell);
Assert.AreEqual(4, ptgs.Length);
Assert.AreEqual(typeof(FuncPtg), ptgs[3].GetType());
Assert.AreEqual("MID", ((FuncPtg)ptgs[3]).Name);
assertRefPtgA1('V', ptgs, 0);
}
// Now check the IF formulas
row = ws.GetRow(0) as HSSFRow;
// H1, MID is used in the expression IF checks, so A1 should be V
cell = row.GetCell(CellReference.ConvertColStringToIndex("H")) as HSSFCell;
assertRefPtgA1('V', getPtgs(cell), 0);
// E1, MID is used in the FALSE route, so A1 should be V
// A1 should be V in the IF check
// A1 should be R in the FALSE route
cell = row.GetCell(CellReference.ConvertColStringToIndex("E")) as HSSFCell;
assertRefPtgA1('V', getPtgs(cell), 0);
assertRefPtgA1('R', getPtgs(cell), 6);
// Check that, for B1, D1, F1 and G1, the references to A1
// from all of IF check, True and False are V
cell = row.GetCell(CellReference.ConvertColStringToIndex("B")) as HSSFCell;
assertRefPtgA1('V', getPtgs(cell), 0);
// assertRefPtgA1('V', getPtgs(cell), 4); // FIXME!
cell = row.GetCell(CellReference.ConvertColStringToIndex("D")) as HSSFCell;
assertRefPtgA1('V', getPtgs(cell), 0);
// assertRefPtgA1('V', getPtgs(cell), 6); // FIXME!
cell = row.GetCell(CellReference.ConvertColStringToIndex("F")) as HSSFCell;
assertRefPtgA1('V', getPtgs(cell), 0);
// assertRefPtgA1('V', getPtgs(cell), 4); // FIXME!
// assertRefPtgA1('V', getPtgs(cell), 9); // FIXME!
cell = row.GetCell(CellReference.ConvertColStringToIndex("G")) as HSSFCell;
assertRefPtgA1('V', getPtgs(cell), 0);
// assertRefPtgA1('V', getPtgs(cell), 4); // FIXME!
// assertRefPtgA1('V', getPtgs(cell), 9); // FIXME!
// Check our cached values were correctly evaluated
cell = row.GetCell(CellReference.ConvertColStringToIndex("A")) as HSSFCell;
Assert.AreEqual("abc", cell.StringCellValue);
cell = row.GetCell(CellReference.ConvertColStringToIndex("B")) as HSSFCell;
Assert.AreEqual("ab", cell.StringCellValue);
cell = row.GetCell(CellReference.ConvertColStringToIndex("C")) as HSSFCell;
Assert.AreEqual("A", cell.StringCellValue);
cell = row.GetCell(CellReference.ConvertColStringToIndex("D")) as HSSFCell;
Assert.AreEqual("ab", cell.StringCellValue);
cell = row.GetCell(CellReference.ConvertColStringToIndex("E")) as HSSFCell;
Assert.AreEqual("X", cell.StringCellValue);
cell = row.GetCell(CellReference.ConvertColStringToIndex("F")) as HSSFCell;
Assert.AreEqual("bc", cell.StringCellValue);
cell = row.GetCell(CellReference.ConvertColStringToIndex("G")) as HSSFCell;
Assert.AreEqual("ab", cell.StringCellValue);
cell = row.GetCell(CellReference.ConvertColStringToIndex("H")) as HSSFCell;
Assert.AreEqual("A", cell.StringCellValue);
// Enable this to write out + check in Excel
if (OUTPUT_TEST_FILES)
{
FileStream out1 = new FileStream("/tmp/test.xls", FileMode.Create, FileAccess.ReadWrite);
wb.Write(out1);
out1.Close();
}
}
/// <summary>
/// 获取Sheet页的数据
/// </summary>
/// <param name="sheetIndex">Sheet页Index，从0开始</param>
/// <returns>DataTable</returns>
public DataTable GetDataTable(int sheetIndex = 0)
{
DataTable dt = new DataTable();
HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(sheetIndex);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
HSSFCell cell = (HSSFCell)headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
HSSFRow row = (HSSFRow)sheet.GetRow(i);
if (row == null)
{
continue;
}
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
ICell cell = row.GetCell(j);
if (cell != null)
{
if (cell.CellType == CellType.Numeric)
{
//NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型
{
dataRow[j] = cell.DateCellValue;
}
else//其他数字类型
{
dataRow[j] = cell.NumericCellValue;
}
}
else if (cell.CellType == CellType.Blank)//空数据类型
{
dataRow[j] = "";
}
else if (cell.CellType == CellType.Formula)//公式类型
{
HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(workbook);
dataRow[j] = eva.Evaluate(cell).StringValue;
}
else //其他类型都按字符串类型来处理
{
dataRow[j] = cell.StringCellValue;
}
}
}
dt.Rows.Add(dataRow);
}
return(dt);
}
private static void ConfirmEvaluation(double expectedValue, HSSFFormulaEvaluator fe, ICell cell)
{
Assert.AreEqual(expectedValue, fe.Evaluate(cell).NumberValue, 0.0);
}
private void CheckReport(HSSFWorkbook wb, DataSet ds, DataSet dsFrom1104Formula, List <string> list_FormulaTableName)
{
for (int i = 0; i < wb.NumberOfSheets; i++)
{
HSSFSheet sheet = wb.GetSheetAt(i) as HSSFSheet; //读取wb中当前sheet的数据
DataTable dt_FromSum = ds.Tables[ds.Tables[0].TableName.Substring(0, 10) + sheet.SheetName]; //取出对应ds中的dt，需加上前缀，如“汇总20190930”
DataGridView dgv_FromSum = new DataGridView();
//从list_Dgv中取出对应的Dgv
foreach (DataGridView _dgv in list_Dgv)
{
if (_dgv.Name == sheet.SheetName)
{
dgv_FromSum = _dgv;
break;
}
}
string dt_Formula_Name = sheet.SheetName;
DataTable dtOfCheck = new DataTable();
//如果DB中存在此表校验公式的表
if (list_FormulaTableName.Contains(dt_Formula_Name))
{
foreach (DataTable dt_Checking in dsFrom1104Formula.Tables)
{
if (dt_Checking.TableName == dt_Formula_Name)
{
dtOfCheck = dt_Checking;
break;
}
}
if (dtOfCheck.Rows.Count > 0)
{
//依次取出dtOfCheck中的每行，即每个公式，赋值到wbForImportingChecking中对应的sheet中，开始校验
foreach (DataRow checking_Row in dtOfCheck.Rows)
{
int checking_RowIndex = int.Parse(checking_Row[0].ToString());
int checking_ColumnIndex = int.Parse(checking_Row[1].ToString());
HSSFCell checking_Cell = new HSSFCell(wb, sheet, checking_RowIndex, short.Parse(checking_ColumnIndex.ToString()));
//checking_Cell = sheet.GetRow(checking_RowIndex).GetCell(checking_ColumnIndex) as HSSFCell; //这样会出现null的Cell
string str_Formula_Original = checking_Row[2].ToString();
string str_Formula = str_Formula_Original;
dgv_FromSum.Rows[checking_RowIndex].Cells[checking_ColumnIndex].ToolTipText = str_Formula;
//检验单元格的值：（此时cell的ToString返回的是公式，需将其计算后，才能得到校验的值)
//计算公式
try
{
HSSFFormulaEvaluator ev = new HSSFFormulaEvaluator(wb);
HSSFFormulaEvaluator.SetupEnvironment(new string[] { wb.SummaryInformation.Title }, new HSSFFormulaEvaluator[] { ev });
Dictionary <string, IFormulaEvaluator> dic_Wb = new Dictionary <string, IFormulaEvaluator>();
dic_Wb.Add(wb.SummaryInformation.Title, ev as IFormulaEvaluator);
ev.SetupReferencedWorkbooks(dic_Wb);
ev.IgnoreMissingWorkbooks = true;
checking_Cell.SetCellFormula(str_Formula);
ev.EvaluateInCell(checking_Cell);
}
catch (Exception ex)
{
//公式计算有问题的单元格设置颜色和备注
//设置颜色
HSSFCellStyle cellStyleOfWrong = wbForImportingChecking.CreateCellStyle() as HSSFCellStyle;
cellStyleOfWrong.FillBackgroundColor = HSSFColor.DarkYellow.Index;
checking_Cell.CellStyle = cellStyleOfWrong;
//创建批注
HSSFPatriarch patr = sheet.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFComment comment_NullWarning = patr.CreateComment(new HSSFClientAnchor(0, 0, 0, 0, checking_ColumnIndex, checking_RowIndex, checking_ColumnIndex, checking_RowIndex));
//设置批注的内容和作者
comment_NullWarning.String = new HSSFRichTextString("此单元格无法计算公式(有可能是未导入引用的汇总表)，请自行校验");
comment_NullWarning.Author = "LYF";
checking_Cell.CellComment = comment_NullWarning;
//赋值到Dgv中
dgv_FromSum.Rows[checking_RowIndex].Cells[checking_ColumnIndex].Style.BackColor = Color.SandyBrown;
dgv_FromSum.Rows[checking_RowIndex].Cells[checking_ColumnIndex].ToolTipText = "此单元格无法计算公式，请自行校验";
continue;
//throw new Exception(ex.Message);
}
string result = checking_Cell.ToString();
//赋值给对应的dt和dgv
dt_FromSum.Rows[checking_RowIndex][checking_ColumnIndex] = result;
dgv_FromSum.Rows[checking_RowIndex].Cells[checking_ColumnIndex].Value = result;
//设置颜色
HSSFCellStyle cellStyleForChecking = wbForImportingChecking.CreateCellStyle() as HSSFCellStyle;
if (double.Parse(result) == 0)
{
cellStyleForChecking.FillBackgroundColor = HSSFColor.Aqua.Index;
dgv_FromSum.Rows[checking_RowIndex].Cells[checking_ColumnIndex].Style.BackColor = Color.Aqua;
}
else if (double.Parse(result) != 0)
{
cellStyleForChecking.FillBackgroundColor = HSSFColor.Red.Index;
dgv_FromSum.Rows[checking_RowIndex].Cells[checking_ColumnIndex].Style.BackColor = Color.Red;
}
checking_Cell.CellStyle = cellStyleForChecking;
}
dgv_FromSum.CellClick += new DataGridViewCellEventHandler(Dgv_CellClick); //添加点击单元格事件
dgv_FromSum.EndEdit();
bs_Dgv.EndEdit();
}
}
}
}
AmortizationScheduleResult IAmortizationCalculatorReport.Calculate()
{
var CurrentTask = "Loading sheet";
try
{
if (!this.ReloadTemplate("Sheet1"))
{
throw new Exception("Template could not be loaded :(");
}
List <string> Errors = new List <string>();
CurrentTask = "Validating Parms";
if (this.UPB <= 0)
{
Errors.Add(string.Format("UPB value of {0} is invalid.", this.UPB));
}
if (this.StartDate.Equals(DateTime.MinValue))
{
Errors.Add(string.Format("StartDate value is not set."));
}
if (this.FixedPaymentAmount < 0)
{
Errors.Add(string.Format("FixedPaymentAmount value of {0} is invalid.", this.FixedPaymentAmount));
}
if (this.InterestRate < 0)
{
Errors.Add(string.Format("InterestRate value of {0} is invalid.", this.InterestRate));
}
if (this.AmortizationTermYears <= 0)
{
Errors.Add(string.Format("AmortizationTermYears value of {0} is invalid.", this.AmortizationTermYears));
}
if (this.BalloonPaymentMonths < 0)
{
Errors.Add(string.Format("BalloonPaymentMonths value of {0} is invalid.", this.BalloonPaymentMonths));
}
if (this.InterestOnlyEnd < 0)
{
Errors.Add(string.Format("InterestOnlyEnd value of {0} is invalid.", this.InterestOnlyEnd));
}
if (this.PIKEndMonth < 0)
{
Errors.Add(string.Format("PIKEndMonth value of {0} is invalid.", this.PIKEndMonth));
}
if (this.PIKStartMonth < 0)
{
Errors.Add(string.Format("PIKStartMonth value of {0} is invalid.", this.PIKStartMonth));
}
if (Errors.Count > 0)
{
throw new ArgumentException(string.Join(" ", Errors.ToArray()));
}
CurrentTask = "Setting Parms";
sheet.SetCellValue(10, "F", this, "UPB");
sheet.SetCellValue(12, "F", this, "InterestRate");
if (double.TryParse(this.InterestCalculationMethodology.ToDescriptionString(), out var dblInterestCalculationMethodology))
{
sheet.SetCellValue(14, "F", dblInterestCalculationMethodology);
}
sheet.SetCellValue(15, "F", this, "AmortizationTermYears");
sheet.SetCellValue(16, "F", this, "BalloonPaymentMonths");
sheet.SetCellValue(17, "F", this, "StartDate");
sheet.SetCellValue(18, "F", this, "FirstPaymentMonth");
sheet.SetCellValue(21, "F", this, "PaymentFrequency");
sheet.SetCellValue(22, "F", (this.IsInterestOnly ? "Y" : "N"));
sheet.SetCellValue(23, "F", this, "PIKStartMonth");
sheet.SetCellValue(24, "F", this, "PIKEndMonth");
sheet.SetCellValue(25, "F", this, "InterestOnlyEnd");
sheet.SetCellValue(26, "F", (this.IsFixedPayment ? "Y" : "N"));
sheet.SetCellValue(27, "F", this, "FixedPaymentAmount");
CurrentTask = "Evaluating Formula";
if (workbook is XSSFWorkbook)
{
XSSFFormulaEvaluator.EvaluateAllFormulaCells(workbook);
}
else
{
HSSFFormulaEvaluator.EvaluateAllFormulaCells(workbook);
}
var result = new AmortizationScheduleResult();
var row = 35;
CurrentTask = string.Format("Current Row is {0}", row);
for (int i = row; i < 500; i++)
{
CurrentTask = string.Format("Row is {0}, reading {1}", row, "endBalance");
var endBalance = sheet.GetCellValue(i, "J", 0.0m);
CurrentTask = string.Format("Row is {0}, reading {1}", row, "payment");
var payment = sheet.GetCellValue(i, "F", 0.0m);
CurrentTask = string.Format("Row is {0}, reading {1}", row, "into amort items....");
CurrentTask = string.Format("Row is {0}, item {1} - Month:{2}, ItemDate:{3}, BeginningBalance:{4}, Principal:{5}, Interest:{6}, Balloon:{7}",
row, "AmortizationScheduleItem"
, sheet.GetCellValue(i, "C", 0)
, sheet.GetCellValue(i, "D", DateTime.MinValue)
, sheet.GetCellValue(i, "E", 0.0m)
, sheet.GetCellValue(i, "G", 0.0m)
, sheet.GetCellValue(i, "H", 0.0m)
, sheet.GetCellValue(i, "I", 0.0m)
);
result.AmortizationScheduleItemList.Add(new AmortizationScheduleItem()
{
Month = sheet.GetCellValue(i, "C", 0),
ItemDate = sheet.GetCellValue(i, "D", DateTime.MinValue),
Factor = 0,
BeginningBalance = sheet.GetCellValue(i, "E", 0.0m),
Payment = payment,
Principal = sheet.GetCellValue(i, "G", 0.0m),
Interest = sheet.GetCellValue(i, "H", 0.0m),
Balloon = sheet.GetCellValue(i, "I", 0.0m),
EndBalance = endBalance
});
//}
if (endBalance == 0)
{
break;
}
}
CurrentTask = string.Format("Retuning through file {0}", result.GeneratedFileName);
result.GeneratedFileName = this.GeneratedFileName;
return(result);
}
catch (Exception ex)
{
throw new Exception(string.Format("Error while calculating Amortization Calculator. {0}. {1}", ex.Message, CurrentTask), ex);
}
finally
{
try
{
SaveToFile(this.GeneratedFileName);
}
catch (Exception)
{
}
}
}
