.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();
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;
}
}
}
///////////////如何将datatable转list<>对象
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
namespace DAL
{
public class CovertListHelper
{
public List<T> convertToList<T>(DataTable dt) where T : new()
{
//定义集合
List<T> ts = new List<T>();
//获得此模型的类型
Type type = typeof(T);
//定义一个临时的变量
string tempName = "";
//遍历datatable中所有数据行
foreach (DataRow dr in dt.Rows)
{
T t = new T();
//获得此模型的公共属性
PropertyInfo[] propertys = t.GetType().GetProperties();
//遍历所有属性
foreach (PropertyInfo pi in propertys)
{
//将此属性赋值给临时变量
tempName = pi.Name;
//检查datatable是否包含此列
if (dt.Columns.Contains(tempName))
{
//判断此属性是否有setter，这个啥意思呢，就是我们的实体层的{get;set;}如果我们的实体有了set方法，就说明可以赋值！
if (!pi.CanWrite) continue;
{
//取值
object value = dr[tempName];
if (value != DBNull.Value)
pi.SetValue(t, value, null);
}
}
}
//对象添加到泛型集合中
ts.Add(t);
}
return ts;
}
}
}
////该方法的用法
//实例化这个类
DAL.CovertListHelper tolist = new CovertListHelper();
//把DataTable转换为List
//要转换成的List类型为：UserEntity
//需要转换的DataTable为：table
List<UserEntity> list = tolist.convertToList<UserEntity>(table);
