首页下NPOI
/// <summary>
///将Excel转为DataTable
/// </ summary>
/// <param name =“filePath”>文件路径</ param>
/// <returns> </ returns>
public DataTable ExcelToDataTable(string filePath,string sheetName)
{
var dt = new DataTable();
int startRow = 0;
ISheet sheet = null;
using(FileStream file = new FileStream(filePath,FileMode.Open,FileAccess.Read))
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
if(sheetName!=“”)
{
sheet = hssfworkbook.CreateSheet(sheetName);
else
{
sheet = hssfworkbook.GetSheetAt(0);
}
if(sheet!= null)
{
IRow firstRow = sheet.GetRow(0);
int cellCount = firstRow.LastCellNum; //一行最后一个单元的编号,即为总的列数
for(int i = firstRow.FirstCellNum; 我<cellCount; i ++)
{
ICell cell = firstRow.GetCell(i);
if(cell!= null)
{
string cellValue = cell.StringCellValue;
if(cellValue!= null)
{
DataColumn column = new DataColumn(cellValue);
dt.Columns.Add(列);
}
}
}
STARTROW = sheet.FirstRowNum + 1;
int rowCount = sheet.LastRowNum;
for(int i = startRow; 我<= rowCount; i ++)
{
IRow row = sheet.GetRow(i);
if(row == null)继续;
DataRow dataRow = dt.NewRow();
for(int j = row.FirstCellNum; j <cellCount; j ++)
{
if(row.GetCell(j)!= null)
dataRow [j] = row.GetCell(j).ToString();
}
dt.Rows.Add(dataRow);
}
return dt;
}
return dt;
}
}