Excel文件数据导入和导出功能实现

/// <summary>
/// 导入
/// </summary>
/// <returns></returns>
[HttpPost]
public GZAPIResult Import(List<data_WeiFaList_XianChang> datas)
{
	//var  entities.engsys_assetsType.AsNoTracking()
	using (var trans = entities.Database.BeginTransaction())
	{
		try
		{
			var dataDBCache = entities.data_WeiFaList_XianChang.AsNoTracking().ToList();
			List<data_WeiFaList_XianChang> dataNew = new List<data_WeiFaList_XianChang>();

			var ids = datas.Select(s => s.ChuFaDanHao).ToList();
			entities.data_WeiFaList_XianChang.Where(w => ids.Contains(w.ChuFaDanHao)).BatchDelete();
			entities.SaveChanges();

			//entities.BulkInsert(datas);
			entities.data_WeiFaList_XianChang.AddRange(datas);
			entities.SaveChanges();
			trans.Commit();
		}
		catch (Exception ex)
		{
			Logs.Intance.Error(ex, "现场违法，导入失败");
			trans.Rollback();
			return GZAPIException(ex);
		}
	}
	return GZAPISuccess();

}

/// <summary>
/// 导出
/// </summary>
/// <returns></returns>
[HttpGet]
public IActionResult Export()
{
	var data = entities.data_WeiFaList_XianChang.AsNoTracking().Select(s => new
	{
		s.ChuFaDanHao,
		s.WeiFaShiJian,
		s.ChePaiHaoMa,
		s.HaoPaiZhongLei,
		s.WeiFaDiZhi,
		s.WeiFaXingWei,
		s.WeiFaDaiMa,
		s.DangShiRen,
		s.JiaShiZhengHao,
		s.ZhiQinMinJingJingHao,
		s.ChuLiJiGuan,
		s.ChuLiDaDui
	}).ToList();

	DataTable dt = new DataTable();
	dt.Columns.Add("ChuFaDanHao", typeof(System.String)).Caption = "处罚单号";
	dt.Columns.Add("WeiFaShiJian", typeof(System.DateTime)).Caption = "违法时间";
	dt.Columns.Add("ChePaiHaoMa", typeof(System.String)).Caption = "车牌号码";
	dt.Columns.Add("HaoPaiZhongLei", typeof(System.String)).Caption = "号牌种类";
	dt.Columns.Add("WeiFaDiZhi", typeof(System.String)).Caption = "违法地址";
	dt.Columns.Add("WeiFaXingWei", typeof(System.String)).Caption = "违法行为";
	dt.Columns.Add("WeiFaDaiMa", typeof(System.String)).Caption = "违法代码";
	dt.Columns.Add("DangShiRen", typeof(System.String)).Caption = "当事人";
	dt.Columns.Add("JiaShiZhengHao", typeof(System.String)).Caption = "驾驶证号";
	dt.Columns.Add("ZhiQinMinJingJingHao", typeof(System.String)).Caption = "执勤民警警号";
	dt.Columns.Add("ChuLiJiGuan", typeof(System.String)).Caption = "处理机关支队名称";
	dt.Columns.Add("ChuLiDaDui", typeof(System.String)).Caption = "处理机关大队名称";
	foreach (var v in data)
	{
		dt.Rows.Add(v.ChuFaDanHao, v.WeiFaShiJian, v.ChePaiHaoMa, v.HaoPaiZhongLei, v.WeiFaDiZhi, v.WeiFaXingWei,
			v.WeiFaDaiMa, v.DangShiRen, v.JiaShiZhengHao, v.ZhiQinMinJingJingHao, v.ChuLiJiGuan, v.ChuLiDaDui);
	}
	dt.AcceptChanges();

	var bytes = NOPIHelper.saveExcel(dt, true, NOPIHelper.WorkbookType.xlsx);

	MemoryStream ms = new MemoryStream(bytes);
	ms.Position = 0;
	//Access-Control-Expose-Headers: Content-Disposition
	//var result= File(bytes, "application/octet-stream", $"Devices_{batchID}.xlsx");
	Response.Headers.Add("Access-Control-Expose-Headers", "Content-Disposition");

	// 返回中文文件名，需要使用Uri.EscapeUriString 编码一下，要不然文件名中中文部分会异常
	return File(ms, "application/octet-stream", Uri.EscapeUriString("高速现场违法信息.xlsx"));
}

/// <summary>
/// 导出
/// </summary>
/// <returns></returns>
[HttpPost]
public IActionResult Export(P_SearchList searchData)
{
	var queryEntity = entitiesQuery.jiazhao_StudentInfo;

	if (searchData.Date_Range != null && searchData.Date_Range.Length == 2)
	{
		var JoinDate_Begin = searchData.Date_Range[0];
		var JoinDate_End = searchData.Date_Range[1];
		queryEntity = queryEntity.Where(w => w.JoinDate >= JoinDate_Begin && w.JoinDate <= JoinDate_End);
	}

	if (!String.IsNullOrEmpty(searchData.UserName)) queryEntity = queryEntity.Where(w => w.UserName == searchData.UserName);
	if (!String.IsNullOrEmpty(searchData.Principal)) queryEntity = queryEntity.Where(w => w.Principal == searchData.Principal);
	if (!String.IsNullOrEmpty(searchData.CarType)) queryEntity = queryEntity.Where(w => w.CarType == searchData.CarType);
	if (!String.IsNullOrEmpty(searchData.Status)) queryEntity = queryEntity.Where(w => w.Status == searchData.Status);

	var query = QueryList(queryEntity);

	var data = query.ToList();

	var bytes = NOPIHelper.saveExcel(data, true, NOPIHelper.WorkbookType.xlsx);

	MemoryStream ms = new MemoryStream(bytes);
	ms.Position = 0;
	//Access-Control-Expose-Headers: Content-Disposition
	//var result= File(bytes, "application/octet-stream", $"Devices_{batchID}.xlsx");
	Response.Headers.Add("Access-Control-Expose-Headers", "Content-Disposition");

	// 返回中文文件名，需要使用Uri.EscapeUriString 编码一下，要不然文件名中中文部分会异常
	return File(ms, "application/octet-stream", Uri.EscapeUriString("学员档案.xlsx"));
}