.Net 基于MiniExcel的导入功能接口示例

way1:通过IFormFile，去读取数据转化集合操作数据

 /// <summary>
 /// 导入
 /// </summary>
 /// <param name="formFile"></param>
 /// <returns></returns>
 [HttpPost("Import")]
 public async Task<ExecResult> Import(IFormFile formFile)
 {
     try
     {
         if (formFile == null) throw new Exception("请选择文件导入!");
         var fileExt = Path.GetExtension(formFile.FileName);
         if (fileExt != ".xlsx") throw new Exception("请导入后缀名为.xlsx的Excel文件!");

         using var stream = new MemoryStream();
         formFile.CopyTo(stream);
         var maintainItems = new List<TpmMaintainItem>();
         var allItems = await _dbcontext.TpmMaintainItems.ToListAsync();
         var equipments = await _dbcontext.Equipment.ToListAsync();
         int num = 1;
         StringBuilder sb = new StringBuilder();
         foreach (IDictionary<string, object> row in stream.Query(true))
         {
             var equipmentCode = row["设备编码"];
             var name = row["维保项名称"];
             var cycle = row["维保项周期(天)"];
             var content = row["维保项内容"];
             var isMustCheck = row["是否必检"];
             var advanceDays = row["提前天数"];
             if (equipmentCode == null) sb.AppendLine($"第{num}行设备编码不存在!");
             if (name == null) sb.AppendLine($"第{num}行维保项名称不能为空!");
             if (cycle == null) sb.AppendLine($"第{num}行维保项周期(天)不能为空!");
             if (content == null) sb.AppendLine($"第{num}行维保项内容不能为空!");
             if (isMustCheck == null)
             {
                 sb.AppendLine($"第{num}行是否必检不能为空!");
             }
             else if(!(isMustCheck.ToString() == "是" || isMustCheck.ToString() == "否"))
             {
                 sb.AppendLine($"第{num}行是否必检为非法字符，只能填写是或否!");
             }
             if (advanceDays == null) sb.AppendLine($"第{num}行提前天数不能为空!");

             var equipment = equipments.FirstOrDefault(s=>s.EquipmentCode == equipmentCode.ToString());
             if(equipment == null) sb.AppendLine($"第{num}行设备编码:{equipmentCode}不存在!");
             var maintainItem = new TpmMaintainItem()
             {
                 EquipmentId = Convert.ToInt32(equipment?.EquipmentID),
                 Name = name.ToString(),
                 Cycle = Convert.ToInt32(cycle),
                 Content = content.ToString(),
                 IsMustCheck = string.Equals(isMustCheck.ToString(), "是") ? true : false,
                 AdvanceDays = Convert.ToInt32(advanceDays),
                 CreateTime = DateTime.Now
             };
             if (allItems.Any(s => s.Name == maintainItem.Name))
                 sb.AppendLine($"第{num}行数据已存在!");
             if(maintainItems.Any(s => s.Name == maintainItem.Name))
                 sb.AppendLine($"第{num}行数据重复!");
             maintainItems.Add(maintainItem);
             num++;
         }
         if(sb.Length > 0)
         {
             throw new Exception(sb.ToString());
         }
         else
         {
             await _dbcontext.TpmMaintainItems.AddRangeAsync(maintainItems);
             await _dbcontext.SaveChangesAsync();
         }

         return new ExecResult(true, "");
     }
     catch (Exception ex)
     {
         _logger.LogError(ex, "");
         return new ExecResult(false, "", ex.Message);
     }
 }

way2:通过文件路径path读取数据转化成集合操作数据

