.net core中使用NPOI导入excel

前端我这里是用layui 

<button type="button" class="layui-btn" id="test3"><i class="layui-icon"></i>上传文件</button>

js代码

//指定允许上传的文件类型
upload.render({
elem: ‘#test3‘
, url: ‘/Area/Project/ExporExcel‘ //改成您自己的上传接口
, accept: ‘file‘ //普通文件
, done: function (res) {
for (var i = 0; i < res.data.length; i++) {

}

layer.msg(‘上传成功‘);
console.log(res);
}
});

///请求的接口

public ActionResult ExporExcel(IFormFile file)
{
var msg = "未知错误";
List<CoreEntity> CoreList = new List<CoreEntity>();
if (file.Length > 0) {
DataTable dt = new DataTable();
string strMsg;
//利用IFormFile里面的OpenReadStream()方法直接读取文件流
dt = ExcelHelper.ExcelToDatatable(file.OpenReadStream(),Path.GetExtension(file.FileName),out strMsg);
if (dt.Rows.Count>0) {
for (int i = 0; i < dt.Rows.Count; i++) {
CoreEntity resultData = new CoreEntity();
resultData.CoreName = dt.Rows[i]["中心名"].ToString();
resultData.CoreNum = dt.Rows[i]["中心号"].ToString();
resultData.CoreUser = dt.Rows[i]["中心对接人"].ToString();
resultData.RecruitmentAgent = dt.Rows[i]["负责人"].ToString();
resultData.CoreProvince = dt.Rows[i]["省"].ToString();
resultData.CoreCity = dt.Rows[i]["市"].ToString();
CoreList.Add(resultData);
}
return Json(new { code = 0,msg = "导入成功",data= CoreList });
}
if (dt.Rows.Count > 0) {
} else {
//resultData.Code = -1;
//resultData.Msg = "Excel导入表无数据!";
}
}
return Json(new { code = 0,msg = msg });

}

///下面是封装好的类需要在Nuget中安装 NPOI这个  直接搬砖过去就可以用了 

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;

namespace CRM.Common
{
public static class ExcelHelper
{
/// <summary>
/// 将Excel单表转为Datatable
/// </summary>
/// <param name="stream"></param>
/// <param name="fileType"></param>
/// <param name="strMsg"></param>
/// <param name="sheetName"></param>
/// <returns></returns>
public static DataTable ExcelToDatatable(Stream stream, string fileType, out string strMsg, string sheetName = null)
{
strMsg = "";
DataTable dt = new DataTable();
ISheet sheet = null;
IWorkbook workbook = null;
try
{
#region 判断excel版本
//2007以上版本excel
if (fileType == ".xlsx")
{
workbook = new XSSFWorkbook(stream);
}
//2007以下版本excel
else if (fileType == ".xls")
{
workbook = new HSSFWorkbook(stream);
}
else
{
throw new Exception("传入的不是Excel文件！");
}
#endregion
if (!string.IsNullOrEmpty(sheetName))
{
sheet = workbook.GetSheet(sheetName);
if (sheet == null)
{
sheet = workbook.GetSheetAt(0);
}
}
else
{
sheet = workbook.GetSheetAt(0);
}
if (sheet != null)
{
IRow firstRow = sheet.GetRow(0);
int cellCount = firstRow.LastCellNum;
for (int i = firstRow.FirstCellNum; i < cellCount; i++)
{
ICell cell = firstRow.GetCell(i);
if (cell != null)
{
string cellValue = cell.StringCellValue.Trim();
if (!string.IsNullOrEmpty(cellValue))
{
DataColumn dataColumn = new DataColumn(cellValue);
dt.Columns.Add(dataColumn);
}
}
}
DataRow dataRow = null;
//遍历行
for (int j = sheet.FirstRowNum + 1; j <= sheet.LastRowNum; j++)
{
IRow row = sheet.GetRow(j);
dataRow = dt.NewRow();
if (row == null || row.FirstCellNum < 0)
{
continue;
}
//遍历列
for (int i = row.FirstCellNum; i < cellCount; i++)
{
ICell cellData = row.GetCell(i);
if (cellData != null)
{
//判断是否为数字型，必须加这个判断不然下面的日期判断会异常
if (cellData.CellType == CellType.Numeric)
{
//判断是否日期类型
if (DateUtil.IsCellDateFormatted(cellData))
{
dataRow[i] = cellData.DateCellValue;
}
else
{
dataRow[i] = cellData.ToString().Trim();
}
}
else
{
dataRow[i] = cellData.ToString().Trim();
}
}
}
dt.Rows.Add(dataRow);
}
}
else
{
throw new Exception("没有获取到Excel中的数据表！");
}
}
catch (Exception ex)
{
strMsg = ex.Message;
}
return dt;
}
}
}

.net core中使用NPOI导入excel

原文：https://www.cnblogs.com/yjm8023/p/13817412.html