.NET Core6.0使用NPOI导入导出Excel

一、使用NPOI导出Excel
//引入NPOI包

• HTML

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

• JS

    //导出Excelfunction 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")]//导出文件public async Task<IActionResult> ExportFile(){//获取数据库数据var result = await _AnsweringQuestion.GetTeacherName();string filePath = "";//获取文件路径和名称var wwwroot = _hostingEnvironment.WebRootPath;var filename = "Table.xlsx";filePath = Path.Combine(wwwroot, filename);//创建一个工作簿和工作表NPOI.XSSF.UserModel.XSSFWorkbook book = new NPOI.XSSF.UserModel.XSSFWorkbook();var sheet = book.CreateSheet();//创建表头行var headerRow = sheet.CreateRow(0);headerRow.CreateCell(0).SetCellValue("姓名");headerRow.CreateCell(1).SetCellValue("科目");headerRow.CreateCell(2).SetCellValue("说明");//创建数据行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 文件写入磁盘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导入Excel

• HTML

 <input type="button" class="layui-btn layui-btn-blue2 layui-btn-sm" id="Excel" value="导入" />

• JS

    <script>/*从本地添加excel文件方法*/layui.use('upload', function () {var $ = layui.jquery, upload = layui.upload, form = layui.form;upload.render({elem: '#Excel'//附件上传按钮ID, url: '/Home/ImportFile'//附件上传后台地址, multiple: true, accept: 'file', exts: 'xls|xlsx'//(允许的类别), before: function (obj) {/*上传前执行的部分*/ }, done: function excel(res) {console.log(res);}, allDone: function (res) {console.log(res);}});});//上传事件结束
</script>

• C#
• 控制器代码

        //注入依赖private readonly Microsoft.AspNetCore.Hosting.IHostingEnvironment _hostingEnvironment;public HomeController(Microsoft.AspNetCore.Hosting.IHostingEnvironment hostingEnvironment){_hostingEnvironment = hostingEnvironment;}//控制器/// <summary>/// 导入/// </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);//取到数据后，接下来写你的业务逻辑就可以了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 = "导入成功", 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导入/// <summary>/// Excel导入/// </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列名对应的实体属性/// </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 转换为long/// <summary>/// 将object转换为long，若转换失败，则返回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转换为long，若转换失败，则返回指定值。不抛出异常。  /// </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 转换为int/// <summary>/// 将object转换为int，若转换失败，则返回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转换为int，若转换失败，则返回指定值。不抛出异常。 /// null返回默认值/// </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 转换为short/// <summary>/// 将object转换为short，若转换失败，则返回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转换为short，若转换失败，则返回指定值。不抛出异常。  /// </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 转换为demical/// <summary>/// 将object转换为demical，若转换失败，则返回指定值。不抛出异常。  /// </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转换为demical，若转换失败，则返回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 转化为bool/// <summary>/// 将object转换为bool，若转换失败，则返回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转换为bool，若转换失败，则返回指定值。不抛出异常。  /// </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 转换为float/// <summary>/// 将object转换为float，若转换失败，则返回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转换为float，若转换失败，则返回指定值。不抛出异常。  /// </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 转换为Guid/// <summary>/// 将string转换为Guid，若转换失败，则返回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 转换为DateTime/// <summary>/// 将string转换为DateTime，若转换失败，则返回日期最小值。不抛出异常。  /// </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转换为DateTime，若转换失败，则返回默认值。  /// </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 转换为string/// <summary>/// 将object转换为string，若转换失败，则返回""。不抛出异常。  /// </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 转换为double/// <summary>/// 将object转换为double，若转换失败，则返回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转换为double，若转换失败，则返回指定值。不抛出异常。  /// </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}

• 添加实体类UserEntity,要跟Excel的列名一致

 public class UserEntity{[Description("名称")]public string Name { get; set; }[Description("年龄")]public string Age { get; set; }[Description("性别")]public string Gender { get; set; }[Description("手机号")]public string Tel { get; set; }}

• Excel模板
  
• 实现效果