.Net5导入导出execl表格
通过 using OfficeOpenXml 进行实现
using System; using System.Collections.Generic; using System.IO; using System.Reflection; using OfficeOpenXml; namespace Sino { /// <summary> /// 通用工具类 /// </summary> public static class ExcelHelper { /// <summary> /// 导出 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="path"></param> /// <param name="data"></param> /// <param name="headers"></param> /// <returns></returns> public static bool ExportListToExcel<T>(string path, List<T> data, Dictionary<string, string> headers = null) { FileInfo file = new FileInfo(path); if (file.Exists) { file.Delete(); file = new FileInfo(path); } using (var package = new ExcelPackage(file)) { var worksheet = package.Workbook.Worksheets.Add("sheet1"); worksheet.Cells.LoadFromCollection(data, true); if (headers != null) { for (int i = 0; i < worksheet.Dimension.End.Column; i++) { var name = worksheet.Cells[1, i + 1]?.Value?.ToString(); if (string.IsNullOrEmpty(name) == false && headers.ContainsKey(name)) { worksheet.Cells[1, i + 1].Value = headers[name]; } } } worksheet.DefaultColWidth = 20; package.Save(); } return true; } /// <summary> /// 导入 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="existingFile"></param> /// <param name="headers"></param> /// <returns></returns> public static List<T> LoadFromExcel<T>(FileInfo existingFile, Dictionary<string, string> headers = null) where T : new() { List<T> resultList = new List<T>(); Dictionary<string, int> dictHeader = new Dictionary<string, int>(); using (ExcelPackage package = new ExcelPackage(existingFile)) { ExcelWorksheet worksheet = package.Workbook.Worksheets[1]; int colStart = worksheet.Dimension.Start.Column; //工作区开始列 int colEnd = worksheet.Dimension.End.Column; //工作区结束列 int rowStart = worksheet.Dimension.Start.Row; //工作区开始行号 int rowEnd = worksheet.Dimension.End.Row; //工作区结束行号 //将每列标题添加到字典中 for (int i = colStart; i <= colEnd; i++) { dictHeader[worksheet.Cells[rowStart, i].Value.ToString()] = i; } List<PropertyInfo> propertyInfoList = new List<PropertyInfo>(typeof(T).GetProperties()); string headerName = ""; for (int row = rowStart + 1; row <= rowEnd; row++) { T result = new T(); //为对象T的各属性赋值 foreach (PropertyInfo p in propertyInfoList) { try { headerName = p.Name; //导入的时候如果替换过header属性则需要替换过来在复给类值 if (headers != null) { if (headers.ContainsKey(headerName)) headerName = headers[headerName]; } ExcelRange cell = worksheet.Cells[row, dictHeader[headerName]]; //与属性名对应的单元格 if (cell.Value == null) continue; switch (p.PropertyType.Name.ToLower()) { case "string": p.SetValue(result, cell.GetValue<String>()); break; case "int16": p.SetValue(result, cell.GetValue<Int16>()); break; case "int32": p.SetValue(result, cell.GetValue<Int32>()); break; case "int64": p.SetValue(result, cell.GetValue<Int64>()); break; case "decimal": p.SetValue(result, cell.GetValue<Decimal>()); break; case "double": p.SetValue(result, cell.GetValue<Double>()); break; case "datetime": p.SetValue(result, cell.GetValue<DateTime>()); break; case "boolean": p.SetValue(result, cell.GetValue<Boolean>()); break; case "byte": p.SetValue(result, cell.GetValue<Byte>()); break; case "char": p.SetValue(result, cell.GetValue<Char>()); break; case "single": p.SetValue(result, cell.GetValue<Single>()); break; default: break; } } catch (KeyNotFoundException ex) { } } resultList.Add(result); } } return resultList; } } }
调用导入
[HttpPost] [Route("import")] public async Task<IActionResult> Import(IFormFile excelfile) { string sFileName = $@"{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx"; try { string webRootPath = _hostingEnvironment.WebRootPath; string contentRootPath = _hostingEnvironment.ContentRootPath; var path = Path.Combine(webRootPath, "file", sFileName); if (!Directory.Exists(webRootPath + "\\file")) Directory.CreateDirectory(webRootPath + "\\file"); var fileName = excelfile.FileName; using (FileStream fileStream = System.IO.File.Create(path)) { excelfile.CopyTo(fileStream); fileStream.Flush(); } FileInfo f = new FileInfo(path); System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch(); watch.Start(); FileInfo fileExcel = new FileInfo(path); List<Import> data = Sino.ExcelHelper.LoadFromExcel<Import>(fileExcel , new Dictionary<string, string> { { "xxxx", "xxx" } ,{ "xxx", "xxx } ,{ "xxx", "xxxxxxx"} ,{ "xxx","x"} ,{ "xx","x"} ,{ "xx","x"} ,{ "xx","x"} ,{ "x","x"} ,{ "x","x"} ,{ "x","x"} ,{ "x","x"} //,{ "x","x"} //,{ "x","x"} //,{ "x","x"} }); //导入数据去重 data = data.Where((x, i) => data.FindIndex(n => n.Key == x.Key) == i).ToList(); int successCount = 0; foreach (var item in data) { if (!_Records.Exists(x => x.Key == item.Key)) { if (item?.Key.Length < 12) continue; //业务入库 successCount++; } } await _Records.Context.CommitAsync(); var result = new ResultDataObject<string>(); result.Flag = 1; result.Data = "file" + "/" + sFileName; result.Message = "成功导入【" + successCount + "】条数据！"; return Ok(result); } catch (Exception ee) { var result = new ResultDataObject<string>(); result.Flag = 0; result.Data = "file" + "/" + sFileName; result.Message = "失败！"; return Ok(result); } }
导出
/// <summary> /// 导出excel功能 /// </summary> /// <param name="inputDevice"></param> /// <returns></returns> [HttpPost] [Route("export")] public async Task<IActionResult> Export([FromBody] Input input) { var result = new ResultDataObject<string>(); if (inputDevice?.Ids == null || inputDevice?.Ids.Count() <= 0) { result.Flag = 0; return Ok(result); } Sino.Domain.Repositories.DbContext sno = new Domain.Repositories.Context(); string sql = $@"select * from Records where id in ('{string.Join("','", inputDevice.Ids)}')"; System.Data.DataTable dtScan = Sino.Domain.Repositories.EntityFramework.DbContextExtend.ExecuteBackDt(sno, sql, System.Data.CommandType.Text, null); List<ExportData> devices = new List<ExportData>(); foreach (System.Data.DataRow item in dtScan.Rows) { devices.Add(new ExportData() { //业务赋值 }); } sno.Dispose(); string webRootPath = _hostingEnvironment.WebRootPath; string sFileName = $@"{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx"; var path = Path.Combine(webRootPath, "file", sFileName); Sino.ExcelHelper.ExportListToExcel(path, devices, new Dictionary<string, string> { { "Id", "Id" } ,{ "ReturnTime","归还时间"} ,{ "CreateTime","创建时间"} ,{ "UpdateTime","修改时间"} }); result.Flag = 1; result.Data = "file" + "/" + sFileName; result.Message = "成功！"; return Ok(result); }
