一、使用NPOI導(dǎo)出Excel
//引入NPOI包

• HTML

<input type="button" class="layui-btn layui-btn-blue2 layui-btn-sm" id="ExportExcel" onclick="ExportExcel()" value="導(dǎo)出" />

• JS

    //導(dǎo)出Excel
    function ExportExcel() {
        window.location.href = "@Url.Action("ExportFile")";
    }

• C#

 private readonly Microsoft.AspNetCore.Hosting.IHostingEnvironment _hostingEnvironment;
 public HomeController(Microsoft.AspNetCore.Hosting.IHostingEnvironment hostingEnvironment)
        {
            _hostingEnvironment = hostingEnvironment;
        }


        [HttpGet("ExportFile")]
        //導(dǎo)出文件
        public async Task<IActionResult> ExportFile()
        {
            //獲取數(shù)據(jù)庫(kù)數(shù)據(jù)
            var result = await _AnsweringQuestion.GetTeacherName();
            string filePath = "";
            //獲取文件路徑和名稱
            var wwwroot = _hostingEnvironment.WebRootPath;
            var filename = "Table.xlsx";
            filePath = Path.Combine(wwwroot, filename);
            //創(chuàng)建一個(gè)工作簿和工作表
            NPOI.XSSF.UserModel.XSSFWorkbook book = new NPOI.XSSF.UserModel.XSSFWorkbook();
            var sheet = book.CreateSheet();
            //創(chuàng)建表頭行
            var headerRow = sheet.CreateRow(0);
            headerRow.CreateCell(0).SetCellValue("姓名");
            headerRow.CreateCell(1).SetCellValue("科目");
            headerRow.CreateCell(2).SetCellValue("說(shuō)明");
            //創(chuàng)建數(shù)據(jù)行
            var data = result.DataList;
            for (int i = 0; i < data.Count(); i++)
            {
                var dataRow = sheet.CreateRow(i + 1);
                dataRow.CreateCell(0).SetCellValue(data[i].TeacherName);
                dataRow.CreateCell(1).SetCellValue(data[i].TeachSubject); 
                dataRow.CreateCell(2).SetCellValue(data[i].TeacherDesc);
            }
            //將Execel 文件寫(xiě)入磁盤(pán)
            using (var f = System.IO.File.OpenWrite(filePath))
            {
                book.Write(f);
            }
            //將Excel 文件作為下載返回給客戶端
            var bytes = System.IO.File.ReadAllBytes(filePath);
            return File(bytes, "application/octet-stream", $"{System.DateTime.Now.ToString("yyyyMMdd")}.xlsx");
        }

二、使用NPOI導(dǎo)入Excel文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-662500.html

• HTML

 <input type="button" class="layui-btn layui-btn-blue2 layui-btn-sm" id="Excel" value="導(dǎo)入" />

• JS

    <script>
    /*從本地添加excel文件方法*/
    layui.use('upload', function () {
        var $ = layui.jquery
            , upload = layui.upload
            , form = layui.form;
        upload.render({
            elem: '#Excel'//附件上傳按鈕ID
            , url: '/Home/ImportFile'//附件上傳后臺(tái)地址
            , multiple: true
            , accept: 'file'
            , exts: 'xls|xlsx'//(允許的類別)
            , before: function (obj) {/*上傳前執(zhí)行的部分*/ }
            , done: function excel(res) {
                console.log(res);
            }
            , allDone: function (res) {
            console.log(res);
            }
        });
    });//上傳事件結(jié)束
</script>

