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++;
}
}
