/// <summary>
/// excel文件流转化成datatable
/// </summary>
/// <param name="fileStream">文件流</param>
/// <param name="haveNote">是否包含备注</param>
/// <returns></returns>
public static DataTable ExcelToTableForXLSX(Stream fileStream, bool haveNote = false)
{
var dt = new DataTable();
using (var fs = fileStream)
{
var xssfworkbook = new XSSFWorkbook(fs);
var sheet = xssfworkbook.GetSheetAt(0);
//表头  判断是否包含备注
var firstRowNum = sheet.FirstRowNum;
if (haveNote)
{
firstRowNum += 1;
}
var header = sheet.GetRow(firstRowNum);
var columns = new List<int>();
for (var i = 0; i < header.LastCellNum; i++)
{

var obj = GetValueTypeForXLSX(header.GetCell(i) as XSSFCell);
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
//continue;
}
else
dt.Columns.Add(new DataColumn(obj.ToString()));
//LogHelper.WriteLog(obj.ToString());
columns.Add(i);
}
//数据
for (var i = firstRowNum + 1; i <= sheet.LastRowNum; i++)
{
var dr = dt.NewRow();
var hasValue = false;
if (sheet.GetRow(i) == null)
{
continue;
}
foreach (var j in columns)
{
var cell = sheet.GetRow(i).GetCell(j);
if (cell != null && cell.CellType == CellType.Numeric)
{
//NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
if (DateUtil.IsCellDateFormatted(cell)) //日期类型
{
dr[j] = cell.DateCellValue;
}
else //其他数字类型
{
dr[j] = cell.NumericCellValue;
}
}
else
{
dr[j] = GetValueTypeForXLSX(sheet.GetRow(i).GetCell(j) as XSSFCell);
}
if (dr[j] != null && dr[j].ToString() != string.Empty)
{
hasValue = true;
}
}
if (hasValue)
{
dt.Rows.Add(dr);
}
}
}
fileStream.Close();
return dt;
}

/// <summary>
/// 获取单元格类型(xlsx)
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private static object GetValueTypeForXLSX(XSSFCell cell)
{
if (cell == null)
return null;
switch (cell.CellType)
{

case CellType.Blank: //BLANK:
return null;
case CellType.Boolean: //BOOLEAN:
return cell.BooleanCellValue;
case CellType.Numeric: //NUMERIC:
return cell.NumericCellValue;
case CellType.String: //STRING:
return cell.StringCellValue;
case CellType.Error: //ERROR:
return cell.ErrorCellValue;
case CellType.Formula: //FORMULA:
default:
return "=" + cell.CellFormula;
}
}

/// &lt;summary&gt; /// excel文件流转化成datatable /// &lt;/summary&gt; /// &lt;param name="fileStream"&gt;文件流&lt;/param&gt; /// &lt;param name="haveNote"&gt;是否包含备注&lt;/param&gt; /// &lt;returns&gt;&lt;/returns&gt; ... public DataTable TXTTo DataTable (string fileName, string columnName) DataTable dt = new DataTable (); FileStream fs = new FileStream (fileName, System.IO.FileMode.Open, System.IO.FileAccess.Read); StreamReader sr = new StreamReader sheetIndex 表索引号，如第一个表为0 headerRowIndex 标题行索引号，如第一行为0 public static DataTable RenderFrom Excel (Stream excel FileStream , int sheetIndex) return ... using System.Data.OleDb; string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=path/to/ excel /file.xlsx;Extended Properties=' Excel 12.0;HDR=YES;'"; string query = "SELECT * FROM [Sheet1$]"; OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, connectionString); DataSet dataSet = new DataSet(); dataAdapter.Fill(dataSet); DataTable dataTable = dataSet.Tables[0]; 其中，`path/to/ excel /file.xlsx`是 Excel 文件 的路径，`Sheet1`是要读取的工作表名称。