• C#
• 控制器代碼

        //注入依賴
        private readonly Microsoft.AspNetCore.Hosting.IHostingEnvironment _hostingEnvironment;
        public HomeController(Microsoft.AspNetCore.Hosting.IHostingEnvironment hostingEnvironment)
        {
            _hostingEnvironment = hostingEnvironment;
        }
        //控制器
         /// <summary>
        /// 導(dǎo)入
        /// </summary>
        /// <param name="file"></param>
        /// <returns></returns>

        [RequestSizeLimit(524288000)]  //文件大小限制
        [HttpPost]
        public async Task<IActionResult> ImportFile(IFormFile file)
        {
            //把文件保存到文件夾下
            var path = "wwwroot/" + file.FileName;
            using (FileStream files = new FileStream(path, FileMode.OpenOrCreate))
            {
                file.CopyTo(files);
            }
            var wwwroot = _hostingEnvironment.WebRootPath;
            var fileSrc = wwwroot+"\\"+ file.FileName;
            List<UserEntity> list = new ExcelHelper<UserEntity>().ImportFromExcel(fileSrc);
            //取到數(shù)據(jù)后，接下來(lái)寫(xiě)你的業(yè)務(wù)邏輯就可以了
            for (int i = 0; i < list.Count; i++)
            {
                var Name = string.IsNullOrEmpty(list[i].Name.ToString())? "" : list[i].Name.ToString();
                var Age = string.IsNullOrEmpty(list[i].Age.ToString()) ? "" : list[i].Age.ToString();
                var Gender = string.IsNullOrEmpty(list[i].Gender.ToString()) ? "" : list[i].Gender.ToString();
                var Tel = string.IsNullOrEmpty(list[i].Tel.ToString()) ? "" : list[i].Tel.ToString();

            }

            return Ok(new { Msg = "導(dǎo)入成功", Code = 200});
        }

• 添加ExcelHelper類

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Collections.Concurrent;
using System.ComponentModel;
using System.Reflection;

namespace NetCore6Demo.Models
{
    public class ExcelHelper<T> where T : new()
    {
        #region Excel導(dǎo)入
        /// <summary>
        /// Excel導(dǎo)入
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public List<T> ImportFromExcel(string FilePath)
        {
            List<T> list = new List<T>();
            HSSFWorkbook hssfWorkbook = null;
            XSSFWorkbook xssWorkbook = null;
            ISheet sheet = null;
            using (FileStream file = new FileStream(FilePath, FileMode.Open, FileAccess.Read))
            {
                switch (Path.GetExtension(FilePath))
                {
                    case ".xls":
                        hssfWorkbook = new HSSFWorkbook(file);
                        sheet = hssfWorkbook.GetSheetAt(0);
                        break;

                    case ".xlsx":
                        xssWorkbook = new XSSFWorkbook(file);
                        sheet = xssWorkbook.GetSheetAt(0);
                        break;

                    default:
                        throw new Exception("不支持的文件格式");
                }
            }
            IRow columnRow = sheet.GetRow(0); //第1行為字段名
            Dictionary<int, PropertyInfo> mapPropertyInfoDict = new Dictionary<int, PropertyInfo>();
            for (int j = 0; j < columnRow.LastCellNum; j++)
            {
                ICell cell = columnRow.GetCell(j);
                PropertyInfo propertyInfo = MapPropertyInfo(cell.ParseToString());
                if (propertyInfo != null)
                {
                    mapPropertyInfoDict.Add(j, propertyInfo);
                }
            }

            for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                T entity = new T();
                for (int j = row.FirstCellNum; j < columnRow.LastCellNum; j++)
                {
                    if (mapPropertyInfoDict.ContainsKey(j))
                    {
                        if (row.GetCell(j) != null)
                        {
                            PropertyInfo propertyInfo = mapPropertyInfoDict[j];
                            switch (propertyInfo.PropertyType.ToString())
                            {
                                case "System.DateTime":
                                case "System.Nullable`1[System.DateTime]":
                                    mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToDateTime());
                                    break;

                                case "System.Boolean":
                                case "System.Nullable`1[System.Boolean]":
                                    mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToBool());
                                    break;

                                case "System.Byte":
                                case "System.Nullable`1[System.Byte]":
                                    mapPropertyInfoDict[j].SetValue(entity, Byte.Parse(row.GetCell(j).ParseToString()));
                                    break;
                                case "System.Int16":
                                case "System.Nullable`1[System.Int16]":
                                    mapPropertyInfoDict[j].SetValue(entity, Int16.Parse(row.GetCell(j).ParseToString()));
                                    break;
                                case "System.Int32":
                                case "System.Nullable`1[System.Int32]":
                                    mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToInt());
                                    break;

                                case "System.Int64":
                                case "System.Nullable`1[System.Int64]":
                                    mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToLong());
                                    break;

                                case "System.Double":
                                case "System.Nullable`1[System.Double]":
                                    mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToDouble());
                                    break;

                                case "System.Single":
                                case "System.Nullable`1[System.Single]":
                                    mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToDouble());
                                    break;

