/**
* Verify that each <code>criteria</code> predicate is valid, i.e. not an error
*
* @throws EvaluationException if there are criteria which resulted in Errors.
*/
internal static void ValidateCriteria(IMatchPredicate[] criteria)
{
foreach (IMatchPredicate predicate in criteria)
{
// check for errors in predicate and return immediately using this error code
if (predicate is NPOI.SS.Formula.Functions.Countif.ErrorMatcher)
{
throw new EvaluationException(
ErrorEval.ValueOf(((NPOI.SS.Formula.Functions.Countif.ErrorMatcher)predicate).Value));
}
}
}
private static void ConfirmErrorResult(String msgPrefix, int expectedErrorCode, CellValue actual)
{
if (actual.CellType != CellType.Error)
{
throw new AssertionException(msgPrefix + " Expected cell error ("
+ ErrorEval.GetText(expectedErrorCode) + ") but actual value was "
+ actual.FormatAsString());
}
if (expectedErrorCode != actual.ErrorValue)
{
throw new AssertionException(msgPrefix + " Expected cell error code ("
+ ErrorEval.GetText(expectedErrorCode)
+ ") but actual error code was ("
+ ErrorEval.GetText(actual.ErrorValue)
+ ")");
}
}
private static void ConfirmErrorResult(String msgPrefix, int expectedErrorCode, NPOI.SS.UserModel.CellValue actual)
{
if (actual.CellType != NPOI.SS.UserModel.CellType.ERROR)
{
throw new AssertFailedException(msgPrefix + " Expected cell error ("
+ ErrorEval.GetText(expectedErrorCode) + ") but actual value was "
+ FormatValue(actual));
}
if (expectedErrorCode != actual.ErrorValue)
{
throw new AssertFailedException(msgPrefix + " Expected cell error code ("
+ ErrorEval.GetText(expectedErrorCode)
+ ") but actual error code was ("
+ ErrorEval.GetText(actual.ErrorValue)
+ ")");
}
}
public String FormatAsString()
{
switch (_cellType)
{
case CellType.Numeric:
return(_numberValue.ToString(CultureInfo.InvariantCulture));
case CellType.String:
return('"' + _textValue + '"');
case CellType.Boolean:
return(_boolValue ? "TRUE" : "FALSE");
case CellType.Error:
return(ErrorEval.GetText(_errorCode));
}
return("<error unexpected cell type " + _cellType + ">");
}
public String FormatAsString()
{
switch (_cellType)
{
case NPOI.SS.UserModel.CellType.NUMERIC:
return(_numberValue.ToString());
case NPOI.SS.UserModel.CellType.STRING:
return('"' + _textValue + '"');
case NPOI.SS.UserModel.CellType.BOOLEAN:
return(_booleanValue ? "TRUE" : "FALSE");
case NPOI.SS.UserModel.CellType.ERROR:
return(ErrorEval.GetText(_errorCode));
}
return("<error unexpected cell type " + _cellType + ">");
}
public String FormatAsString()
{
switch (_cellType)
{
case CellType.NUMERIC:
return(_numberValue.ToString(CultureInfo.InvariantCulture));
case CellType.STRING:
return('"' + _textValue + '"');
case CellType.BOOLEAN:
return(_boolValue ? "TRUE" : "FALSE");
case CellType.ERROR:
return(ErrorEval.GetText(_errorCode));
}
return("<error unexpected cell type " + _cellType + ">");
}
private static void Confirm(IFormulaEvaluator fe, ICell cell, String formula,
ErrorEval expectedResult)
{
fe.ClearAllCachedResultValues();
cell.CellFormula = (formula);
CellValue cv = fe.Evaluate(cell);
if (cv.CellType != CellType.Error)
{
throw new AssertionException("expected error cell type but got " + cv.FormatAsString());
}
int expCode = expectedResult.ErrorCode;
if (cv.ErrorValue != expCode)
{
throw new AssertionException("Expected error '" + ErrorEval.GetText(expCode)
+ "' but got '" + cv.FormatAsString() + "'.");
}
}
private static void ConfirmExpectedResult(String msg, ICell expected, CellValue actual)
{
if (expected == null)
{
throw new AssertionException(msg + " - Bad Setup data expected value is null");
}
if (actual == null)
{
throw new AssertionException(msg + " - actual value was null");
}
switch (expected.CellType)
{
case CellType.BLANK:
Assert.AreEqual(CellType.BLANK, actual.CellType, msg);
break;
case CellType.BOOLEAN:
Assert.AreEqual(CellType.BOOLEAN, actual.CellType, msg);
Assert.AreEqual(expected.BooleanCellValue, actual.BooleanValue, msg);
break;
case CellType.ERROR:
Assert.AreEqual(CellType.ERROR, actual.CellType, msg);
Assert.AreEqual(ErrorEval.GetText(expected.ErrorCellValue), ErrorEval.GetText(actual.ErrorValue), msg);
break;
case CellType.FORMULA: // will never be used, since we will call method After formula Evaluation
throw new AssertionException("Cannot expect formula as result of formula Evaluation: " + msg);
case CellType.NUMERIC:
Assert.AreEqual(CellType.NUMERIC, actual.CellType, msg);
AbstractNumericTestCase.AssertEqual(msg, expected.NumericCellValue, actual.NumberValue,
AbstractNumericTestCase.POS_ZERO, AbstractNumericTestCase.DIFF_TOLERANCE_FACTOR);
break;
case CellType.STRING:
Assert.AreEqual(CellType.STRING, actual.CellType, msg);
Assert.AreEqual(expected.RichStringCellValue.String, actual.StringValue, msg);
break;
}
}
private static void ConfirmExpectedResult(string msg, ICell expected, CellValue actual)
{
if (expected == null)
{
throw new AssertFailedException(msg + " - Bad Setup data expected value is null");
}
if (actual == null)
{
throw new AssertFailedException(msg + " - actual value was null");
}
switch (expected.CellType)
{
case CellType.Blank:
Assert.AreEqual(CellType.Blank, actual.CellType, msg);
break;
case CellType.Boolean:
Assert.AreEqual(CellType.Boolean, actual.CellType, msg);
Assert.AreEqual(expected.BooleanCellValue, actual.BooleanValue, msg);
break;
case CellType.Error:
Assert.AreEqual(CellType.Error, actual.CellType, msg);
Assert.AreEqual(msg, ErrorEval.GetText(expected.ErrorCellValue), ErrorEval.GetText(actual.ErrorValue));
break;
case CellType.Formula: // will never be used, since we will call method After formula Evaluation
throw new AssertFailedException("Cannot expect formula as result of formula Evaluation: " + msg);
case CellType.Numeric:
Assert.AreEqual(CellType.Numeric, actual.CellType, msg);
TestMathX.AssertEquals(msg, expected.NumericCellValue, actual.NumberValue, TestMathX.POS_ZERO, TestMathX.DIFF_TOLERANCE_FACTOR);
break;
case CellType.String:
Assert.AreEqual(CellType.String, actual.CellType, msg);
Assert.AreEqual(msg, expected.RichStringCellValue.String, actual.StringValue);
break;
}
}
public void Test3args()
{
ValueEval[] args =
{
new NumberEval(0.005),
new NumberEval(24),
new NumberEval(1000),
};
ValueEval ev = invoke(args);
if (ev is ErrorEval)
{
ErrorEval err = (ErrorEval)ev;
if (err.ErrorCode == FormulaError.VALUE.Code)
{
Assert.Fail("Identified bug 44691");
}
}
Confirm(-44.3206, invokeNormal(args));
}
public void Test3args()
{
ValueEval[] args =
{
new NumberEval(0.005),
new NumberEval(24),
new NumberEval(1000),
};
ValueEval ev = invoke(args);
if (ev is ErrorEval)
{
ErrorEval err = (ErrorEval)ev;
if (err.ErrorCode == HSSFErrorConstants.ERROR_VALUE)
{
throw new AssertionException("Identified bug 44691");
}
}
Confirm(-44.3206, invokeNormal(args));
}
public string FormatAsString()
{
switch (_cellType)
{
case CellType.Numeric:
string result = _numberValue.ToString(CultureInfo.InvariantCulture);
//if (result.IndexOf(".") < 0)
// result = result + ".0";
return(result);
case CellType.String:
return('"' + _textValue + '"');
case CellType.Boolean:
return(_boolValue ? "TRUE" : "FALSE");
case CellType.Error:
return(ErrorEval.GetText(_errorCode));
}
return("<error unexpected cell type " + _cellType + ">");
}
private static void ConfirmExpectedResult(String msg, ICell expected, CellValue actual)
{
Assert.IsNotNull(expected, msg + " - Bad setup data expected value is null");
Assert.IsNotNull(actual, msg + " - actual value was null");
switch (expected.CellType)
{
case CellType.Blank:
Assert.AreEqual(CellType.Blank, actual.CellType, msg);
break;
case CellType.Boolean:
Assert.AreEqual(CellType.Boolean, actual.CellType, msg);
Assert.AreEqual(expected.BooleanCellValue, actual.BooleanValue, msg);
break;
case CellType.Error:
Assert.AreEqual(CellType.Error, actual.CellType, msg);
Assert.AreEqual(ErrorEval.GetText(expected.ErrorCellValue), ErrorEval.GetText(actual.ErrorValue), msg);
break;
case CellType.Formula: // will never be used, since we will call method After formula Evaluation
Assert.Fail("Cannot expect formula as result of formula Evaluation: " + msg);
break;
case CellType.Numeric:
Assert.AreEqual(CellType.Numeric, actual.CellType, msg);
AbstractNumericTestCase.AssertEquals(msg, expected.NumericCellValue, actual.NumberValue,
AbstractNumericTestCase.POS_ZERO, AbstractNumericTestCase.DIFF_TOLERANCE_FACTOR);
break;
case CellType.String:
Assert.AreEqual(CellType.String, actual.CellType, msg);
Assert.AreEqual(expected.RichStringCellValue.String, actual.StringValue, msg);
break;
}
}
private ValueEval convertObjectEval(Object token)
{
if (token == null)
{
throw new ArgumentNullException("Array item cannot be null");
}
if (token is String)
{
return(new StringEval((String)token));
}
if (token is Double)
{
return(new NumberEval(((Double)token)));
}
if (token is Boolean)
{
return(BoolEval.ValueOf((Boolean)token));
}
if (token is Constant.ErrorConstant)
{
return(ErrorEval.ValueOf(((Constant.ErrorConstant)token).ErrorCode));
}
throw new ArgumentException("Unexpected constant class (" + token.GetType().Name + ")");
}
/// <summary>
/// Excel文件导成Datatable
/// </summary>
/// <param name="strFilePath">Excel文件目录地址</param>
/// <param name="strTableName">Datatable表名</param>
/// <param name="iSheetIndex">Excel sheet index</param>
/// <returns></returns>
public static DataTable XlSToDataTable(string strFilePath, string strTableName, int iSheetIndex)
{
string strExtName = Path.GetExtension(strFilePath);
DataTable dt = new DataTable();
if (!string.IsNullOrEmpty(strTableName))
{
dt.TableName = strTableName;
}
if (strExtName.Equals(".xls") || strExtName.Equals(".xlsx"))
{
using (FileStream file = new FileStream(strFilePath, FileMode.Open, FileAccess.Read))
{
//兼容excel 2003和2007(使用接口，自动识别excel2003/2007格式)
IWorkbook workbook = WorkbookFactory.Create(file);
//HSSFWorkbook workbook = new HSSFWorkbook(file);
ISheet sheet = workbook.GetSheetAt(iSheetIndex);
//列头
foreach (ICell item in sheet.GetRow(sheet.FirstRowNum).Cells)
{
dt.Columns.Add(item.ToString(), typeof(string));
}
//写入内容
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
while (rows.MoveNext())
{
IRow row = (IRow)rows.Current;
if (row != null)
{
if (row.RowNum == sheet.FirstRowNum)
{
continue;
}
DataRow dr = dt.NewRow();
foreach (ICell item in row.Cells)
{
switch (item.CellType)
{
case CellType.Boolean:
dr[item.ColumnIndex] = item.BooleanCellValue;
break;
case CellType.Error:
dr[item.ColumnIndex] = ErrorEval.GetText(item.ErrorCellValue);
break;
case CellType.Formula:
switch (item.CachedFormulaResultType)
{
case CellType.Boolean:
dr[item.ColumnIndex] = item.BooleanCellValue;
break;
case CellType.Error:
dr[item.ColumnIndex] = ErrorEval.GetText(item.ErrorCellValue);
break;
case CellType.Numeric:
if (DateUtil.IsCellDateFormatted(item))
{
dr[item.ColumnIndex] =
item.DateCellValue.ToString("yyyy-MM-dd hh:MM:ss");
}
else
{
dr[item.ColumnIndex] = item.NumericCellValue;
}
break;
case CellType.String:
string str = item.StringCellValue;
if (!string.IsNullOrEmpty(str))
{
dr[item.ColumnIndex] = str.ToString();
}
else
{
dr[item.ColumnIndex] = null;
}
break;
case CellType.Unknown:
case CellType.Blank:
default:
dr[item.ColumnIndex] = string.Empty;
break;
}
break;
case CellType.Numeric:
if (DateUtil.IsCellDateFormatted(item))
{
dr[item.ColumnIndex] = item.DateCellValue.ToString("yyyy-MM-dd hh:MM:ss");
}
else
{
dr[item.ColumnIndex] = item.NumericCellValue;
}
break;
case CellType.String:
string strValue = item.StringCellValue;
if (!string.IsNullOrEmpty(strValue))
{
dr[item.ColumnIndex] = strValue.ToString();
}
else
{
dr[item.ColumnIndex] = string.Empty;
}
break;
case CellType.Unknown:
case CellType.Blank:
default:
dr[item.ColumnIndex] = string.Empty;
break;
}
}
dt.Rows.Add(dr);
}
}
}
}
return(dt);
}
/// <summary>
/// 将excel导入到datatable
/// </summary>
/// <param name="filePath">excel路径</param>
/// <param name="isColumnName">第一行是否是列名</param>
/// <returns>返回datatable</returns>
public static DataTable ExcelToDataTable(string filePath, bool isColumnName, string logPath, string successLogPath)
{
DataTable dataTable = null;
FileStream fs = null;
DataColumn column = null;
DataRow dataRow = null;
IWorkbook workbook = null;
ISheet sheet = null;
IRow row = null;
ICell cell = null;
int startRow = 0;
try
{
using (fs = File.OpenRead(filePath))
{
// 2007版本
if (filePath.IndexOf(".xlsx") > 0)
{
workbook = new XSSFWorkbook(fs);
}
// 2003版本
else if (filePath.IndexOf(".xls") > 0)
{
workbook = new HSSFWorkbook(fs);
}
if (workbook != null)
{
sheet = workbook.GetSheetAt(0);//读取第一个sheet，当然也可以循环读取每个sheet
dataTable = new DataTable();
if (sheet != null)
{
int rowCount = sheet.LastRowNum;//总行数
if (rowCount > 0)
{
IRow firstRow = sheet.GetRow(0); //第一行
int cellCount = firstRow.LastCellNum; //列数
//构建datatable的列
if (isColumnName)
{
startRow = 1;//如果第一行是列名，则从第二行开始读取
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
cell = firstRow.GetCell(i);
if (cell != null)
{
if (cell.StringCellValue != null)
{
column = new DataColumn(cell.StringCellValue);
dataTable.Columns.Add(column);
}
}
}
}
else
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
column = new DataColumn("column" + (i + 1));
dataTable.Columns.Add(column);
}
}
//填充行
for (int i = startRow; i <= rowCount; ++i)
{
row = sheet.GetRow(i);
if (row == null)
{
continue;
}
dataRow = dataTable.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
cell = row.GetCell(j);
if (cell == null)
{
dataRow[j] = "";
}
else
{
//CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)
//switch (cell.CellType)
//{
// case CellType.Blank:
// dataRow[j] = "";
// break;
// case CellType.Numeric:
// dataRow[j] = cell.DateCellValue;
// //short format = cell.CellStyle.DataFormat;
// ////对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理
// //if (format == 14 || format == 31 || format == 57 || format == 58)
// // dataRow[j] = cell.DateCellValue;
// //else
// // dataRow[j] = cell.NumericCellValue;
// break;
// case CellType.String:
// dataRow[j] = cell.StringCellValue;
// break;
//}
switch (cell.CellType)
{
case CellType.String:
string str = row.GetCell(j).StringCellValue;
if (str != null && str.Length > 0)
{
dataRow[j] = str.ToString();
}
else
{
dataRow[j] = null;
}
break;
case CellType.Numeric:
if (DateUtil.IsCellDateFormatted(row.GetCell(j)))
{
dataRow[j] = DateTime.FromOADate(row.GetCell(j).NumericCellValue);
}
else
{
dataRow[j] = Convert.ToDouble(row.GetCell(j).NumericCellValue);
}
break;
case CellType.Boolean:
dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.Error:
dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
case CellType.Formula:
switch (row.GetCell(j).CachedFormulaResultType)
{
case CellType.String:
string strFORMULA = row.GetCell(j).StringCellValue;
if (strFORMULA != null && strFORMULA.Length > 0)
{
dataRow[j] = strFORMULA.ToString();
}
else
{
dataRow[j] = null;
}
break;
case CellType.Numeric:
dataRow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);
break;
case CellType.Boolean:
dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.Error:
dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
default:
dataRow[j] = "";
break;
}
break;
default:
dataRow[j] = "";
break;
}
}
}
dataTable.Rows.Add(dataRow);
}
}
}
}
}
return(dataTable);
}
catch (Exception ex)
{
if (fs != null)
{
fs.Close();
}
LogHelper.WriteLog("ExcelToDataTable method error:" + ex.ToString(), logPath);
return(null);
}
}
private static void ConfirmPredicate(bool expectedResult, I_MatchPredicate matchPredicate, ErrorEval value)
{
Assert.AreEqual(expectedResult, matchPredicate.Matches(value));
}
/// <summary>
/// 将制定sheet中的数据导出到datatable中
/// </summary>
/// <param name="sheet">需要导出的sheet</param>
/// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
/// <returns></returns>
static DataTable ImportDt(HSSFSheet sheet, int HeaderRowIndex, bool needHeader)
{
DataTable table = new DataTable();
HSSFRow headerRow;
int cellCount;
try
{
if (HeaderRowIndex < 0 || !needHeader)
{
headerRow = sheet.GetRow(0) as HSSFRow;
cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
{
DataColumn column = new DataColumn(Convert.ToString(i));
table.Columns.Add(column);
}
}
else
{
headerRow = sheet.GetRow(HeaderRowIndex) as HSSFRow;
cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
{
if (headerRow.GetCell(i) == null)
{
if (table.Columns.IndexOf(Convert.ToString(i)) > 0)
{
DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
table.Columns.Add(column);
}
else
{
DataColumn column = new DataColumn(Convert.ToString(i));
table.Columns.Add(column);
}
}
else if (table.Columns.IndexOf(headerRow.GetCell(i).ToString()) > 0)
{
DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
table.Columns.Add(column);
}
else
{
DataColumn column = new DataColumn(headerRow.GetCell(i).ToString());
table.Columns.Add(column);
}
}
}
int rowCount = sheet.LastRowNum;
for (int i = (HeaderRowIndex + 1); i <= sheet.LastRowNum; i++)
{
try
{
HSSFRow row;
if (sheet.GetRow(i) == null)
{
row = sheet.CreateRow(i) as HSSFRow;
}
else
{
row = sheet.GetRow(i) as HSSFRow;
}
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j <= cellCount; j++)
{
try
{
if (row.GetCell(j) != null)
{
switch (row.GetCell(j).CellType)
{
case CellType.STRING:
string str = row.GetCell(j).StringCellValue;
if (str != null && str.Length > 0)
{
dataRow[j] = str.ToString();
}
else
{
dataRow[j] = null;
}
break;
case CellType.NUMERIC:
if (DateUtil.IsCellDateFormatted(row.GetCell(j)))
{
dataRow[j] = DateTime.FromOADate(row.GetCell(j).NumericCellValue);
}
else
{
dataRow[j] = Convert.ToDouble(row.GetCell(j).NumericCellValue);
}
break;
case CellType.BOOLEAN:
dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.ERROR:
dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
case CellType.FORMULA:
switch (row.GetCell(j).CachedFormulaResultType)
{
case CellType.STRING:
string strFORMULA = row.GetCell(j).StringCellValue;
if (strFORMULA != null && strFORMULA.Length > 0)
{
dataRow[j] = strFORMULA.ToString();
}
else
{
dataRow[j] = null;
}
break;
case CellType.NUMERIC:
dataRow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);
break;
case CellType.BOOLEAN:
dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.ERROR:
dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
default:
dataRow[j] = "";
break;
}
break;
default:
dataRow[j] = "";
break;
}
}
}
catch (Exception exception)
{
//wl.WriteLogs(exception.ToString());
}
}
table.Rows.Add(dataRow);
}
catch (Exception exception)
{
//wl.WriteLogs(exception.ToString());
}
}
}
catch (Exception exception)
{
//wl.WriteLogs(exception.ToString());
}
return(table);
}
/**
* returns an appropriate Eval impl instance for the Ptg. The Ptg must be
* one of: Area3DPtg, AreaPtg, ReferencePtg, Ref3DPtg, IntPtg, NumberPtg,
* StringPtg, BoolPtg <br/>special Note: OperationPtg subtypes cannot be
* passed here!
*/
private ValueEval GetEvalForPtg(Ptg ptg, OperationEvaluationContext ec)
{
// consider converting all these (ptg is XxxPtg) expressions To (ptg.GetType() == XxxPtg.class)
if (ptg is NamePtg)
{
// named ranges, macro functions
NamePtg namePtg = (NamePtg)ptg;
IEvaluationName nameRecord = _workbook.GetName(namePtg);
if (nameRecord.IsFunctionName)
{
return(new NameEval(nameRecord.NameText));
}
if (nameRecord.HasFormula)
{
return(EvaluateNameFormula(nameRecord.NameDefinition, ec));
}
throw new Exception("Don't now how To evalate name '" + nameRecord.NameText + "'");
}
if (ptg is NameXPtg)
{
return(ec.GetNameXEval(((NameXPtg)ptg)));
}
if (ptg is IntPtg)
{
return(new NumberEval(((IntPtg)ptg).Value));
}
if (ptg is NumberPtg)
{
return(new NumberEval(((NumberPtg)ptg).Value));
}
if (ptg is StringPtg)
{
return(new StringEval(((StringPtg)ptg).Value));
}
if (ptg is BoolPtg)
{
return(BoolEval.ValueOf(((BoolPtg)ptg).Value));
}
if (ptg is ErrPtg)
{
return(ErrorEval.ValueOf(((ErrPtg)ptg).ErrorCode));
}
if (ptg is MissingArgPtg)
{
return(MissingArgEval.instance);
}
if (ptg is AreaErrPtg || ptg is RefErrorPtg ||
ptg is DeletedArea3DPtg || ptg is DeletedRef3DPtg)
{
return(ErrorEval.REF_INVALID);
}
if (ptg is Ref3DPtg)
{
Ref3DPtg rptg = (Ref3DPtg)ptg;
return(ec.GetRef3DEval(rptg.Row, rptg.Column, rptg.ExternSheetIndex));
}
if (ptg is Area3DPtg)
{
Area3DPtg aptg = (Area3DPtg)ptg;
return(ec.GetArea3DEval(aptg.FirstRow, aptg.FirstColumn, aptg.LastRow, aptg.LastColumn, aptg.ExternSheetIndex));
}
if (ptg is RefPtg)
{
RefPtg rptg = (RefPtg)ptg;
return(ec.GetRefEval(rptg.Row, rptg.Column));
}
if (ptg is AreaPtg)
{
AreaPtg aptg = (AreaPtg)ptg;
return(ec.GetAreaEval(aptg.FirstRow, aptg.FirstColumn, aptg.LastRow, aptg.LastColumn));
}
if (ptg is UnknownPtg)
{
// POI uses UnknownPtg when the encoded Ptg array seems To be corrupted.
// This seems To occur in very rare cases (e.g. unused name formulas in bug 44774, attachment 21790)
// In any case, formulas are re-parsed before execution, so UnknownPtg should not Get here
throw new RuntimeException("UnknownPtg not allowed");
}
if (ptg is ExpPtg)
{
// ExpPtg is used for array formulas and shared formulas.
// it is currently unsupported, and may not even get implemented here
throw new RuntimeException("ExpPtg currently not supported");
}
throw new RuntimeException("Unexpected ptg class (" + ptg.GetType().Name + ")");
}
public EvalEx(ErrorEval error)
{
_error = error;
}
/// <summary>
/// 将制定sheet中的数据导出到datatable中
/// </summary>
/// <param name="sheet">需要导出的sheet</param>
/// <param name="headerRowIndex">列头所在行号，-1表示没有列头</param>
/// <param name="needHeader"></param>
/// <returns></returns>
static DataTable ImportDt(ISheet sheet, int headerRowIndex, bool needHeader)
{
if (sheet == null)
{
throw new ArgumentNullException("sheet");
}
var table = new DataTable();
try
{
IRow headerRow;
var cellCount = 0;
if (headerRowIndex < 0 || !needHeader)
{
headerRow = sheet.GetRow(0) as XSSFRow;
if (headerRow != null)
{
cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
{
var column = new DataColumn(Convert.ToString(i));
table.Columns.Add(column);
}
}
}
else
{
headerRow = sheet.GetRow(headerRowIndex) as XSSFRow;
if (headerRow != null)
{
cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
if (i == 118)
{
}
if (headerRow.GetCell(i) == null)
{
if (table.Columns.IndexOf(Convert.ToString(i)) > 0)
{
var column = new DataColumn(Convert.ToString("重复列名" + i));
table.Columns.Add(column);
}
else
{
var column = new DataColumn(Convert.ToString(i));
table.Columns.Add(column);
}
}
else if (table.Columns.IndexOf(headerRow.GetCell(i).ToString()) > 0)
{
var column = new DataColumn(Convert.ToString("重复列名" + i));
table.Columns.Add(column);
}
else
{
var column = new DataColumn(headerRow.GetCell(i).ToString());
table.Columns.Add(column);
}
}
}
}
for (var i = (headerRowIndex + 1); i <= sheet.LastRowNum; i++)
{
try
{
IRow row;
if (sheet.GetRow(i) == null)
{
row = sheet.CreateRow(i) as XSSFRow;
}
else
{
row = sheet.GetRow(i) as XSSFRow;
}
var dataRow = table.NewRow();
for (int j = row.FirstCellNum; j <= cellCount; j++)
{
try
{
if (row.GetCell(j) == null)
{
continue;
}
switch (row.GetCell(j).CellType)
{
case CellType.String:
var str = row.GetCell(j).StringCellValue;
if (!string.IsNullOrEmpty(str))
{
dataRow[j] = str;
}
else
{
dataRow[j] = null;
}
break;
case CellType.Numeric:
dataRow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);
break;
case CellType.Boolean:
dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.Error:
dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
default:
dataRow[j] = "";
break;
}
}
catch (Exception exception)
{
LoggerManager.Error(exception.ToString());
}
}
table.Rows.Add(dataRow);
}
catch (Exception exception)
{
LoggerManager.Error(exception.ToString());
}
}
}
catch (Exception exception)
{
LoggerManager.Error(exception.ToString());
}
return(table);
}
protected object GetCellValue(ICell item)
{
if (item == null)
{
return(string.Empty);
}
switch (item.CellType)
{
case CellType.Boolean:
return(item.BooleanCellValue);
case CellType.Error:
return(ErrorEval.GetText(item.ErrorCellValue));
case CellType.Formula:
switch (item.CachedFormulaResultType)
{
case CellType.Boolean:
return(item.BooleanCellValue);
case CellType.Error:
return(ErrorEval.GetText(item.ErrorCellValue));
case CellType.Numeric:
if (DateUtil.IsCellDateFormatted(item))
{
return(item.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss"));
}
else
{
return(item.NumericCellValue);
}
case CellType.String:
var str = item.StringCellValue;
if (!string.IsNullOrEmpty(str))
{
return(str);
}
else
{
return(string.Empty);
}
case CellType.Unknown:
case CellType.Blank:
default:
return(string.Empty);
}
case CellType.Numeric:
if (DateUtil.IsCellDateFormatted(item))
{
return(item.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss"));
}
else
{
return(item.NumericCellValue);
}
case CellType.String:
var strValue = item.StringCellValue;
return(strValue.Trim());
case CellType.Unknown:
case CellType.Blank:
default:
return(string.Empty);
}
}
/// <summary>
/// 将制定sheet中的数据导出到datatable中
/// </summary>
/// <param name="sheet">需要导出的sheet</param>
/// <param name="headerRowIndex">列头所在行号，-1表示没有列头</param>
/// <param name="needHeader"></param>
/// <returns></returns>
static DataTable ImportDt(ISheet sheet, int headerRowIndex, bool needHeader)
{
DataTable table = new DataTable();
IRow headerRow;
int cellCount;
if (headerRowIndex < 0 || !needHeader)
{
headerRow = sheet.GetRow(0);
cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
{
DataColumn column = new DataColumn(Convert.ToString(i));
table.Columns.Add(column);
}
}
else
{
headerRow = sheet.GetRow(headerRowIndex);
cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
{
if (headerRow.GetCell(i) == null)
{
if (table.Columns.IndexOf(Convert.ToString(i)) > 0)
{
DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
table.Columns.Add(column);
}
else
{
DataColumn column = new DataColumn(Convert.ToString(i));
table.Columns.Add(column);
}
}
else if (table.Columns.IndexOf(headerRow.GetCell(i).ToString()) > 0)
{
DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
table.Columns.Add(column);
}
else
{
DataColumn column = new DataColumn(headerRow.GetCell(i).ToString());
table.Columns.Add(column);
}
}
}
for (int i = (headerRowIndex + 1); i <= sheet.LastRowNum; i++)
{
IRow row;
if (sheet.GetRow(i) == null)
{
row = sheet.CreateRow(i);
}
else
{
row = sheet.GetRow(i);
}
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j <= cellCount; j++)
{
if (row.GetCell(j) != null)
{
switch (row.GetCell(j).CellType)
{
case CellType.String:
string str = row.GetCell(j).StringCellValue;
if (str != null && str.Length > 0)
{
dataRow[j] = str;
}
else
{
dataRow[j] = null;
}
break;
case CellType.Numeric:
if (DateUtil.IsCellDateFormatted(row.GetCell(j)))
{
dataRow[j] = DateTime.FromOADate(row.GetCell(j).NumericCellValue);
}
else
{
dataRow[j] = Convert.ToDouble(row.GetCell(j).NumericCellValue);
}
break;
case CellType.Boolean:
dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.Error:
dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
case CellType.Formula:
switch (row.GetCell(j).CachedFormulaResultType)
{
case CellType.String:
string strFormula = row.GetCell(j).StringCellValue;
if (!string.IsNullOrEmpty(strFormula))
{
dataRow[j] = strFormula;
}
else
{
dataRow[j] = null;
}
break;
case CellType.Numeric:
dataRow[j] = Convert.ToString(row.GetCell(j).NumericCellValue,
CultureInfo.InvariantCulture);
break;
case CellType.Boolean:
dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.Error:
dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
default:
dataRow[j] = "";
break;
}
break;
default:
dataRow[j] = "";
break;
}
}
}
table.Rows.Add(dataRow);
}
return(table);
}
private void ConfirmError(ValueEval xArray, ValueEval yArray, ErrorEval expectedError)
{
ConfirmError(SUM_SQUARES, xArray, yArray, expectedError);
ConfirmError(DIFF_SQUARES, xArray, yArray, expectedError);
ConfirmError(SUM_SQUARES_OF_DIFFS, xArray, yArray, expectedError);
}
private static void ConfirmValueError(string msg, string real_num, string i_num, string suffix, ErrorEval numError)
{
ValueEval result = invokeValue(real_num, i_num, suffix);
Assert.AreEqual(typeof(ErrorEval), result.GetType());
Assert.AreEqual(numError, result, msg);
}
private static void ConfirmValueError(String msg, String numerator, String denominator, ErrorEval numError)
{
ValueEval result = invokeValue(numerator, denominator);
Assert.AreEqual(typeof(ErrorEval), result.GetType());
Assert.AreEqual(numError, result, msg);
}
private void ConfirmError(Function function, ValueEval xArray, ValueEval yArray, ErrorEval expectedError)
{
ValueEval result = invoke(function, xArray, yArray);
Assert.AreEqual(typeof(ErrorEval), result.GetType());
Assert.AreEqual(expectedError.ErrorCode, ((ErrorEval)result).ErrorCode);
}
private string exportsheet(ICell rowCell)
{
if (rowCell == null)
{
return("");
}
object shstring = "";
switch (rowCell.CellType)
{
case CellType.Boolean:
shstring = Convert.ToString(rowCell.BooleanCellValue);
break;
case CellType.Error:
shstring = ErrorEval.GetText(rowCell.ErrorCellValue);
break;
case CellType.Formula:
switch (rowCell.CachedFormulaResultType)
{
case CellType.Boolean:
shstring = Convert.ToString(rowCell.BooleanCellValue);
break;
case CellType.Error:
shstring = ErrorEval.GetText(rowCell.ErrorCellValue);
break;
case CellType.Numeric:
shstring = Convert.ToString(rowCell.NumericCellValue);
break;
case CellType.String:
string strFORMULA = rowCell.StringCellValue;
if (strFORMULA != null && strFORMULA.Length > 0)
{
shstring = strFORMULA.ToString();
}
else
{
shstring = "";
}
break;
default:
shstring = "";
break;
}
break;
case CellType.Numeric:
if (DateUtil.IsCellDateFormatted(rowCell))
{
shstring = DateTime.FromOADate(rowCell.NumericCellValue);
}
else
{
shstring = Convert.ToDouble(rowCell.NumericCellValue);
}
break;
case CellType.String:
string str = rowCell.StringCellValue;
if (!string.IsNullOrEmpty(str))
{
shstring = Convert.ToString(str);
}
else
{
shstring = null;
}
break;
default:
shstring = "";
break;
}
shstring = shstring == null ? "" : shstring;
return(shstring.ToString());
}
private void ConfirmError(ValueEval xArray, ValueEval yArray, ErrorEval expectedError)
{
ConfirmError(SLOPE, xArray, yArray, expectedError);
}
private double EvaluateInternal(ValueVector x, ValueVector y, int size)
{
// error handling is as if the x is fully Evaluated before y
ErrorEval firstXerr = null;
ErrorEval firstYerr = null;
bool accumlatedSome = false;
// first pass: read in data, compute xbar and ybar
double sumx = 0.0, sumy = 0.0;
for (int i = 0; i < size; i++)
{
ValueEval vx = x.GetItem(i);
ValueEval vy = y.GetItem(i);
if (vx is ErrorEval)
{
if (firstXerr == null)
{
firstXerr = (ErrorEval)vx;
continue;
}
}
if (vy is ErrorEval)
{
if (firstYerr == null)
{
firstYerr = (ErrorEval)vy;
continue;
}
}
// only count pairs if both elements are numbers
if (vx is NumberEval && vy is NumberEval)
{
accumlatedSome = true;
NumberEval nx = (NumberEval)vx;
NumberEval ny = (NumberEval)vy;
sumx += nx.NumberValue;
sumy += ny.NumberValue;
}
else
{
// all other combinations of value types are silently ignored
}
}
double xbar = sumx / size;
double ybar = sumy / size;
// second pass: compute summary statistics
double xxbar = 0.0, xybar = 0.0;
for (int i = 0; i < size; i++)
{
ValueEval vx = x.GetItem(i);
ValueEval vy = y.GetItem(i);
if (vx is ErrorEval)
{
if (firstXerr == null)
{
firstXerr = (ErrorEval)vx;
continue;
}
}
if (vy is ErrorEval)
{
if (firstYerr == null)
{
firstYerr = (ErrorEval)vy;
continue;
}
}
// only count pairs if both elements are numbers
if (vx is NumberEval && vy is NumberEval)
{
NumberEval nx = (NumberEval)vx;
NumberEval ny = (NumberEval)vy;
xxbar += (nx.NumberValue - xbar) * (nx.NumberValue - xbar);
xybar += (nx.NumberValue - xbar) * (ny.NumberValue - ybar);
}
else
{
// all other combinations of value types are silently ignored
}
}
double beta1 = xybar / xxbar;
double beta0 = ybar - beta1 * xbar;
if (firstXerr != null)
{
throw new EvaluationException(firstXerr);
}
if (firstYerr != null)
{
throw new EvaluationException(firstYerr);
}
if (!accumlatedSome)
{
throw new EvaluationException(ErrorEval.DIV_ZERO);
}
if (function == FUNCTION.INTERCEPT)
{
return(beta0);
}
else
{
return(beta1);
}
}