/** * 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); } }