                                case "System.Decimal":
                                case "System.Nullable`1[System.Decimal]":
                                    mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToDecimal());
                                    break;

                                default:
                                case "System.String":
                                    mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString());
                                    break;
                            }
                        }
                    }
                }
                list.Add(entity);
            }
            hssfWorkbook?.Close();
            xssWorkbook?.Close();
            return list;
        }



        /// <summary>
        /// 查找Excel列名對(duì)應(yīng)的實(shí)體屬性
        /// </summary>
        /// <param name="columnName"></param>
        /// <returns></returns>
        private PropertyInfo MapPropertyInfo(string columnName)
        {
            PropertyInfo[] propertyList = GetProperties(typeof(T));
            PropertyInfo propertyInfo = propertyList.Where(p => p.Name == columnName).FirstOrDefault();
            if (propertyInfo != null)
            {
                return propertyInfo;
            }
            else
            {
                foreach (PropertyInfo tempPropertyInfo in propertyList)
                {
                    DescriptionAttribute[] attributes = (DescriptionAttribute[])tempPropertyInfo.GetCustomAttributes(typeof(DescriptionAttribute), false);
                    if (attributes.Length > 0)
                    {
                        if (attributes[0].Description == columnName)
                        {
                            return tempPropertyInfo;
                        }
                    }
                }
            }
            return null;
        }

        private static ConcurrentDictionary<string, object> dictCache = new ConcurrentDictionary<string, object>();
        #region 得到類里面的屬性集合
        /// <summary>
        /// 得到類里面的屬性集合
        /// </summary>
        /// <param name="type"></param>
        /// <param name="columns"></param>
        /// <returns></returns>
        public static PropertyInfo[] GetProperties(Type type, string[] columns = null)
        {
            PropertyInfo[] properties = null;
            if (dictCache.ContainsKey(type.FullName))
            {
                properties = dictCache[type.FullName] as PropertyInfo[];
            }
            else
            {
                properties = type.GetProperties();
                dictCache.TryAdd(type.FullName, properties);
            }

            if (columns != null && columns.Length > 0)
            {
                //  按columns順序返回屬性
                var columnPropertyList = new List<PropertyInfo>();
                foreach (var column in columns)
                {
                    var columnProperty = properties.Where(p => p.Name == column).FirstOrDefault();
                    if (columnProperty != null)
                    {
                        columnPropertyList.Add(columnProperty);
                    }
                }
                return columnPropertyList.ToArray();
            }
            else
            {
                return properties;
            }
        }
        #endregion


        #endregion
    }
}

