利用了c#的第三方插件Nopi
/// <summary>
/// 传入一个文件的地址,返回一个datatable的表
/// </summary>
/// <param name="filepath"></param>
/// <returns>databel</returns>
public DataTable XlsToDatatable(string filepath)
{
DataTable dt = new DataTable();
using (var file = new FileStream(filepath, FileMode.Open, FileAccess.Read))
{
var hssfworkbook = new HSSFWorkbook(file);
var sheet = hssfworkbook.GetSheetAt(0);
//var row = (HSSFRow)rows.Current;
//sheet.TopRow.
var rows = sheet.GetRowEnumerator();
HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
//SET EVERY COLUMN NAME
HSSFCell cell = (HSSFCell)headerRow.GetCell(j);
var keys = dic.Where(q => q.Value == cell.ToString()).Select(q => q.Key);
List<string> keyList = (from q in dic
where q.Value == cell.ToString()
select q.Key).ToList<string>(); //get all keys
if (keyList.Count!=0)
{
dt.Columns.Add(keyList[0]);
}
else
{
dt.Columns.Add(cell.ToString());
}
}
while (rows.MoveNext())
{
IRow row = (HSSFRow)rows.Current;
DataRow dr = dt.NewRow();
if (row.RowNum == 0) continue;//The firt row is title,no need import
for (int i = 0; i < row.LastCellNum; i++)
{
if (i >= dt.Columns.Count)//cell count>column count,then break //每条记录的单元格数量不能大于表格栏位数量 20140213
{
break;
}
ICell cell = row.GetCell(i);
if ((i == 0) && (string.IsNullOrEmpty(cell.ToString()) == true))//每行第一个cell为空,break
{
break;
}
if (cell == null)
{
dr[i] = null;
}
else
{
//dr[i] = cell.ToString();
if (cell.CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(cell))
{
dr[i] = cell.DateCellValue.ToString("yyyy-MM-dd hh:mm:ss"); // //如果内容是时间格式转成这种格式
}
else
{
dr[i] = row.GetCell(i).ToString();
}
}
}
dt.Rows.Add(dr);
}
return dt;
}
}