public DataTable GetData(string filePath)
{
HSSFWorkbook hssfworkbook;
#region//初始化信息
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
}
catch (Exception e)
{
throw e;
}
#endregion
using (NPOI.SS.UserModel.Sheet sheet = hssfworkbook.GetSheetAt(0))
{
DataTable table = new DataTable();
NPOI.SS.UserModel.Row headerRow = sheet.GetRow(0); //第一行为标题行
int cellCount = headerRow.LastCellNum; //LastCellNum = PhysicalNumberOfCells
int rowCount = sheet.LastRowNum; //LastRowNum = PhysicalNumberOfRows - 1
//handling header.
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
{
NPOI.SS.UserModel.Row row = sheet.GetRow(i);
DataRow dataRow = table.NewRow();
if (row != null)
{
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
dataRow[j] = GetCellValue(row.GetCell(j));
}
}
}
table.Rows.Add(dataRow);
}
return(table);
}
}
/**
* @return <c>null</c> if cell is1 missing, empty or blank
*/
private static String GetTargetFunctionName(NPOI.SS.UserModel.Row r)
{
if (r == null)
{
Console.Error.WriteLine("Warning - given null row, can't figure out function name");
return(null);
}
Cell cell = r.GetCell(SS.COLUMN_INDEX_FUNCTION_NAME);
if (cell == null)
{
Console.Error.WriteLine("Warning - NPOI.SS.UserModel.Row " + r.RowNum + " has no cell " + SS.COLUMN_INDEX_FUNCTION_NAME + ", can't figure out function name");
return(null);
}
if (cell.CellType == NPOI.SS.UserModel.CellType.BLANK)
{
return(null);
}
if (cell.CellType == NPOI.SS.UserModel.CellType.STRING)
{
return(cell.RichStringCellValue.String);
}
throw new AssertFailedException("Bad cell type for 'function name' column: ("
+ cell.CellType + ") row (" + (r.RowNum + 1) + ")");
}
private static Cell GetExpectedValueCell(NPOI.SS.UserModel.Row row, short columnIndex)
{
if (row == null)
{
return(null);
}
return(row.GetCell(columnIndex));
}
/// <summary>
/// Get a specific cell from a row. If the cell doesn't exist,
/// </summary>
/// <param name="row">The row that the cell is part of</param>
/// <param name="column">The column index that the cell is in.</param>
/// <returns>The cell indicated by the column.</returns>
public static NPOI.SS.UserModel.Cell GetCell(NPOI.SS.UserModel.Row row, int column)
{
NPOI.SS.UserModel.Cell cell = row.GetCell(column);
if (cell == null)
{
cell = row.CreateCell(column);
}
return(cell);
}
/// <summary>
/// Gets the cell to manage it.
/// </summary>
/// <param name="rowPos">The row pos.</param>
/// <param name="columnPos">The column pos.</param>
/// <returns>Cell of the spreadsheet</returns>
public NPOI.SS.UserModel.Cell GetCell(int rowPos, int columnPos)
{
NPOI.SS.UserModel.Row row = _sheet.GetRow(rowPos);
if (row == null)
{
AddRow(rowPos);
row = _sheet.GetRow(rowPos);
}
//Creating the cell
NPOI.SS.UserModel.Cell cell = row.GetCell(columnPos);
if (cell == null)
{
row.CreateCell(columnPos);
cell = row.GetCell(columnPos);
}
return(cell);
}
public void TestCountifFromSpreadsheet()
{
String FILE_NAME = "countifExamples.xls";
int START_ROW_IX = 1;
int COL_IX_ACTUAL = 2;
int COL_IX_EXPECTED = 3;
int failureCount = 0;
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook(FILE_NAME);
NPOI.SS.UserModel.Sheet sheet = wb.GetSheetAt(0);
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
int maxRow = sheet.LastRowNum;
for (int rowIx = START_ROW_IX; rowIx < maxRow; rowIx++)
{
NPOI.SS.UserModel.Row row = sheet.GetRow(rowIx);
if (row == null)
{
continue;
}
Cell cell = row.GetCell(COL_IX_ACTUAL);
NPOI.SS.UserModel.CellValue cv = fe.Evaluate(cell);
double actualValue = cv.NumberValue;
double expectedValue = row.GetCell(COL_IX_EXPECTED).NumericCellValue;
if (actualValue != expectedValue)
{
Console.Error.WriteLine("Problem with Test case on row " + (rowIx + 1) + " "
+ "Expected = (" + expectedValue + ") Actual=(" + actualValue + ") ");
failureCount++;
}
}
if (failureCount > 0)
{
throw new AssertFailedException(failureCount + " countif evaluations failed. See stderr for more details");
}
}
/**
*
* @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,
NPOI.SS.UserModel.Row formulasRow, NPOI.SS.UserModel.Row expectedValuesRow)
{
int result = Result.NO_EVALUATIONS_FOUND; // so far
int endcolnum = formulasRow.LastCellNum;
//evaluator.SetCurrentRow(formulasRow);
// iterate across the row for all the evaluation cases
for (short colnum = SS.COLUMN_INDEX_FIRST_TEST_VALUE; colnum < endcolnum; colnum++)
{
Cell c = formulasRow.GetCell(colnum);
if (c == null || c.CellType != NPOI.SS.UserModel.CellType.FORMULA)
{
continue;
}
NPOI.SS.UserModel.CellValue actualValue = evaluator.Evaluate(c);
Cell 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 (AssertFailedException)
{
_evaluationFailureCount++;
//printShortStackTrace(System.err, e);
result = Result.SOME_EVALUATIONS_FAILED;
}
}
return(result);
}
/// <summary>
/// 根据Excel获取集合信息
/// </summary>
/// <param name="fileName">Excel存储路径</param>
/// <returns></returns>
public List <IMovieShowList.MovieShow> GetList4Excel(string fileName)
{
List <IMovieShowList.MovieShow> list = new List <IMovieShowList.MovieShow>();
HSSFWorkbook wk = null;
using (FileStream fs = System.IO.File.Open(fileName, FileMode.Open,
FileAccess.Read, FileShare.ReadWrite))
{
//把xls文件读入workbook变量里，之后就可以关闭了
wk = new HSSFWorkbook(fs);
fs.Close();
}
HSSFSheet sheet = (HSSFSheet)wk.GetSheetAt(0);
if (sheet != null)
{
int rowIndex = 0;
NPOI.SS.UserModel.Row row = null;
while ((row = sheet.GetRow(rowIndex)) != null)
{
try
{
Cell c1 = row.GetCell(0);
string room = c1.RichStringCellValue.String;
Cell c2 = row.GetCell(1);
string tim = c2.RichStringCellValue.String;
Cell c3 = row.GetCell(2);
string name = c3.RichStringCellValue.String;
if (string.IsNullOrWhiteSpace(room) || string.IsNullOrWhiteSpace(tim) || string.IsNullOrWhiteSpace(name))
{
rowIndex++;
continue;
}
string time = ParseBeginTime(tim);
list.Add(new IMovieShowList.MovieShow()
{
Room = room,
BeginTime = time,
Name = name
});
rowIndex++;
}
catch (Exception ex)
{
isOk = false;
Msg = "xcel文件中的时间有错误,在" + (rowIndex + 1) + "行" + Environment.NewLine + ex.Message;
break;
}
}
sheet.Dispose();
}
return(list);
}