• 添加Extensions類

 public static partial class Extensions
    {
        #region 轉(zhuǎn)換為long
        /// <summary>
        /// 將object轉(zhuǎn)換為long，若轉(zhuǎn)換失敗，則返回0。不拋出異常。  
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public static long ParseToLong(this object obj)
        {
            try
            {
                return long.Parse(obj.ToString());
            }
            catch
            {
                return 0L;
            }
        }

        /// <summary>
        /// 將object轉(zhuǎn)換為long，若轉(zhuǎn)換失敗，則返回指定值。不拋出異常。  
        /// </summary>
        /// <param name="str"></param>
        /// <param name="defaultValue"></param>
        /// <returns></returns>
        public static long ParseToLong(this string str, long defaultValue)
        {
            try
            {
                return long.Parse(str);
            }
            catch
            {
                return defaultValue;
            }
        }
        #endregion

        #region 轉(zhuǎn)換為int
        /// <summary>
        /// 將object轉(zhuǎn)換為int，若轉(zhuǎn)換失敗，則返回0。不拋出異常。  
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public static int ParseToInt(this object str)
        {
            try
            {
                return Convert.ToInt32(str);
            }
            catch
            {
                return 0;
            }
        }

        /// <summary>
        /// 將object轉(zhuǎn)換為int，若轉(zhuǎn)換失敗，則返回指定值。不拋出異常。 
        /// null返回默認(rèn)值
        /// </summary>
        /// <param name="str"></param>
        /// <param name="defaultValue"></param>
        /// <returns></returns>
        public static int ParseToInt(this object str, int defaultValue)
        {
            if (str == null)
            {
                return defaultValue;
            }
            try
            {
                return Convert.ToInt32(str);
            }
            catch
            {
                return defaultValue;
            }
        }
        #endregion

        #region 轉(zhuǎn)換為short
        /// <summary>
        /// 將object轉(zhuǎn)換為short，若轉(zhuǎn)換失敗，則返回0。不拋出異常。  
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public static short ParseToShort(this object obj)
        {
            try
            {
                return short.Parse(obj.ToString());
            }
            catch
            {
                return 0;
            }
        }

        /// <summary>
        /// 將object轉(zhuǎn)換為short，若轉(zhuǎn)換失敗，則返回指定值。不拋出異常。  
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public static short ParseToShort(this object str, short defaultValue)
        {
            try
            {
                return short.Parse(str.ToString());
            }
            catch
            {
                return defaultValue;
            }
        }
        #endregion

        #region 轉(zhuǎn)換為demical
        /// <summary>
        /// 將object轉(zhuǎn)換為demical，若轉(zhuǎn)換失敗，則返回指定值。不拋出異常。  
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public static decimal ParseToDecimal(this object str, decimal defaultValue)
        {
            try
            {
                return decimal.Parse(str.ToString());
            }
            catch
            {
                return defaultValue;
            }
        }

        /// <summary>
        /// 將object轉(zhuǎn)換為demical，若轉(zhuǎn)換失敗，則返回0。不拋出異常。  
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public static decimal ParseToDecimal(this object str)
        {
            try
            {
                return decimal.Parse(str.ToString());
            }
            catch
            {
                return 0;
            }
        }
        #endregion

        #region 轉(zhuǎn)化為bool
        /// <summary>
        /// 將object轉(zhuǎn)換為bool，若轉(zhuǎn)換失敗，則返回false。不拋出異常。  
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public static bool ParseToBool(this object str)
        {
            try
            {
                return bool.Parse(str.ToString());
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 將object轉(zhuǎn)換為bool，若轉(zhuǎn)換失敗，則返回指定值。不拋出異常。  
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public static bool ParseToBool(this object str, bool result)
        {
            try
            {
                return bool.Parse(str.ToString());
            }
            catch
            {
                return result;
            }
        }
        #endregion

        #region 轉(zhuǎn)換為float
        /// <summary>
        /// 將object轉(zhuǎn)換為float，若轉(zhuǎn)換失敗，則返回0。不拋出異常。  
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public static float ParseToFloat(this object str)
        {
            try
            {
                return float.Parse(str.ToString());
            }
            catch
            {
                return 0;
            }
        }

        /// <summary>
        /// 將object轉(zhuǎn)換為float，若轉(zhuǎn)換失敗，則返回指定值。不拋出異常。  
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public static float ParseToFloat(this object str, float result)
        {
            try
            {
                return float.Parse(str.ToString());
            }
            catch
            {
                return result;
            }
        }
        #endregion

        #region 轉(zhuǎn)換為Guid
        /// <summary>
        /// 將string轉(zhuǎn)換為Guid，若轉(zhuǎn)換失敗，則返回Guid.Empty。不拋出異常。  
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public static Guid ParseToGuid(this string str)
        {
            try
            {
                return new Guid(str);
            }
            catch
            {
                return Guid.Empty;
            }
        }
        #endregion

        #region 轉(zhuǎn)換為DateTime
        /// <summary>
        /// 將string轉(zhuǎn)換為DateTime，若轉(zhuǎn)換失敗，則返回日期最小值。不拋出異常。  
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public static DateTime ParseToDateTime(this string str)
        {
            try
            {
                if (string.IsNullOrWhiteSpace(str))
                {
                    return DateTime.MinValue;
                }
                if (str.Contains("-") || str.Contains("/"))
                {
                    return DateTime.Parse(str);
                }
                else
                {
                    int length = str.Length;
                    switch (length)
                    {
                        case 4:
                            return DateTime.ParseExact(str, "yyyy", System.Globalization.CultureInfo.CurrentCulture);
                        case 6:
                            return DateTime.ParseExact(str, "yyyyMM", System.Globalization.CultureInfo.CurrentCulture);
                        case 8:
                            return DateTime.ParseExact(str, "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture);
                        case 10:
                            return DateTime.ParseExact(str, "yyyyMMddHH", System.Globalization.CultureInfo.CurrentCulture);
                        case 12:
                            return DateTime.ParseExact(str, "yyyyMMddHHmm", System.Globalization.CultureInfo.CurrentCulture);
                        case 14:
                            return DateTime.ParseExact(str, "yyyyMMddHHmmss", System.Globalization.CultureInfo.CurrentCulture);
                        default:
                            return DateTime.ParseExact(str, "yyyyMMddHHmmss", System.Globalization.CultureInfo.CurrentCulture);
                    }
                }
            }
            catch
            {
                return DateTime.MinValue;
            }
        }

        /// <summary>
        /// 將string轉(zhuǎn)換為DateTime，若轉(zhuǎn)換失敗，則返回默認(rèn)值。  
        /// </summary>
        /// <param name="str"></param>
        /// <param name="defaultValue"></param>
        /// <returns></returns>
        public static DateTime ParseToDateTime(this string str, DateTime? defaultValue)
        {
            try
            {
                if (string.IsNullOrWhiteSpace(str))
                {
                    return defaultValue.GetValueOrDefault();
                }
                if (str.Contains("-") || str.Contains("/"))
                {
                    return DateTime.Parse(str);
                }
                else
                {
                    int length = str.Length;
                    switch (length)
                    {
                        case 4:
                            return DateTime.ParseExact(str, "yyyy", System.Globalization.CultureInfo.CurrentCulture);
                        case 6:
                            return DateTime.ParseExact(str, "yyyyMM", System.Globalization.CultureInfo.CurrentCulture);
                        case 8:
                            return DateTime.ParseExact(str, "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture);
                        case 10:
                            return DateTime.ParseExact(str, "yyyyMMddHH", System.Globalization.CultureInfo.CurrentCulture);
                        case 12:
                            return DateTime.ParseExact(str, "yyyyMMddHHmm", System.Globalization.CultureInfo.CurrentCulture);
                        case 14:
                            return DateTime.ParseExact(str, "yyyyMMddHHmmss", System.Globalization.CultureInfo.CurrentCulture);
                        default:
                            return DateTime.ParseExact(str, "yyyyMMddHHmmss", System.Globalization.CultureInfo.CurrentCulture);
                    }
                }
            }
            catch
            {
                return defaultValue.GetValueOrDefault();
            }
        }
        #endregion

        #region 轉(zhuǎn)換為string
        /// <summary>
        /// 將object轉(zhuǎn)換為string，若轉(zhuǎn)換失敗，則返回""。不拋出異常。  
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public static string ParseToString(this object obj)
        {
            try
            {
                if (obj == null)
                {
                    return string.Empty;
                }
                else
                {
                    return obj.ToString();
                }
            }
            catch
            {
                return string.Empty;
            }
        }
        public static string ParseToStrings<T>(this object obj)
        {
            try
            {
                var list = obj as IEnumerable<T>;
                if (list != null)
                {
                    return string.Join(",", list);
                }
                else
                {
                    return obj.ToString();
                }
            }
            catch
            {
                return string.Empty;
            }

        }
        #endregion

        #region 轉(zhuǎn)換為double
        /// <summary>
        /// 將object轉(zhuǎn)換為double，若轉(zhuǎn)換失敗，則返回0。不拋出異常。  
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static double ParseToDouble(this object obj)
        {
            try
            {
                return double.Parse(obj.ToString());
            }
            catch
            {
                return 0;
            }
        }

        /// <summary>
        /// 將object轉(zhuǎn)換為double，若轉(zhuǎn)換失敗，則返回指定值。不拋出異常。  
        /// </summary>
        /// <param name="str"></param>
        /// <param name="defaultValue"></param>
        /// <returns></returns>
        public static double ParseToDouble(this object str, double defaultValue)
        {
            try
            {
                return double.Parse(str.ToString());
            }
            catch
            {
                return defaultValue;
            }
        }
        #endregion
    }

• 添加實(shí)體類UserEntity,要跟Excel的列名一致

 public class UserEntity
    {
        [Description("名稱")]
        public string Name { get; set; }
        [Description("年齡")]
        public string Age { get; set; }
        [Description("性別")]
        public string Gender { get; set; }
        [Description("手機(jī)號(hào)")]
        public string Tel { get; set; }
    }

• Excel模板
  
• 實(shí)現(xiàn)效果
  

到了這里，關(guān)于.NET Core6.0使用NPOI導(dǎo)入導(dǎo)出Excel的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！