public class TpmMaintainItemImport
{
    /// <summary>
    /// 设备编码
    ///</summary>
    [ExcelColumnName("设备编码")]
    public string EquipmentCode { get; set; }
    /// <summary>
    /// 名称 
    ///</summary>
    [ExcelColumnName("维保项名称")]
    public string Name { get; set; }
    /// <summary>
    /// 周期 
    ///</summary>
    [ExcelColumnName("维保项周期(天)")]
    public int? Cycle { get; set; }
    /// <summary>
    /// 内容 
    ///</summary>
    [ExcelColumnName("维保项内容")]
    public string Content { get; set; }
    /// <summary>
    /// 是否必检(false-非必检;true-必检;)
    /// </summary>
    [ExcelColumnName("是否必检")]
    public string IsMustCheck { get; set; }
    /// <summary>
    /// 提前天数
    /// </summary>
    [ExcelColumnName("提前天数")]
    public int? AdvanceDays { get; set; }
}

 /// <summary>
 /// 导入
 /// </summary>
 /// <param name="path"></param>
 /// <returns></returns>
 [HttpPost("Import2")]
 public async Task<ExecResult> Import2(string path)
 {
     try
     {
         path = $@"C:\Users\xk\Desktop\维保项模板.xlsx";
         var rows = MiniExcel.Query<TpmMaintainItemImport>(path).ToList();
         var fileExt = Path.GetExtension(path.Split('\\').Last());
         if (fileExt != ".xlsx") throw new Exception("请导入后缀名为.xlsx的Excel文件!");

         var maintainItems = new List<TpmMaintainItem>();
         var allItems = await _dbcontext.TpmMaintainItems.ToListAsync();
         var equipments = await _dbcontext.Equipment.ToListAsync();
         int num = 1;
         StringBuilder sb = new StringBuilder();
         foreach (var row in rows)
         {
             var equipmentCode = row.EquipmentCode;
             var name = row.Name;
             var cycle = row.Cycle;
             var content = row.Content;
             var isMustCheck = row.IsMustCheck;
             var advanceDays = row.AdvanceDays;
             if (equipmentCode == null) sb.AppendLine($"第{num}行设备编码不存在!");
             if (name == null) sb.AppendLine($"第{num}行维保项名称不能为空!");
             if (cycle == null) sb.AppendLine($"第{num}行维保项周期(天)不能为空!");
             if (content == null) sb.AppendLine($"第{num}行维保项内容不能为空!");
             if (isMustCheck == null)
             {
                 sb.AppendLine($"第{num}行是否必检不能为空!");
             }
             else if (!(isMustCheck.ToString() == "是" || isMustCheck.ToString() == "否"))
             {
                 sb.AppendLine($"第{num}行是否必检为非法字符，只能填写是或否!");
             }
             if (advanceDays == null) sb.AppendLine($"第{num}行提前天数不能为空!");

             var equipment = equipments.FirstOrDefault(s => s.EquipmentCode == equipmentCode.ToString());
             if (equipment == null) sb.AppendLine($"第{num}行设备编码:{equipmentCode}不存在!");
             var maintainItem = new TpmMaintainItem()
             {
                 EquipmentId = Convert.ToInt32(equipment?.EquipmentID),
                 Name = name.ToString(),
                 Cycle = Convert.ToInt32(cycle),
                 Content = content.ToString(),
                 IsMustCheck = string.Equals(isMustCheck.ToString(), "是") ? true : false,
                 AdvanceDays = Convert.ToInt32(advanceDays),
                 CreateTime = DateTime.Now
             };
             if (allItems.Any(s => s.Name == maintainItem.Name))
                 sb.AppendLine($"第{num}行数据已存在!");
             if (maintainItems.Any(s => s.Name == maintainItem.Name))
                 sb.AppendLine($"第{num}行数据重复!");
             maintainItems.Add(maintainItem);
             num++;
         }
         if (sb.Length > 0)
         {
             throw new Exception(sb.ToString());
         }
         else
         {
             await _dbcontext.TpmMaintainItems.AddRangeAsync(maintainItems);
             await _dbcontext.SaveChangesAsync();
         }

         return new ExecResult(true, "");
     }
     catch (Exception ex)
     {
         _logger.LogError(ex, "");
         return new ExecResult(false, "", ex.Message);
     }
 }