public static DataTable GetDataFromExcel(string excelFilePath) { //get exist excel file XSSFWorkbook excelBook = new XSSFWorkbook(File.Open(excelFilePath, FileMode.Open)); var sheet = excelBook.GetSheetAt(0); var headerRow = sheet.GetRow(0); var table = new DataTable(); //total columns int cellCount = headerRow.LastCellNum; //get column name for (var i = headerRow.FirstCellNum; i < cellCount; i++) { var columnName = new DataColumn(headerRow.GetCell(i).StringCellValue); table.Columns.Add(columnName); } //total rows int rowCount = sheet.LastRowNum; for (var i = (sheet.FirstRowNum + 1); i <= rowCount; i++) { var row = sheet.GetRow(i); if (row == null) { continue; } var dataRow = table.NewRow(); for (var j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { XSSFFormulaEvaluator e = new XSSFFormulaEvaluator(excelBook);//解析带有公式的Excel var cell = e.EvaluateInCell(row.GetCell(j)); dataRow[j] = cell.ToString(); } } table.Rows.Add(dataRow); } return table; }
/** * @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) { IFormulaEvaluator evaluator = new XSSFFormulaEvaluator(workbook); int rowIndex = startRowIndex; while (true) { IRow r = sheet.GetRow(rowIndex); String targetFunctionName = GetTargetFunctionName(r); if (targetFunctionName == null) { throw new AssertionException("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.OrdinalIgnoreCase)) { // expected results are on the row below IRow expectedValuesRow = sheet.GetRow(rowIndex + 1); if (expectedValuesRow == null) { int missingRowNum = rowIndex + 2; //+1 for 1-based, +1 for next row throw new AssertionException("Missing expected values row for function '" + targetFunctionName + " (row " + missingRowNum + ")"); } switch (ProcessFunctionRow(evaluator, targetFunctionName, r, expectedValuesRow)) { case Result.ALL_EVALUATIONS_SUCCEEDED: _functionSuccessCount++; break; case Result.SOME_EVALUATIONS_FAILED: _functionFailureCount++; break; case Result.NO_EVALUATIONS_FOUND: // do nothing break; default: throw new RuntimeException("unexpected result"); } } rowIndex += SS.NUMBER_OF_ROWS_PER_FUNCTION; } }
public void Test48539() { XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("48539.xlsx"); Assert.AreEqual(3, wb.NumberOfSheets); // Try each cell individually XSSFFormulaEvaluator eval = new XSSFFormulaEvaluator(wb); for (int i = 0; i < wb.NumberOfSheets; i++) { ISheet s = wb.GetSheetAt(i); foreach (IRow r in s) { foreach (ICell c in r) { if (c.CellType == CellType.FORMULA) { CellValue cv = eval.Evaluate(c); if (cv.CellType == CellType.NUMERIC) { // assert that the calculated value agrees with // the cached formula result calculated by Excel double cachedFormulaResult = c.NumericCellValue; double EvaluatedFormulaResult = cv.NumberValue; Assert.AreEqual(cachedFormulaResult, EvaluatedFormulaResult, 1E-7, c.CellFormula); } } } } } // Now all of them XSSFFormulaEvaluator.EvaluateAllFormulaCells(wb); }
/** * Sum across multiple workbooks * eg =SUM($Sheet1.C1:$Sheet4.C1) * DISABLED As we can't currently Evaluate these */ public void DISABLEDtest48703() { XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("48703.xlsx"); XSSFSheet sheet = wb.GetSheetAt(0) as XSSFSheet; // Contains two forms, one with a range and one a list XSSFRow r1 = sheet.GetRow(0) as XSSFRow; XSSFRow r2 = sheet.GetRow(1) as XSSFRow; XSSFCell c1 = r1.GetCell(1) as XSSFCell; XSSFCell c2 = r2.GetCell(1) as XSSFCell; Assert.AreEqual(20.0, c1.NumericCellValue); Assert.AreEqual("SUM(Sheet1!C1,Sheet2!C1,Sheet3!C1,Sheet4!C1)", c1.CellFormula); Assert.AreEqual(20.0, c2.NumericCellValue); Assert.AreEqual("SUM(Sheet1:Sheet4!C1)", c2.CellFormula); // Try Evaluating both XSSFFormulaEvaluator eval = new XSSFFormulaEvaluator(wb); eval.EvaluateFormulaCell(c1); eval.EvaluateFormulaCell(c2); Assert.AreEqual(20.0, c1.NumericCellValue); Assert.AreEqual(20.0, c2.NumericCellValue); }
public void Bug48539() { XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("48539.xlsx"); Assert.AreEqual(3, wb.NumberOfSheets); Assert.AreEqual(0, wb.NumberOfNames); // Try each cell individually XSSFFormulaEvaluator eval = new XSSFFormulaEvaluator(wb); for (int i = 0; i < wb.NumberOfSheets; i++) { ISheet s = wb.GetSheetAt(i); foreach (IRow r in s) { foreach (ICell c in r) { if (c.CellType == CellType.Formula) { String formula = c.CellFormula; CellValue cv; try { cv = eval.Evaluate(c); } catch (Exception e) { throw new RuntimeException("Can't evaluate formula: " + formula, e); } if (cv.CellType == CellType.Numeric) { // assert that the calculated value agrees with // the cached formula result calculated by Excel double cachedFormulaResult = c.NumericCellValue; double EvaluatedFormulaResult = cv.NumberValue; Assert.AreEqual(cachedFormulaResult, EvaluatedFormulaResult, 1E-7, c.CellFormula); } } } } } // Now all of them XSSFFormulaEvaluator.EvaluateAllFormulaCells(wb); }
public void Test57196_Detail2() { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.CreateSheet("Sheet1") as XSSFSheet; XSSFRow row = sheet.CreateRow(0) as XSSFRow; XSSFCell cell = row.CreateCell(0) as XSSFCell; cell.CellFormula = (/*setter*/"DEC2HEX(O2+D2)"); XSSFFormulaEvaluator fe = new XSSFFormulaEvaluator(wb); CellValue cv = fe.Evaluate(cell); Assert.IsNotNull(cv); }
private void CheckValue(XSSFWorkbook excel, String expect) { XSSFFormulaEvaluator Evaluator = new XSSFFormulaEvaluator(excel); Evaluator.EvaluateAll(); XSSFCell cell = excel.GetSheetAt(0).GetRow(1).GetCell(1) as XSSFCell; CellValue value = Evaluator.Evaluate(cell); Assert.AreEqual(expect, value.FormatAsString()); }
/** * @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) { IFormulaEvaluator evaluator = new XSSFFormulaEvaluator(workbook); 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 AssertionException("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 AssertionException("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 break; } } } rowIndex++; } }