1. Use NPOI to export Excel
//Introduce NPOI package
- HTML
<input type="button" class="layui-btn layui-btn-blue2 layui-btn-sm" id="ExportExcel" onclick="ExportExcel()" value=\ "Export" />
- js
//Export 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")]
//export file
public async Task<IActionResult> ExportFile()
{<!-- -->
//Get database data
var result = await _AnsweringQuestion.GetTeacherName();
string filePath = "";
//Get file path and name
var wwwroot = _hostingEnvironment.WebRootPath;
var filename = "Table.xlsx";
filePath = Path.Combine(wwwroot, filename);
//Create a workbook and worksheet
NPOI.XSSF.UserModel.XSSFWorkbook book = new NPOI.XSSF.UserModel.XSSFWorkbook();
var sheet = book.CreateSheet();
// create header row
var headerRow = sheet. CreateRow(0);
headerRow.CreateCell(0).SetCellValue("Name");
headerRow.CreateCell(1).SetCellValue("account");
headerRow.CreateCell(2).SetCellValue("Description");
//create data row
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);
}
//Write the Excel file to disk
using (var f = System.IO.File.OpenWrite(filePath))
{<!-- -->
book. Write(f);
}
//Return the Excel file to the client as a download
var bytes = System.IO.File.ReadAllBytes(filePath);
return File(bytes, "application/octet-stream", $"{<!-- -->System.DateTime.Now.ToString("yyyyMMdd")}.xlsx");
}
2. Use NPOI to import Excel
- HTML
<input type="button" class="layui-btn layui-btn-blue2 layui-btn-sm" id="Excel" value="Import" />
- js
<script>
/*Add excel file method from local*/
layui.use('upload', function () {<!-- -->
var $ = layui.jquery
, upload = layui.upload
, form = layui. form;
upload.render({<!-- -->
elem: '#Excel'//Attachment upload button ID
, url: '/Home/ImportFile'//Attachment upload background address
, multiple: true
, accept: 'file'
, exts: 'xls|xlsx'//(allowed categories)
, before: function (obj) {<!-- -->/*Part executed before uploading*/ }
, done: function excel(res) {<!-- -->
console. log(res);
}
, allDone: function (res) {<!-- -->
console. log(res);
}
});
});//End of upload event
</script>
- C#
- controller code
//Inject dependencies
private readonly Microsoft.AspNetCore.Hosting.IHostingEnvironment _hostingEnvironment;
public HomeController(Microsoft.AspNetCore.Hosting.IHostingEnvironment hostingEnvironment)
{<!-- -->
_hostingEnvironment = hostingEnvironment;
}
//Controller
/// <summary>
/// Import
/// </summary>
/// <param name="file"></param>
/// <returns></returns>
[RequestSizeLimit(524288000)] //File size limit
[HttpPost]
public async Task<IActionResult> ImportFile(IFormFile file)
{<!-- -->
//Save the file to the folder
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);
//After getting the data, just write your business logic.
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 = "Import successful", Code = 200});
}
- Add ExcelHelper class
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 import
/// <summary>
/// Excel import
/// </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("Unsupported file format");
}
}
IRow columnRow = sheet.GetRow(0); //The first line is the field name
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>
/// Find the entity attribute corresponding to the Excel column name
/// </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 Get the attribute collection in the class
/// <summary>
/// Get the attribute collection in the class
/// </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 & amp; & amp; columns.Length > 0)
{<!-- -->
//Return properties in columns order
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
}
}
- Add Extensions class
public static partial class Extensions
{<!-- -->
#region converted to long
/// <summary>
/// Convert object to long. If the conversion fails, return 0. No exception is thrown.
/// </summary>
/// <param name="str"></param>
/// <returns></returns>
public static long ParseToLong(this object obj)
{<!-- -->
try
{<!-- -->
return long.Parse(obj.ToString());
}
catch
{<!-- -->
return 0L;
}
}
/// <summary>
/// Convert object to long. If the conversion fails, return the specified value. No exception is thrown.
/// </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 converted to int
/// <summary>
/// Convert object to int. If the conversion fails, 0 is returned. No exception is thrown.
/// </summary>
/// <param name="str"></param>
/// <returns></returns>
public static int ParseToInt(this object str)
{<!-- -->
try
{<!-- -->
return Convert.ToInt32(str);
}
catch
{<!-- -->
return 0;
}
}
/// <summary>
/// Convert object to int. If the conversion fails, return the specified value. No exception is thrown.
/// null returns the default value
/// </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 converted to short
/// <summary>
/// Convert object to short. If the conversion fails, return 0. No exception is thrown.
/// </summary>
/// <param name="str"></param>
/// <returns></returns>
public static short ParseToShort(this object obj)
{<!-- -->
try
{<!-- -->
return short. Parse(obj. ToString());
}
catch
{<!-- -->
return 0;
}
}
/// <summary>
/// Convert object to short. If the conversion fails, return the specified value. No exception is thrown.
/// </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 Convert to democratic
/// <summary>
/// Convert object to democratic. If the conversion fails, return the specified value. No exception is thrown.
/// </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>
/// Convert object to demical, if the conversion fails, return 0. No exception is thrown.
/// </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 converted to bool
/// <summary>
/// Convert object to bool, if the conversion fails, return false. No exception is thrown.
/// </summary>
/// <param name="str"></param>
/// <returns></returns>
public static bool ParseToBool(this object str)
{<!-- -->
try
{<!-- -->
return bool. Parse(str. ToString());
}
catch
{<!-- -->
return false;
}
}
/// <summary>
/// Convert object to bool, if the conversion fails, return the specified value. No exception is thrown.
/// </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 converted to float
/// <summary>
/// Convert object to float, if the conversion fails, return 0. No exception is thrown.
/// </summary>
/// <param name="str"></param>
/// <returns></returns>
public static float ParseToFloat(this object str)
{<!-- -->
try
{<!-- -->
return float.Parse(str.ToString());
}
catch
{<!-- -->
return 0;
}
}
/// <summary>
/// Convert object to float, if the conversion fails, return the specified value. No exception is thrown.
/// </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 Convert to Guid
/// <summary>
/// Convert string to Guid, if the conversion fails, return Guid.Empty. No exception is thrown.
/// </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 Convert to DateTime
/// <summary>
/// Convert string to DateTime, if the conversion fails, return the minimum value of the date. No exception is thrown.
/// </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>
/// Convert string to DateTime. If the conversion fails, return the default value.
/// </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 converted to string
/// <summary>
/// Convert object to string. If the conversion fails, return "". No exception is thrown.
/// </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 converted to double
/// <summary>
/// Convert object to double, and return 0 if the conversion fails. No exception is thrown.
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public static double ParseToDouble(this object obj)
{<!-- -->
try
{<!-- -->
return double. Parse(obj. ToString());
}
catch
{<!-- -->
return 0;
}
}
/// <summary>
/// Convert object to double, if the conversion fails, return the specified value. No exception is thrown.
/// </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
}
- Add the entity class UserEntity, which must be consistent with the column name of Excel
public class UserEntity
{<!-- -->
[Description("Name")]
public string Name {<!-- --> get; set; }
[Description("Age")]
public string Age {<!-- --> get; set; }
[Description("Gender")]
public string Gender {<!-- --> get; set; }
[Description("Mobile phone number")]
public string Tel {<!-- --> get; set; }
}
- Excel template
- achieve effect
