.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
//导出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")]
//导出文件
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模板
- 实现效果
